By the end of today you can

• explain what clean data means, and why every decision is recorded;
• recognise data that is not tidy;
• chain steps with the pipe |>;
• choose cases and variables, sort, create variables, and summarise by group;
• reshape between wide and long, and join tables;
• clean a real survey export from start to finish;
• compute scale scores with reversed items;
• report the cleaning in a thesis.

Raw data is never ready

• test responses sit alongside real ones;
• the same form is submitted twice;
• one person types “female”, another “F”;
• missing answers are stored as 99;
• a slip of the finger turns 25 into 250.

None of this is unusual, and none of it is harmless.

Five qualities of clean data

One missing code shifts the average

answers <- c(4, 3, 5, 99, 2)
mean(answers)
#> [1] 22.6
mean(na_if(answers, 99), na.rm = TRUE)
#> [1] 3.5

With the code left in, the average is impossible on a 1-to-5 scale.

In real data the distortion is smaller, and so harder to notice.

Three rules for every cleaning decision

flowchart LR
    A["Raw file<br>never edited"] --> B["Code makes<br>every change"] --> C["Check after<br>every step"]

Record every decision in the script, and in the thesis where it affects the results.

Many cleaning mistakes produce no error message at all.

Tidy data

Each variable forms a column, each observation forms a row, each value sits in its own cell.

Once data is tidy, every tool in today’s lecture works on it.

A table that looks neat but is not tidy

One variable, wellbeing, is spread over two columns.

A second variable, the semester, is hidden in the column names.

The same data, tidy

One row is one student in one semester.

“Average wellbeing per semester” becomes a simple grouped summary.

The tidyverse

library(tidyverse) loads them all. This course loads only what each chapter needs.

Nested functions read inside out

round(mean(sleep), 1)

First the mean, then the rounding, but written the other way round.

As steps multiply, this gets hard to follow.

The pipe reads in order

sleep |> mean() |> round(1)

The pipe passes the result on its left as the first argument on its right.

Read it as “and then”. Ctrl+Shift+M types it in RStudio.

Older code uses %>%, which does the same job.

dplyr verbs each do one job

Every verb takes a data frame first and returns a data frame, so verbs chain.

A tiny table to learn on

pilot <- tibble(
  student = c("S1", "S2", "S3", "S4", "S5"),
  faculty = c("Education", "Humanities", "Education",
              "Health Sciences", "Humanities"),
  sleep   = c(6.5, 7.5, 5.5, 8, 6),
  stress  = c(3.2, 2.1, 4.5, 1.8, 3.9)
)

A tibble is the tidyverse’s data frame, which prints more neatly.

filter() chooses cases

pilot |> filter(sleep < 7)
pilot |> filter(sleep < 7, faculty == "Education")
pilot |> filter(faculty %in% c("Education", "Humanities"))

Conditions separated by commas must all be true. %in% matches any of several values.

Every filter is a decision about the sample

“Students who completed all four semesters” is a different population from “all students”.

Report each filter, and the number of cases it removed.

select() and arrange()

pilot |> select(student, sleep)     # keep two columns
pilot |> select(-faculty)           # drop one column
pilot |> arrange(desc(stress))      # most stressed first

select() keeps columns in the order named. desc() sorts largest first.

mutate() creates variables

pilot |> mutate(
  sleep_minutes = sleep * 60,
  short_sleep   = sleep < 7
)

A total score, a unit conversion, a category from a number: all new columns from existing ones.

case_when() sorts values into categories

pilot |> mutate(
  stress_level = case_when(
    stress >= 4   ~ "High",
    stress >= 2.5 ~ "Medium",
    .default = "Low"
  )
)

Conditions are checked in order; the first true one wins. .default catches the rest.

Categories lose information

A stress score of 3.9 and one of 2.6 both become “Medium”.

Useful for description, but analyses should normally keep the original number.

summarise() reduces a table

pilot |> summarise(
  mean_sleep = mean(sleep),
  students   = n()
)

