Tidy data

What “tidy” means: the three rules

Imagine you have just collected the term-test scores of three students across three subjects, and you drop them into a spreadsheet. Before you can compute a single class average, draw one bar chart, or train one model, your data has to sit in a shape your tools can actually work with. There is a name for that shape, and a surprisingly small set of rules that define it: tidy data.

Tidy data is just a table that obeys three rules. First, each variable forms one column. Second, each observation forms one row. Third, each value sits in its own single cell (and, as a fourth corollary, each kind of thing you measured gets its own table). That is the whole idea. A “variable” is anything you recorded that can vary — subject, score, student name. An “observation” is one complete measured unit — here, one student paired with one subject and the score that goes with that pairing. A “value” is a single entry: 85, “math”, “Mei”.

Why obsess over this? Because tidiness is a promise about the future. Once a table is tidy, every later step — filtering rows, grouping and summarizing, drawing a chart, joining to another table, feeding a model — becomes a short, predictable, one-line operation. When a table is messy, those same steps turn into fragile manual fiddling that breaks the moment the data changes. Tidy data is the small upfront investment that makes the other 80% of the job easy.

Observations, variables, and values

These three words do all the heavy lifting, so let us pin them down on a concrete table. Suppose Mei scored 85 in math, 92 in science, and 80 in english. The thing we keep re-measuring — once per subject — is the variable called “score”. The labels math, science, english are the values of a second variable called “subject”. And “Mei” is a value of a third variable, “name”. A single completed measurement — Mei, math, 85 — is one observation, and in a tidy table it occupies exactly one row.

Variables come in a few flavors, and naming them helps you choose charts and methods later. A numerical variable is a number you can do arithmetic on — score, age, revenue. A categorical variable is a label from a fixed set of options — subject, city, yes/no. You will meet the full taxonomy in the variable-types entry; for now just notice that “score” is numerical while “subject” and “name” are categorical, and that a tidy table is perfectly happy to hold a mix of both, one type per column.

Here is the subtle part nobody warns beginners about: what counts as a “variable” versus an “observation” depends on the question you are asking. Is “subject” a variable (so each student-subject pair is an observation), or is each student a single observation with three score columns? Both can be legitimate. The right tidy shape is the one whose row — its unit of observation — matches the thing you want to analyze. If you will compare subjects, make subject a column and put one student-subject pair per row.

Common messy shapes (and why they hurt)

Most real spreadsheets arrive messy in one of a handful of recognizable ways. Learning to spot them is half the battle, because each mess maps to a specific, predictable pain downstream. The single most common offender is column headers that are actually values, not variable names. Picture our gradebook stored as columns name, math, science, english. It looks fine to a human, but math, science, and english are not three different variables — they are three values of one variable, subject. The variable “score” has been scattered across three columns and given no name at all.

Why does that hurt? The moment you ask “what is the average across all subjects?” you cannot write one clean instruction, because the value you want lives under three different column names. Add a fourth subject next month and every formula and chart that named those columns breaks. A tidy table with a single score column would have shrugged the change off.

1. Column headers are values, not names. Years (2023, 2024) or categories (math, science) used as column headers — the real variable is unnamed and split across columns.
2. Two variables crammed into one column. A cell like “85/A” mixing a number and a letter grade, or “Taipei, 2024” mixing place and year. You cannot sort, filter, or compute on either piece.
3. Variables stored in both rows and columns. A table where some rows are variables and some columns are too — the classic “half-pivoted” spreadsheet that resists every operation.
4. Several kinds of thing in one table. Mixing students and teachers (different units) in one sheet forces awkward blank cells and ambiguous rows.
5. One kind of thing spread across many tables. One sheet per class, one CSV per month — the same observation type fragmented, so any whole-picture question needs gluing first.

The fixes mirror the messes. Headers-that-are-values are repaired by reshaping wide-to-long; two-variables-in-a-cell by splitting on a separator; row-and-column mixes by pivoting; mixed units by separating into one table per unit; and many-tables-one-unit by stacking and then joining. The next section walks through the most common case end to end.

A messy table, tidied step by step

Let us do it for real. We start with the wide gradebook — three students, with math, science, and english as columns — exactly the headers-are-values mess. Our goal is the long form: columns name, subject, score, with one row per student-subject pairing. The operation that turns wide into long is called reshaping, or pivoting; in pandas the wide-to-long direction is melt, and in R's tidyr it is pivot_longer. The name does not matter; the move does.

