Session 2: Cleaning Your Data

The pipe, column names, fixing types and values, recoding categories, new variables, and saving clean data

NoteIn this session you will learn to
  1. Chain steps together with the pipe %>%
  2. Standardize column names with clean_names() and rename columns with rename()
  3. Fix column types (dates, numbers) with mutate()
  4. Fix values with if_else() and case_when()
  5. Create new variables (BMI, age groups)
  6. Combine every cleaning step into one pipeline and save the result

Before you start: open the workshop folder in Positron (File → Open Folder…) and open student_scripts/Session 2 - Cleaning_STUDENT.R. Today starts from the raw files, raw_data/linelist_raw.xlsx and raw_data/measles_raw.csv, and works through the problem lists you wrote at the end of Session 1. If you fall behind, ready-made copies of today’s results (linelist_clean.rds and measles_clean.rds) are in processed_data/.

Where this session fits. Today covers Clean and Save and share (highlighted): you fix the problems you found in Session 1, then save the clean data so the next session can start from it.

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

You will build two cleaned datasets side by side: linelist_clean in the Follow along code, and measles_clean in the TRY ITs.

Part 1: Set up and recall the problem list

Every session starts the same way: open the project, load packages, import data.

library(tidyverse)
library(here)
library(rio)
library(janitor)
library(skimr)
Note🔍 What this code does
  • The same packages as in Session 1. Today janitor gives you clean_names(), and the tidyverse gives you the pipe, mutate(), if_else(), case_when(), and the str_ text functions.
  • On this page the start-up messages are hidden; in Positron you’ll see them in the Console. Messages are fine: only a line that starts with Error is a problem.
linelist_raw <- import(here("raw_data", "linelist_raw.xlsx"))
hosp_info    <- import(here("raw_data", "hosp_info.txt"))
Note🔍 What this code does
  • Both files are imported fresh from raw_data/, exactly as in Session 1. Today you clean a copy in R; the files themselves never change.
  • hosp_info is a small look-up table: one row for each of the 6 hospitals, with its correct name. You’ll use it in Part 6 to fix the misspelled hospital names.
  • The extra spaces before <- only line the two lines up so they’re easier to read. R ignores them.

The problem list from Session 1 is today’s to-do list:

# Problem Fixed in
1 Column names have spaces, dots, and capitals Part 3
2 Date columns are different types Part 4
3 age is text, and 37 ages are in months Parts 4–5
4 Hospital names are misspelled or inconsistent Part 6
5 Gender and outcome have missing values Session 3
6 Impossible weights and heights Parts 5, 7
7 130 rows have no Case ID Session 3
8 25 rows are duplicates Session 3
ImportantNever edit the raw data file

All fixes happen in code. The raw file stays exactly as it arrived, so anyone can re-run your script and get the same result.

Important✏️ TRY IT 1 (1 min): import the practice data

Import raw_data/measles_raw.csv and call it measles_raw.

✅ Check: in the Variables pane, measles_raw shows 1215 rows and 11 columns

measles_raw <- import(here("raw_data", "measles_raw.csv"))

Part 2: The pipe %>%

In Session 1, doing two things at once meant putting one function inside another:

head(arrange(linelist_raw, wt_kg), 3)
  Case ID infection date date.onset  hosp date date of outcome OUTCOME Gender
1  6056ba           <NA> 2014-10-20 2014-10-21      2014-11-05   Death      f
2  6056ba           <NA> 2012-10-20 2012-10-21      2012-11-05   Death      f
3  7400b3     2014-10-07 2014-10-12 2014-10-13      2014-10-18   Death      f
                 hospital age age_unit wt_kg ht_cm
1                    <NA>   0    years   -11    24
2              HMO Health   0    years   -11    24
3 Regional Medical Center   0    years   -10    13

You read that from the inside out: arrange the data, then take the head. Every extra step adds another layer of brackets. The pipe %>% writes the same steps in order, from left to right:

linelist_raw %>% arrange(wt_kg) %>% head(3)
  Case ID infection date date.onset  hosp date date of outcome OUTCOME Gender
1  6056ba           <NA> 2014-10-20 2014-10-21      2014-11-05   Death      f
2  6056ba           <NA> 2012-10-20 2012-10-21      2012-11-05   Death      f
3  7400b3     2014-10-07 2014-10-12 2014-10-13      2014-10-18   Death      f
                 hospital age age_unit wt_kg ht_cm
1                    <NA>   0    years   -11    24
2              HMO Health   0    years   -11    24
3 Regional Medical Center   0    years   -10    13
Note🔍 What this code does
  • Both code blocks do exactly the same thing and give the same 3 rows: the data sorted by weight, smallest first.
  • In the first block, R starts with the innermost function, arrange(), and hands its result to head().
  • In the second, linelist_raw %>% passes the data into arrange(wt_kg), and that result goes into head(3). You never write linelist_raw inside the brackets: the pipe fills in the first argument for you.
  • The first two rows share the Case ID 6056ba and both have a weight of -11 kg: an impossible value, and an ID that appears twice. You fix the weights in Part 5; repeated IDs and duplicates come up in Session 3.