One row of summaries. n() counts the rows.

.by summarises each group

pilot |> summarise(
  mean_sleep = mean(sleep),
  students   = n(),
  .by = faculty
)
pilot |> count(faculty)

The real power of summaries. Older code writes group_by(faculty) |> summarise(...), with the same result.

Verbs chained answer real questions

semesters |>
  left_join(students |> select(student_id, workshop),
            join_by(student_id)) |>
  summarise(mean_wellbeing = mean(wellbeing),
            students = n(),
            .by = c(semester, workshop)) |>
  arrange(semester, workshop)

Semester records, and then the workshop group, and then averages per semester and group, and then sort.

What the chain shows

Level before the workshop, invited students ahead from semester 2. Chapter 7 tests it.

From semester 3 there are 563 students, not 600: some left the study.

Wide and long

flowchart LR
    W["Wide<br>one column per occasion"] -->|pivot_longer| L["Long<br>one row per measurement"]
    L -->|pivot_wider| W

Spreadsheets and SPSS often use wide. Analysis in R is usually easier in long.

pivot_longer() stacks columns

long <- wide |>
  pivot_longer(
    cols         = c(wellbeing_1, wellbeing_2),
    names_to     = "semester",
    names_prefix = "wellbeing_",
    values_to    = "wellbeing"
  )

pivot_wider() is for presenting

long |> pivot_wider(names_from = semester,
                    values_from = wellbeing)

A wide table is easier for a reader.

The semester-by-workshop table two slides back was made with pivot_wider().

Joins combine tables by an identifier

The study keeps students, semester records, and supervisors in separate tables.

people <- tibble(student = c("S1", "S2", "S3"),
                 faculty = c("Education", "Humanities", "Education"))
scores <- tibble(student   = c("S1", "S1", "S2", "S4"),
                 wellbeing = c(58, 63, 71, 66))

left_join() keeps every row of the first table

scores |> left_join(people, join_by(student))

Matching information from people is added to each score.

S4 has a score but no entry in people, so their faculty is NA.

anti_join() finds what is missing

people |> anti_join(scores, join_by(student))

The rows of the first table with no match in the second.

Here: S3, the student without any scores.

A join answers a new question

students |>
  left_join(supervisors |> select(supervisor_id, rank),
            join_by(supervisor_id)) |>
  summarise(considering_dropout = mean(considering_dropout == "Yes"),
            students = n(), .by = rank)

Considering dropout by supervisor rank: from about 12% to about 18%.

Differences this small could easily be chance (Chapter 7).

Who left the study?

left <- students |>
  anti_join(semesters |> filter(semester == 3),
            join_by(student_id))
nrow(left)
#> [1] 37

37 students have no semester 3 record, and 22 of them had said they were considering dropping out.

Those who left are not a random selection (Chapter 6).

Cleaning the survey export

flowchart TB
    subgraph R1[" "]
        direction LR
        A[Inspect] --> B[Tests and<br>duplicates] --> C[Names] --> D[Categories]
    end
    subgraph R2[" "]
        direction LR
        E[Missing codes<br>and text] --> F[Impossible<br>values] --> G[Scale scores] --> H[Check]
    end
    R1 --> R2

Each step is short. The order matters, and so does checking after each one.

Inspect first

raw <- read_excel(data2thesis_example("wellbeing_raw.xlsx"))
dim(raw)
#> [1] 615  64
raw |> count(Q3_Gender)

615 rows for 600 students. Gender is spelled six ways: F, Female, female, M, Male, male.

Names with spaces, such as `Q1_Student ID`, need backticks.

Remove tests and duplicates

responses <- raw |>
  filter(!str_detect(str_to_upper(`Q1_Student ID`), "^TEST")) |>
  select(-`Response ID`) |>
  distinct()
nrow(responses)
#> [1] 600

! is “not”, ^ is “at the start”. The response ID differs even between duplicates, so it is dropped before distinct().

