Session 3: Missing Data, Joins, and Export

Missing values, rows with no ID, duplicates, replace_na() and across(), select(), joins, and exporting

NoteIn this session you will learn to
  1. Find where data are missing and decide what to do about it
  2. Set aside rows with no ID, and remove duplicate rows
  3. Replace missing values and choose which columns to keep
  4. Combine two datasets with a join, and check that the join worked
  5. 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).

  1. Import
  2. Inspect
  3. Clean
  4. Combine
  5. Reshape
  6. Summarize
  7. Visualize
  8. Save and share
  9. Automate

Today you finish two datasets side by side, so they are ready for analysis:

Part 1: Set up

library(tidyverse)
library(here)
library(rio)
library(janitor)
library(skimr)
Note🔍 What this code does
  • The same five library() lines as last session. Packages stay loaded only until you close Positron or restart R, so every session starts with them.
linelist_clean <- import(here("processed_data", "linelist_clean.rds"), trust = TRUE)
case_info      <- import(here("raw_data", "case_info.csv"))
Note🔍 What this code does
  • linelist_clean is the cleaned linelist you saved at the end of Session 2: 6604 rows and 16 columns, with the dates still dates and the missing values still NA. That’s what .rds files keep.
  • case_info comes straight from the raw data: 5888 rows and 7 columns. It has one row per case, with its case_id and its symptoms (fever, chills, cough, aches, vomit) and temperature (temp). We join it to the linelist in Part 6.
  • The extra spaces after case_info only line up the <- arrows to make the code easier to read. R ignores them.
NoteWhy 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
Important✏️ TRY IT 1 (2 min): import the measles data

Import processed_data/measles_clean.rds (as measles_clean, with trust = TRUE) and raw_data/district_info.csv (as district_info).

✅ Check: measles_clean has 1215 rows; district_info has 5 rows

measles_clean <- import(here("processed_data", "measles_clean.rds"), trust = TRUE)
district_info <- import(here("raw_data", "district_info.csv"))

nrow(measles_clean)
[1] 1215
district_info
  district population n_clinics
1    North     185000        12
2    South     142000         9
3     East      98000         7
4     West      76500         5
5  Central     210000        15

district_info has one row per district, with its population and number of clinics. We join it to the measles data in Part 6. If your measles_clean.rds is missing or broken, download a fresh copy of the workshop folder (ZIP button above).

Part 2: Where is data missing?

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 
Note🔍 What this code does
  • is.na() gives TRUE for every missing cell; colSums() adds up each column (TRUE counts as 1). You get one number per column.
  • case_id is missing for 130 rows, outcome for 1493, and bmi for 1382: the impossible BMIs you set to NA in Session 2 are counted here too.
  • hospital_name has 0, because in Session 2 every missing or unclear hospital became “Other/Unknown”; the original hospital column still has its 1509 NAs.

No!

  • date_of_outcome is missing for patients who have not had an outcome yet — that is real information
  • bmi is missing where we set impossible values to NA in Session 2
  • case_id missing is a problem: we can’t link these rows to anything else
NoteThree common ways to handle missing values
  1. Leave them as NA — most R functions have na.rm = TRUE to skip them (e.g., mean(x, na.rm = TRUE))
  2. Remove the rows — drop_na(column) removes rows missing that column. Use carefully: you may lose a lot of data
  3. Replace them — e.g., with “Unknown” for categories, so they show up in tables. Never invent numbers without a statistical method (imputation)
# 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.

Note🔍 What this code does
  • nrow(linelist_clean) counts all rows: 6604.
  • linelist_clean %>% drop_na(bmi) %>% nrow() removes every row where bmi is missing, then counts what’s left: 5222.
  • Nothing is saved, because there is no <-. This only shows what drop_na() would do, so you can decide before you lose data.
Important✏️ TRY IT 2 (2 min): missing values in the measles data

Count the missing values in each column of measles_clean. Which column has missing values that are not a mistake?

✅ Check: days_in_hospital has the most NAs (766)

colSums(is.na(measles_clean))
      patient_id      report_date         district              sex 
              37               29               34               40 
       age_years       vaccinated            fever             rash 
              31                0                0                0 
          temp_c days_in_hospital          outcome          age_cat 
               0              766               68               31 

days_in_hospital (766 NAs): patients who were never hospitalized have no hospital days. Missing is not always an error!

Note📖 Good to know
  • R has one missing-value marker, NA, but a dataset often hides missing values in other ways: an empty cell, a space, “N/A”, “unknown”, or a code like 999 (as in the measles ages). Look for those with count() and turn them into NA before counting what’s missing.
  • Most summary functions return NA if any value is missing: mean(c(1, NA, 3)) is NA. Add na.rm = TRUE to skip the missing values: mean(c(1, NA, 3), na.rm = TRUE) is 2.

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.

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 row
  • sprintf("%05d", 1) pads a number with zeros to 5 digits: "00001"
  • paste0() glues text together: "ID" + "00001" = "ID00001"
