Cleaning Data in Practice

Week 3 · Tuesday, Oct 13 · Lecture

Dr. Marcela Alfaro Córdoba

Today’s plan

Time	What
1:30	Warm-up: trace a loop on paper
1:40	Six ways to pick elements
2:00	`%in%`, and why `==` lies
2:10	Lists, models, and data frames: the comma matters
2:30	A cleaning pass on a messy file, one checked step at a time
2:55	`scripts/clean-survey.R`: cleaning you can `source()`

Learning goals: pick exactly the rows, columns and values you want, and turn a messy file into a clean one with a script anyone can re-run.

Type along in week03/subsetting.R. Still no AI.

Warm-up: trace it

⏱ 5 min

On paper. Write what out holds after each step, then the final result.

x <- c(4, 15, 7, 22)
out <- character(length(x))
for (i in seq_along(x)) {
  if (x[i] > 20) {
    out[i] <- "big"
  } else if (x[i] > 5) {
    out[i] <- "mid"
  } else {
    out[i] <- "small"
  }
}

"small" "mid" "mid" "big". We discuss your DS printout on Tuesday, Oct 20. This is the kind of trace we’ll do on it.

Today’s script

Create week03/subsetting.R:

# Week 3 · Subsetting and cleaning
source("scripts/load-gapminder.R")
source("scripts/check-gapminder.R")

Tip

If those two lines don’t run cleanly after a restart, fix that first. The check script was Thursday’s last activity.

Six ways to pick elements

Why subsetting is cleaning

Most data cleaning is three moves:

Find the values that are wrong
Pull out the rows and columns you need
Replace bad values with something honest, usually NA

All three are subsetting. R has three operators, [ ], [[ ]] and $, and six ways to say which elements you want.

Vectors: by position, or leaving some out

w <- c(a = 5.1, b = 7.3, c = 2.2, d = 9.8, e = 6.4)

w[2]               #>   b      7.3
w[c(1, 3)]         #>   a   c  5.1 2.2
w[2:4]             # a range
w[-1]              # everything EXCEPT position 1
w[-(1:2)]          #>   c   d   e      2.2 9.8 6.4

w[-1:2]
#> Error in w[-1:2] : only 0's may be mixed with negative subscripts
w[10]
#> <NA>
#>   NA

-1:2 is c(-1, 0, 1, 2): you can’t mix “leave out” and “keep”. And an index past the end gives NA, not an error, so it can slip by unnoticed.

By name, and by condition

w["d"]                  # by name
w[c("a", "e")]          #>   a   e      5.1 6.4

w > 6
#>     a     b     c     d     e
#> FALSE  TRUE FALSE  TRUE  TRUE
w[w > 6]                #>   b   d   e      7.3 9.8 6.4
which(w > 6)            #> b d e          2 4 5     the POSITIONS
w[w > 3 & w < 7]        #>   a   e      5.1 6.4

w[w > 6] reads “the w’s where w > 6 is TRUE”. A logical vector, one value per element, does the picking. That’s the move behind almost all cleaning.

Your turn: two roads, one place

⏱ 5 min

In your script:

w <- c(a = 5.1, b = 7.3, c = 2.2, d = 9.8, e = 6.4)
target <- c(b = 7.3, d = 9.8, e = 6.4)

Produce target from w in three different ways: by position, by name, and by a condition. Check each with identical(..., target).

Then: which of the three still gives the right answer if someone adds a new element at the front of w?

w[c(2, 4, 5)] or w[-c(1, 3)] · w[c("b", "d", "e")] · w[w > 6]. Names and conditions survive a new element; positions don’t.

`%in%`: matching several values

`==` with a vector gives wrong answers, quietly

The Andes, from gapminder:

andes <- c("Bolivia", "Chile", "Peru")
sum(gapminder$country == andes)      #> [1] 12
sum(gapminder$country %in% andes)    #> [1] 36

3 countries × 12 years should be 36. What did == do?

head(gapminder[gapminder$country == andes, c("country", "year")], 4)
#>     country year
#> 133 Bolivia 1952
#> 136 Bolivia 1967
#> 139 Bolivia 1982
#> 142 Bolivia 1997

