library(tidyverse)
library(here)
library(rio)
library(janitor)
library(skimr)Session 2: Cleaning Your Data
The pipe, column names, fixing types and values, recoding categories, new variables, and saving clean data
- Chain steps together with the pipe
%>% - Standardize column names with
clean_names()and rename columns withrename() - Fix column types (dates, numbers) with
mutate() - Fix values with
if_else()andcase_when() - Create new variables (BMI, age groups)
- 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.
- Import
- Inspect
- Clean
- Combine
- Reshape
- Summarize
- Visualize
- Save and share
- 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.
👀 Follow along 1.1 — Load today’s packages: tidyverse, here, rio, janitor, and skimr.
👀 Follow along 1.2 — Import raw_data/linelist_raw.xlsx (linelist_raw) and raw_data/hosp_info.txt (hosp_info).
linelist_raw <- import(here("raw_data", "linelist_raw.xlsx"))
hosp_info <- import(here("raw_data", "hosp_info.txt"))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 |
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.
Part 2: The pipe %>%
In Session 1, doing two things at once meant putting one function inside another:
👀 Follow along 2.1 — Sort by weight and show 3 rows: first with one function inside another, then with the pipe (the next two code blocks).
Pattern: DATA %>% STEP_1() %>% STEP_2()
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
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)becomeslinelist_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:
👀 Follow along 2.2 — A longer pipeline, one step per line: select() Case ID, wt_kg, and ht_cm, then arrange() by wt_kg, then head(5).
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
That is three steps, readable top to bottom. Written the Session 1 way, it would be head(arrange(select(linelist_raw, ...), wt_kg), 5).
You may also see |>, the pipe built into R itself. For this workshop they do the same thing.
- 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.
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!
👀 Follow along 3.1 — Create linelist_clean from linelist_raw with clean_names(). Compare names() before and after.
Pattern: NEW_DATA <- OLD_DATA %>% clean_names()
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"
rename()
rename(new_name = old_name) — the new name goes on the left.
👀 Follow along 3.2 — Rename infection_date → date_of_infection, date_onset → onset_date, and hosp_date → hospital_date.
Pattern: DATA <- DATA %>% rename(NEW_NAME = OLD_NAME, NEW_NAME = OLD_NAME)
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"
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.
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.
👀 Follow along 4.1 — Turn date_of_infection, onset_date, hospital_date, and date_of_outcome into dates with as.Date().
Pattern: DATA <- DATA %>% mutate(COLUMN = as.Date(COLUMN), COLUMN = as.Date(COLUMN))
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…
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.
👀 Follow along 4.2 — Turn age into a number with as.numeric().
linelist_clean <- linelist_clean %>%
mutate(age = as.numeric(age))
class(linelist_clean$age)[1] "numeric"
- Forgetting the
<-at the start: the result prints in the Console but isn’t saved.
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.”
👀 Follow along 5.1 — Create age_years (ages in months divided by 12).
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
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.
👀 Follow along 5.2 — Replace weights of 0 or below 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
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:
🔁 Follow along 5.3 — Estimated onset (hospital_date when onset_date is missing) with if_else() and with ifelse(). Compare the first 3 values.
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
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.
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
👀 Follow along 6.1 — Recode gender "m" → "male" and "f" → "female". Check with count().
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
Any row that matches no condition becomes NA — here, that is the rows that were already missing.
Hospital names (using the look-up table)
👀 Follow along 6.2 — Look at hosp_info and count(hospital), then create hospital_name with case_when() + str_detect(). Check with count(hospital, hospital_name) (the next two code blocks).
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
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").
- 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)
Part 7: Creating new variables
BMI
mutate() can calculate a new column from other columns.
👀 Follow along 7.1 — Calculate bmi, then set BMIs below 10 or above 60 to NA (the next two code blocks).
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
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
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
👀 Follow along 7.2 — Create age_cat: Under 18, 18-29, 30-39, 40-49, 50-59, 60-69, 70-79, 80+.
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
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.
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”):
🔁 Follow along 7.3 — The same groups with cut(). Check with table(useNA = "ifany").
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
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”.
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.
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.
👀 Follow along 8.1 — One pipeline from linelist_raw to linelist_clean.
Pattern: linelist_clean <- linelist_raw %>% clean_names() %>% rename(...) %>% mutate(...)
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"…
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.
👀 Follow along 8.2 — Save linelist_clean as processed_data/linelist_clean.rds.
Pattern: export(DATA, here("FOLDER", "FILE_NAME.rds"))
export(linelist_clean, here("processed_data", "linelist_clean.rds")).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.
- 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. - Brought your own data? Fix the problems on your own problem list, then
export()the clean version toprocessed_data/.
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()andas.numeric()fix types.if_else()fixes values that meet one condition;case_when()handles many, checked in order. UseNAto blank out impossible values instead of deleting rows.str_detect()andstr_to_title()catch misspellings and inconsistent capitals; check every recode withcount(old, new).- Keep one pipeline from raw to clean data, and save the result with
export()as.rdsinprocessed_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.