Data Tidying

Data Skills for Psychology — Module 7

Welcome!

These slides are adapted from Applied Data Skills Ch. 8: Data Tidying.

Today’s Goals

By the end of this class, you will be able to:

Recognize what makes data “tidy” (and what makes it messy)
Reshape data between wide and long formats
Split a single column into multiple columns
Chain tidying steps together with the pipe (|>)

Setup

Make sure these packages are loaded:

library(tidyverse)   # readr, dplyr, tidyr, stringr, etc.
library(readxl)      # read .xlsx files
library(janitor)     # clean_names()

Tip

Grab the Data tidying cheat sheet — it’s a great reference for the pivot_* and separate*() functions.

Download Today’s Data

Save these into your data/ folder:

Both contain the same information — proportion of women in parliaments across countries and years — but stored very differently.

Why Tidy?

A Reality Check

“Every messy dataset is messy in its own way.” — Hadley Wickham

Data tidying is often the most time-consuming part of an analysis.

Moving between formats that are easy for humans to read and formats that are easy for computers to use takes practice — that conceptual shift is the real skill of this chapter.

Three Words to Pin Down

Observation: all the information about a single “thing” in a single condition (e.g., one country in one year).

Variable: one type of information about that observation (e.g., year, number of MPs).

Value: the data for one variable for one observation (e.g., 2025).

The Three Rules of Tidy Data

For data to be “tidy”:

Each observation has its own row
Each variable has its own column
Each value has its own cell

When data is tidy, you can keep using the same code patterns even when the dataset grows or changes shape.

Untidy vs Tidy

An Untidy Example

untidy <- read_csv("data/untidy.csv", show_col_types = FALSE)
untidy

What’s “off” about this table?

The country column squeezes in two values (name and code)
Each year has its own column (1950, 1975, 2000, 2025)
Each cell holds two numbers: women / members

Why That Hurts

Suppose you want the average proportion of women per year. In Excel you would:

Split each year column into women_* and members_*
Compute proportion for each year (4 new columns)
Average each new column separately

If a new year of data arrives, you redo every step. Painful.

The Tidy Version

tidy_data <- read_csv("data/tidy.csv", show_col_types = FALSE)
tidy_data

Each row is one country in one year. Five columns: country_name, country_code, year, members, women.

And the Calculation Becomes…

tidy_data |>
  mutate(prop_women = women / members) |>
  summarise(
    avg_prop_women = mean(prop_women),
    .by = year
  )

The same code works whether you have 4 years or 40. That’s the power of tidy.

Reshaping Data

Wide vs Long

Wide format: all measurements about one thing on the same row.

Long format: each measurement on its own row.

You’ll often need to convert between them. Think of it as the R version of a pivot table in Excel.

`pivot_longer()`: Wide → Long

The Idea

pivot_longer() takes several columns and stacks them into two new columns:

one for the old column names
one for the values that were in those columns

Three key arguments:

cols — which columns to “fold up”
names_to — name of the new column for the old headers
values_to — name of the new column for the old values

In Code

long <- pivot_longer(
  data = untidy,
  cols = `1950`:`2025`,
  names_to = "year",
  values_to = "women_members"
)

long

Now there’s one row per country-year, with year as a real variable.

Warning

Because the column names are numbers, you must wrap them in backticks. Without them, R thinks 1950:2025 means “columns 1950 through 2025.”

Error in `pivot_longer()`:
! Can't select columns past the end.
ℹ Locations 1950, 1951, …, 2025 don't exist.

Activity: Sketch a Long Version

Try it!

Convert this table to long format. You don’t need to write code — just sketch it on paper or in a spreadsheet.

band	twitter	instagram
The Beatles	3.8	3.8
The Rolling Stones	3.4	3.1
One Direction	31.3	22.8

How many rows should the long version have?
What should the new column names be?
Bonus: What would the long version look like if we also had a youtube column?

`pivot_wider()`: Long → Wide

The Idea

pivot_wider() is the reverse — it spreads one column’s values into new column names.

Three key arguments:

id_cols — column(s) that uniquely identify each new row
names_from — column whose values become the new headers
values_from — column whose values fill the new cells