The pipe takes the result on its left and passes it in as the first argument of the function on its right. Read it as “and then”: take linelist_raw, and then arrange it by weight, and then show the first 3 rows.

  • Keyboard shortcut: Shift + Cmd + M (Mac) or Shift + Ctrl + M (Windows)
  • With the pipe, you don’t write the data inside each function: count(linelist_raw, hospital) becomes linelist_raw %>% count(hospital)

Longer pipelines

Put each step on its own line, with the %>% at the end of the line. select() keeps only the columns you name:

linelist_raw %>%
  select(`Case ID`, wt_kg, ht_cm) %>%   # keep three columns
  arrange(wt_kg) %>%                    # sort by weight
  head(5)                               # show the first 5 rows
  Case ID wt_kg ht_cm
1  6056ba   -11    24
2  6056ba   -11    24
3  7400b3   -10    13
4  0fde59   -10    18
5  fca629    -9    24
Note🔍 What this code does
  • select(`Case ID`, wt_kg, ht_cm) keeps only these three columns, in this order. Case ID has a space in it, so it needs backticks.
  • arrange(wt_kg) sorts the rows by weight, smallest first, and head(5) keeps the first 5.
  • Each line ends with %>%, so R knows the next line continues the same pipeline. The text after # is a comment for people; R ignores it.
  • Nothing is saved: the result prints and is gone. To keep a result, start the pipeline with NEW_NAME <-.

That is three steps, readable top to bottom. Written the Session 1 way, it would be head(arrange(select(linelist_raw, ...), wt_kg), 5).

Note

You may also see |>, the pipe built into R itself. For this workshop they do the same thing.

WarningCommon mistakes
  • Put %>% at the end of a line, not at the start of the next one. R stops at a complete line, so a line starting with %>% gives an error.
  • Don’t end the last line with %>%: R then waits for another step (a + appears in the Console). Press Esc and remove it.
Important✏️ TRY IT 2 (3 min): the pipe

In Session 1 you wrote head(arrange(measles_raw, desc(`Temp (C)`)), 10). Rewrite it with the pipe, and add a select() step so you only see `Patient ID` and `Temp (C)`.

✅ Check: two columns, and the top temperature is 104.7

measles_raw %>%
  select(`Patient ID`, `Temp (C)`) %>%
  arrange(desc(`Temp (C)`)) %>%
  head(10)
   Patient ID Temp (C)
1     MS-0663    104.7
2     MS-0444    104.0
3     MS-0908    104.0
4     MS-0523    104.0
5     MS-0132    103.5
6     MS-0052    103.3
7     MS-1027    103.3
8     MS-0167    103.3
9     MS-0110    102.9
10    MS-0943    102.6

Same answer as in Session 1, but now you can read it in order. From here on, almost every piece of code in the workshop uses the pipe.

Note📖 Good to know
  • Most tidyverse functions take the data as their first argument. That’s what makes the pipe work: whatever comes out of one step goes straight into the first argument of the next.
  • The pipe %>% comes from the magrittr package, which the tidyverse loads for you.

Part 3: Column names

clean_names()

One function (from janitor) makes every name lower case, replaces spaces and dots with _, and removes symbols. "Case ID" becomes case_id — after this, you never need backticks again!

names(linelist_raw)
 [1] "Case ID"         "infection date"  "date.onset"      "hosp date"      
 [5] "date of outcome" "OUTCOME"         "Gender"          "hospital"       
 [9] "age"             "age_unit"        "wt_kg"           "ht_cm"          
linelist_clean <- linelist_raw %>%
  clean_names()

names(linelist_clean)
 [1] "case_id"         "infection_date"  "date_onset"      "hosp_date"      
 [5] "date_of_outcome" "outcome"         "gender"          "hospital"       
 [9] "age"             "age_unit"        "wt_kg"           "ht_cm"          
Note🔍 What this code does
  • The first names() shows the 12 original names, as in Session 1.
  • linelist_raw %>% clean_names() makes a copy of the data with tidied names, and linelist_clean <- saves it as a new object. linelist_raw is unchanged.
  • The second names() shows the result: "Case ID" became case_id, "date.onset" became date_onset, and "OUTCOME" became outcome. Only the names change; the data in the columns stays the same.

rename()

rename(new_name = old_name) — the new name goes on the left.

linelist_clean <- linelist_clean %>%
  rename(
    date_of_infection = infection_date,
    onset_date        = date_onset,
    hospital_date     = hosp_date
  )

names(linelist_clean)
 [1] "case_id"           "date_of_infection" "onset_date"       
 [4] "hospital_date"     "date_of_outcome"   "outcome"          
 [7] "gender"            "hospital"          "age"              