Note🔍 What this code does
  • filter(is.na(case_id)) keeps only the 130 rows with no ID.
  • mutate(case_id = ...) overwrites the empty case_id column with new IDs, built from the three functions above: ID00001 for the first row up to ID00130 for the last.
  • The new IDs start with “ID” so nobody can mistake them for real case IDs.
  • The last two lines show 6 of the rows (only four columns, so they fit) and count them: 130.
Important✏️ TRY IT 3 (3 min): measles rows with no ID

Create measles_missing_ids: the rows of measles_clean with no patient_id, with new IDs NEW-0001, NEW-0002, …

Pattern: paste0("NEW-", sprintf("%04d", row_number()))

✅ Check: measles_missing_ids has 37 rows

measles_missing_ids <- measles_clean %>%
  filter(is.na(patient_id)) %>%
  mutate(patient_id = paste0("NEW-", sprintf("%04d", row_number())))

nrow(measles_missing_ids)
[1] 37

These are the same 37 rows with no Patient ID you found in Session 1. "%04d" pads to 4 digits, so the IDs are NEW-0001 to NEW-0037.

Part 4: Duplicates

distinct() keeps one copy of each row that appears more than once.

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
Note🔍 What this code does
  • ! means “not”, so !is.na(case_id) is TRUE for rows that have an ID. filter() keeps those: 6604 − 130 = 6474 rows.
  • distinct() then keeps one copy of every row that appears more than once, leaving 6451. That’s 23 copies removed. (Session 1 found 25 duplicate rows; the other 2 were among the rows with no ID, which are already set aside in missing_ids.)
  • The two nrow() lines show the counts before (6604) and after (6451).

Removing exact copies is safe. But some IDs still appear more than once, with different details:

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.

Note🔍 What this code does
  • get_dupes(case_id) (janitor) checks only the case_id column, not the whole row. It returns every row whose ID appears more than once, with a dupe_count column saying how many times.
  • The first line counts those rows: 1106 rows, sharing 543 IDs.
  • The second pipeline shows the first 6, with a few columns picked by select(), so you can compare the records that share an ID.
Important✏️ TRY IT 4 (3 min): measles duplicates

Create measles_final from measles_clean: keep rows that have a patient_id, then remove exact duplicates. How many rows did distinct() remove?

✅ Check: 1178 rows after filter(), 1163 after distinct()

measles_final <- measles_clean %>%
  filter(!is.na(patient_id))

nrow(measles_final)
[1] 1178
measles_final <- measles_final %>%
  distinct()

nrow(measles_final)
[1] 1163

distinct() removed 15 rows (1178 − 1163) — the 15 duplicates you found in Session 1.

Note📖 Good to know
  • Two kinds of duplicates need different handling. Exact copies (every column the same) are usually the same record entered twice, so distinct() can remove them safely. Repeated IDs with different details may be two patients given the same ID, or one patient updated later. Don’t delete those without checking.
  • distinct(case_id, .keep_all = TRUE) would keep only the first row for each ID. It’s quick, but it decides for you which record survives.

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".

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.

Note🔍 What this code does
  • where(is.character) picks the text columns: case_id, outcome, gender, hospital, age_unit, hospital_name, and age_cat.
  • across(...) runs replace_na(.x, "Unknown") on each of them, and mutate() saves the result back into the same columns.
  • count(gender) now shows Unknown 304 instead of NA, and count(outcome) shows Unknown 1453. The numbers didn’t change, only the label.
  • Why bother? Text “Unknown” shows up in tables and plots as a category of its own, while NA is easy to drop by accident (as table() did in Session 1).
Note

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.

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…
Note🔍 What this code does
  • select() keeps the 13 columns you list, in that order, and drops the other 3 (hospital, age, and age_unit).
  • A long list can go over several lines. R knows the command isn’t finished until the closing bracket.
  • glimpse() confirms the result: 6451 rows and 13 columns, with each column’s type.
NoteOther ways to use 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)
Important✏️ TRY IT 5 (3 min): replace missing text

Replace NA with “Unknown” in every text column of measles_final. Why does days_in_hospital still have NAs?

✅ Check: count(district) now shows Unknown (34) instead of NA

measles_final <- measles_final %>%
  mutate(across(where(is.character), ~ replace_na(.x, "Unknown")))

measles_final %>% count(district)
  district   n
1  Central 110
2     East 223
3    North 342
4    South 284
5  Unknown  34
6     West 170

days_in_hospital is a number, not text, so where(is.character) skips it. That’s what we want: “Unknown” is not a number of days.

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:

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  
Note🔍 What this code does
  • tibble() makes a small data frame, just like data.frame() in Session 1.
  • patients has ids 1, 2, 3 and labs has ids 2, 3, 4, so ids 2 and 3 are in both, 1 is only in patients, and 4 is only in labs.
  • left_join() keeps all 3 patients; Ana (id 1) gets NA for result because she has no lab. inner_join() keeps only the 2 ids in both. full_join() keeps all 4 ids. anti_join() returns the 1 patient with no lab.
  • by = "id" names the key: the column used to match rows. It must exist in both data frames.
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.

