This module introduces the tidy data philosophy. You’ll learn what makes data “tidy” and what pitfalls to avoid, explore practical tools like melt and pivot to reshape your data, and understand why standardizing data structure makes analysis faster and more reliable.
Have you ever spent hours wrestling with a dataset before you could even start analyzing it? It is often said that 80% of data analysis is spent cleaning and preparing data. This isn’t an exaggeration.
Getting your data into the right shape makes everything else easier. The good news is that once you understand the tidy data philosophy, you can apply it consistently across projects. If you want to dive deeper, read Tidy Data by Hadley Wickham.
At its core, tidy data is a standard way of mapping the meaning of a dataset to its structure. Whether your data is messy or tidy depends entirely on how rows, columns, and tables match up with observations, variables, and types.
Let’s talk about the three core principles. First, each variable forms its own column. A variable measures the same underlying attribute (like height, temperature, or duration) across different units.
Second, each observation forms a row. An observation captures all measurements on the same unit (like a person, a day, or a race) across different attributes.
Third, each type of observational unit gets its own table. In a study of allergy medication, you’d have separate tables for demographic data, daily medical data, and meteorological data, not one giant table mixing everything together.
Why does this matter? Tidy datasets are dramatically easier to manipulate, model, and visualize. They make exploration faster and analysis clearer. Most importantly, they standardize data organization, making your code reusable and reliable.
Now let’s flip the perspective and look at the most common mistakes. When you first encounter messy data, it usually falls into one of five patterns.
The first problem is that column headers often contain values instead of variable names. Imagine a table where months (“Jan”, “Feb”, “Mar”) are the column headers, rather than having a single “Month” column with those values.
The second problem is multiple variables stored in one column. You might find a column like “height_weight” containing values like “5.5_130” instead of splitting those into separate “height” and “weight” columns.
The third problem is variables scattered across both rows and columns. A piece of information like gender might be encoded in a specific column and also hidden within the values of another column.
The fourth problem is mixing different types of observational units in one table. For example, a single table containing both patient demographic information and medical test results mashes two fundamentally different kinds of data together.
The fifth and final problem is splitting a single observational unit across multiple tables. Patient information scattered across one table for addresses, another for test results, and another for appointments, with no clean way to link them together, makes every analysis painful.
How do we fix messy data? Let’s learn practical tools to reshape your messy data into tidy form. The examples here are adapted from Python for Data Science.
Picture data stored in “wide” format, where different columns represent different variables of the same type. Consider this simple example:
| first | last | height | weight | |
|---|---|---|---|---|
| 0 | John | Smith | 5.5 | 130 |
| 1 | Mary | Doe | 5.0 | 110 |
Notice how “height” and “weight” sit in separate columns. This wide format breaks tidy principles. It makes comparisons awkward when you want to plot or analyze these measurements together.
The pandas.DataFrame.melt() method solves this by transforming data from wide to long format. After melting, instead of separate columns for “height” and “weight”, you get one column for the variable type and another for the value.
Let’s see it in action:
import pandas as pd
df_melted = df.melt(
id_vars=['first', 'last'],
var_name='quantity',
value_name='value'
)
df_melted| first | last | quantity | value | |
|---|---|---|---|---|
| 0 | John | Smith | height | 5.5 |
| 1 | Mary | Doe | height | 5.0 |
| 2 | John | Smith | weight | 130.0 |
| 3 | Mary | Doe | weight | 110.0 |
Now each row represents a single measurement for an individual. If you want to compare height and weight, they’re in the same column format, making analysis natural.
This is the essence of tidy data.
What if you need to go the other direction? Sometimes the opposite problem arises. Your data starts in “long” format, with a separate row for each measurement type (like “cases” or “population”) for each country and year.
This scatters information about a single observation across multiple rows, making it hard to see all statistics for country A in 2020 at once.
import numpy as np
# Long format: each row is a different variable for country and year
df = pd.DataFrame({
'country': ['A', 'A', 'A', 'A', 'B', 'B', 'B', 'B'],
'year': [2020, 2021, 2020, 2021, 2020, 2021, 2020, 2021],
'variable': ['cases', 'cases', 'population', 'population', 'cases', 'cases', 'population', 'population'],
'value': [100, 200, 120, 220, 130, 230, 140, 240]
})
df| country | year | variable | value | |
|---|---|---|---|---|
| 0 | A | 2020 | cases | 100 |
| 1 | A | 2021 | cases | 200 |
| 2 | A | 2020 | population | 120 |
| 3 | A | 2021 | population | 220 |
| 4 | B | 2020 | cases | 130 |
| 5 | B | 2021 | cases | 230 |
| 6 | B | 2020 | population | 140 |
| 7 | B | 2021 | population | 240 |
The pivot() function reshapes the data so that each observation (a country-year combination) has its measurements spread across columns. This transforms long data back to a wider, more readable format.
Now each row shows all measurements for a single country and year, making analysis straightforward.
What about more complex cases? A trickier situation emerges when your data has multi-level column headers, with variables split across two or more header rows. Imagine measurements for different people and types, with test results for multiple groups all shown as columns.
This nested structure makes it hard to access and visualize data cleanly.
| P1 | P2 | |||
|---|---|---|---|---|
| A | B | A | B | |
| 0 | 0.371574 | 0.027619 | 0.907870 | 0.479421 |
| 1 | 0.752297 | 0.720421 | 0.689671 | 0.440814 |
| 2 | 0.105156 | 0.507902 | 0.383719 | 0.444173 |
| 3 | 0.042351 | 0.788528 | 0.474429 | 0.776338 |
The stack() method solves this by converting one level of column headers into a row index, transforming wide data to long. Now each row represents a single measurement, and all values of the same variable sit together in one column.
The unstack() method does the reverse, spreading data back out from the index into columns. Let’s see both methods in action:
| P1 | P2 | ||
|---|---|---|---|
| 0 | A | 0.371574 | 0.907870 |
| B | 0.027619 | 0.479421 | |
| 1 | A | 0.752297 | 0.689671 |
| B | 0.720421 | 0.440814 | |
| 2 | A | 0.105156 | 0.383719 |
| B | 0.507902 | 0.444173 | |
| 3 | A | 0.042351 | 0.474429 |
| B | 0.788528 | 0.776338 |