Data Cleaning and Preparation with Billboard Christmas Dataset

Loading the Dataset and Data Type Assessment

Welcome! Today, we will refine our Billboard Christmas dataset, preparing it for data visualization. Start by loading the dataset into a Pandas DataFrame. This step will set a strong foundation for data cleaning by giving us a preview of the dataset's structure. First, let's double-check the structure of our dataset: Python import pandas as pd df = pd.read_csv('billboard_christmas.csv') print(df.head()) import pandas as pd df = pd.read_csv('billboard_christmas.csv') print(df.head()) The output of the above code will be: text url weekid ... month day 0 http://www.billboard.com/charts/hot-100/1958-1... 12/13/1958 ... 12 13 1 http://www.billboard.com/charts/hot-100/1958-1... 12/20/1958 ... 12 20 2 http://www.billboard.com/charts/hot-100/1958-1... 12/20/1958 ... 12 20 3 http://www.billboard.com/charts/hot-100/1958-1... 12/20/1958 ... 12 20 4 http://www.billboard.com/charts/hot-100/1958-1... 12/27/1958 ... 12 27 [5 rows x 13 columns] url weekid ... month day 0 http://www.billboard.com/charts/hot-100/1958-1... 12/13/1958 ... 12 13 1 http://www.billboard.com/charts/hot-100/1958-1... 12/20/1958 ... 12 20 2 http://www.billboard.com/charts/hot-100/1958-1... 12/20/1958 ... 12 20 3 http://www.billboard.com/charts/hot-100/1958-1... 12/20/1958 ... 12 20 4 http://www.billboard.com/charts/hot-100/1958-1... 12/27/1958 ... 12 27 [5 rows x 13 columns] Take special note of the weekid column. We'll be converting this into a datetime format to leverage datetime features in the next steps. Understanding data types will help us decode and work with data correctly.

Date Conversion and Feature Creation

Having a look at weekid, let's convert it to a datetime format, which enables us to easily extract month and week details. Extracting these details will enhance your dataset with temporal features that can aid in identifying trends. The following code snippet carries out these conversions: Python # Convert 'weekid' to datetime df['weekid'] = pd.to_datetime(df['weekid']) # Extract month and week of year from the date df['month'] = df['weekid'].dt.month df['week_of_year'] = df['weekid'].dt.isocalendar().week # Create a boolean feature for December df['is_december'] = df['month'] == 12 # Print the head of the dataframe to see new columns print(df[['weekid', 'month', 'week_of_year', 'is_december']].head()) # Convert 'weekid' to datetime df['weekid'] = pd.to_datetime(df['weekid']) # Extract month and week of year from the date df['month'] = df['weekid'].dt.month df['week_of_year'] = df['weekid'].dt.isocalendar().week # Create a boolean feature for December df['is_december'] = df['month'] == 12 # Print the head of the dataframe to see new columns print(df[['weekid', 'month', 'week_of_year', 'is_december']].head()) The output of the above code will be: text [5 rows x 13 columns] weekid month week_of_year is_december 0 1958-12-13 12 50 True 1 1958-12-20 12 51 True 2 1958-12-20 12 51 True 3 1958-12-20 12 51 True 4 1958-12-27 12 52 True [5 rows x 13 columns] weekid month week_of_year is_december 0 1958-12-13 12 50 True 1 1958-12-20 12 51 True 2 1958-12-20 12 51 True 3 1958-12-20 12 51 True 4 1958-12-27 12 52 True By converting weekid and using .dt.month and .dt.isocalendar().week, we enrich the dataset with new dimensions for identifying seasonal patterns. The is_december feature efficiently flags entries that occur in December, pivotal for holiday-focused analysis.

Join the 1M+ learners on CodeSignal

Be a part of our community of 1M+ users who develop and demonstrate their skills on CodeSignal

Python

import pandas as pd

df = pd.read_csv('billboard_christmas.csv')
print(df.head())

text

                                                 url      weekid  ...  month day
0  http://www.billboard.com/charts/hot-100/1958-1...  12/13/1958  ...     12  13
1  http://www.billboard.com/charts/hot-100/1958-1...  12/20/1958  ...     12  20
2  http://www.billboard.com/charts/hot-100/1958-1...  12/20/1958  ...     12  20
3  http://www.billboard.com/charts/hot-100/1958-1...  12/20/1958  ...     12  20
4  http://www.billboard.com/charts/hot-100/1958-1...  12/27/1958  ...     12  27