== recycled andes: row 1 vs. Bolivia, row 2 vs. Chile, row 3 vs. Peru, row 4 vs. Bolivia… No warning, because 1704 is a multiple of 3.

The fix

gapminder$country %in% andes

“For each row: is this country one of these?” One TRUE/FALSE per row, no recycling.

andes_rows <- gapminder[gapminder$country %in% andes, ]
nrow(andes_rows)                   #> [1] 36
table(andes_rows$country)
#> Bolivia   Chile    Peru
#>      12      12      12

Important

Knowing what the count should be (3 × 12) is what caught the bug. Before you subset, write down the number of rows you expect. After, check it.

Lists, models, data frames

`[ ]` vs `[[ ]]` on a list

kit <- list(label = "field kit", counts = c(4, 8, 15), site = "A")

kit[2]          # a LIST that contains counts: the train car
kit[[2]]        #> [1]  4  8 15    what's inside the car
kit$counts      # same as kit[["counts"]]
kit$counts[3]   #> [1] 15          list subsetting, then vector subsetting

A fitted model is a list, so the same tools pull numbers out of it:

fit <- lm(lifeExp ~ gdpPercap, data = gapminder)
names(fit)
#>  [1] "coefficients"  "residuals"  "effects"  "rank"  "fitted.values"
#>  [6] "assign"  "qr"  "df.residual"  "xlevels"  "call"  "terms"  "model"
fit$df.residual       #> [1] 1702

We’ll come back to models in Week 5. For now: names() and $ let you look inside anything.

Data frames: `[rows, columns]`

gapminder[gapminder$year == 2007, ]                     # rows by condition
gapminder[gapminder$lifeExp > 81, c("country", "year", "lifeExp")]
#>              country year lifeExp
#> 72         Australia 2007  81.235
#> 671  Hong Kong China 2002  81.495
#> ...
gapminder[, -(1:2)]                                     # drop columns 1, 2
gapminder[gapminder$year %in% c(1967, 2007), ]          # 284 rows

The comma decides. Before it: rows. After it: columns. Empty: all of them.

gapminder[1:10]
#> Error in `[.data.frame`(gapminder, 1:10) : undefined columns selected

No comma means “columns” (a data frame is a list of columns), and there are only 7. You meant gapminder[1:10, ].

Your turn: fix five broken subsets

⏱ 8 min

Each line has one common mistake. Fix it, and say what the mistake was in a comment.

q1 <- gapminder[gapminder$year = 1987, ]          # rows from 1987
q2 <- gapminder[, -1:3]                           # all columns except 1-3
q3 <- gapminder[gapminder$lifeExp > 78]           # rows with lifeExp over 78
q4 <- gapminder[1, 3, 4]                          # row 1, columns 3 and 4
q5 <- gapminder[gapminder$year == 1967 | 2002, ]  # rows from 1967 and 2002

For each fix, write the number of rows you expect before you check with nrow().

== not = · -(1:3) · missing comma · c(3, 4) · year == 1967 | year == 2002, or better, year %in% c(1967, 2002). q5 as written keeps every row: 2002 on its own is TRUE.

A cleaning pass

The file

A short, made-up campus survey: where people live on campus, age, commute time in minutes, and visits per week to the library. Typed in by hand, by several people.

survey <- read.csv("data/field_survey.csv")
str(survey)
#> 'data.frame':    48 obs. of  5 variables:
#>  $ id         : int  1 2 3 4 5 6 7 8 9 10 ...
#>  $ site       : chr  "East" "North" "East" " north" ...
#>  $ age        : int  26 38 39 18 21 -99 23 28 39 37 ...
#>  $ commute_min: chr  "23" "11" "17" "7" ...
#>  $ visits     : int  1 6 0 0 4 6 0 5 5 5 ...

⏱ 3 min

Using only that output: write down three things that look wrong. There are at least four.

Look before you touch

