library(tidyverse)
library(here)
library(rio)
library(janitor)
library(skimr)Session 3: Missing Data, Joins, and Export
Missing values, rows with no ID, duplicates, replace_na() and across(), select(), joins, and exporting
- Find where data are missing and decide what to do about it
- Set aside rows with no ID, and remove duplicate rows
- Replace missing values and choose which columns to keep
- Combine two datasets with a join, and check that the join worked
- Export finished datasets for analysis
Before you start: open the workshop folder in Positron (File → Open Folder…) and open student_scripts/Session 3 - Missing Data, Joins, and Export_STUDENT.R. We start from processed_data/linelist_clean.rds and processed_data/measles_clean.rds, which you saved at the end of Session 2. Missed Session 2? Ready-made copies of both files are already in processed_data/.
Where this session fits. Today finishes Clean (missing values, rows with no ID, and duplicates), then covers Combine (adding a second dataset with a join) and Save and share (exporting the finished data).
- Import
- Inspect
- Clean
- Combine
- Reshape
- Summarize
- Visualize
- Save and share
- Automate
Today you finish two datasets side by side, so they are ready for analysis:
linelist_clean→linelist_final→combined_linelist(follow along)measles_clean→measles_final(in the TRY ITs)
Part 1: Set up
👀 Follow along 1.1 — Load today’s packages: tidyverse, here, rio, janitor, and skimr.
👀 Follow along 1.2 — Import processed_data/linelist_clean.rds (linelist_clean) and raw_data/case_info.csv (case_info).
linelist_clean <- import(here("processed_data", "linelist_clean.rds"), trust = TRUE)
case_info <- import(here("raw_data", "case_info.csv"))trust = TRUE?
An .rds file can contain R code, so rio asks you to confirm you trust the file before opening it. We made this file ourselves in Session 2, so it is safe. Without trust = TRUE, rio prints a warning (the file still opens).
Still on the problem list from Session 1:
| # | Problem | Fixed in |
|---|---|---|
| 5 | Gender and outcome have missing values | Part 5 |
| 7 | 130 rows have no Case ID | Part 3 |
| 8 | 25 rows are duplicates | Part 4 |
Part 2: Where is data missing?
👀 Follow along 2.1 — Count the missing values in each column with colSums(is.na(linelist_clean)).
colSums(is.na(linelist_clean)) case_id date_of_infection onset_date hospital_date
130 2315 286 0
date_of_outcome outcome gender hospital
1061 1493 317 1509
age age_unit wt_kg ht_cm
100 0 34 0
age_years hospital_name bmi age_cat
100 0 1382 100
- Leave them as
NA— most R functions havena.rm = TRUEto skip them (e.g.,mean(x, na.rm = TRUE)) - Remove the rows —
drop_na(column)removes rows missing that column. Use carefully: you may lose a lot of data - Replace them — e.g., with “Unknown” for categories, so they show up in tables. Never invent numbers without a statistical method (imputation)
👀 Follow along 2.2 — How many rows would drop_na(bmi) remove? Compare nrow() before and after.
# drop_na() example: how many rows would we lose?
nrow(linelist_clean)[1] 6604
linelist_clean %>% drop_na(bmi) %>% nrow()[1] 5222
drop_na(bmi) would throw away 1382 patients — more than one in five. That is why we don’t do it here.
Part 3: Rows with no ID
In Part 6 we join case_info on case_id, so rows without an ID can’t be linked. Instead of deleting them, we save them separately with new IDs so they are not lost.
👀 Follow along 3.1 — Create missing_ids: filter(is.na(case_id)), then give each row a new case_id with paste0(), sprintf(), and row_number().
missing_ids <- linelist_clean %>%
filter(is.na(case_id)) %>%
mutate(case_id = paste0("ID", sprintf("%05d", row_number())))
missing_ids %>% select(case_id, gender, age_years, outcome) %>% head() case_id gender age_years outcome
1 ID00001 male 0.75 Recover
2 ID00002 female 10.00 Death
3 ID00003 male 44.00 Recover
4 ID00004 <NA> NA <NA>
5 ID00005 male 0.00 Recover
6 ID00006 female 1.00 Death
nrow(missing_ids)[1] 130
row_number()gives 1, 2, 3, … for each rowsprintf("%05d", 1)pads a number with zeros to 5 digits:"00001"paste0()glues text together:"ID"+"00001"="ID00001"
Part 4: Duplicates
distinct() keeps one copy of each row that appears more than once.
👀 Follow along 4.1 — Create linelist_final: keep the rows that have a case_id with filter(!is.na(case_id)), then remove exact copies with distinct().
linelist_final <- linelist_clean %>%
filter(!is.na(case_id)) %>% # keep rows that HAVE an ID (! means "not")
distinct()
nrow(linelist_clean)[1] 6604
nrow(linelist_final)[1] 6451
Removing exact copies is safe. But some IDs still appear more than once, with different details:
👀 Follow along 4.2 — Do any IDs still appear more than once? get_dupes(case_id)
linelist_final %>% get_dupes(case_id) %>% nrow()[1] 1106
linelist_final %>%
get_dupes(case_id) %>%
select(case_id, dupe_count, gender, age_years, onset_date, outcome) %>%
head(6) case_id dupe_count gender age_years onset_date outcome
1 04c762 3 female 3 2014-11-08 Recover
2 04c762 3 female 3 2012-11-08 Recover
3 04c762 3 female 3 2012-11-08 Recover
4 19703e 3 female 15 2014-11-13 <NA>
5 19703e 3 female 15 2012-11-13 <NA>
6 19703e 3 female 15 2012-11-13 <NA>
These rows are not exact copies (every column the same), so distinct() keeps them. Deciding which record is correct needs a person — in a real project, you would ask whoever collected the data. We keep them for this workshop.
Part 5: Replacing missing values and choosing columns
replace_na() with across()
across() applies the same function to many columns at once. Here: for every text column, replace NA with "Unknown".
👀 Follow along 5.1 — Replace NA with “Unknown” in every text column of linelist_final. Check with count(gender) and count(outcome).
linelist_final <- linelist_final %>%
mutate(across(where(is.character), ~ replace_na(.x, "Unknown")))
linelist_final %>% count(gender) gender n
1 Unknown 304
2 female 3073
3 male 3074
linelist_final %>% count(outcome) outcome n
1 Death 2840
2 Recover 2158
3 Unknown 1453
Read ~ replace_na(.x, "Unknown") as: “for each column (.x), replace its NAs with Unknown”. where(is.character) picks only text columns, so number and date columns keep their NAs.
For a single column: mutate(gender = replace_na(gender, "Unknown"))
select(): choose and order columns
We no longer need the original hospital, age, and age_unit columns — hospital_name and age_years replace them. select() keeps columns in the order you list them.
👀 Follow along 5.2 — select() case_id, gender, age_years, age_cat, wt_kg, ht_cm, bmi, the four date columns, hospital_name, and outcome.
linelist_final <- linelist_final %>%
select(case_id, gender, age_years, age_cat, wt_kg, ht_cm, bmi,
date_of_infection, onset_date, hospital_date, date_of_outcome,
hospital_name, outcome)
glimpse(linelist_final)Rows: 6,451
Columns: 13
$ case_id <chr> "5fe599", "8689b7", "11f8ea", "b8812a", "893f25", "b…
$ gender <chr> "male", "female", "male", "female", "male", "female"…
$ age_years <dbl> 2, 3, 56, 18, 3, 16, 16, 0, 61, 27, 12, 42, 19, 7, 7…
$ age_cat <chr> "Under 18", "Under 18", "50-59", "18-29", "Under 18"…
$ wt_kg <dbl> 27, 25, 91, 41, 36, 56, 47, NA, 86, 69, 67, 84, 68, …
$ ht_cm <dbl> 48, 59, 238, 135, 71, 116, 87, 11, 226, 174, 112, 18…
$ bmi <dbl> NA, NA, 16.1, 22.5, NA, 41.6, NA, NA, 16.8, 22.8, 53…
$ date_of_infection <date> 2014-05-08, NA, NA, 2014-05-04, 2014-05-18, 2014-05…
$ onset_date <date> 2014-05-13, 2014-05-13, 2014-05-16, 2014-05-18, 201…
$ hospital_date <date> 2014-05-15, 2014-05-14, 2014-05-18, 2014-05-20, 201…
$ date_of_outcome <date> NA, 2014-05-18, 2014-05-30, NA, 2014-05-29, 2014-05…
$ hospital_name <chr> "Other/Unknown", "Other/Unknown", "St. Mark's Matern…
$ outcome <chr> "Unknown", "Recover", "Recover", "Unknown", "Recover…
select()
select(-hospital) drops a column · select(case_id, everything()) moves one column first · select(starts_with("date")) picks columns by name
The whole thing as one pipeline
linelist_final <- linelist_clean %>%
filter(!is.na(case_id)) %>%
distinct() %>%
mutate(across(where(is.character), ~ replace_na(.x, "Unknown"))) %>%
select(case_id, gender, age_years, age_cat, wt_kg, ht_cm, bmi,
date_of_infection, onset_date, hospital_date, date_of_outcome,
hospital_name, outcome)Part 6: Joining datasets
How joins work
A join combines two data frames by matching rows on a key column that both share. A tiny example first:
👀 Follow along 6.1 — A tiny example. Make patients (id 1, 2, 3) and labs (id 2, 3, 4) with tibble(), then try four joins on them.
patients <- tibble(id = c(1, 2, 3), name = c("Ana", "Ben", "Cho"))
labs <- tibble(id = c(2, 3, 4), result = c("positive", "negative", "positive"))
left_join(patients, labs, by = "id") # all patients; NA where no lab result# A tibble: 3 × 3
id name result
<dbl> <chr> <chr>
1 1 Ana <NA>
2 2 Ben positive
3 3 Cho negative
inner_join(patients, labs, by = "id") # only ids in BOTH# A tibble: 2 × 3
id name result
<dbl> <chr> <chr>
1 2 Ben positive
2 3 Cho negative
full_join(patients, labs, by = "id") # everyone from both# A tibble: 4 × 3
id name result
<dbl> <chr> <chr>
1 1 Ana <NA>
2 2 Ben positive
3 3 Cho negative
4 4 <NA> positive
anti_join(patients, labs, by = "id") # patients with NO lab result# A tibble: 1 × 2
id name
<dbl> <chr>
1 1 Ana
| Join | Keeps |
|---|---|
left_join() |
every row of the first data frame |
right_join() |
every row of the second data frame |
inner_join() |
only rows that match in both |
full_join() |
every row from both |
anti_join() |
rows of the first with no match — great for checking |
left_join() is the one you will use most: start with your main dataset and add information to it.
merge() and %in%
Base R joins with one function, merge(). The all arguments choose the type of join:
🔁 Follow along 6.2 — The base R way: merge() with and without all.x = TRUE, then find patients with no lab result with %in% (the next two code blocks).
merge(patients, labs, by = "id") # inner join (the default!) id name result
1 2 Ben positive
2 3 Cho negative
merge(patients, labs, by = "id", all.x = TRUE) # left join id name result
1 1 Ana <NA>
2 2 Ben positive
3 3 Cho negative
merge(patients, labs, by = "id", all = TRUE) # full join id name result
1 1 Ana <NA>
2 2 Ben positive
3 3 Cho negative
4 4 <NA> positive
Watch out: merge() keeps only matching rows unless you say otherwise, and it sorts the result by the key. %in% asks “is each value found in the other vector?”, and gives one TRUE/FALSE per value. It is the base R way to find rows with no match:
patients$id %in% labs$id[1] FALSE TRUE TRUE
patients[!patients$id %in% labs$id, ] # the same as anti_join()# A tibble: 1 × 2
id name
<dbl> <chr>
1 1 Ana
%in% is worth learning well: you will use it inside filter() too, for example filter(district %in% c("North", "South")).
Join the case information
👀 Follow along 6.3 — Create combined_linelist by left_join()-ing case_info to linelist_final.
Pattern: NEW_DATA <- MAIN_DATA %>% left_join(OTHER_DATA, by = "KEY_COLUMN")
glimpse(case_info)Rows: 5,888
Columns: 7
$ case_id <chr> "5fe599", "8689b7", "11f8ea", "b8812a", "893f25", "be99c8", "0…
$ fever <chr> "no", "Unknown", "Unknown", "no", "no", "no", "Unknown", "no",…
$ chills <chr> "no", "Unknown", "Unknown", "no", "no", "no", "Unknown", "no",…
$ cough <chr> "yes", "Unknown", "Unknown", "no", "yes", "yes", "Unknown", "y…
$ aches <chr> "no", "Unknown", "Unknown", "no", "no", "no", "Unknown", "no",…
$ vomit <chr> "yes", "Unknown", "Unknown", "no", "yes", "yes", "Unknown", "y…
$ temp <dbl> 36.8, 36.9, 36.9, 36.8, 36.9, 37.6, 37.3, 37.0, 36.4, 35.9, 36…
combined_linelist <- linelist_final %>%
left_join(case_info, by = "case_id")
glimpse(combined_linelist)Rows: 6,451
Columns: 19
$ case_id <chr> "5fe599", "8689b7", "11f8ea", "b8812a", "893f25", "b…
$ gender <chr> "male", "female", "male", "female", "male", "female"…
$ age_years <dbl> 2, 3, 56, 18, 3, 16, 16, 0, 61, 27, 12, 42, 19, 7, 7…
$ age_cat <chr> "Under 18", "Under 18", "50-59", "18-29", "Under 18"…
$ wt_kg <dbl> 27, 25, 91, 41, 36, 56, 47, NA, 86, 69, 67, 84, 68, …
$ ht_cm <dbl> 48, 59, 238, 135, 71, 116, 87, 11, 226, 174, 112, 18…
$ bmi <dbl> NA, NA, 16.1, 22.5, NA, 41.6, NA, NA, 16.8, 22.8, 53…
$ date_of_infection <date> 2014-05-08, NA, NA, 2014-05-04, 2014-05-18, 2014-05…
$ onset_date <date> 2014-05-13, 2014-05-13, 2014-05-16, 2014-05-18, 201…
$ hospital_date <date> 2014-05-15, 2014-05-14, 2014-05-18, 2014-05-20, 201…
$ date_of_outcome <date> NA, 2014-05-18, 2014-05-30, NA, 2014-05-29, 2014-05…
$ hospital_name <chr> "Other/Unknown", "Other/Unknown", "St. Mark's Matern…
$ outcome <chr> "Unknown", "Recover", "Recover", "Unknown", "Recover…
$ fever <chr> "no", "Unknown", "Unknown", "no", "no", "no", "Unkno…
$ chills <chr> "no", "Unknown", "Unknown", "no", "no", "no", "Unkno…
$ cough <chr> "yes", "Unknown", "Unknown", "no", "yes", "yes", "Un…
$ aches <chr> "no", "Unknown", "Unknown", "no", "no", "no", "Unkno…
$ vomit <chr> "yes", "Unknown", "Unknown", "no", "yes", "yes", "Un…
$ temp <dbl> 36.8, 36.9, 36.9, 36.8, 36.9, 37.6, 37.3, 37.0, 36.4…
Check the join
Always check the number of rows. A left join should not add rows — if it does, the key appears more than once in the second data frame.
👀 Follow along 6.4 — Check the join with nrow() and anti_join().
nrow(linelist_final)[1] 6451
nrow(combined_linelist)[1] 6451
# Cases with no matching case_info (should be 0)
anti_join(linelist_final, case_info, by = "case_id") %>% nrow()[1] 0
sum(!linelist_final$case_id %in% case_info$case_id) # the same check in base R[1] 0
# Is case_id unique in case_info?
case_info %>% get_dupes(case_id) %>% nrow()[1] 0
Same number of rows before and after, every case matched, and case_id is unique in case_info: the join worked.
Part 7: Exporting
Like import(), export() picks the file type from the extension:
| Format | Use it for | Watch out |
|---|---|---|
.rds |
Files you will open in R again | Only R can open it |
.csv |
Sharing — opens in Excel and any software | Column types are lost; missing text comes back as blank "" instead of NA |
.xlsx |
Sharing with Excel users | Same as .csv |
We save the combined dataset both ways: .rds to keep working in R, .csv to share.
👀 Follow along 7.1 — Save combined_linelist as .rds, and linelist_final, missing_ids, and combined_linelist as .csv, all in processed_data/.
# To keep working in R (Sessions 4–8 start from this file)
export(combined_linelist, here("processed_data", "combined_linelist.rds"))
# To share
export(linelist_final, here("processed_data", "linelist_final.csv"))
export(missing_ids, here("processed_data", "missing_ids.csv"))
export(combined_linelist, here("processed_data", "combined_linelist.csv"))- How many rows would
inner_join()give instead ofleft_join()for the measles data? Why? - Write all of your measles steps (Parts 4–6) as one pipeline.
- Brought your own data? Handle its missing values and duplicates, join a second dataset if you have one, and export the result.
See the solutions file for the answers.
Key points
- Not every
NAis a mistake. Count them withcolSums(is.na(data)), then decide: leave them, remove the rows (drop_na()), or replace them. - Set rows with no ID aside with new IDs (
filter(is.na(...)),paste0(),sprintf(),row_number()) instead of losing them. distinct()removes exact duplicate rows;get_dupes(id)finds IDs that appear more than once with different details.mutate(across(where(is.character), ~ replace_na(.x, "Unknown")))fills missing text in every text column at once;select()chooses and orders columns.left_join(main, other, by = "key")adds columns from a second dataset. Always check the row count and useanti_join()to find rows with no match.export()saves data:.rdsto keep working in R,.csvto share.
Next: Session 4 — Data structures. We look at how R stores data (vectors), pull out pieces with base R subsetting, and reshape data between wide and long with pivot_longer() and pivot_wider().