library(dplyr)
## 
## Attaching package: 'dplyr'
## The following objects are masked from 'package:stats':
## 
##     filter, lag
## The following objects are masked from 'package:base':
## 
##     intersect, setdiff, setequal, union
flights <- read.csv("/Users/gavin/Downloads/CVG_Flights.csv", header = TRUE, na.strings = "")
airlines <- read.csv("/Users/gavin/Downloads/airlines.csv", header = TRUE, na.strings = "")
airports <- read.csv("/Users/gavin/Downloads/airports.csv", header = TRUE, na.strings = "")
nrow(flights)
## [1] 7763
ncol(flights)
## [1] 27
nrow(airlines)
## [1] 14
ncol(airlines)
## [1] 2
nrow(airports)
## [1] 322
ncol(airports)
## [1] 7
merged_data <- merge(merge(flights, airports, by.x = "ORIGIN_AIRPORT", by.y = "IATA_CODE", all.x = TRUE), airlines, by.x = "AIRLINE", by.y = "IATA_CODE", all.x = TRUE)
head(merged_data)
##   AIRLINE ORIGIN_AIRPORT FLIGHT_DATE FLIGHT_NUMBER DESTINATION_AIRPORT
## 1      DL            ATL   1/12/2015          1126                 CVG
## 2      DL            ATL   1/23/2015          1955                 CVG
## 3      DL            ATL   2/16/2015           565                 CVG
## 4      DL            ATL    3/4/2015           915                 CVG
## 5      DL            ATL   2/24/2015           565                 CVG
## 6      DL            ATL   1/22/2015          1126                 CVG
##   SCHEDULED_DEPARTURE DEPARTURE_TIME DEPARTURE_DELAY TAXI_OUT  WHEELS_OFF
## 1          9:54:00 PM    11:43:00 PM             109       38  9:21:00 PM
## 2          2:07:00 PM     2:01:00 PM              -6       20  2:21:00 PM
## 3         12:05:00 PM    12:11:00 PM               6       10 12:21:00 PM
## 4          7:12:00 PM     7:14:00 PM               2       21  7:35:00 PM
## 5         12:05:00 PM     1:24:00 PM              79       48  2:12:00 PM
## 6          9:54:00 PM     9:51:00 PM              -3       17 10:08:00 PM
##   SCHEDULED_TIME ELAPSED_TIME AIR_TIME DISTANCE  WHEELS_ON TAXI_IN
## 1             83          101       58      373 1:19:00 AM       5
## 2             86           86       60      373 1:21:00 AM       6
## 3             84           71       55      373 1:16:00 AM       6
## 4             87           80       53      373 2:28:00 AM       6
## 5             84          110       57      373 1:09:00 AM       5
## 6             83           76       55      373 2:03:00 AM       4
##   SCHEDULED_ARRIVAL ARRIVAL_TIME ARRIVAL_DELAY DIVERTED CANCELLED
## 1       11:17:00 PM   1:24:00 AM           127        0         0
## 2        3:33:00 PM   3:27:00 PM            -6        0         0
## 3        1:29:00 PM   1:22:00 PM            -7        0         0
## 4        8:39:00 PM   8:34:00 PM            -5        0         0
## 5        1:29:00 PM   3:14:00 PM           105        0         0
## 6       11:17:00 PM  11:07:00 PM           -10        0         0
##   CANCELLATION_REASON AIR_SYSTEM_DELAY SECURITY_DELAY AIRLINE_DELAY
## 1                <NA>               18              0             0
## 2                <NA>               NA             NA            NA
## 3                <NA>               NA             NA            NA
## 4                <NA>               NA             NA            NA
## 5                <NA>               26              0            73
## 6                <NA>               NA             NA            NA
##   LATE_AIRCRAFT_DELAY WEATHER_DELAY
## 1                 109             0
## 2                  NA            NA
## 3                  NA            NA
## 4                  NA            NA
## 5                   6             0
## 6                  NA            NA
##                                            AIRPORT    CITY STATE COUNTRY
## 1 Hartsfield-Jackson Atlanta International Airport Atlanta    GA     USA
## 2 Hartsfield-Jackson Atlanta International Airport Atlanta    GA     USA
## 3 Hartsfield-Jackson Atlanta International Airport Atlanta    GA     USA
## 4 Hartsfield-Jackson Atlanta International Airport Atlanta    GA     USA
## 5 Hartsfield-Jackson Atlanta International Airport Atlanta    GA     USA
## 6 Hartsfield-Jackson Atlanta International Airport Atlanta    GA     USA
##   LATITUDE LONGITUDE            AIRLINE.y
## 1 33.64044 -84.42694 Delta Air Lines Inc.
## 2 33.64044 -84.42694 Delta Air Lines Inc.
## 3 33.64044 -84.42694 Delta Air Lines Inc.
## 4 33.64044 -84.42694 Delta Air Lines Inc.
## 5 33.64044 -84.42694 Delta Air Lines Inc.
## 6 33.64044 -84.42694 Delta Air Lines Inc.
any(is.na(merged_data))
## [1] TRUE
colSums(is.na(merged_data))
##             AIRLINE      ORIGIN_AIRPORT         FLIGHT_DATE       FLIGHT_NUMBER 
##                   0                   0                   0                   0 
## DESTINATION_AIRPORT SCHEDULED_DEPARTURE      DEPARTURE_TIME     DEPARTURE_DELAY 
##                   0                   0                 370                 370 
##            TAXI_OUT          WHEELS_OFF      SCHEDULED_TIME        ELAPSED_TIME 
##                 378                 378                   0                 392 
##            AIR_TIME            DISTANCE           WHEELS_ON             TAXI_IN 
##                 392                   0                 381                 381 
##   SCHEDULED_ARRIVAL        ARRIVAL_TIME       ARRIVAL_DELAY            DIVERTED 
##                   0                 381                 392                   0 
##           CANCELLED CANCELLATION_REASON    AIR_SYSTEM_DELAY      SECURITY_DELAY 
##                   0                7385                5814                5814 
##       AIRLINE_DELAY LATE_AIRCRAFT_DELAY       WEATHER_DELAY             AIRPORT 
##                5814                5814                5814                   0 
##                CITY               STATE             COUNTRY            LATITUDE 
##                   0                   0                   0                   0 
##           LONGITUDE           AIRLINE.y 
##                   0                   0
colMeans(is.na(merged_data))
##             AIRLINE      ORIGIN_AIRPORT         FLIGHT_DATE       FLIGHT_NUMBER 
##          0.00000000          0.00000000          0.00000000          0.00000000 
## DESTINATION_AIRPORT SCHEDULED_DEPARTURE      DEPARTURE_TIME     DEPARTURE_DELAY 
##          0.00000000          0.00000000          0.04766199          0.04766199 
##            TAXI_OUT          WHEELS_OFF      SCHEDULED_TIME        ELAPSED_TIME 
##          0.04869252          0.04869252          0.00000000          0.05049594 
##            AIR_TIME            DISTANCE           WHEELS_ON             TAXI_IN 
##          0.05049594          0.00000000          0.04907896          0.04907896 
##   SCHEDULED_ARRIVAL        ARRIVAL_TIME       ARRIVAL_DELAY            DIVERTED 
##          0.00000000          0.04907896          0.05049594          0.00000000 
##           CANCELLED CANCELLATION_REASON    AIR_SYSTEM_DELAY      SECURITY_DELAY 
##          0.00000000          0.95130748          0.74893727          0.74893727 
##       AIRLINE_DELAY LATE_AIRCRAFT_DELAY       WEATHER_DELAY             AIRPORT 
##          0.74893727          0.74893727          0.74893727          0.00000000 
##                CITY               STATE             COUNTRY            LATITUDE 
##          0.00000000          0.00000000          0.00000000          0.00000000 
##           LONGITUDE           AIRLINE.y 
##          0.00000000          0.00000000
prop.table(table(merged_data$CANCELLED))
## 
##          0          1 
## 0.95130748 0.04869252
unique(merged_data$CANCELLATION_REASON)
## [1] NA  "B" "A" "C"
sum(is.na(merged_data$DEPARTURE_TIME))
## [1] 370
merged_data$TIME_DIFFERENCE <- merged_data$SCHEDULED_TIME - merged_data$ELAPSED_TIME

