An-Analysis-of-Commercial-Flight-Patterns-in-the-USA-1987-2008.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#importin the libraries\n",
    "from pyspark.sql import SparkSession\n",
    "from pyspark.sql.functions import *\n",
    "from pyspark.sql import Window\n",
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#create spark session\n",
    "spark = SparkSession \\\n",
    "        .builder \\\n",
    "        .appName(\"AirFlightData\") \\\n",
    "        .getOrCreate()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "pd.set_option('display.max_columns', None)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "data_df= spark.read.csv('airline.csv.shuffle.bz2', header=True, inferSchema=True)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#EDA\n",
    "#data_df.describe()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#data_df.printSchema()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#data_df.select([count(when(isnull(c) | isnan(c), c)).alias(c) for c in data_df.columns])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#ActualElapsedTime \n",
    "#data_df.summary()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "data_df=data_df.na.drop(subset=[\"ArrDelay\"])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "data_df=data_df.na.fill(value=0)\n",
    "data_df=data_df.na.fill('')"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Best time of day/day of week/time of year to fly to\n",
    "minimize delays"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Best time of the day to travel\n",
    "#lets first find the highst deay time\n",
    "\n",
    "GetHour = udf(lambda x: str(x)[0:-2] if str(x)[0:-2] else 0 , StringType())\n",
    "data_hour_df=data_df.withColumn(\"Hour\", GetHour(data_df['CRSDepTime']))\n",
    "\n",
    "data_hour_df=data_hour_df.groupBy(\"Hour\").agg(mean(\"ArrDelay\").alias(\"average\")).sort('average')\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "data_day_df=data_df.groupBy(\"DayOfWeek\").agg(mean(\"ArrDelay\").alias(\"average\")).sort('average')\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "data_DayofMonth_df=data_df.groupBy(\"DayofMonth\").agg(mean(\"ArrDelay\").alias(\"average\")).sort('average')\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "data_Month_df=data_df.groupBy(\"Month\").agg(mean(\"ArrDelay\").alias(\"average\")).sort('average')\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "data_year_df=data_df.groupBy(\"Year\").agg(mean(\"ArrDelay\").alias(\"average\")).sort('average')\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "data_FlightNum_df=data_df.groupBy(\"FlightNum\").agg(mean(\"ArrDelay\").alias(\"average\")).sort('average')\n",
    "\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "data_Origin_DISt_df=data_df.groupBy(\"Origin\",\"Dest\").agg(mean(\"ArrDelay\").alias(\"average\")).sort('average')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#the Most company with delays\n",
    "Most_delays_bycompany_df=data_df.groupBy(\"UniqueCarrier\").agg(mean(\"ArrDelay\").alias(\"average\")).sort('average')\n"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "How does the number of people flying between different\n",
    "locations change over time?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "data_traveling_from_df=data_df.groupBy(\"Year\",\"Origin\").count().alias(\"count\").sort('count')\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "data_traveling_To_df=data_df.groupBy(\"Year\",\"Dest\").count().alias(\"count\").sort('count')\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "## main travel cancellation reasons\n",
    "cancelled=data_df.groupBy('CancellationCode').count().alias(\"count\").sort('count').where(data_df.CancellationCode !='')\n",
    "\n"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "How well does weather predict plane delays?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "Bad_weather_days_df=data_df.where(data_df.CancellationCode =='B').select(data_df['Year'].alias('Y'),\\\n",
    "    data_df[\"Month\"].alias('M'),data_df['DayofMonth'].alias('D'),'Origin','Dest').drop_duplicates()\n",
    "\n",
    "Bad_weather_days_df=Bad_weather_days_df.join(data_df, (Bad_weather_days_df['Y'] == data_df['Year']) &\\\n",
    "    (Bad_weather_days_df['M'] == data_df['Month']) &\\\n",
    "       (Bad_weather_days_df['D'] == data_df['DayofMonth']), 'inner')\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "Bad_weather_days_dayofmonth_df=Bad_weather_days_df.groupBy(\"DayofMonth\").agg(mean(\"ArrDelay\").alias(\"average\")).sort('average')\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "Bad_weather_month_df=Bad_weather_days_df.groupBy(\"Month\").agg(mean(\"ArrDelay\").alias(\"average\")).sort('average')\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "Bad_weather_day_df=Bad_weather_days_df.groupBy(\"DayOfWeek\").agg(mean(\"ArrDelay\").alias(\"average\")).sort('average')\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "data_hour_df.write.options(header=True).csv('delay by hour')\n",
    "data_day_df.write.options(header=True).csv('delay by day')\n",
    "data_DayofMonth_df.write.options(header=True).csv('delay by day of month')\n",
    "data_Month_df.write.options(header=True).csv('delay by Month')\n",
    "data_year_df.write.options(header=True).csv('delay by Year')\n",
    "data_FlightNum_df.write.options(header=True).csv('delay by flight number')\n",
    "data_Origin_DISt_df.write.options(header=True).csv('delay by origin and destnation')\n",
    "Most_delays_bycompany_df.write.options(header=True).csv('delay by company')\n",
    "data_traveling_from_df.write.options(header=True).csv('most airports that were origins by year')\n",
    "data_traveling_To_df.write.options(header=True).csv('most airports that were Destnation by year')\n",
    "cancelled.write.options(header=True).csv('most reasons the flights canceled for')\n",
    "\n",
    "Bad_weather_days_dayofmonth_df.write.options(header=True).csv('Bad wether effect on delay by day of month')\n",
    "Bad_weather_month_df.write.options(header=True).csv('Bad wether effect on delay by month')\n",
    "Bad_weather_day_df.write.options(header=True).csv('Bad wether effect on delay by Day of the week')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import bz2\n",
    "\n",
    "bfb = bz2.BZ2File('airline.csv', mode='wb')\n",
    "bfb.co\n"
   ]
  }
 ],
 "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.10.4"
  },
  "orig_nbformat": 4,
  "vscode": {
   "interpreter": {
    "hash": "0b72cfdca056c30f4f63f36d895bd96dfc6025b0bf21aba3014f18b17660fa99"
   }
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}