# This script follows loosely  the first 3 sections of chapter 19 of the book
# "R for Data Science" (2nd edition)

# JOINS

library(tidyverse)
library(nycflights13)

# Some datasets included in package nycflights13
airlines
airports
planes
weather

############# 19.2 - KEYS #################

# Checking primary keys (a.k.a. id columns). In other words, check if every entry
# of the key column (or id column) is unique.
planes %>% count(tailnum) %>% filter(n > 1)
weather %>% count(time_hour, origin) %>% filter(n > 1)

# It is also important to check for missing values.
planes %>% filter(is.na(tailnum))
weather %>% filter(is.na(time_hour) | is.na(origin))

# Sometimes there is no single id column (i.e. no primary key). An example is:
flights

# Perhaps (time_hour, carrier, flight) constitute a key
flights %>% count(time_hour, carrier, flight) %>% filter(n > 1)

# However it may be simpler to add a "surrogate key", i.e., a made-up ID column.
flights2 <- flights %>% mutate(id = row_number(), .before = 1)
flights2

############## 19.3 - Basic Joins ##############

# Mutating join - allows to combine variables from two data frames. "It first
# matches observations by their keys, then copies across variables from one data
# frame to the other."

# Create a "narrower" dataset.
flights2 <- flights  %>%  
  select(year, time_hour, origin, dest, tailnum, carrier)
flights2

# Add the full airline name to the dataset.
flights2 %>%
  left_join(airlines)

# Find out temperature and wind speed at each departure.
flights2 %>%
  left_join(weather %>% select(origin, time_hour, temp, wind_speed))

# Find out the size of the plane that was flying.
flights2 %>% 
  left_join(planes %>% select(tailnum, type, engines, seats))

# When left_join() fails to find a match for a row in x, it fills in the new
# variables with missing values. For the plane "N3ALAA" there will be missing
# values.
flights2 %>% 
  filter(tailnum == "N3ALAA") %>% 
  left_join(planes  %>%  select(tailnum, type, engines, seats))

# Specifying the join keys instead of "trusting" the natural join

flights2 %>% left_join(planes) # "natural" join that gives ill results...

# We only want to join on tailnum so we need to provide an explicit specification.
flights2 %>% 
  left_join(planes, join_by(tailnum))

# join_by(tailnum) is short for join_by(tailnum == tailnum)
flights2 %>% 
  left_join(planes, join_by(tailnum == tailnum))

# Notice that we now have two year variables:
#  - year.x from the first table in the join
#  - year.y from the second table in the join


# There are two ways to join the flight2 and airports table:

flights2 %>% 
  left_join(airports, join_by(dest == faa)) # by destination airport

flights2 %>% 
  left_join(airports, join_by(origin == faa)) # by airport of origin

# (filtering joins and non-equi joins are out of the scope of our course and 
# won't be covered)