MATH2349 Semester 2, 2019

Assignment 3

Required packages

library(readr)
library(magrittr)
library(tidyr)
library(Hmisc)
library(dplyr)
library(outliers)

Executive Summary

• The assignment makes use of the knowledge gained in the data pre-processing course to process the open data set.The data utilised in this assignment is about the flight delay and cancelation information in the United States. The three datasets used are aeroplane.csv,airport.csv and airlines.csv. This datasets are joined using left join by a variable that is common. In order to increase the ease of handelling the data, few non required variables are excluded.
• The variables are of different classes ,some variables are made into a factor. And the month and the week were changed from numeric value to the names of the month and week respectively.
• The dataset was already tidy. The variables which are of no used were removed. A new column was generated from the existing ones by utilising the mutate function.
• The missing values that belonged to numerical class were replaced by the mean and the missing values that belong to categorical were replaced by the mode.
• By utilising the boxplot the outliers were detected and capping techniques were utilised to remove the outliers to the maximum extend. Finally, transformation was conducted on few variables to decrease the skewness of the data and to normalise the data. A normalised data is appropriate to conduct statistical analysis.

Data

• The data is taken from the United States department of transportation’s Bureau of Transportation Statistics.
• The dataset is obtained from the following link:https://www.kaggle.com/usdot/flight-delays#flights.csv
• Three datasets have been utilised in this assignment. The primary dataset is the aeroplane.csv. The other datasets are airport.csv and airlines.csv.
• By utilising the readr package, the datasets are imported and read. The datas are in csv format so read_csv function is made use of.These datasets are analysed and then combined by a common variable.The 3 datasets are joined by using left join.
• The common variable in aeroplane and airlines is the variable AIRLINE which has the airline name.And the airports dataset is combined by the ORIGIN_AIRPORT variable which has the unique airport id.The variables in the airlines dataset has been changed accordingly.

aeroplane<-read_csv("flight-delays/flights.csv")
airports<-read_csv("flight-delays/airports.csv")
airlines<-read_csv("flight-delays/airlines.csv")
#Changing of one of the column names
colnames(airlines)[colnames(airlines)=="AIRLINE"] <- "Plane_Name"
#Changing of one of the column names
colnames(airlines)[colnames(airlines)=="IATA_CODE"] <- "AIRLINE"
air<-aeroplane %>% left_join(airlines, by = "AIRLINE")
#Changing of one of the column names
colnames(airports)[colnames(airports)=="IATA_CODE"] <- "ORIGIN_AIRPORT"
Air_details<-air %>% left_join(airports, by = "ORIGIN_AIRPORT")
head(Air_details, n=10)

Understand

• Inspection of the dataset Air_details is conducted.The dimension is checked there are 5819079 observations and 38 rows.Structure of the dataset is checked and few variables are conveterd to factor.The dataset is checked for incomplete rows and it is dealt with later on. The month and week variable are replaced with their respective month and week name and then converted to factor.

head(Air_details)

dim(Air_details)

[1] 5819079      38

#Any incomplete rows
Air_details[!complete.cases(Air_details),]

Air_details$STATE<-as.factor(Air_details$STATE)
levels(Air_details$STATE)

 [1] "AK" "AL" "AR" "AS" "AZ" "CA" "CO" "CT" "DE" "FL" "GA" "GU" "HI" "IA" "ID" "IL" "IN" "KS" "KY" "LA" "MA" "MD" "ME" "MI"
[25] "MN" "MO" "MS" "MT" "NC" "ND" "NE" "NH" "NJ" "NM" "NV" "NY" "OH" "OK" "OR" "PA" "PR" "RI" "SC" "SD" "TN" "TX" "UT" "VA"
[49] "VI" "VT" "WA" "WI" "WV" "WY"

Air_details$ORIGIN_AIRPORT<-as.factor(Air_details$ORIGIN_AIRPORT)
is.factor(Air_details$ORIGIN_AIRPORT)

[1] TRUE

Air_details$DESTINATION_AIRPORT<-as.factor(Air_details$DESTINATION_AIRPORT)
is.factor(Air_details$DESTINATION_AIRPORT)

[1] TRUE