[10] "age_unit"          "wt_kg"             "ht_cm"            
Note🔍 What this code does
  • rename() changes three names. In each pair, the new name is on the left of = and the current name on the right.
  • All other columns keep their names, and there are still 12 columns.
  • linelist_clean <- linelist_clean %>% ... takes the current version, changes it, and saves it back under the same name.
TipOne step at a time

We build the clean data step by step. Each step takes the current version, changes one thing, and saves it back with <-:

linelist_clean <- linelist_clean %>% STEP()

If your object gets into a bad state, re-run from the first step (linelist_clean <- linelist_raw %>% clean_names()). In Part 8 we put all the steps into one pipeline.

Important✏️ TRY IT 3 (2 min): clean the measles column names

Create measles_clean from measles_raw with clean_names(). What did `Temp (C)` and `Vaccinated?` turn into?

✅ Check: names(measles_clean) includes "temp_c" and "vaccinated"

measles_clean <- measles_raw %>%
  clean_names()

names(measles_clean)
 [1] "patient_id"       "report_date"      "district"         "sex"             
 [5] "age_years"        "vaccinated"       "fever"            "rash"            
 [9] "temp_c"           "days_in_hospital" "outcome"         

`Temp (C)` became temp_c and `Vaccinated?` became vaccinated: the parentheses and the ? are removed, and the space becomes _.

Note📖 Good to know
  • The names clean_names() makes are in snake_case: lower case, with words joined by _. Most R code uses this style, and you never need backticks for it.
  • clean_names() also makes repeated names unique (a second a becomes a_2) and puts an x in front of names that start with a number (2020 cases becomes x2020_cases), because R names can’t start with a number.

Part 4: Fixing types with mutate()

mutate() creates a new column or overwrites an existing one: mutate(column = what_to_put_in_it). Use the same name to overwrite a column; a new name to add one.

Dates

as.Date() converts text or date-times to dates.

linelist_clean <- linelist_clean %>%
  mutate(
    date_of_infection = as.Date(date_of_infection),
    onset_date        = as.Date(onset_date),
    hospital_date     = as.Date(hospital_date),
    date_of_outcome   = as.Date(date_of_outcome)
  )

linelist_clean %>% select(date_of_infection:date_of_outcome) %>% glimpse()
Rows: 6,604
Columns: 4
$ 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…
Note🔍 What this code does
  • Inside mutate(), each line column = as.Date(column) overwrites one column with a date version of itself. Four columns are converted in one mutate(), separated by commas.
  • onset_date was text ("2014-05-13") and the other three were date-times from Excel. as.Date() turns both kinds into plain dates.
  • select(date_of_infection:date_of_outcome) uses : to pick every column from date_of_infection to date_of_outcome. glimpse() then shows that all four are now <date>.
  • Missing dates stay missing (NA).
NoteDates written in other formats

as.Date() expects year-month-day ("2014-05-13"). For other formats, the lubridate package (part of the tidyverse) has functions named after the order: dmy("13/05/2014"), mdy("05/13/2014"), ymd("2014-05-13").

Numbers stored as text

as.numeric() turns text like "56" into the number 56. Text that isn’t a number (like "unknown") becomes NA, and R warns you: “NAs introduced by coercion”. That warning is fine if you expected it.

linelist_clean <- linelist_clean %>%
  mutate(age = as.numeric(age))

class(linelist_clean$age)
[1] "numeric"
Note🔍 What this code does
  • mutate(age = as.numeric(age)) replaces the text column age with numbers.
  • class() tells you what kind of values a column holds: now "numeric".
  • There’s no warning here, because every filled-in age was a number written as text. The 100 ages that were already missing stay NA. In the measles data (TRY IT 4), “unknown” can’t become a number, so you will see the warning there.
WarningCommon mistakes
  • Forgetting the <- at the start: the result prints in the Console but isn’t saved.
Important✏️ TRY IT 4 (3 min): fix the measles types

In measles_clean:

  1. make report_date a date with as.Date()
  2. make age_years a number with as.numeric(). Which values became NA?

✅ Check: summary(measles_clean$age_years) shows a maximum of 999. Suspicious!

measles_clean <- measles_clean %>%
  mutate(
    report_date = as.Date(report_date),
    age_years   = as.numeric(age_years)   # "unknown" becomes NA (the warning is expected)
  )

summary(measles_clean$age_years)
   Min. 1st Qu.  Median    Mean 3rd Qu.    Max.    NA's 
   0.00    4.00    7.00   12.13   12.00  999.00      27 

The 27 "unknown" ages became NA — that is what the warning is about. A maximum age of 999 is not a real age; we fix it in the next part.

Note📖 Good to know
  • R stores a date as the number of days since 1 January 1970: 13 May 2014 is day 16203. That’s what lets you subtract dates or sort them. You’ll see these numbers in Part 5 when something goes wrong.
  • as.Date(), as.numeric(), and as.character() all follow the same pattern: as. plus the type you want.

Part 5: Fixing values with if_else()

if_else(condition, value_if_TRUE, value_if_FALSE) checks every row and fixes the values that meet one condition.

Ages in months → years

