3 Data Manipulation

Raw data is never ready for analysis. Survey tools record test responses alongside real ones, participants submit the same form twice, one person types “female” and another “F”, missing answers are stored as 99, and a slip of the finger turns an age of 25 into 250. None of this is unusual, and none of it is harmless: a single 99 among answers from 1 to 5 can shift an average noticeably, and a duplicated row counts one participant twice. Preparing data for analysis, usually called data cleaning, often takes longer than the analysis itself, and its decisions shape every result that follows.

Cleaning is therefore part of the analysis, not a chore before it, and it deserves the same care. This chapter first sets out what clean data means and the principles that guide cleaning decisions. It then introduces the tools for working with tables in R: choosing cases and variables, creating new variables, summarising by group, reshaping, and combining tables. Finally, it applies them, step by step, to the raw export of the wellbeing survey that Chapter 2 opened: the file with test responses, duplicated rows, the same answer spelled four ways, ages stored as text, and missing values coded as 99.

By the end of this chapter you will be able to

Explain what clean data means, and why every cleaning decision must be recorded.
Explain what tidy data is, and recognise data that is not tidy.
Chain steps together with the pipe |>.
Choose cases and variables, sort, create new variables, and summarise, overall and by group.
Reshape data between wide and long formats, and join tables that share an identifier.
Clean a real survey export: remove test and duplicate rows, rename variables, fix inconsistent categories, convert text to numbers, and handle missing codes and impossible values.
Compute questionnaire scale scores, including reversed items, and report the cleaning in a thesis.

Teaching & Practice: Interactive Lecture Slides · Browser Playground Exercises

3.1 What clean data means

Data is clean when it can be trusted to represent what was measured. That broad idea can be broken into five qualities, each of which the survey export fails in some way. Clean data is valid: every value is possible for its variable, so there are no ages of 250 or 26 hours of sleep a night. It is accurate: values record what the participant actually meant, so a missing-answer code is not mistaken for a real answer. It is complete as far as possible, and where values are missing, they are marked as missing rather than hidden behind codes. It is consistent: the same answer is always recorded in the same way, so “F”, “female”, and “Female” become one category. And it is unique: each case appears exactly once, with no test entries and no duplicates.

Each failure distorts results in a different way. A missing-answer code that stays in the data is the easiest to see. Suppose five students answered a question on a 1-to-5 scale, and one of them skipped it, which the survey tool recorded as 99:

library(dplyr)

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 22.6, a value that is impossible on a 1-to-5 scale. Marked as missing with na_if(), the code drops out, and the average of the four real answers is 3.5. In a real dataset the distortion is usually smaller and therefore harder to notice, which is exactly what makes it dangerous. Duplicates work in a similar way, but on the sample size and the balance of the sample: a participant who appears twice counts twice.

Three principles follow for every cleaning decision. The raw file is never edited; it stays exactly as it was received, and all changes are made by code that starts from it. Every decision is recorded, in the script and, where it affects the results, in the thesis: how many cases were removed and why, which values were set to missing, how scale scores were built. And the result is checked after every step, because many cleaning mistakes produce no error message at all. The final section of this chapter shows how to report the cleaning in a thesis.

3.2 Tidy data

Clean data should also be arranged in a way that makes analysis straightforward. The arrangement used throughout this book is called tidy data (Wickham 2014). In tidy data, each variable forms a column, each observation forms a row, and each value sits in its own cell. The idea is easiest to see in a small example. Table 3.1 stores two semesters of wellbeing for three students side by side, as a spreadsheet often would.

Table 3.1: Wellbeing in wide format: one variable, wellbeing, is spread over two columns

student	wellbeing_s1	wellbeing_s2
S1	58	63
S2	71	70
S3	64	69

The table looks neat, but one variable, wellbeing, is spread over two columns, and a second variable, the semester, is hidden inside the column names. Table 3.2 holds the same six measurements in tidy form.

Table 3.2: The same data in tidy (long) format: one column per variable, one row per observation