head(merged_data$TIME_DIFFERENCE)
## [1] -18   0  13   7 -26   7
filtered_data <- merged_data %>%
  filter(AIRLINE == "Delta" & ORIGIN_AIRPORT == "Cincinnati/Northern Kentucky International Airport" & DEPARTURE_DELAY >30)

head(filtered_data$FLIGHT_NUMBER)
## integer(0)
avg_delay_by_airline <- merged_data %>%
  group_by(AIRLINE) %>%
  summarize(AVG_DELAY = mean(DEPARTURE_DELAY, na.rm = TRUE))

arrange(avg_delay_by_airline, desc(AVG_DELAY))
## # A tibble: 5 × 2
##   AIRLINE AVG_DELAY
##   <chr>       <dbl>
## 1 F9          23.9 
## 2 MQ          19.1 
## 3 OO          11.1 
## 4 EV          10.4 
## 5 DL           6.09
arrange(avg_delay_by_airline, AVG_DELAY)
## # A tibble: 5 × 2
##   AIRLINE AVG_DELAY
##   <chr>       <dbl>
## 1 DL           6.09
## 2 EV          10.4 
## 3 OO          11.1 
## 4 MQ          19.1 
## 5 F9          23.9
avg_delay_by_airline <- merged_data %>%
  group_by(ORIGIN_AIRPORT) %>%
  summarize(AVG_DELAY = mean(DEPARTURE_DELAY, na.rm = TRUE))

arrange(avg_delay_by_airline, desc(AVG_DELAY)) %>% head()
## # A tibble: 6 × 2
##   ORIGIN_AIRPORT AVG_DELAY
##   <chr>              <dbl>
## 1 TTN                 74.3
## 2 RDU                 70.8
## 3 PIT                 33.5
## 4 JFK                 27.4
## 5 LAS                 26.0
## 6 LGA                 25.6
table(merged_data$AIRLINE[merged_data$ORIGIN_AIRPORT == "CVG"])
## 
##   DL   EV   F9   MQ   OO 
## 1149  776  258 1041  662