<jupyter_start><jupyter_text>Annotating Lang's Fairy Tales With Wikipedia LinksThe Wikipedia page [`Lang's_Fairy_Books`](https://en.wikipedia.org/wiki/Lang's_Fairy_Books) lists the contents of Lang's coloured fairy books (as well as several other books), along with links to the Wikipedia page associated with each tale, if available.This means we can have a go at annotating our database with Wikipedia links for each story. From those pages in turn, or associated *DBpedia* pages, we might also be able to extract Aarne-Thompson classification codes for the corresponding stories.<jupyter_code>from sqlite_utils import Database

db_name = "lang_fairy_tale.db"
db = Database(db_name)
conn = db.conn

# Load in the sql magic
%load_ext sql
%sql sqlite:///$db_name<jupyter_output><empty_output><jupyter_text>Load in the Wikipedia page that lists Lang's Fairy Book collections and provides links to other WIkipedia pages associated with stories contained in them.<jupyter_code>import requests
from bs4 import BeautifulSoup

url = "https://en.wikipedia.org/wiki/Lang's_Fairy_Books"

html = requests.get(url)<jupyter_output><empty_output><jupyter_text>We now make some lovely soup from the page that we can then start to fish entrails out of:<jupyter_code>wp_soup = BeautifulSoup(html.content, "html.parser")
# Find the span for a particular book
wp_book_loc =  wp_soup.find("span", id="The_Blue_Fairy_Book_(1889)")

# Then navigate relative to this to get the (linked) story list
wp_book_stories = wp_book_loc.find_parent().find_next("ul").find_all('li')
wp_book_stories[:3]<jupyter_output><empty_output><jupyter_text>Get the Wikipedia path for stories with a Wikipedia page:<jupyter_code>wp_book_paths = [(li.find("a").get("title"), li.find("a").get("href")) for li in wp_book_stories]

wp_book_paths[:3]<jupyter_output><empty_output><jupyter_text>Useful as a list of `dict`s or *pandas* `DataFrame`?<jupyter_code>import pandas as pd

wp_book_paths_wide = []

for item in wp_book_paths:
    wp_book_paths_wide.append( {"title":item[0].strip(), "path":item[1]} )
    
wp_book_df = pd.DataFrame(wp_book_paths_wide)
wp_book_df<jupyter_output><empty_output><jupyter_text>See if we can then cross reference these with stories in the database?<jupyter_code>q = "SELECT book, title, chapter_order FROM books WHERE book='The Blue Fairy Book' ORDER BY chapter_order ASC"
df_blue = pd.read_sql(q, conn)

df_blue.head()<jupyter_output><empty_output><jupyter_text>Let's see if the chapters align in terms of order as presented:<jupyter_code>pd.DataFrame({"book":df_blue["title"], "wp":wp_book_df["title"], "wp_path":wp_book_df["path"]})<jupyter_output><empty_output><jupyter_text>Yes, they do so we can use that as a basis of a merge. That said, in the genral case it would probably also be useful to generate a fuzzy match score between matched titles with a report on any low scoring matches, just in case the alignment has gone awry.<jupyter_code># TO DO  - wp table for links, story and story order?
# TO DO fuzzy match score test just to check ingest and allow user to check poor matches<jupyter_output><empty_output><jupyter_text>In passing,what if we wanted to try to match on the titles themselves?If we use decased, but otherwise exact, matching, we see it's bit flaky....<jupyter_code>pd.merge(df_blue["title"], wp_book_df,
         left_on=df_blue["title"].str.lower(),
         right_on=wp_book_df["title"].str.lower(),
         how ="left" )<jupyter_output><empty_output><jupyter_text>A fuzzy match might be able to improve things...<jupyter_code># Reused from on https://stackoverflow.com/a/56315491/454773
from fuzzywuzzy import fuzz
from fuzzywuzzy import process

def fuzzy_merge(df_1, df_2, key1, key2, threshold=90, limit=2):
    """
    :param df_1: the left table to join
    :param df_2: the right table to join
    :param key1: key column of the left table
    :param key2: key column of the right table
    :param threshold: how close the matches should be to return a match, based on Levenshtein distance
    :param limit: the amount of matches that will get returned, these are sorted high to low
    :return: dataframe with boths keys and matches
    """
    s = df_2[key2].tolist()
    
    m = df_1[key1].apply(lambda x: process.extract(x, s, limit=limit))  
    df_1['matches'] = m
    
    m2 = df_1['matches'].apply(lambda x: ', '.join([i[0] for i in x if i[1] >= threshold]))
    df_1['matches'] = m2
    return df_1
fuzzy_merge(df_blue, wp_book_df, "title", "title", 88, limit=1)[["title", "matches"]]
#https://github.com/jsoma/fuzzy_pandas/

# This is probably overkill...
#%pip install fuzzy_pandas
import fuzzy_pandas as fpd

fpd.fuzzy_merge(df_blue[["title"]], wp_book_df,
            left_on='title',
            right_on='title',
            ignore_case=True,
            ignore_nonalpha=True,
            method='jaro', #bilenko, levenshtein, metaphone, jaro
            threshold=0.86, # If we move to 0.86 wee get a false positive...
            keep_left='all',
            keep_right="all"
               )

fpd.fuzzy_merge(df_blue[["title"]], wp_book_df,
            left_on='title',
            right_on='title',
            ignore_case=True,
            ignore_nonalpha=True,
            method='metaphone', #levenshtein, metaphone, jaro, bilenko
            threshold=0.86,
            keep_left='all',
            keep_right="all"
               )<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.10476718403547672, 'fraction_numerical': 0.010716925351071693, 'mean_word_length': 4.429287863590773, 'pattern_counts': {'":': 5, '<': 41, '<?xml version=': 0, '>': 42, 'https://': 4, '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': '10', 'dir': 'jupyter-structured-clean-dedup', 'id': '242645', 'license': 'MIT', 'n_tokens_mistral': 1821, 'n_tokens_neox': 1746, 'n_words': 538, 'path': 'lang-fairy-books-db_PART_3.ipynb', 'repo_name': 'psychemedia/storynotes', 'size': '5412'}