<jupyter_start><jupyter_text>Top Charts Exploratory Data Analysis  Loading Dependencies<jupyter_code>import pandas as pd
from collections import Counter
import altair as alt
import nltk
import regex as re<jupyter_output><empty_output><jupyter_text>Loading in Data<jupyter_code>df = pd.read_csv('cleaned_data/all_top_songs_with_genres_nolist.csv')
# preview of dataframe
df.head()<jupyter_output><empty_output><jupyter_text>Cleaning Up List of Genres<jupyter_code># cleaning up the genres column on copy of dataframe
df_ = df.copy()
df_['genre'] = df_['genre'].str.split(", ")

# add all values to a list to generate a unique list of values
genres_list = []
for idx, value in enumerate(df_['genre']):
    genres_list.extend(value)<jupyter_output><empty_output><jupyter_text>Adding in Columns for genres<jupyter_code>df_['pop'] = df.genre.str.contains('pop')==True
df_['rb'] = df.genre.str.contains('r-b')==True
df_['rap'] = df.genre.str.contains('rap')==True
df_['rock'] = df.genre.str.contains('rock')==True
df_['non-music'] = df.genre.str.contains('non-music')==True
df_['country'] = df.genre.str.contains('country')==True
df_['no_genre'] = df.genre.str.contains('m')==True
df_['pop'] = df_['pop'].astype(int)
df_['rb'] = df_['rb'].astype(int)
df_['rap'] = df_['rap'].astype(int)
df_['rock'] = df_['rock'].astype(int)
df_['non-music'] = df_['non-music'].astype(int)
df_['country'] = df_['country'].astype(int)
df_['no_genre'] = df_['no_genre'].astype(int)
df_.head()
### Saving to CSV
df_.to_csv('cleaned_data/OHE_all_top_songs.csv', index=False)
df_[df_['non-music'] == 1]['artist']
# drop non-music and bc they are all either having another genre or missing a genre
df_ = df_.drop(columns=['non-music'])
missing_genres = []
for i in range(len(df_.artist)):
    if sum(df_.iloc[i,6:11]) > 0:
        item = 0
        missing_genres.append(item)
    else:
        item = 1
        missing_genres.append(item)
df_['no_genre'] = missing_genres<jupyter_output><empty_output><jupyter_text>Visualizations<jupyter_code>genre_frequencies = dict(Counter(genres_list))
genre_frequencies
genre_frequencies_df = pd.DataFrame.from_records([genre_frequencies])
genre_frequencies_df = genre_frequencies_df.rename(index={0:'counts'}).T.reset_index().rename(columns={'index':'genres'})
genre_frequencies_df = genre_frequencies_df[genre_frequencies_df['genres'].isin(['r-b', 'pop', 'rap', 'rock', 'country'])]
genre_frequencies_df.to_csv('cleaned_data/genre_song_counts.csv', index = False)
bars = alt.Chart(data=genre_frequencies_df).mark_bar().encode(
x= 'genres',
y = 'counts',
color = 'genres'
)
text = bars.mark_text(
    align='center',
    # baseline='top',
    dy=-10 
).encode(
    text='counts:Q',
)

(bars + text).properties(height=500, width = 400,title = "Frequency of Genres on Top 200 Charts").configure_range(
    category={'scheme': 'tableau10'}
)<jupyter_output><empty_output><jupyter_text>There seem to be data that is labeled as non-music which is strange because there shouldn't be any labeled non-music. If there is another genre listed, remove non-music  Keyword Extraction of all Genres<jupyter_code>### Importing More Dependencies
from resources.word_extraction.text_cleaning import lem_stem_text
from resources.word_extraction.stopwords import remove_stopw, get_stopwords
from resources.analyze import find_keywords, find_instances
df_['cleaned_lyrics'] = df_['lyrics'].str.replace('[^\w\s]','')
df_['cleaned_lyrics'] = df_['cleaned_lyrics'].str.replace('missing lyrics','')
df_['cleaned_lyrics'] = df_['cleaned_lyrics'].apply(remove_stopw)
df_['cleaned_lyrics'] = df_['cleaned_lyrics'].apply(lem_stem_text)
df_['cleaned_lyrics'] = df_.cleaned_lyrics.str.strip().str.split(' ')
df_
## getting a list of all lemmed and stemmed keywords without stopwords
lyrics_wordlist = df_['cleaned_lyrics'].tolist()
words_list = []
for i in lyrics_wordlist:
    words_list.extend(i)
