Lesson 7 of 17 · Week 03 · Tue Oct 13

Cleaning Data in Practice: Subsetting

Why subsetting is cleaning

Most data cleaning is subsetting: finding the rows that are wrong, pulling out the columns you need, replacing bad values with NA. R has three subsetting operators, [, [[ and $, and six ways to say which elements you want. Once you know them, cleaning becomes a sequence of small, checkable steps.

Adapted from Software Carpentry's “Subsetting Data” episode (CC BY 4.0, The Carpentries). Text, examples and exercises have been rewritten for this course.

Vectors: six ways to pick elements

w <- c(a = 5.1, b = 7.3, c = 2.2, d = 9.8, e = 6.4)
w[2]                 # by position
w[c(1, 3)]           # several positions
w[2:4]               # a range
w[-1]                # everything EXCEPT position 1
w[-(1:2)]            # everything except 1 and 2  (note the parentheses!)
w["d"]               # by name
w[c("a", "e")]
w[w > 6]             # by a logical condition
w[w > 3 & w < 7]     # & = and, | = or, ! = not

Watch out: w[-1:2] means w[c(-1, 0, 1, 2)], which is an error (you can't mix negative and positive indices). w[10] returns NA rather than an error, so an index that's out of range can go unnoticed. which(w > 6) gives the positions where a condition is TRUE.

Matching several values: %in%

To keep elements equal to any of several values, don't use == with a vector: it compares element by element, recycling the shorter vector, and silently gives wrong answers. Use %in%:

andes <- c("Bolivia", "Chile", "Peru")
gm$country == andes            # WRONG: recycles, compares row 1 to Bolivia, row 2 to Chile...
gm$country == "Bolivia" | gm$country == "Chile" | gm$country == "Peru"   # works, but clunky
gm$country %in% andes          # right: TRUE wherever the country is any of the three

Matrices and lists

m <- matrix(1:12, nrow = 3)
m[2, 3]              # row 2, column 3
m[2, ]               # all of row 2
m[, c(1, 4)]         # columns 1 and 4
m[5]                 # a matrix is a vector: the 5th element, counting down columns

kit <- list(label = "field kit", counts = c(4, 8, 15), site = "A")
kit[2]               # a LIST containing counts (the train car)
kit[[2]]             # the counts vector itself (what's in the car)
kit$counts[3]        # 15

Model output is a list too. Look at its pieces with names() (or str()) and pull one out with $: e.g. fit <- lm(lifeExp ~ gdpPercap, data = gm), then names(fit).

Data frames

gm[gm$year == 2007, ]                        # rows by condition, all columns
gm[gm$lifeExp > 75, c("country", "year")]    # rows AND columns
gm[c(1:4, 9:12), ]                           # rows by position
gm[, -(1:2)]                                 # drop columns 1 and 2
gm[gm$year %in% c(1967, 2007), ]

With one index and no comma, gm[1:3] picks columns 1–3 (a data frame is a list of columns), and gm[1:20] is an error because gm has only 6 columns. With a comma, gm[1:3, ] picks rows. The comma matters.

Cleaning in practice

A typical cleaning pass, one small step at a time. Check each step before moving on:

survey$age[survey$age == -99] <- NA             # a code for "missing" becomes a real NA
survey$site <- tolower(trimws(survey$site))     # " North" and "north " become "north"
table(survey$site, useNA = "ifany")             # every spelling, and how many NAs
survey[survey$age < 0 | survey$age > 110, ]     # rows that can't be right
sum(is.na(survey$age))                          # how many are missing now?
survey <- survey[!duplicated(survey), ]         # drop exact duplicate rows

Never overwrite the raw file. Your script reads the raw data from data/ and writes the clean version to results/ (Week 06), so every cleaning decision is written down and can be re-run.

Exercises

Each exercise gives you up to 10 checked attempts. Use Run as many times as you like to experiment first, then click Check answer when you're ready. Your exercises use numbers/subsets unique to you, so answers won't match a classmate's exactly.