# Define numbers by actual days and months.
Air_details$DAY_OF_WEEK[Air_details$DAY_OF_WEEK == 1] <- 'Monday'
Air_details$DAY_OF_WEEK[Air_details$DAY_OF_WEEK == 2] <- 'Tuesday'
Air_details$DAY_OF_WEEK[Air_details$DAY_OF_WEEK == 3] <- 'Wednesday'
Air_details$DAY_OF_WEEK[Air_details$DAY_OF_WEEK == 4] <- 'Thursday'
Air_details$DAY_OF_WEEK[Air_details$DAY_OF_WEEK == 5] <- 'Friday'
Air_details$DAY_OF_WEEK[Air_details$DAY_OF_WEEK == 6] <- 'Saturday'
Air_details$DAY_OF_WEEK[Air_details$DAY_OF_WEEK == 7] <- 'Sunday'
Air_details$MONTH[Air_details$MONTH == 1] <- 'January'
Air_details$MONTH[Air_details$MONTH == 2] <- 'February'
Air_details$MONTH[Air_details$MONTH == 3] <- 'March'
Air_details$MONTH[Air_details$MONTH == 4] <- 'April'
Air_details$MONTH[Air_details$MONTH == 5] <- 'May'
Air_details$MONTH[Air_details$MONTH== 6] <- 'June'
Air_details$MONTH[Air_details$MONTH == 7] <-'July'
Air_details$MONTH[Air_details$MONTH == 8] <- 'August'
Air_details$MONTH[Air_details$MONTH == 9] <- 'September'
Air_details$MONTH[Air_details$MONTH == 10] <- 'October'
Air_details$MONTH[Air_details$MONTH == 11] <- 'November'
Air_details$MONTH[Air_details$MONTH == 12] <- 'December'
Air_details$DAY_OF_WEEK<-as.factor(Air_details$DAY_OF_WEEK)
levels(Air_details$DAY_OF_WEEK)

[1] "Friday"    "Monday"    "Saturday"  "Sunday"    "Thursday"  "Tuesday"   "Wednesday"

Air_details$MONTH<-as.factor(Air_details$MONTH)
levels(Air_details$MONTH)

 [1] "April"     "August"    "December"  "February"  "January"   "July"      "June"      "March"     "May"       "November" 
[11] "October"   "September"

Tidy & Manipulate Data I

The dataset choosen is already tidy. Here the unwanred variables are removed to increase the ease of anaysis.

#unwanted are removed
Air_details_tidy<- Air_details %>% 
  select(-DIVERTED,-`CANCELLED`,-`CANCELLATION_REASON`,-`AIR_SYSTEM_DELAY`,-SECURITY_DELAY,-AIRLINE_DELAY,-LATE_AIRCRAFT_DELAY,-WEATHER_DELAY)

Tidy & Manipulate Data II

In order to check the speed of aeroplane in km/hr.A mutate function is used to make a new variable that is the Speed_aeroplane by using Distance which is the distance between two airports and by using AIR_TIME.

Air_details_tidy<-mutate(Air_details_tidy,
        Speed_aeroplane = DISTANCE / (AIR_TIME/60))

Scan I

The total number of missing observations are found by using colSums function and the percentage of missing values of total observation is gotten. By using which and is.na function the location of the missing values is found out.The missing values based whether they are mean or catergorical variable are replaced by mean or mode of the respective variable.

colSums(is.na(Air_details_tidy))

               YEAR               MONTH                 DAY         DAY_OF_WEEK             AIRLINE       FLIGHT_NUMBER 
                  0                   0                   0                   0                   0                   0 
        TAIL_NUMBER      ORIGIN_AIRPORT DESTINATION_AIRPORT SCHEDULED_DEPARTURE      DEPARTURE_TIME     DEPARTURE_DELAY 
              14721                   0                   0                   0               86153               86153 
           TAXI_OUT          WHEELS_OFF      SCHEDULED_TIME        ELAPSED_TIME            AIR_TIME            DISTANCE 
              89047               89047                   6              105071              105071                   0 
          WHEELS_ON             TAXI_IN   SCHEDULED_ARRIVAL        ARRIVAL_TIME       ARRIVAL_DELAY          Plane_Name 
              92513               92513                   0               92513              105071                   0 
            AIRPORT                CITY               STATE             COUNTRY            LATITUDE           LONGITUDE 
             486165              486165              486165              486165              490770              490770 
    Speed_aeroplane 
             105071 

