6  Tidy Data

The verbs in the last chapter assume your data arrives in a sensible shape. Real data often does not. This chapter is about what “tidy” data means, why the tidyverse cares so much about it, and how to clean up a dataset that shows up messy.

Learning Objectives

By the end of this chapter you should be able to:

  1. Explain what “tidy data” means and why it matters.
  2. Recognize common ways real datasets violate tidiness, and describe the fix.
  3. Clean up awkward column names.
  4. Combine several verbs into a single readable pipeline that answers a real question.

6.1 Why Shape Matters

When you meet a new dataset in the wild, it is almost never in exactly the form you need. Column names are weird. Units are inconsistent. Dates are stored as strings. Some rows belong to a different year. The things you actually want to compute are combinations of things that are in the data. Data transformation is the work of getting from the dataset as it exists to the dataset you can actually analyze.

In Excel, this work happens in a mix of formulas, filters, and manual edits. In R, it happens in a script — which means it is reproducible, auditable, and easy to re-run. This is one of R’s biggest advantages.

The tidyverse package dplyr provides a small set of verbs for data transformation that are powerful enough to express most of what you will ever need. The key insight of dplyr is that most data manipulation is a sequence of simple operations, and if you have the right small vocabulary, complex transformations become readable.

6.2 Tidy Data

Before we dive into verbs, a philosophical detour.

Hadley Wickham (one of the creators of the tidyverse) has written extensively about what he calls tidy data (Wickham et al. 2023). The idea is simple: a dataset is “tidy” if:

  1. Every variable has its own column.
  2. Every observation has its own row.
  3. Every value has its own cell.

That sounds obvious, but a huge fraction of real datasets violate it. Common problems:

  • Column headers are values, not variable names. For example, a table with columns 2020, 2021, 2022, 2023 where each column contains yields. The column header should be a variable called year; the values should be in a single yield column.
  • Multiple variables in one column. For example, a region_variety column that contains "South_InVigor". These should be two separate columns.
  • Observations split across rows. For example, one row for price and another for quantity of the same product. These should be columns.

You can do analysis on untidy data, but it is much harder. Almost every tool in the tidyverse assumes tidy input. If your data is not tidy, your first step should usually be to make it tidy using tools like pivot_longer and pivot_wider (which you will meet in Module 5, or whenever it comes up naturally).

For now the point is: when you are reading in a dataset, look at the shape. Is each row an observation? Is each column a variable? If not, fixing that is the first step.

6.3 Combining It All

Let’s do something realistic. Suppose we want to answer: “For each region, what was the mean yield (in t/ha) of fields larger than 100 acres, in 2025, for the top three varieties by total production?”

yields |>
  filter(year == 2025, acres > 100) |>
  mutate(yield_t_ha = yield_bu_acre * 0.0560,
         total_tonnes = yield_t_ha * acres * 0.4047) |>
  group_by(variety) |>
  mutate(variety_total = sum(total_tonnes, na.rm = TRUE)) |>
  ungroup() |>
  filter(dense_rank(desc(variety_total)) <= 3) |>
  group_by(region, variety) |>
  summarise(mean_yield_t_ha = mean(yield_t_ha, na.rm = TRUE),
            n_fields = n(),
            .groups = "drop") |>
  arrange(region, desc(mean_yield_t_ha))

This is a lot. Don’t panic. Read it top to bottom and notice how each step does one thing:

  1. Filter to 2025 and fields over 100 acres.
  2. Compute metric yield and total production for each field.
  3. For each variety, compute its total production across all its fields. Here we use group_by(variety) followed by mutate (not summarise) on purpose: summarise would collapse each variety down to a single row, but mutate keeps all the rows and just adds the group total as a new column on each one. (After a grouped operation, call ungroup() to remove the grouping so later steps behave normally.)
  4. Keep the top three varieties by total production. desc(variety_total) sorts largest-first; dense_rank(...) assigns rank 1 to the biggest, 2 to the next, and so on; and <= 3 keeps ranks 1, 2, and 3.
  5. Regroup by region and variety and summarise down to one row per group, computing the mean yield and the number of fields (n() counts the rows in each group). The .groups = "drop" argument just tells summarise to return an ordinary, ungrouped data frame afterward (without it, R prints a chatty message about how it left the grouping).
  6. Sort the final table.

Do not worry about writing something this long yet — the point is that each line is one small, readable step. Writing it as a pipeline makes the logic visible. Compare with how you would do this in Excel — probably three PivotTables, a manual filter, and a lot of copy-pasting between sheets.

6.4 Renaming and Cleaning Column Names

Real-world datasets often have terrible column names: Yield (bu/ac), REGION_NAME, X1. These will work in R but are annoying to type and easy to mistype. Clean them up early:

yields |> rename(yield_bu_ac = "Yield (bu/ac)",
                 region = REGION_NAME)

For wholesale renaming, janitor::clean_names() is invaluable. (This is from the janitor package, not the tidyverse. Install it once with install.packages("janitor").)

library(janitor)
yields <- read_csv("messy_data.csv") |> clean_names()

clean_names converts everything to lowercase with underscores, strips special characters, and generally makes your column names nice. I use it on almost every dataset I read in.

6.5 Test Bank Sample

  1. (Concept.) Give two reasons why we use R in addition to Excel. Give one situation where Excel is still the right choice.
  2. (Syntax.) What does the <- operator do in R? What does c() do?
  3. (Reading data.) You have a file called yields.csv in your working directory. Write one line of R that reads it into a data frame called yields.
  4. (Summary.) Write R code that computes the mean, median, and standard deviation of the yield column of the yields data frame, ignoring any missing values.
  5. (Concept.) What does “tidy data” mean? Give an example of untidy data and how to fix it.
  6. (dplyr.) Write a dplyr pipeline that filters yields to fields in the South region, computes yield in t/ha, and sorts descending by yield.
  7. (group_by.) Write a pipeline that computes the mean and standard deviation of yield for each variety, for fields larger than 50 acres.
  8. (case_when.) A dataset has a yield column and a units column. Write code to create a yield_t_ha column that converts as needed.
  9. (Missing values.) Why is it dangerous to blindly drop missing values? Under what conditions is it safe?
  10. (Scripting.) Explain why the following is a reproducibility problem: > “I changed the CSV file in Excel, then re-ran my R script.”
  11. (AI.) Describe one situation where using an AI coding assistant would help you, and one where it could lead you astray.

6.6 Practice Exercises

Use the field_yields.csv dataset for these.

  1. Install R and Positron. Run the script from Section 4.4.
  2. Create a vector of the heights (in cm) of five people. Compute the mean, median, and standard deviation.
  3. Read in field_yields.csv and reproduce a table of summary statistics (mean, median, and standard deviation of yield) using dplyr verbs.
  4. Filter to one region and one year, and find the variety with the highest mean yield.
  5. Create a new column that flags whether each field is “above average” in yield for its region.
  6. The dataset has a few fields with a missing (NA) yield. Find them with filter(is.na(yield_bu_acre)), then compute the mean yield with and without na.rm = TRUE and explain the difference.
  7. Add a yield_t_ha column converting yield_bu_acre to tonnes per hectare (× 0.0560), then sort the fields from highest to lowest metric yield.
  8. Rewrite your Module 1 worked example (canola yields) as an R script. Compare the result with what you got in Excel.
  9. Break your script intentionally (misspell a column name) and read the error message. Can you fix it?