Air Traffic.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Data processing of OpenSky COVID-19 Flight Dataset"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Import libraries"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "from pathlib import Path\n",
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Read all OpenSky CSV files (takes some time)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "flightlist = pd.concat(\n",
    "    pd.read_csv(file, parse_dates=[\"firstseen\", \"lastseen\", \"day\"])\n",
    "    for file in Path(\"opensky_data\").glob(\"flightlist_*.csv.gz\")\n",
    ")\n",
    "flightlist = flightlist.drop(columns=[\"Unnamed: 0\"])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Basic exploratory data analysis"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "flightlist.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "flightlist.describe()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "print(len(flightlist))\n",
    "print(flightlist.origin.isnull().sum(axis = 0) / len(flightlist))\n",
    "print(flightlist.destination.isnull().sum(axis = 0) / len(flightlist))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Delete all records that have no origin or destination data (this action is subject to discussion)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "flightlist = flightlist[flightlist['origin'].notna() & flightlist['destination'].notna()]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Make matching of countries by ICAO codes table"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Read ICAO data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "icao_codes = pd.read_excel(Path(\"auxiliary_data/icao_codes.xlsx\"), usecols=[\"ICAO\", \"Country\"], sheet_name = \"data\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "icao_codes.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Join by origin"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "flightlist = pd.merge(flightlist, icao_codes, how=\"left\", left_on = [\"origin\"], right_on=[\"ICAO\"])\n",
    "flightlist = flightlist.rename(columns={\"Country\": \"country_origin\"}).drop(columns=[\"ICAO\"])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Join by destination"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "flightlist = pd.merge(flightlist, icao_codes, how=\"left\", left_on = [\"destination\"], right_on=[\"ICAO\"])\n",
    "flightlist = flightlist.rename(columns={\"Country\": \"country_destination\"}).drop(columns=[\"ICAO\"])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Explore results of join"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "flightlist.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "print(len(flightlist))\n",
    "print(flightlist.country_origin.isnull().sum(axis = 0) / len(flightlist))\n",
    "print(flightlist.country_destination.isnull().sum(axis = 0) / len(flightlist))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Make matching of countries by ICAO nationality prefixes"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Create prefixes for origin and destination"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "flightlist['origin_pref'] = flightlist['origin'].astype(str).str[:2]\n",
    "flightlist['destination_pref'] = flightlist['destination'].astype(str).str[:2]\n",
    "flightlist.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Read ICAO nationality prefixes data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "country_prefix = pd.read_excel(Path(\"auxiliary_data/icao_nationality_prefixes.xlsx\"), usecols=[\"prefix\", \"Country\"], sheet_name = \"data\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "country_prefix.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Join by origin and destination (1st and 2nd cells respectively)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "flightlist = pd.merge(flightlist, country_prefix, how=\"left\", left_on = [\"origin_pref\"], right_on=[\"prefix\"])\n",
    "flightlist = flightlist.rename(columns={\"Country\": \"country_origin_2\"}).drop(columns=[\"prefix\"])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "flightlist = pd.merge(flightlist, country_prefix, how=\"left\", left_on = [\"destination_pref\"], right_on=[\"prefix\"])\n",
    "flightlist = flightlist.rename(columns={\"Country\": \"country_destination_2\"}).drop(columns=[\"prefix\"])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "flightlist.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Fill NAs from the 1st join with values from the 2nd join"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "flightlist['country_origin'] = flightlist['country_origin'].fillna(flightlist['country_origin_2'])\n",
    "flightlist['country_destination'] = flightlist['country_destination'].fillna(flightlist['country_destination_2'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "print(len(flightlist))\n",
    "print(flightlist.country_origin.isnull().sum(axis = 0) / len(flightlist))\n",
    "print(flightlist.country_destination.isnull().sum(axis = 0) / len(flightlist))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Remove records which didn't match with any country"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "flightlist = flightlist[flightlist['country_origin'].notna() & flightlist['country_destination'].notna()]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Delete unnecessary columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "flightlist = flightlist.drop(columns=[\"country_origin_2\", \"country_destination_2\", \"origin_pref\", \"destination_pref\"])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Add a column that indicates whether the flight is international or not."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "flightlist['international'] = (flightlist[\"country_origin\"] != flightlist[\"country_destination\"])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Remove timezone information from \"day\" column"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "flightlist['day'] = flightlist['day'].dt.date"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Calculate the number of domestic flights for every country by dates"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "domestic_flights=flightlist[~flightlist.international].groupby(['country_origin', 'day'], as_index = False)['callsign'\n",
    "        ].count().rename(columns = {'callsign': 'domestic_flights', 'country_origin':'country'})"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Calculate the number of international departures and arrivals for every country by dates"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "international_departures=flightlist[flightlist.international].groupby(['country_origin', 'day'], as_index = False)['callsign'\n",
    "        ].count().rename(columns = {'callsign': 'international_departures', 'country_origin':'country'})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "international_arrivals=flightlist[flightlist.international].groupby(['country_destination', 'day'], as_index = False)['callsign'\n",
    "        ].count().rename(columns = {'callsign': 'international_arrivals', 'country_destination':'country'})"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Join all these tables"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "flights = pd.merge(domestic_flights, international_departures, how=\"outer\", on=[\"country\", \"day\"])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "flights = pd.merge(flights, international_arrivals, how=\"outer\", on=[\"country\", \"day\"])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "flights['total_flights'] = flights['domestic_flights'] + flights['international_departures'] + flights['international_arrivals']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "flights.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Write results to CSV and Excel"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "flights.to_csv(Path(\"flight_data/flights.csv\"), index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "flights.to_excel(Path(\"flight_data/flights.xlsx\"), index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.6.10"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}