import pandas as pd

# Wide gradebook: one column per subject (subjects are VALUES, not variables)
wide = pd.DataFrame({
    'name':    ['Mei', 'Jun', 'Ana'],
    'math':    [85, 90, 78],
    'science': [92, 88, 95],
    'english': [80, 84, 91],
})

# Tidy it: one row per (student, subject) observation
tidy = wide.melt(id_vars='name', var_name='subject', value_name='score')
# tidy now has 9 rows and 3 columns: name | subject | score

How big does the tidy table get? Each of the n students contributes one row for each of the k measured subjects, so the long form has n times k rows. With three students and three subjects that is nine rows — the three original score cells per student, now laid out one per line. This little multiplication is worth internalizing, because it is also why long form can balloon for very wide source data.

The long-form row count. Here 3 students × 3 subjects = 9 tidy rows.

Now watch the payoff. With one named score column and one named subject column, the question we struggled with earlier — the average per subject — collapses to a single, self-explanatory line. This is the group-by pattern: split the rows into groups, apply a summary to each, combine the results.

# Now an average-per-subject is one short, predictable line
tidy.groupby('subject')['score'].mean()

# math     84.33
# science  91.67
# english  85.00

Tidy data and the tools that love it

Tidy form is not an aesthetic preference; it is the shape the entire modern toolchain quietly expects. A dataframe — the table-shaped object at the heart of pandas, R, and SQL — vectorizes operations across rows, which is fast and concise only when each column is one clean variable. Plotting libraries built in the “grammar of graphics” style (ggplot2, plotnine, seaborn) ask you to map a column to each visual channel: this column to the x-axis, that column to color. That mapping is trivial in long form and nearly impossible in wide form.

Tidy tables also snap together. The moment your data is one observation type per table, combining datasets becomes a join: line two tables up on a shared column — a key that identifies rows — and merge them. Suppose a second little lookup table records which department owns each subject. Because both tables are tidy and share a clean “subject” column, gluing them is one call.

# A second tidy lookup table: which department owns each subject
subjects = pd.DataFrame({
    'subject':    ['math', 'science', 'english'],
    'department': ['STEM', 'STEM', 'Humanities'],
})

# Join on the shared key 'subject' — tidy tables snap together
tidy.merge(subjects, on='subject', how='left')

When to bend the rules

Tidy (long) form is the best default for analysis, but it is a default, not a commandment — and a good analyst knows when to bend it on purpose. The most common reason is people. A long table with hundreds of name-subject-score rows is miserable to read; a wide table with one row per student and a column per subject is exactly how a human wants to scan a report card. For presentation, you reshape long back to wide on the way out.

There are technical exceptions too. Many machine-learning algorithms want a wide “design matrix”: one row per unit you predict, and one column per feature — closer to wide than to long. Storage and speed can also push the other way: the n times k row blow-up of long form can be wasteful for very wide data, and some specialized stores (matrices, images, dense time series) have a natural array shape that no one should force into three skinny columns.

A tidiness checklist

Run this checklist over any new table before you analyze it. It takes a minute and saves hours. If a row fails, you now know the exact reshape, split, or split-into-tables move that fixes it.

1. Is each column exactly one variable, with a real name? If a column header is itself a value (a year, a category), reshape wide-to-long.
2. Is each row exactly one observation, at the unit you intend to analyze? Decide what one row means before reshaping.
3. Does each cell hold a single value? Split any “85/A” or “Taipei, 2024” cell into separate columns.
4. Does each table describe just one kind of thing? If students and teachers share a sheet, split into one table per unit.
5. Are the same observations spread across many files or sheets? Stack them into one table (and keep a key so you can join later).
6. Are variable types clean — numbers as numbers, dates as dates, categories as labels — and missing values marked, not faked with zeros or blanks?

That is tidy data: one column per variable, one row per observation, one value per cell, one kind of thing per table. It is the small, boring discipline that makes exploration, charting, joining, and modeling fall into place. Next in this track we tackle the dirt that tidy structure does not by itself remove — missing values, outliers, duplicates, and the careful art of cleaning and joining real-world data without losing or doubling a single row.