Assignment 5A

Introduction

In this assignment, I will be using a dataset that contains information on arrival delays for two airlines across five destinations, and performing analysis to compare the arrival delays of the two airlines. This will require reading the dataset (which is in CSV file format), tidying and transforming the data, and performing the analysis, all using R. I expect the biggest challenge of this assignment being the data tidying and transforming, as it will need to be resturctured for the analysis.

Body

To start, I load in all of the necessary libraries:

library(tidyverse)
library(knitr)
library(janitor)

I then have to read the CSV from my github repository and create the dataframe:

url <- "https://raw.githubusercontent.com/stevenmacaluso/DATA607-Assignment5A/refs/heads/main/data.csv"
data <- read.csv(url)

kable(data)

X	X.1	Los.Angeles	Phoenix	San.Diego	San.Fransisco	Seattle
ALASKA	on time	497	221	212	503	1841
	delayed	62	12	20	102	305
		NA	NA	NA	NA	NA
AM WEST	on time	694	4840	383	320	201
	delayed	117	415	65	129	61

Now that the dataframe is created, it is time to do some data cleaning. First, I’ll add headers to the columns that do not have headers:

names(data)[1] <- "Airlines"
names(data)[2] <- "Status"

kable(data)

Airlines	Status	Los.Angeles	Phoenix	San.Diego	San.Fransisco	Seattle
ALASKA	on time	497	221	212	503	1841
	delayed	62	12	20	102	305
		NA	NA	NA	NA	NA
AM WEST	on time	694	4840	383	320	201
	delayed	117	415	65	129	61

Next, I will make sure all empty strings are set to NA. This will be necessary for the next step of the data cleaning process:

data[data == ""] <- NA

kable(data)

Airlines	Status	Los.Angeles	Phoenix	San.Diego	San.Fransisco	Seattle
ALASKA	on time	497	221	212	503	1841
NA	delayed	62	12	20	102	305
NA	NA	NA	NA	NA	NA	NA
AM WEST	on time	694	4840	383	320	201
NA	delayed	117	415	65	129	61

Now that this is done, I can remove the blank row in the middle of the dataframe:

data <- remove_empty(data, which = "rows")

kable(data)

	Airlines	Status	Los.Angeles	Phoenix	San.Diego	San.Fransisco	Seattle
1	ALASKA	on time	497	221	212	503	1841
2	NA	delayed	62	12	20	102	305
4	AM WEST	on time	694	4840	383	320	201
5	NA	delayed	117	415	65	129	61

After this step, the only empty values are those under “Airlines.” Knowing the structure of the original data is important here, because I know that these empty values should be changed to whatever is above it. I use the following code to make this change:

for(i in 2:nrow(data)) {
  
  if(is.na(data$Airlines[i])){
    data$Airlines[i] <- data$Airlines[i-1]
  }
}

kable(data)

	Airlines	Status	Los.Angeles	Phoenix	San.Diego	San.Fransisco	Seattle
1	ALASKA	on time	497	221	212	503	1841
2	ALASKA	delayed	62	12	20	102	305
4	AM WEST	on time	694	4840	383	320	201
5	AM WEST	delayed	117	415	65	129	61

Now it is time to start transforming the data. First, I transfer this data out of wide format by using tidyr::gather:

data_tf <- tidyr::gather(data, "City", "Count", 3:7)

kable(data_tf)

Airlines	Status	City	Count
ALASKA	on time	Los.Angeles	497
ALASKA	delayed	Los.Angeles	62
AM WEST	on time	Los.Angeles	694
AM WEST	delayed	Los.Angeles	117
ALASKA	on time	Phoenix	221
ALASKA	delayed	Phoenix	12
AM WEST	on time	Phoenix	4840
AM WEST	delayed	Phoenix	415
ALASKA	on time	San.Diego	212
ALASKA	delayed	San.Diego	20
AM WEST	on time	San.Diego	383
AM WEST	delayed	San.Diego	65
ALASKA	on time	San.Fransisco	503
ALASKA	delayed	San.Fransisco	102
AM WEST	on time	San.Fransisco	320
AM WEST	delayed	San.Fransisco	129
ALASKA	on time	Seattle	1841
ALASKA	delayed	Seattle	305
AM WEST	on time	Seattle	201
AM WEST	delayed	Seattle	61

Next, I will use tidyr::spread to group data by airline and city. This will create a unique value for each row that displays the combination of delayed and on-time arrivals by city and airline:

data_tf2 <- tidyr::spread(data_tf, Status, Count)

kable(data_tf2)

Airlines	City	delayed	on time
ALASKA	Los.Angeles	62	497
ALASKA	Phoenix	12	221
ALASKA	San.Diego	20	212
ALASKA	San.Fransisco	102	503
ALASKA	Seattle	305	1841
AM WEST	Los.Angeles	117	694
AM WEST	Phoenix	415	4840
AM WEST	San.Diego	65	383
AM WEST	San.Fransisco	129	320
AM WEST	Seattle	61	201

Now I can start the analysis. First, I’ll start by making a graph displaying the number of delays by location:

ggplot(data_tf2) + geom_bar(mapping = aes(x = City, y = delayed, fill = Airlines), stat = "identity") +
  labs( title = "Number of Delays by Location", y = "Count")

Next, I’ll do the same for on-time arrivals:

ggplot(data_tf2) + geom_bar(mapping = aes(x = City, y = `on time`, fill = Airlines), stat = "identity") +
  labs( title = "Number of On-Time Arrivals by Location", y = "Count")

Finally, we’ll take a closer look at delayed arrivals, as I feel this is a more important metric for analyzing success / failure. The following code will create a summary displaying the maximum, minimum, mean, median, standard deviation, and interquartile range for arrival delays.

d_sum <- data_tf2 %>% dplyr::group_by(Airlines) %>% 
  dplyr::summarise(max=max(delayed), 
                   min=min(delayed),
                   mean=mean(delayed),
                   sd=sd(delayed),
                   median=median(delayed),
                   IQR=IQR(delayed))
kable(d_sum)

Airlines	max	min	mean	sd	median	IQR
ALASKA	305	12	100.2	120.0175	62	82
AM WEST	415	61	157.4	147.1625	117	64

Conclusion

To conclude, this assignment showcases how to tidy and transform data in R for the purpose of analysis. When analyzing data, it is crucial to ensure the data is in a use-able format. Many times, data is organized in a certain way for readability purposes, but many times that format is not useful for the purpose of data analysis in R. Further analysis on this data can be done to build upon this work, or more data from different airlines and cities can be added.