01-analyze-with-pandas.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "fbfb8579-5795-40e7-8110-132dde6d97b4",
   "metadata": {
    "tags": []
   },
   "source": [
    "# Analyze a subset with pandas\n",
    "\n",
    "\n",
    "In this notebook, we'll explore ~1 year the airline on-time performance data using pandas and understand some limitations of pandas.\n",
    "\n",
    "---"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a466a26d-571e-4239-9720-ac9cbd76178f",
   "metadata": {
    "tags": []
   },
   "source": [
    "Big data analysis always start with a manageable subset of the data, this allows you to:\n",
    "\n",
    "* Explore it with familiar tools like NumPy and pandas, and\n",
    "* Experiment with various computations you wish to do faster.\n",
    "\n",
    "After you have your computations ready, you can focus on scaling up!"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ebe4f6a7",
   "metadata": {},
   "source": [
    "\n",
    "## Introduce dataset: Airline on-time performance data\n",
    "\n",
    "In this tutorial, we will analyze **the [\"airline on-time performance\" dataset](https://www.transtats.bts.gov/Fields.asp?gnoyr_VQ=FGJ) -- a collection of flight records maintained by the U.S. Department of Transportation's Bureau of Transportation Statistics (BTS)**.\n",
    "\n",
    "This dataset provides information about the on-time performance of domestic flights operated by large air carriers in the United States, including flight delays, cancellations, and diversions. It covers flights operated by 23 major airlines and the records from 1987-present day.\n",
    "\n",
    "We will work with data from 2003-2022, which is ~70 GB in size on disk.\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "d70b3412",
   "metadata": {},
   "source": [
    "## Read a subset with pandas"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "dbc3a6fa-e1fe-4aa1-be7f-3ee92225d56c",
   "metadata": {},
   "source": [
    "Let's start by reading data for one year, 2022.\n",
    "\n",
    "The data is stored as one CSV file per month for each year:\n",
    "\n",
    "<img src=\"./images/csv-files.png\">"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "17e48bd6-d99f-4047-b06c-86127c6ee3e5",
   "metadata": {
    "tags": []
   },
   "source": [
    "The following cell prepares has some preliminary credential needed, we'll take a closer look at cloud storage in a future notebook."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "46d48237-e530-4312-9122-098688f4d972",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "import gcsfs\n",
    "\n",
    "fs = gcsfs.GCSFileSystem()\n",
    "files = [f\"gcs://{f}\" for f in fs.glob(\"quansight-datasets/airline-ontime-performance/csv/*2022.csv\")]"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "7566197d-183f-4072-95f8-460f37f8f904",
   "metadata": {},
   "source": [
    "You can read only one file at a time in a pandas DataDrame, so we'll read 12 individual files for each month and concatenate them:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "016048c6-69c2-4ab4-a0ad-87e226a4c9ba",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "8b796689-6a9e-476f-b8fb-7da8109bcb53",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "import json\n",
    "\n",
    "with open('prep/dtypes.json', 'r') as f:\n",
    "    dtypes = json.load(f)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "d05e9c77-e83f-47ba-812a-a3248745c674",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "# Note: This cell will take ~3 minutes to execute on a medium machine profile\n",
    "\n",
    "df_list = []\n",
    "\n",
    "for file in files:\n",
    "    df_temp = pd.read_csv(file, dtype=dtypes)\n",
    "    df_list.append(df_temp)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "2e0418e2-e3a2-4fb8-bf4d-e4a49c753731",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "df = pd.concat(df_list)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "919b495e-dda7-49b6-a5c3-b27ed5f81801",
   "metadata": {},
   "source": [
    "## Explore the dataset"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "73fa1406-9222-4d4c-90e8-fb4f5e00aefc",
   "metadata": {},
   "source": [
    "While the previous cells execute, let's [learn more about the dataset](https://www.transtats.bts.gov/Fields.asp?gnoyr_VQ=FGJ).\n",
    "\n",
    "Go to the above link and take a look at the information available. 👆"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "7e436f37-60f7-4ecb-b03c-c979e419f909",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "91149162-3324-4c63-9cda-6e034e41484b",
   "metadata": {},
   "source": [
    "Let's also list some column names for quicker access later, and note that the column names are capitalized in our dataset."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "d94c98ac-b131-4b87-9f3d-daee56d58464",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "df.columns[:61]"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "1ccc97a9-45b5-4dee-8133-4d055b95ed7f",
   "metadata": {},
   "source": [
    "Now let's perform some quick computations to get a better understanding of the dataset."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ae5a6d94-96c4-488a-8f16-e948ecf8af9f",
   "metadata": {},
   "source": [
    "### What is the total time people spent on a flight in 2022?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "804e4b78-02b0-43bb-b97a-e40c865c37e9",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "time_in_flight = df[\"ACTUAL_ELAPSED_TIME\"].sum()\n",
    "\n",
    "print(f\"People spent a total of {time_in_flight} minutes on a domestic flight, in 2022 in the USA; \\nwhich is ~{round(time_in_flight / (60*24*30*12), 2)} years in aggregate.\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "6dcac683-ec9f-483f-bab9-2ea99327e54e",
   "metadata": {
    "tags": []
   },
   "source": [
    "### 💻 Your turn: What are the maximum and average delays in flight departures?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "13c5a629-a693-43d6-a3ba-45127215b3eb",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "# Your code here. When ready, click on the three dots for the solution."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "c6177e76-fbd5-4862-a5e2-60991046ea5b",
   "metadata": {
    "jupyter": {
     "source_hidden": true
    },
    "tags": []
   },
   "outputs": [],
   "source": [
    "max_dep_delay = df[\"DEP_DELAY\"].max()\n",
    "print(f\"The maximum departure delay in departure is {max_dep_delay} minutes, or ~{max_dep_delay // 60} hours.\")\n",
    "\n",
    "mean_dep_delay = df[\"DEP_DELAY\"].mean()\n",
    "print(f\"The average departure delay in departure is {round(mean_dep_delay, 2)} minutes.\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "5f884484-9154-4621-8585-1fec2a9155fb",
   "metadata": {},
   "source": [
    "### Which airport/airline has the most flight departure and arrival delays?"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "82d70c61-6e67-4b04-b8f5-80e835cf68ef",
   "metadata": {},
   "source": [
    "**Airport:**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "6723f623-2a8b-4b18-bb64-fa509bc27dd5",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "df.groupby(\"ORIGIN\")[\"DEP_DELAY\"].count().idxmax()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "fb28958f-307a-46ce-b6fd-dfc7010ec1f2",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "df.groupby(\"ORIGIN\")[\"ARR_DELAY\"].count().idxmax()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "008d4ef5-a0e8-4e94-91fe-f08548c6a337",
   "metadata": {},
   "source": [
    "That's the code for Hartsfield-Jackson Atlanta International Airport, interesting!"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "e15c485e-e2f8-424b-bfe2-06f8f135c21e",
   "metadata": {
    "tags": []
   },
   "source": [
    "**💻 Your turn: Airline**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "35649714-032f-4d43-a0a2-c210323099be",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Your code here. When ready, click on the three dots for the solutions."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "acea16c2-da2e-4510-b669-5935f829e7eb",
   "metadata": {
    "jupyter": {
     "source_hidden": true
    },
    "tags": []
   },
   "outputs": [],
   "source": [
    "df.groupby(\"OP_CARRIER\")[\"DEP_DELAY\"].count().idxmax()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "20e8d625-9567-43cd-93e5-72dab3d4b4d6",
   "metadata": {
    "jupyter": {
     "source_hidden": true
    },
    "tags": []
   },
   "outputs": [],
   "source": [
    "df.groupby(\"OP_CARRIER\")[\"ARR_DELAY\"].count().idxmax()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "fdfe9b06-7bc0-438e-9871-848df3786cfe",
   "metadata": {
    "jupyter": {
     "source_hidden": true
    },
    "tags": []
   },
   "source": [
    "'WN' is the code for Southwest Airlines."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "d220a37f-d1ba-49f2-96df-5e787ae9cb61",
   "metadata": {
    "tags": []
   },
   "source": [
    "### Get all \"DISTANCE\" values in kilometers instead of miles"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "5c262a93-6d90-4ef0-a7b5-e2a01b375c4f",
   "metadata": {},
   "outputs": [],
   "source": [
    "df.DISTANCE.apply(lambda x: x*1.609344)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a5cb0519-6631-44d6-be72-901717053024",
   "metadata": {
    "tags": []
   },
   "source": [
    "### 💻 Your turn: Which type of delay contributes most to the overall departure delay?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "cc52b071-eca9-4f52-8122-619431c1c271",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Your code here. When ready, click on the three dots for the solution."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "5daff761-f0bc-4151-b570-fc688324d186",
   "metadata": {
    "jupyter": {
     "source_hidden": true
    },
    "tags": []
   },
   "outputs": [],
   "source": [
    "# Note: Kernel restarts!\n",
    "\n",
    "df[['CARRIER_DELAY', 'WEATHER_DELAY', 'NAS_DELAY', 'SECURITY_DELAY', 'LATE_AIRCRAFT_DELAY']] \\\n",
    ".sum() \\\n",
    ".idxmax(axis=1)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "6fae07fd-0f9e-4405-b7e7-85e3a5896b1e",
   "metadata": {},
   "source": [
    "This computation leads to a kernel restart because we're reaching the limits of pandas here."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "07f38d1b-a77f-4d87-9b59-1b461c2b230b",
   "metadata": {
    "tags": []
   },
   "source": [
    "## Need for scale: Try to read the full dataset in pandas\n",
    "\n",
    "You also won't be able to load the full dataset in pandas without the kernel crashing."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "5e04589e-c25e-46af-953a-c1f68891c55c",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "files = [f\"gcs://{f}\" for f in fs.glob(\"quansight-datasets/airline-ontime-performance/csv/*.csv\")]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "6f69b86c-9837-44dc-9a19-90b205eaccfb",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "with open('prep/dtypes.json', 'r') as f:\n",
    "    dtypes = json.load(f)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "3c76a891-b535-412f-a74c-e2640b3f5f8f",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "# Note: Kernel restarts\n",
    "\n",
    "df_list = []\n",
    "\n",
    "for file in files:\n",
    "    df_temp = pd.read_csv(file, dtype=dtypes)\n",
    "    df_list.append(df_temp)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "65a85c5f-91a9-47b8-932f-76a1c6f268c7",
   "metadata": {},
   "source": [
    "We'll see how we can overcome this with Dask in the upcoming notebooks!"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "eed1c896-0785-4c6f-90a3-5f1ce77fc3f9",
   "metadata": {
    "tags": []
   },
   "source": [
    "---\n",
    "\n",
    "## Next →\n",
    "\n",
    "Let's learn to create some [pretty plots](./02-intro-to-hvplot.ipynb)!"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "c74e581e-f308-4354-91c2-577f69295998",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "pycon2023-pycon2023-tutorial",
   "language": "python",
   "name": "conda-env-pycon2023-pycon2023-tutorial-py"
  },
  "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.10.10"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}