<jupyter_start><jupyter_text>Tidy data In this notebook, we take a closer look at the [British Library books dataset](https://github.com/mromanello/ADA-DHOxSS/tree/master/data) and tidy it up.<jupyter_code># imports

import pandas as pd
import json, os, codecs, requests
from collections import defaultdict, OrderedDict
import seaborn as sns<jupyter_output><empty_output><jupyter_text>Import the datasetLet us import the sample dataset in memory, as it is, without transformations. We rely on some Python libraries to do so.<jupyter_code># book metadata
m_url = "https://raw.githubusercontent.com/mromanello/ADA-DHOxSS/master/data/bl_books/sample/book_data_sample.json"
r = requests.get(m_url)
metadata = r.json()
# load the fulltexts (we need to do some gymnastics to load from GitHub)
user = "mromanello"
repo = "ADA-DHOxSS"
raw_root = "https://raw.githubusercontent.com/mromanello/ADA-DHOxSS/master/"
data_root = "data/bl_books/sample/full_texts/"

url = "https://api.github.com/repos/{}/{}/git/trees/master?recursive=1".format(user, repo)
r = requests.get(url)
res = r.json()

texts = defaultdict(list)
for file in res["tree"]:
    file = file["path"]
    if ".json" in file and file.startswith(data_root):
        r = requests.get(raw_root+file)
        t = r.json()
        texts[file.split("/")[-1]] = t
# enriched metadata
em_url = "https://raw.githubusercontent.com/mromanello/ADA-DHOxSS/master/data/bl_books/sample/extra_metadata_sample.csv"
df_extra = pd.read_csv(em_url, delimiter=";")
df_extra = df_extra.rename(str.lower, axis='columns') # rename columns to lower case<jupyter_output><empty_output><jupyter_text>Take a lookLet's take a look to the dataset<jupyter_code># there are 452 books in the sample

print(len(metadata))
# each one contains the following catalog metadata

metadata[0]<jupyter_output><empty_output><jupyter_text>**Questions** on 'metadata':* Can you identify some messy aspects of this dataset representation?* Take a look at the 'shelfmarks' or 'title' fields: what is the problem here?* Do the same for the 'authors' and 'pdf' fields: what is the problem?* Look at the datefield of the *third* item in this list: is there a problem?<jupyter_code># let's check we have the same amount of books with a text file

print(len(texts))<jupyter_output>452<jupyter_text>*Note: we have selected for the sample just the first volume/pdf for every book.*<jupyter_code># each text comes as a list of lists: one per page, as follows [page number, text]

texts['000000196_01_text.json'][:9]
# the extra metadata can be already used as a data frame

df_extra[df_extra["first_pdf"] == "lsidyv35c55757"]
# we perform here a selection of rows which abide to a given condition. We'll see more of this later on.<jupyter_output><empty_output><jupyter_text>**Question**: explore this data frame and find examples of messy aspects.<jupyter_code># Create data frames for all datasets
# We drop some variables we don't need at this stage

# metadata
datefield = list() # '1841'
publisher = list() # 'Privately printed',
title = list() # ["The Poetical Aviary, with a bird's-eye view of the English poets. [The preface signed: A. A.] Ms. notes"]
edition = list() # ''
place = list() # 'Calcutta'
issuance = list() # 'monographic'
authors = list() # {'creator': ['<NAME>.']}
first_pdf = list() # {'1': 'lsidyv35c55757'}
number_volumes = list()
identifier = list() # '000000196'
fulltext_filename = list() # 'sample/full_texts/000000196_01_text.json'
for book in metadata:
    if book["date"]:
        datefield.append(int(book["date"][:4]))
    else:
        datefield.append(None)
    publisher.append(book["publisher"])
    title.append(book["title"][0])
    edition.append(book["edition"])
    place.append(book["place"])
    issuance.append(book["issuance"])
    if "creator" in book["authors"].keys():
        authors.append(book["authors"]["creator"]) # this is a list!
    else:
        authors.append([''])
    first_pdf.append(book["pdf"]["1"])
    number_volumes.append(len(book["pdf"]))
    identifier.append(book["identifier"])
    fulltext_filename.append(book["fulltext_filename"].split("/")[-1])
df_meta = pd.DataFrame.from_dict({"datefield": datefield, "publisher": publisher,
                                 "title": title, "edition": edition, "place": place,
                                 "issuance": issuance, "authors": authors, "first_pdf": first_pdf,
                                 "number_volumes": number_volumes, "identifier": identifier,
                                 "fulltext_filename": fulltext_filename})

# texts
how_many_pages = 50 # we reduce the amount of text to the first n pages, to make it faster to play with it
fulltext_filename = list()
fulltext = list()
for f,t in texts.items():
    fulltext_filename.append(f)
    text = " ".join([line[1] for line in t][:how_many_pages])
    fulltext.append(text)

df_texts = pd.DataFrame.from_dict({"fulltext_filename": fulltext_filename, "fulltext": fulltext})
df_meta.head(5)
df_texts.head(1)<jupyter_output><empty_output><jupyter_text>A second lookLet's check data types and typical values to be sure of what we have.<jupyter_code>df_meta.dtypes
df_meta["datefield"].hist(bins=20)
df_meta["number_volumes"].hist()
variable = "place"
df_meta[variable].value_counts()[:11]
df_meta[variable].value_counts()[-10:]
df_extra["genre"].value_counts()
df_extra["type"].value_counts()<jupyter_output><empty_output><jupyter_text>**Questions**:    * 'place' seems like a reasonably uniform variable. Try with 'edition' instead, and think about how that might be more problematic.* While the 'genre' variable is uniform in representation, the 'type' variable is not. Can you find out what are the most well-represented types for each genre category? How might this influence our use of 'type' for analysis?  Tidying up Here is the model we want to end-up with, organised in tables and relations among them:* Full view (for your curiosity): https://dbdiagram.io/d/5d06a4adfff7633dfc8e3a42* Reduced view (we here use this one): https://dbdiagram.io/d/5d06a5d0fff7633dfc8e3a47<jupyter_code># first, join the extra metadata genre column to the metadata data frame. More details on joins in class 3.1.

df_extra_genre = df_extra[["type","genre","first_pdf"]]
df_book = df_meta.join(df_extra_genre.set_index('first_pdf'), on='first_pdf')
df_book.head(1)
# second, add the book_id to the book_text dataframe

df_book_text = df_texts.join(df_book[["identifier","fulltext_filename"]].set_index('fulltext_filename'), on='fulltext_filename')
df_book_text = df_book_text.rename(columns={"identifier":"book_id"})
df_book_text.head(3)
# third, pull our author information and create the author table and the author-book table

author_id = 0 # this is a counter which provides for a distinct identifier to every author
author_dict = OrderedDict()
author_book_table = {"book_id":list(),"author_id":list()}
for book_id, authors in df_book[["identifier","authors"]].values:
    for author in authors:
        if author not in author_dict.keys():
            author_dict[author] = author_id
            author_id += 1
        author_book_table["book_id"].append(book_id)
        author_book_table["author_id"].append(author_dict[author])
        
df_author_book = pd.DataFrame.from_dict(author_book_table)
df_author = pd.DataFrame.from_dict({"name":[v for v in author_dict.keys()],
                                   "id":[k for k in author_dict.values()]})
df_author.set_index("id", inplace=True)
df_author.head(3)
df_author_book.head(3)
# drop authors from df_books

df_book.drop(columns=["authors"], inplace=True)<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.12593565331582404, 'fraction_numerical': 0.014182534471437951, 'mean_word_length': 4.818181818181818, 'pattern_counts': {'":': 18, '<': 33, '<?xml version=': 0, '>': 33, 'https://': 7, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 5, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '8', 'dir': 'jupyter-structured-clean-dedup', 'id': '112327', 'license': 'CC-BY-4.0', 'n_tokens_mistral': 2542, 'n_tokens_neox': 2435, 'n_words': 690, 'path': '2.2_Tidy_data.ipynb', 'repo_name': 'Giovanni1085/UvA_CSDA_2021', 'size': '7613'}