EDS 221 Day 7 AM

Joining data

August 18th, 2026

Meet the data: surveys & species

Instead of one giant table, this dataset spreads rodent, bird, and reptile trapping records across two related data frames.

library(tidyverse)

surveys <- read_csv("data/surveys.csv", na = c("", "NA"))
species <- read_csv("data/species.csv", na = c("", "NA"))

surveys
# A tibble: 18,676 × 7
   record_id date     plot_id species_id sex   hindfoot_length weight
       <dbl> <chr>      <dbl> <chr>      <chr>           <dbl>  <dbl>
 1     16879 1/6/1990       1 DM         F                  37     35
 2     16880 1/6/1990       1 OL         M                  21     28
 3     16881 1/6/1990       6 PF         M                  16      7
 4     16882 1/6/1990      23 RM         F                  17      9
 5     16883 1/6/1990      12 RM         M                  17     10
 6     16884 1/6/1990      24 RM         M                  17      9
 7     16885 1/6/1990      12 SF         M                  25     35
 8     16886 1/6/1990      24 SH         F                  30     73
 9     16887 1/6/1990      12 SF         M                  28     44
10     16888 1/6/1990      17 DO         M                  36     55
# ℹ 18,666 more rows

Relational data

A relational dataset stores information across multiple tables instead of one, connected by shared columns called keys.

  • surveys — one row per animal captured on a trapping night
  • species — one row per species

Splitting data this way avoids repeating a species’ full scientific name and taxon on every one of its thousands of capture records.

Keys: primary key

A primary key uniquely identifies each row in its own table. Every species appears exactly once in species, identified by species_id.

species |>
  count(species_id) |>
  filter(n > 1)
# A tibble: 0 × 2
# ℹ 2 variables: species_id <chr>, n <int>

Zero rows out means every species_id is unique — species_id is a valid primary key for species.

Keys: foreign key

A foreign key refers to a primary key in another table. species_id also appears in surveys — but the same species is captured many times.

surveys |>
  count(species_id) |>
  filter(n > 1)
# A tibble: 38 × 2
   species_id     n
   <chr>      <int>
 1 AB            80
 2 AH           301
 3 AS             2
 4 BA            43
 5 CB            27
 6 DM          4921
 7 DO          1543
 8 DS            95
 9 DX            36
10 NL           348
# ℹ 28 more rows

Rows here mean species_id repeats in surveys — it’s a foreign key there, not a primary key.

inner_join(): matching columns

inner_join() adds columns from a second table by matching rows on a shared key. join_by() says which column to match on.

surveys |>
  inner_join(species, join_by(species_id)) |>
  select(record_id, species_id, species_name)
# A tibble: 18,269 × 3
   record_id species_id species_name             
       <dbl> <chr>      <chr>                    
 1     16879 DM         Dipodomys merriami       
 2     16880 OL         Onychomys leucogaster    
 3     16881 PF         Perognathus flavus       
 4     16882 RM         Reithrodontomys megalotis
 5     16883 RM         Reithrodontomys megalotis
 6     16884 RM         Reithrodontomys megalotis
 7     16885 SF         Sigmodon fulviventer     
 8     16886 SH         Sigmodon hispidus        
 9     16887 SF         Sigmodon fulviventer     
10     16888 DO         Dipodomys ordii          
# ℹ 18,259 more rows

inner_join(): different column names

When the key has different names in each table, connect them with == inside join_by(). Here species_id in surveys matches code in this renamed copy of species.

species_renamed <- species |> rename(code = species_id)

surveys |>
  inner_join(species_renamed, join_by(species_id == code)) |>
  select(record_id, species_id, species_name)
# A tibble: 18,269 × 3
   record_id species_id species_name             
       <dbl> <chr>      <chr>                    
 1     16879 DM         Dipodomys merriami       
 2     16880 OL         Onychomys leucogaster    
 3     16881 PF         Perognathus flavus       
 4     16882 RM         Reithrodontomys megalotis
 5     16883 RM         Reithrodontomys megalotis
 6     16884 RM         Reithrodontomys megalotis
 7     16885 SF         Sigmodon fulviventer     
 8     16886 SH         Sigmodon hispidus        
 9     16887 SF         Sigmodon fulviventer     
10     16888 DO         Dipodomys ordii          
# ℹ 18,259 more rows

What happens to row count

inner_join() keeps only rows with a match in both tables. Some captures are missing a species_id, so they drop out.

nrow(surveys)
[1] 18676
surveys |>
  inner_join(species, join_by(species_id)) |>
  nrow()
[1] 18269

Always check row counts before and after a join — an unexpected change usually means a key wasn’t as unique as you thought.

The join family

inner_join() is one of several mutating joins that add columns from a second table. They differ in which unmatched rows they keep.

  • inner_join() — keep rows that match in both tables
  • left_join() — keep all rows from the left table
  • right_join() — keep all rows from the right table
  • full_join() — keep all rows from either table

left_join() is the most common — it never drops rows from the table you started with.

Filtering joins

Filtering joins use a second table to keep or drop rows from the first — without adding any new columns.

semi_join() — keep rows with a match

birds <- species |> filter(taxa == "Bird")

surveys |>
  semi_join(birds, join_by(species_id)) |>
  distinct(species_id)
# A tibble: 8 × 1
  species_id
  <chr>     
1 AB        
2 PG        
3 PC        
4 UP        
5 AS        
6 CB        
7 PU        
8 US

anti_join() — keep rows without a match

surveys |>
  anti_join(birds, join_by(species_id)) |>
  distinct(species_id)
# A tibble: 35 × 1
   species_id
   <chr>     
 1 DM        
 2 OL        
 3 PF        
 4 RM        
 5 SF        
 6 SH        
 7 DO        
 8 OT        
 9 AH        
10 PE        
# ℹ 25 more rows

What to memorize

Joining lets you work with data split across multiple tables instead of duplicating information in one giant sheet.

Keys

  • Primary key: unique in its own table
  • Foreign key: refers to another table’s primary key

Joins

  • inner_join(), left_join()
  • join_by()