Final Project

Tools for Data Science

Author

Margaret Curd 

## I put this in the console to load all packages
#install.packages("reticulate")
#install.packages(c("nycflights13","sqldf","tidyverse"))
#reticulate::py_install(c("pandas", "seaborn", "matplotlib", "nycflights13"))
#reticulate::py_install("setuptools<=81.0.2", pip = TRUE)
library(dplyr)

In this project you will be working with R, SQL, and Python in the same document. We will use the data sets airlines and flights from the package nycflights13.

  1. Render the empty file (to make sure everything is working)
  2. Consistently Render the file each time you answer a question

In R, install the packages nycflights13, sqldf, tidyverse and load all of them and data sets flights and airlines. Take your time to understand the data sets.

#install.packages(c("nycflights13","sqldf","tidyverse"))

# Load all packages here
library(nycflights13)
library(sqldf)
library(tidyverse)

# Load the data here
data(flights)
data(airlines)

Question 1: List the name of airlines where the destination is MIA airport with their average arrival delays and sort them from the smallest to largest average arrival delays. Use data frames flights and airlines.

We shall solve this question using R, SQL, and Python.

R solution

MIA = inner_join(flights, airlines, by = "carrier") %>% filter(dest == "MIA") %>% select(name, arr_delay, dest) %>% group_by(name) %>% summarize(avg_arr_delay = mean(arr_delay, na.rm= TRUE)) %>% arrange(avg_arr_delay)

MIA
# A tibble: 3 × 2
  name                   avg_arr_delay
  <chr>                          <dbl>
1 American Airlines Inc.         -1.88
2 Delta Air Lines Inc.            2.27
3 United Air Lines Inc.           6.66

SQL solution

Write your SQL query in the function sqldf(). For example,sqldf("select * from relig_income") list all rows in the data frame relig_income.

result_sql = sqldf("
  SELECT
    a.name AS airline_name,
    AVG(f.arr_delay) AS avg_arr_delay
  FROM
    flights f
  INNER JOIN
    airlines a ON f.carrier = a.carrier
  WHERE
    f.dest = 'MIA'
  GROUP BY
    a.name
  ORDER BY
    avg_arr_delay ASC
")

print(result_sql)
            airline_name avg_arr_delay
1 American Airlines Inc.     -1.878482
2   Delta Air Lines Inc.      2.268091
3  United Air Lines Inc.      6.655685

Python solution

# load python libraries
import pandas as pd
import matplotlib.pyplot as plt
from nycflights13 import flights, airlines

# load data

from nycflights13 import flights as flights_df
from nycflights13 import airlines as airlines_df

# code here
MIA = (
    flights
    .merge(airlines, on="carrier")
    .query("dest == 'MIA'")
    [["name", "arr_delay", "dest"]]
    .groupby("name", as_index=False)
    .agg(avg_arr_delay=("arr_delay", "mean"))
    .sort_values("avg_arr_delay")
)

MIA
                     name  avg_arr_delay
0  American Airlines Inc.      -1.878482
1    Delta Air Lines Inc.       2.268091
2   United Air Lines Inc.       6.655685

Question 2: Plot the boxplot of the departure delays vs the name of airlines where the destination is MIA airport. Solve this question using R and Python.

R solution

mia_flights <- flights %>%
  filter(dest == "MIA") %>%
  inner_join(airlines, by = "carrier")

# Boxplot: departure delay vs airline name
ggplot(mia_flights, aes(x = name, y = dep_delay)) +
  geom_boxplot() +
  labs(
    title = "Departure Delays for Flights to MIA by Airline",
    x = "Airline",
    y = "Departure Delay (minutes)"
  )

Python solution

import pandas as pd
import matplotlib.pyplot as plt
from nycflights13 import flights, airlines

from nycflights13 import flights as flights_df
from nycflights13 import airlines as airlines_df

# Filter flights going to MIA and join airline names
mia_flights = flights_df[flights_df["dest"] == "MIA"].merge(
    airlines_df, on="carrier"
)

# Boxplot: departure delay vs airline name
plt.figure(figsize=(12, 6))

mia_flights.boxplot(column="dep_delay", by="name")

plt.xticks(rotation=45, ha="right")
(array([1, 2, 3]), [Text(1, 0, 'American Airlines Inc.'), Text(2, 0, 'Delta Air Lines Inc.'), Text(3, 0, 'United Air Lines Inc.')])
plt.title("Departure Delays for Flights to MIA by Airline")
plt.xlabel("Airline")
plt.ylabel("Departure Delay (minutes)")
plt.tight_layout()
plt.show()

Question 3: For each airlines, 1) find the month where the average departure delay time is the highest in the year. 2) Make a visualization to show the results. Solve this question using your preferred language R or Python.

# load libraries
library(tidyverse)
library(nycflights13)

# load data
data(flights)
data(airlines)

# 1) code here to find the months

highest_month = inner_join(flights, airlines, by = "carrier") %>% select(name, dep_delay, month) %>% group_by(name, month) %>% summarize(avg_dep_delay = mean(dep_delay, na.rm= TRUE)) %>% slice_max(avg_dep_delay)

View(highest_month)

# 2) code here to make the visualization
ggplot(highest_month, aes(x = name, y = avg_dep_delay, fill = factor(month)))+
  geom_col() +
  labs(y = "Highest Average Departure Delay", x = "Airline", fill = "Month")+
  theme(axis.text.x = element_text(angle = 45, hjust = 1))