| name |
|---|
| Ada Lovelace |
| Marie Curie |
| Janaki Ammal |
| Chien-Shiung Wu |
| Katherine Johnson |
| Rosalind Franklin |
| Vera Rubin |
| Gladys West |
| Flossie Wong-Staal |
| Jennifer Doudna |
Data Wrangling II
Introduction to Global Health Data Science
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
Source: Discover Magazine
Inputs
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 NAHow 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
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…
x# A tibble: 3 × 2
id value_x
<dbl> <chr>
1 1 x1
2 2 x2
3 3 x3 y# 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 NAright_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 NAfull_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 NAanti_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 1958Putting 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 Bonmatisurvey# 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
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 HaalandFor 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