“If the unit is months, divide by 12; otherwise keep the age.”

linelist_clean <- linelist_clean %>%
  mutate(age_years = if_else(age_unit == "months", age / 12, age))

linelist_clean %>%
  filter(age_unit == "months") %>%
  select(age, age_unit, age_years) %>%
  head()
  age age_unit age_years
1  24   months 2.0000000
2  NA   months        NA
3   7   months 0.5833333
4   7   months 0.5833333
5  24   months 2.0000000
6  24   months 2.0000000
Tip🧩 Reading a function call

Most cleaning steps in this workshop have the same shape as the code you just ran:

linelist_clean <- linelist_clean %>% mutate(age_years = if_else(age_unit == "months", age / 12, age))

Piece What it is What it does
linelist_clean <- the object and the assignment arrow saves the result back under the same name
linelist_clean %>% the data and the pipe passes the data into the next function
mutate(...) a function from dplyr (tidyverse) adds or changes a column
age_years = a named argument of mutate() the name of the new column
if_else(...) a function used as the value of that column works out a value for every row
age_unit == "months" the first argument of if_else(): a condition TRUE or FALSE for each row
age / 12 the second argument used where the condition is TRUE
age the third argument used where the condition is FALSE

R works it out from the inside out: for every row, if_else() checks the condition and picks age / 12 or age; mutate() puts the results in the new column age_years; and <- saves the changed data as linelist_clean.

Note🔍 What this code does
  • if_else() checks every row: if age_unit is "months", the new age_years is age / 12; otherwise it’s age, unchanged.
  • The second pipeline only checks the result and isn’t saved: it keeps the rows in months, shows three columns, and prints the first 6.
  • 24 months became 2 years and 7 months became 0.58 years. One of these patients has no age (NA), and it stays NA: missing in, missing out.

A 6-month-old baby would be counted as 6 years old. All 37 ages recorded in months would be too old, which would shift the age groups and the average age.

Impossible values → missing

Weights of zero or below are impossible. Instead of deleting the whole row (and losing the patient’s age, outcome, and dates), we replace only the bad value with NA.

linelist_clean <- linelist_clean %>%
  mutate(wt_kg = if_else(wt_kg <= 0, NA, wt_kg))

summary(linelist_clean$wt_kg)
   Min. 1st Qu.  Median    Mean 3rd Qu.    Max.    NA's 
   1.00   41.00   55.00   52.99   66.00  111.00      34 
Note🔍 What this code does
  • wt_kg <= 0 is TRUE for the 34 weights that are zero or below. For those, if_else() puts NA; every other weight is kept as it was.
  • summary() now shows a smallest weight of 1 kg and 34 NAs. The rows are still there, with all their other information.
Note🔁 The base R way: ifelse() (one letter shorter, one big difference)

Base R has ifelse(), and you will see it everywhere. It works the same way until you use it on dates. Say we want an “estimated onset” that uses the hospital date when the onset date is missing:

if_else(is.na(linelist_clean$onset_date), linelist_clean$hospital_date, linelist_clean$onset_date) %>% head(3)
[1] "2014-05-13" "2014-05-13" "2014-05-16"
ifelse(is.na(linelist_clean$onset_date), linelist_clean$hospital_date, linelist_clean$onset_date) %>% head(3)
[1] 16203 16203 16206
Note🔍 What this code does
  • is.na(linelist_clean$onset_date) is TRUE where the onset date is missing. Then the hospital date is used; otherwise the onset date.
  • This code runs outside mutate(), so each column is pulled out with $.
  • Both lines pick the same values. if_else() prints them as dates ("2014-05-13"); ifelse() prints 16203, the same date as a number of days since 1 January 1970.

ifelse() silently turned the dates into numbers (days since 1 January 1970). if_else() keeps the type, and gives an error instead of a surprise when the two values have different types. That strictness is why we use if_else() in this workshop.

Important✏️ TRY IT 5 (4 min): 999 and Fahrenheit

In measles_clean:

  1. 999 is a code for “unknown”: replace age_years of 999 with NA
  2. temperatures above 45 were entered in Fahrenheit. Convert them: Celsius = (Fahrenheit − 32) × 5 / 9, rounded to 1 decimal with round(..., 1)

✅ Check: summary() shows age_years max 52 and temp_c max 40.9

measles_clean <- measles_clean %>%
  mutate(
    age_years = if_else(age_years == 999, NA, age_years),
    temp_c    = if_else(temp_c > 45, round((temp_c - 32) * 5 / 9, 1), temp_c)
  )

summary(measles_clean$age_years)
   Min. 1st Qu.  Median    Mean 3rd Qu.    Max.    NA's 
  0.000   4.000   7.000   8.796  12.000  52.000      31 
summary(measles_clean$temp_c)
   Min. 1st Qu.  Median    Mean 3rd Qu.    Max. 
  36.00   38.40   38.90   38.82   39.40   40.90 

