Data Wrangling II

Introduction to Global Health Data Science

Amy Herring

Duke University
STA/GLHLTH 198 Fall 2026

2026-09-14

We…

have multiple data frames

want to bring them together

This slide deck adapted from Data Science in a Box!

Data: Women in Science

Information on 10 women in science who changed the world

name
Ada Lovelace
Marie Curie
Janaki Ammal
Chien-Shiung Wu
Katherine Johnson
Rosalind Franklin
Vera Rubin
Gladys West
Flossie Wong-Staal
Jennifer Doudna

Source: Discover Magazine

professions

# A tibble: 10 × 2
   name               profession                        
   <chr>              <chr>                             
 1 Ada Lovelace       Mathematician                     
 2 Marie Curie        Physicist and Chemist             
 3 Janaki Ammal       Botanist                          
 4 Chien-Shiung Wu    Physicist                         
 5 Katherine Johnson  Mathematician                     
 6 Rosalind Franklin  Chemist                           
 7 Vera Rubin         Astronomer                        
 8 Gladys West        Mathematician                     
 9 Flossie Wong-Staal Virologist and Molecular Biologist
10 Jennifer Doudna    Biochemist

How many rows are in the data?

dates

# A tibble: 8 × 3
  name               birth_year death_year
  <chr>                   <dbl>      <dbl>
1 Janaki Ammal             1897       1984
2 Chien-Shiung Wu          1912       1997
3 Katherine Johnson        1918       2020
4 Rosalind Franklin        1920       1958
5 Vera Rubin               1928       2016
6 Gladys West              1930       2026
7 Flossie Wong-Staal       1947       2020
8 Jennifer Doudna          1964         NA

How many rows are in the data?

works

# A tibble: 9 × 2
  name               known_for                                   
  <chr>              <chr>                                       
1 Ada Lovelace       first computer algorithm                    
2 Marie Curie        theory of radioactivity,  discovery of elem…
3 Janaki Ammal       hybrid species, biodiversity protection     
4 Chien-Shiung Wu    confim and refine theory of radioactive bet…
5 Katherine Johnson  calculations of orbital mechanics critical …
6 Vera Rubin         existence of dark matter                    
7 Gladys West        mathematical modeling of the shape of the E…
8 Flossie Wong-Staal first scientist to clone HIV and create a m…
9 Jennifer Doudna    one of the primary developers of CRISPR, a …

How many rows are in the data?

Desired output

# A tibble: 10 × 5
   name               profession  birth_year death_year known_for
   <chr>              <chr>            <dbl>      <dbl> <chr>    
 1 Ada Lovelace       Mathematic…         NA         NA first co…
 2 Marie Curie        Physicist …         NA         NA theory o…
 3 Janaki Ammal       Botanist          1897       1984 hybrid s…
 4 Chien-Shiung Wu    Physicist         1912       1997 confim a…
 5 Katherine Johnson  Mathematic…       1918       2020 calculat…
 6 Rosalind Franklin  Chemist           1920       1958 <NA>     
 7 Vera Rubin         Astronomer        1928       2016 existenc…
 8 Gladys West        Mathematic…       1930       2026 mathemat…
 9 Flossie Wong-Staal Virologist…       1947       2020 first sc…
10 Jennifer Doudna    Biochemist        1964         NA one of t…

Joining data frames

something_join(x, y)

left_join(): all rows from x
right_join(): all rows from y
full_join(): all rows from both x and y
semi_join(): all rows from x where there are matching values in y, keeping just columns from x
inner_join(): all rows from x where there are matching values in y, return all combination of multiple matches in the case of multiple matches
anti_join(): return all rows from x where there are not matching values in y, never duplicate rows of x

For the next few slides…

# A tibble: 3 × 2
     id value_x
  <dbl> <chr>  
1     1 x1     
2     2 x2     
3     3 x3

# A tibble: 3 × 2
     id value_y
  <dbl> <chr>  
1     1 y1     
2     2 y2     
3     4 y4

`left_join()`

left_join(x, y)

# A tibble: 3 × 3
     id value_x value_y
  <dbl> <chr>   <chr>  
1     1 x1      y1     
2     2 x2      y2     
3     3 x3      <NA>

`left_join()`

professions %>%
  left_join(dates) #<<