student	semester	wellbeing
S1	1	58
S1	2	63
S2	1	71
S2	2	70
S3	1	64
S3	2	69

Now each of the three variables has its own column, and each row is one observation: one student in one semester. Questions such as “average wellbeing in each semester” become a simple grouped summary. The students table in the package is tidy, with one row per student and one column per variable. The survey export is not: it squeezes four semesters of measurements side by side into one row per student. Much of cleaning consists of making data tidy, because once it is tidy, every tool in this chapter works on it.

3.3 The tidyverse

The tools in this chapter come from the tidyverse, a collection of packages that share one way of working and are designed for tidy data. This chapter uses four of them: dplyr for working with rows and columns, tidyr for reshaping data, stringr for working with text, and readr for turning text into numbers (and for reading and writing files). The whole collection can be installed at once with install.packages("tidyverse") and loaded with library(tidyverse), but this book loads only the packages each chapter needs:

library(dplyr)
library(tidyr)
library(stringr)
library(readr)

3.3.1 Chaining steps with the pipe

Analysis is a series of steps: take the data, keep some rows, calculate something, round the result. In Chapter 1, such steps were written by putting functions inside each other:

sleep <- c(6.5, 7, 5.5, 8, 6)
round(mean(sleep), 1)

[1] 6.6

This reads from the inside out, which gets hard to follow as the steps multiply. The pipe, written |>, writes the steps in the order they happen. It takes the result on its left and passes it as the first argument to the function on its right:

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

[1] 6.6

The pipe reads as “and then”: take sleep, and then take the mean, and then round it to one decimal place. In RStudio, Ctrl+Shift+M (Cmd+Shift+M on a Mac) types the pipe for you.

The older pipe

Before R had its own pipe, the tidyverse used %>%, from the magrittr package. You will see it in many tutorials and older scripts. For everything in this book, it does the same job as |>.

3.4 Working with cases and variables

The dplyr package provides a small set of verbs, each doing one job. Every verb takes a data frame first and returns a data frame, so verbs can be chained with the pipe. The verbs are easiest to understand on a tiny table. The function tibble() builds one; a tibble is the tidyverse’s version of a data frame, which prints more neatly:

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)
)
pilot

# A tibble: 5 × 4
  student faculty         sleep stress
  <chr>   <chr>           <dbl>  <dbl>
1 S1      Education         6.5    3.2
2 S2      Humanities        7.5    2.1
3 S3      Education         5.5    4.5
4 S4      Health Sciences   8      1.8
5 S5      Humanities        6      3.9

3.4.1 Choosing the cases you need

Most analyses use only some of the cases: the students of one faculty, the first semester, the participants who completed the study. The verb filter() keeps the rows that meet a condition:

pilot |> filter(sleep < 7)

# A tibble: 3 × 4
  student faculty    sleep stress
  <chr>   <chr>      <dbl>  <dbl>
1 S1      Education    6.5    3.2
2 S3      Education    5.5    4.5
3 S5      Humanities   6      3.9

Several conditions separated by commas must all be true:

pilot |> filter(sleep < 7, faculty == "Education")

# A tibble: 2 × 4
  student faculty   sleep stress
  <chr>   <chr>     <dbl>  <dbl>
1 S1      Education   6.5    3.2
2 S3      Education   5.5    4.5

To keep rows matching any of several values, use %in%:

pilot |> filter(faculty %in% c("Education", "Humanities"))

# A tibble: 4 × 4
  student faculty    sleep stress
  <chr>   <chr>      <dbl>  <dbl>
1 S1      Education    6.5    3.2
2 S2      Humanities   7.5    2.1
3 S3      Education    5.5    4.5
4 S5      Humanities   6      3.9

Every filter is a decision about the sample, and should be reported: an analysis of “students who completed all four semesters” answers a different question from an analysis of all students.

3.4.2 Choosing variables

Large datasets have far more variables than any one analysis needs. The verb select() keeps the columns you name, in the order you name them, and a minus sign drops a column instead:

pilot |> select(student, sleep)

# A tibble: 5 × 2
  student sleep
  <chr>   <dbl>