[5 rows x 13 columns]

Python

# Convert 'weekid' to datetime
df['weekid'] = pd.to_datetime(df['weekid'])

# Extract month and week of year from the date
df['month'] = df['weekid'].dt.month
df['week_of_year'] = df['weekid'].dt.isocalendar().week

# Create a boolean feature for December
df['is_december'] = df['month'] == 12

# Print the head of the dataframe to see new columns
print(df[['weekid', 'month', 'week_of_year', 'is_december']].head())

text

[5 rows x 13 columns]
      weekid  month  week_of_year  is_december
0 1958-12-13     12            50         True
1 1958-12-20     12            51         True
2 1958-12-20     12            51         True
3 1958-12-20     12            51         True
4 1958-12-27     12            52         True

Python

# Check for missing values
print("Missing values by column:")
print(df.isnull().sum())

# Check for duplicate rows
print("\nDuplicate rows:", df.duplicated().sum())

# Check for missing values
print("Missing values by column:")
print(df.isnull().sum())

# Check for duplicate rows
print("\nDuplicate rows:", df.duplicated().sum())

text

Missing values by column:
url                         0
weekid                      0
week_position               0
song                        0
performer                   0
songid                      0
instance                    0
previous_week_position    108
peak_position               0
weeks_on_chart              0
year                        0
month                       0
day                         0
week_of_year                0
is_december                 0
dtype: int64

Duplicate rows: 0

Missing values by column:
url                         0
weekid                      0
week_position               0
song                        0
performer                   0
songid                      0
instance                    0
previous_week_position    108
peak_position               0
weeks_on_chart              0
year                        0
month                       0
day                         0
week_of_year                0
is_december                 0
dtype: int64

Duplicate rows: 0

Python

# Strip whitespace and capitalize the first letter of each word in 'song' and 'performer'
df['song'] = df['song'].str.strip().str.title()
df['performer'] = df['performer'].str.strip().str.title()

# Print the first few rows to see changes
print(df[['song', 'performer']].head())

text

               song     performer
0   Run Rudolph Run   Chuck Berry
1  Jingle Bell Rock   Bobby Helms
2   Run Rudolph Run   Chuck Berry
3   White Christmas   Bing Crosby
4   Green Chri$Tma$  Stan Freberg

Python

# Create a clean copy and save to a new CSV file
df_clean = df.copy()
df_clean.to_csv('billboard_christmas_clean.csv', index=False)

print("Data saved to 'billboard_christmas_clean.csv'")

# Create a clean copy and save to a new CSV file
df_clean = df.copy()
df_clean.to_csv('billboard_christmas_clean.csv', index=False)

print("Data saved to 'billboard_christmas_clean.csv'")

Python

Data saved to 'billboard_christmas_clean.csv'

Data saved to 'billboard_christmas_clean.csv'

Python

# Check for missing values
print("Missing values by column:")
print(df.isnull().sum())

# Check for duplicate rows
print("\nDuplicate rows:", df.duplicated().sum())

text

Missing values by column:
url                         0
weekid                      0
week_position               0
song                        0
performer                   0
songid                      0
instance                    0
previous_week_position    108
peak_position               0
weeks_on_chart              0
year                        0
month                       0
day                         0
week_of_year                0
is_december                 0
dtype: int64

Duplicate rows: 0

Python

# Strip whitespace and capitalize the first letter of each word in 'song' and 'performer'
df['song'] = df['song'].str.strip().str.title()
df['performer'] = df['performer'].str.strip().str.title()

# Print the first few rows to see changes
print(df[['song', 'performer']].head())

text

               song     performer
0   Run Rudolph Run   Chuck Berry
1  Jingle Bell Rock   Bobby Helms
2   Run Rudolph Run   Chuck Berry
3   White Christmas   Bing Crosby
4   Green Chri$Tma$  Stan Freberg

Python

# Create a clean copy and save to a new CSV file
df_clean = df.copy()
df_clean.to_csv('billboard_christmas_clean.csv', index=False)

print("Data saved to 'billboard_christmas_clean.csv'")

Python

Data saved to 'billboard_christmas_clean.csv'

Loading the Dataset and Data Type Assessment

Date Conversion and Feature Creation

Join the 1M+ learners on CodeSignal

Data Quality Checks

Standardizing Text Data

Saving the Cleaned Dataset

Lesson Summary