View on GitHub

Lecture 9: Tidy Data¶

What is Tidy Data?

Tidy data is a structured format where:

Each row represents one observation (e.g., a country in a given year).
Each column is a variable (e.g., GDP, life expectancy).
Each table represents a dataset (e.g., economic statistics).

💡 Why use tidy data?

Easier to analyze: Works well with groupby(), agg(), and visualization libraries like Seaborn.
More readable: No redundant columns.
Plays nicely with Pandas and Seaborn.

In [144]:

Copied!

import pandas as pd
import pandas as pd

Wide Format to Tidy (Long) Format¶

In the dataset below, each year's population is in a separate column, which makes it a wide format.

We can convert it to tidy format using pd.melt().

In [147]:

Copied!





# population over time
df = pd.DataFrame({
    "country": ["USA", "Canada", "Brazil"],
    "1990": [253, 28, 149],
    "2000": [282, 31, 170],
    "2010": [309, 34, 192],
    "2020": [339, 38, 209],
    "continent": ["North America", "North America", "South America"],
})

# Wide format
display(df)
# population over time
df = pd.DataFrame({
    "country": ["USA", "Canada", "Brazil"],
    "1990": [253, 28, 149],
    "2000": [282, 31, 170],
    "2010": [309, 34, 192],
    "2020": [339, 38, 209],
    "continent": ["North America", "North America", "South America"],
})

# Wide format
display(df)

	country	1990	2000	2010	2020	continent
0	USA	253	282	309	339	North America
1	Canada	28	31	34	38	North America
2	Brazil	149	170	192	209	South America

`pd.melt()`¶

id_vars: The columns that stay the same (identifiers).
var_name: Name of the new column that will hold the old column headers (years).
value_name: Name of the new column that will store the values (population in this case).

In [150]:

Copied!

df_tidy = df.melt(id_vars=["country", "continent"], var_name="year", value_name="population")
display(df_tidy)
df_tidy = df.melt(id_vars=["country", "continent"], var_name="year", value_name="population")
display(df_tidy)

	country	continent	year	population
0	USA	North America	1990	253
1	Canada	North America	1990	28
2	Brazil	South America	1990	149
3	USA	North America	2000	282
4	Canada	North America	2000	31
5	Brazil	South America	2000	170
6	USA	North America	2010	309
7	Canada	North America	2010	34
8	Brazil	South America	2010	192
9	USA	North America	2020	339
10	Canada	North America	2020	38
11	Brazil	South America	2020	209

Notice how each row now represents one country in one year, and each column is a single variable.

Converting Tidy (Long) Format Back to Wide Format¶

If you ever need to go back to wide format, you can use pivot() or pivot_table().

In [151]:

Copied!

df_wide = df_tidy.pivot(index="country", columns="year", values="population")
display(df_wide)
df_wide = df_tidy.pivot(index="country", columns="year", values="population")
display(df_wide)

year	1990	2000	2010	2020
country
Brazil	149	170	192	209
Canada	28	31	34	38
USA	253	282	309	339

Here, each row is a country, and each column is a year—back to wide format.

Summarizing Tidy Data with `groupby()`¶

Tidy data makes it straightforward to group and summarize.

`groupby("year")["population"].mean()`¶

This computes the mean population for each year across all countries.

In [164]:

Copied!

for key,data in df_tidy.groupby("year"):
    display(key)
    display(data)
for key,data in df_tidy.groupby("year"):
    display(key)
    display(data)

'1990'

	country	continent	year	population
0	USA	North America	1990	253
1	Canada	North America	1990	28
2	Brazil	South America	1990	149

'2000'

	country	continent	year	population
3	USA	North America	2000	282
4	Canada	North America	2000	31
5	Brazil	South America	2000	170

'2010'

	country	continent	year	population
6	USA	North America	2010	309
7	Canada	North America	2010	34
8	Brazil	South America	2010	192

'2020'

	country	continent	year	population
9	USA	North America	2020	339
10	Canada	North America	2020	38
11	Brazil	South America	2020	209

