Meetup 7: Joins and SQL

George I. Hagstrom

2025-10-06

Lab 3-4 Review: Axes

  • Never plot different units on the same axis
  • airquality dataset
  • contains ozone, solar.R, Wind, Temp
airquality |> 
  pivot_longer(Ozone:Temp,
               names_to = "variable",
               values_to = "value") |> 
               ggplot(aes(x=value,y=variable)) +
  geom_boxplot() +
  labs(title="NOT LIKE THIS") +
  theme_bw(base_size = 16)

Lab 3-4 Review: Axes

  • Never plot different units on the same axis
  • airquality dataset
  • contains ozone, solar.R, Wind, Temp

Lab 3-4 Review: Axes

  • Instead use facets to make each axis distinct
airquality |> 
  pivot_longer(Ozone:Temp,
               names_to = "variable",
               values_to = "value") |> 
               ggplot(aes(x=value)) +
  geom_boxplot() +
  facet_wrap(~variable,ncol = 2,scales="free_x") +
  labs(title="LIKE THIS") +
  theme_bw(base_size = 16) +
  theme( axis.text.y = element_blank(),
           axis.ticks = element_blank())

Lab 3-4 Review: Axes

  • Instead use facets to make each axis distinct

Investigating Outliers

  • In the diabetes dataset there was an observation with a skin thickness of 99
  • Should we discard this measurement?
  • Make scatterplot of BMI vs skin thickness:

Project Proposal

  • No lab next week
  • But project proposal due October 20th
  • Target 2 Pages
    • Intro
    • Data
    • Analysis Plan
  • Idea: Demonstrate full data science workflow
  • Emphasis on everything but modeling
  • Feel free to ask me about your idea as you develop it

Project Proposal

  • Pick a topic that either:
    • Relates to a specific professional goal or your current job
    • Relates to a something you know a lot about
    • Is something you have become very interested in
  • Read about your topic when developing your proposal!
    • Have a short section on with references explaining state-of-the-art

Heilmeier Framework

Developing a Research Proposal: Heilmeier Questions

George Heilmeier developed a series of questions he would ask of potential DARPA grant recipients

The answers to these questions get you to the core of your idea

Heilmeier Questions

  1. What are you trying to do? (Without Jargon)
  2. How is it done today and what are the limits?
  3. What is your approach and why will it be successful?
  4. Who cares about this question? (Stakeholders?)
  5. What are the risks/obstacles
  6. What will it cost? (0$)
  7. How long will it take? (Deadline)
  8. What does progress/success look like?

This Week

  • Two main topics: Joins and SQL
  • Separate video for SQL with a coding vignette

Combining Data From Multiple Tables

  • Data usually stored in separate files/data frames
  • Analysis requires data in a single frame
  • Need tools to combine data-frames

Example: nycflights13

Example: nycflights13

flights |> select(time_hour,carrier,dep_time) |> head(8) |> kable()
time_hour carrier dep_time
2013-01-01 05:00:00 UA 517
2013-01-01 05:00:00 UA 533
2013-01-01 05:00:00 AA 542
2013-01-01 05:00:00 B6 544
2013-01-01 06:00:00 DL 554
2013-01-01 05:00:00 UA 554
2013-01-01 06:00:00 B6 555
2013-01-01 06:00:00 EV 557

Example: nycflights13

planes |> select(tailnum,year,model) |> head(8) |> kable()
tailnum year model
N10156 2004 EMB-145XR
N102UW 1998 A320-214
N103US 1999 A320-214
N104UW 1999 A320-214
N10575 2002 EMB-145LR
N105UW 1999 A320-214
N107US 1999 A320-214
N108UW 1999 A320-214

Example: nycflights13

airports |> select(faa,name,lat,lon) |> head(8) |> kable()
faa name lat lon
04G Lansdowne Airport 41.13047 -80.61958
06A Moton Field Municipal Airport 32.46057 -85.68003
06C Schaumburg Regional 41.98934 -88.10124
06N Randall Airport 41.43191 -74.39156
09J Jekyll Island Airport 31.07447 -81.42778
0A9 Elizabethton Municipal Airport 36.37122 -82.17342
0G6 Williams County Airport 41.46731 -84.50678
0G7 Finger Lakes Regional Airport 42.88356 -76.78123

Example: nycflights13

  • Can combine data frames along rows that share matching values of a common variable:
flights |> 
  select(dest,origin) |> 
  head(6) |>
  kable()
dest origin
IAH EWR
IAH LGA
MIA JFK
BQN JFK
ATL LGA
ORD EWR 
airports |> 
  select(faa) |> 
  head(8) |> 
  kable()
faa
04G
06A
06C
06N
09J
0A9
0G6
0G7
  • Columns which connect different data frames are Keys

Example: nycflights13

  • Can combine data frames along rows that share matching values of a common variable:
flights |> 
  select(dest,origin) |> 
  head(6) |>
  kable()
dest origin
IAH EWR
IAH LGA
MIA JFK
BQN JFK
ATL LGA
ORD EWR 
airports |> 
  select(faa) |> 
  head(8) |> 
  kable()
faa
04G
06A
06C
06N
09J
0A9
0G6
0G7
  • Columns which connect different data frames are Keys

Example: nycflights13

  • Can combine data frames along rows that share matching values of a common variable:
flights |> 
  select(tailnum) |> 
  head(6) |>
  kable()
tailnum
N14228
N24211
N619AA
N804JB
N668DN
N39463 
planes |> 
  select(tailnum) |> 
  head(8) |> 
  kable()
