<jupyter_start><jupyter_text>Tutorial - Extracting Text and Analysing Sentiment from Digitized Documents in the UCSD Library's Digital Archive  Part 4 - Analyzing CategoriesIn this section of the tutorial, we'll look into the relationship between the documents we've extracted, the accuracy and completion rates, and the categories assigned to each document by the GCP natural language processing API's pre-trained classification model.   Normalizing the datasetFirst thing you might notice is that this is a difficult dataset to query. Let's read the classification data into a dataframe.<jupyter_code>import pandas as pd
from google.colab import drive<jupyter_output><empty_output><jupyter_text>as before, we need to mount the drive to get access to the csv file created and saved in part 2<jupyter_code>drive.mount('/content/gdrive')
df_categories = pd.read_csv('gdrive/My Drive//No-More-Silence/glbths_2005-13_001_001_categories.csv')<jupyter_output><empty_output><jupyter_text>Take a look at the Categories column. The data returned from the API is in JSON format, which corresponds to a dictionary in Python.Unfortunately, this will make it difficult to query records based on a particular category (in relational database design, this means the table is not in first normal form - each cell does not represent a "single, atomic (indivisible) value"[1]).[1] https://en.wikipedia.org/wiki/First_normal_form<jupyter_code>df_categories<jupyter_output><empty_output><jupyter_text>Create a table with one category per line. Database design is an intetesting topic. For this tutorial, we'll create a separate row for each unique document-page-id, category, and probability (in RDBMS terms, we'll get this into first normal form).<jupyter_code>import json
document_page_ids = []
category_names = []
category_confidences = []

for d in df_categories.itertuples():
  if type(d[5]) == type('str'):
    json_acceptable_string = d[5].replace("'", "\"")
    jsn = json.loads(json_acceptable_string)
    for c in jsn['categories']:
      document_page_ids.append(d[2])
      category_names.append(c['name'])
      category_confidences.append(c['confidence'])
df_categories_1f = pd.DataFrame({"document_page_id": document_page_ids, "category": category_names, "confidence": category_confidences})
df_categories_1f<jupyter_output><empty_output><jupyter_text>Investigating the relationshop between records and categories, retention rate, and confidence Outer join to get all rows<jupyter_code>df = pd.read_csv('gdrive/My Drive//No-More-Silence/glbths_2005-13_001_001.csv')

print(df_categories_1f.columns)
print(df.columns)
#df_categories['document_page_id']
!pip install pandasql
from pandasql import sqldf
pysqldf = lambda q: sqldf(q, globals())
df_join = pysqldf("SELECT d.document_page_id, d.retained, dc.document_page_id, dc.category, dc.confidence FROM \
  df d LEFT OUTER JOIN df_categories_1f dc ON d.document_page_id = dc.document_page_id")
df_join
pysqldf("SELECT category, COUNT(*), AVG(retained), AVG(confidence) FROM df_join GROUP BY category ORDER BY COUNT(*) DESC")<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.08712487899322362, 'fraction_numerical': 0.011293965795417877, 'mean_word_length': 6.294117647058823, 'pattern_counts': {'":': 3, '<': 21, '<?xml version=': 0, '>': 21, 'https://': 1, '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': '5', 'dir': 'jupyter-structured-clean-dedup', 'id': '506993', 'license': 'MIT', 'n_tokens_mistral': 978, 'n_tokens_neox': 917, 'n_words': 305, 'path': 'NMS-Tutorial-Part-4-Categories.ipynb', 'repo_name': 'geoffswc/NLP-NoMoreSilence', 'size': '3099'}