The four 999s are now NA (31 missing ages in total), and the 27 Fahrenheit temperatures are now in °C, so every temperature is between 36 and 40.9.

Note📖 Good to know
  • A comparison with a missing value gives a missing answer: NA <= 0 is NA, not TRUE or FALSE. So if_else() gives NA for those rows, and missing values stay missing unless you deal with them on purpose (Session 3).
  • Prefer NA to deleting rows. A patient with an impossible weight still has a real age, outcome, and dates that you want to keep.

Part 6: Recoding categories with case_when()

case_when() is if_else() for many conditions. R checks each line in order and uses the first one that is TRUE:

case_when(
  condition_1 ~ new_value_1,
  condition_2 ~ new_value_2,
  .default    = value_for_everything_else
)

Gender

linelist_clean <- linelist_clean %>%
  mutate(gender = case_when(
    gender == "m" ~ "male",
    gender == "f" ~ "female"
  ))

linelist_clean %>% count(gender)
  gender    n
1 female 3140
2   male 3147
3   <NA>  317
Note🔍 What this code does
  • case_when() checks each line in order: rows where gender is "m" get "male", rows where it’s "f" get "female".
  • The result overwrites the gender column. count(gender) checks it: 3140 female, 3147 male, and 317 NA.
  • The 317 missing values matched neither line, and there’s no .default, so they stay NA.

Any row that matches no condition becomes NA — here, that is the rows that were already missing.

Hospital names (using the look-up table)

hosp_info
                      hosp_name catchment_pop     level district beds
1              Central Hospital       1950280  Tertiary  Central  500
2             Military Hospital         10000 Secondary    North  100
3       Regional Medical center         50280 Secondary     West  200
4 St. Mark's Maternity Hospital         12000 Secondary     East   80
5                    HMO Health          5000   Primary    South   50
6               Premiere Health          4200   Primary  Central   40
  established
1        1985
2        1960
3        1995
4        2000
5        2010
6        2015
linelist_clean %>% count(hospital)
                               hospital    n
1                       Central Hopital   11
2                      Central Hospital  457
3                            HMO Health  288
4                      Military Hopital   32
5                     Military Hospital  800
6                      Mitylira Hopital    1
7                     Mitylira Hospital   79
8                                 Other  907
9                        Premier Health  288
10                          Regional MC   48
11              Regional Medical Center 1756
12 St. Mark's Maternity Hospital (SMMH)  417
13   St. Marks Maternity Hopital (SMMH)   11
14                                 <NA> 1509

str_detect(column, "pattern") is TRUE when the text contains the pattern, so one line catches every misspelling. | means “or”.

linelist_clean <- linelist_clean %>%
  mutate(hospital_name = case_when(
    str_detect(hospital, "Central")           ~ "Central Hospital",
    str_detect(hospital, "Military|Mitylira") ~ "Military Hospital",
    str_detect(hospital, "Regional")          ~ "Regional Medical center",
    str_detect(hospital, "Mark")              ~ "St. Mark's Maternity Hospital",
    str_detect(hospital, "HMO")               ~ "HMO Health",
    str_detect(hospital, "Premier")           ~ "Premiere Health",
    .default = "Other/Unknown"
  ))

# Check: every old value next to its new value
linelist_clean %>% count(hospital, hospital_name)
                               hospital                 hospital_name    n
1                       Central Hopital              Central Hospital   11
2                      Central Hospital              Central Hospital  457
3                            HMO Health                    HMO Health  288
4                      Military Hopital             Military Hospital   32
5                     Military Hospital             Military Hospital  800
6                      Mitylira Hopital             Military Hospital    1
7                     Mitylira Hospital             Military Hospital   79
8                                 Other                 Other/Unknown  907
9                        Premier Health               Premiere Health  288
10                          Regional MC       Regional Medical center   48
11              Regional Medical Center       Regional Medical center 1756
12 St. Mark's Maternity Hospital (SMMH) St. Mark's Maternity Hospital  417
13   St. Marks Maternity Hopital (SMMH) St. Mark's Maternity Hospital   11
14                                 <NA>                 Other/Unknown 1509
Note🔍 What this code does
  • hosp_info lists the 6 correct hospital names. count(hospital) shows 14 different values in the linelist, including misspellings and NA.
  • Each str_detect(hospital, "...") line is TRUE when the hospital name contains that piece of text, so "Central" catches both “Central Hospital” and “Central Hopital”. "Military|Mitylira" catches either spelling.
  • The result goes into a new column, hospital_name; the original hospital column stays as it was, so you can compare the two.
  • .default = "Other/Unknown" catches everything else: the hospital called “Other” and the missing values.
  • count(hospital, hospital_name) lists every old value next to its new one (14 rows), so you can check that each spelling landed in the right place.
NoteOther useful text functions (stringr)

str_to_lower(), str_to_upper(), str_to_title() change capitalization (str_to_title("north") gives "North") · str_trim() removes extra spaces · str_replace(x, "old", "new") replaces text. You may need these for the TRY ITs!