3 test responses and 12 duplicates removed: one row per student.

Usable variable names

responses <- responses |>
  rename(
    student_id      = `Q1_Student ID`,
    age             = Q2_Age,
    financial_worry = `Q10_How worried are you about money? (1-5)`
    # ... and the rest
  ) |>
  rename_with(~ item_names, .cols = Q11_1:Q11_22)

rename() puts the new name first. The questionnaire wording belongs in the codebook.

Naming items in one go

item_names <- c(paste0("stress_", 1:6), paste0("burnout_", 1:6),
                paste0("support_", 1:6), paste0("satisfaction_", 1:4))

paste0("stress_", 1:6) builds stress_1 to stress_6.

rename_with() gives all 22 items their scale names at once.

Fix categories by pattern

responses <- responses |>
  mutate(
    gender  = if_else(str_starts(str_to_lower(str_trim(gender)), "f"),
                      "Female", "Male"),
    faculty = case_when(
      str_detect(str_to_lower(faculty), "educ")    ~ "Education",
      str_detect(str_to_lower(faculty), "social")  ~ "Social Sciences",
      str_detect(str_to_lower(faculty), "natural|^sciences$") ~ "Natural Sciences"
      # ... and the other faculties
    ))

Lower-case and trim first, then look for a pattern instead of listing every spelling.

Order and across()

“Social sciences” also contains “sciences”, so Social Sciences is checked before Natural Sciences.

across(c(has_children, lives_away, considering_dropout),
       ~ if_else(str_starts(str_to_lower(.x), "y"), "Yes", "No"))

across() applies one change to several columns. Count the categories again after each fix.

Missing codes and numbers stored as text

responses <- responses |>
  mutate(
    financial_worry = financial_worry |> na_if("99") |> na_if("-9") |>
                      as.integer(),
    across(all_of(item_names), ~ as.integer(na_if(.x, "99"))),
    age = parse_number(age)
  )

na_if() turns a code into NA; as.integer() turns text into whole numbers.

Do not replace 99 everywhere

A 99 is not always a code.

On a wellbeing score from 0 to 100, it is a perfectly real value.

Replace missing codes only in the columns where you know they are codes.

parse_number() and the decimal comma

parse_number(c("7 hrs", "6.5", "6,5"))
#> [1]  7.0  6.5 65.0
parse_number(str_replace(c("7 hrs", "6.5", "6,5"), ",", "."))
#> [1] 7.0 6.5 6.5

parse_number() reads the number out of text, but a comma separates thousands in English.

No error message warns you. Only checking does.

Impossible values become NA

responses |> filter(age > 100) |> select(student_id, age)
responses <- responses |>
  mutate(age = if_else(age > 100, NA, age))

Student S0007’s age is 250. It might have been 25, or 50: the honest fix is NA, not a guess.

Unusual but possible values, such as 4 hours of sleep, are real data and stay.

Reversed items point the other way

stress_4: “I feel confident handling problems in my studies.”

Agreeing means less stress. Averaged as it stands, it cancels part of the scale.

On a 1-to-5 scale, 6 - x reverses it: 1 becomes 5, 2 becomes 4.

Scale scores

scores <- questionnaire_clean |>
  mutate(
    stress_4      = 6 - stress_4,
    stress_score  = rowMeans(pick(stress_1:stress_6), na.rm = TRUE),
    support_score = rowMeans(pick(support_1:support_6), na.rm = TRUE)
    # ... burnout and satisfaction
  ) |>
  select(student_id, ends_with("_score"))

pick() selects a scale’s items; rowMeans() averages each student’s answers.

Decisions inside a scale score

With na.rm = TRUE, a student who skipped one item still gets a score.

How many skipped items is too many is a decision to report.

Keep the original items: Chapter 9 needs them to check the scales.

Semesters from wide to long

semesters_clean <- responses |>
  select(student_id, matches("_S[1-4]$")) |>
  pivot_longer(cols      = -student_id,
               names_to  = c(".value", "semester"),
               names_sep = "_S")