1 S1        6.5
2 S2        7.5
3 S3        5.5
4 S4        8  
5 S5        6

pilot |> select(-faculty)

# A tibble: 5 × 3
  student sleep stress
  <chr>   <dbl>  <dbl>
1 S1        6.5    3.2
2 S2        7.5    2.1
3 S3        5.5    4.5
4 S4        8      1.8
5 S5        6      3.9

3.4.3 Sorting cases

Sorting helps when looking at data, for example to see the most extreme values first. The verb arrange() sorts rows by one or more columns, and desc() sorts from largest to smallest:

pilot |> arrange(desc(stress))

# A tibble: 5 × 4
  student faculty         sleep stress
  <chr>   <chr>           <dbl>  <dbl>
1 S3      Education         5.5    4.5
2 S5      Humanities        6      3.9
3 S1      Education         6.5    3.2
4 S2      Humanities        7.5    2.1
5 S4      Health Sciences   8      1.8

3.4.4 Creating new variables

Many variables used in analysis are calculated from others: a total score, a conversion to other units, a category derived from a number. The verb mutate() adds new columns calculated from existing ones, or changes existing ones:

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

# A tibble: 5 × 6
  student faculty         sleep stress sleep_minutes short_sleep
  <chr>   <chr>           <dbl>  <dbl>         <dbl> <lgl>      
1 S1      Education         6.5    3.2           390 TRUE       
2 S2      Humanities        7.5    2.1           450 FALSE      
3 S3      Education         5.5    4.5           330 TRUE       
4 S4      Health Sciences   8      1.8           480 FALSE      
5 S5      Humanities        6      3.9           360 TRUE

To sort values into categories, case_when() checks conditions in order and uses the first one that is true, and .default catches everything else:

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

# A tibble: 5 × 5
  student faculty         sleep stress stress_level
  <chr>   <chr>           <dbl>  <dbl> <chr>       
1 S1      Education         6.5    3.2 Medium      
2 S2      Humanities        7.5    2.1 Low         
3 S3      Education         5.5    4.5 High        
4 S4      Health Sciences   8      1.8 Low         
5 S5      Humanities        6      3.9 Medium

Turning a number into categories in this way loses information, since a stress score of 3.9 and one of 2.6 become the same “Medium”. It is useful for description, but analyses should normally keep the original number.

3.4.5 Summarising, overall and by group

Summaries reduce many values to a few numbers. The verb summarise() reduces a table to one row of summaries, and n() counts the rows:

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

# A tibble: 1 × 2
  mean_sleep students
       <dbl>    <int>
1        6.7        5

The real power comes with groups. The .by argument calculates the summary separately for each group:

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

# A tibble: 3 × 3
  faculty         mean_sleep students
  <chr>                <dbl>    <int>
1 Education             6           2
2 Humanities            6.75        2
3 Health Sciences       8           1

For simple counts, count() is a shortcut:

pilot |> count(faculty)

# A tibble: 3 × 2
  faculty             n
  <chr>           <int>
1 Education           2
2 Health Sciences     1
3 Humanities          2

Grouping in older code

In many tutorials you will see group_by(faculty) |> summarise(...) instead of the .by argument. Both give the same result. The .by argument is newer and keeps each step self-contained, so this book uses it.

3.4.6 Combining the steps

Each verb is simple, but chained together they answer real questions. One such question concerns how average wellbeing changed over the four semesters, separately for students who were and were not invited to the workshop. The semester records are in semesters, and the workshop group is in students, so the workshop group is first added to the semester records. (The left_join() in the first line is explained in Section 3.6.)

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    workshop mean_wellbeing students
1        1     Invited       60.64667      300
2        1 Not invited       60.26667      300
3        2     Invited       65.47000      300
4        2 Not invited       60.22000      300
5        3     Invited       63.22261      283
6        3 Not invited       59.22857      280
7        4     Invited       60.93286      283
8        4 Not invited       58.42500      280