To check for any of several exact values, use %in%: sex %in% c("Male", "male", "M").

WarningCommon mistakes
  • Use ~ (tilde), not =, between the condition and the new value
  • Put a comma after each line except the last
  • Check every recode with count(old_column, new_column)
Important✏️ TRY IT 6 (4 min): sex and vaccinated

In measles_clean, recode

  • sex: Male / male / M → "male", Female / female / F → "female"
  • vaccinated: Yes / yes / Y → "Yes", No / no / N / NO → "No", Unknown → "Unknown"

Pattern: COLUMN %in% c("VALUE_1", "VALUE_2") ~ "NEW_VALUE"

✅ Check: count(sex) shows female 533, male 642, NA 40; count(vaccinated) shows only No, Unknown, Yes

measles_clean <- measles_clean %>%
  mutate(
    sex = case_when(
      sex %in% c("Male", "male", "M")     ~ "male",
      sex %in% c("Female", "female", "F") ~ "female"
    ),
    vaccinated = case_when(
      vaccinated %in% c("Yes", "yes", "Y")     ~ "Yes",
      vaccinated %in% c("No", "no", "N", "NO") ~ "No",
      vaccinated == "Unknown"                  ~ "Unknown"
    )
  )

measles_clean %>% count(sex)
     sex   n
1 female 533
2   male 642
3   <NA>  40
measles_clean %>% count(vaccinated)
  vaccinated   n
1         No 741
2    Unknown 183
3        Yes 291

Six spellings of sex became two values (the 40 missing stay NA), and eight spellings of vaccinated became three: No 741, Unknown 183, Yes 291.

Important✏️ TRY IT 7 (5 min): district

Make district one of North, South, East, West, Central.

  • Step 1: district = str_to_title(district) fixes the capitals
  • Step 2: case_when() + str_detect() fixes the misspellings (“Nroth”, “Cetnral”) and the “… District” versions. Keep NA as NA (no .default).

✅ Check: count(district) shows exactly 5 districts plus NA (34)

measles_clean <- measles_clean %>%
  mutate(
    district = str_to_title(district),
    district = case_when(
      str_detect(district, "North|Nroth")     ~ "North",
      str_detect(district, "South")           ~ "South",
      str_detect(district, "East")            ~ "East",
      str_detect(district, "West")            ~ "West",
      str_detect(district, "Central|Cetnral") ~ "Central"
    )
  )

measles_clean %>% count(district)
  district   n
1  Central 113
2     East 233
3    North 356
4    South 297
5     West 182
6     <NA>  34

str_to_title() turns “north” into “North” first, so each str_detect() line only has to catch the misspellings and the “… District” versions. Because there is no .default, the 34 missing districts stay NA.

Note📖 Good to know
  • str_detect() is case-sensitive: "north" does not contain "North". That’s why TRY IT 7 runs str_to_title() first.
  • All the stringr text functions start with str_. In Positron, type str_ and the list of them pops up.
  • The order of the lines in case_when() matters. If a value could match two lines, it gets the first one.

Part 7: Creating new variables

BMI

mutate() can calculate a new column from other columns.

linelist_clean <- linelist_clean %>%
  mutate(bmi = round(wt_kg / (ht_cm / 100)^2, 1))

summary(linelist_clean$bmi)
   Min. 1st Qu.  Median    Mean 3rd Qu.    Max.    NA's 
  10.90   24.60   32.10   48.09   49.88 1250.00      34 

That is impossible: the heights and weights don’t fit together. Set BMIs outside a plausible range (10–60) to NA.

linelist_clean <- linelist_clean %>%
  mutate(bmi = if_else(bmi < 10 | bmi > 60, NA, bmi))

summary(linelist_clean$bmi)
   Min. 1st Qu.  Median    Mean 3rd Qu.    Max.    NA's 
  10.90   23.20   28.70   30.58   36.50   60.00    1382 
Note🔍 What this code does
  • BMI is weight in kg divided by height in metres, squared. ht_cm / 100 turns centimetres into metres, ^2 squares it, and round(..., 1) keeps one decimal.
  • The first summary() shows a maximum BMI of 1250 and 34 NAs: the weights you set to NA in Part 5 give a missing BMI.
  • In the second block, bmi < 10 | bmi > 60 is TRUE when the BMI is below 10 or above 60. Those 1348 values become NA, so 1382 BMIs are missing in total, and the rest lie between 10.9 and 60.

About 1,400 BMIs are now NA, because many height and weight values in this teaching dataset are unrealistic. Setting only the bad value to NA (instead of filtering out those rows) keeps everything else we know about each patient.

Age groups

linelist_clean <- linelist_clean %>%
  mutate(age_cat = case_when(
    age_years < 18 ~ "Under 18",
    age_years < 30 ~ "18-29",
    age_years < 40 ~ "30-39",
    age_years < 50 ~ "40-49",
    age_years < 60 ~ "50-59",
    age_years < 70 ~ "60-69",
    age_years < 80 ~ "70-79",
    age_years >= 80 ~ "80+"
  ))

