Introduction

This code-through explores how to put two data frames together with the join functions in the dplyr package, and how to check that a join did what you wanted.


Content Overview

Specifically, we’ll explain and demonstrate how inner_join(), left_join(), and full_join() are different from each other. We’ll also show how to use anti_join() to find rows that did not match, and how to join on more than one column, like team and week. Last, we’ll show how a join between tables at different levels, like players and teams, can quietly make extra rows.


Why You Should Care

This topic is valuable because joins are one of the most common steps in working with data, and one of the easiest to get wrong without noticing. R will not warn you if a join drops 20% of your rows or turns 32 rows into 1,000. The code still runs, and the results are wrong. A few quick checks can save you from that.


Learning Objectives

Specifically, you’ll learn how to choose the right join for your question and how to guess how many rows a join will give back. You’ll also learn how to find rows that did not match with anti_join(), and how to add up data to the right level before joining. Last, you’ll use a join to answer a real question: which NFL division gains the most yards?



Body Title

Here, we’ll show how to put two tables together. A join matches rows from one table to rows in another table, using a column that both tables share, like a team name. Some rows will match and some will not, and each kind of join treats those rows in a different way. We’ll look at each one with small tables first, so you can see exactly what happens.


Further Exposition

This is based on the work of the dplyr package, whose join functions come from SQL, the language used by databases. The idea of a key column comes from there too. In a database, each table is about one kind of thing, like teams, players, or games, and the tables are linked by a column they share.


Basic Example

A basic example shows how joins work with two small tables, so you can see every row. The teams table has the conference for four teams. The players table has the passing yards for four quarterbacks. The two tables do not have the same teams. Dallas (DAL) is missing from players, and Baltimore (BAL) is missing from teams.

teams <- data.frame(
  team       = c("BUF", "DAL", "KC", "SF"),
  conference = c("AFC", "NFC", "AFC", "NFC")
)

players <- data.frame(
  player = c("Allen", "Jackson", "Mahomes", "Purdy"),
  team   = c("BUF", "BAL", "KC", "SF"),
  yards  = c(3731, 3678, 4183, 4280)
)

teams
players


Advanced Examples

More specifically, anti_join() can be used to find out why rows are missing. It gives back the rows in the first table that have no match in the second table. It is the fastest way to find rows that did not match.

# which teams have no player?
anti_join(teams, players, by = "team")
# which players have no team?
anti_join(players, teams, by = "team")


What’s more, it can also be used for joining on more than one column. Sometimes one column is not enough to find a row. Here, each team shows up in more than one week, so the key must be team and week. If we join on team only, every week of a team matches every week in the other table.

yards.by.week <- data.frame(
  team  = c("BUF", "BUF", "KC", "KC"),
  week  = c(1, 2, 1, 2),
  yards = c(401, 352, 360, 441)
)

points.by.week <- data.frame(
  team   = c("BUF", "BUF", "KC", "KC"),
  week   = c(1, 2, 1, 2),
  points = c(34, 23, 27, 31)
)

# wrong: joining on team only
wrong <- left_join(yards.by.week, points.by.week, by = "team")
nrow(wrong)
## [1] 8
# right: joining on team and week
right <- left_join(yards.by.week, points.by.week, by = c("team", "week"))
nrow(right)
## [1] 4
right


Most notably, it’s valuable for answering a real question with real data. We’ll use the nflreadr package, which gives you free NFL data. We’ll use two tables. The player stats table has one row per player per week, with things like passing yards and rushing yards. The teams table has one row per team, with its name, conference, and division. You need an internet connection to run this part, because nflreadr downloads the data.

season <- 2024

stats <- load_player_stats(seasons = season)

# newer versions of nflreadr call this column "team", older ones call it "recent_team"
if ("recent_team" %in% names(stats)) {
  stats <- rename(stats, team = recent_team)
}

# only regular season games
stats <- stats %>% 
  filter(season_type == "REG")

nfl.teams <- load_teams(current = TRUE) %>% 
  select(team_abbr, team_name, team_conf, team_division)

# a quick look at each table
stats %>% 
  select(player_display_name, team, week, passing_yards, rushing_yards) %>% 
  head(5)
nfl.teams %>% 
  head(5)



Further Resources

Learn more about [package, technique, dataset] with the following:


dplyr: Two-table verbs
Explains each kind of join with short examples.

R for Data Science: Joins
A longer chapter that shows more ways to check your results.

nflreadr documentation
Shows how to load the NFL data used in this tutorial.



Works Cited

This code through references and cites the following sources:


Wickham, H., Çetinkaya-Rundel, M., & Grolemund, G. (2023). R for Data Science (2nd ed.). O’Reilly Media. https://r4ds.hadley.nz/joins.html

Wickham, H., François, R., Henry, L., & Müller, K. dplyr: A Grammar of Data Manipulation. https://dplyr.tidyverse.org

nflverse team. nflreadr: Download ‘nflverse’ Data. https://nflreadr.nflverse.com