Read aloud, the code says: take the semester records, and then add each student’s workshop group, and then calculate the average wellbeing and the number of students for each semester and workshop group, and then sort the result. In semester 1, before the workshop, the two groups are almost level. From semester 2, the invited students are ahead. Chapter 7 tests whether that difference is larger than chance.

The number of students also drops in semesters 3 and 4. Some students left the study, which is worth remembering: it returns later in this chapter and in Chapter 6.

3.5 Reshaping between wide and long

The same data can be laid out in two ways, as the section on tidy data showed. In wide format, repeated measurements sit side by side, one column per occasion. In long format, each measurement has its own row. Here is the wide table from Table 3.1 in R:

wide <- tibble(
  student     = c("S1", "S2", "S3"),
  wellbeing_1 = c(58, 71, 64),
  wellbeing_2 = c(63, 70, 69)
)
wide

# A tibble: 3 × 3
  student wellbeing_1 wellbeing_2
  <chr>         <dbl>       <dbl>
1 S1               58          63
2 S2               71          70
3 S3               64          69

Spreadsheets and SPSS often use wide format. For analysis in R, long format is usually easier, because each variable (student, semester, wellbeing) is then one column. The function pivot_longer() turns wide into long:

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

# A tibble: 6 × 3
  student semester wellbeing
  <chr>   <chr>        <dbl>
1 S1      1               58
2 S1      2               63
3 S2      1               71
4 S2      2               70
5 S3      1               64
6 S3      2               69

The arguments say which columns to stack (cols), where the old column names should go (names_to), which part of the names to drop (names_prefix), and where the values should go (values_to). The result is Table 3.2.

The opposite function, pivot_wider(), is especially useful for presenting results, because a wide table is easier to read:

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

# A tibble: 3 × 3
  student   `1`   `2`
  <chr>   <dbl> <dbl>
1 S1         58    63
2 S2         71    70
3 S3         64    69

The average wellbeing table from the previous section, for example, reads more easily with one column per workshop group:

semesters |>
  left_join(students |> select(student_id, workshop), join_by(student_id)) |>
  summarise(mean_wellbeing = round(mean(wellbeing), 1), .by = c(semester, workshop)) |>
  pivot_wider(names_from = workshop, values_from = mean_wellbeing)

# A tibble: 4 × 3
  semester `Not invited` Invited
     <int>         <dbl>   <dbl>
1        1          60.3    60.6
2        2          60.2    65.5
3        3          59.2    63.2
4        4          58.4    60.9

3.6 Joining tables

Research data is often spread over several tables that share an identifier. The wellbeing study keeps students in one table, their semester records in another, and their supervisors in a third. Joins combine tables by matching the identifier. Two small tables show how:

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)
)

The function left_join() keeps every row of the first table and adds the matching information from the second:

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

# A tibble: 4 × 3
  student wellbeing faculty   
  <chr>       <dbl> <chr>     
1 S1             58 Education 
2 S1             63 Education 
3 S2             71 Humanities
4 S4             66 <NA>

The argument join_by(student) names the column that links the two tables. Student S4 has a score but no entry in people, so their faculty is NA.

The function anti_join() does the opposite: it keeps the rows of the first table that have no match in the second. It is the quickest way to find what is missing, here the students without any scores:

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

# A tibble: 1 × 2
  student faculty  
  <chr>   <chr>    
1 S3      Education

On the wellbeing data, joins answer questions that no single table can. Each student’s supervisor has an academic rank, stored in supervisors, and a join brings it next to each student’s answer about dropping out:

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

                 rank considering_dropout students
1            Lecturer           0.1367521      234
2           Professor           0.1217391      115
3 Assistant Professor           0.1752988      251

The shares differ a little, from about 12% to about 18%. Differences this small could easily be chance; Chapter 7 shows how to test that. A second join finds the students who have no records for semester 3:

left <- students |>
  anti_join(semesters |> filter(semester == 3), join_by(student_id))
nrow(left)

[1] 37

left |> count(considering_dropout)

  considering_dropout  n
1                  No 15
2                 Yes 22

