Data Transformations & Manipulation with dplyr

Let’s load some packages to begin with

library(tidyverse)
library(skimr)
library(scales)
library(flextable)
library(mice)
library(naniar)

Next we import the data set on Michigan flights

# This data set is in my computer storage unfortunately
miFlights <- read_csv("miFlights2019-2021.csv")

Let’s Use the skim() function to explore characteristics of the data set.

skim(miFlights)

Variable type: character

Variable type: numeric

Variable type: POSIXct

Create a bar chart showing how many flights departed out of each airport (origin) using the count() and geom_col() functions. Also sort the bars by descending height using the fct_reorder() function.

miFlights |>
  count(origin) |>
  ggplot(aes(x = fct_reorder(origin, n, .desc = TRUE),
             y = n, fill = origin)) +
  scale_fill_viridis_d() + 
  scale_y_continuous(labels = label_comma(),
                     expand = expansion(mult = c(0, 0.1))) +
  geom_col(color='black') +
labs(title = "Michigan Flights, 2019-2021",
     y = "Number of Flights",
     x = "Origin Airport",
     caption = "Data source: anyflights R package") +
  ggthemes::theme_few() +
  theme(legend.position = "none")

Reproduce the same bar chart using geom_bar(). Hint: one way to sort the bars by height with geom_bar()`` is to use thefct_infreq()` function.

miFlights |>
  ggplot(aes(x = fct_infreq(origin,ordered = TRUE), fill = origin)) +
  geom_bar(color = 'black') +
  scale_fill_viridis_d() + 
  scale_y_continuous(labels = label_comma()) +
  # geom_col(color='black') +
labs(title = "Michigan Flights, 2019-2021",
     y = "Number of Flights",
     x = "Origin Airport",
     caption = "Data source: anyflights R package") +
  ggthemes::theme_few() +
  theme(legend.position = "none")

miFlights |>
  ggplot(aes(x = fct_infreq(origin), fill = origin)) +
  scale_fill_viridis_d() + 
  scale_y_continuous(labels = label_comma(),
                     expand = expansion(mult = c(0, 0.1))) +
  geom_bar(color='black') +
labs(title = "Michigan Flights, 2019-2021",
     y = "Number of Flights",
     x = "Origin Airport",
     caption = "Data source: anyflights R package") +
  ggthemes::theme_few() +
  theme(legend.position = "none")

Selecting all flights on January 1st in the data set, create a new object called janFlights.

# Subsetting to flights that departed in January 1st
janFlights <- miFlights |>
  dplyr::filter(month == 1, day == 1)

Suppose we want to create a data set called dec25 that contains flight data from December 25th. What code would we need using the filter() function to create dec25?

# Christmas day flights
dec25 <- miFlights |>
  dplyr::filter(month == 12 & day == 25)

Find all flights that departed in November or December, creating an object called novDec.

# Flights in December or November
novDec <- miFlights |>
  dplyr::filter(month == 12 | month == 11)

# or

notNovDec <- miFlights |>
  dplyr::filter(month %in% 11:12 )

Find all flights that departed in summer break.

# Flights in summer break
summer_break_flights <- miFlights |>
  dplyr::filter(month %in% 5:8)

Select all flights except those in the months of November or December using !.

# Flights not in Nov and not in Dec
notNovDec <- miFlights |>
  dplyr::filter(month !=12 & month != 11)

Knowing that arr_delay and dep_delay represent the arrival and departure delays in minutes respectively, what data set is produced using the code below?

dplyr::filter(miFlights, !(arr_delay > 120 | dep_delay > 120))

## # A tibble: 444,025 × 37
##     year month   day dep_time sched_dep_time dep_delay arr_time sched_arr_time
##    <dbl> <dbl> <dbl>    <dbl>          <dbl>     <dbl>    <dbl>          <dbl>
##  1  2019     1     1      455            500        -5      830            834
##  2  2019     1     1      506            511        -5      710            730
##  3  2019     1     1      531            535        -4      647            710
##  4  2019     1     1      534            545       -11      750            742
##  5  2019     1     1      550            600       -10      712            748
##  6  2019     1     1      555            600        -5      822            834
##  7  2019     1     1      555            555         0      709            715
##  8  2019     1     1      555            600        -5      755            817
##  9  2019     1     1      600            600         0      559            615
## 10  2019     1     1      601            603        -2      927            924
## # ℹ 444,015 more rows
## # ℹ 29 more variables: arr_delay <dbl>, carrier <chr>, flight <dbl>,
## #   tailnum <chr>, origin <chr>, dest <chr>, air_time <dbl>, distance <dbl>,
## #   hour <dbl>, minute <dbl>, time_hour <dttm>, temp <dbl>, dewp <dbl>,
## #   humid <dbl>, wind_dir <dbl>, wind_speed <dbl>, wind_gust <dbl>,
## #   precip <dbl>, pressure <dbl>, visib <dbl>, carrier_name <chr>,
## #   plane_year <dbl>, plane_type <chr>, plane_manufacturer <chr>, …

dplyr::filter(miFlights, arr_delay <= 120, dep_delay <= 120)

## # A tibble: 444,025 × 37
##     year month   day dep_time sched_dep_time dep_delay arr_time sched_arr_time
##    <dbl> <dbl> <dbl>    <dbl>          <dbl>     <dbl>    <dbl>          <dbl>
##  1  2019     1     1      455            500        -5      830            834
##  2  2019     1     1      506            511        -5      710            730
##  3  2019     1     1      531            535        -4      647            710
##  4  2019     1     1      534            545       -11      750            742
##  5  2019     1     1      550            600       -10      712            748
##  6  2019     1     1      555            600        -5      822            834
##  7  2019     1     1      555            555         0      709            715
##  8  2019     1     1      555            600        -5      755            817
##  9  2019     1     1      600            600         0      559            615
## 10  2019     1     1      601            603        -2      927            924
## # ℹ 444,015 more rows
## # ℹ 29 more variables: arr_delay <dbl>, carrier <chr>, flight <dbl>,
## #   tailnum <chr>, origin <chr>, dest <chr>, air_time <dbl>, distance <dbl>,
## #   hour <dbl>, minute <dbl>, time_hour <dttm>, temp <dbl>, dewp <dbl>,
## #   humid <dbl>, wind_dir <dbl>, wind_speed <dbl>, wind_gust <dbl>,
## #   precip <dbl>, pressure <dbl>, visib <dbl>, carrier_name <chr>,
## #   plane_year <dbl>, plane_type <chr>, plane_manufacturer <chr>, …

Create a new object called miFlightsComplete where all departure times are non-missing, and miFlightsMiss where all departure times are missing

# Rows with non-missing departure time
miFlightsComplete <- miFlights |>
  dplyr::filter(is.na(dep_time) == FALSE)

# Rows with missing departure time
miFlightsMiss <- miFlights |>
  dplyr::filter(is.na(dep_time) == TRUE)

Sort miFlights by the day of the flight (smallest to largest), and print the first 4 columns and 5 rows of the resulting data set using the slice_head() function.

library(gt)
miFlights |>
  arrange(day) |>
  dplyr::select(1:4) |>
  slice_head(n = 5) |>
  gt()

Sort miFlights by the day of the flight (largest to smallest), and print the first 4 columns and 5 rows of the resulting data set using the slice_head() function.

library(gt)
miFlights |>
  arrange(desc(day)) |>
  dplyr::select(1:4) |>
  slice_head(n = 5) |>
  gt()

Sort miFlights by the year, month, and day of the flight.

library(gt)
miFlights |>
  arrange(year, month, day) |>
  dplyr::select(1:4) |>
  slice_head(n = 5) |>
  gt()

# for desc

miFlights |>
  arrange(year, desc(month), day) |>
  dplyr::select(1:4) |>
  slice_head(n = 5) |>
  gt()

Sort miFlights to find the 3 most delayed flights (arr_delay), and the 3 that left the earliest relative to their scheduled departure (dep_delay).

# 3 most delayed flights

miFlights |>
  arrange(desc(arr_delay)) |>
  # dplyr::select(1:8) |>
  slice_head(n = 3) |>
  gt()

# 3 that left the earliest

miFlights |>
  arrange(dep_delay) |>
  # dplyr::select(1:8) |>
  slice_head(n = 3) |>
  gt()

Sort miFlights to find the fastest (highest speed) flights using a function of the variables distance and air_time.

# Fastest flight

miFlights |>
  arrange(desc(distance / air_time)) |>
  slice_head(n = 2) |>
  gt()

For flights coming out of GRR, find the 3 flights that traveled the farthest (distance) and that arrived the earliest in the morning (arr_time) simultaneously.

miFlights |>
  filter(origin == "GRR") |>
  arrange(desc(distance), arr_time) |>
  slice_head(n = 3)

## # A tibble: 3 × 37
##    year month   day dep_time sched_dep_time dep_delay arr_time sched_arr_time
##   <dbl> <dbl> <dbl>    <dbl>          <dbl>     <dbl>    <dbl>          <dbl>
## 1  2021     3    28      555            600        -5      714            758
## 2  2021     4    11      552            600        -8      716            740
## 3  2021     3    18      558            600        -2      720            758
## # ℹ 29 more variables: arr_delay <dbl>, carrier <chr>, flight <dbl>,
## #   tailnum <chr>, origin <chr>, dest <chr>, air_time <dbl>, distance <dbl>,
## #   hour <dbl>, minute <dbl>, time_hour <dttm>, temp <dbl>, dewp <dbl>,
## #   humid <dbl>, wind_dir <dbl>, wind_speed <dbl>, wind_gust <dbl>,
## #   precip <dbl>, pressure <dbl>, visib <dbl>, carrier_name <chr>,
## #   plane_year <dbl>, plane_type <chr>, plane_manufacturer <chr>,
## #   plane_model <chr>, plane_engines <dbl>, plane_seats <dbl>, …

Drop the year and month columns from miFlights creating a new data set called miDropped.

# Dropping year and month
miDropped <- miFlights |>
  dplyr::select(-year, -month)

We can also drop sets of contiguous or touching columns:

Drop all variables between year and day columns (inclusive) from miFlights creating a new data set called miDropped2.

miDropped2 <- miFlights |>
  dplyr::select(-c(year:day))

Another option is to use select() in tandem with the everything() helper.

miFlights |>
  dplyr::select(time_hour, air_time, everything()) |>
  slice_head(n = 5)

## # A tibble: 5 × 37
##   time_hour           air_time  year month   day dep_time sched_dep_time
##   <dttm>                 <dbl> <dbl> <dbl> <dbl>    <dbl>          <dbl>
## 1 2019-01-01 21:00:00       88  2019     1     1       55           2115
## 2 2019-01-01 05:00:00      187  2019     1     1      455            500
## 3 2019-01-01 05:00:00      162  2019     1     1      506            511
## 4 2019-01-01 05:00:00       64  2019     1     1      531            535
## 5 2019-01-01 05:00:00       71  2019     1     1      534            545
## # ℹ 30 more variables: dep_delay <dbl>, arr_time <dbl>, sched_arr_time <dbl>,
## #   arr_delay <dbl>, carrier <chr>, flight <dbl>, tailnum <chr>, origin <chr>,
## #   dest <chr>, distance <dbl>, hour <dbl>, minute <dbl>, temp <dbl>,
## #   dewp <dbl>, humid <dbl>, wind_dir <dbl>, wind_speed <dbl>, wind_gust <dbl>,
## #   precip <dbl>, pressure <dbl>, visib <dbl>, carrier_name <chr>,
## #   plane_year <dbl>, plane_type <chr>, plane_manufacturer <chr>,
## #   plane_model <chr>, plane_engines <dbl>, plane_seats <dbl>, …

We can use select() and everything() to rearrange columns and still drop columns too:

miFlights |>
  dplyr::select(time_hour, air_time, everything(), -day) |>
  slice_head(n=5)

## # A tibble: 5 × 36
##   time_hour           air_time  year month dep_time sched_dep_time dep_delay
##   <dttm>                 <dbl> <dbl> <dbl>    <dbl>          <dbl>     <dbl>
## 1 2019-01-01 21:00:00       88  2019     1       55           2115       220
## 2 2019-01-01 05:00:00      187  2019     1      455            500        -5
## 3 2019-01-01 05:00:00      162  2019     1      506            511        -5
## 4 2019-01-01 05:00:00       64  2019     1      531            535        -4
## 5 2019-01-01 05:00:00       71  2019     1      534            545       -11
## # ℹ 29 more variables: arr_time <dbl>, sched_arr_time <dbl>, arr_delay <dbl>,
## #   carrier <chr>, flight <dbl>, tailnum <chr>, origin <chr>, dest <chr>,
## #   distance <dbl>, hour <dbl>, minute <dbl>, temp <dbl>, dewp <dbl>,
## #   humid <dbl>, wind_dir <dbl>, wind_speed <dbl>, wind_gust <dbl>,
## #   precip <dbl>, pressure <dbl>, visib <dbl>, carrier_name <chr>,
## #   plane_year <dbl>, plane_type <chr>, plane_manufacturer <chr>,
## #   plane_model <chr>, plane_engines <dbl>, plane_seats <dbl>, …

Create a subset of the miFlights data set called timeFlights that only contains variables that end with the word “time”.

timeFlights <- miFlights |>
  dplyr::select(ends_with("time")) |>
  slice_head(n = 5)

Create a new data frame called departureInfo that only has variables that start with “dep”

departureInfo <- miFlights |>
  dplyr::select(starts_with("dep")) |>
  slice_head(n = 5)

Create a new data frame called newFlights by rearranging the columns of the full miFlights data set so that flight number (flight), origin (origin), and destination (dest) are provided first, then all other columns except the tail number (tailnum).

newFlights <- miFlights |>
  dplyr::select(flight, origin, dest, everything(), -tailnum) |>
  slice_head( n=5)

We can create a new variable, gain, that is a function of existing variables in miFlights:

flights_sml <- miFlights |> dplyr::select(ends_with("delay"), distance, air_time)

# Creating time gained variable
flights_sml |> mutate(gain = dep_delay - arr_delay) |> 
  slice_head(n = 5)

## # A tibble: 5 × 5
##   dep_delay arr_delay distance air_time  gain
##       <dbl>     <dbl>    <dbl>    <dbl> <dbl>
## 1       220       303      500       88   -83
## 2        -5        -4     1214      187    -1
## 3        -5       -20      986      162    15
## 4        -4       -23      409       64    19
## 5       -11         8      632       71   -19

Extending the code provided with a single call to mutate(), create a new variable, speed, that is equal to distance divided by air_time, producing a new data set called flightSpeeds.

# Culculating speed variable
flightSpeeds <- flights_sml |>
  mutate(gain = dep_delay - arr_delay,
         speed_mph = distance / air_time*60)

Re-order the bars height based on Delay, add colorand add theme

# Calculating average flight delay by carrier
delaySummary <- miFlights |> group_by(carrier_name) |> 
  group_by(carrier_name) |>
  summarize(Delay = mean(arr_delay, na.rm = TRUE))



# Creating waterfall chart
delaySummary |>
  ggplot(aes(x = fct_reorder(carrier_name, Delay, .desc = FALSE),
             y = Delay,
             fill = Delay > 0)) +
  geom_col() +
    scale_fill_manual(values = c("#D55E00", "#0072B2")) +
  labs(title =  "Average Flight Delays per Carrrier",
       subtitle = "Michigan Flights, 2019-2021",
       x = "Carrier",
       y = "Delay(Minutes)",
       caption ="Data source: anyflights R package") +
  theme_bw(base_size = 14) +
  theme(axis.text.x = element_text(angle = 45, hjust = 1),
        legend.position = "none",
        title = element_text(face = "bold"))

Create a data frame summarizing the median flight delay (arr_delay) by month. Which month has the worst delays? In which month are flights most early / on-time?

# Median flight delay by month and get one with worst delays
worst_delay_month <- miFlights |>
  group_by(month) |>
  summarize(median_arr_delay = median(arr_delay, na.rm = TRUE)) |>
  arrange(desc(median_arr_delay)) |>
  slice(1)

June has worst delays

# Month with most on-time flights
on_time_month <- miFlights |>
  group_by(month) |>
  summarize(median_arr_delay = median(arr_delay, na.rm = TRUE)) |>
  arrange(median_arr_delay) |>
  slice(1)

January has the most on-time flights

Which type of plot would be most useful for displaying the typical delay each month? Creating and viewing this plot, are there any apparent trends?

Line plot

miFlights |>
  group_by(month) |>
  summarize(median_arr_delay = median(arr_delay, na.rm = TRUE)) |>
  ggplot(aes(x = month, y = median_arr_delay)) +
  geom_line() +
  scale_x_continuous(breaks = 1:12, labels = month.abb) +
  labs(title = "Median Arrival Delay by Month",
       x = "Month", 
       y = "Median Arrival Delay")

There are trends. The Median arrival delay increases from Jan to June and then starts decreasing

Reproduce the visualization below using the facet argument with gg_miss_var().

miFlights |>
  gg_miss_var(show_pct = TRUE,
              facet = origin)

Another way to visualize this missingness is using the gg_miss_fct() function from the naniar package. Recreate the mosaic plot showing missingness for each variable depending on the origin airport using the gg_miss_fct() function.

miFlights |>
  dplyr::select(origin, dest, carrier,
                contains(c("time", "delay"))) |>
  gg_miss_fct(fct = origin)

Use the gg_miss_upset() function to visualize the joint missingness of some variables related to the weather.

miFlights |>
  gg_miss_upset()

Use the gg_miss_fct() function from the naniar package to visualize missingness for each variable depending on the carrier.

miFlights |>
  gg_miss_fct(fct = carrier_name)

Use the gg_miss_upset() function to visualize the joint missingness of variables related to delays by selecting the variables dep_time through arr_delay.

miFlights |>
  dplyr::select(dep_time:arr_delay) |>
  gg_miss_upset()