solutions/Solution_09_flight_duration.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Previously we used functions that are build into the default python language.\n",
    "Enhanced functionality is available in python via other [modules](https://www.w3schools.com/python/python_modules.asp). For now we will only use packages that are already installed with the default python. We import this additional functionality with following code."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import json\n",
    "import datetime"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "json (Java Script Object Notation) is a way to store data in hierarchical order. To deal with data in that format python provides the [json package]( https://docs.python.org/3/library/json.html).\n",
    "\n",
    "As we will use dates and times we also import the package [datetime](https://docs.python.org/3/library/datetime.html) which provides the needed functionality."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "flightsjson = \"\"\"\n",
    "{\n",
    "    \"flights\":\n",
    "    [\n",
    "        {\n",
    "            \"Origin\": \"FRA\",\n",
    "            \"Destination\": \"NYC\",\n",
    "            \"Depdate\": \"2020-12-24T12:24:00Z\",\n",
    "            \"Arrdate\": \"2020-12-24T18:42:00Z\"\n",
    "        },\n",
    "        {\n",
    "            \"Origin\": \"FRA\",\n",
    "            \"Destination\": \"EWR\",\n",
    "            \"Depdate\": \"2020-12-24T13:42:00Z\",\n",
    "            \"Arrdate\": \"2020-12-24T19:22:00Z\"\n",
    "        },\n",
    "        {\n",
    "            \"Origin\": \"FRA\",\n",
    "            \"Destination\": \"EWR\",\n",
    "            \"Depdate\": \"2020-12-24T17:42:00Z\",\n",
    "            \"Arrdate\": \"2020-12-24T21:22:00Z\"\n",
    "        }\n",
    "    ]\n",
    "}\n",
    "\"\"\""
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The anoying colleague again came to us. This time with the json file that is structured as above. He asked us to sort the flights by flight duration time from lowest to highest.\n",
    "\n",
    "Many ways lead to rome:"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## First way: Build in packages\n",
    "One way to rome only uses the build in packages json and datetime to do the work.\n",
    "It has the advantage that it will run anywhere you can run python because it has no other dependencies:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {
    "tags": [
     "solution"
    ]
   },
   "outputs": [],
   "source": [
    "flights = json.loads(flightsjson)[\"flights\"]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[{'Origin': 'FRA',\n",
       "  'Destination': 'NYC',\n",
       "  'Depdate': '2020-12-24T12:24:00Z',\n",
       "  'Arrdate': '2020-12-24T18:42:00Z'},\n",
       " {'Origin': 'FRA',\n",
       "  'Destination': 'EWR',\n",
       "  'Depdate': '2020-12-24T13:42:00Z',\n",
       "  'Arrdate': '2020-12-24T19:22:00Z'},\n",
       " {'Origin': 'FRA',\n",
       "  'Destination': 'EWR',\n",
       "  'Depdate': '2020-12-24T17:42:00Z',\n",
       "  'Arrdate': '2020-12-24T21:22:00Z'}]"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "flights"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {
    "tags": [
     "solution"
    ]
   },
   "outputs": [],
   "source": [
    "for flight in flights:\n",
    "    dateformat = \"%Y-%m-%dT%H:%M:%SZ\"\n",
    "    flight[\"Depdate\"] = datetime.datetime.strptime(flight[\"Depdate\"], dateformat)\n",
    "    flight[\"Arrdate\"] = datetime.datetime.strptime(flight[\"Arrdate\"], dateformat)\n",
    "    flight[\"Flighttime\"] = flight[\"Arrdate\"] - flight[\"Depdate\"]\n",
    "\n",
    "sorted_flights = sorted(flights, key=lambda f: f[\"Flighttime\"])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[{'Origin': 'FRA',\n",
       "  'Destination': 'EWR',\n",
       "  'Depdate': datetime.datetime(2020, 12, 24, 17, 42),\n",
       "  'Arrdate': datetime.datetime(2020, 12, 24, 21, 22),\n",
       "  'Flighttime': datetime.timedelta(seconds=13200)},\n",
       " {'Origin': 'FRA',\n",
       "  'Destination': 'EWR',\n",
       "  'Depdate': datetime.datetime(2020, 12, 24, 13, 42),\n",
       "  'Arrdate': datetime.datetime(2020, 12, 24, 19, 22),\n",
       "  'Flighttime': datetime.timedelta(seconds=20400)},\n",
       " {'Origin': 'FRA',\n",
       "  'Destination': 'NYC',\n",
       "  'Depdate': datetime.datetime(2020, 12, 24, 12, 24),\n",
       "  'Arrdate': datetime.datetime(2020, 12, 24, 18, 42),\n",
       "  'Flighttime': datetime.timedelta(seconds=22680)}]"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sorted_flights"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Second way: pandas\n",
    "Another way to rome only uses [pandas](https://pandas.pydata.org/pandas-docs/stable/user_guide/10min.html) to read the json and sort it, that is much faster,\n",
    "if the json is more then our two example flights\n",
    "as it will run the computations in a vectorized way. \n",
    "Rough estimation: for 1.000 to 10.000.000 this will be the fastest solution"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {
    "tags": [
     "solution"
    ]
   },
   "outputs": [],
   "source": [
    "flights = json.loads(flightsjson)[\"flights\"]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {
    "tags": [
     "solution"
    ]
   },
   "outputs": [],
   "source": [
    "df = pd.read_json(json.dumps(flights), orient=\"records\", convert_dates=[\"Depdate\", \"Arrdate\"])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Origin</th>\n",
       "      <th>Destination</th>\n",
       "      <th>Depdate</th>\n",
       "      <th>Arrdate</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>FRA</td>\n",
       "      <td>NYC</td>\n",
       "      <td>2020-12-24 12:24:00+00:00</td>\n",
       "      <td>2020-12-24 18:42:00+00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>FRA</td>\n",
       "      <td>EWR</td>\n",
       "      <td>2020-12-24 13:42:00+00:00</td>\n",
       "      <td>2020-12-24 19:22:00+00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>FRA</td>\n",
       "      <td>EWR</td>\n",
       "      <td>2020-12-24 17:42:00+00:00</td>\n",
       "      <td>2020-12-24 21:22:00+00:00</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  Origin Destination                   Depdate                   Arrdate\n",
       "0    FRA         NYC 2020-12-24 12:24:00+00:00 2020-12-24 18:42:00+00:00\n",
       "1    FRA         EWR 2020-12-24 13:42:00+00:00 2020-12-24 19:22:00+00:00\n",
       "2    FRA         EWR 2020-12-24 17:42:00+00:00 2020-12-24 21:22:00+00:00"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Origin                      object\n",
       "Destination                 object\n",
       "Depdate        datetime64[ns, UTC]\n",
       "Arrdate        datetime64[ns, UTC]\n",
       "dtype: object"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.dtypes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {
    "tags": [
     "solution"
    ]
   },
   "outputs": [],
   "source": [
    "df[\"Flighttime\"] = df[\"Arrdate\"] - df[\"Depdate\"]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {
    "tags": [
     "solution"
    ]
   },
   "outputs": [],
   "source": [
    "df = df.sort_values(by=\"Flighttime\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Origin</th>\n",
       "      <th>Destination</th>\n",
       "      <th>Depdate</th>\n",
       "      <th>Arrdate</th>\n",
       "      <th>Flighttime</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>FRA</td>\n",
       "      <td>EWR</td>\n",
       "      <td>2020-12-24 17:42:00+00:00</td>\n",
       "      <td>2020-12-24 21:22:00+00:00</td>\n",
       "      <td>03:40:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>FRA</td>\n",
       "      <td>EWR</td>\n",
       "      <td>2020-12-24 13:42:00+00:00</td>\n",
       "      <td>2020-12-24 19:22:00+00:00</td>\n",
       "      <td>05:40:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>FRA</td>\n",
       "      <td>NYC</td>\n",
       "      <td>2020-12-24 12:24:00+00:00</td>\n",
       "      <td>2020-12-24 18:42:00+00:00</td>\n",
       "      <td>06:18:00</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  Origin Destination                   Depdate                   Arrdate  \\\n",
       "2    FRA         EWR 2020-12-24 17:42:00+00:00 2020-12-24 21:22:00+00:00   \n",
       "1    FRA         EWR 2020-12-24 13:42:00+00:00 2020-12-24 19:22:00+00:00   \n",
       "0    FRA         NYC 2020-12-24 12:24:00+00:00 2020-12-24 18:42:00+00:00   \n",
       "\n",
       "  Flighttime  \n",
       "2   03:40:00  \n",
       "1   05:40:00  \n",
       "0   06:18:00  "
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Third Way: Pyspark\n",
    "Another way is to only use [pyspark](https://spark.apache.org/docs/latest/sql-getting-started.html#tab_python_0) to read the json and sort it.\n",
    "This has some overhead as it will transfer the data to a cluster and between multiple nodes.\n",
    "But it will scale even over the biggest table of flights you can find\n",
    "Rough estimation: usefull for 100.000 and up.\n",
    "As it deals with distributed computing this is an additional task that I do not recommend for starters, but feel free. "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Google Colab: Pyspark is not available on google colab out of the box, thus uncomment the commented lines in the two cells below (remove the #) and execute it to install pyspark\n",
    "Databricks: ignore the next two cells"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# !pip install pyspark"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [],
   "source": [
    "# from pyspark.sql import SparkSession\n",
    "# spark = SparkSession.builder.getOrCreate()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'3.0.1'"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "spark.version"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {
    "tags": [
     "solution"
    ]
   },
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "c:\\users\\u538946\\appdata\\local\\continuum\\miniconda3\\envs\\python_advent_calendar\\lib\\site-packages\\pyspark\\sql\\session.py:381: UserWarning: inferring schema from dict is deprecated,please use pyspark.sql.Row instead\n",
      "  warnings.warn(\"inferring schema from dict is deprecated,\"\n"
     ]
    }
   ],
   "source": [
    "sparkdf = spark.createDataFrame(flights)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {
    "tags": [
     "solution"
    ]
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "DataFrame[Arrdate: string, Depdate: string, Destination: string, Origin: string]"
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sparkdf"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {
    "tags": [
     "solution"
    ]
   },
   "outputs": [],
   "source": [
    "sparkdf = sparkdf.withColumn(\"Arrdate\", sparkdf[\"Arrdate\"].cast(\"timestamp\"))\n",
    "sparkdf = sparkdf.withColumn(\"Depdate\", sparkdf[\"Depdate\"].cast(\"timestamp\"))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {
    "tags": [
     "solution"
    ]
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "DataFrame[Arrdate: timestamp, Depdate: timestamp, Destination: string, Origin: string]"
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sparkdf"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "tags": [
     "solution"
    ]
   },
   "outputs": [],
   "source": [
    "# first I thought about this, but the returned type interval can unfortunately not be used for sorting here\n",
    "# sparkdf = sparkdf.withColumn(\"FlighttimeReadable\", sparkdf[\"Arrdate\"] - sparkdf[\"Depdate\"])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {
    "tags": [
     "solution"
    ]
   },
   "outputs": [],
   "source": [
    "sparkdf = sparkdf.withColumn(\"Flighttime\", sparkdf[\"Arrdate\"].cast(\"long\") - sparkdf[\"Depdate\"].cast(\"long\"))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {
    "tags": [
     "solution"
    ]
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+-------------------+-------------------+-----------+------+----------+\n",
      "|            Arrdate|            Depdate|Destination|Origin|Flighttime|\n",
      "+-------------------+-------------------+-----------+------+----------+\n",
      "|2020-12-24 19:42:00|2020-12-24 13:24:00|        NYC|   FRA|     22680|\n",
      "|2020-12-24 20:22:00|2020-12-24 14:42:00|        EWR|   FRA|     20400|\n",
      "|2020-12-24 22:22:00|2020-12-24 18:42:00|        EWR|   FRA|     13200|\n",
      "+-------------------+-------------------+-----------+------+----------+\n",
      "\n"
     ]
    }
   ],
   "source": [
    "sparkdf.show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {
    "tags": [
     "solution"
    ]
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[('Arrdate', 'timestamp'),\n",
       " ('Depdate', 'timestamp'),\n",
       " ('Destination', 'string'),\n",
       " ('Origin', 'string'),\n",
       " ('Flighttime', 'bigint')]"
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sparkdf.dtypes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {
    "tags": [
     "solution"
    ]
   },
   "outputs": [],
   "source": [
    "df = sparkdf.sort(\"Flighttime\").toPandas()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Arrdate</th>\n",
       "      <th>Depdate</th>\n",
       "      <th>Destination</th>\n",
       "      <th>Origin</th>\n",
       "      <th>Flighttime</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2020-12-24 22:22:00</td>\n",
       "      <td>2020-12-24 18:42:00</td>\n",
       "      <td>EWR</td>\n",
       "      <td>FRA</td>\n",
       "      <td>13200</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2020-12-24 20:22:00</td>\n",
       "      <td>2020-12-24 14:42:00</td>\n",
       "      <td>EWR</td>\n",
       "      <td>FRA</td>\n",
       "      <td>20400</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2020-12-24 19:42:00</td>\n",
       "      <td>2020-12-24 13:24:00</td>\n",
       "      <td>NYC</td>\n",
       "      <td>FRA</td>\n",
       "      <td>22680</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "              Arrdate             Depdate Destination Origin  Flighttime\n",
       "0 2020-12-24 22:22:00 2020-12-24 18:42:00         EWR    FRA       13200\n",
       "1 2020-12-24 20:22:00 2020-12-24 14:42:00         EWR    FRA       20400\n",
       "2 2020-12-24 19:42:00 2020-12-24 13:24:00         NYC    FRA       22680"
      ]
     },
     "execution_count": 26,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df"
   ]
  }
 ],
 "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.7.9"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}