Elements of Data Science
SDS 322E

H. Sherry Zhang
Department of Statistics and Data Sciences
University of Texas at Austin

Get the repository for today:
library(usethis)
create_from_github("SDS322E-26FALL/0501-join", fork = FALSE)

Learning objectives

Join multiple data frames together for exploratory data analysis
- joins: left_join(), right_join(), inner_join(), full_join(), semi_join(), and anti_join()
- revision: filter(), mutate(), %in%
- new: fct_recode(), tibble()

Application 1a: distribution of air time of flights

How does the distribution of air time vary by carrier?

flights |> 
  # we looked in the air_time vs. distance plot
  # before the there are long flights 
  # thqt go to HNL (Hawaii)
  filter(dest != "HNL") |> 
  filter(carrier %in% c("AA", "DL", "UA")) |> 
  ggplot(aes(x = air_time)) + 
  geom_histogram(binwidth = 10)  + 
  facet_wrap(vars(carrier), ncol = 1)

Maybe I want a better facet header than “AA”, “DL”, and “UA”. 🤔

Application 1a: use fct_recode to recode carrier names

I didn’t give you can example on fct_recode(), but here you go.

flights |> 
  # we looked in the air_time vs. distance plot
  # before the there are long flights 
  # thqt go to HNL (Hawaii)
  filter(dest != "HNL") |> 
  filter(carrier %in% c("AA", "DL", "UA")) |> 
  mutate(carrier = fct_recode(
    carrier, 
    "American Airlines" = "AA", 
    "Delta Air Lines" = "DL", 
    "United Air Lines" = "UA")) |> 
  ggplot(aes(x = air_time)) + 
  geom_histogram(binwidth = 10)  + 
  facet_wrap(vars(carrier), ncol = 1)

Application 1b: distribution of air time of flights

When we have more carriers, manually recoding all the airlines can be tedious. 🤔

our_carriers = c("AA", "DL", "WN",
                 "MQ", "UA", "9E")
flights |> 
  filter(dest != "HNL") |> 
  filter(carrier %in% our_carriers) |> 
  ggplot(aes(x = air_time)) + 
  geom_histogram(binwidth = 10)  + 
  facet_wrap(vars(carrier), ncol = 1)

The full flight data

The airline data

head(airlines, 5)

# A tibble: 5 × 2
  carrier name                  
  <chr>   <chr>                 
1 9E      Endeavor Air Inc.     
2 AA      American Airlines Inc.
3 AS      Alaska Airlines Inc.  
4 B6      JetBlue Airways       
5 DL      Delta Air Lines Inc.

head(flights, 5)

# A tibble: 5 × 19
   year month   day dep_time sched_dep_time dep_delay arr_time sched_arr_time
  <int> <int> <int>    <int>          <int>     <dbl>    <int>          <int>
1  2013     1     1      517            515         2      830            819
2  2013     1     1      533            529         4      850            830
3  2013     1     1      542            540         2      923            850
4  2013     1     1      544            545        -1     1004           1022
5  2013     1     1      554            600        -6      812            837
  arr_delay carrier flight tailnum origin dest  air_time distance  hour minute
      <dbl> <chr>    <int> <chr>   <chr>  <chr>    <dbl>    <dbl> <dbl>  <dbl>
1        11 UA        1545 N14228  EWR    IAH        227     1400     5     15
2        20 UA        1714 N24211  LGA    IAH        227     1416     5     29
3        33 AA        1141 N619AA  JFK    MIA        160     1089     5     40
4       -18 B6         725 N804JB  JFK    BQN        183     1576     5     45
5       -25 DL         461 N668DN  LGA    ATL        116      762     6      0
# ℹ 1 more variable: time_hour <dttm>

Application 1b: use left_join to recode carrier names

df <- flights |> 
  filter(dest != "HNL") |> 
  filter(carrier %in% our_carriers) |> 
  left_join(airlines)

colnames(df)

 [1] "year"           "month"          "day"            "dep_time"      
 [5] "sched_dep_time" "dep_delay"      "arr_time"       "sched_arr_time"
 [9] "arr_delay"      "carrier"        "flight"         "tailnum"       
[13] "origin"         "dest"           "air_time"       "distance"      
[17] "hour"           "minute"         "time_hour"      "name"

Application 1b: use left_join to recode carrier names

our_carriers = c("AA", "DL", "WN",
                 "MQ", "UA", "9E")
df <- flights |> 
  filter(dest != "HNL") |> 
  filter(carrier %in% our_carriers) |> 
  left_join(airlines)