len(words_list)
# Creating a DataFrame of the Word Counts
lyric_word_frequencies = pd.DataFrame.from_dict(Counter(words_list), orient = 'index').reset_index()
lyric_word_frequencies = lyric_word_frequencies.rename(columns={'index':'word', 0:'count'})
lyric_word_frequencies = lyric_word_frequencies.sort_values(by = "count", ascending = False)
lyric_word_frequencies
lyric_word_frequencies.head(20)
lyric_word_frequencies.to_csv('cleaned_data/lyric_word_frequencies.csv', index = False)
top_100 = lyric_word_frequencies[:100]
top_100<jupyter_output><empty_output><jupyter_text>Top Words by Genre<jupyter_code>pd.Series(genres_list).unique()
pop = df_[df_['pop'] == 1]
rb = df_[df_['rb'] == 1]
rap = df_[df_['rap'] == 1]
rock = df_[df_['rock'] == 1]
country = df_[df_['country'] == 1]
m = df_[df_['no_genre'] == 1]
def top_lyrics(df, dfname):
    '''Function to find the top lyric unigrams based on a df containing lyrics'''
    ## getting a list of all lemmed and stemmed keywords without stopwords
    lyrics_wordlist = df['cleaned_lyrics'].tolist()
    words_list = []
    for i in lyrics_wordlist:
        words_list.extend(i)
    len(words_list)
    # Creating a DataFrame of the Word Counts
    lyric_word_frequencies = pd.DataFrame.from_dict(Counter(words_list), orient = 'index').reset_index()
    lyric_word_frequencies = lyric_word_frequencies.rename(columns={'index':'word', 0:'count'})
    lyric_word_frequencies = lyric_word_frequencies.sort_values(by = "count", ascending = False)
    lyric_word_frequencies['genre'] = dfname
    return lyric_word_frequencies