NA_DEPARTURE_DELAY<-which(is.na(Air_details_tidy$DEPARTURE_DELAY))
NA_ARRIVAL_DELAY<-which(is.na(Air_details_tidy$ARRIVAL_DELAY))
NA_TAILNUMBER<-which(is.na(Air_details_tidy$TAIL_NUMBER))
NA_AIR_TIME<-which(is.na(Air_details_tidy$AIR_TIME))
NA_Speed_aeroplane<-which(is.na(Air_details_tidy$Speed_aeroplane))
#Replace NA 
Air_details_tidy$DEPARTURE_DELAY[NA_DEPARTURE_DELAY]<-mean(Air_details_tidy$DEPARTURE_DELAY,na.rm = T)
Air_details_tidy$ARRIVAL_DELAY[NA_ARRIVAL_DELAY]<-mean(Air_details_tidy$ARRIVAL_DELAY,na.rm = T)
Air_details_tidy$AIR_TIME[NA_AIR_TIME]<-mean(Air_details_tidy$AIR_TIME,na.rm = T)
Air_details_tidy$Speed_aeroplane[NA_Speed_aeroplane]<-mean(Air_details_tidy$Speed_aeroplane,na.rm = T)
colSums(is.na(Air_details_tidy))

               YEAR               MONTH                 DAY         DAY_OF_WEEK             AIRLINE       FLIGHT_NUMBER 
                  0                   0                   0                   0                   0                   0 
        TAIL_NUMBER      ORIGIN_AIRPORT DESTINATION_AIRPORT SCHEDULED_DEPARTURE      DEPARTURE_TIME     DEPARTURE_DELAY 
              14721                   0                   0                   0               86153                   0 
           TAXI_OUT          WHEELS_OFF      SCHEDULED_TIME        ELAPSED_TIME            AIR_TIME            DISTANCE 
              89047               89047                   6              105071                   0                   0 
          WHEELS_ON             TAXI_IN   SCHEDULED_ARRIVAL        ARRIVAL_TIME       ARRIVAL_DELAY          Plane_Name 
              92513               92513                   0               92513                   0                   0 
            AIRPORT                CITY               STATE             COUNTRY            LATITUDE           LONGITUDE 
             486165              486165              486165              486165              490770              490770 
    Speed_aeroplane 
                  0 

Scan II

• The boxplot is utilised to scan the variables for the outliers.The z-score method is used but it is rather ineffective in removing the outliers.While z-score is calculated rescaling and centering of data takes place and data points away from zero are noticed.The points which are away from zero are outliers.A threshold of 3 is utilised it means if the value of z-score is greter than 3 taht data point is considered to be outliers.
• So the capping method is utilised to remove the outliers.In this methodology, the lower and upper percentile is utilised to replace the outliers.We used replace by median, replacing by 1st quad and replacing by 3r quad.
• In replace by median function, the upper and lower percentile limit is set to 25 and 75%.But it is not affective in this case.
• In the replacing by 1st quad function, it is affective to a certain extend.
• In the replacing by 3r quad function, the lower and upper percentile limit has been extended to 5 and 95% and it more effective than the other functions.

#z-score method
# Calculate z-score
z.scores.AIR_TIME <- Air_details_tidy$AIR_TIME %>%  scores(type = "z")
z.scores.DISTANCE <- Air_details_tidy$DISTANCE %>%  scores(type = "z")
clean_AIR_TIME <- Air_details_tidy[- which(abs(z.scores.AIR_TIME) >3 ),]
clean_DISTANCE<- Air_details_tidy[- which(abs(z.scores.DISTANCE) >3 ),]
par(mfrow=c(1,4))
boxplot(Air_details_tidy$AIR_TIME, main = "Box Plot of AIR_TIME",verticle=TRUE, col = "pink")
boxplot(Air_details_tidy$DISTANCE, main = "Box Plot of DISTANCE",verticle=TRUE, col = "pink")

clean_AIR_TIME$AIR_TIME %>%  boxplot(main="Zscore Box Plot of AIR_TIME",
                                       ylab="AIR_TIME($)", col = "pink")