# A tibble: 10 × 4
   name               profession            birth_year death_year
   <chr>              <chr>                      <dbl>      <dbl>
 1 Ada Lovelace       Mathematician                 NA         NA
 2 Marie Curie        Physicist and Chemist         NA         NA
 3 Janaki Ammal       Botanist                    1897       1984
 4 Chien-Shiung Wu    Physicist                   1912       1997
 5 Katherine Johnson  Mathematician               1918       2020
 6 Rosalind Franklin  Chemist                     1920       1958
 7 Vera Rubin         Astronomer                  1928       2016
 8 Gladys West        Mathematician               1930       2026
 9 Flossie Wong-Staal Virologist and Molec…       1947       2020
10 Jennifer Doudna    Biochemist                  1964         NA

`right_join()`

right_join(x, y)

# A tibble: 3 × 3
     id value_x value_y
  <dbl> <chr>   <chr>  
1     1 x1      y1     
2     2 x2      y2     
3     4 <NA>    y4

`right_join()`

professions %>%
  right_join(dates) #<<

# A tibble: 8 × 4
  name               profession             birth_year death_year
  <chr>              <chr>                       <dbl>      <dbl>
1 Janaki Ammal       Botanist                     1897       1984
2 Chien-Shiung Wu    Physicist                    1912       1997
3 Katherine Johnson  Mathematician                1918       2020
4 Rosalind Franklin  Chemist                      1920       1958
5 Vera Rubin         Astronomer                   1928       2016
6 Gladys West        Mathematician                1930       2026
7 Flossie Wong-Staal Virologist and Molecu…       1947       2020
8 Jennifer Doudna    Biochemist                   1964         NA

`full_join()`

full_join(x, y)

# A tibble: 4 × 3
     id value_x value_y
  <dbl> <chr>   <chr>  
1     1 x1      y1     
2     2 x2      y2     
3     3 x3      <NA>   
4     4 <NA>    y4

`full_join()`

dates %>%
  full_join(works) #<<

# A tibble: 10 × 4
   name               birth_year death_year known_for            
   <chr>                   <dbl>      <dbl> <chr>                
 1 Janaki Ammal             1897       1984 hybrid species, biod…
 2 Chien-Shiung Wu          1912       1997 confim and refine th…
 3 Katherine Johnson        1918       2020 calculations of orbi…
 4 Rosalind Franklin        1920       1958 <NA>                 
 5 Vera Rubin               1928       2016 existence of dark ma…
 6 Gladys West              1930       2026 mathematical modelin…
 7 Flossie Wong-Staal       1947       2020 first scientist to c…
 8 Jennifer Doudna          1964         NA one of the primary d…
 9 Ada Lovelace               NA         NA first computer algor…
10 Marie Curie                NA         NA theory of radioactiv…

`inner_join()`

inner_join(x, y)

# A tibble: 2 × 3
     id value_x value_y
  <dbl> <chr>   <chr>  
1     1 x1      y1     
2     2 x2      y2

`inner_join()`

dates %>%
  inner_join(works) #<<

# A tibble: 7 × 4
  name               birth_year death_year known_for             
  <chr>                   <dbl>      <dbl> <chr>                 
1 Janaki Ammal             1897       1984 hybrid species, biodi…
2 Chien-Shiung Wu          1912       1997 confim and refine the…
3 Katherine Johnson        1918       2020 calculations of orbit…
4 Vera Rubin               1928       2016 existence of dark mat…
5 Gladys West              1930       2026 mathematical modeling…
6 Flossie Wong-Staal       1947       2020 first scientist to cl…
7 Jennifer Doudna          1964         NA one of the primary de…

`semi_join()`

semi_join(x, y)

# A tibble: 2 × 2
     id value_x
  <dbl> <chr>  
1     1 x1     
2     2 x2

`semi_join()`

dates %>%
  semi_join(works) #<<

# A tibble: 7 × 3
  name               birth_year death_year
  <chr>                   <dbl>      <dbl>
1 Janaki Ammal             1897       1984
2 Chien-Shiung Wu          1912       1997
3 Katherine Johnson        1918       2020
4 Vera Rubin               1928       2016
5 Gladys West              1930       2026
6 Flossie Wong-Staal       1947       2020
7 Jennifer Doudna          1964         NA

`anti_join()`

anti_join(x, y)

# A tibble: 1 × 2
     id value_x
  <dbl> <chr>  
1     3 x3

`anti_join()`

dates %>%
  anti_join(works) #<<

# A tibble: 1 × 3
  name              birth_year death_year
  <chr>                  <dbl>      <dbl>
