Join

There are various join functions in dplyr. This page will only show the use of dplyr::inner_join(). This function joins 2 tibbles/data.frames based on a key column. This allows you to add data from matching rows similar to VLOOKUP in excel.

Tidyverse reference page

Dataset

Before demonstrating various examples we will create a 2 tibbles:

• acris_records: Contain records of frogs from Acris species (Cricket frogs).
• species_info: Contains IUCN and lineage information on the the 2 frog

We’ll use these for joining.

#Vectors for tibble
Species <- c("Acris gryllus","Acris blanchardi","Acris blanchardi","Acris crepitans",
                "Acris crepitans","Acris blanchardi","Acris blanchardi",
                "Acris blanchardi","Acris gryllus","Acris gryllus")
length <- c(32,10,14,23,19,11,9,14,32,22)
#Tibble
acris_records <- tibble::tibble(Species, length)
acris_records

# A tibble: 10 × 2
   Species          length
   <chr>             <dbl>
 1 Acris gryllus        32
 2 Acris blanchardi     10
 3 Acris blanchardi     14
 4 Acris crepitans      23
 5 Acris crepitans      19
 6 Acris blanchardi     11
 7 Acris blanchardi      9
 8 Acris blanchardi     14
 9 Acris gryllus        32
10 Acris gryllus        22

Acquire IUCN and lineage information from the amphibian_div_tbl data from the mgrtibbles package.

#Load package
library("mgrtibbles")
species_info <- mgrtibbles::amphibian_div_tbl |>
    #Select columns
    dplyr::select(Species:Genus) |>
    #Extract distinct/unique rows
    dplyr::distinct() |>
    #Select Acris rows
    dplyr::filter(Genus=="Acris")
species_info

# A tibble: 3 × 6
  Species          IUCN.Red.List.Status iucn_2cat Order Family  Genus
  <chr>            <fct>                <fct>     <chr> <chr>   <chr>
1 Acris blanchardi <NA>                 <NA>      Anura Hylidae Acris
2 Acris crepitans  Least Concern (LC)   LC        Anura Hylidae Acris
3 Acris gryllus    Least Concern (LC)   LC        Anura Hylidae Acris

inner_join()

Join 2 tibbles/data.frames with a key column. This key column must have the same name and matching values.

dplyr::inner_join(
    #Left tibble
    acris_records,
    #Right tibble
    species_info,
    #Key column
    by = "Species"
)

# A tibble: 10 × 7
   Species          length IUCN.Red.List.Status iucn_2cat Order Family  Genus
   <chr>             <dbl> <fct>                <fct>     <chr> <chr>   <chr>
 1 Acris gryllus        32 Least Concern (LC)   LC        Anura Hylidae Acris
 2 Acris blanchardi     10 <NA>                 <NA>      Anura Hylidae Acris
 3 Acris blanchardi     14 <NA>                 <NA>      Anura Hylidae Acris
 4 Acris crepitans      23 Least Concern (LC)   LC        Anura Hylidae Acris
 5 Acris crepitans      19 Least Concern (LC)   LC        Anura Hylidae Acris
 6 Acris blanchardi     11 <NA>                 <NA>      Anura Hylidae Acris
 7 Acris blanchardi      9 <NA>                 <NA>      Anura Hylidae Acris
 8 Acris blanchardi     14 <NA>                 <NA>      Anura Hylidae Acris
 9 Acris gryllus        32 Least Concern (LC)   LC        Anura Hylidae Acris
10 Acris gryllus        22 Least Concern (LC)   LC        Anura Hylidae Acris

Swapping the left and right tibbles swaps their positions in the joined tibble.

dplyr::inner_join(
    #Left tibble
    species_info,
    #Right tibble
    acris_records,
    #Key column
    by = "Species"
)

# A tibble: 10 × 7
   Species          IUCN.Red.List.Status iucn_2cat Order Family  Genus length
   <chr>            <fct>                <fct>     <chr> <chr>   <chr>  <dbl>
 1 Acris blanchardi <NA>                 <NA>      Anura Hylidae Acris     10
 2 Acris blanchardi <NA>                 <NA>      Anura Hylidae Acris     14
 3 Acris blanchardi <NA>                 <NA>      Anura Hylidae Acris     11
 4 Acris blanchardi <NA>                 <NA>      Anura Hylidae Acris      9
 5 Acris blanchardi <NA>                 <NA>      Anura Hylidae Acris     14
 6 Acris crepitans  Least Concern (LC)   LC        Anura Hylidae Acris     23
 7 Acris crepitans  Least Concern (LC)   LC        Anura Hylidae Acris     19
 8 Acris gryllus    Least Concern (LC)   LC        Anura Hylidae Acris     32
 9 Acris gryllus    Least Concern (LC)   LC        Anura Hylidae Acris     32
10 Acris gryllus    Least Concern (LC)   LC        Anura Hylidae Acris     22