In all, 37 students have no semester 3 record: they left the study after the first year, and 22 of them had said they were considering dropping out. The students who left are not a random selection, which matters for any analysis of the later semesters. Chapter 6 returns to this.

3.7 Cleaning the survey export

The survey export can now be cleaned. The steps below are the ones needed for almost any survey data, and each is preceded by the problem it solves. Each step is short. What matters is doing them in a sensible order, and checking the result after each one.

3.7.1 Inspecting the export

Cleaning starts with looking, because problems that have not been seen cannot be fixed:

library(readxl)
raw <- read_excel(data2thesis_example("wellbeing_raw.xlsx"))
dim(raw)

[1] 615  64

The file has 615 rows, although there are only 600 students. A few lines of code show what needs fixing:

raw |> count(Q3_Gender)

# A tibble: 7 × 2
  Q3_Gender     n
  <chr>     <int>
1 F            28
2 Female      233
3 M            46
4 Male        173
5 female       56
6 male         78
7 <NA>          1

raw |> filter(str_detect(str_to_upper(`Q1_Student ID`), "TEST")) |> select(1:4)

# A tibble: 3 × 4
  `Response ID` `Q1_Student ID` Q2_Age Q3_Gender
  <chr>         <chr>           <chr>  <chr>    
1 R0001         TEST            99     Female   
2 R0002         test            <NA>   <NA>     
3 R0003         TEST2           30     Male

