Run in Google Colab

View on GitHub

Homework 5 - Tidy and Process the Billboard Dataset¶

The goal of this homework is to tidy the Billboard dataset and perform some basic analysis and visualization.

You will demonstrate your understanding of data tidying principles and your ability to manipulate and visualize data using Python.

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.

Instructions¶

1. Project Setup: Follow the instructions on how to set up your Python and Jupyter (or VSCode) environment, as well as how to clone or download our repository. These can be found in the class notes.

2. Dataset Path: Verify the path to the CSV file (e.g. "../../Datasets/billboard.csv"). Adjust if necessary.

3. Load the Billboard CSV into a DataFrame with pd.read_csv().

4. Melt/Unpivot the 76 weekly columns (x1st.week through x76th.week) into two new columns:

   • week (indicating which week of the chart it is, e.g., 1, 2, 3...)

   • rank (the song's chart position for that week).

   • This is typically done using pd.melt() or pd.wide_to_long().

5. Clean the week values so that they are numeric (1 to 76) instead of strings like "x1st.week".

6. Create a date column that indicates the exact date on the chart for each row, using:

   date = date.entered + (week - 1) * 7 days
   

   • You may want to transform date.entered in a datetime format (you may need to convert it with pd.to_datetime() check documentation).

7. Split the data into two tables:

   1. songs table, which should contain unique song information (e.g., song_id, artist, track, time, etc.).

      • Remember to drop duplicates so you only have one row per song.

      • Generate a new song_id column that can be used to join back to the weekly info.

   2. positions table, which should contain weekly data: (song_id, week, rank, date).

8. Save your tidy DataFrames to Feather format:

   • Save the full “tidy” DataFrame (one row per song-week) with a filename suffix _tidy.

   • Also save the separate songs and positions tables as separate Feather files (e.g., songs.feather, positions.feather).

9. Submission:

   • Export your completed notebook to HTML or PDF.

   • If on Jupyter, select File > Export Notebook As > HTML.

   • If on VSCode, use Jupyter: Export to HTML from the command palette.

   • Submit your exported file on Canvas.

Dataset Overview¶

The dataset consists of songs and their weekly chart positions on the Billboard Hot 100. It has the following main columns:

• year: The year the song entered the chart.
• artist: The artist of the song.
• track: The title of the song.
• time: The duration of the song.
• date.entered: The date the song first entered the chart.
• x1st.week to x76th.week: The chart position of the song for each of the 76 weeks (1 = highest rank, 100 = lower rank).

Goals (Detailed)¶

1. Load the Billboard dataset from CSV into a pandas DataFrame.

2. Tidy the data by converting the weekly columns into a single week column (numeric) and a rank column.

3. Compute an exact date for each row by adding (week - 1) * 7 days to the date.entered.

4. Split the data into:

   • songs: (One row per song) containing static info and a new song_id.

   • positions: (One row per song-week) containing columns: [song_id, week, rank, date].

5. Save everything to Feather files (or CSV if you prefer, but Feather is recommended).

---

In [ ]:

import pandas as pd

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

# Let's quickly check the first few rows to confirm the structure.
df_bill.head()

import pandas as pd # 1. Load the Billboard dataset df_bill = pd.read_csv("../../Datasets/billboard.csv") # Adjust path if needed # Let's quickly check the first few rows to confirm the structure. df_bill.head()

The dataset has columns like:

• year, artist.inverted, track, time, genre, etc. (Song info)

• date.entered, date.peaked (Chart-related dates)

• x1st.week through x76th.week (Chart positions over 76 weeks)

Our objective is to melt these weekly columns into a single week and rank column.

In [ ]:

# 2. Melt/unpivot the data into a "tidy" format.
# HINT: you can use pd.melt(). 
# We'll create a new DataFrame, for example df_tidy.

# Your code here

# 2. Melt/unpivot the data into a "tidy" format. # HINT: you can use pd.melt(). # We'll create a new DataFrame, for example df_tidy. # Your code here

After melting, each row should represent one song in one week. However, the week column will have strings like "x1st.week", "x2nd.week", etc. Let's clean those up and create a numeric week column.

In [ ]:

# 3. Convert the week column to numeric (strip the "x", remove ".week", etc.)

# Your code here

# 3. Convert the week column to numeric (strip the "x", remove ".week", etc.) # Your code here

Now, week should be 1, 2, 3, ... 76. Next, we want to calculate the exact date on the chart for each row by adding (week - 1) * 7 days to date.entered. Create a column named "date" to hold the result. Make sure date.entered is converted to datetime first, if it isn't already.

In [ ]:

# 4. Create the 'date' column: date = date.entered + (week - 1)*7 days

# Your code here

# 4. Create the 'date' column: date = date.entered + (week - 1)*7 days # Your code here

5. 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.

Generate a new song_id for each unique song, then merge/join this song_id back into the melted DataFrame.

In [ ]:

# 5.1 Create a songs table (unique rows). Assign a song_id. 
# Then merge that ID back into the main tidy DataFrame.

# 5.1 Create a songs table (unique rows). Assign a song_id. # Then merge that ID back into the main tidy DataFrame.

In [ ]:

# Your code here

# Your code here

6. Create the Positions Table¶

Once the song_id is part of the melted DataFrame, we can select the columns (song_id, week, rank, date) into a separate DataFrame called positions.

In [ ]:

# Your code here

# Your code here

By the end of this process you should have:

• A songs table with unique song info and a song_id for each song (keep song names, artists, etc.)

• A positions table with (song_id, week, rank, date) for each song-week.

For example:

songs table:

song_id	artist	track	time	year
1	The Beatles	Hey Jude	3:35	1968
2	Elton John	Rocket	3:10	1972
3	Whitney Houston	I Will Always	4:30	1992
...	...	...	...	...

positions table:

song_id	week	rank	date
1	1	1	1968-09-14
1	2	1	1968-09-21
1	3	1	1968-09-28
...	...	...	...

7. Save Tidy Data to Feather¶

We want to save:

• The complete tidy DataFrame (one row per song-week) with a filename suffix _tidy.

• The songs and positions tables as separate Feather files.

In [ ]:

# Your code here

# Your code here

Part 1 - Basic Analysis¶

Below are some guided analyses to help you get started. Feel free to explore more on your own!

A. Only songs that reached top 10¶

1. Use query() to filter rows in positions for rank <= 10.

2. Merge or join with the songs table to get the song details.

Hint: Remember you can drop duplicates if you only want the list of unique songs that reached top 10.

In [ ]:

# Your code here

# Your code here

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

1. Filter positions for rank <= 10.

2. Group by song_id and count the number of rows. This gives weeks_in_top_10.

3. Merge back with the songs table to display the track/artist.

In [ ]:

# Your code here

# Your code here

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

1. Again filter for rank <= 10.

2. Group by song_id and find the minimum week value. Call this week_reached_top_10.

In [ ]:

# Your code here

# Your code here

Part 2 - Visualization¶

For this part, you may use Altair (Preferable!), Matplotlib, Seaborn, or any other Python visualization library.

Below are some suggested questions and plots:

1. Histogram of Weeks in the Top 10

   • How many songs stayed for 1 week vs. 2 weeks vs. 10 weeks in the top 10?

   • Plot this distribution.

In [ ]:

# 1. Example: Histogram of the number of weeks each song stayed in the top 10.
# (Assuming you've created a 'weeks_in_top_10' table or series.)

# Your code here

# 1. Example: Histogram of the number of weeks each song stayed in the top 10. # (Assuming you've created a 'weeks_in_top_10' table or series.) # Your code here

2. Line Chart of a Song's Rank Over Time

   • Pick a well-known song from the dataset (e.g., one of the most weeks at #1).

   • Plot its rank vs. date (or week).

   • The line should show how the rank changes over consecutive weeks.

In [ ]:

# Use the original `positions` DataFrame to plot a song's rank over time.
# Your code here

# Use the original `positions` DataFrame to plot a song's rank over time. # Your code here

3. Distribution of Peak Ranks

   • For each song, find its best (lowest number) rank.

   • Plot a histogram of these peak positions. Where do most songs peak? (#1, #5, #20, etc.)

In [ ]:

# Your code here

# Your code here

Bonus: Additional Analysis (Optional, but will give you extra credit!)¶

(Add more charts and visualizations as you see fit!)

Be creative and explore the dataset! showcase at least a new plot or analysis.

In [ ]:

# Your extra analyses here

# Your extra analyses here

Final Instructions¶

1. Make sure your notebook runs from top to bottom without errors.

2. Export it to HTML or PDF.

3. Submit via Canvas.

Good luck and have fun exploring the Billboard dataset!