1 Rosalind Franklin       1920       1958

Putting it all together

professions %>%
  left_join(dates) %>%
  left_join(works)

# A tibble: 10 × 5
   name               profession  birth_year death_year known_for
   <chr>              <chr>            <dbl>      <dbl> <chr>    
 1 Ada Lovelace       Mathematic…         NA         NA first co…
 2 Marie Curie        Physicist …         NA         NA theory o…
 3 Janaki Ammal       Botanist          1897       1984 hybrid s…
 4 Chien-Shiung Wu    Physicist         1912       1997 confim a…
 5 Katherine Johnson  Mathematic…       1918       2020 calculat…
 6 Rosalind Franklin  Chemist           1920       1958 <NA>     
 7 Vera Rubin         Astronomer        1928       2016 existenc…
 8 Gladys West        Mathematic…       1930       2026 mathemat…
 9 Flossie Wong-Staal Virologist…       1947       2020 first sc…
10 Jennifer Doudna    Biochemist        1964         NA one of t…

Dealing with the missing values

Just in case you’re curious…

professions %>%
  left_join(dates) %>%
  left_join(works) %>%
  mutate(
    birth_year = case_when(
      name == "Ada Lovelace" ~ 1815,
      name == "Marie Curie" ~ 1867,
      TRUE ~ birth_year
    ),
    death_year = case_when(
      name == "Ada Lovelace" ~ 1852,
      name == "Marie Curie" ~ 1934,
      TRUE ~ death_year
    ),
    known_for = case_when(
      name == "Rosalind Franklin" ~ "X-ray diffraction images revealing DNA's double helix structure",
      TRUE ~ known_for
    )
  )

# A tibble: 10 × 5
   name               profession  birth_year death_year known_for
   <chr>              <chr>            <dbl>      <dbl> <chr>    
 1 Ada Lovelace       Mathematic…       1815       1852 first co…
 2 Marie Curie        Physicist …       1867       1934 theory o…
 3 Janaki Ammal       Botanist          1897       1984 hybrid s…
 4 Chien-Shiung Wu    Physicist         1912       1997 confim a…
 5 Katherine Johnson  Mathematic…       1918       2020 calculat…
 6 Rosalind Franklin  Chemist           1920       1958 X-ray di…
 7 Vera Rubin         Astronomer        1928       2016 existenc…
 8 Gladys West        Mathematic…       1930       2026 mathemat…
 9 Flossie Wong-Staal Virologist…       1947       2020 first sc…
10 Jennifer Doudna    Biochemist        1964         NA one of t…

Case study: Medical records

Medical Records

Have:
- Enrollment: officially enrolled in Duke MyChart
- Survey: completed by those seen in hospital clinics over past year
Want: Survey info for all officially enrolled in Duke MyChart

enrollment

# A tibble: 3 × 2
     id name          
  <dbl> <chr>         
1     1 Erling Haaland
2     2 Lamine Yamal  
3     3 Aitana Bonmati

survey

# A tibble: 5 × 3
     id name             clinic                      
  <dbl> <chr>            <chr>                       
1     2 Lamine Yamal     Nutrition                   
2     3 Aitana Bonmati   Orthopedics                 
3     4 Gianni Infantino Cognitive Behavioral Therapy
4     5 Lionel Messi     Endocrinology               
5     3 Aitana Bonmati   Radiology

Medical records

In MyChart
Survey missing
MyChart not activated

For those enrolled in MyChart, for which clinic did they complete a survey in the past year?

enrollment %>% 
  left_join(survey, by = "name") %>% 
  select(name, clinic)

# A tibble: 4 × 2
  name           clinic     
  <chr>          <chr>      
1 Erling Haaland <NA>       
2 Lamine Yamal   Nutrition  
3 Aitana Bonmati Orthopedics
4 Aitana Bonmati Radiology

For those enrolled in MyChart, who did not complete a survey?

enrollment %>% 
  anti_join(survey, by = "name")

# A tibble: 1 × 2
     id name          
  <dbl> <chr>         
1     1 Erling Haaland

For those who completed a survey, who is not in MyChart?

survey %>% 
  anti_join(enrollment, by = "name")

# A tibble: 2 × 3
     id name             clinic                      
  <dbl> <chr>            <chr>                       
1     4 Gianni Infantino Cognitive Behavioral Therapy
2     5 Lionel Messi     Endocrinology