tailnum
N10156
N102UW
N103US
N104UW
N10575
N105UW
N107US
N108UW
  • Columns that are common “Keys” used to link data frames

Primary Keys

A Primary Key is a column/group of columns whose values uniquely determine each row of the data frame.

  • For planes it is tailnum
planes |> select(tailnum, year, type, model) |> head(5) |> kable()
tailnum year type model
N10156 2004 Fixed wing multi engine EMB-145XR
N102UW 1998 Fixed wing multi engine A320-214
N103US 1999 Fixed wing multi engine A320-214
N104UW 1999 Fixed wing multi engine A320-214
N10575 2002 Fixed wing multi engine EMB-145LR

Definition

A Primary Key is a column/group of columns whose values uniquely determine each row of the data frame.

  • For planes it is tailnum
planes |> 
  count(tailnum) |> 
  filter(n > 1)
# A tibble: 0 × 2
# ℹ 2 variables: tailnum <chr>, n <int>
  • This count command shows that each row has a unique value of tailnum proving that it is a primary key

Compound Keys

  • Sometimes no single variable uniquely identifies rows.

  • Multiple variables can combine to be a primary key

  • For weather it requires origin and time-hour:

weather |> count(time_hour) |> filter(n>1) |> nrow()
[1] 8706
weather |> 
  count(origin,time_hour) |> 
  filter(n > 1)
# A tibble: 0 × 3
# ℹ 3 variables: origin <chr>, time_hour <dttm>, n <int>

Foreign Keys

A Foreign Key is a variable/group of variables that is a primary key for another data frame.

flights |> select(flight,time_hour,tailnum) |> head(4) |> kable()
flight time_hour tailnum
1545 2013-01-01 05:00:00 N14228
1714 2013-01-01 05:00:00 N24211
1141 2013-01-01 05:00:00 N619AA
725 2013-01-01 05:00:00 N804JB
  • tailnum is a primary key for planes
  • Foreign key allows for rows to be matched between data frames

Joins

  • Joins are functions that combine two data frames

something_join(x,y,by = join_by(...))

  • Here something is either:
    • left_join
    • right_join
    • full_join
    • inner_join
    • semi_join
    • anti_join

Join Types

Two main “types” of joins:

  • Mutating Joins:
    • Combine data between data frames
    • left_join, right_join, full_join, inner_join
  • Filtering Joins:
    • Filter rows based on matches
    • semi_join, anti_join

Mutating Joins

  1. left_join: Keeps all rows of x, matches/adds from y
  2. right_join: reversed left_join
  3. full_join: keeps all rows of both
  4. inner_join: only rows occurring in both

R4DS 19.8

Left Join

  • Starting Point:

R4DS 19.2

Left Join

R4DS 19.2

left_join(x,y) |> kable()
key val_x val_y
1 x1 y1
2 x2 y2
3 x3 NA

Right Join

R4DS 19.2

right_join(x,y) |> kable()
key val_x val_y
1 x1 y1
2 x2 y2
4 NA y3

Inner Join

R4DS 19.2

inner_join(x,y) |> kable()
key val_x val_y
1 x1 y1
2 x2 y2

Full Join

R4DS 19.2
full_join(x,y) |> kable()
key val_x val_y
1 x1 y1
2 x2 y2
3 x3 NA
4 NA y3

What about Keys?

  • join functions automatically try to find a foreign key
  • You may specify a key using join_by and a formula:
flights |> left_join(airports,join_by(dest == faa)) |> 
  select(year:dep_delay) |> head(5)
# A tibble: 5 × 6
   year month   day dep_time sched_dep_time dep_delay
  <int> <int> <int>    <int>          <int>     <dbl>
1  2013     1     1      517            515         2
2  2013     1     1      533            529         4
3  2013     1     1      542            540         2
4  2013     1     1      544            545        -1
5  2013     1     1      554            600        -6
  • Potential for multiple matches and duplication when there is no foreign key

Filtering Joins

  • semi_join(x,y) removes rows of x that don’t have matches in y
  • Use semi_join to restrict airports to the destination airports
airports |> 
  semi_join(flights, join_by(faa == origin))
# A tibble: 3 × 8
  faa   name                  lat   lon   alt    tz dst   tzone           
  <chr> <chr>               <dbl> <dbl> <dbl> <dbl> <chr> <chr>           
1 EWR   Newark Liberty Intl  40.7 -74.2    18    -5 A     America/New_York
2 JFK   John F Kennedy Intl  40.6 -73.8    13    -5 A     America/New_York
3 LGA   La Guardia           40.8 -73.9    22    -5 A     America/New_York

Filtering Joins

  • anti_join(x,y) removes rows of x that have matches in y
  • Use anti_join to find implicit missing values
  • Flights where weather info lacking:
flights |> 
  anti_join(weather) |> 
  head(5) |> 
  select(year:dep_delay) |>
  print()
# A tibble: 5 × 6
   year month   day dep_time sched_dep_time dep_delay
  <int> <int> <int>    <int>          <int>     <dbl>
1  2013     1     1     1153           1200        -7
2  2013     1     1     1154           1200        -6
3  2013     1     1     1155           1200        -5
4  2013     1     1     1155           1200        -5
5  2013     1     1     1157           1200        -3

Data Science in Context Presentations

Meetup Reflection/One Minute Paper

Please fill out the following google form after the meeting or watching the video:

Click Here