In [161]:

Copied!

df_year_mean = df_tidy.groupby("year")["population"].std()
display(df_year_mean)
# 
df_year_mean = df_tidy.groupby("year")["population"].std()
display(df_year_mean)
#

year
1990    112.606986
2000    125.741799
2010    138.008454
2020    150.964676
Name: population, dtype: float64

Grouping by Multiple Columns¶

We can also group by both year and country.

In [ ]:

Copied!

df_year_country_sum = df_tidy.groupby(["year", "continent"])["population"].sum()
display(df_year_country_sum)
df_year_country_sum = df_tidy.groupby(["year", "continent"])["population"].sum()
display(df_year_country_sum)

year  continent    
1990  North America    281
      South America    149
2000  North America    313
      South America    170
2010  North America    343
      South America    192
2020  North America    377
      South America    209
Name: population, dtype: int64

This returns a multi-index Series, showing the population by year and by country.

`agg()` for Multiple Summaries¶

The agg() function lets us apply multiple aggregations at once.

For instance, we can find the mean and the max population per year.

In [167]:

Copied!

df_agg = df_tidy.groupby("year").agg({"population": ["mean", "max","sum"]})
display(df_agg)
df_agg = df_tidy.groupby("year").agg({"population": ["mean", "max","sum"]})
display(df_agg)

	population
	mean	max	sum
year
1990	143.333333	253	430
2000	161.000000	282	483
2010	178.333333	309	535
2020	195.333333	339	586

This shows the average (mean) population and the maximum (max) population in each year.

Handling Missing Data¶

Let's introduce some missing values to demonstrate dropna() and fillna().

In [169]:

Copied!

# Create a copy with artificially introduced NaNs
df_missing = df_tidy.copy()
df_missing.loc[(df_missing["country"] == "Brazil") & (df_missing["year"] == "2020"), "population"] = None

display(df_missing)
# Create a copy with artificially introduced NaNs
df_missing = df_tidy.copy()
df_missing.loc[(df_missing["country"] == "Brazil") & (df_missing["year"] == "2020"), "population"] = None

display(df_missing)

	country	continent	year	population
0	USA	North America	1990	253.0
1	Canada	North America	1990	28.0
2	Brazil	South America	1990	149.0
3	USA	North America	2000	282.0
4	Canada	North America	2000	31.0
5	Brazil	South America	2000	170.0
6	USA	North America	2010	309.0
7	Canada	North America	2010	34.0
8	Brazil	South America	2010	192.0
9	USA	North America	2020	339.0
10	Canada	North America	2020	38.0
11	Brazil	South America	2020	NaN

`dropna()`¶

Removes rows with missing values.

In [173]:

Copied!

df_dropped = df_missing.dropna(subset=["population"])
display(df_dropped)
df_dropped = df_missing.dropna(subset=["population"])
display(df_dropped)

	country	continent	year	population
0	USA	North America	1990	253.0
1	Canada	North America	1990	28.0
2	Brazil	South America	1990	149.0
3	USA	North America	2000	282.0
4	Canada	North America	2000	31.0
5	Brazil	South America	2000	170.0
6	USA	North America	2010	309.0
7	Canada	North America	2010	34.0
8	Brazil	South America	2010	192.0
9	USA	North America	2020	339.0
10	Canada	North America	2020	38.0

Brazil's 2020 row is completely removed because of the missing population.

`fillna()`¶

Fills missing values with a specified value or method.

In [174]:

Copied!

df_filled = df_missing.fillna(0)
display(df_filled)
df_filled = df_missing.fillna(0)
display(df_filled)

	country	continent	year	population
0	USA	North America	1990	253.0
1	Canada	North America	1990	28.0
2	Brazil	South America	1990	149.0
3	USA	North America	2000	282.0
4	Canada	North America	2000	31.0
5	Brazil	South America	2000	170.0
6	USA	North America	2010	309.0
7	Canada	North America	2010	34.0
8	Brazil	South America	2010	192.0
9	USA	North America	2020	339.0
10	Canada	North America	2020	38.0
11	Brazil	South America	2020	0.0