GPA_S1 holds two pieces of information: the variable and the semester.

.value uses the first piece as a column name.

The same steps, on the semesters

semesters_clean <- semesters_clean |>
  mutate(
    sleep_hours = parse_number(str_replace(sleep_hours, ",", ".")),
    sleep_hours = if_else(sleep_hours > 24, NA, sleep_hours),
    study_hours = if_else(study_hours > 168, NA, study_hours),
    gpa         = if_else(gpa > 4, NA, gpa)
  ) |>
  filter(!if_all(gpa:wellbeing, is.na))

The last line removes empty rows for students who had left the study.

Check the missing values

semesters_clean |> summarise(across(everything(), ~ sum(is.na(.x))))

About 1% of the records still have gaps: answers really not given, and impossible values set to NA.

These are genuine gaps (Chapter 6).

Compare with a trusted result

same(questionnaire_clean |> arrange(student_id),
     questionnaire |> arrange(student_id))
#> [1] TRUE
same(semesters_clean |> arrange(student_id, semester),
     semesters |> arrange(student_id, semester))
#> [1] TRUE

The package’s clean tables came from the same export, so the cleaning matches exactly.

Writing it up

The survey export contained 615 responses. Test responses (3) and duplicate submissions (12) were removed, leaving 600 students. Missing-answer codes (99 and −9) were recoded as missing, and impossible values (4 in all) were set to missing. The reverse-worded item stress_4 was recoded before scale scores were computed.

In the thesis, fill these numbers in with code.

Save the cleaning as a script

# 01-clean-data.R: from the raw file to the clean tables
write_csv(semesters_clean, here::here("data", "semesters_clean.csv"))

Never edit the raw file by hand.

A new problem next month is fixed in the script and run again, and every step stays on record.

In your field: environmental and public health

airquality |>
  summarise(mean_ozone   = mean(Ozone, na.rm = TRUE),
            missing_days = sum(is.na(Ozone)),
            .by = Month)

Ozone peaks in July and August, and June has 21 missing days.

If the missing days were mostly hot ones, the June average would be too low.

Practical lab: the Chapter 3 playground

Work through the playground exercises in your browser, with hints and solutions.

Every exercise also runs in RStudio, from the downloadable chapter project.

Practical exercises 1–3: verbs and reshaping

1. Average age by faculty, sorted from oldest to youngest.
2. Classify semester sleep as Short, Moderate, or Recommended, and count each.
3. A table of GPA with one row per student and one column per semester.

Practical exercises 4–6: joins and judgement

4. Join semesters to students and compare full-time and part-time GPA.
5. Explain what happens to stress scores if stress_4 is not reversed.
6. Give one example of each quality of clean data from your own field.

Try this yourself

Take any messy spreadsheet you have used.

• list its problems under the five qualities;
• write the cleaning as one script from the untouched file;
• count the categories after every fix;
• write a three-sentence cleaning report.

Then change one value in the raw file and run the script again.

Troubleshooting guide (Part 1)

Troubleshooting guide (Part 2)

Completion checklist

• the raw file is untouched;
• test and duplicate rows are removed and counted;
• every category has one spelling;
• missing codes are NA in the right columns only;
• text columns are converted to numbers and checked;
• impossible values are set to NA;
• reversed items are recoded before scoring;
• the cleaning is one script, reported in the thesis.

Misconceptions to leave behind (Part 1)

Misconceptions to leave behind (Part 2)

The chapter in one sentence

Clean in code from an untouched file, check after every step, and report every decision.

Next: Chapter 4

The next chapter turns clean data into graphs:

• what a graph is for, and how people read graphs;
• choosing a graph for the question;
• the grammar of graphics with ggplot2;
• graphs for publication, and honest graphs;
• exporting figures.

Questions

What is the messiest dataset you have worked with?

Which of its problems would have been silent?