In Code

wide <- pivot_wider(
  data = long,
  id_cols = year,
  names_from = country,
  values_from = women_members
)

wide

Now each row is a year, and each country has its own column. Useful for some kinds of comparison or display.

One Value Per Cell

The Problem

Look back at our long data — the women_members column still holds two values in each cell, like "3 / 257".

Tidy data needs one value per cell, so we need to split this column.

`separate()` to the Rescue

split_data <- separate(
  data = long,
  col = women_members,        # the column to split
  into = c("women", "members"),  # the new column names
  sep = " / ",                # how to split
  remove = TRUE,              # drop the original column?
  convert = TRUE              # try to fix data types?
)

split_data

convert = TRUE is handy — it’ll turn "3" and "257" into actual numbers automatically.

Activity: Split the `country` Column

Try it!

The country column still combines two pieces of information ("Argentina (ARG)").

Use separate() to split it into country_name and country_code
By default, separate() splits at any non-letter, non-number character
You may need the extra = "drop" argument to handle the trailing )

tidy_data <- separate(
  data = split_data,
  col = country,
  into = c("country_name", "country_code"),
  extra = "drop"
)

Pipes in Action

The Same Workflow, Without Pipes

Here’s everything we did, written as separate steps:

untidy <- read_csv("data/untidy.csv", show_col_types = FALSE)

long <- pivot_longer(untidy,
                     cols = `1950`:`2025`,
                     names_to = "year",
                     values_to = "women_members")

split_data <- separate(long,
                       col = women_members,
                       into = c("women", "members"),
                       sep = " / ",
                       convert = TRUE)

tidy_data <- separate(split_data,
                      col = country,
                      into = c("country_name", "country_code"),
                      extra = "drop")

Four intermediate objects. Easy to lose track of which one is “current.”

The Pipe Version

tidy_data <- read_csv("data/untidy.csv", show_col_types = FALSE) |>
  pivot_longer(
    cols = `1950`:`2025`,
    names_to = "year",
    values_to = "women_members"
  ) |>
  separate(
    col = women_members,
    into = c("women", "members"),
    sep = " / ",
    convert = TRUE
  ) |>
  separate(
    col = country,
    into = c("country_name", "country_code"),
    extra = "drop"
  )

One object, read top-to-bottom: read it, then pivot, then separate, then separate again.

How to Read a Pipe

Read in the data with read_csv() — and then
Reshape longer with pivot_longer() — and then
Split women_members into two columns — and then
Split country into two columns

Note

You can debug a pipe by highlighting from the start to just before the pipe you want to stop at, and running it. The output is the data at that step.

When to Break the Chain

Don’t feel obligated to fit every step into one pipeline.

Break it up when:

The chain gets so long it’s hard to read
You want to inspect or save an intermediate result
You’re debugging and need to isolate a step

Pipes are a tool for clarity, not a contest.

Wrapping Up

Key Takeaways

Tidy data: one observation per row, one variable per column, one value per cell
pivot_longer() stacks wide columns into a single column (great for plotting and summarising)
pivot_wider() does the reverse (great for display)
separate() splits a column with multiple values per cell into several columns
Chain tidying steps with |> to keep your code readable

Tidying is a conceptual skill — practice with real datasets is the only way to get fluent.

Data Tidying

Welcome!

Today’s Goals

Setup

Download Today’s Data

Why Tidy?

A Reality Check

Three Words to Pin Down

The Three Rules of Tidy Data

Untidy vs Tidy

An Untidy Example

Why That Hurts

The Tidy Version

And the Calculation Becomes…

Reshaping Data

Wide vs Long

pivot_longer(): Wide → Long

The Idea

In Code

Activity: Sketch a Long Version

pivot_wider(): Long → Wide

The Idea

In Code

One Value Per Cell

The Problem

separate() to the Rescue

Activity: Split the country Column

Pipes in Action

The Same Workflow, Without Pipes

The Pipe Version

How to Read a Pipe

When to Break the Chain

Wrapping Up

Key Takeaways

`pivot_longer()`: Wide → Long

`pivot_wider()`: Long → Wide

`separate()` to the Rescue

Activity: Split the `country` Column