Now, the missing value is replaced with 0.

Combining Data with `merge()`¶

Often, you'll have multiple DataFrames that need to be joined.

Below is an example for merging a GDP dataset with our population dataset.

In [175]:

Copied!





gdp_data = pd.DataFrame({
    "country": ["USA", "Canada", "Brazil"],
    "year": ["2020", "2020", "2020"],
    "gdp": [21439, 1736, 1445],  # GDP in billions (fictitious or approximate)
})

# Merging on both country and year
df_merged = df_tidy.merge(gdp_data, on=["country", "year"], how="left")
display(df_merged)
gdp_data = pd.DataFrame({
    "country": ["USA", "Canada", "Brazil"],
    "year": ["2020", "2020", "2020"],
    "gdp": [21439, 1736, 1445],  # GDP in billions (fictitious or approximate)
})

# Merging on both country and year
df_merged = df_tidy.merge(gdp_data, on=["country", "year"], how="left")
display(df_merged)

	country	continent	year	population	gdp
0	USA	North America	1990	253	NaN
1	Canada	North America	1990	28	NaN
2	Brazil	South America	1990	149	NaN
3	USA	North America	2000	282	NaN
4	Canada	North America	2000	31	NaN
5	Brazil	South America	2000	170	NaN
6	USA	North America	2010	309	NaN
7	Canada	North America	2010	34	NaN
8	Brazil	South America	2010	192	NaN
9	USA	North America	2020	339	21439.0
10	Canada	North America	2020	38	1736.0
11	Brazil	South America	2020	209	1445.0

We used how="left" so that all rows from df_tidy are preserved, even if some may not match in gdp_data.

how="inner" would only keep matching rows.
how="outer" keeps all rows from both DataFrames.

Example: `sort_values()` and `query()`¶

Tidy data also makes it easy to sort and filter.

In [176]:

Copied!

# Sort by population descending
df_sorted = df_tidy.sort_values("population", ascending=False)
display(df_sorted)
# Sort by population descending
df_sorted = df_tidy.sort_values("population", ascending=False)
display(df_sorted)

	country	continent	year	population
9	USA	North America	2020	339
6	USA	North America	2010	309
3	USA	North America	2000	282
0	USA	North America	1990	253
11	Brazil	South America	2020	209
8	Brazil	South America	2010	192
5	Brazil	South America	2000	170
2	Brazil	South America	1990	149
10	Canada	North America	2020	38
7	Canada	North America	2010	34
4	Canada	North America	2000	31
1	Canada	North America	1990	28

`query()`¶

An alternative way to filter rows:

df.query("population > 200 and country == 'USA'")

is equivalent to

df[(df["population"] > 200) & (df["country"] == "USA")]

In [177]:

Copied!

df_filtered = df_tidy.query("population > 200 and country == 'USA'")
display(df_filtered)
df_filtered = df_tidy.query("population > 200 and country == 'USA'")
display(df_filtered)

	country	continent	year	population
0	USA	North America	1990	253
3	USA	North America	2000	282
6	USA	North America	2010	309
9	USA	North America	2020	339

Tidy and Process the Billboard Dataset¶

The Billboard dataset comes with 76 columns corresponding to the chart position of each song from x1st.week through x76th.week. This is a classic example of wide data that needs to be melted (unpivoted) into a long (tidy) format.

Goals¶

Load the Billboard dataset from CSV.
Tidy the data so each row represents one song in one week.
Calculate the actual date for each week using date.entered + week * 7 days.
Split the data into two tables:
- A songs table with static song information.
- A positions table with (song_id, week, rank, date).
Save the tidy data to Feather format in the same directory with _tidy suffix.

In [178]:

Copied!

import pandas as pd

# 1. Load the Billboard dataset
df_bill = pd.read_csv("../../Datasets/billboard.csv")

# Let's check a few columns to see the structure.
df_bill.head()
import pandas as pd

# 1. Load the Billboard dataset
df_bill = pd.read_csv("../../Datasets/billboard.csv")

