<jupyter_start><jupyter_text>Mucis chart.  General setup.___<jupyter_code># Import libraries
import numpy as np
import pandas as pd
import matplotlib.pyplot as plt

%matplotlib inline
plt.style.use('ggplot')
plt.rcParams['figure.figsize'] = [12, 8]<jupyter_output><empty_output><jupyter_text>Load the data set.___<jupyter_code># Load the data set with specified encoding since it's not in utf-8
billboard = pd.read_csv('../Data/billboard.csv', encoding='latin-1')
billboard.head()
billboard.tail()
billboard.columns
# Chart evolution for the first song
plt.plot(billboard.loc[0, 'x1st.week':'x76th.week'])
plt.show()
# Adjust the x axis
plt.plot(range(1,77), billboard.loc[0, 'x1st.week':'x76th.week'])
plt.show()
# Check the same as above for all rows
for _, row in billboard.iterrows():
    plt.plot(range(1,77), row['x1st.week':'x76th.week'], color='C0', alpha=0.1)<jupyter_output><empty_output><jupyter_text>It is clear from the above that the songs usually don't last very long. Also, it appears that the rankings are not very useful for the analysis since they are divided between multiple columns. So we need to rearrange the data set.  Cleaning the data set.___<jupyter_code># Select only some columns from the original data set
bshort = billboard[['artist.inverted', 'track', 'time', 'genre', 'date.entered',
       'x1st.week', 'x2nd.week', 'x3rd.week']]
# Rename the columns
bshort.columns = ['artist', 'track', 'time', 'genre', 'date.entered', 'wk1', 'wk2', 'wk3']
bshort.head()
# Melting each row into multiple ones to represent the simple ranking
bmelt = bshort.melt(['artist', 'track', 'time', 'genre', 'date.entered'], 
                    ['wk1', 'wk2', 'wk3'], 'week', 'rank')
bmelt.head()
bmelt.query('track == "Music"')
# Convert week column to a number by taking the third char in each string
bmelt['week'] = bmelt['week'].apply(lambda s: int(s[2]))
bmelt.query('track == "Music"')
# Convert date column to datetime object
bmelt['date.entered'] = pd.to_datetime(bmelt['date.entered'])
bmelt['date.entered'][0]
# For example the second week 
bmelt['date.entered'][0] + pd.Timedelta('7 days')
# Get the correct date for each week
bmelt['date'] = bmelt['date.entered'] + pd.Timedelta('7 days') * (bmelt['week'] - 1)
bmelt['date']
# Delete the column date entered
bmelt.drop(['date.entered'], axis=1, inplace = True)
bmelt.query('track == "Music"')
# Final data frame with values sorted by artist and track name
bfinal = bmelt[['artist', 'track', 'time', 'genre', 'date', 'week', 'rank']]
bfinal = bfinal.sort_values(['artist', 'track'])
bfinal.head()<jupyter_output><empty_output><jupyter_text>Data Normalization.___After tidying the data set we created some redundancy where artist names, track names and time appearing multiple times. So now we can create a separate table with track data and link it to the table above using an index.<jupyter_code># Create a table with track data
tracks = bfinal[['artist', 'track', 'time', 'genre']].drop_duplicates()
tracks.head()
# Reset the index and create a new column with ids
tracks.index.name = 'id'
tracks_id = tracks.reset_index()
tracks_id.head()
#Merging the tables
pd.merge(tracks_id, bfinal, on=['track', 'artist', 'time', 'genre']).head()
# Removing the data we don't need
tidy_set = pd.merge(tracks_id, bfinal, on=['track', 'artist', 'time', 'genre']
                   ).drop(['track', 'artist', 'time', 'genre'], axis=1)
tidy_set.head()
# Find the index of the artist with the lowest rank in week 1
lowest_rating = tidy_set[tidy_set.week == 1]['rank'].idxmin()
lowest_rating
# Find the song
tidy_set.loc[lowest_rating]
# The information in track data
tracks_id.query('id == 1')
# Find the song
tidy_set.loc[tidy_set[tidy_set.week == 1]['rank'].idxmax()]
tracks_id.query('id == 248')<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.15398837823560485, 'fraction_numerical': 0.012942419440042261, 'mean_word_length': 5.506872852233677, 'pattern_counts': {'":': 0, '<': 17, '<?xml version=': 0, '>': 17, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '4', 'dir': 'jupyter-structured-clean-dedup', 'id': '261523', 'license': 'MIT', 'n_tokens_mistral': 1336, 'n_tokens_neox': 1272, 'n_words': 394, 'path': 'Data cleaning/Billboard.ipynb', 'repo_name': 'olgarozhdestvina/Data-Science-and-Machine-Learning', 'size': '3786'}