linelist_clean %>% count(age_cat)
   age_cat    n
1    18-29 1487
2    30-39  598
3    40-49  252
4    50-59   78
5    60-69   27
6    70-79    6
7      80+    1
8 Under 18 4055
9     <NA>  100
Note🔍 What this code does
  • Each line checks one upper limit. A patient aged 25 fails age_years < 18, passes age_years < 30, and gets “18-29”. case_when() stops at the first TRUE line.
  • The last line, age_years >= 80, is written out in full so that missing ages match no line and stay NA.
  • count(age_cat) shows most patients are children: 4055 are under 18. The 100 patients with no age are NA.

Because case_when() stops at the first TRUE line, age_years < 30 only catches ages 18–29 (younger ages were already caught by the line above).

count() lists the groups in alphabetical order (“18-29” before “Under 18”). In Session 5 you will learn how to put them in a sensible order with factors.

Note🔁 The base R way: cut()

Base R’s cut() makes groups from numbers by giving the break points. right = FALSE means each group includes its lower limit (18 goes into “18-29”):

cut(linelist_clean$age_years,
    breaks = c(0, 18, 30, 40, 50, 60, 70, 80, Inf),
    labels = c("Under 18", "18-29", "30-39", "40-49", "50-59", "60-69", "70-79", "80+"),
    right  = FALSE) %>%
  table(useNA = "ifany")
.
Under 18    18-29    30-39    40-49    50-59    60-69    70-79      80+ 
    4055     1487      598      252       78       27        6        1 
    <NA> 
     100 
Note🔍 What this code does
  • breaks gives the edges of the groups: 0, 18, 30, … and Inf (infinity), so there’s no upper limit for the last group.
  • labels names the 8 groups, in order. There is always one label fewer than there are breaks.
  • right = FALSE puts each break into the group above it, so 18 goes into “18-29”.
  • The result is piped into table(useNA = "ifany"), which counts each group, including the missing ages.

The same counts as case_when(), and already in the right order, because cut() returns a factor (Session 5). Forgetting right = FALSE is the classic mistake: then an 18-year-old lands in “Under 18”.

WarningCommon mistakes

Ending with TRUE ~ "80+" (or .default = "80+") puts every patient with a missing age into the 80+ group. Write the last condition out in full (age_years >= 80 ~ "80+") so missing ages stay NA.

Important✏️ TRY IT 8 (4 min): outcome and age groups

In measles_clean:

  1. make outcome consistent with str_to_title()
  2. create age_cat: “Under 5”, “5-9”, “10-14”, “15+”

✅ Check: count(outcome) shows only Died, Recovered, NA; count(age_cat) shows 4 groups plus NA (31)

measles_clean <- measles_clean %>%
  mutate(
    outcome = str_to_title(outcome),
    age_cat = case_when(
      age_years < 5   ~ "Under 5",
      age_years < 10  ~ "5-9",
      age_years < 15  ~ "10-14",
      age_years >= 15 ~ "15+"
    )
  )

measles_clean %>% count(outcome)
    outcome    n
1      Died   52
2 Recovered 1095
3      <NA>   68
measles_clean %>% count(age_cat)
  age_cat   n
1   10-14 232
2     15+ 205
3     5-9 368
4 Under 5 379
5    <NA>  31

“died” and “recovered” now match “Died” and “Recovered” (52 died, 1095 recovered, 68 missing). The 31 missing ages (the 27 “unknown” plus the four 999s) stay NA because the last condition is written out in full.

Note📖 Good to know
  • R follows the usual maths order: brackets first, then ^, then * and /, then + and -. So wt_kg / (ht_cm / 100)^2 divides the weight by the squared height.
  • | means “or” and & means “and”. You can combine as many conditions as you need.

Part 8: One pipeline and saving your work

Here is every step as one pipeline. This is the version to keep in a real project: one block that goes from raw data to clean data, so anyone can re-run it and get exactly the same result.

linelist_clean <- linelist_raw %>%
  clean_names() %>%
  rename(
    date_of_infection = infection_date,
    onset_date        = date_onset,
    hospital_date     = hosp_date
  ) %>%
  mutate(
    # Types
    date_of_infection = as.Date(date_of_infection),
    onset_date        = as.Date(onset_date),
    hospital_date     = as.Date(hospital_date),
    date_of_outcome   = as.Date(date_of_outcome),
    age               = as.numeric(age),
    # Values
    age_years = if_else(age_unit == "months", age / 12, age),
    wt_kg     = if_else(wt_kg <= 0, NA, wt_kg),
    gender    = case_when(
      gender == "m" ~ "male",
      gender == "f" ~ "female"
    ),
    hospital_name = case_when(
      str_detect(hospital, "Central")           ~ "Central Hospital",
      str_detect(hospital, "Military|Mitylira") ~ "Military Hospital",
      str_detect(hospital, "Regional")          ~ "Regional Medical center",
      str_detect(hospital, "Mark")              ~ "St. Mark's Maternity Hospital",
      str_detect(hospital, "HMO")               ~ "HMO Health",
      str_detect(hospital, "Premier")           ~ "Premiere Health",
      .default = "Other/Unknown"
    ),
    # New variables
    bmi = round(wt_kg / (ht_cm / 100)^2, 1),
    bmi = if_else(bmi < 10 | bmi > 60, NA, bmi),
    age_cat = case_when(
      age_years < 18 ~ "Under 18",
      age_years < 30 ~ "18-29",
      age_years < 40 ~ "30-39",
      age_years < 50 ~ "40-49",
      age_years < 60 ~ "50-59",
      age_years < 70 ~ "60-69",
      age_years < 80 ~ "70-79",
      age_years >= 80 ~ "80+"
    )
  )

