<jupyter_start><jupyter_text>Tidy datasets are easy to manipulate, model and visualise, and have a specific structure: each variable is a column, each observation is a row, and each type of observational unit is a table (3rd level normalization of relational database). Tidy Data, <NAME> (2014)<jupyter_code># This statement widens the notebook page to the window size.
from IPython.core.display import display, HTML
display(HTML("<style>.container { width:100% !important; }</style>"))
import numpy as np
import pandas as pd
# error message in reading as pandas cannot decode the encoding
billboard = pd.read_csv('billboard.csv')
# Latin_1 encoding accept any possible byte as input and converts it to the unicode character of same code.
billboard = pd.read_csv('billboard.csv', encoding='latin_1')
billboard<jupyter_output><empty_output><jupyter_text>This table is messy in two ways:1. Table combines two observational unit: the song and its rank in each week. 2. Variable of week is repreated in over 76 columns!<jupyter_code># rename columns to be more readable
new_cols = [
    'wk1', 'wk2', 'wk3', 'wk4', 'wk5', 'wk6', 'wk7', 'wk8', 'wk9', 'wk10',
    'wk11', 'wk12', 'wk13', 'wk14', 'wk15', 'wk16', 'wk17', 'wk18', 'wk19',
    'wk20', 'wk21', 'wk22', 'wk23', 'wk24', 'wk25', 'wk26', 'wk27', 'wk28',
    'wk29', 'wk30', 'wk31', 'wk32', 'wk33', 'wk34', 'wk35', 'wk36', 'wk37',
    'wk38', 'wk39', 'wk40', 'wk41', 'wk42', 'wk43', 'wk44', 'wk45', 'wk46',
    'wk47', 'wk48', 'wk49', 'wk50', 'wk51', 'wk52', 'wk53', 'wk54', 'wk55',
    'wk56', 'wk57', 'wk58', 'wk59', 'wk60', 'wk61', 'wk62', 'wk63', 'wk64',
    'wk65', 'wk66', 'wk67', 'wk68', 'wk69', 'wk70', 'wk71', 'wk72', 'wk73',
    'wk74', 'wk75', 'wk76'
]
billboard.rename(columns=dict(zip(billboard.columns[7:], new_cols)),
                 inplace=True)

# If there is no need to make the changes inplace in the original read datafile (for example for a later computation),
# following method is more efficient as just the indices are read not the whole DataFrame.

# billboard.columns.values[range(7,83)] = new_cols

billboard.rename(columns={'artist.inverted': 'artist'}, inplace=True)

billboard.head()
# `id_vars` or identifier variables are columns not to be changed while `value_vars` or measured varaible are "unpivoted" to the row axis leaving just two non-identifier columns, 'variable' and 'value'.
# Note how readable and coincise the table becomes compared to the original table.
# Note that 'year' and 'date.peaked' extra columns are eliminated as they can be computed them from the 'date.entered' and 'week' columns.

billboardmelt = billboard.melt(
    id_vars=['artist', 'track', 'time', 'genre', 'date.entered'],
    value_vars=new_cols,
    var_name='week',
    value_name='rank')
billboardmelt
# Covnvert the string-integer format of week column values to integers to be more readable and also be computable.
billboardmelt['week'] = billboardmelt['week'].apply(lambda s: int(s[2:]))
# Sort the table to note the redundancy; multiple columns of artist, track, time, genre have repretitive records.
billboardmelt.sort_values(['artist', 'track'], inplace=True)
billboardmelt<jupyter_output><empty_output><jupyter_text>First let's remove date redundancy "date.entered" column was usable for original dataset with one date row for all the multiple weeks columns. However, now that mutiple columns are rows each consequtive week needs its separate corresponding date otherwise for the rows of all weeks from 1 to 76, the "date.entered" of the first week would be repeated (date redundancy). To remove this redundancy, date of each subsequent week is computed by adding multiples of 7 to the original "date.entered": for example; date for week 3 is "date.entered" + 14 while date of week 76 is date.entered + 75*7.<jupyter_code># converts date.entered strings to proper panda date format to make is computable
billboardmelt['date.entered'] = pd.to_datetime(billboardmelt['date.entered'])

# Compute date column from date.entered (the date of the first week that a song enters the market) and week columns.
billboardmelt['date'] = billboardmelt['date.entered'] + pd.Timedelta(
    '7 days') * (billboardmelt['week'] - 1)

# Drop the original date.entered column.
billboardmelt.drop(['date.entered'], axis=1, inplace=True)
billboardmelt<jupyter_output><empty_output><jupyter_text>To remove artist, track, time and genre redundancies we move them into a SEPARATE table as distinct observational unit of song details and move week, rank and date column to another SEPARATE table as distinct observational unit of ranking and then link them together through unique indices.<jupyter_code># Create a separate table with track data and unique index (like primary key in database) using drop_duplictaes() method
tracks = billboardmelt[['artist', 'track', 'time', 'genre']].drop_duplicates()

# Basically, unique indices of tracks dataframe is inserted into the tracksid datafarme as a separate column. To carry the unique index "id" explicitly as a separate column, a new index is made by
# reseting the index to default. tracks dataframe is assigned to tracksid dataframe with a default index and a column of unique indices.
tracks.index.name = 'id'
tracksid = tracks.reset_index()
tracksid
# Link tracksid table to the ranking using unique indices by joining tracksid dataframe with billboardmelt and dropping common columns.
# This makes the second table with unique observational unit of ranking with the relevant date and week columns.
# Note that unique ids are repeated accoring to week number but date, week and rank are unique.

ranking = pd.merge(tracksid,
                   billboardmelt,
                   on=['artist', 'track', 'time',
                       'genre']).drop(['artist', 'track', 'time', 'genre'],
                                      axis=1)
ranking<jupyter_output><empty_output><jupyter_text>In summary, messy billboard dataframe is split into two tidy tables of tracksid and ranking.    -Should we need rank of a song, we query on the distinct observational ranking table.    -Should we need details of song, we query on the distinct observational song table usinf the song unique index.  Example: Find the details for the song with maximum ranking in the first week<jupyter_code># First find the unique song index
ranking.loc[ranking[ranking.week == 1]['rank'].idxmax()]
# Then find the track details
tracksid.query('id ==248')
%load_ext version_information
%version_information numpy, pandas, matplotlib, version_information<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.10651609949641386, 'fraction_numerical': 0.027773538837173813, 'mean_word_length': 4.920505871725384, 'pattern_counts': {'":': 1, '<': 24, '<?xml version=': 0, '>': 24, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 1, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '5', 'dir': 'jupyter-structured-clean-dedup', 'id': '6208', 'license': 'MIT', 'n_tokens_mistral': 2147, 'n_tokens_neox': 1891, 'n_words': 803, 'path': 'tidyingMessySongDataframe.ipynb', 'repo_name': 'mghanava/dataWrangling', 'size': '6561'}