Note🔁 The base R way: merge() and %in%

Base R joins with one function, merge(). The all arguments choose the type of join:

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  
Note🔍 What this code does
  • merge(patients, labs, by = "id") gives 2 rows: like inner_join(), it keeps only the matches unless you ask for more. all.x = TRUE keeps every row of the first data frame (a left join), and all = TRUE keeps every row of both (a full join).
  • patients$id %in% labs$id checks each patient id against the lab ids: FALSE TRUE TRUE (only id 1 has no lab).
  • ! flips that to TRUE FALSE FALSE, and patients[ROWS, ] keeps the rows where it’s TRUE: Ana. The empty space after the comma means “all columns”. You’ll learn this bracket style in Session 4.

%in% is worth learning well: you will use it inside filter() too, for example filter(district %in% c("North", "South")).

Join the case information

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…
Tip🧩 Reading a function call

Here is the join you just ran, taken apart:

combined_linelist <- linelist_final %>% left_join(case_info, by = "case_id")

Piece What it is What it does
combined_linelist <- a new object and the assignment arrow saves the joined data under a new name, so linelist_final stays as it was
linelist_final %>% the main data frame, then the pipe passes linelist_final in as the first argument of left_join(): the data frame whose rows are all kept
left_join(...) a function from dplyr (part of the tidyverse) adds columns from a second data frame to the first
case_info the second argument the data frame the new columns come from
by = "case_id" a named argument the key: rows are matched where case_id is the same in both

Without the pipe, the same line is left_join(linelist_final, case_info, by = "case_id"). Read it aloud as: “combined_linelist gets: take linelist_final, and then left-join case_info to it, matching on case_id.”

Note🔍 What this code does
  • glimpse(case_info) shows the 7 columns you’re about to add: case_id plus the symptoms and temp.
  • The join goes through linelist_final row by row, finds the row in case_info with the same case_id, and copies its other 6 columns across.
  • combined_linelist has 6451 rows (the same as linelist_final) and 19 columns: the 13 you kept plus 6 from case_info. case_id appears only once.

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.

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.

Note🔍 What this code does
  • The two nrow() lines both give 6451: the join added columns, not rows.
  • anti_join(linelist_final, case_info, by = "case_id") returns the cases that found no match in case_info; nrow() counts them: 0. The %in% line does the same check in base R and also gives 0.
  • get_dupes(case_id) on case_info finds no repeated IDs (it prints “No duplicate combinations found” and returns 0 rows). That matters: if an ID appeared twice in case_info, the left join would copy that case into two rows.
Important✏️ TRY IT 6 (4 min): join the district information

Add district_info to measles_final with left_join() (the key is district). Did the number of rows change? Which rows got no population, and why?

Pattern: anti_join(measles_final, district_info, by = "district") %>% count(district)

✅ Check: still 1163 rows; the 34 “Unknown” district rows have no population

measles_final <- measles_final %>%
  left_join(district_info, by = "district")

nrow(measles_final)
[1] 1163
anti_join(measles_final, district_info, by = "district") %>% count(district)
  district  n
1  Unknown 34

The number of rows did not change. district_info has no “Unknown” district, so those 34 rows have no match and their population is NA.

Note📖 Good to know
  • Before any join, ask two questions: Is the key spelled and formatted the same way in both data frames (“ID001” won’t match “id001” or “ID 001”)? And is the key unique in the data frame you’re adding? If not, rows multiply.
  • When the key has different names in the two data frames, give both: by = c("case_id" = "id").
  • Joins are how you link different sources: lab results to patients, census figures to counties, survey answers to participants.

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.

# 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"))
Note🔍 What this code does
  • export(DATA, PATH) writes a data frame to a file. Like import(), it chooses the format from the ending you give: .rds or .csv.
  • here("processed_data", ...) builds the path, so the files land in processed_data/ on any computer. If a file with that name already exists, it’s replaced without asking.
  • Nothing prints. Look in the Explorer: four files in processed_data/. The .csv copy of combined_linelist is about six times bigger than the .rds copy (about 780 KB vs 130 KB), because .rds files are compressed.
Important✏️ TRY IT 7 (2 min): save the measles data

Save measles_final as processed_data/measles_final.rds and measles_final.csv, and measles_missing_ids as processed_data/measles_missing_ids.csv.

✅ Check: three new files in processed_data/ (look in the Explorer). You need measles_final.rds in Sessions 4–8!

export(measles_final,       here("processed_data", "measles_final.rds"))
export(measles_final,       here("processed_data", "measles_final.csv"))
export(measles_missing_ids, here("processed_data", "measles_missing_ids.csv"))

The .rds file keeps the dates, number columns, and NAs exactly as they are, so every later session starts from it. The .csv files are for sharing.

Tip⭐ Bonus
  1. How many rows would inner_join() give instead of left_join() for the measles data? Why?
  2. Write all of your measles steps (Parts 4–6) as one pipeline.
  3. 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 NA is a mistake. Count them with colSums(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 use anti_join() to find rows with no match.
  • export() saves data: .rds to keep working in R, .csv to 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().