glimpse(linelist_clean)
Rows: 6,604
Columns: 16
$ case_id           <chr> "5fe599", "8689b7", "11f8ea", "b8812a", "893f25", "b…
$ 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…
$ outcome           <chr> NA, "Recover", "Recover", NA, "Recover", "Recover", …
$ gender            <chr> "male", "female", "male", "female", "male", "female"…
$ hospital          <chr> "Other", NA, "St. Mark's Maternity Hospital (SMMH)",…
$ age               <dbl> 2, 3, 56, 18, 3, 16, 16, 0, 61, 27, 12, 42, 19, 7, 7…
$ age_unit          <chr> "years", "years", "years", "years", "years", "years"…
$ 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…
$ age_years         <dbl> 2, 3, 56, 18, 3, 16, 16, 0, 61, 27, 12, 42, 19, 7, 7…
$ hospital_name     <chr> "Other/Unknown", "Other/Unknown", "St. Mark's Matern…
$ bmi               <dbl> NA, NA, 16.1, 22.5, NA, 41.6, NA, NA, 16.8, 22.8, 53…
$ age_cat           <chr> "Under 18", "Under 18", "50-59", "18-29", "Under 18"…
Note🔍 What this code does
  • This pipeline starts again from linelist_raw, so it doesn’t depend on anything you ran before. Running this one block gives you the whole clean dataset.
  • It does every step from Parts 3 to 7, in order: clean_names(), then rename(), then one big mutate(). The # comments mark the groups of steps.
  • glimpse() shows the result: 6604 rows and 16 columns, the original 12 plus age_years, hospital_name, bmi, and age_cat.

Inside one mutate(), later lines can use columns made on earlier lines (bmi is created, then fixed).

Save your work

export() is the opposite of import(). Save cleaned data to processed_data/ — never over the raw file.

export(linelist_clean, here("processed_data", "linelist_clean.rds"))
Note🔍 What this code does
  • export() takes the data first, then the path to save it to. Like import(), it picks the file type from the ending: .rds here.
  • Nothing prints. The file appears in the processed_data/ folder in the Explorer.
  • If a file with that name already exists (like the ready-made copy), export() replaces it without asking.

.rds is R’s own file format. It keeps everything exactly as it is in R — dates stay dates and missing values stay NA — so it’s the best choice for a file you will open in R again. (We compare .rds and .csv in Session 3.)

Session 3 starts from this file.

Important✏️ TRY IT 9 (2 min): save the measles data

Save measles_clean as processed_data/measles_clean.rds. You need this file in Session 3!

✅ Check: the file appears in the processed_data folder (Explorer, on the left)

export(measles_clean, here("processed_data", "measles_clean.rds"))

Same pattern as for linelist_clean: export(DATA, here("FOLDER", "FILE_NAME.rds")). Session 3 starts by importing this file.

Tip⭐ Bonus
  1. Rewrite all of your measles TRY ITs as one pipeline that starts from measles_raw (like Part 8). It should give the same result — see the solutions file.
  2. Brought your own data? Fix the problems on your own problem list, then export() the clean version to processed_data/.
Note📖 Good to know
  • Keeping the whole cleaning in one script means anyone can see every change you made to the data, and rerun it if the raw data is updated.
  • Use .rds for files you’ll open in R again, and .csv or .xlsx for sharing with people who use Excel. export() makes any of them: just change the ending.

Key points

  • The pipe %>% means “and then”: data %>% step_1() %>% step_2() reads in order, left to right.
  • Never edit the raw data file — every fix happens in code, so anyone can re-run it.
  • clean_names() fixes every column name at once; rename(new = old) changes one.
  • mutate() creates or overwrites columns; as.Date() and as.numeric() fix types.
  • if_else() fixes values that meet one condition; case_when() handles many, checked in order. Use NA to blank out impossible values instead of deleting rows.
  • str_detect() and str_to_title() catch misspellings and inconsistent capitals; check every recode with count(old, new).
  • Keep one pipeline from raw to clean data, and save the result with export() as .rds in processed_data/.

Next: Session 3 — Missing Data, Joins, and Export. We deal with rows that have no Case ID, duplicates, and missing values, join extra case information, and save the final datasets.