summary(survey$age)
#>    Min. 1st Qu.  Median    Mean 3rd Qu.    Max.
#>  -99.00   21.00   27.50   27.40   35.25  230.00
table(survey$site, useNA = "ifany")
#>
#>         north   East    Est  North  NORTH  South south
#>      1      2     11      1     13      1     17      2
sum(duplicated(survey))     #> [1] 3

-99 is a code for “didn’t answer”, not an age. 230 is a typo
Six spellings of three sites, one blank, and one "Est"
commute_min is chr: one entry isn’t a number
3 rows were entered twice

Note

read.csv() turns blank text cells into "", not NA. That’s the unlabeled column of 1 in the table.

The rules of a cleaning pass

One small step. Check it. Next step.

After every step, look at one of:

nrow(survey)                      # did I lose or gain rows?
colSums(is.na(survey))            # did I create or remove NAs?
table(survey$site, useNA = "ifany")
summary(survey$age)

Important

The raw file in data/ is never touched. Every fix happens in code, so it’s written down, it can be checked, and it can be undone.

Step 1: duplicates

survey[duplicated(survey), "id"]     #> [1]  8 20 33
survey <- survey[!duplicated(survey), ]
nrow(survey)                         #> [1] 45

48 − 3 = 45. Expected, checked. duplicated() marks the second copy of each exact repeat, so !duplicated() keeps the first.

Step 2: impossible ages

survey[which(survey$age < 0 | survey$age > 110), ]
#>    id  site age commute_min visits
#> 6   6 South -99          21      6
#> 14 14 South 230          15      2
#> 29 29 North -99          34      3

survey$age[survey$age == -99] <- NA
survey$age[survey$age > 110] <- NA
sum(is.na(survey$age))               #> [1] 3
summary(survey$age)
#>    Min. 1st Qu.  Median    Mean 3rd Qu.    Max.    NA's
#>   18.00   21.00   28.00   28.83   35.75   41.00       3

Warning

Once a column has NAs, survey[survey$age > 110, ] returns junk rows full of NA. Use which() to look: it gives only the positions that are truly TRUE.

Step 3: one spelling per site

survey$site <- tolower(trimws(survey$site))   # " north", "NORTH" -> "north"
table(survey$site, useNA = "ifany")
#>        east   est north south
#>    1     10     1    15    18

survey$site[survey$site == "est"] <- "east"
survey$site[survey$site == ""] <- NA
table(survey$site, useNA = "ifany")
#>  east north south  <NA>
#>    11    15    18     1

11 + 15 + 18 + 1 = 45. Every row accounted for. "est" → "east" is a judgment call: write a comment saying why you made it.

Step 4: a number column stored as text

as.numeric(survey$commute_min)
#> Warning message: NAs introduced by coercion

Don’t silence the warning. Find what it choked on:

bad <- is.na(as.numeric(survey$commute_min))
survey[bad, c("id", "commute_min")]
#>    id commute_min
#> 11 11      15 min
#> 35 35

survey$commute_min[survey$commute_min == "15 min"] <- "15"
survey$commute_min[survey$commute_min == ""] <- NA
survey$commute_min <- as.numeric(survey$commute_min)    # no warning now
colSums(is.na(survey))
#>          id        site         age commute_min      visits
#>           0           1           3           1           0

Your turn: put it in a script

⏱ 12 min

Create scripts/clean-survey.R with all four steps, in order, starting from:

# Clean the raw campus survey.
# Reads data/field_survey.csv, never changes it. Creates `survey`.
# Run with: source("scripts/clean-survey.R")

survey <- read.csv("data/field_survey.csv")

Give each step a comment that says why, not just what. Then restart R and source() it. Does str(survey) show 45 rows and commute_min as num?

Stuck on a step? Run the script with source("scripts/clean-survey.R", echo = TRUE) and read up from the first error.

End the script with checks

Thursday’s stop(), now guarding the clean data. At the bottom of scripts/clean-survey.R:

