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