A. Data Enrichment Process-IMPROVE.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "45373607",
   "metadata": {},
   "source": [
    "# Data Enrichment Process\n",
    "In this notebook, we are working on enriching the Book-Crossing dataset. We are describing every step in detail."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "048f3154",
   "metadata": {},
   "source": [
    "## 0. Import Libraries\n",
    "First, we are importing libraries that are needed for our process."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 192,
   "id": "cf3cc328",
   "metadata": {},
   "outputs": [],
   "source": [
    "import time\n",
    "from qwikidata.sparql  import return_sparql_query_results\n",
    "from qwikidata.linked_data_interface import get_entity_dict_from_api\n",
    "import ftfy\n",
    "import pandas as pd\n",
    "import numpy as np\n",
    "import pickle as pkl\n",
    "from collections import Counter\n",
    "import urllib.request\n",
    "from urllib.error import HTTPError\n",
    "import json\n",
    "import string\n",
    "from difflib import SequenceMatcher\n",
    "from viapy.api import ViafAPI\n",
    "import re\n",
    "import math\n",
    "import matplotlib.pyplot as plt\n",
    "from scipy.signal import find_peaks\n",
    "\n",
    "import warnings\n",
    "warnings.simplefilter(action='ignore', category=FutureWarning)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "794c1c54",
   "metadata": {},
   "source": [
    "We are setting the locations where we are saving our output files. The \"large\" location is used for files too large to be added on github - hence they are ignored during the git commit."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "8fae3d25",
   "metadata": {},
   "outputs": [],
   "source": [
    "location_to_save = \"data/final_files/\"\n",
    "large_location_to_save = \"data/final_files/large_files/\""
   ]
  },
  {
   "cell_type": "markdown",
   "id": "8cebdb7a",
   "metadata": {},
   "source": [
    "## 3. First look in the data\n",
    "<b>Goal</b>: To do a first examination on how the data looks like."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "cd10f6b5",
   "metadata": {},
   "source": [
    "A. Read book file. The book dataset is directly taken from http://www2.informatik.uni-freiburg.de/~cziegler/BX/."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 68,
   "id": "c0c80ec2",
   "metadata": {},
   "outputs": [],
   "source": [
    "filename = \"data/items_books.csv\" # Books-Crossing books\n",
    "books = pd.read_csv(filename, low_memory = False).drop([\"Image-URL-S\", \"Image-URL-M\",\"Image-URL-L\"], axis=1) # read books and remove images\n",
    "books.columns = [\"ISBN\", \"title\", \"author\", \"year\", \"publisher\"] # rename columns to simplify"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 69,
   "id": "346b99c1",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "There are 271360 books.\n"
     ]
    }
   ],
   "source": [
    "print(\"There are \"+str(len(books))+\" books.\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 70,
   "id": "ad138458",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>ISBN</th>\n",
       "      <th>title</th>\n",
       "      <th>author</th>\n",
       "      <th>year</th>\n",
       "      <th>publisher</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0195153448</td>\n",
       "      <td>Classical Mythology</td>\n",
       "      <td>Mark P. O. Morford</td>\n",
       "      <td>2002</td>\n",
       "      <td>Oxford University Press</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>0002005018</td>\n",
       "      <td>Clara Callan</td>\n",
       "      <td>Richard Bruce Wright</td>\n",
       "      <td>2001</td>\n",
       "      <td>HarperFlamingo Canada</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>0060973129</td>\n",
       "      <td>Decision in Normandy</td>\n",
       "      <td>Carlo D'Este</td>\n",
       "      <td>1991</td>\n",
       "      <td>HarperPerennial</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>0374157065</td>\n",
       "      <td>Flu: The Story of the Great Influenza Pandemic...</td>\n",
       "      <td>Gina Bari Kolata</td>\n",
       "      <td>1999</td>\n",
       "      <td>Farrar Straus Giroux</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>0393045218</td>\n",
       "      <td>The Mummies of Urumchi</td>\n",
       "      <td>E. J. W. Barber</td>\n",
       "      <td>1999</td>\n",
       "      <td>W. W. Norton &amp;amp; Company</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         ISBN                                              title  \\\n",
       "0  0195153448                                Classical Mythology   \n",
       "1  0002005018                                       Clara Callan   \n",
       "2  0060973129                               Decision in Normandy   \n",
       "3  0374157065  Flu: The Story of the Great Influenza Pandemic...   \n",
       "4  0393045218                             The Mummies of Urumchi   \n",
       "\n",
       "                 author  year                   publisher  \n",
       "0    Mark P. O. Morford  2002     Oxford University Press  \n",
       "1  Richard Bruce Wright  2001       HarperFlamingo Canada  \n",
       "2          Carlo D'Este  1991             HarperPerennial  \n",
       "3      Gina Bari Kolata  1999        Farrar Straus Giroux  \n",
       "4       E. J. W. Barber  1999  W. W. Norton &amp; Company  "
      ]
     },
     "execution_count": 70,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "books.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "dc4497b3-9d95-49b6-868f-84e0a2c4ce1e",
   "metadata": {},
   "source": [
    "A'. Apply the ftfy.fix_test in all strings of the dataframe."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 71,
   "id": "f8ee4058-1375-4ddd-8b16-ab66cb84f4fb",
   "metadata": {},
   "outputs": [],
   "source": [
    "books.title = books.title.apply(ftfy.fix_text)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 72,
   "id": "9e9abac9-ea2b-44d8-bfb6-dda32f1afdd3",
   "metadata": {},
   "outputs": [],
   "source": [
    "books.author = books.author.apply(lambda x: ftfy.fix_text(x) if type(x)==str else x)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 73,
   "id": "f10cf5d9-5ff3-4c69-b251-f932f5427dea",
   "metadata": {},
   "outputs": [],
   "source": [
    "books.publisher = books.publisher.apply(lambda x: ftfy.fix_text(x) if type(x)==str else x)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "61d2a111",
   "metadata": {},
   "source": [
    "B. Choose \"unique\" authors based on exact author name."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 74,
   "id": "cdee6371",
   "metadata": {},
   "outputs": [],
   "source": [
    "authors = pd.DataFrame(books.author.unique(), columns=[\"author\"]).dropna().reset_index().drop(\"index\", axis=1) # find unique authors and remove nan values"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 75,
   "id": "34bb433f",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "There are 102022 unique authors.\n"
     ]
    }
   ],
   "source": [
    "print(\"There are \"+str(len(authors))+\" unique authors.\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 76,
   "id": "a22bad9c",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>author</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Mark P. O. Morford</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Richard Bruce Wright</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Carlo D'Este</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Gina Bari Kolata</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>E. J. W. Barber</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                 author\n",
       "0    Mark P. O. Morford\n",
       "1  Richard Bruce Wright\n",
       "2          Carlo D'Este\n",
       "3      Gina Bari Kolata\n",
       "4       E. J. W. Barber"
      ]
     },
     "execution_count": 76,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "authors.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "40377263",
   "metadata": {},
   "source": [
    "C. Statistics."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "6e0ce0d0",
   "metadata": {},
   "source": [
    "C.1 Non-latin named authors. We are looking into how many authors have legible names in English."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 63,
   "id": "49960e77",
   "metadata": {},
   "outputs": [],
   "source": [
    "# def isEnglish(s): # function to check if a name is latin\n",
    "#     try:\n",
    "#         s.encode(encoding='utf-8').decode('ascii')\n",
    "#     except UnicodeDecodeError:\n",
    "#         return False\n",
    "#     else:\n",
    "#         return True"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 64,
   "id": "2f3f2b84",
   "metadata": {},
   "outputs": [],
   "source": [
    "# authors[\"author_latin\"] = authors.author.apply(lambda x : isEnglish(x))\n",
    "# num_latin_authors = len(authors[authors.author_latin == True])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 65,
   "id": "4e39f91c",
   "metadata": {},
   "outputs": [],
   "source": [
    "# print(\"There are \", num_latin_authors,\"latin authors out of\", str(len(authors))+\".\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 67,
   "id": "96e7fc25",
   "metadata": {},
   "outputs": [],
   "source": [
    "# del authors[\"author_latin\"]"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "6908cf47",
   "metadata": {},
   "source": [
    "C.2 Author entries with a single name. We are looking into how many authors are only referenced by one name (e.g., their last name)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 77,
   "id": "5850e94d",
   "metadata": {},
   "outputs": [],
   "source": [
    "authors[\"author_single\"] = authors.author.apply(lambda x: len(x.split(\" \"))==1)\n",
    "num_single_named_authors = len(authors[authors.author_single == True])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 78,
   "id": "219a0448",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "There are  2501 unique single named authors out of 102022.\n"
     ]
    }
   ],
   "source": [
    "print(\"There are \", num_single_named_authors,\"unique single named authors out of\", str(len(authors))+\".\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 79,
   "id": "75241ff3",
   "metadata": {},
   "outputs": [],
   "source": [
    "del(authors[\"author_single\"])"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "570aff28",
   "metadata": {},
   "source": [
    "We see that there are authors with illegible names, but also with just one name - we need extra information to make sure that we know the right person. See the next three cels for examples."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 80,
   "id": "228ef25f",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>ISBN</th>\n",
       "      <th>title</th>\n",
       "      <th>author</th>\n",
       "      <th>year</th>\n",
       "      <th>publisher</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>64014</th>\n",
       "      <td>3807701710</td>\n",
       "      <td>Per Anhalter Durch Due Galaxis</td>\n",
       "      <td>Adams</td>\n",
       "      <td>1994</td>\n",
       "      <td>Koch, Neff &amp; Oetinger &amp; Co</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>68221</th>\n",
       "      <td>3548224938</td>\n",
       "      <td>Das Leben, Das Universum Und Der Ganze Rest (F...</td>\n",
       "      <td>Adams</td>\n",
       "      <td>0</td>\n",
       "      <td>Ullstein-Taschenbuch-Verlag, Zweigniederlassun...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>109698</th>\n",
       "      <td>3548224911</td>\n",
       "      <td>Per Anhalter Durch Die Galaxis</td>\n",
       "      <td>Adams</td>\n",
       "      <td>0</td>\n",
       "      <td>Ullstein-Taschenbuch-Verlag, Zweigniederlassun...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>135546</th>\n",
       "      <td>354822492X</td>\n",
       "      <td>Das Restaurant Am Ende Des Universums (Fiction...</td>\n",
       "      <td>Adams</td>\n",
       "      <td>0</td>\n",
       "      <td>Ullstein-Taschenbuch-Verlag, Zweigniederlassun...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>242053</th>\n",
       "      <td>0671657690</td>\n",
       "      <td>DIRK GENTLY DETCTX</td>\n",
       "      <td>Adams</td>\n",
       "      <td>1987</td>\n",
       "      <td>Pocket</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>242349</th>\n",
       "      <td>0140390545</td>\n",
       "      <td>Mont Saint Michel and Chartres (Penguin Classics)</td>\n",
       "      <td>Adams</td>\n",
       "      <td>1986</td>\n",
       "      <td>Penguin Books</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "              ISBN                                              title author  \\\n",
       "64014   3807701710                     Per Anhalter Durch Due Galaxis  Adams   \n",
       "68221   3548224938  Das Leben, Das Universum Und Der Ganze Rest (F...  Adams   \n",
       "109698  3548224911                     Per Anhalter Durch Die Galaxis  Adams   \n",
       "135546  354822492X  Das Restaurant Am Ende Des Universums (Fiction...  Adams   \n",
       "242053  0671657690                                 DIRK GENTLY DETCTX  Adams   \n",
       "242349  0140390545  Mont Saint Michel and Chartres (Penguin Classics)  Adams   \n",
       "\n",
       "        year                                          publisher  \n",
       "64014   1994                         Koch, Neff & Oetinger & Co  \n",
       "68221      0  Ullstein-Taschenbuch-Verlag, Zweigniederlassun...  \n",
       "109698     0  Ullstein-Taschenbuch-Verlag, Zweigniederlassun...  \n",
       "135546     0  Ullstein-Taschenbuch-Verlag, Zweigniederlassun...  \n",
       "242053  1987                                             Pocket  \n",
       "242349  1986                                      Penguin Books  "
      ]
     },
     "execution_count": 80,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "books[books.author == \"Adams\"]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 81,
   "id": "284ff9b3",
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>ISBN</th>\n",
       "      <th>title</th>\n",
       "      <th>author</th>\n",
       "      <th>year</th>\n",
       "      <th>publisher</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>162</th>\n",
       "      <td>0671461494</td>\n",
       "      <td>The Hitchhiker's Guide to the Galaxy</td>\n",
       "      <td>Douglas Adams</td>\n",
       "      <td>1982</td>\n",
       "      <td>Pocket</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>336</th>\n",
       "      <td>0345371984</td>\n",
       "      <td>Last Chance to See</td>\n",
       "      <td>Douglas Adams</td>\n",
       "      <td>1992</td>\n",
       "      <td>Ballantine Books</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>369</th>\n",
       "      <td>0517577402</td>\n",
       "      <td>Mostly Harmless</td>\n",
       "      <td>Douglas Adams</td>\n",
       "      <td>1992</td>\n",
       "      <td>Random House Inc</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>879</th>\n",
       "      <td>0345418778</td>\n",
       "      <td>Mostly Harmless</td>\n",
       "      <td>Douglas Adams</td>\n",
       "      <td>2000</td>\n",
       "      <td>Ballantine Books</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>914</th>\n",
       "      <td>0345391802</td>\n",
       "      <td>The Hitchhiker's Guide to the Galaxy</td>\n",
       "      <td>Douglas Adams</td>\n",
       "      <td>1995</td>\n",
       "      <td>Ballantine Books</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>214105</th>\n",
       "      <td>0613064054</td>\n",
       "      <td>The Hitchhiker's Guide to the Galaxy</td>\n",
       "      <td>Douglas Adams</td>\n",
       "      <td>1999</td>\n",
       "      <td>Sagebrush Bound</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>226193</th>\n",
       "      <td>2207249158</td>\n",
       "      <td>Le Guide galactique, tome 2 : Le dernier resta...</td>\n",
       "      <td>Douglas Adams</td>\n",
       "      <td>1999</td>\n",
       "      <td>Deno�«l</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>228755</th>\n",
       "      <td>0671678523</td>\n",
       "      <td>LONG DARK TEA-TIME OF THE SOUL</td>\n",
       "      <td>Douglas Adams</td>\n",
       "      <td>1989</td>\n",
       "      <td>Simon &amp; Schuster Audio</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>228871</th>\n",
       "      <td>1590071506</td>\n",
       "      <td>The Salmon of Doubt: Hitchhiking the Galaxy On...</td>\n",
       "      <td>Douglas Adams</td>\n",
       "      <td>2002</td>\n",
       "      <td>New Millennium Audio</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>249759</th>\n",
       "      <td>0345418905</td>\n",
       "      <td>Life, the Universe and Everything</td>\n",
       "      <td>Douglas Adams</td>\n",
       "      <td>1997</td>\n",
       "      <td>Ballantine Books</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>95 rows × 5 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "              ISBN                                              title  \\\n",
       "162     0671461494               The Hitchhiker's Guide to the Galaxy   \n",
       "336     0345371984                                 Last Chance to See   \n",
       "369     0517577402                                    Mostly Harmless   \n",
       "879     0345418778                                    Mostly Harmless   \n",
       "914     0345391802               The Hitchhiker's Guide to the Galaxy   \n",
       "...            ...                                                ...   \n",
       "214105  0613064054               The Hitchhiker's Guide to the Galaxy   \n",
       "226193  2207249158  Le Guide galactique, tome 2 : Le dernier resta...   \n",
       "228755  0671678523                     LONG DARK TEA-TIME OF THE SOUL   \n",
       "228871  1590071506  The Salmon of Doubt: Hitchhiking the Galaxy On...   \n",
       "249759  0345418905                  Life, the Universe and Everything   \n",
       "\n",
       "               author  year               publisher  \n",
       "162     Douglas Adams  1982                  Pocket  \n",
       "336     Douglas Adams  1992        Ballantine Books  \n",
       "369     Douglas Adams  1992        Random House Inc  \n",
       "879     Douglas Adams  2000        Ballantine Books  \n",
       "914     Douglas Adams  1995        Ballantine Books  \n",
       "...               ...   ...                     ...  \n",
       "214105  Douglas Adams  1999         Sagebrush Bound  \n",
       "226193  Douglas Adams  1999                 Deno�«l  \n",
       "228755  Douglas Adams  1989  Simon & Schuster Audio  \n",
       "228871  Douglas Adams  2002    New Millennium Audio  \n",
       "249759  Douglas Adams  1997        Ballantine Books  \n",
       "\n",
       "[95 rows x 5 columns]"
      ]
     },
     "execution_count": 81,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "books[books.author == \"Douglas Adams\"]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 82,
   "id": "45eb88c0",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>ISBN</th>\n",
       "      <th>title</th>\n",
       "      <th>author</th>\n",
       "      <th>year</th>\n",
       "      <th>publisher</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>4495</th>\n",
       "      <td>0345460952</td>\n",
       "      <td>The Salmon of Doubt</td>\n",
       "      <td>DOUGLAS ADAMS</td>\n",
       "      <td>2003</td>\n",
       "      <td>Ballantine Books</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6221</th>\n",
       "      <td>0345391829</td>\n",
       "      <td>Life, the Universe, and Everything (Hitchhiker...</td>\n",
       "      <td>DOUGLAS ADAMS</td>\n",
       "      <td>1995</td>\n",
       "      <td>Ballantine Books</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13634</th>\n",
       "      <td>0345391837</td>\n",
       "      <td>So Long, and Thanks for All the Fish</td>\n",
       "      <td>DOUGLAS ADAMS</td>\n",
       "      <td>1999</td>\n",
       "      <td>Ballantine Books</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19031</th>\n",
       "      <td>1400045088</td>\n",
       "      <td>The Salmon of Doubt: Hitchhiking the Galaxy On...</td>\n",
       "      <td>DOUGLAS ADAMS</td>\n",
       "      <td>2002</td>\n",
       "      <td>Harmony</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>79035</th>\n",
       "      <td>0345453743</td>\n",
       "      <td>The Ultimate Hitchhiker's Guide to the Galaxy</td>\n",
       "      <td>DOUGLAS ADAMS</td>\n",
       "      <td>2002</td>\n",
       "      <td>Del Rey</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>98237</th>\n",
       "      <td>1400052939</td>\n",
       "      <td>The Hitchhiker's Guide to the Galaxy Deluxe 25...</td>\n",
       "      <td>DOUGLAS ADAMS</td>\n",
       "      <td>2004</td>\n",
       "      <td>Harmony</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "             ISBN                                              title  \\\n",
       "4495   0345460952                                The Salmon of Doubt   \n",
       "6221   0345391829  Life, the Universe, and Everything (Hitchhiker...   \n",
       "13634  0345391837               So Long, and Thanks for All the Fish   \n",
       "19031  1400045088  The Salmon of Doubt: Hitchhiking the Galaxy On...   \n",
       "79035  0345453743      The Ultimate Hitchhiker's Guide to the Galaxy   \n",
       "98237  1400052939  The Hitchhiker's Guide to the Galaxy Deluxe 25...   \n",
       "\n",
       "              author  year         publisher  \n",
       "4495   DOUGLAS ADAMS  2003  Ballantine Books  \n",
       "6221   DOUGLAS ADAMS  1995  Ballantine Books  \n",
       "13634  DOUGLAS ADAMS  1999  Ballantine Books  \n",
       "19031  DOUGLAS ADAMS  2002           Harmony  \n",
       "79035  DOUGLAS ADAMS  2002           Del Rey  \n",
       "98237  DOUGLAS ADAMS  2004           Harmony  "
      ]
     },
     "execution_count": 82,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "books[books.author == \"DOUGLAS ADAMS\"]"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "2317d9b6",
   "metadata": {},
   "source": [
    "## 4. Access Google Books API\n",
    "<b>Goal</b>: To use the ISBNs from the dataset to get <u>the author and title of the book from <b>Google Books</b></u>. The reason is that the Book-Crossing dataset seems relatively incosistent in terms of naming."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f6228aae",
   "metadata": {},
   "source": [
    "A. Function to access the GB API."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 300,
   "id": "89072856",
   "metadata": {},
   "outputs": [],
   "source": [
    "def get_author_title(ISBN):\n",
    "    base_api_link = \"https://www.googleapis.com/books/v1/volumes?q=isbn:\"\n",
    "    headers = {'User-Agent':'EnrichBookCrossingBot (savvina.daniil@cwi.nl)'}\n",
    "    request = urllib.request.Request(base_api_link + ISBN+'&fields=items(volumeInfo(title,authors))', headers=headers)\n",
    "\n",
    "    with urllib.request.urlopen(request) as f:\n",
    "        text = f.read()\n",
    "    decoded_text = text.decode(\"utf-8\")\n",
    "    obj = json.loads(decoded_text) \n",
    "    volume_info = obj[\"items\"][0] \n",
    "    title = volume_info[\"volumeInfo\"][\"title\"]\n",
    "    authors = volume_info[\"volumeInfo\"][\"authors\"]\n",
    "        \n",
    "    return authors,title"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "214d0f6a",
   "metadata": {},
   "source": [
    "B. Initialize the entries."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 296,
   "id": "d102f50e",
   "metadata": {},
   "outputs": [],
   "source": [
    "books[\"alt_title\"] = \"\"\n",
    "books[\"alt_author\"] = \"\""
   ]
  },
  {
   "cell_type": "markdown",
   "id": "2145786e",
   "metadata": {},
   "source": [
    "C. Run the requests."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "fb23a3dc-f999-4982-b93e-2a2d988b8923",
   "metadata": {},
   "source": [
    "Do requests in batches"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 306,
   "id": "4c7c4c1d-bbfd-4ec6-aa58-0d111bef9d65",
   "metadata": {},
   "outputs": [],
   "source": [
    "a = '\\t645'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 309,
   "id": "ea0c2ea9-24d2-46a1-9d35-98ed1d34d48e",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'\\t645'"
      ]
     },
     "execution_count": 309,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "a.replace('\\n','')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "97fba026",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "0.035172462463378906 111800\n",
      "wait for Whats Bred In the Bone\n",
      "wait for Walking on Water: Reaching God in Our Time\n",
      "wait for Six Great Modern Short Novels\n",
      "60.889049768447876 111900\n",
      "wait for Paper Moon\n",
      "wait for Journey Great Barrier Reef\n",
      "wait for The Marble Faun (Everyman Paperback Classics)\n",
      "wait for Daniel Deronda (Classics Library (NTC))\n",
      "wait for Love, Hear My Heart\n",
      "wait for Permission Marketing : Turning Strangers Into Friends And Friends Into Customers\n",
      "94.8202576637268 112000\n",
      "SAVE!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!\n",
      "wait for MURDER MOST GRIZZLY : MURDER MOST GRIZZLY\n",
      "wait for Antique Fairy Tales (Little Unicorn)\n",
      "wait for Mosby's Diagnostic and Laboratory Test Reference (Mosby's Diagnostic and Laboratory Test Reference, 6th ed)\n",
      "64.23736929893494 112100\n",
      "wait for The Long Shot\n",
      "43.28814172744751 112200\n",
      "wait for Urban Myths: Incredible But True Stories of Sex, Drink and Unreliable Machinary. Featuring the Poodle in the Microwave, the Hairy Hand and Other Friend-of-a-friend Classics\n",
      "wait for Jimmy's blues: Selected poems\n",
      "wait for Jimmy's blues: Selected poems\n",
      "wait for Doctor Who: The Nightmare Fair : The Missing Episodes\n",
      "wait for On the Edge\n",
      "wait for War With the Robots\n",
      "wait for The Death of Bernadette Lefthand: A Novel\n",
      "wait for The Death of Bernadette Lefthand: A Novel\n",
      "wait for Babar and Zephir (Babar Books (Random House))\n",
      "wait for The Usborne Book of Whodunnits: The Deckchair Detectives/Unlimited Murder/the Missing Clue (Whodunnits Series)\n",
      "134.46026396751404 112300\n",
      "wait for Messie No More\n",
      "wait for Messie No More\n",
      "wait for The Cot Death Cover-up?\n",
      "63.12126159667969 112400\n",
      "wait for The shape of futures past: The story of prediction\n",
      "wait for WITHOUT CONSCIENCE: THE DISTURBING WORLD OF THE PSYCHOPATHS AMONG US\n",
      "wait for Ordinary Jack\n",
      "62.98720359802246 112500\n",
      "wait for The Sleepy Men\n",
      "43.79215669631958 112600\n",
      "wait for The Holocaust in American Life\n",
      "wait for Behind the Mask of Chivalry: The Making of the Second Ku Klux Klan\n",
      "wait for The Tooth Fairy Legend\n",
      "wait for World Almanac and Book of Facts\n",
      "wait for Life On The Edge\n",
      "86.41477632522583 112700\n",
      "wait for The Case Of The Floating Crime (Nancy Drew)\n",
      "wait for Ultimate Dinosaur Book\n",
      "55.222816944122314 112800\n",
      "wait for Our Kind: Who We Are, Where We Came From, Where Are We Going\n",
      "wait for Kingfisher Reference Atlas an a Z Guide to Cou\n",
      "wait for Tablets of Ararat\n",
      "62.969478607177734 112900\n",
      "wait for Guinness Book of World Records 1981\n",
      "wait for The Princeton Review: Word Smart : Building an Educated Vocabulary (Princeton Review (Hardcover))\n",
      "52.72664141654968 113000\n",
      "SAVE!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!\n",
      "wait for Boy Meets Boy\n",
      "wait for DISCOVER ITALY\n",
      "wait for The Kings and Queens of England and Scotland\n",
      "wait for Days of the Blackbird: A Tale of Northern Italy\n",
      "74.28331708908081 113100\n",
      "wait for The House of the 99 Ghosts. (Ab 12 J.).\n",
      "wait for A Distant Presence: The Story Behind Paul's Letter to the Philippians (Narrative Commentary Series)\n",
      "wait for Norris McWhirter's Book of Historical Records\n",
      "63.15945887565613 113200\n",
      "wait for Mad Scientists' Club\n",
      "wait for The windows of time frozen: And other stories\n",
      "52.724791288375854 113300\n",
      "wait for I'm Really Dragged but Nothing Gets Me Down\n",
      "wait for Scottish Surnames\n",
      "wait for Pennsylvania State Parks: A Complete Outdoor Recreation Guide for Campers, Boaters, Anglers, Hikers and Outdoor Lovers (State Park Guidebooks)\n",
      "wait for Bible Readers Companion (Home Bible Study Library)\n",
      "wait for Bible Readers Companion (Home Bible Study Library)\n",
      "83.66170001029968 113400\n",
      "wait for Creole Caress\n",
      "wait for Pocket PC Starterpak for Dummies\n",
      "55.15908908843994 113500\n",
      "wait for Yuletide Match (Signet Regency Romance)\n",
      "wait for Man Woman and Child\n",
      "wait for Straight on Til' Morning\n",
      "62.84996247291565 113600\n",
      "wait for Das Spiegelhaus.\n",
      "wait for The Officer's Ward\n",
      "wait for Roosevelt's Secret War: FDR and World War II Espionage\n",
      "wait for Over Dose: The Case Against the Drug Companies: Prescription Drugs, Side Effects, and Your Health\n",
      "wait for For Kings and Planets: A Novel\n",
      "wait for La casa de la laguna\n",
      "94.44216203689575 113700\n",
      "wait for Thursday's Child\n",
      "wait for Radio Romance\n",
      "wait for The Ramses 3: The Battle of Kadesh (Ramses)\n",
      "62.84983491897583 113800\n",
      "wait for Rocks and Minerals (Field Guide and Introduction to the Geology and Chemistry of)\n",
      "wait for Blaze : The Forensics of Fire\n",
      "53.221259117126465 113900\n",
      "wait for Driven to Distraction/Recognizing and Coping With Attention Deficit Disorder from Childhood Through Adulthood\n",
      "wait for Guenevere 2: The Knight of the Sacred Lake (Guenevere)\n",
      "wait for Piano Lessons: Music, Love, & True Adventures\n",
      "wait for Piano Lessons: Music, Love, & True Adventures\n",
      "wait for Jenny\n",
      "wait for Shifra Stein's Day Trips from San Antonio and Austin: Getaways Less Than Two Hours Away (3rd ed)\n",
      "wait for The Missing Chapter: A Nero Wolfe Mystery\n",
      "wait for Drug and Alcohol Abuse: The Authoritative Guide for Parents, Teachers, and Counselors (The Language of Science)\n",
      "114.34325408935547 114000\n",
      "SAVE!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!\n",
      "wait for Action! : A Novel\n",
      "wait for Fiske Guide to Getting into the Right College, The : The Complete Guide to Everything You Need to Know to Getting into and Pay ing for College (1997)\n",
      "wait for ADD: Helping Your Child : Untying the Knot of Attention Deficit Disorder\n",
      "wait for Me too\n",
      "76.6584324836731 114100\n",
      "wait for Eating for Good Health (Health and Healing the Natural Way)\n",
      "wait for Resurrection Man: A Novel\n",
      "wait for Arigo: surgeon of the rusty knife,\n",
      "wait for Quantum Leap: Knights of the Morningstar (Quantum Leap)\n",
      "73.11513710021973 114200\n",
      "wait for Diana's Boys: William and Harry and the Mother They Loved\n",
      "wait for Stolen Pony\n",
      "wait for Madonna: Unauthorized\n",
      "wait for Madonna: Unauthorized\n",
      "73.56206917762756 114300\n",
      "wait for Save the Mustangs How a Federal Law Is Passed\n",
      "wait for Chronicles of Avonlea\n",
      "wait for Dark Shadows : Angélique's Descent\n",
      "wait for Dark Shadows : Angélique's Descent\n",
      "wait for The Collected Stories of Peter Taylor (Contemporary American Fiction)\n",
      "83.36138892173767 114400\n",
      "wait for Alan and the Animal Kingdom\n",
      "wait for Trixie Belden and the Mystery Off Old Telegraph Road (Mystery Off Old Telegraph Road)\n",
      "wait for Kalte Glut. ( Rache ist ein s�¼��es Wort). Roman.\n",
      "64.32164335250854 114500\n",
      "wait for Irrungen, Wirrungen\n",
      "wait for Irrungen, Wirrungen\n",
      "53.11808133125305 114600\n",
      "wait for Der Weg zum Erfolg. Ein ersch�¶pfender Ratgeber in Wort und Bild.\n",
      "wait for IANUA NOVA. Ausgabe B. Kurzausgabe I/ II. Lehrgang f�¼r Latein als 2. oder 3. Fremdsprache. (Lernmaterialien)\n",
      "wait for IANUA NOVA. Ausgabe B. Kurzausgabe I/ II. Lehrgang f�¼r Latein als 2. oder 3. Fremdsprache. (Lernmaterialien)\n",
      "wait for Stranger at Winfield House\n",
      "wait for Another Chance at Heaven\n",
      "83.5730333328247 114700\n",
      "wait for Golden Fire (Heartfire)\n",
      "wait for Harlequin 50th Anniversary Collection #4 (Private Eye; Beguiled)\n",
      "wait for Stop Getting Dumped! All You Need to Know to Make Men Fall Madly in Love with You and Marry \\The One\\\" in 3 Years or Less\"\n",
      "63.96989393234253 114800\n",
      "wait for Prayers (Puffy Books)\n",
      "wait for Spanish Now!: A Level-One Worktext/El Espanol Actual! : Primer Programa\n",
      "wait for The New Oxford Annotated Bible with the Apocrypha\n",
      "wait for On Writer's Block\n",
      "73.66951632499695 114900\n",
      "wait for Tracer (Contemporary American Fiction)\n",
      "wait for A Primate's Memoir: A Neuroscientist's Unconventional Life Among the Baboons\n",
      "52.72058343887329 115000\n",
      "SAVE!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!\n",
      "wait for The Dream of a Common Language\n",
      "wait for Paraph of Bone & Other Kinds of Blue\n",
      "wait for The Beautiful Dead End\n",
      "wait for Trails of Fredericton (Trails of the Cities)\n",
      "75.69890332221985 115100\n",
      "wait for Das Todesjahr des Ricardo Reis.\n",
      "wait for Last Year's River: A Novel\n",
      "54.54151272773743 115200\n",
      "wait for INCREDIBLE JOURNEY\n",
      "wait for Bandits\n",
      "52.954331398010254 115300\n",
      "wait for Well-Schooled in Murder\n",
      "wait for Descent from Glory: Four Generations of the John Adams Family\n",
      "wait for Love in the Time of Cholera (General Series)\n",
      "wait for The Grouchy Grammarian: A How-Not-To Guide to the 47 Most Common Mistakes in English Made by Journalists, Broadcasters, and Others Who Should Know Better\n",
      "wait for Soros: The Life and Times of a Messianic Billionaire\n",
      "wait for The Bedford Guide for College Writers: With Reader, Research Manual, and Handbook\n",
      "93.01298069953918 115400\n",
      "wait for El Coronel No Tiene Quien Le Escriba\n",
      "wait for El Coronel No Tiene Quien Le Escriba\n",
      "wait for Last Romantics\n",
      "wait for Beyond the Sky and the Earth: A Journey into Bhutan\n",
      "73.56092286109924 115500\n",
      "wait for The Jesus Mysteries: Was the \\Original Jesus\\\" a Pagan God?\"\n",
      "wait for PC Power: Microcomputer Applications\n",
      "wait for Abracadabra (Superromance/#473)\n",
      "wait for Work Yourself Happy\n",
      "wait for The FUN BOOK : 102 WAYS FOR GIRLS TO HAVE SOME\n",
      "83.09724068641663 115600\n",
      "wait for Scarlet Letter: A Case Study in Contemporary Criticism (Case Studies in Contemporary Criticism)\n",
      "wait for The Essence of Shaolin White Crane: Martial Power and Qigong\n",
      "wait for Deadly White Female\n",
      "63.7922043800354 115700\n",
      "wait for The Red Badge of Courage (Illustrated Classic Editions)\n",
      "wait for All the Days After Sunday (Serenade/Saga)\n",
      "53.07502722740173 115800\n",
      "wait for Who is Bugs Potter (R)\n",
      "wait for QPB Anthology of Women's Writing\n",
      "wait for Desire Lines\n",
      "64.04395389556885 115900\n",
      "wait for The World Treasury of Children's Literature: Book 3 (World Treasury of Children's Literature)\n",
      "wait for The Willow Tree: A Novel\n",
      "wait for Song of Isis\n",
      "wait for The Peanut Butter Murders\n",
      "wait for A Separate Creation: How Biology Makes Us Gay\n",
      "wait for A Haven in Winter\n",
      "93.25784516334534 116000\n",
      "SAVE!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!\n",
      "wait for Catfish Cafe (Thomas Black Mysteries)\n",
      "wait for The Amber Room\n",
      "wait for Summer Point\n",
      "64.62559604644775 116100\n",
      "wait for Dragondoom (Spectra Super Release)\n",
      "wait for Under the Mermaid Angel (Laurel-Leaf Books)\n",
      "53.34081840515137 116200\n",
      "wait for Sweet Silver Blues\n",
      "wait for In Celebration of Women\n",
      "wait for Ewan McGregor: The Unauthorized Biography\n",
      "wait for Late Clara Beame\n",
      "75.41041016578674 116300\n",
      "wait for HER FATHER'S SINS\n",
      "wait for La rupture pour vivre\n",
      "wait for Resolutions for the Millennium\n",
      "wait for Gemsicuted\n",
      "74.26844573020935 116400\n",
      "wait for The Original Alice: From Manuscript to Wonderland\n",
      "wait for Son of Man (Sf Collector's)\n",
      "wait for El tío Vania, La gaviota, El jardín de los cerezos\n",
      "63.25931119918823 116500\n",
      "wait for See No Evil\n",
      "wait for Why the Tree Loves the Ax: A Novel\n",
      "wait for Little Sister\n",
      "wait for Father Bear Comes Home (I Can Read Book 1)\n",
      "74.3994653224945 116600\n",
      "wait for Faeries: Issac Asimov's Magical Worlds of Fantasy\n",
      "wait for Cam Jansen and the Mystery at the Monkey House (Cam Jansen Adventure, 10)\n",
      "wait for Love and the Single Corpse (Hannah and Kiki Mysteries)\n",
      "63.98638892173767 116700\n",
      "wait for Murder On the Orient Express\n",
      "wait for Who Killed Dorian Gray? (Claire Rawlings Mysteries)\n",
      "wait for Things That Go Bump! (Bobby's World)\n",
      "wait for Torchlight to Valhalla\n",
      "73.06600666046143 116800\n",
      "wait for Ford Tempo Mercury Topaz 1984-94 (Automobile Repair Manuals Series)\n",
      "wait for Black Blade\n",
      "wait for Black Blade\n",
      "wait for Geh, wohin dein Herz dich tr�¤gt.\n",
      "wait for Barney Fife's Guide to Life Love and Self-Defense\n",
      "wait for Gewagtes Spiel.\n",
      "wait for Looking High & Low for One Lost Sheep (Tales from the Bible)\n",
      "108.19068789482117 116900\n",
      "wait for Ultimo desembarco ; Vente a Sinapia (Literatura)\n",
      "wait for Beneath the Texas Sky (Zebra Historical Romance S.)\n",
      "53.21939206123352 117000\n",
      "SAVE!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!\n",
      "wait for Angels Flight (Detective Harry Bosch Mysteries)\n",
      "wait for Hug a Teddy\n",
      "wait for The Impressionists Handbook\n",
      "wait for The Letters of J.R.R. Tolkien\n",
      "wait for Wit and Wisdom from the Peanut Butter Gang\n",
      "85.77871870994568 117100\n",
      "wait for Women On the Edge of Time\n",
      "44.47441554069519 117200\n",
      "wait for Murach's Beginning Java 2\n",
      "wait for Thundercats: Dogs of War (Thundercats)\n",
      "53.85667014122009 117300\n",
      "wait for Why Is The Foul Pole Fair? (Or, Answers to the Baseball Questions Your Dad Hoped You Wouldn't Ask)\n",
      "wait for Beginner's Guide to Geology\n",
      "53.66978716850281 117400\n",
      "wait for Hollywood\n",
      "wait for Superbook Dinosaurs (Superbooks)\n",
      "wait for Lost Futures\n",
      "wait for Lust for Blood: The Consuming Story of Vampires\n",
      "wait for Women of Darkness\n",
      "wait for Dusk\n",
      "wait for ZOIDS: Chaotic Century, Vol. 3\n",
      "wait for ZOIDS: Chaotic Century, Vol. 3\n",
      "wait for ZOIDS: Chaotic Century, Vol. 2\n",
      "wait for Baby Tyrannosaurus Rex\n",
      "134.0367455482483 117500\n",
      "wait for Comédias da vida privada: 101 crônicas escolhidas\n",
      "wait for The Origin of Humankind (Science Masters)\n",
      "wait for The Great Dinosaurs\n",
      "wait for Eight Little Piggies: Reflections in Natural History\n",
      "74.01521968841553 117600\n",
      "wait for Le 210e jour\n",
      "wait for The Natural History Museum at South Kensington\n",
      "wait for The World of Dinosaurs\n",
      "wait for La Fontaine p�©trifiante\n",
      "wait for Fossils and Evolution\n",
      "83.96478199958801 117700\n",
      "wait for Dinosaurs from China =\n",
      "45.07199740409851 117800\n",
      "wait for The Sound of Wings\n",
      "wait for Darwin (Past Masters S.)\n",
      "wait for Time Machines: Scientific Explorations in Deep Time\n",
      "wait for Collins guide to dinosaurs\n"
     ]
    }
   ],
   "source": [
    "start = time.time()\n",
    "for index,row in books.iloc[111800:].iterrows():\n",
    "    \n",
    "    if row[\"alt_author\"]==\"\":\n",
    "        \n",
    "        ISBN = row[\"ISBN\"]\n",
    "        ISBN = ISBN.replace('\\t','') # issue with control characters\n",
    "            \n",
    "        statement = True\n",
    "        \n",
    "        while statement:\n",
    "            try:\n",
    "                authors, title = get_author_title(ISBN)\n",
    "                statement = False\n",
    "            except (IndexError,KeyError) as e:\n",
    "                authors, title = '', ''\n",
    "                statement = False\n",
    "            except HTTPError as e: \n",
    "                print('wait for', row.title)\n",
    "                time.sleep(10)\n",
    "            \n",
    "    if type(authors)==list:\n",
    "        authors = \"|\".join(authors)\n",
    "        books.at[index, \"alt_title\"] = title\n",
    "        books.at[index, \"alt_author\"] = authors\n",
    "        \n",
    "    if index%100==0:\n",
    "        print(time.time()-start, index)\n",
    "        start = time.time()\n",
    "        if index%1000==0:\n",
    "            print(\"SAVE!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!\")\n",
    "            books.to_csv(location_to_save+\"items_books_some_isbn.csv\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "7b4b3133",
   "metadata": {},
   "source": [
    "D. Save file."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "ea847b27",
   "metadata": {},
   "outputs": [],
   "source": [
    "books.to_csv(location_to_save+\"items_books_some_isbn.csv\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "332ba26a",
   "metadata": {},
   "source": [
    "## 5. Analyze results from Google Books API\n",
    "<b>Goal</b>: To check the structure and completeness of the Google Books information. Also, to compare the author name received by Google Books to the author name already existing in the dataset."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "6086fabf",
   "metadata": {},
   "source": [
    "A. Read file."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "id": "bd2f04d7",
   "metadata": {},
   "outputs": [],
   "source": [
    "books = pd.read_csv(location_to_save + \"items_books_some_isbn.csv\", low_memory = False, index_col=0).fillna(\"\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "07dc3f09",
   "metadata": {},
   "source": [
    "B. Check completeness."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "id": "e3246f76",
   "metadata": {},
   "outputs": [],
   "source": [
    "no_results_authors = len(books[(books.alt_author==\"\")].drop_duplicates(subset = \"author\"))\n",
    "no_results_books = len(books[(books.alt_author==\"\")])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "id": "4a6c7036",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "For 6511 unique authors, we couldn't match the ISBN of at least one of their books, out of 102024 authors.\n",
      "For 10134 books, we couldn't match the ISBN, out of 271360 books.\n"
     ]
    }
   ],
   "source": [
    "print(\"For\", no_results_authors,\"unique authors, we couldn't match the ISBN of at least one of their books, out of\",len(books.drop_duplicates(subset = \"author\")), \"authors.\")\n",
    "print(\"For\", no_results_books,\"books, we couldn't match the ISBN, out of\", len(books), \"books.\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "77456da8",
   "metadata": {},
   "source": [
    "C. Compare Book-Crossing with Google Books. We aim to see whether the results match the actual information on the dataset, in particular in terms of author name."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "248e45d2",
   "metadata": {},
   "source": [
    "C.1 First estimation: Naive, which means directly comparing the strings of \"author\" and \"alt_author\"."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "id": "1ad3f09b",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "At first glance, we didn't find the same author for 92052 books out of  271360. However, it needs more processing.\n"
     ]
    }
   ],
   "source": [
    "first_estimation = len(books[books.author!=books.alt_author])\n",
    "print(\"At first glance, we didn't find the same author for\", first_estimation,\"books out of \",str(len(books))+\". However, it needs more processing.\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "297c6ec8",
   "metadata": {},
   "source": [
    "C.2 Second estimation: Simplify author names.\n",
    "\n",
    "We need a function that:\n",
    "1. Removes spaces\n",
    "2. Removes punctuation\n",
    "3. Makes lowercase\n",
    "\n",
    "<b>This way, we can more properly compare the strings.</b>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 251,
   "id": "cf59e59a",
   "metadata": {},
   "outputs": [],
   "source": [
    "def simplify(name):\n",
    "    name = name.replace(\" \",\"\").translate(str.maketrans('', '', string.punctuation)).lower()\n",
    "    return name"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "id": "c3b1c142",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'savvinagdaniil'"
      ]
     },
     "execution_count": 32,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "simplify(\"Savvina G. Daniil\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "6e14d5ba",
   "metadata": {},
   "source": [
    "We create a column called \"correct_author\" where we replace it with the alt_author if simplified it is the same as the book crossing author. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "id": "84ef3dfc",
   "metadata": {},
   "outputs": [],
   "source": [
    "books[\"correct_author\"] = ''\n",
    "for index,row in books.iterrows():\n",
    "    if row.alt_author!=\"\":\n",
    "        if simplify(row.author)==simplify(row.alt_author):\n",
    "            books.at[index, \"correct_author\"] = row.alt_author"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "id": "3c8e7b16",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "With the simplifying, we didn't find the same author for 81731 books out of  271360. However, it needs even more processing.\n"
     ]
    }
   ],
   "source": [
    "second_estimation = len(books[books.correct_author==\"\"])\n",
    "print(\"With the simplifying, we didn't find the same author for\", second_estimation,\"books out of \",str(len(books))+\". However, it needs even more processing.\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "25d5526b",
   "metadata": {},
   "source": [
    "C.3 Third estimation: Account for multiple authors."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "2848c384",
   "metadata": {},
   "source": [
    "Since in alt_author we collect all authors, we make sure that we consider if any of them (separated by \"|\") is the same as author."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "id": "3e7a443e",
   "metadata": {},
   "outputs": [],
   "source": [
    "for index, row in books.iterrows():\n",
    "    if row.correct_author==\"\": # only if we haven't already found the correct author of that book\n",
    "        if row.alt_author!=\"\": # if we got ISBN result\n",
    "            alt_author = row.alt_author\n",
    "            bag_of_alt_authors = alt_author.split(\"|\")\n",
    "            simplified_bag_of_alt_authors = [simplify(x) for x in bag_of_alt_authors]\n",
    "            author = simplify(row.author)\n",
    "            for i in range(len(bag_of_alt_authors)):\n",
    "                if author==simplified_bag_of_alt_authors[i]:\n",
    "                    books.at[index, \"correct_author\"] = bag_of_alt_authors[i]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "id": "a0a609b6",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "With accounting for bag of authors, we didn't find the same author for 50833 books out of  271360. However, it needs even more processing.\n"
     ]
    }
   ],
   "source": [
    "third_estimation = len(books[books.correct_author==\"\"])\n",
    "print(\"With accounting for bag of authors, we didn't find the same author for\", third_estimation,\"books out of \",str(len(books))+\". However, it needs even more processing.\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "d293c26b",
   "metadata": {},
   "source": [
    "C.4 Fourth estimation: Reverse names."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "969e0ba1",
   "metadata": {},
   "source": [
    "We noticed that some names are reversed, e.g. Charles Glass and Glass Charles are not considered the same name by our processing. So we add an extra layer: sort the characters of the name. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "id": "caee586a",
   "metadata": {},
   "outputs": [],
   "source": [
    "for index, row in books.iterrows():\n",
    "    if row.correct_author==\"\": # only if we haven't already found the correct author of that book\n",
    "        alt_author = row.alt_author\n",
    "        bag_of_alt_authors = alt_author.split(\"|\")\n",
    "        simplified_bag_of_alt_authors = [sorted(simplify(x)) for x in bag_of_alt_authors]\n",
    "        author = sorted(simplify(row.author))\n",
    "        for i in range(len(bag_of_alt_authors)):\n",
    "            if author==simplified_bag_of_alt_authors[i]:\n",
    "                books.at[index, \"correct_author\"] = bag_of_alt_authors[i]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "id": "3a4f5931",
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "With the sorting trick, we didn't find the same author for 49731 books out of  271360. However, it needs even more processing.\n"
     ]
    }
   ],
   "source": [
    "fourth_estimation = len(books[books.correct_author==\"\"])\n",
    "print(\"With the sorting trick, we didn't find the same author for\", fourth_estimation,\"books out of \",str(len(books))+\". However, it needs even more processing.\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c6e7f6e5",
   "metadata": {},
   "source": [
    "C.5 Fifth estimation: String similarity. "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "1e30d13a",
   "metadata": {},
   "source": [
    "At this point, we can no longer rely on simply the literal comparison between strings. We need to introduce a similarity measure between them."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "id": "e1ad1b9e",
   "metadata": {},
   "outputs": [],
   "source": [
    "def similarity(a, b):\n",
    "    return SequenceMatcher(None, a, b).ratio()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "980fda39-1ea4-46b4-a4cc-474561b1bfcc",
   "metadata": {},
   "source": [
    "check hamming distance"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 54,
   "id": "4a4b9f04",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(0.9032258064516129, 0.2962962962962963)"
      ]
     },
     "execution_count": 54,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "similarity(\"Savvina Daniil\", \"Savvina G. Daniil\"), similarity(\"Savvina Daniil\", \"John Williams\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "54389789-07fb-495d-b2af-58bbd8328ecb",
   "metadata": {},
   "source": [
    "Idea: guess the last name of the alt_author, and see if it appears in the fairbook dataset."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ac758b49",
   "metadata": {},
   "source": [
    "We consider every similarity over 0.7 to be the same name."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "id": "9bf0998c",
   "metadata": {},
   "outputs": [],
   "source": [
    "for index, row in books.iterrows():\n",
    "    if row.correct_author==\"\": # only if we haven't already found the correct author of that book\n",
    "        alt_author = row.alt_author\n",
    "        bag_of_alt_authors = alt_author.split(\"|\")\n",
    "        simplified_bag_of_alt_authors = [sorted(simplify(x)) for x in bag_of_alt_authors]\n",
    "        author = sorted(simplify(row.author))\n",
    "        \n",
    "        max_sim = 0\n",
    "        max_i = -1000\n",
    "        \n",
    "        for i in range(len(bag_of_alt_authors)):\n",
    "            sim = similarity(author, simplified_bag_of_alt_authors[i])\n",
    "            if sim>max_sim:\n",
    "                max_sim = sim\n",
    "                max_i = i\n",
    "        \n",
    "        if max_sim>=0.7:\n",
    "            books.at[index, \"correct_author\"] = bag_of_alt_authors[max_i]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "id": "8b9a0df0",
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "With the similarity threshold, we didn't find the same author for 22019 books out of  271360. At this point, it is hard to further progress.\n"
     ]
    }
   ],
   "source": [
    "fifth_estimation = len(books[books.correct_author==\"\"])\n",
    "print(\"With the similarity threshold, we didn't find the same author for\", fifth_estimation,\"books out of \",str(len(books))+\". At this point, it is hard to further progress.\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "id": "3546e1fa",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "FINAL SUM UP\n",
      "Books:                           | 271360\n",
      "Books with ISBN results:         | 261226\n",
      "Books with matching ISBN results:| 249341\n"
     ]
    }
   ],
   "source": [
    "print(\"FINAL SUM UP\")\n",
    "print(\"Books:                           |\", len(books))\n",
    "print(\"Books with ISBN results:         |\", len(books[books.alt_author!=\"\"]))\n",
    "print(\"Books with matching ISBN results:|\", len(books[books.correct_author!=\"\"]))"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "e9b791f1",
   "metadata": {},
   "source": [
    "C.6 Save first author from GB. \n",
    "We may need to use them when running the viaf API."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "id": "ceeca8b2",
   "metadata": {},
   "outputs": [],
   "source": [
    "books[\"alt_first_author\"] = books.alt_author.apply(lambda x: x.split(\"|\")[0])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "id": "6c991b76",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>ISBN</th>\n",
       "      <th>title</th>\n",
       "      <th>author</th>\n",
       "      <th>year</th>\n",
       "      <th>publisher</th>\n",
       "      <th>alt_title</th>\n",
       "      <th>alt_author</th>\n",
       "      <th>correct_author</th>\n",
       "      <th>alt_first_author</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0195153448</td>\n",
       "      <td>Classical Mythology</td>\n",
       "      <td>Mark P. O. Morford</td>\n",
       "      <td>2002</td>\n",
       "      <td>Oxford University Press</td>\n",
       "      <td>Classical Mythology</td>\n",
       "      <td>Mark P. O. Morford|Robert J. Lenardon</td>\n",
       "      <td>Mark P. O. Morford</td>\n",
       "      <td>Mark P. O. Morford</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>0002005018</td>\n",
       "      <td>Clara Callan</td>\n",
       "      <td>Richard Bruce Wright</td>\n",
       "      <td>2001</td>\n",
       "      <td>HarperFlamingo Canada</td>\n",
       "      <td>Clara Callan</td>\n",
       "      <td>Richard Bruce Wright</td>\n",
       "      <td>Richard Bruce Wright</td>\n",
       "      <td>Richard Bruce Wright</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>0060973129</td>\n",
       "      <td>Decision in Normandy</td>\n",
       "      <td>Carlo D'Este</td>\n",
       "      <td>1991</td>\n",
       "      <td>HarperPerennial</td>\n",
       "      <td>Decision in Normandy</td>\n",
       "      <td>Carlo D'Este</td>\n",
       "      <td>Carlo D'Este</td>\n",
       "      <td>Carlo D'Este</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>0374157065</td>\n",
       "      <td>Flu: The Story of the Great Influenza Pandemic...</td>\n",
       "      <td>Gina Bari Kolata</td>\n",
       "      <td>1999</td>\n",
       "      <td>Farrar Straus Giroux</td>\n",
       "      <td>Flu</td>\n",
       "      <td>Gina Bari Kolata</td>\n",
       "      <td>Gina Bari Kolata</td>\n",
       "      <td>Gina Bari Kolata</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>0393045218</td>\n",
       "      <td>The Mummies of Urumchi</td>\n",
       "      <td>E. J. W. Barber</td>\n",
       "      <td>1999</td>\n",
       "      <td>W. W. Norton &amp;amp; Company</td>\n",
       "      <td>The Mummies of Ürümchi</td>\n",
       "      <td>E. J. W. Barber</td>\n",
       "      <td>E. J. W. Barber</td>\n",
       "      <td>E. J. W. Barber</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         ISBN                                              title  \\\n",
       "0  0195153448                                Classical Mythology   \n",
       "1  0002005018                                       Clara Callan   \n",
       "2  0060973129                               Decision in Normandy   \n",
       "3  0374157065  Flu: The Story of the Great Influenza Pandemic...   \n",
       "4  0393045218                             The Mummies of Urumchi   \n",
       "\n",
       "                 author  year                   publisher  \\\n",
       "0    Mark P. O. Morford  2002     Oxford University Press   \n",
       "1  Richard Bruce Wright  2001       HarperFlamingo Canada   \n",
       "2          Carlo D'Este  1991             HarperPerennial   \n",
       "3      Gina Bari Kolata  1999        Farrar Straus Giroux   \n",
       "4       E. J. W. Barber  1999  W. W. Norton &amp; Company   \n",
       "\n",
       "                alt_title                             alt_author  \\\n",
       "0     Classical Mythology  Mark P. O. Morford|Robert J. Lenardon   \n",
       "1            Clara Callan                   Richard Bruce Wright   \n",
       "2    Decision in Normandy                           Carlo D'Este   \n",
       "3                     Flu                       Gina Bari Kolata   \n",
       "4  The Mummies of Ürümchi                        E. J. W. Barber   \n",
       "\n",
       "         correct_author      alt_first_author  \n",
       "0    Mark P. O. Morford    Mark P. O. Morford  \n",
       "1  Richard Bruce Wright  Richard Bruce Wright  \n",
       "2          Carlo D'Este          Carlo D'Este  \n",
       "3      Gina Bari Kolata      Gina Bari Kolata  \n",
       "4       E. J. W. Barber       E. J. W. Barber  "
      ]
     },
     "execution_count": 45,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "books.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "4e1fcac7",
   "metadata": {},
   "source": [
    "C.7 Save file"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 46,
   "id": "4597ee8e",
   "metadata": {},
   "outputs": [],
   "source": [
    "books.to_csv(location_to_save+\"items_books_some_isbn.csv\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "3dd92510",
   "metadata": {},
   "source": [
    "## 6. Access VIAF API\n",
    "<b>Goal</b>: To run \"correct_author\" if it exists to the VIAF(https://viaf.org/) API, so as to get a VIAF id. \n",
    "\n",
    "Note: For now, we are only running it for the authors that have a \"correct_author\" name, i.e., the ones for whom their BC name matched their GB name based on the previous analysis."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "6c09ad15-6b28-433d-a61a-0bfd34673d16",
   "metadata": {},
   "source": [
    "Try to use pathlib instead"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "id": "63d5efcb",
   "metadata": {},
   "outputs": [],
   "source": [
    "books = pd.read_csv(location_to_save+\"items_books_some_isbn.csv\", low_memory = False, index_col=0).fillna(\"\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "d25da0b5",
   "metadata": {},
   "source": [
    "A. Function to access the API."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "id": "94e0e2e1",
   "metadata": {},
   "outputs": [],
   "source": [
    "def get_viaf_entry(author_name):\n",
    "    #print(author_name)\n",
    "    viaf = ViafAPI()\n",
    "    viaf_id = \"\"\n",
    "    viaf_name = \"\"\n",
    "    potential_entries = viaf.suggest(author_name)[:5]\n",
    "    if len(potential_entries)==0:\n",
    "        print(\"No results!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!\")\n",
    "    for entry in potential_entries:\n",
    "        if entry[\"nametype\"]==\"personal\":\n",
    "            #print(entry[\"term\"])\n",
    "            try:\n",
    "                viaf_id = entry[\"viafid\"]\n",
    "                viaf_name = entry[\"term\"]\n",
    "                #print(\"We found viaf_id!\")\n",
    "            except: # put keyerror, don't just call them ''\n",
    "                print(author_name + \" has no Viaf ID.\")\n",
    "            break # look at this again!!!\n",
    "        print(\"ISSUE! We didn't find for\", author_name)\n",
    "    return viaf_id, viaf_name"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a8913bfe",
   "metadata": {},
   "source": [
    "B. Initialize the entries."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 55,
   "id": "c7470e08",
   "metadata": {},
   "outputs": [],
   "source": [
    "books[\"viaf_id\"] = \"\"\n",
    "books[\"viaf_name\"] = \"\""
   ]
  },
  {
   "cell_type": "markdown",
   "id": "655f287a",
   "metadata": {},
   "source": [
    "C. Define unique authors.\n",
    "\n",
    "The purpose is to not rerun the API again for the same \"correct author\" name."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 49,
   "id": "7de0d946",
   "metadata": {},
   "outputs": [],
   "source": [
    "definitive_unique_authors = books[books.correct_author!=\"\"].drop_duplicates(subset=\"correct_author\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "4b879e11",
   "metadata": {},
   "source": [
    "D. Run the requests."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "5b928eed",
   "metadata": {},
   "outputs": [],
   "source": [
    "start = time.time()\n",
    "i=0\n",
    "for index, row in definitive_unique_authors.iloc[86000:].iterrows():\n",
    "    if row.viaf_id==\"\":\n",
    "        author = row.correct_author\n",
    "        viaf_id, viaf_name = get_viaf_entry(author)\n",
    "        if viaf_id == \"\":\n",
    "            print(\"try normal author name\")\n",
    "            viaf_id, viaf_name = get_viaf_entry(row.author)\n",
    "        books.at[index, \"viaf_id\"] = viaf_id\n",
    "        books.at[index, \"viaf_name\"] = viaf_name\n",
    "        definitive_unique_authors.at[index, \"viaf_id\"] = viaf_id\n",
    "        definitive_unique_authors.at[index, \"viaf_name\"] = viaf_name\n",
    "    if i%500==0:\n",
    "        print(index, time.time()-start)\n",
    "        books.to_csv(location_to_save+\"items_books_after_viaf.csv\")\n",
    "    i+=1"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "22f62004",
   "metadata": {},
   "source": [
    "E. Save file."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 75,
   "id": "97dd2e70",
   "metadata": {},
   "outputs": [],
   "source": [
    "books.to_csv(location_to_save+\"items_books_after_viaf.csv\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "67614a12",
   "metadata": {},
   "source": [
    "## 7. Complete the VIAF enriched dataset\n",
    "<b>Goal</b>: Given that we dropped duplicates for \"correct_author\", we now need to fill the entire books dataset with the VIAF ids we got."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a7b19e57",
   "metadata": {},
   "source": [
    "A. Read file."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 50,
   "id": "f846304f",
   "metadata": {},
   "outputs": [],
   "source": [
    "books = pd.read_csv(location_to_save+\"items_books_after_viaf.csv\", dtype = object, index_col=0).fillna(\"\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "826819d4",
   "metadata": {},
   "source": [
    "B. Keep books with matching ISBN results and then authors with viaf results."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 164,
   "id": "3c96a6f2",
   "metadata": {},
   "outputs": [],
   "source": [
    "books_with_matching_ISBN_results = books[books.correct_author!=\"\"]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 165,
   "id": "af2153f5",
   "metadata": {},
   "outputs": [],
   "source": [
    "authors_with_viaf = books_with_matching_ISBN_results[[\"correct_author\", \"viaf_id\", \"viaf_name\"]].loc[books_with_matching_ISBN_results.viaf_id!=\"\"]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 166,
   "id": "f1f18f16",
   "metadata": {},
   "outputs": [],
   "source": [
    "books_with_matching_ISBN_results = books_with_matching_ISBN_results.drop([\"viaf_id\", \"viaf_name\"], axis =1)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "56fef462",
   "metadata": {},
   "source": [
    "C. Keep books with mathcing ISBN results and viaf results."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 167,
   "id": "605059fc",
   "metadata": {},
   "outputs": [],
   "source": [
    "books_with_matching_ISBN_results_and_viaf = pd.merge(books_with_matching_ISBN_results.reset_index(), authors_with_viaf, on=\"correct_author\").set_index(\"index\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "642339e4",
   "metadata": {},
   "source": [
    "D. Fill the books dataset with these results."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 168,
   "id": "2887c801",
   "metadata": {},
   "outputs": [],
   "source": [
    "books.loc[books_with_matching_ISBN_results_and_viaf.index] = books_with_matching_ISBN_results_and_viaf"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f94ecb7c",
   "metadata": {},
   "source": [
    "E. Sum up."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 183,
   "id": "c66b06d4",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "FINAL SUM UP                              | Found  | Remaining\n",
      "------------------------------------------|--------|----------\n",
      "Books:                                    | 271360 | 0\n",
      "Books with ISBN results:                  | 261226 | 10134\n",
      "Books with matching ISBN results:         | 249341 | 22019\n",
      "Books with matching ISBN and VIAF results:| 244457 | 26903\n"
     ]
    }
   ],
   "source": [
    "print(\"FINAL SUM UP                              | Found  | Remaining\")\n",
    "print(\"------------------------------------------|--------|----------\")\n",
    "print(\"Books:                                    |\", len(books),\"|\", len(books) - len(books))\n",
    "print(\"Books with ISBN results:                  |\", len(books[books.alt_author!=\"\"]),\"|\", len(books[books.alt_author==\"\"]))\n",
    "print(\"Books with matching ISBN results:         |\", len(books[books.correct_author!=\"\"]),\"|\", len(books[books.correct_author==\"\"]))\n",
    "print(\"Books with matching ISBN and VIAF results:|\", len(books[books.viaf_id!=\"\"]),\"|\", len(books[books.viaf_id==\"\"]))"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a2f6afc0",
   "metadata": {},
   "source": [
    "F. Save file."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 170,
   "id": "19688f3b",
   "metadata": {},
   "outputs": [],
   "source": [
    "books.to_csv(location_to_save+\"items_books_after_viaf.csv\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "d5f034d2",
   "metadata": {},
   "source": [
    "## 8. Rerun VIAF API for books without matching ISBN results\n",
    "There are ~5K books with matcing authors for whom we didn't find VIAF id. However, we didn't try books without matching authors, or even any Google Books results at all.\n",
    "\n",
    "<b>Goal</b>: To run again the VIAF API, this time for the remaining authors (i.e., those for whom we don't have the \"correct_author\" name)."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "bd393b54",
   "metadata": {},
   "source": [
    "A. Read file."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "172f816d",
   "metadata": {},
   "outputs": [],
   "source": [
    "books = pd.read_csv(location_to_save+\"items_books_after_viaf.csv\", dtype = object, index_col=0).fillna(\"\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "65295c2f",
   "metadata": {},
   "source": [
    "B. Define authors to run: unique in terms of combination of author and GB first author."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "9af82208",
   "metadata": {},
   "outputs": [],
   "source": [
    "unique_unfilled_authors = books[books.correct_author==\"\"].drop_duplicates([\"author\", \"alt_first_author\"])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "id": "a88d6e0a",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "There are 15732 unique authors for whom we didn't run the VIAF API.\n"
     ]
    }
   ],
   "source": [
    "print(\"There are\",len(unique_unfilled_authors), \"unique authors for whom we didn't run the VIAF API.\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "97907e8a",
   "metadata": {},
   "source": [
    "C. Run VIAF again."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "2f5fe8df",
   "metadata": {},
   "outputs": [],
   "source": [
    "start = time.time()\n",
    "i=0\n",
    "for index, row in unique_unfilled_authors.iterrows():\n",
    "    if books.at[index,\"viaf_id\"]==\"\":\n",
    "        author = row.author\n",
    "        alt_first_author = row.alt_first_author\n",
    "        viaf_id_author, viaf_name_author = get_viaf_entry(author)\n",
    "        if alt_first_author!=\"\":\n",
    "            viaf_id_alt_first_author, viaf_name_alt_first_author = get_viaf_entry(alt_first_author)\n",
    "        else:\n",
    "            viaf_id_alt_first_author, viaf_name_alt_first_author = \"\", \"\"\n",
    "        books.at[index, \"viaf_id\"] = viaf_id_author+\"||\"+viaf_id_alt_first_author\n",
    "        books.at[index, \"viaf_name\"] = viaf_name_author+\"||\"+viaf_name_alt_first_author\n",
    "        unique_unfilled_authors.at[index, \"viaf_id\"] = viaf_id_author+\"||\"+viaf_id_alt_first_author\n",
    "        unique_unfilled_authors.at[index, \"viaf_name\"] = viaf_name_author+\"||\"+viaf_name_alt_first_author\n",
    "        if i%500==0:\n",
    "            print(index, time.time()-start)\n",
    "            books.to_csv(location_to_save+\"items_books_after_viaf.csv\")\n",
    "        i+=1"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "663e03ed",
   "metadata": {},
   "source": [
    "D. Save file."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 160,
   "id": "8c079892",
   "metadata": {},
   "outputs": [],
   "source": [
    "books.to_csv(location_to_save+\"items_books_after_viaf.csv\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "3f6e99ad",
   "metadata": {},
   "source": [
    "## 9. Complete VIAF enriched dataset (again)\n",
    "<b>Goal</b>: Given that we dropped duplicates for combination of \"author\" and \"alt_first_author\", we now need to fill the entire books dataset with the viaf ids we got."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "2b3b64f3",
   "metadata": {},
   "source": [
    "A. Read file."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 161,
   "id": "087c08f0",
   "metadata": {},
   "outputs": [],
   "source": [
    "books = pd.read_csv(location_to_save+\"items_books_after_viaf.csv\", dtype = object, index_col=0).fillna(\"\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "e350c309",
   "metadata": {},
   "source": [
    "B. Keep books without matching ISBN results and then authors with viaf results."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 162,
   "id": "951b7cb8",
   "metadata": {},
   "outputs": [],
   "source": [
    "books_without_matching_ISBN_results = books.loc[books.correct_author==\"\"]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 163,
   "id": "4ae60be8",
   "metadata": {},
   "outputs": [],
   "source": [
    "authors_with_viaf = books_without_matching_ISBN_results[[\"author\",\"alt_first_author\",\"viaf_id\",\"viaf_name\"]].loc[books.viaf_id!=\"\"]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 164,
   "id": "3f6082ce",
   "metadata": {},
   "outputs": [],
   "source": [
    "books_without_matching_ISBN_results = books_without_matching_ISBN_results.drop([\"viaf_id\",\"viaf_name\"], axis=1)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c57cf8b5",
   "metadata": {},
   "source": [
    "C. Keep books with mathcing ISBN results and viaf results."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 165,
   "id": "5e402ca2",
   "metadata": {},
   "outputs": [],
   "source": [
    "books_without_matching_ISBN_results_and_with_viaf = pd.merge(books_without_matching_ISBN_results.reset_index(), authors_with_viaf, on=[\"author\",\"alt_first_author\"]).set_index(\"index\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "284b7dae",
   "metadata": {},
   "source": [
    "D. Fill the books dataset with these results."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 166,
   "id": "c7c7aa65",
   "metadata": {},
   "outputs": [],
   "source": [
    "books.loc[books_without_matching_ISBN_results_and_with_viaf.index] = books_without_matching_ISBN_results_and_with_viaf"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "b2c28d4c",
   "metadata": {},
   "source": [
    "E. Sum up."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 167,
   "id": "d75b1953",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "FINAL SUM UP                                                       | Found  | Remaining\n",
      "-------------------------------------------------------------------|--------|----------\n",
      "Books:                                                             | 271360 | 0\n",
      "Books without matching ISBN results:                               | 22019  | 249341\n",
      "Books without matching ISBN results but with VIAF results:         | 20108  | 251252\n"
     ]
    }
   ],
   "source": [
    "print(\"FINAL SUM UP                                                       | Found  | Remaining\")\n",
    "print(\"-------------------------------------------------------------------|--------|----------\")\n",
    "print(\"Books:                                                             |\", len(books),\"|\", len(books) - len(books))\n",
    "print(\"Books without matching ISBN results:                               |\", len(books[books.correct_author==\"\"]),\" |\", len(books[books.correct_author!=\"\"]))\n",
    "print(\"Books without matching ISBN results but with VIAF results:         |\", len(books[(books.correct_author==\"\")&(books.viaf_id!=\"||\")]),\" |\", len(books)-len(books[(books.correct_author==\"\")&(books.viaf_id!=\"||\")]))"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "3e0f3b11",
   "metadata": {},
   "source": [
    "F. Save file."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 168,
   "id": "783edda2",
   "metadata": {},
   "outputs": [],
   "source": [
    "books.to_csv(location_to_save+\"items_books_after_viaf.csv\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "afc97565",
   "metadata": {},
   "source": [
    "## 10. Solve double VIAF ids.\n",
    "For the cases where there wasn't matching ISBN results, we ran both the BC author and the GB author in the VIAF API. This means that sometimes we got duplicated results for VIAF id.\n",
    "\n",
    "<b>Goal</b>: To resolve this issue by dropping a VIAF id when needed. "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "6637ab49",
   "metadata": {},
   "source": [
    "A. Read file."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "id": "7bc9c8fb",
   "metadata": {},
   "outputs": [],
   "source": [
    "books = pd.read_csv(location_to_save+\"items_books_after_viaf.csv\", dtype = object, index_col=0).fillna(\"\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "06bc8f67",
   "metadata": {},
   "source": [
    "B. Consider only books without matching ISBN results but with VIAF results (because this is the only case where we might have got duplicate ids)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "id": "0f6910bd",
   "metadata": {},
   "outputs": [],
   "source": [
    "relevant_books = books[(books.viaf_id.str.contains(\"||\",regex = False))&(books.viaf_id!=\"||\")].copy()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "4df07524",
   "metadata": {},
   "source": [
    "C. Consider both retrieved viaf results."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 271,
   "id": "6e53bacd",
   "metadata": {},
   "outputs": [],
   "source": [
    "relevant_books[\"viaf_id_1\"] = relevant_books.viaf_id.apply(lambda x: x.split(\"||\")[0])\n",
    "relevant_books[\"viaf_id_2\"] = relevant_books.viaf_id.apply(lambda x: x.split(\"||\")[-1])\n",
    "\n",
    "relevant_books[\"viaf_name_1\"] = relevant_books.viaf_name.apply(lambda x: x.split(\"||\")[0])\n",
    "relevant_books[\"viaf_name_2\"] = relevant_books.viaf_name.apply(lambda x: x.split(\"||\")[-1])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 272,
   "id": "3c303a8e",
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(20108, 271360)"
      ]
     },
     "execution_count": 272,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(relevant_books), len(books)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "eba76d03",
   "metadata": {},
   "source": [
    "D. Consider the following cases:\n",
    "1. If the viaf ids are the same, keep the common one.\n",
    "2. If one of the viaf ids is \"\", keep the other one."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 273,
   "id": "3d381b5c",
   "metadata": {},
   "outputs": [],
   "source": [
    "for index, row in relevant_books.iterrows():\n",
    "    viaf_id_1 = row.viaf_id_1\n",
    "    viaf_id_2 = row.viaf_id_2\n",
    "    if viaf_id_1 == viaf_id_2: # if they are the same\n",
    "        relevant_books.at[index, \"viaf_id\"] = viaf_id_1\n",
    "        relevant_books.at[index, \"viaf_name\"] = row.viaf_name_1\n",
    "    elif viaf_id_1 == \"\":\n",
    "        relevant_books.at[index, \"viaf_id\"] = viaf_id_2\n",
    "        relevant_books.at[index, \"viaf_name\"] = row.viaf_name_2\n",
    "    elif viaf_id_2 == \"\":\n",
    "        relevant_books.at[index, \"viaf_id\"] = viaf_id_1\n",
    "        relevant_books.at[index, \"viaf_name\"] = row.viaf_name_1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 274,
   "id": "0dcb2c7d",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "5403"
      ]
     },
     "execution_count": 274,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(relevant_books[(relevant_books.viaf_id.str.contains(\"||\",regex = False))])"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "30561464",
   "metadata": {},
   "source": [
    "So 5403 books yielded two different valid viaf results. "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f10d3cad",
   "metadata": {},
   "source": [
    "E. Consider the following cases:\n",
    "1. If the GB author name and the viaf name are the same, keep the GB author.\n",
    "2. In any other case, keep the BC author. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 275,
   "id": "1e11c664",
   "metadata": {},
   "outputs": [],
   "source": [
    "less_relevant_books = relevant_books[(relevant_books.viaf_id.str.contains(\"||\",regex = False))]"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "5259d8cc-c935-4c42-9a52-fb49b913c86d",
   "metadata": {},
   "source": [
    "maybe try the sequence matcher?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 276,
   "id": "52aaa29a",
   "metadata": {},
   "outputs": [],
   "source": [
    "for index, row in less_relevant_books.iterrows():\n",
    "    viaf_id_1 = row.viaf_id_1\n",
    "    viaf_id_2 = row.viaf_id_2\n",
    "    \n",
    "    viaf_name_1 = row.viaf_name_1\n",
    "    viaf_name_2 = row.viaf_name_2\n",
    "    \n",
    "    check_1 = (sorted(simplify(viaf_name_1.rsplit(',', 1)[0])) == sorted(simplify(row.author)))\n",
    "    check_2 = (sorted(simplify(viaf_name_2.rsplit(',', 1)[0])) == sorted(simplify(row.alt_first_author)))\n",
    "    \n",
    "    if (check_2 == True) & (check_1 == False):\n",
    "        less_relevant_books.at[index, \"viaf_id\"] = viaf_id_2\n",
    "        less_relevant_books.at[index, \"viaf_name\"] = row.viaf_name_2\n",
    "        \n",
    "    else:\n",
    "        less_relevant_books.at[index, \"viaf_id\"] = viaf_id_1\n",
    "        less_relevant_books.at[index, \"viaf_name\"] = row.viaf_name_1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 277,
   "id": "5fe2202b",
   "metadata": {},
   "outputs": [],
   "source": [
    "relevant_books.loc[less_relevant_books.index] = less_relevant_books"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "dd54cfa1",
   "metadata": {},
   "source": [
    "F. Replace the book entries with the updated."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 278,
   "id": "c91ec0bc",
   "metadata": {},
   "outputs": [],
   "source": [
    "books.loc[relevant_books.index] = relevant_books.drop([\"viaf_id_1\", \"viaf_id_2\", \"viaf_name_1\", \"viaf_name_2\"], axis=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 284,
   "id": "19cb1084",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Replace the || entries to reach a final dataset.\n",
    "books.viaf_id = books.viaf_id.apply(lambda x: \"\" if x == \"||\" else x)\n",
    "books.viaf_name = books.viaf_name.apply(lambda x: \"\" if x == \"||\" else x)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ab84f315",
   "metadata": {},
   "source": [
    "G. Save file."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 287,
   "id": "6524b62b",
   "metadata": {},
   "outputs": [],
   "source": [
    "books.to_csv(location_to_save+\"final_items_books.csv\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "6861340b",
   "metadata": {},
   "source": [
    "## 11. Match Book-Crossing with WikiData\n",
    "<b>Goal</b>: To finally match BC+ with WikiData. We will do that using the VIAF id we obtained at the previous steps."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a42eb62d",
   "metadata": {},
   "source": [
    "A. Read files."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 55,
   "id": "a0ff587f",
   "metadata": {},
   "outputs": [
    {
     "ename": "FileNotFoundError",
     "evalue": "[Errno 2] No such file or directory: 'data/final_files/items_with_viaf_wikidata.pkl'",
     "output_type": "error",
     "traceback": [
      "\u001b[0;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[0;31mFileNotFoundError\u001b[0m                         Traceback (most recent call last)",
      "Cell \u001b[0;32mIn[55], line 5\u001b[0m\n\u001b[1;32m      2\u001b[0m books \u001b[38;5;241m=\u001b[39m pd\u001b[38;5;241m.\u001b[39mread_csv(location_to_save\u001b[38;5;241m+\u001b[39m\u001b[38;5;124m\"\u001b[39m\u001b[38;5;124mfinal_items_books.csv\u001b[39m\u001b[38;5;124m\"\u001b[39m, dtype \u001b[38;5;241m=\u001b[39m \u001b[38;5;28mobject\u001b[39m, index_col\u001b[38;5;241m=\u001b[39m\u001b[38;5;241m0\u001b[39m)\u001b[38;5;241m.\u001b[39mfillna(\u001b[38;5;124m\"\u001b[39m\u001b[38;5;124m\"\u001b[39m)    \n\u001b[1;32m      4\u001b[0m \u001b[38;5;66;03m# WikiData\u001b[39;00m\n\u001b[0;32m----> 5\u001b[0m \u001b[38;5;28;01mwith\u001b[39;00m \u001b[38;5;28;43mopen\u001b[39;49m\u001b[43m(\u001b[49m\u001b[43mlocation_to_save\u001b[49m\u001b[43m \u001b[49m\u001b[38;5;241;43m+\u001b[39;49m\u001b[43m \u001b[49m\u001b[38;5;124;43m\"\u001b[39;49m\u001b[38;5;124;43mitems_with_viaf_wikidata.pkl\u001b[39;49m\u001b[38;5;124;43m\"\u001b[39;49m\u001b[43m,\u001b[49m\u001b[38;5;124;43m\"\u001b[39;49m\u001b[38;5;124;43mrb\u001b[39;49m\u001b[38;5;124;43m\"\u001b[39;49m\u001b[43m)\u001b[49m \u001b[38;5;28;01mas\u001b[39;00m f:\n\u001b[1;32m      6\u001b[0m     wd_results \u001b[38;5;241m=\u001b[39m  pkl\u001b[38;5;241m.\u001b[39mload(f)\n",
      "File \u001b[0;32m~/new_environment/conda/envs/enrich/lib/python3.8/site-packages/IPython/core/interactiveshell.py:284\u001b[0m, in \u001b[0;36m_modified_open\u001b[0;34m(file, *args, **kwargs)\u001b[0m\n\u001b[1;32m    277\u001b[0m \u001b[38;5;28;01mif\u001b[39;00m file \u001b[38;5;129;01min\u001b[39;00m {\u001b[38;5;241m0\u001b[39m, \u001b[38;5;241m1\u001b[39m, \u001b[38;5;241m2\u001b[39m}:\n\u001b[1;32m    278\u001b[0m     \u001b[38;5;28;01mraise\u001b[39;00m \u001b[38;5;167;01mValueError\u001b[39;00m(\n\u001b[1;32m    279\u001b[0m         \u001b[38;5;124mf\u001b[39m\u001b[38;5;124m\"\u001b[39m\u001b[38;5;124mIPython won\u001b[39m\u001b[38;5;124m'\u001b[39m\u001b[38;5;124mt let you open fd=\u001b[39m\u001b[38;5;132;01m{\u001b[39;00mfile\u001b[38;5;132;01m}\u001b[39;00m\u001b[38;5;124m by default \u001b[39m\u001b[38;5;124m\"\u001b[39m\n\u001b[1;32m    280\u001b[0m         \u001b[38;5;124m\"\u001b[39m\u001b[38;5;124mas it is likely to crash IPython. If you know what you are doing, \u001b[39m\u001b[38;5;124m\"\u001b[39m\n\u001b[1;32m    281\u001b[0m         \u001b[38;5;124m\"\u001b[39m\u001b[38;5;124myou can use builtins\u001b[39m\u001b[38;5;124m'\u001b[39m\u001b[38;5;124m open.\u001b[39m\u001b[38;5;124m\"\u001b[39m\n\u001b[1;32m    282\u001b[0m     )\n\u001b[0;32m--> 284\u001b[0m \u001b[38;5;28;01mreturn\u001b[39;00m \u001b[43mio_open\u001b[49m\u001b[43m(\u001b[49m\u001b[43mfile\u001b[49m\u001b[43m,\u001b[49m\u001b[43m \u001b[49m\u001b[38;5;241;43m*\u001b[39;49m\u001b[43margs\u001b[49m\u001b[43m,\u001b[49m\u001b[43m \u001b[49m\u001b[38;5;241;43m*\u001b[39;49m\u001b[38;5;241;43m*\u001b[39;49m\u001b[43mkwargs\u001b[49m\u001b[43m)\u001b[49m\n",
      "\u001b[0;31mFileNotFoundError\u001b[0m: [Errno 2] No such file or directory: 'data/final_files/items_with_viaf_wikidata.pkl'"
     ]
    }
   ],
   "source": [
    "# books-crossing + Google Books + VIAF\n",
    "books = pd.read_csv(location_to_save+\"final_items_books.csv\", dtype = object, index_col=0).fillna(\"\")    \n",
    "\n",
    "# WikiData\n",
    "with open(location_to_save + \"items_with_viaf_wikidata.pkl\",\"rb\") as f:\n",
    "    wd_results =  pkl.load(f)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "id": "a5036c89",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>QID</th>\n",
       "      <th>viaf_id</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Q270705</td>\n",
       "      <td>16565</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Q289693</td>\n",
       "      <td>135150789</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Q481146</td>\n",
       "      <td>14775085</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Q507746</td>\n",
       "      <td>6650</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Q568833</td>\n",
       "      <td>158264550</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       QID    viaf_id\n",
       "0  Q270705      16565\n",
       "1  Q289693  135150789\n",
       "2  Q481146   14775085\n",
       "3  Q507746       6650\n",
       "4  Q568833  158264550"
      ]
     },
     "execution_count": 26,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "wd_results.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "af4ce1e4",
   "metadata": {},
   "source": [
    "B. Merge datasets on VIAF id."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "id": "b3fd6000",
   "metadata": {},
   "outputs": [],
   "source": [
    "merged_dataset = pd.merge(books.reset_index(), wd_results, on=\"viaf_id\").set_index(\"index\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "id": "7a417e6e",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "187775"
      ]
     },
     "execution_count": 28,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(merged_dataset)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "id": "2cfad10e",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "88"
      ]
     },
     "execution_count": 29,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(merged_dataset[merged_dataset.duplicated(subset = \"ISBN\")])"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "d75a9563",
   "metadata": {},
   "source": [
    "There are 88 items that are duplicated! In the sense that either two QIDs are connected to one VIAF id, or the other way around. We will deal with it at future step."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c3bbc180",
   "metadata": {},
   "source": [
    "C. Save meged dataset."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "id": "777d33a7",
   "metadata": {},
   "outputs": [],
   "source": [
    "merged_dataset.to_csv(location_to_save+\"final_merged_dataset.csv\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "e19685ef",
   "metadata": {},
   "source": [
    "## 12. Call for WikiData properties\n",
    "<b>Goal</b>: To fill the dataset with author information. We use WikiData QID to access the WikiData entry of each author using the WikiData API."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "aa86088b",
   "metadata": {},
   "source": [
    "A. Read file."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "a347c292",
   "metadata": {},
   "outputs": [],
   "source": [
    "books = pd.read_csv(location_to_save+\"final_merged_dataset.csv\", dtype = object, index_col=0).fillna(\"\")    "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "561b9056",
   "metadata": {},
   "source": [
    "B. Set properties that we are looking for."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "d79a66d7",
   "metadata": {},
   "outputs": [],
   "source": [
    "properties = {\"sexuality\":\"P91\",\n",
    "             \"country\":\"P27\",\n",
    "             \"language\":\"P6886\",\n",
    "             \"religion\":\"P140\",\n",
    "             \"gender\":\"P21\",\n",
    "             \"ethnicity\":\"P172\",\n",
    "             \"birthyear\":\"P569\",\n",
    "             \"deathyear\":\"P570\"}"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "df02fea9",
   "metadata": {},
   "source": [
    "C. Keep unique authors."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "6d71e875",
   "metadata": {},
   "outputs": [],
   "source": [
    "unique_authors = books.drop_duplicates([\"viaf_id\",\"QID\"]).copy()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "eebac069",
   "metadata": {},
   "outputs": [],
   "source": [
    "for k,v in properties.items():\n",
    "    unique_authors[k]=\"\"\n",
    "unique_authors[\"label\"] = \"\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "c4679974",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "40529"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(unique_authors)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "247dc020",
   "metadata": {},
   "source": [
    "D. Run WikiData API."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "050b8cdc",
   "metadata": {},
   "outputs": [],
   "source": [
    "start = time.time()\n",
    "i=0\n",
    "for index,row in unique_authors.iloc[7860:].iterrows():\n",
    "    author_qid = row[\"QID\"]\n",
    "    author_dict = get_entity_dict_from_api(author_qid)\n",
    "    \n",
    "    for prop, qid in properties.items():\n",
    "        if prop==\"birthyear\" or prop==\"deathyear\":\n",
    "            try:\n",
    "                value = [x[\"mainsnak\"][\"datavalue\"][\"value\"][\"time\"] for x in author_dict[\"claims\"][qid]]\n",
    "            except:\n",
    "                value = \"\"\n",
    "        else:\n",
    "            try:\n",
    "                value = [x[\"mainsnak\"][\"datavalue\"][\"value\"][\"id\"] for x in author_dict[\"claims\"][qid]]\n",
    "            except:\n",
    "                value = \"\"\n",
    "        unique_authors.at[index,prop] = str(value)\n",
    "    \n",
    "    try:\n",
    "        label = author_dict[\"labels\"][\"en\"][\"value\"] # author name in WikiData\n",
    "    except:\n",
    "        label = \"\"\n",
    "    unique_authors.at[index,\"label\"] = label\n",
    "    \n",
    "    if i%100==0:\n",
    "        print(\"100 is gone!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!\", index)\n",
    "        print(time.time()-start)\n",
    "    if i%500==0:\n",
    "        unique_authors.to_csv(location_to_save + 'unique_authors_with_properties.csv')\n",
    "    i+=1"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "8df14837",
   "metadata": {},
   "source": [
    "E. Save file."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "id": "ae1760cb",
   "metadata": {},
   "outputs": [],
   "source": [
    "unique_authors.to_csv(location_to_save + 'unique_authors_with_properties.csv')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "e467b41b",
   "metadata": {},
   "source": [
    "## 13. WikiData properties to labels\n",
    "Most of the properties we gathered have QID values rather than explainable ones. \n",
    "<b>Goal</b>: To use WikiData API again to turn the QID values of the properties into readable labels."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "9d8d4219",
   "metadata": {},
   "source": [
    "A. Read files."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "id": "36a3b7a5",
   "metadata": {},
   "outputs": [],
   "source": [
    "unique_authors = pd.read_csv(location_to_save+\"unique_authors_with_properties.csv\", dtype = object, index_col=0).fillna(\"\")    "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "fcaf155c",
   "metadata": {},
   "source": [
    "B. Process Qvalues."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "id": "5cfcea2f",
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "relevant_columns = list(unique_authors.columns[12:-3])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "id": "16293165",
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['sexuality', 'country', 'language', 'religion', 'gender', 'ethnicity']"
      ]
     },
     "execution_count": 40,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "relevant_columns # the ones that have QID values"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "id": "be67799e",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>sexuality</th>\n",
       "      <th>country</th>\n",
       "      <th>language</th>\n",
       "      <th>religion</th>\n",
       "      <th>gender</th>\n",
       "      <th>ethnicity</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>index</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td></td>\n",
       "      <td>['Q16']</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>['Q6581097']</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td></td>\n",
       "      <td>['Q30', 'Q38']</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>['Q6581097']</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td></td>\n",
       "      <td>['Q30']</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>['Q6581072']</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td></td>\n",
       "      <td>['Q30']</td>\n",
       "      <td>['Q1860']</td>\n",
       "      <td></td>\n",
       "      <td>['Q6581072']</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td></td>\n",
       "      <td>['Q30']</td>\n",
       "      <td>['Q1860']</td>\n",
       "      <td>['Q93191']</td>\n",
       "      <td>['Q6581072']</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      sexuality         country   language    religion        gender ethnicity\n",
       "index                                                                         \n",
       "1                       ['Q16']                         ['Q6581097']          \n",
       "2                ['Q30', 'Q38']                         ['Q6581097']          \n",
       "3                       ['Q30']                         ['Q6581072']          \n",
       "4                       ['Q30']  ['Q1860']              ['Q6581072']          \n",
       "5                       ['Q30']  ['Q1860']  ['Q93191']  ['Q6581072']          "
      ]
     },
     "execution_count": 43,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "unique_authors[relevant_columns].head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "id": "8e00f77c",
   "metadata": {},
   "outputs": [],
   "source": [
    "for col in relevant_columns: # Change the syntax of the entry: comma separated \n",
    "    unique_authors[col] = unique_authors[col].apply(lambda d: re.sub(r'\\W+', ',', d).strip(\",\"))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "id": "367dd683",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>sexuality</th>\n",
       "      <th>country</th>\n",
       "      <th>language</th>\n",
       "      <th>religion</th>\n",
       "      <th>gender</th>\n",
       "      <th>ethnicity</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>index</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td></td>\n",
       "      <td>Q16</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>Q6581097</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td></td>\n",
       "      <td>Q30,Q38</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>Q6581097</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td></td>\n",
       "      <td>Q30</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>Q6581072</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td></td>\n",
       "      <td>Q30</td>\n",
       "      <td>Q1860</td>\n",
       "      <td></td>\n",
       "      <td>Q6581072</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td></td>\n",
       "      <td>Q30</td>\n",
       "      <td>Q1860</td>\n",
       "      <td>Q93191</td>\n",
       "      <td>Q6581072</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      sexuality  country language religion    gender ethnicity\n",
       "index                                                         \n",
       "1                    Q16                    Q6581097          \n",
       "2                Q30,Q38                    Q6581097          \n",
       "3                    Q30                    Q6581072          \n",
       "4                    Q30    Q1860           Q6581072          \n",
       "5                    Q30    Q1860   Q93191  Q6581072          "
      ]
     },
     "execution_count": 45,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "unique_authors[relevant_columns].head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "id": "48fd3dc7",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array(['Q6581097', 'Q6581072', '', 'Q189125', 'Q48270,Q6581072',\n",
       "       'Q1052281', 'Q48270', 'Q2449503', 'Q6581097,Q6581072',\n",
       "       'Q48270,Q96000630', 'Q18116794,Q6581097', 'Q6581072,Q6581097',\n",
       "       'Q1052281,Q6581072', 'Q1097630', 'Q1097630,Q48270',\n",
       "       'Q6581072,Q1097630', 'Q27679684,Q6581097'], dtype=object)"
      ]
     },
     "execution_count": 47,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "unique_authors.gender.unique() # example of how the values look like now"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c8ad0b1b",
   "metadata": {},
   "source": [
    "C. Create property dict. We basically collect the unique Qvalues for every property."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 48,
   "id": "49ba4758",
   "metadata": {},
   "outputs": [],
   "source": [
    "property_labels = {}\n",
    "for col in relevant_columns:\n",
    "    property_labels[col] = []\n",
    "    unique_values = unique_authors[col].unique()\n",
    "    for value in unique_values:\n",
    "        if value!=\"\":\n",
    "            values = value.split(\",\")\n",
    "            for val in values:\n",
    "                if val not in property_labels[col]:\n",
    "                    property_labels[col].append(val)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 50,
   "id": "86f39986",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['Q6581097',\n",
       " 'Q6581072',\n",
       " 'Q189125',\n",
       " 'Q48270',\n",
       " 'Q1052281',\n",
       " 'Q2449503',\n",
       " 'Q96000630',\n",
       " 'Q18116794',\n",
       " 'Q1097630',\n",
       " 'Q27679684']"
      ]
     },
     "execution_count": 50,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "property_labels[\"gender\"] # example"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "9b4bc9c4",
   "metadata": {},
   "source": [
    "D. Turn QID to readable label."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 175,
   "id": "e3a7ee79",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "3.39420223236084\n",
      "sexuality\n",
      "204.8985240459442\n",
      "country\n",
      "240.67512369155884\n",
      "language\n",
      "{'de': {'language': 'de', 'value': 'Surmang-Kagyü'}}\n",
      "294.4538872241974\n",
      "religion\n",
      "297.62130784988403\n",
      "gender\n",
      "{'be': {'language': 'be', 'value': 'Чэхі, племя'}, 'ru': {'language': 'ru', 'value': 'Чехи (племя)'}, 'cs': {'language': 'cs', 'value': 'Čechové'}, 'ro': {'language': 'ro', 'value': 'cehi'}}\n",
      "{'es': {'language': 'es', 'value': 'Wasco'}, 'ca': {'language': 'ca', 'value': 'wasco'}, 'vec': {'language': 'vec', 'value': 'wasco'}}\n",
      "{'ru': {'language': 'ru', 'value': 'евреи в Румынии'}, 'eo': {'language': 'eo', 'value': 'rumanaj judoj'}}\n",
      "373.84655117988586\n",
      "ethnicity\n"
     ]
    }
   ],
   "source": [
    "start = time.time()\n",
    "for col in relevant_columns:\n",
    "    new_vals = []\n",
    "    for val in property_labels[col]:\n",
    "        prop_dict = get_entity_dict_from_api(val) # check if the library can do multiple at once\n",
    "        try:\n",
    "            new_val = prop_dict[\"labels\"][\"en\"][\"value\"]      \n",
    "        except:\n",
    "            # take the first if english doesn't exist\n",
    "            print(prop_dict[\"labels\"])\n",
    "            new_val = list(prop_dict[\"labels\"].values())[0][\"value\"]\n",
    "        new_vals.append(new_val)\n",
    "    property_labels[col].append(new_vals)\n",
    "    print(time.time() - start)\n",
    "    print(col)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 176,
   "id": "2d3573a0",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "sexuality\n",
      "country\n",
      "language\n",
      "religion\n",
      "gender\n",
      "ethnicity\n"
     ]
    }
   ],
   "source": [
    "for col in property_labels.keys():\n",
    "    vals = property_labels[col]\n",
    "    rang = len(vals) - 1\n",
    "    label_list = vals[-1]\n",
    "    label_dict = {}\n",
    "    for i in range(rang):\n",
    "        label_dict[vals[i]] = label_list[i]\n",
    "    property_labels[col] = label_dict\n",
    "    print(col)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 177,
   "id": "5591dd81",
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Unique values per property\n",
      "sexuality: 8\n",
      "country: 377\n",
      "language: 90\n",
      "religion: 187\n",
      "gender: 10\n",
      "ethnicity: 260\n"
     ]
    }
   ],
   "source": [
    "print(\"Unique values per property\")\n",
    "for column in property_labels.keys():\n",
    "    print(column+\": \"+str(len(property_labels[column].keys())))\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "7c590202",
   "metadata": {},
   "source": [
    "E. Save property dict."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 178,
   "id": "5fdc03bf",
   "metadata": {},
   "outputs": [],
   "source": [
    "with open(location_to_save+\"property_dict.pickle\", \"wb\") as f: # naming it pickle so it's in github\n",
    "    pkl.dump(property_labels,f)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "70ad8372",
   "metadata": {},
   "source": [
    "F. Replace values in the dataframe itself."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 179,
   "id": "12c35bf2",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "sexuality\n",
      "country\n",
      "language\n",
      "religion\n",
      "gender\n",
      "ethnicity\n"
     ]
    }
   ],
   "source": [
    "for col in relevant_columns:\n",
    "    series = unique_authors[col]\n",
    "    new_series = []\n",
    "    for item in series:\n",
    "        if item!=\"\":\n",
    "            items = item.split(\",\")\n",
    "            new_items = []\n",
    "            for it in items:\n",
    "                new_it = property_labels[col][it]\n",
    "                new_items.append(new_it)\n",
    "            new_items = \",\".join(new_items)\n",
    "        else: \n",
    "            new_items = \"\"\n",
    "        new_series.append(new_items)\n",
    "    unique_authors[col] = new_series\n",
    "    print(col)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 180,
   "id": "0fba6018",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array(['male', 'female', '', 'transgender person', 'non-binary,female',\n",
       "       'transgender female', 'non-binary', 'transgender male',\n",
       "       'male,female', 'non-binary,X-gender', 'genderfluid,male',\n",
       "       'female,male', 'transgender female,female', 'intersex',\n",
       "       'intersex,non-binary', 'female,intersex', 'transfeminine,male'],\n",
       "      dtype=object)"
      ]
     },
     "execution_count": 180,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "unique_authors.gender.unique()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "90cde78c",
   "metadata": {},
   "source": [
    "G. Process birth and deathyear."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 51,
   "id": "9a9c4445",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "\"['+1937-03-04T00:00:00Z']\""
      ]
     },
     "execution_count": 51,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "unique_authors.birthyear[1] # try datetime python function instead!"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 52,
   "id": "16fb0929",
   "metadata": {},
   "outputs": [],
   "source": [
    "for index, row in unique_authors[[\"birthyear\", \"deathyear\"]].iterrows():\n",
    "    b = row.birthyear\n",
    "    d = row.deathyear\n",
    "    if b!=\"\":\n",
    "        if b[0]==\"[\":\n",
    "            bs = b.split(\"['\")\n",
    "            b_entry = bs[1]\n",
    "            new_b = b_entry[:5]\n",
    "        else:\n",
    "            new_b = b[:5]\n",
    "        \n",
    "        unique_authors.at[index, \"birthyear\"] = new_b\n",
    "    \n",
    "    if d!=\"\":\n",
    "        if d[0]==\"[\":\n",
    "            ds = d.split(\"['\")\n",
    "            d_entry = ds[1]\n",
    "            new_d = d_entry[:5]\n",
    "        else:\n",
    "            new_d = d[:5]\n",
    "        unique_authors.at[index, \"deathyear\"] = new_d"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 53,
   "id": "eb25c0ee",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'+1937'"
      ]
     },
     "execution_count": 53,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "unique_authors.birthyear[1]"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "eb297380",
   "metadata": {},
   "source": [
    "H. Save file."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 192,
   "id": "9fefe08c",
   "metadata": {},
   "outputs": [],
   "source": [
    "unique_authors.to_csv(location_to_save + 'unique_authors_with_readable_properties.csv')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "5403b515",
   "metadata": {},
   "source": [
    "## 14. Solve the double VIAF id - Qid problem\n",
    "<b>Goal</b>: To investigate why there is not a 1-1 relationship between VIAF ids and Qids and solve the issue."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "22ed2c69",
   "metadata": {},
   "source": [
    "A. Read file: it's the unique authors with filled readable properties."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 56,
   "id": "abfc954e",
   "metadata": {},
   "outputs": [],
   "source": [
    "unique_authors = pd.read_csv(location_to_save+\"unique_authors_with_readable_properties.csv\", dtype = object, index_col=0).fillna(\"\")    "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 57,
   "id": "c4846c46",
   "metadata": {},
   "outputs": [],
   "source": [
    "duplicate_viaf_ids = unique_authors[unique_authors.duplicated(\"viaf_id\", keep = False)].sort_values(by = \"viaf_id\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 63,
   "id": "320d0f44",
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "72"
      ]
     },
     "execution_count": 63,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(duplicate_viaf_ids)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 64,
   "id": "597e0a76",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['title', 'author', 'year', 'publisher', 'alt_title', 'alt_author',\n",
       "       'correct_author', 'alt_first_author', 'viaf_id', 'viaf_name', 'QID',\n",
       "       'sexuality', 'country', 'language', 'religion', 'gender', 'ethnicity',\n",
       "       'birthyear', 'deathyear', 'label'],\n",
       "      dtype='object')"
      ]
     },
     "execution_count": 64,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "duplicate_viaf_ids.columns[1:]"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "162ea11b",
   "metadata": {},
   "source": [
    "B. Remove duplicate viaf id rows if they are the same in terms of every property apart from qid. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 65,
   "id": "c3f0a929",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "72"
      ]
     },
     "execution_count": 65,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(duplicate_viaf_ids.drop_duplicates(subset=list(duplicate_viaf_ids.columns[1:])))"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a458489f",
   "metadata": {},
   "source": [
    "It's the same number, which means that the double qids are actually mistakenly twice put people."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 66,
   "id": "8d0c5d1c",
   "metadata": {},
   "outputs": [],
   "source": [
    "unique_viaf_ids = duplicate_viaf_ids.drop_duplicates(subset=list(duplicate_viaf_ids.columns[1:]))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 67,
   "id": "13faf695",
   "metadata": {},
   "outputs": [],
   "source": [
    "viaf_id_to_QID_map = dict(zip(unique_viaf_ids.viaf_id, unique_viaf_ids.QID))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 68,
   "id": "8c5f1ac3",
   "metadata": {},
   "outputs": [],
   "source": [
    "duplicate_viaf_ids[\"QID\"] = duplicate_viaf_ids[\"viaf_id\"].map(viaf_id_to_QID_map)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 78,
   "id": "4a8e3b61",
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "duplicate_viaf_ids = duplicate_viaf_ids.drop_duplicates()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 79,
   "id": "337ccfa2",
   "metadata": {},
   "outputs": [],
   "source": [
    "unique_authors.loc[list(duplicate_viaf_ids.index)] = duplicate_viaf_ids"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 82,
   "id": "fa6131dd",
   "metadata": {},
   "outputs": [],
   "source": [
    "unique_authors = unique_authors.drop_duplicates()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "35c24234",
   "metadata": {},
   "source": [
    "C. Save file."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 84,
   "id": "61c8c475",
   "metadata": {},
   "outputs": [],
   "source": [
    "unique_authors.to_csv(location_to_save + 'unique_authors_with_readable_properties.csv')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "181d6862",
   "metadata": {},
   "source": [
    "D. Check the duplicate Qids."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 85,
   "id": "542a7a96",
   "metadata": {},
   "outputs": [],
   "source": [
    "duplicate_Qids = unique_authors[unique_authors.duplicated(\"QID\", keep = False)].sort_values(by = \"QID\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "43877fda-47bc-4eaf-ace0-423ab0432491",
   "metadata": {},
   "source": [
    "check pseudonym property P742"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 90,
   "id": "c664e04f",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>author</th>\n",
       "      <th>alt_author</th>\n",
       "      <th>correct_author</th>\n",
       "      <th>viaf_id</th>\n",
       "      <th>viaf_name</th>\n",
       "      <th>QID</th>\n",
       "      <th>label</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>index</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>138202</th>\n",
       "      <td>M Masters</td>\n",
       "      <td>M. Masters</td>\n",
       "      <td>M. Masters</td>\n",
       "      <td>92725843</td>\n",
       "      <td>M Masters</td>\n",
       "      <td>Q10363150</td>\n",
       "      <td>Robert Alexander</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>58684</th>\n",
       "      <td>R. D. Zimmerman</td>\n",
       "      <td>R. D. Zimmerman</td>\n",
       "      <td>R. D. Zimmerman</td>\n",
       "      <td>51809100</td>\n",
       "      <td>R. D Zimmerman</td>\n",
       "      <td>Q10363150</td>\n",
       "      <td>Robert Alexander</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>206069</th>\n",
       "      <td>David A Myers</td>\n",
       "      <td>David A. Myers</td>\n",
       "      <td>David A. Myers</td>\n",
       "      <td>85444993</td>\n",
       "      <td>David A Myers</td>\n",
       "      <td>Q1174497</td>\n",
       "      <td>David Myers</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27195</th>\n",
       "      <td>David Myers</td>\n",
       "      <td>David Myers</td>\n",
       "      <td>David Myers</td>\n",
       "      <td>54210999</td>\n",
       "      <td>David Myers American psychologist</td>\n",
       "      <td>Q1174497</td>\n",
       "      <td>David Myers</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>159754</th>\n",
       "      <td>Three Initiates</td>\n",
       "      <td>Three Initiates|Three Initiates Staff</td>\n",
       "      <td>Three Initiates</td>\n",
       "      <td>31992123</td>\n",
       "      <td>Three Initiates</td>\n",
       "      <td>Q1335019</td>\n",
       "      <td>William Walker Atkinson</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>204045</th>\n",
       "      <td>Yogi Ramacharaka</td>\n",
       "      <td>Yogi Ramacharaka</td>\n",
       "      <td>Yogi Ramacharaka</td>\n",
       "      <td>103211604</td>\n",
       "      <td>Yogi Rāmacharaka</td>\n",
       "      <td>Q1335019</td>\n",
       "      <td>William Walker Atkinson</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29109</th>\n",
       "      <td>Karen Keast</td>\n",
       "      <td>Karen Keast</td>\n",
       "      <td>Karen Keast</td>\n",
       "      <td>270879607</td>\n",
       "      <td>Karen Keast</td>\n",
       "      <td>Q17409349</td>\n",
       "      <td>Sandra Canfield</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10960</th>\n",
       "      <td>Sandra Canfield</td>\n",
       "      <td>Sandra Canfield</td>\n",
       "      <td>Sandra Canfield</td>\n",
       "      <td>7411024</td>\n",
       "      <td>Sandra Canfield</td>\n",
       "      <td>Q17409349</td>\n",
       "      <td>Sandra Canfield</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5730</th>\n",
       "      <td>Caroline Arnold</td>\n",
       "      <td>Judith Arnold|Rachel Lindsay</td>\n",
       "      <td>Rachel Lindsay</td>\n",
       "      <td>88010581</td>\n",
       "      <td>Rachel Lindsay, 1926-2014</td>\n",
       "      <td>Q3161936</td>\n",
       "      <td>Roberta Leigh</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18158</th>\n",
       "      <td>Roberta Leigh</td>\n",
       "      <td>Roberta Leigh</td>\n",
       "      <td>Roberta Leigh</td>\n",
       "      <td>120568873</td>\n",
       "      <td>Roberta Leigh</td>\n",
       "      <td>Q3161936</td>\n",
       "      <td>Roberta Leigh</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                  author                             alt_author  \\\n",
       "index                                                             \n",
       "138202         M Masters                             M. Masters   \n",
       "58684    R. D. Zimmerman                        R. D. Zimmerman   \n",
       "206069     David A Myers                         David A. Myers   \n",
       "27195        David Myers                            David Myers   \n",
       "159754   Three Initiates  Three Initiates|Three Initiates Staff   \n",
       "204045  Yogi Ramacharaka                       Yogi Ramacharaka   \n",
       "29109        Karen Keast                            Karen Keast   \n",
       "10960    Sandra Canfield                        Sandra Canfield   \n",
       "5730     Caroline Arnold           Judith Arnold|Rachel Lindsay   \n",
       "18158      Roberta Leigh                          Roberta Leigh   \n",
       "\n",
       "          correct_author    viaf_id                          viaf_name  \\\n",
       "index                                                                    \n",
       "138202        M. Masters   92725843                          M Masters   \n",
       "58684    R. D. Zimmerman   51809100                     R. D Zimmerman   \n",
       "206069    David A. Myers   85444993                      David A Myers   \n",
       "27195        David Myers   54210999  David Myers American psychologist   \n",
       "159754   Three Initiates   31992123                    Three Initiates   \n",
       "204045  Yogi Ramacharaka  103211604                   Yogi Rāmacharaka   \n",
       "29109        Karen Keast  270879607                        Karen Keast   \n",
       "10960    Sandra Canfield    7411024                    Sandra Canfield   \n",
       "5730      Rachel Lindsay   88010581          Rachel Lindsay, 1926-2014   \n",
       "18158      Roberta Leigh  120568873                      Roberta Leigh   \n",
       "\n",
       "              QID                    label  \n",
       "index                                       \n",
       "138202  Q10363150         Robert Alexander  \n",
       "58684   Q10363150         Robert Alexander  \n",
       "206069   Q1174497              David Myers  \n",
       "27195    Q1174497              David Myers  \n",
       "159754   Q1335019  William Walker Atkinson  \n",
       "204045   Q1335019  William Walker Atkinson  \n",
       "29109   Q17409349          Sandra Canfield  \n",
       "10960   Q17409349          Sandra Canfield  \n",
       "5730     Q3161936            Roberta Leigh  \n",
       "18158    Q3161936            Roberta Leigh  "
      ]
     },
     "execution_count": 90,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "duplicate_Qids[[\"author\", \"alt_author\", \"correct_author\", \"viaf_id\", \"viaf_name\", \"QID\", \"label\"]].head(10)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "bb1c4e58",
   "metadata": {},
   "source": [
    "By manually checking, it looks like they are pen names. In this case, we can just drop because it doesn't matter."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 91,
   "id": "c5e0dc3e",
   "metadata": {},
   "outputs": [],
   "source": [
    "unique_Qids = duplicate_Qids.drop_duplicates(subset=list(duplicate_Qids.columns[1:]))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 92,
   "id": "d038d587",
   "metadata": {},
   "outputs": [],
   "source": [
    "QID_to_viaf_id_map = dict(zip(unique_Qids.QID, unique_Qids.viaf_id))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 93,
   "id": "29985fb8",
   "metadata": {},
   "outputs": [],
   "source": [
    "duplicate_Qids[\"viaf_id\"] = duplicate_Qids[\"QID\"].map(QID_to_viaf_id_map)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 94,
   "id": "de4df857",
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "duplicate_Qids = duplicate_Qids.drop_duplicates()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 95,
   "id": "770a832b",
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/export/scratch2/home/savvina/new_environment/lib64/python3.6/site-packages/pandas/core/indexing.py:670: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  iloc._setitem_with_indexer(indexer, value)\n",
      "/export/scratch2/home/savvina/new_environment/lib64/python3.6/site-packages/ipykernel_launcher.py:1: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  \"\"\"Entry point for launching an IPython kernel.\n"
     ]
    }
   ],
   "source": [
    "unique_authors.loc[list(duplicate_Qids.index)] = duplicate_Qids"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 99,
   "id": "1760519b",
   "metadata": {},
   "outputs": [],
   "source": [
    "unique_authors = unique_authors.drop_duplicates(subset = [\"QID\", \"viaf_id\"])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 103,
   "id": "d7f04b15",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "40471"
      ]
     },
     "execution_count": 103,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(unique_authors)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "60421ca5",
   "metadata": {},
   "source": [
    "E. Save file."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 104,
   "id": "61cdbd19",
   "metadata": {},
   "outputs": [],
   "source": [
    "unique_authors.to_csv(location_to_save + 'unique_authors_with_readable_properties.csv')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "cf101dd3",
   "metadata": {},
   "source": [
    "## 15. Create \"Final\" Datasets\n",
    "<b>Goal</b>: To fill the datasets with the information collected in unique_authors."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "16df439f",
   "metadata": {},
   "source": [
    "A. Read file: it's the unique authors with filled readable properties."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "a9429cfa",
   "metadata": {},
   "outputs": [],
   "source": [
    "unique_authors = pd.read_csv(location_to_save+\"unique_authors_with_readable_properties.csv\", dtype = object, index_col=0).fillna(\"\")    "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "ae7bdcfb",
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "unique_authors = unique_authors[[\"QID\", \"viaf_id\",\"sexuality\",\"country\",\n",
    "                                \"language\",\"religion\",\"gender\",\"ethnicity\",\n",
    "                                \"birthyear\",\"deathyear\",\"label\"]]"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "6222816b",
   "metadata": {},
   "source": [
    "B. Fill Wikidata books."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "2fa6cf1f",
   "metadata": {},
   "outputs": [],
   "source": [
    "wikidata_books = pd.read_csv(location_to_save+\"final_merged_dataset.csv\", low_memory=False, index_col=0).fillna(\"\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "1f06ad4c",
   "metadata": {},
   "outputs": [],
   "source": [
    "wikidata_books = wikidata_books.drop_duplicates([\"QID\",\"ISBN\"]).drop_duplicates([\"viaf_id\",\"ISBN\"])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "e3f6e43d",
   "metadata": {},
   "outputs": [],
   "source": [
    "wikidata_books_with_properties = pd.merge(wikidata_books, unique_authors, on=[\"viaf_id\",\"QID\"], how=\"left\").fillna(\"\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "577b866a",
   "metadata": {},
   "source": [
    "C. Fill entire books."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "b2a9fd05",
   "metadata": {},
   "outputs": [],
   "source": [
    "books = pd.read_csv(location_to_save+\"final_items_books.csv\", dtype = object, index_col=0).fillna(\"\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "f6025b56",
   "metadata": {},
   "outputs": [],
   "source": [
    "books_with_properties = pd.merge(books,wikidata_books_with_properties, on=list(books.columns), how=\"left\").fillna(\"\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "3921aed8",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>ISBN</th>\n",
       "      <th>title</th>\n",
       "      <th>author</th>\n",
       "      <th>year</th>\n",
       "      <th>publisher</th>\n",
       "      <th>alt_title</th>\n",
       "      <th>alt_author</th>\n",
       "      <th>correct_author</th>\n",
       "      <th>alt_first_author</th>\n",
       "      <th>viaf_id</th>\n",
       "      <th>...</th>\n",
       "      <th>QID</th>\n",
       "      <th>sexuality</th>\n",
       "      <th>country</th>\n",
       "      <th>language</th>\n",
       "      <th>religion</th>\n",
       "      <th>gender</th>\n",
       "      <th>ethnicity</th>\n",
       "      <th>birthyear</th>\n",
       "      <th>deathyear</th>\n",
       "      <th>label</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0195153448</td>\n",
       "      <td>Classical Mythology</td>\n",
       "      <td>Mark P. O. Morford</td>\n",
       "      <td>2002</td>\n",
       "      <td>Oxford University Press</td>\n",
       "      <td>Classical Mythology</td>\n",
       "      <td>Mark P. O. Morford|Robert J. Lenardon</td>\n",
       "      <td>Mark P. O. Morford</td>\n",
       "      <td>Mark P. O. Morford</td>\n",
       "      <td>92220559</td>\n",
       "      <td>...</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>0002005018</td>\n",
       "      <td>Clara Callan</td>\n",
       "      <td>Richard Bruce Wright</td>\n",
       "      <td>2001</td>\n",
       "      <td>HarperFlamingo Canada</td>\n",
       "      <td>Clara Callan</td>\n",
       "      <td>Richard Bruce Wright</td>\n",
       "      <td>Richard Bruce Wright</td>\n",
       "      <td>Richard Bruce Wright</td>\n",
       "      <td>64022406</td>\n",
       "      <td>...</td>\n",
       "      <td>Q7323887</td>\n",
       "      <td></td>\n",
       "      <td>Canada</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>male</td>\n",
       "      <td></td>\n",
       "      <td>+1937</td>\n",
       "      <td>+2017</td>\n",
       "      <td>Richard B. Wright</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>0060973129</td>\n",
       "      <td>Decision in Normandy</td>\n",
       "      <td>Carlo D'Este</td>\n",
       "      <td>1991</td>\n",
       "      <td>HarperPerennial</td>\n",
       "      <td>Decision in Normandy</td>\n",
       "      <td>Carlo D'Este</td>\n",
       "      <td>Carlo D'Este</td>\n",
       "      <td>Carlo D'Este</td>\n",
       "      <td>97755576</td>\n",
       "      <td>...</td>\n",
       "      <td>Q2939184</td>\n",
       "      <td></td>\n",
       "      <td>United States of America,Italy</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>male</td>\n",
       "      <td></td>\n",
       "      <td>+1936</td>\n",
       "      <td>+2020</td>\n",
       "      <td>Carlo D'Este</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>0374157065</td>\n",
       "      <td>Flu: The Story of the Great Influenza Pandemic...</td>\n",
       "      <td>Gina Bari Kolata</td>\n",
       "      <td>1999</td>\n",
       "      <td>Farrar Straus Giroux</td>\n",
       "      <td>Flu</td>\n",
       "      <td>Gina Bari Kolata</td>\n",
       "      <td>Gina Bari Kolata</td>\n",
       "      <td>Gina Bari Kolata</td>\n",
       "      <td>111861625</td>\n",
       "      <td>...</td>\n",
       "      <td>Q1524875</td>\n",
       "      <td></td>\n",
       "      <td>United States of America</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>female</td>\n",
       "      <td></td>\n",
       "      <td>+1948</td>\n",
       "      <td></td>\n",
       "      <td>Gina Kolata</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>0393045218</td>\n",
       "      <td>The Mummies of Urumchi</td>\n",
       "      <td>E. J. W. Barber</td>\n",
       "      <td>1999</td>\n",
       "      <td>W. W. Norton &amp;amp; Company</td>\n",
       "      <td>The Mummies of Ürümchi</td>\n",
       "      <td>E. J. W. Barber</td>\n",
       "      <td>E. J. W. Barber</td>\n",
       "      <td>E. J. W. Barber</td>\n",
       "      <td>91244635</td>\n",
       "      <td>...</td>\n",
       "      <td>Q5363702</td>\n",
       "      <td></td>\n",
       "      <td>United States of America</td>\n",
       "      <td>English</td>\n",
       "      <td></td>\n",
       "      <td>female</td>\n",
       "      <td></td>\n",
       "      <td>+1940</td>\n",
       "      <td></td>\n",
       "      <td>Elizabeth Wayland Barber</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 21 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "         ISBN                                              title  \\\n",
       "0  0195153448                                Classical Mythology   \n",
       "1  0002005018                                       Clara Callan   \n",
       "2  0060973129                               Decision in Normandy   \n",
       "3  0374157065  Flu: The Story of the Great Influenza Pandemic...   \n",
       "4  0393045218                             The Mummies of Urumchi   \n",
       "\n",
       "                 author  year                   publisher  \\\n",
       "0    Mark P. O. Morford  2002     Oxford University Press   \n",
       "1  Richard Bruce Wright  2001       HarperFlamingo Canada   \n",
       "2          Carlo D'Este  1991             HarperPerennial   \n",
       "3      Gina Bari Kolata  1999        Farrar Straus Giroux   \n",
       "4       E. J. W. Barber  1999  W. W. Norton &amp; Company   \n",
       "\n",
       "                alt_title                             alt_author  \\\n",
       "0     Classical Mythology  Mark P. O. Morford|Robert J. Lenardon   \n",
       "1            Clara Callan                   Richard Bruce Wright   \n",
       "2    Decision in Normandy                           Carlo D'Este   \n",
       "3                     Flu                       Gina Bari Kolata   \n",
       "4  The Mummies of Ürümchi                        E. J. W. Barber   \n",
       "\n",
       "         correct_author      alt_first_author    viaf_id  ...       QID  \\\n",
       "0    Mark P. O. Morford    Mark P. O. Morford   92220559  ...             \n",
       "1  Richard Bruce Wright  Richard Bruce Wright   64022406  ...  Q7323887   \n",
       "2          Carlo D'Este          Carlo D'Este   97755576  ...  Q2939184   \n",
       "3      Gina Bari Kolata      Gina Bari Kolata  111861625  ...  Q1524875   \n",
       "4       E. J. W. Barber       E. J. W. Barber   91244635  ...  Q5363702   \n",
       "\n",
       "  sexuality                         country language religion  gender  \\\n",
       "0                                                                       \n",
       "1                                    Canada                      male   \n",
       "2            United States of America,Italy                      male   \n",
       "3                  United States of America                    female   \n",
       "4                  United States of America  English           female   \n",
       "\n",
       "  ethnicity birthyear deathyear                     label  \n",
       "0                                                          \n",
       "1               +1937     +2017         Richard B. Wright  \n",
       "2               +1936     +2020              Carlo D'Este  \n",
       "3               +1948                         Gina Kolata  \n",
       "4               +1940            Elizabeth Wayland Barber  \n",
       "\n",
       "[5 rows x 21 columns]"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "books_with_properties.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "3efd05c8",
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(271360, 271360)"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(books), len(books_with_properties)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "db0a29dd",
   "metadata": {},
   "source": [
    "D. Fill entire ratings."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "id": "6eb57ac7",
   "metadata": {},
   "outputs": [],
   "source": [
    "ratings = pd.read_csv(\"data/ratings_books.csv\", dtype = object).fillna(\"\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "id": "91d2e10d",
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "1149780"
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(ratings)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "id": "2b1d974b",
   "metadata": {},
   "outputs": [],
   "source": [
    "ratings_with_properties = pd.merge(books_with_properties, ratings, on = \"ISBN\", how=\"right\").fillna(\"\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "id": "717bc613",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "1149780"
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(ratings_with_properties)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "96fd816d",
   "metadata": {},
   "source": [
    "D. Fill fairbooks ratings dataset."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "id": "5f97e8c8",
   "metadata": {},
   "outputs": [],
   "source": [
    "fairbook_ratings = pd.read_csv(location_to_save+\"fairbook_ratings.csv\", dtype = object, index_col=0).fillna(\"\")    "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "id": "ca233c9f",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "88552"
      ]
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(fairbook_ratings)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "id": "7c059347",
   "metadata": {},
   "outputs": [],
   "source": [
    "fairbook_ratings_with_properties = pd.merge(fairbook_ratings, books_with_properties, on = \"ISBN\", how = \"left\").fillna(\"\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "id": "68f09905",
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "88552"
      ]
     },
     "execution_count": 28,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(fairbook_ratings_with_properties)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "id": "ea49f825",
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>User-ID</th>\n",
       "      <th>ISBN</th>\n",
       "      <th>Book-Rating</th>\n",
       "      <th>title</th>\n",
       "      <th>author</th>\n",
       "      <th>year</th>\n",
       "      <th>publisher</th>\n",
       "      <th>alt_title</th>\n",
       "      <th>alt_author</th>\n",
       "      <th>correct_author</th>\n",
       "      <th>...</th>\n",
       "      <th>QID</th>\n",
       "      <th>sexuality</th>\n",
       "      <th>country</th>\n",
       "      <th>language</th>\n",
       "      <th>religion</th>\n",
       "      <th>gender</th>\n",
       "      <th>ethnicity</th>\n",
       "      <th>birthyear</th>\n",
       "      <th>deathyear</th>\n",
       "      <th>label</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>276847</td>\n",
       "      <td>3426029553</td>\n",
       "      <td>8</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>...</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>276847</td>\n",
       "      <td>3551551677</td>\n",
       "      <td>10</td>\n",
       "      <td>Harry Potter und der Stein der Weisen</td>\n",
       "      <td>Joanne K. Rowling</td>\n",
       "      <td>1999</td>\n",
       "      <td>Carlsen Verlag GmbH</td>\n",
       "      <td>Harry Potter und der Stein der Weisen</td>\n",
       "      <td>Joanne K. Rowling</td>\n",
       "      <td>Joanne K. Rowling</td>\n",
       "      <td>...</td>\n",
       "      <td>Q34660</td>\n",
       "      <td></td>\n",
       "      <td>United Kingdom</td>\n",
       "      <td>English</td>\n",
       "      <td>Anglicanism</td>\n",
       "      <td>female</td>\n",
       "      <td>English people</td>\n",
       "      <td>+1965</td>\n",
       "      <td></td>\n",
       "      <td>J. K. Rowling</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>276847</td>\n",
       "      <td>3551551685</td>\n",
       "      <td>10</td>\n",
       "      <td>Harry Potter und die Kammer des Schreckens</td>\n",
       "      <td>Joanne K. Rowling</td>\n",
       "      <td>2000</td>\n",
       "      <td>Carlsen Verlag GmbH</td>\n",
       "      <td>Harry Potter und die Kammer des Schreckens (Ha...</td>\n",
       "      <td>J.K. Rowling</td>\n",
       "      <td>J.K. Rowling</td>\n",
       "      <td>...</td>\n",
       "      <td>Q34660</td>\n",
       "      <td></td>\n",
       "      <td>United Kingdom</td>\n",
       "      <td>English</td>\n",
       "      <td>Anglicanism</td>\n",
       "      <td>female</td>\n",
       "      <td>English people</td>\n",
       "      <td>+1965</td>\n",
       "      <td></td>\n",
       "      <td>J. K. Rowling</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>276847</td>\n",
       "      <td>3551551693</td>\n",
       "      <td>10</td>\n",
       "      <td>Harry Potter und der Gefangene von Azkaban</td>\n",
       "      <td>J. K. Rowling</td>\n",
       "      <td>1999</td>\n",
       "      <td>Carlsen Verlag GmbH</td>\n",
       "      <td>Harry Potter und der Gefangene von Askaban (Ha...</td>\n",
       "      <td>J.K. Rowling</td>\n",
       "      <td>J.K. Rowling</td>\n",
       "      <td>...</td>\n",
       "      <td>Q34660</td>\n",
       "      <td></td>\n",
       "      <td>United Kingdom</td>\n",
       "      <td>English</td>\n",
       "      <td>Anglicanism</td>\n",
       "      <td>female</td>\n",
       "      <td>English people</td>\n",
       "      <td>+1965</td>\n",
       "      <td></td>\n",
       "      <td>J. K. Rowling</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>276847</td>\n",
       "      <td>3551551936</td>\n",
       "      <td>10</td>\n",
       "      <td>Harry Potter Und Der Feuerkelch</td>\n",
       "      <td>Joanne K. Rowling</td>\n",
       "      <td>1999</td>\n",
       "      <td>Carlsen Verlag GmbH</td>\n",
       "      <td>Harry Potter und der Feuerkelch (Harry Potter 4)</td>\n",
       "      <td>J.K. Rowling</td>\n",
       "      <td>J.K. Rowling</td>\n",
       "      <td>...</td>\n",
       "      <td>Q34660</td>\n",
       "      <td></td>\n",
       "      <td>United Kingdom</td>\n",
       "      <td>English</td>\n",
       "      <td>Anglicanism</td>\n",
       "      <td>female</td>\n",
       "      <td>English people</td>\n",
       "      <td>+1965</td>\n",
       "      <td></td>\n",
       "      <td>J. K. Rowling</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 23 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "  User-ID        ISBN Book-Rating                                       title  \\\n",
       "0  276847  3426029553           8                                               \n",
       "1  276847  3551551677          10       Harry Potter und der Stein der Weisen   \n",
       "2  276847  3551551685          10  Harry Potter und die Kammer des Schreckens   \n",
       "3  276847  3551551693          10  Harry Potter und der Gefangene von Azkaban   \n",
       "4  276847  3551551936          10             Harry Potter Und Der Feuerkelch   \n",
       "\n",
       "              author  year            publisher  \\\n",
       "0                                                 \n",
       "1  Joanne K. Rowling  1999  Carlsen Verlag GmbH   \n",
       "2  Joanne K. Rowling  2000  Carlsen Verlag GmbH   \n",
       "3      J. K. Rowling  1999  Carlsen Verlag GmbH   \n",
       "4  Joanne K. Rowling  1999  Carlsen Verlag GmbH   \n",
       "\n",
       "                                           alt_title         alt_author  \\\n",
       "0                                                                         \n",
       "1              Harry Potter und der Stein der Weisen  Joanne K. Rowling   \n",
       "2  Harry Potter und die Kammer des Schreckens (Ha...       J.K. Rowling   \n",
       "3  Harry Potter und der Gefangene von Askaban (Ha...       J.K. Rowling   \n",
       "4   Harry Potter und der Feuerkelch (Harry Potter 4)       J.K. Rowling   \n",
       "\n",
       "      correct_author  ...     QID sexuality         country language  \\\n",
       "0                     ...                                              \n",
       "1  Joanne K. Rowling  ...  Q34660            United Kingdom  English   \n",
       "2       J.K. Rowling  ...  Q34660            United Kingdom  English   \n",
       "3       J.K. Rowling  ...  Q34660            United Kingdom  English   \n",
       "4       J.K. Rowling  ...  Q34660            United Kingdom  English   \n",
       "\n",
       "      religion  gender       ethnicity birthyear deathyear          label  \n",
       "0                                                                          \n",
       "1  Anglicanism  female  English people     +1965            J. K. Rowling  \n",
       "2  Anglicanism  female  English people     +1965            J. K. Rowling  \n",
       "3  Anglicanism  female  English people     +1965            J. K. Rowling  \n",
       "4  Anglicanism  female  English people     +1965            J. K. Rowling  \n",
       "\n",
       "[5 rows x 23 columns]"
      ]
     },
     "execution_count": 30,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "fairbook_ratings_with_properties.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a4e48315",
   "metadata": {},
   "source": [
    "E. Fill fairbooks item dataset."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "id": "2a188926",
   "metadata": {},
   "outputs": [],
   "source": [
    "fairbook_ISBNs = fairbook_ratings_with_properties.ISBN.unique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "id": "284ed8be",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "6921"
      ]
     },
     "execution_count": 32,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(fairbook_ISBNs)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "id": "37a88389",
   "metadata": {},
   "outputs": [],
   "source": [
    "fairbook_books_with_properties = books_with_properties[books_with_properties.ISBN.isin(fairbook_ISBNs)]"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "43c6c921",
   "metadata": {},
   "source": [
    "F. Save all files!"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "id": "527d0f9d",
   "metadata": {},
   "outputs": [],
   "source": [
    "wikidata_books_with_properties.to_csv(location_to_save + 'wikipedia_books_with_readable_properties.csv')\n",
    "books_with_properties.to_csv(location_to_save + 'items_books_with_readable_properties.csv')\n",
    "fairbook_ratings_with_properties.to_csv(location_to_save + 'fairbook_ratings_with_readable_properties.csv')\n",
    "ratings_with_properties.to_csv(large_location_to_save + 'entire_ratings_with_readable_properties.csv')\n",
    "fairbook_books_with_properties.to_csv(location_to_save + 'fairbook_books_with_readable_properties.csv')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "76b61112",
   "metadata": {},
   "source": [
    "## 16. FIlter out unwanted \"wrong\" ISBNs\n",
    "Some of the ratings are of books whose ISBN is not in the books dataset. \n",
    "\n",
    "<b>Goal</b>: To drop them."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ebad6dfc",
   "metadata": {},
   "source": [
    "A. Read files."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "367dce61",
   "metadata": {},
   "outputs": [],
   "source": [
    "entire_books = pd.read_csv(location_to_save+\"items_books_with_readable_properties.csv\", low_memory=False, index_col=0).fillna(\"\")\n",
    "entire_ratings = pd.read_csv(large_location_to_save+\"entire_ratings_with_readable_properties.csv\", low_memory=False, index_col=0).fillna(\"\")\n",
    "fairbook_books = pd.read_csv(location_to_save+\"fairbook_books_with_readable_properties.csv\", low_memory=False, index_col=0).fillna(\"\")\n",
    "fairbook_ratings = pd.read_csv(location_to_save+\"fairbook_ratings_with_readable_properties.csv\", low_memory=False, index_col=0).fillna(\"\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "79fc06ca",
   "metadata": {},
   "source": [
    "B. Check the ones not in books."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "id": "b3fd654c",
   "metadata": {},
   "outputs": [],
   "source": [
    "all_ISBNs = list(entire_books.ISBN)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "04b9315b",
   "metadata": {},
   "source": [
    "C. Remove the ones not in books."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "id": "671e3753",
   "metadata": {},
   "outputs": [],
   "source": [
    "fairbook_ratings_correct_ISBN = fairbook_ratings[fairbook_ratings.ISBN.isin(all_ISBNs) == True]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "id": "c622b18b",
   "metadata": {},
   "outputs": [],
   "source": [
    "entire_ratings_correct_ISBN = entire_ratings[entire_ratings.ISBN.isin(all_ISBNs) == True]"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "8596deef",
   "metadata": {},
   "source": [
    "D. Save files."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "5b0df8d7",
   "metadata": {},
   "outputs": [],
   "source": [
    "fairbook_ratings_correct_ISBN.to_csv(location_to_save + 'fairbook_ratings_with_readable_properties_filtered_ISBNs.csv')\n",
    "entire_ratings_correct_ISBN.to_csv(large_location_to_save + 'entire_ratings_with_readable_properties_filtered_ISBNs.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "086e7f06",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(86782, 88552)"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(fairbook_ratings_correct_ISBN), len(fairbook_ratings)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "cceb04cd",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(1031136, 1149780)"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(entire_ratings_correct_ISBN), len(entire_ratings)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "b48e2e85",
   "metadata": {},
   "source": [
    "## 17. Remove duplicate books\n",
    "So far, we distinguish between books by using their ISBN. However, it might be that the same books have different ISBNs due to a differnet publisher, year and country of publication.\n",
    "\n",
    "<b>Goal</b>: To group ISBNs based on common title and author, and then remove duplicate books and ratings."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "e81c34fa",
   "metadata": {},
   "source": [
    "A. Read files."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 66,
   "id": "3ea80397",
   "metadata": {},
   "outputs": [],
   "source": [
    "entire_books = pd.read_csv(location_to_save+\"items_books_with_readable_properties.csv\", low_memory=False, index_col=0).fillna(\"\")\n",
    "fairbook_books = pd.read_csv(location_to_save+\"fairbook_books_with_readable_properties.csv\", low_memory=False, index_col=0).fillna(\"\")\n",
    "entire_ratings = pd.read_csv(large_location_to_save+\"entire_ratings_with_readable_properties_filtered_ISBNs.csv\", low_memory=False, index_col=0).fillna(\"\")\n",
    "fairbook_ratings = pd.read_csv(location_to_save+\"fairbook_ratings_with_readable_properties_filtered_ISBNs.csv\", low_memory=False, index_col=0).fillna(\"\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "5c658b4e",
   "metadata": {},
   "source": [
    "B. Relevant functions."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 67,
   "id": "9b11bd79",
   "metadata": {},
   "outputs": [],
   "source": [
    "def remove_redundant_books(author_df): # for an author create a dictionary with unique ISBN titles.\n",
    "    author_df.alt_title = author_df.alt_title.apply(lambda x: simplify(x)) #simplify google books author name\n",
    "    unique_titles = author_df.alt_title.unique() #unique google books titles\n",
    "    author_df_alt = author_df.copy() \n",
    "    for unique_title in unique_titles: #for every unique title\n",
    "        first_ISBN = author_df.loc[author_df.alt_title == unique_title].iloc[0].ISBN # keep the first appearence ISBN\n",
    "        author_df_alt.loc[author_df_alt.alt_title == unique_title, \"ISBN_alt\"] = first_ISBN # ISBN_alt is this first ISBN\n",
    "        ISBN_dict = dict(zip(author_df_alt.ISBN, author_df_alt.ISBN_alt)) # every ISBN connected to this title is mapped to the first ISBN\n",
    "    return(ISBN_dict)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 68,
   "id": "8f850937",
   "metadata": {},
   "outputs": [],
   "source": [
    "def simplify(name):\n",
    "    name = name.replace(\" \",\"\").translate(str.maketrans('', '', string.punctuation)).lower()\n",
    "    return name"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "498f7b23",
   "metadata": {},
   "source": [
    "C. Create big ISBN dictionary where each group of ISBNs that refer to the same book are mapped to a representative one."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 69,
   "id": "ca004d54",
   "metadata": {},
   "outputs": [],
   "source": [
    "entire_books[\"ISBN_alt\"] = \"\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 70,
   "id": "4bf8334a",
   "metadata": {},
   "outputs": [],
   "source": [
    "unique_authors = entire_books[[\"QID\", \"viaf_id\"]].drop_duplicates()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "81cd062f",
   "metadata": {},
   "outputs": [],
   "source": [
    "start = time.time()\n",
    "large_ISBN_dict = {}\n",
    "for index, row in unique_authors.iterrows():\n",
    "    viaf_id = row.viaf_id\n",
    "    QID = row.QID\n",
    "    author_df = entire_books[(entire_books.QID==QID)&(entire_books.viaf_id==viaf_id)][[\"ISBN\", \n",
    "                                                                                       \"title\",\n",
    "                                                                                      \"alt_title\",\n",
    "                                                                                      \"ISBN_alt\"]]\n",
    "    ISBN_dict = remove_redundant_books(author_df)\n",
    "    large_ISBN_dict.update(ISBN_dict)\n",
    "    del author_df\n",
    "    del ISBN_dict\n",
    "    if index%500==0:\n",
    "        print(\"500 is gone!!!!!!!!!\", index)\n",
    "        print(time.time()-start)\n",
    "        start = time.time()\n",
    "        with open(location_to_save+'ISBN_dict.pkl', 'wb') as handle:\n",
    "            pkl.dump(large_ISBN_dict, handle)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "1a3c5c72",
   "metadata": {},
   "source": [
    "D. Fill ISBN_alt for all datasets with the appropriate representative."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "4724bc41",
   "metadata": {},
   "source": [
    "Book datasets (entire & fairbook)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "1919b223",
   "metadata": {},
   "outputs": [],
   "source": [
    "for index, row in entire_books.iterrows():\n",
    "    if row.alt_title!=\"\":  \n",
    "        ISBN = row.ISBN\n",
    "        ISBN_alt = large_ISBN_dict[ISBN]\n",
    "        entire_books.at[index, \"ISBN_alt\"] = ISBN_alt"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "93ca9062",
   "metadata": {},
   "outputs": [],
   "source": [
    "fairbook_books[\"ISBN_alt\"] = fairbook_books[\"ISBN\"]\n",
    "for index, row in fairbook_books.iterrows():\n",
    "    if row.alt_title!=\"\":\n",
    "        ISBN = row.ISBN\n",
    "        ISBN_alt = large_ISBN_dict[ISBN]\n",
    "        fairbook_books.at[index, \"ISBN_alt\"] = ISBN_alt"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "4f03b80e",
   "metadata": {},
   "source": [
    "Rating datasets (entire & fairbook)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "09cdac12",
   "metadata": {},
   "outputs": [],
   "source": [
    "for index, row in entire_ratings.iterrows():\n",
    "    if row.alt_title!=\"\":\n",
    "        ISBN = row.ISBN\n",
    "        ISBN_alt = large_ISBN_dict[ISBN]\n",
    "        entire_ratings.at[index, \"ISBN\"] = ISBN_alt\n",
    "entire_ratings_correct = entire_ratings.drop_duplicates([\"ISBN\", \"User-ID\"])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "7c653c18",
   "metadata": {},
   "outputs": [],
   "source": [
    "for index, row in fairbook_ratings_.iterrows():\n",
    "    if row.alt_title!=\"\":\n",
    "        ISBN = row.ISBN\n",
    "        ISBN_alt = large_ISBN_dict[ISBN]\n",
    "        fairbook_ratings.at[index, \"ISBN\"] = ISBN_alt\n",
    "fairbook_ratings_correct = fairbook_ratings.drop_duplicates([\"ISBN\", \"User-ID\"])"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "93b28e33",
   "metadata": {},
   "source": [
    "E. Save files."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "4b8322fa",
   "metadata": {},
   "outputs": [],
   "source": [
    " with open(location_to_save+'ISBN_dict.pkl', 'wb') as handle:\n",
    "            pkl.dump(large_ISBN_dict, handle)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "4269fd3c",
   "metadata": {},
   "outputs": [],
   "source": [
    "entire_books.to_csv(location_to_save+\"items_books_with_readable_properties_correct_ISBN.csv\")\n",
    "fairbook_books.to_csv(location_to_save+\"fairbook_books_with_readable_properties_correct_ISBN.csv\")\n",
    "\n",
    "fairbook_ratings_correct.to_csv(location_to_save + 'fairbook_ratings_with_readable_properties_filtered_correct_ISBNs.csv')\n",
    "entire_ratings_correct.to_csv(large_location_to_save + 'entire_ratings_with_readable_properties_filtered_correct_ISBNs.csv')"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.8.18"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}