<jupyter_start><jupyter_text>Parallelizing the computationThe loop-based approach to process each track id individually is taking too long.Now we will leverage the data processing capabilities of pandas.We use an outer join to combine lyrics with track ids. Then we aggregate the results for individual songs.<jupyter_code>import numpy as np
import pandas as pd
word_table = pd.read_csv("../datasets/word_table.csv")
word_table.head()
word_table["word"]=word_table["word"].astype(str)
word_table.index = word_table["word"]
word_table = word_table["descriptor"]
word_table.head()
import sqlite3
conn = sqlite3.connect("../datasets/mxm_dataset.db")

cursor = conn.cursor()
cursor.execute("SELECT track_id, word, count FROM lyrics ORDER BY track_id;")
track_word_count = cursor.fetchall()
cursor.close()
track_word_count[:5]
sqldb_frame = pd.DataFrame(track_word_count, columns=["track_id", "word", "count"])
del track_word_count
sqldb_frame["word"]=sqldb_frame["word"].astype(str)<jupyter_output><empty_output><jupyter_text>Performing an outer joint to match words between lyrics and offensiveness rating<jupyter_code>joint = sqldb_frame.join(word_table, on="word", how="left", lsuffix='_caller', rsuffix='_other')
print(joint.shape)
joint.head()
obscene_indices = joint["descriptor"].astype("str")!="nan"
np.any(obscene_indices)
np.sum(obscene_indices)
joint_indexed = joint.set_index(["track_id", "descriptor"])
joint_indexed.loc[("TRAADYI128E078FB38",),]<jupyter_output><empty_output><jupyter_text>Aggregating the dataThe joint table uses track ids and offensiveness categories as indices. This is what we want, but we still have individual cells for every word.Now we aggregate the items for every index. We sum the entries. This gives us the total count of words in each category.<jupyter_code>joint_indexed.index.is_unique
joint_indexed_filtered = joint_indexed["count"]
joint_indexed_filtered.head()
joint_aggregated = joint_indexed_filtered.agg("sum")
offensiveness_rating = joint_indexed_filtered.groupby(str, axis=0).agg("sum")
offensiveness_rating.head()
offensiveness_rating.to_pickle("../pickles/offensiveness_rating_unstructured")<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.11187845303867404, 'fraction_numerical': 0.0055248618784530384, 'mean_word_length': 8.246808510638298, 'pattern_counts': {'":': 0, '<': 13, '<?xml version=': 0, '>': 13, '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': '3', 'dir': 'jupyter-structured-clean-dedup', 'id': '219257', 'license': 'MIT', 'n_tokens_mistral': 698, 'n_tokens_neox': 681, 'n_words': 169, 'path': 'Project/5_rating_all_songs.ipynb', 'repo_name': 'lhk/epfl_ada', 'size': '2172'}