df |> 
  ggplot(aes(x = air_time)) + 
  geom_histogram(binwidth = 10)  + 
  facet_wrap(vars(name), ncol = 1)

c.f. Previously we have

facet_wrap(vars(carrier), ncol = 1)

What if I want the airline to come in the order of main stream airlines (AA, DL, UA) first and then regionals (MQ, WN, 9E)?

Different types of join

Mutate join

Left join: left_join()
Right join: right_join()
Inner join: inner_join()
Full join: full_join()

Filter join

Semi join: semi_join()
Anti join: anti_join()

Left join

A left join keeps all observations in data frame x. Every row of x is preserved in the output and NA is used if there is no matching value in data frame y.

Left join syntax (1/2)

left_join(DATA_X, DATA_Y, by = "SHARED_KEY")

df1 <- tibble(
  x = c(1, 2, 3), 
  y = c("a", "b", "c")
  )
df1

# A tibble: 3 × 2
      x y    
  <dbl> <chr>
1     1 a    
2     2 b    
3     3 c

df2 <- tibble(
  x = c(1, 2, 4),
  z = c("d", "e", "f")
  )
df2

# A tibble: 3 × 2
      x z    
  <dbl> <chr>
1     1 d    
2     2 e    
3     4 f

left_join(df1, df2, by = "x")

# A tibble: 3 × 3
      x y     z    
  <dbl> <chr> <chr>
1     1 a     d    
2     2 b     e    
3     3 c     <NA>

Other variations:

# you can use pipe
df1 |> left_join(df2, by = "x")

# auto-detect if the key variable has the
# same name 
df1 |> left_join(df2)

Left join syntax (2/2)

When the key variables have different names in the two data frames, you need to specify the names of the key variables in both data frames.

left_join(DATA_X, DATA_Y, by = c("KEY_IN_X" = "KEY_IN_Y"))

df3 <- tibble(
  x1 = c(1, 2, 3), 
  y = c("a", "b", "c")
  )
df3

# A tibble: 3 × 2
     x1 y    
  <dbl> <chr>
1     1 a    
2     2 b    
3     3 c

df4 <- tibble(
  x2 = c(1, 2, 4), 
  z = c("d", "e", "f")
  )
df4

# A tibble: 3 × 2
     x2 z    
  <dbl> <chr>
1     1 d    
2     2 e    
3     4 f

left_join(df3, df4, by = c("x1" = "x2"))

# A tibble: 3 × 3
     x1 y     z    
  <dbl> <chr> <chr>
1     1 a     d    
2     2 b     e    
3     3 c     <NA>

Auto-detect won’t work if the key variables have different names.

Inner join

A left join keeps all observations in x. Every row of x is preserved in the output and NA is used if there is no matching value in y.

An inner join retained the rows if and only if the keys are equal.

Other joins

Your time

Grab the class repository:

usethis::create_from_github("SDS322E-26FALL/0501-join", fork = FALSE)

Which two datasets in nycflights13 are relevant if we would like to plot all the destination on a map? How would you join them?

Is every row joined properly? If not, can you find all of them and decide what you want to do next?

Application 1b: change the facets order

Technically, you can use fct_relevel() to name each factor level in the order you want.

df <- flights |> 
  ... |> 
  mutate(name = fct_relevel(
    name, 
    "United Air Lines", "American Airlines", 
    "Delta Air Lines", "Southwest Airlines Co.",
    "Endeavor Air Inc.", "Envoy Air"))

We can also use fct_reorder() to order the factor levels by a numeric variable.

our_carriers = c("AA", "DL", "WN", "MQ", "UA", "9E")
df <- flights |> 
  filter(dest != "HNL") |> 
  filter(carrier %in% our_carriers) |> 
  left_join(airlines, by = c("carrier" = "carrier")) |> 
  mutate(name = fct_reorder(name, -air_time))

df |> 
  ggplot(aes(x = air_time)) + 
  geom_histogram(binwidth = 10)  + 
  facet_wrap(vars(name), ncol = 1, scales = "free_y")

🙋

Elements of Data ScienceSDS 322E

Learning objectives

Application 1a: distribution of air time of flights

Application 1a: use fct_recode to recode carrier names

Application 1b: distribution of air time of flights

The full flight data

The airline data

Application 1b: use left_join to recode carrier names

Application 1b: use left_join to recode carrier names

Different types of join

Left join

Left join syntax (1/2)

Left join syntax (2/2)

Inner join

Other joins

Your time

Application 1b: change the facets order

Elements of Data Science
SDS 322E