# Checks: stop loudly if the cleaning didn't do what we think it did
if (nrow(survey) != 45) stop("expected 45 rows, found ", nrow(survey))
if (any(duplicated(survey))) stop("duplicate rows remain")
if (!all(survey$site %in% c("north", "south", "east", NA))) {
  stop("unexpected site: ", setdiff(survey$site, c("north", "south", "east", NA)))
}
if (any(survey$age < 0 | survey$age > 110, na.rm = TRUE)) stop("impossible age")
if (!is.numeric(survey$commute_min)) stop("commute_min is not numeric")

message("survey cleaned: ", nrow(survey), " rows")

%in% again: every site must be one of the allowed values. And na.rm = TRUE inside any(), because if can’t decide on NA.

Analysis scripts start with `source()`

week03/survey-summary.R:

source("scripts/clean-survey.R")

for (s in c("north", "south", "east")) {
  m <- mean(survey$commute_min[which(survey$site == s)], na.rm = TRUE)
  print(paste(s, round(m, 1), "min"))
}
#> [1] "north 20.8 min"
#> [1] "south 19.2 min"
#> [1] "east 21.2 min"

Tip

The analysis never sees the raw file, and the cleaning never sees the analysis. If you find another typo next week, you fix it once, in clean-survey.R, and every script that sources it gets the fix.

Your turn: the restart test, for real

⏱ 8 min

Create week03/survey-summary.R from the previous slide
Session → Restart R. Then source("week03/survey-summary.R")
Add a line to the summary: how many people visit the library 3 or more times a week, per site?
Open data/field_survey.csv in Positron and confirm it still has 48 rows and " north" with its space. You never touched it
Restart and source once more

If step 2 works from a fresh session, someone else can run your whole analysis from your folder. That is what we’ll ask of your project.

The shape of your project

stat266a/
├── data/
│   ├── gapminder_data.csv
│   └── field_survey.csv          <- raw, never edited
├── scripts/
│   ├── load-gapminder.R
│   ├── check-gapminder.R
│   └── clean-survey.R            <- read + clean + check
└── week03/
    ├── control-flow.R
    ├── ds03_notes.R
    ├── subsetting.R
    └── survey-summary.R          <- starts with source()

Your project repository should look like this from the first commit: data/ for raw files, scripts/ for code you source(), and analysis scripts that start from them.

Key points

Six ways to index: positive, negative, name, logical, empty (everything), and [[ ]]/$ for one element
Use %in%, not ==, to match against several values
df[rows, cols]: the comma decides which is which
Know how many rows you expect before you subset, and check after
Clean in small steps; check rows and NAs after each one
Never edit the raw file. The cleaning lives in a script you source()
End the cleaning script with stop() checks

Before you go

Tuesday, Oct 20: Project proposal. 3 minutes plus questions, each partner answers. Guidelines.

Show the first rows of your data. Have them come from a script: scripts/load-<yourdata>.R
Say what looks messy in it, and how you’ll check it. That’s your Risks item
No AI while presenting or answering

Also Tuesday, Oct 20: your printed DS 3 PDF (Lesson 6) and a pencil. The discussion check moved there, before the proposals, so Thursday is all ggplot2.

Tonight or tomorrow: Lesson 7: Cleaning Data in Practice, to lock in today. Not graded, still no AI.

Next week: graphics with ggplot2. You’ll want clean data for that.

Cleaning Data in Practice

Today’s plan

Warm-up: trace it

Today’s script

Six ways to pick elements

Why subsetting is cleaning

Vectors: by position, or leaving some out

By name, and by condition

Your turn: two roads, one place

%in%: matching several values

== with a vector gives wrong answers, quietly

The fix

Lists, models, data frames

[ ] vs [[ ]] on a list

Data frames: [rows, columns]

Your turn: fix five broken subsets

A cleaning pass

The file

Look before you touch

The rules of a cleaning pass

Step 1: duplicates

Step 2: impossible ages

Step 3: one spelling per site

Step 4: a number column stored as text

Your turn: put it in a script

End the script with checks

Analysis scripts start with source()

Your turn: the restart test, for real

The shape of your project

Key points

Before you go

`%in%`: matching several values

`==` with a vector gives wrong answers, quietly

`[ ]` vs `[[ ]]` on a list

Data frames: `[rows, columns]`

Analysis scripts start with `source()`