rb_lyrics = top_lyrics(rb, 'r-b')[:15]
rb_lyrics
pop_lyrics = top_lyrics(pop, 'pop')[:15]
country_lyrics = top_lyrics(country, 'country')[:15]
rock_lyrics = top_lyrics(rock, 'rock')[:15]
rap_lyrics = top_lyrics(rap, 'rap')[:15]
full_lyrics = pd.concat([pop_lyrics,country_lyrics,rock_lyrics,rap_lyrics,rb_lyrics])
full_lyrics
full_lyrics.to_csv('cleaned_data/lyric_frequencies/top15_all_genres_lyric_frequencies.csv', index = False)<jupyter_output><empty_output><jupyter_text>Top Songs By Genre  I forgot to get the top songs by genre streams so I am re importing the top 200 files and the previously created OHE (one-hot-encoded) df to create a new df with the streams<jupyter_code>import pandas as pd
## OTHER MISC DATA CLEANING 
df1 = pd.read_csv('/Users/daphneyang/Desktop/5YMIDS_SP21/w209/spotify-visualizations/cleaned_data/2017_weekly_all_locations_top200.csv')
df2 = pd.read_csv('/Users/daphneyang/Desktop/5YMIDS_SP21/w209/spotify-visualizations/cleaned_data/2018_weekly_all_locations_top200.csv')
df3 = pd.read_csv('/Users/daphneyang/Desktop/5YMIDS_SP21/w209/spotify-visualizations/cleaned_data/2019_weekly_all_locations_top200.csv')
df4 = pd.read_csv('/Users/daphneyang/Desktop/5YMIDS_SP21/w209/spotify-visualizations/cleaned_data/2020_weekly_all_locations_top200.csv')
df = pd.concat([df1, df2, df3, df4])
df['streams'] = df['streams'].str.replace(",", '').astype(int)
global_df = df[df['country_chart'].str.contains("Global")]
global_df_total = global_df.groupby(["track", 'spotify_link']).sum().reset_index()
lyrics_df = pd.read_csv('/Users/daphneyang/Desktop/5YMIDS_SP21/w209/spotify-visualizations/cleaned_data/OHE_all_top_songs.csv')
merged_df = pd.merge(lyrics_df, global_df_total, "inner", on = "track")
merged_df = merged_df.rename(columns={'streams': "total_streams"})
merged_df
pop = merged_df[merged_df['pop'] == 1][['track', 'artist', 'total_streams', 'spotify_link']].reset_index(drop=True).sort_values(by=['total_streams'], ascending = False)[:11]
pop['genre'] = 'pop'
rb = merged_df[merged_df['rb'] == 1][['track', 'artist', 'total_streams', 'spotify_link']].reset_index(drop=True).sort_values(by=['total_streams'], ascending = False)[:11]
rb['genre'] = 'r-b'
rap = merged_df[merged_df['rap'] == 1][['track', 'artist', 'total_streams', 'spotify_link']].reset_index(drop=True).sort_values(by=['total_streams'], ascending = False)[:13]
rap['genre'] = 'rap'
rock = merged_df[merged_df['rock'] == 1][['track', 'artist', 'total_streams', 'spotify_link']].reset_index(drop=True).sort_values(by=['total_streams'], ascending = False)[:13]
rock['genre'] = 'rock'
country = merged_df[merged_df['country'] == 1][['track', 'artist', 'total_streams', 'spotify_link']].reset_index(drop=True).sort_values(by=['total_streams'], ascending = False)[:12]
country['genre'] = 'country'
df_output = pd.concat([pop, rb, rap, rock, country])
df_output
df_output.iloc[59][3]
# Change all links to embed links
df_output.to_csv('../cleaned_data/top10_by_genre_all_time.csv', index = False)<jupyter_output><empty_output><jupyter_text>Creating All Topic Songs With Years<jupyter_code>import pandas as pd 

df1 = pd.read_csv('/Users/daphneyang/Desktop/5YMIDS_SP21/w209/spotify-visualizations/cleaned_data/2017_weekly_all_locations_top200.csv')
df1['year'] = '2017'
df2 = pd.read_csv('/Users/daphneyang/Desktop/5YMIDS_SP21/w209/spotify-visualizations/cleaned_data/2018_weekly_all_locations_top200.csv')
df2['year'] = '2018'
df3 = pd.read_csv('/Users/daphneyang/Desktop/5YMIDS_SP21/w209/spotify-visualizations/cleaned_data/2019_weekly_all_locations_top200.csv')
df3['year'] = '2019'
df4 = pd.read_csv('/Users/daphneyang/Desktop/5YMIDS_SP21/w209/spotify-visualizations/cleaned_data/2020_weekly_all_locations_top200.csv')
df4['year'] = '2020'
df = pd.concat([df1, df2, df3, df4])

all_locations_df_max = df.groupby(["track", 'artist','country_chart', 'year']).max().reset_index()[['track','artist',"year", 'streams', "country_chart",'spotify_link']]

all_locations_df_max
all_locations_df_max.to_csv("cleaned_data/2017_2020_all_locations_max_streams.csv", index = False)<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.15601424979044426, 'fraction_numerical': 0.023994132439228835, 'mean_word_length': 7.560538116591928, 'pattern_counts': {'":': 0, '<': 37, '<?xml version=': 0, '>': 38, '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': '9', 'dir': 'jupyter-structured-clean-dedup', 'id': '7133', 'license': 'MIT', 'n_tokens_mistral': 3780, 'n_tokens_neox': 3553, 'n_words': 578, 'path': 'eda_notebooks/top_charts_eda.ipynb', 'repo_name': 'daphne-yang/spotify-visualizations', 'size': '9544'}