Gender is spelled six ways (F, female, Female, and so on), plus a blank from a test response, and there are test responses from when the survey was set up. (The raw file also contains "Female " with a trailing space; read_excel() removes such spaces automatically, but other import functions do not, which is why the cleaning below still uses str_trim().) Column names such as Q1_Student ID contain spaces, so they must be written inside backticks (`) in code. And as Chapter 2 showed, many numeric columns arrived as text.

3.7.2 Test responses and duplicates

Test responses are not data about students, and duplicated submissions would count some students twice, so both must go before anything else. Test responses have a student ID starting with “TEST”, in upper or lower case. The function str_to_upper() makes the case irrelevant, and str_detect() checks for the pattern. The function distinct() then removes rows that are exact copies of another row. The response ID is different for each submission, even for duplicates, so it is dropped first:

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

[1] 600

In the pattern, ! means “not” and ^ means “at the start”. Exactly 600 rows are left: one per student.

3.7.3 Usable variable names

Names such as Q10_How worried are you about money? (1-5) are awkward to type and easy to get wrong. Short, consistent names make the code readable and reduce errors. The function rename() changes names, new name first, and the 22 questionnaire items, Q11_1 to Q11_22, are renamed in one go with rename_with(), using the scale each item belongs to:

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

responses <- responses |>
  rename(
    student_id          = `Q1_Student ID`,
    age                 = Q2_Age,
    gender              = Q3_Gender,
    faculty             = Q4_Faculty,
    programme           = Q5_Programme,
    study_mode          = `Q6_Study mode`,
    employment          = `Q7_Paid work`,
    has_children        = Q8_Children,
    lives_away          = `Q9_Moved away from family`,
    financial_worry     = `Q10_How worried are you about money? (1-5)`,
    workshop            = `Workshop group`,
    workshop_sessions   = `Workshop sessions attended`,
    considering_dropout = `Y1_Considered leaving?`
  ) |>
  rename_with(~ item_names, .cols = Q11_1:Q11_22)

The call paste0("stress_", 1:6) builds the names stress_1 to stress_6, which saves typing them all. The questionnaire’s wording is not lost: it belongs in the codebook (Chapter 2).

3.7.4 Consistent categories

If “F”, “female”, and “Female” stay separate, every table and every test treats them as three different groups. Categories are fixed by looking for a pattern rather than listing every spelling, after str_to_lower() and str_trim() have removed differences in case and stray spaces:

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), "health")  ~ "Health Sciences",
      str_detect(str_to_lower(faculty), "humanit") ~ "Humanities",
      str_detect(str_to_lower(faculty), "social")  ~ "Social Sciences",
      str_detect(str_to_lower(faculty), "natural|^sciences$") ~ "Natural Sciences"
    ),
    programme  = if_else(str_detect(str_to_lower(programme), "ph"), "PhD", "Master's"),
    study_mode = if_else(str_detect(str_to_lower(study_mode), "part"), "Part-time", "Full-time"),
    employment = if_else(str_to_lower(employment) %in% c("none", "no job"), "None", employment),
    across(c(has_children, lives_away, considering_dropout),
           ~ if_else(str_starts(str_to_lower(.x), "y"), "Yes", "No"))
  )
responses |> count(gender)

# A tibble: 2 × 2
  gender     n
  <chr>  <int>
1 Female   312
2 Male     288

responses |> count(faculty)

# A tibble: 5 × 2
  faculty              n
  <chr>            <int>
1 Education          148
2 Health Sciences    154
3 Humanities          95
4 Natural Sciences    87
5 Social Sciences    116

Two details deserve attention. The order of conditions in case_when() matters: “social sciences” also contains “sciences”, so Social Sciences is checked before Natural Sciences. And across() applies the same change to several columns at once, here turning Yes, yes, and Y into Yes.

After each fix, the categories should be counted again, as above. If a spelling was missed, it shows up immediately.

3.7.5 Missing codes and numbers stored as text

Survey tools often mark missing answers with codes such as 99 or -9. As the opening example showed, these codes must become NA before any calculation, or R will treat them as real values. The function na_if() turns a code into NA, and as.integer() then turns the text into whole numbers:

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"))),
    workshop_sessions = as.integer(workshop_sessions),
    age = parse_number(age)
  )

Do not replace 99 everywhere

It is tempting to turn every 99 in the file into NA in one go. But a 99 is not always a code. In 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.

The function parse_number() from readr reads the number out of a text value, ignoring extra text around it. It is useful for the sleep columns, where some students typed "7 hrs". A decimal comma, however, defeats it:

parse_number(c("7 hrs", "6.5", "6,5"))

[1]  7.0  6.5 65.0

The value "6,5" becomes 65, not 6.5, because in English a comma separates thousands. The comma has to be changed to a point first:

parse_number(str_replace(c("7 hrs", "6.5", "6,5"), ",", "."))

[1] 7.0 6.5 6.5

Mistakes like this produce no error message. The only protection is to check the data after each step.

3.7.6 Impossible values

Some values cannot be true, such as an age of 250 or 26 hours of sleep a night. Left in the data, a single one can dominate an average or a regression. They are typing mistakes, and since the true value cannot be known, the honest fix is to set them to NA rather than to guess (an age of 250 might have been 25, or 50):

responses |> filter(age > 100) |> select(student_id, age)

# A tibble: 1 × 2
  student_id   age
  <chr>      <dbl>
1 S0007        250

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

This is where the missing age in Chapter 1 came from. Unusual but possible values, such as a student who sleeps 4 hours a night, are a different matter: they are real data and are kept. Chapter 6 discusses how to handle them.

3.7.7 Scale scores

A questionnaire scale is scored as the average of its items, and the items must all point in the same direction before they are averaged. One stress item, stress_4 (“I feel confident handling problems in my studies”), is worded in the opposite direction to the others: agreeing with it means less stress. Averaged as it stands, it would cancel part of what the other five items measure. It must first be reversed, so that 1 becomes 5, 2 becomes 4, and so on. On a 1-to-5 scale, subtracting from 6 does exactly that:

questionnaire_clean <- responses |>
  select(student_id, all_of(item_names))

scores <- questionnaire_clean |>
  mutate(
    stress_4           = 6 - stress_4,
    stress_score       = rowMeans(pick(stress_1:stress_6), na.rm = TRUE),
    burnout_score      = rowMeans(pick(burnout_1:burnout_6), na.rm = TRUE),
    support_score      = rowMeans(pick(support_1:support_6), na.rm = TRUE),
    satisfaction_score = rowMeans(pick(satisfaction_1:satisfaction_4), na.rm = TRUE)
  ) |>
  select(student_id, ends_with("_score"))
head(scores)

# A tibble: 6 × 5
  student_id stress_score burnout_score support_score satisfaction_score
  <chr>             <dbl>         <dbl>         <dbl>              <dbl>
1 S0191              2.83          1.5           3.83               3.33
2 S0015              4.5           3.83          1.67               3   
3 S0474              3.5           4.5           1.4                2.75
4 S0418              3.17          3             1.67               3.75
5 S0049              3             3.5           3.17               3.33
6 S0537              2.83          2.5           3.2                4.75

The function pick() selects the columns of one scale, and rowMeans() averages each student’s answers across them. With na.rm = TRUE, a student who skipped one item still gets a score from the items they answered. Whether that is acceptable, and how many skipped items is too many, is a decision to report in the thesis. Chapter 9 checks that the items really do measure four separate scales.

The original items should stay in the data, with the scores stored separately as here, because the items are needed again for checking the scales.

3.7.8 From wide to long

The semester measurements sit side by side: GPA_S1, Sleep_S1, …, Wellbeing_S4, 28 columns in all. The data is not tidy, since each row holds four observations, and analyses of change over time need one row per student per semester. This is a pivot_longer() with one extra idea: each column name holds two pieces of information, the variable (GPA) and the semester (1), separated by _S. The special name .value tells R to use the first piece as a column name:

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

# A tibble: 6 × 9
  student_id semester GPA   Sleep Study Exercise Caffeine Meetings Wellbeing
  <chr>      <chr>    <chr> <chr> <chr> <chr>    <chr>    <chr>    <chr>    
1 S0191      1        3.07  5.6   31    3        140      2        64       
2 S0191      2        3.29  5,7   30    4        140      7        65       
3 S0191      3        3.35  5.5   32    3        130      5        60       
4 S0191      4        3.58  6.6   29    2        155      7        69       
5 S0015      1        2.8   6.5   15    2        210      2        34       
6 S0015      2        3.2   6.5   18    0        275      5        38

The selection matches("_S[1-4]$") picks the columns whose names end in _S1 to _S4. The rest is cleaning already familiar from the steps above: tidy names, text to numbers (with the decimal-comma fix for sleep), impossible values to NA, and finally removing the empty rows of students who had left the study:

semesters_clean <- semesters_clean |>
  rename(gpa = GPA, sleep_hours = Sleep, study_hours = Study,
         exercise_days = Exercise, caffeine_mg = Caffeine,
         supervisor_meetings = Meetings, wellbeing = Wellbeing) |>
  mutate(
    semester    = as.integer(semester),
    sleep_hours = parse_number(str_replace(sleep_hours, ",", ".")),
    across(c(gpa, study_hours, exercise_days, caffeine_mg, supervisor_meetings, wellbeing),
           parse_number),
    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))
nrow(semesters_clean)

[1] 2326

The condition if_all(gpa:wellbeing, is.na) is TRUE for rows where every measurement is missing, and ! keeps the others.

3.7.9 Checking the result

The last step checks what the cleaning has produced. The amount of missing data comes first, and across() inside summarise() counts the NAs in every column at once:

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

# A tibble: 1 × 9
  student_id semester   gpa sleep_hours study_hours exercise_days caffeine_mg
       <int>    <int> <int>       <int>       <int>         <int>       <int>
1          0        0     1          21          23            20          25
# ℹ 2 more variables: supervisor_meetings <int>, wellbeing <int>

Up to 25 values per measurement are still missing, about 1% of the records: answers students really did not give, and the impossible values set to NA. These are genuine gaps, and Chapter 6 discusses how to handle them.

The most satisfying check of all is a comparison with a trusted result. The clean tables in the data2thesis package were produced from this same export, so the cleaned data should match them exactly:

same <- function(mine, theirs) {
  mine   <- as.data.frame(mine)[, names(mine)]
  theirs <- as.data.frame(theirs)[, names(mine)]
  isTRUE(all.equal(mine, theirs, check.attributes = FALSE))
}
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

Both are TRUE. The cleaning is complete, and from here on the book uses the clean tables from the package.

Writing it up

A thesis reports the cleaning briefly, usually in the methods chapter, so that a reader can judge what was done to the data. For the survey export, with the numbers filled in by the code:

The survey export contained 615 responses. Test responses (3) and duplicate submissions (12) were removed, leaving 600 students. Inconsistent spellings of categories were standardised, and missing-answer codes (99 and −9) were recoded as missing. Impossible values (4 in all), such as an age of 250 years or 26 hours of sleep a night, were set to missing, because the true values could not be known. The reverse-worded item stress_4 was recoded before scale scores were computed as the mean of each scale’s items, using all items a student had answered.

Save your cleaning as a script

Put all of these steps in one script, for example 01-clean-data.R, that starts from the raw file and ends by saving the clean tables:

write_csv(semesters_clean, here::here("data", "semesters_clean.csv"))

Never edit the raw file by hand. If you find a new problem next month, you fix it in the script and run it again, and every step stays on record.

In your field: environmental and public health

airquality is a real dataset that comes with R: daily air measurements in New York from May to September 1973. Like most real data, it has gaps. The same verbs summarise it by month:

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

  Month mean_ozone missing_days
1     5   23.61538            5
2     6   29.44444           21
3     7   59.11538            5
4     8   59.96154            5
5     9   31.44828            1

Ozone levels peak in July and August, and June has the most missing days, a pattern worth knowing before any analysis: if the missing days were mostly hot ones, the June average would be too low.

3.8 Chapter review

3.8.1 Summary

Clean data is valid, accurate, complete as far as possible, consistent, and unique. Cleaning decisions shape the results, so they are made in code from an untouched raw file, recorded, reported, and checked after every step.
Tidy data has one variable per column, one observation per row, and one value per cell.
The pipe |> passes a result to the next function: read it as “and then”.
dplyr verbs: filter() chooses cases, select() chooses variables, arrange() sorts, mutate() creates variables, summarise() with .by summarises by group, and count() counts. case_when() sorts values into categories.
pivot_longer() and pivot_wider() reshape data between wide and long formats.
left_join() combines tables by an identifier; anti_join() finds rows without a match.
Cleaning survey data: remove test and duplicate rows, rename variables, fix categories by pattern, turn missing codes into NA column by column, convert text to numbers (watch decimal commas), set impossible values to NA, reverse reversed items before computing scale scores, and check the result.

3.8.2 Key terms

Data cleaning, tidyverse, tidy data, tibble, pipe, verb, grouped summary, wide format, long format, join, identifier, missing code, reversed item, scale score.

3.9 Exercises

The playground has these and more, with hints and solutions.

Using students, find the average age of students in each faculty, sorted from oldest to youngest. (Remember na.rm = TRUE.)
Add a column to semesters that is "Short" when sleep_hours is under 6, "Recommended" when it is 7 or more, and "Moderate" otherwise. Count the semester records in each category.
Using semesters, make a table of average GPA with one row per student and one column per semester, using pivot_wider(). Show the first rows.
Join semesters to students and compare the average semester GPA of full-time and part-time students.
Explain why the stress_4 item must be reversed before the stress score is calculated, and describe what would happen to the scores if it were not.
For each of the five qualities of clean data, give one example of a problem from your own field, and the cleaning decision it would require.

3.10 Further reading

R for Data Science (Wickham et al. 2023): chapters “Data transformation”, “Data tidying”, “Joins”, “Strings”, and “Missing values”.
“Tidy data” (Wickham 2014), the paper that introduced the idea, with many examples of untidy data and how to fix it.
The dplyr and tidyr websites, with a reference page for every function.

References

Wickham, Hadley. 2014. “Tidy Data.” Journal of Statistical Software 59 (10): 1–23. https://doi.org/10.18637/jss.v059.i10.

Wickham, Hadley, Mine Çetinkaya-Rundel, and Garrett Grolemund. 2023. R for Data Science: Import, Tidy, Transform, Visualize, and Model Data. 2nd ed. O’Reilly Media. https://r4ds.hadley.nz.