Skip to main content

9  Tidy Data

9.1 Why This Week Matters

Field and agency spreadsheets are usually built for reading, not computing: one column per year, per site, or per category. That layout is fine for a person scanning a table and awkward for group_by() and ggplot(), which want the category in one column.

Tidy data means:

  • each variable is a column
  • each observation is a row
  • each value is a cell

Reshaping is the easy part. The hard part is knowing what a column means before you reshape it.

flowchart LR
  A[Read the wide table] --> B[Ask what each column means]
  B --> C[pivot_longer to tidy]
  C --> D[Summarize]
  D --> E[pivot_wider to show]

We practise on monthly drought conditions for the lower 48 states, 1895-2022: the NOAA 9-month Standardized Precipitation Index (SPI), via drought.gov and TidyTuesday 2022-06-14. 73,344 rows (48 states x 1,528 months), values in percent of state area.

This is a short module: the second half of class is Your Project Repository.

9.2 Learning Goals

By the end of this module, you should be able to:

  • say whether a table is tidy, and name the variable that is spread across columns
  • reshape wide to long with pivot_longer() (names_to, values_to)
  • predict the row count after a pivot, and check it
  • reshape long to wide with pivot_wider() for a table people read
  • explain why cumulative categories cannot be added

9.3 Vocabulary

Term Meaning
Tidy One variable per column, one observation per row, one value per cell
Wide Values of one variable spread across several columns (d0, d1, … or y2024, y2025)
Long That variable in one column, with one row per value
names_to In pivot_longer(): the NEW column that holds the old column names. In quotes
values_to In pivot_longer(): the NEW column that holds the numbers. In quotes
Cumulative category “At least this severe”: d2 includes everything in d3 and d4

9.4 The Main Idea

One row of the drought file, Nebraska, August 2012:

state date d0 d1 d2 d3 d4
nebraska d_20120801 97.5 93.7 79.5 65.7 44.9

Add the five columns and you get 381.3%. A state cannot be 381% covered. The columns are cumulative: d2 is the share in severe drought or worse. In every one of the 73,344 rows, d0 >= d1 >= d2 >= d3 >= d4. So 79.5% of Nebraska was at least in severe drought, and only 79.5 - 65.7 = 13.8% was in severe drought and nothing worse.

The code would have added them without complaint. The definition (in the dataset’s readme, and visible in the numbers) is what catches it. This is the Module 05 trap again: total rows stacked on the rows they total.

9.5 Build The Workflow

9.5.1 Wide to long: pivot_longer()

drought_long <- drought |>
  select(state, date, d0:d4) |>
  pivot_longer(
    d0:d4,
    names_to = "category",
    values_to = "pct_area"
  )
  • The first argument: columns that already exist (no quotes).
  • names_to and values_to: new column names you choose (in quotes).
  • Rows: each original row becomes five, so 73,344 x 5 = 366,720. Predict the row count before you run a pivot, then check it.

9.5.2 Tidy, then summarize

With the category in one column, Module 05 tools work directly: filter(category == "d2"), then group_by(year) and summarize(). On this index, Nebraska’s worst severe-drought year is 1934, not 2012 (2012 is fifth).

9.5.3 Long to wide: pivot_wider()

plains |>
  pivot_wider(names_from = year, values_from = mean_d2)

Tidy is for computing; wide is often better for a table in a report. New column names that are numbers (2012) need backticks in code: `2012`.

9.6 Common Problems

You see Why Fix
object 'category' not found names_to = category without quotes names_to = "category"
Far more columns than expected after the pivot Forgot select(); w0-w4, x0, x9 came along select() the columns you need first
Row count is not original x 5 Stacked the wrong columns (c(d0, d4) is two columns, d0:d4 is five) Use d0:d4
Percentages over 100 after a sum Summed cumulative categories Use one category, or subtract (d2 - d3)
pivot_wider() gives list-columns and a warning More than one value per cell: rows not summarized first group_by() + summarize() to one row per cell, then pivot

9.7 Try It

In your Module 05 repo, class/week_06_follow_along.R: Sections 1-4, then the Challenge.

9.7.1 In-Class Challenge Hints

Try each step for a few minutes before opening a hint.

Same pipeline as Section 3 (Nebraska severe drought). Change three things: the state, the category, and add one more condition for the years.

Extreme drought is d3. Years 2000 and later is year >= 2000, inside the same filter(). Then group_by(year), summarize() the mean of pct_area, and arrange(desc()).

Start again from drought (the wide table), not drought_long: the wet columns were dropped by select(). Filter to Nebraska, keep w0:w4, pivot them, make year, then summarize like Step 1.

select(state, date, w0:w4), then pivot_longer(w0:w4, names_to = ..., values_to = ...). Copy the year line from Section 2. Filter category == "w1" and year >= 1950.

9.8 Connect To The Assignment

There is no weekly assignment for Module 06. Reshaping comes back in your project: if your data has one column per year, site, or species, the proposal’s Plan section should say which columns you will pivot and what the row count should be afterwards.

9.9 Finish The Professional Workflow