# Let's check a few columns to see the structure.
df_bill.head()

Out[178]:

	year	artist.inverted	track	time	genre	date.entered	date.peaked	x1st.week	x2nd.week	x3rd.week	...	x67th.week	x68th.week	x69th.week	x70th.week	x71st.week	x72nd.week	x73rd.week	x74th.week	x75th.week	x76th.week
0	2000	Destiny's Child	Independent Women Part I	3:38	Rock	2000-09-23	2000-11-18	78	63.0	49.0	...	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN
1	2000	Santana	Maria, Maria	4:18	Rock	2000-02-12	2000-04-08	15	8.0	6.0	...	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN
2	2000	Savage Garden	I Knew I Loved You	4:07	Rock	1999-10-23	2000-01-29	71	48.0	43.0	...	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN
3	2000	Madonna	Music	3:45	Rock	2000-08-12	2000-09-16	41	23.0	18.0	...	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN
4	2000	Aguilera, Christina	Come On Over Baby (All I Want Is You)	3:38	Rock	2000-08-05	2000-10-14	57	47.0	45.0	...	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN

5 rows × 83 columns

The dataset has columns like:

year, artist.inverted, track, time, genre … (song info)
date.entered, date.peaked … (chart-related dates)
x1st.week through x76th.week … (chart positions over 76 weeks)

We want to melt these weekly columns into a single week and rank column.

In [ ]:

Notice how each row is now one song in one week. However, the week column currently contains strings like "x1st.week", "x2nd.week", etc. Let's clean those up and create a numeric week column.

In [ ]:

Now, week = 1, 2, 3, ... 76. Next, we want to calculate the exact date on the chart for each row by adding week * 7 days to date.entered.

In [ ]:

Split into Two Tables¶

Why split? We often separate the static song info (e.g., artist, track, time, genre) from the weekly chart performance (week, rank, date).

Songs Table: Contains unique identifiers for each song plus basic metadata.
Positions Table: Contains (song_id, week, rank, date), referencing the song_id from the songs table.

In [ ]:

Next, we merge this song_id back into our df_tidy so we can create the positions table.

In [ ]:

Create the Positions Table¶

We only keep the relevant columns for weekly positions: song_id, week, rank, and date.

In [ ]:

8.Playing with the data¶

Now that we have our data in a tidy format, let's do some analysis.

Only songs that reached top 10¶

We can use query() to filter the data for songs that reached the top 10 at least once. We will merge this back to the songs table to get the song details.

In [ ]:

Now we need to remove duplicates to get a list of unique songs that reached the top 10.

In [ ]:

How long did each song stay in the top 10?¶

In [ ]:

In which week did each song reach the top 10?¶

In [ ]:

9. Save Tidy Data to Feather¶

We want to save:

The tidy DataFrame (df_tidy) to a single file with the suffix _tidy.
(Optionally) Also save songs and positions as separate Feather files if needed.

In [ ]:

Lecture 9: Tidy Data¶

Wide Format to Tidy (Long) Format¶

pd.melt()¶

Converting Tidy (Long) Format Back to Wide Format¶

Summarizing Tidy Data with groupby()¶

groupby("year")["population"].mean()¶

Grouping by Multiple Columns¶

agg() for Multiple Summaries¶

Handling Missing Data¶

dropna()¶

fillna()¶

Combining Data with merge()¶

Example: sort_values() and query()¶

query()¶

Tidy and Process the Billboard Dataset¶

Goals¶

Split into Two Tables¶

Create the Positions Table¶

8.Playing with the data¶

Only songs that reached top 10¶

How long did each song stay in the top 10?¶

In which week did each song reach the top 10?¶

9. Save Tidy Data to Feather¶

`pd.melt()`¶

Summarizing Tidy Data with `groupby()`¶

`groupby("year")["population"].mean()`¶

`agg()` for Multiple Summaries¶

`dropna()`¶

`fillna()`¶

Combining Data with `merge()`¶

Example: `sort_values()` and `query()`¶

`query()`¶