clean_DISTANCE$DISTANCE %>%  boxplot(main="zscore Box Plot of DISTANCE",
                                       ylab="DISTANCE($)", col = "pink")

#Median method air time
Replace_by_median <- function(z) {
 quantiles <- quantile( z, c( 0.25, 0.50, .75 ) )
 z[ z < quantiles[1] - 1.5*IQR(z) ] <- quantiles[2]
 z[ z > quantiles[3] + 1.5*IQR(z) ] <- quantiles[2]
 z}
Replacing_by_1st_quad<- function(z) {
 quantiles <- quantile( z, c( 0.25, 0.50, .75 ) )
 z[ z < quantiles[1] - 1.5*IQR(z) ] <- quantiles[1]
 z[ z > quantiles[3] + 1.5*IQR(z) ] <- quantiles[1]
 z}
Replacing_by_3rd_quad<- function(z) {
 quantiles <- quantile( z, c( 0.05,0.25, 0.75, .95 ) )
 z[ z < quantiles[1] - 1.5*IQR(z) ] <- quantiles[3]
 z[ z > quantiles[3] + 1.5*IQR(z) ] <- quantiles[3]
 z}
par(mfrow=c(1,4))

boxplot(Air_details_tidy$AIR_TIME,main="Before Outlier Removal  ", col= "pink")
After_Cap<-Air_details_tidy$AIR_TIME %>% Replace_by_median()
boxplot(After_Cap,main="AIR_TIME by Median", col= "pink")

After_Cap_1<-Air_details_tidy$AIR_TIME %>% Replacing_by_1st_quad()
boxplot(After_Cap_1,main="AIR_TIME by 1st Qu",col= "red")
After_Cap_2<-Air_details_tidy$AIR_TIME %>% Replacing_by_3rd_quad()
boxplot(After_Cap_2,main="AIR_TIME by 3rd Qu",col= "yellow")

#Median method dustance
Replacing_by_median <- function(z) {
 quantiles <- quantile( z, c( 0.25, 0.50, .75 ) )
 z[ z < quantiles[1] - 1.5*IQR(z) ] <- quantiles[2]
 z[ z > quantiles[3] + 1.5*IQR(z) ] <- quantiles[2]
 z}
Replacing_by_1st<- function(z) {
 quantiles <- quantile( z, c( 0.25, 0.50, .75 ) )
 z[ z < quantiles[1] - 1.5*IQR(z) ] <- quantiles[1]
 z[ z > quantiles[3] + 1.5*IQR(z) ] <- quantiles[1]
 z}

Replacing_by_3rd<- function(z) {
 quantiles <- quantile( z, c( 0.05,0.25, 0.75, .95 ) )
 z[ z < quantiles[1] - 1.5*IQR(z) ] <- quantiles[3]
 z[ z > quantiles[3] + 1.5*IQR(z) ] <- quantiles[3]
 z}
par(mfrow=c(1,4))

boxplot(Air_details_tidy$DISTANCE,main="Before Outlier Removal ", col= "red")
After_Cap<-Air_details_tidy$DISTANCE %>% Replacing_by_median()
boxplot(After_Cap,main="DISTANCE by Median", col= "red")

After_Cap_1<-Air_details_tidy$DISTANCE %>% Replacing_by_1st()
boxplot(After_Cap_1,main="DISTANCE by 1st Qudr",col= "blue")
After_Cap_2<-Air_details_tidy$DISTANCE %>% Replacing_by_3rd()
boxplot(After_Cap_2,main="DISTANCE by 3rd Qu",col= "pink")

Transform

The transformation has been conducted on AIR_TIME and DISTANCE variables. The aim is to decrease the skewness and convert it to normalised data.The log transformation has been used, it spreads out the congested data.Log transformation is used for right skewed.A normal distribution of the data is achieved by this method.

par(mfrow=c(1,4))
hist(Air_details_tidy$AIR_TIME,main="AIR_TIME Before Transformation",col = "green")
Tair<-log10(Air_details_tidy$AIR_TIME)
hist(Tair,main="AIR_TIME After Transformation",col = "green")

hist(Air_details_tidy$DISTANCE,main="DISTANCE Before Transformation",col = "pink")
T_distance<-log10(Air_details_tidy$DISTANCE)
hist(T_distance,main="DISTANCE After Transformation",col = "pink")