A. Data Enrichment Process.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": 1,
   "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 pandas as pd\n",
    "import numpy as np\n",
    "import pickle as pkl\n",
    "from collections import Counter\n",
    "import urllib.request\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": "735ade72",
   "metadata": {},
   "source": [
    "## 1. Download all WikiData entries\n",
    "<b>Goal</b>: To download (using SparQL queries) <u>all WikiData entries that have VIAF ids</u>. Presumably, these are all the authors contained in WikiData, and their VIAF id will be used later on to link them to our dataset. "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "80789b5c",
   "metadata": {},
   "source": [
    "A. Get all WikiData entries with VIAF ID."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "8af61a82",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "It took 70.46 seconds.\n"
     ]
    }
   ],
   "source": [
    "query_string = \"\"\"\n",
    "        SELECT DISTINCT ?item ?viaf  # return QID and VIAF ID \n",
    "WHERE \n",
    "{ ?item wdt:P214 ?viaf. # select all the items in WikiData that have VIAF ID\n",
    "}\n",
    "        \"\"\"\n",
    "start = time.time()\n",
    "items_with_viaf = return_sparql_query_results(query_string) # run the query and get results\n",
    "print(\"It took \"+str(np.round(time.time()-start,2))+ \" seconds.\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "7e2b43e6",
   "metadata": {},
   "source": [
    "B. Process results. We end up only keeping their QID (WikiData identification) and VIAF id."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "eacd2ee9",
   "metadata": {},
   "outputs": [],
   "source": [
    "items_with_viaf = pd.DataFrame(items_with_viaf[\"results\"][\"bindings\"])\n",
    "items_with_viaf[\"item\"] = items_with_viaf[\"item\"].apply(lambda x: x[\"value\"].split(\"/\")[-1]) # keep only QID\n",
    "items_with_viaf[\"viaf\"] = items_with_viaf[\"viaf\"].apply(lambda x: x[\"value\"]) # keep only VIAF ID\n",
    "items_with_viaf.columns = [\"QID\", \"viaf_id\"] # rename columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "78e00b3b",
   "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>Q119346</td>\n",
       "      <td>24252</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Q270705</td>\n",
       "      <td>16565</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Q507746</td>\n",
       "      <td>6650</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Q573519</td>\n",
       "      <td>2274</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Q691595</td>\n",
       "      <td>3710150325578510090002</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       QID                 viaf_id\n",
       "0  Q119346                   24252\n",
       "1  Q270705                   16565\n",
       "2  Q507746                    6650\n",
       "3  Q573519                    2274\n",
       "4  Q691595  3710150325578510090002"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "items_with_viaf.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "ab09c47a",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "In WikiData, there are 3306052 items with VIAF ID.\n"
     ]
    }
   ],
   "source": [
    "print(\"In WikiData, there are \"+str(len(items_with_viaf))+\" items with VIAF ID.\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a5ddbf5a",
   "metadata": {},
   "source": [
    "C. Save results."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "f106b3af",
   "metadata": {},
   "outputs": [],
   "source": [
    "# save with Pickle\n",
    "with open(large_location_to_save + \"items_with_viaf_wikidata.pkl\", \"wb\") as output_file:\n",
    "    pkl.dump(items_with_viaf, output_file)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "6ba2ab3a",
   "metadata": {},
   "source": [
    "## 2. Recreate Fairbook dataset\n",
    "<b>Goal</b>: To cut down the dataset the same way that they did in <u>FairBook</u>. In this section, we are using code from https://github.com/rahmanidashti/FairBook."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "bdaded2b",
   "metadata": {},
   "source": [
    "A. Read files. The ratings dataset is directly taken from http://www2.informatik.uni-freiburg.de/~cziegler/BX/."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "d1fb9d54",
   "metadata": {},
   "outputs": [],
   "source": [
    "dataset = pd.read_csv(\"data/ratings_books.csv\", low_memory=False).fillna(\"\")\n",
    "dataset.columns = ['User-ID', 'ISBN', 'Book-Rating']"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "2bb5a3ea",
   "metadata": {},
   "source": [
    "B. Print statistics."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "693c576a",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Original dataset statistics: \n",
      "> No. of users: 105283\n",
      "> No. of Books: 340556\n",
      "> No. of Interaction: 1149780\n"
     ]
    }
   ],
   "source": [
    "print(\"Original dataset statistics: \")\n",
    "print(f\"> No. of users: {len(dataset['User-ID'].unique())}\")\n",
    "print(f\"> No. of Books: {len(dataset['ISBN'].unique())}\")\n",
    "print(f\"> No. of Interaction: {dataset.shape[0]}\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ac56cb7d",
   "metadata": {},
   "source": [
    "C. Filter dataset."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "d58ba0f7",
   "metadata": {},
   "outputs": [],
   "source": [
    "def filter_rows_by_values(df, col, values):\n",
    "    return df[~df[col].isin(values)]"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "eb4af1b9",
   "metadata": {},
   "source": [
    "C.1. Remove implicit (i.e. 0) ratings."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "2c2ba106",
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Explicit dataset statistics: \n",
      "> No. of users: 77805\n",
      "> No. of Books: 185973\n",
      "> No. of Interaction: 433671\n"
     ]
    }
   ],
   "source": [
    "dataset = filter_rows_by_values(dataset, \"Book-Rating\", [0])\n",
    "\n",
    "# statistics on explicit dataset\n",
    "print(\"Explicit dataset statistics: \")\n",
    "print(f\"> No. of users: {len(dataset['User-ID'].unique())}\")\n",
    "print(f\"> No. of Books: {len(dataset['ISBN'].unique())}\")\n",
    "print(f\"> No. of Interaction: {dataset.shape[0]}\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ac5cee96",
   "metadata": {},
   "source": [
    "C.2. Remove:\n",
    "1. Users with more than 200 ratings\n",
    "2. Users with less than 5 rarings\n",
    "3. Items with less than 5 ratings"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "d450b56f",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "The number of users with more than 200 interactions: 144\n"
     ]
    }
   ],
   "source": [
    "# To check if there is any user with more than 200 interaction in the preprocessed dataset\n",
    "# The correct output will be zero\n",
    "uid_value_counts = dataset['User-ID'].value_counts()\n",
    "print(\"The number of users with more than 200 interactions:\", len(uid_value_counts[uid_value_counts > 200]))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "b58c689f",
   "metadata": {},
   "outputs": [],
   "source": [
    "# To remove the users with fewer than 5 interaction we first count the number of interactino per user and add a new column (`Count`) in the dataframe.\n",
    "# This column shows the number of interaction per user in the dataset\n",
    "users_counts = dataset['User-ID'].value_counts()\n",
    "users_counts = users_counts.to_dict() #converts to dictionary\n",
    "dataset['Count'] = dataset['User-ID'].map(users_counts)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "33e3642c",
   "metadata": {},
   "outputs": [],
   "source": [
    "dataset = filter_rows_by_values(dataset, \"Count\", list(range(200, max(dataset['Count']) + 1)))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "5496bad0",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "New dataset statistics (users with interactions < 200): \n",
      "> No. of users: 77660\n",
      "> No. of Books: 156891\n",
      "> No. of Interaction: 364245\n"
     ]
    }
   ],
   "source": [
    "# statistics on explicit dataset after removing users with more than 200 int.\n",
    "print(f\"New dataset statistics (users with interactions < {200}): \")\n",
    "print(f\"> No. of users: {len(dataset['User-ID'].unique())}\")\n",
    "print(f\"> No. of Books: {len(dataset['ISBN'].unique())}\")\n",
    "print(f\"> No. of Interaction: {dataset.shape[0]}\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "9c5dbe79",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "The current number of user and item with < 5 interactions: \n",
      "No. of users < 5 ineractions: 63585\n",
      "No. of items < 5 ineractions: 119254\n",
      "The current number of user and item with < 5 interactions: \n",
      "No. of users < 5 ineractions: 5825\n",
      "No. of items < 5 ineractions: 1594\n",
      "The current number of user and item with < 5 interactions: \n",
      "No. of users < 5 ineractions: 432\n",
      "No. of items < 5 ineractions: 208\n",
      "The current number of user and item with < 5 interactions: \n",
      "No. of users < 5 ineractions: 69\n",
      "No. of items < 5 ineractions: 45\n",
      "The current number of user and item with < 5 interactions: \n",
      "No. of users < 5 ineractions: 19\n",
      "No. of items < 5 ineractions: 17\n",
      "The current number of user and item with < 5 interactions: \n",
      "No. of users < 5 ineractions: 11\n",
      "No. of items < 5 ineractions: 13\n",
      "The current number of user and item with < 5 interactions: \n",
      "No. of users < 5 ineractions: 6\n",
      "No. of items < 5 ineractions: 2\n",
      "The current number of user and item with < 5 interactions: \n",
      "No. of users < 5 ineractions: 2\n",
      "No. of items < 5 ineractions: 4\n",
      "The current number of user and item with < 5 interactions: \n",
      "No. of users < 5 ineractions: 1\n",
      "No. of items < 5 ineractions: 1\n",
      "The current number of user and item with < 5 interactions: \n",
      "No. of users < 5 ineractions: 0\n",
      "No. of items < 5 ineractions: 0\n"
     ]
    }
   ],
   "source": [
    "user_interaction, item_interaction = 1, 1\n",
    "\n",
    "while user_interaction != 0 or item_interaction != 0:\n",
    "    print(f\"The current number of user and item with < 5 interactions: \")\n",
    "    # user side fewer than ds_rate cheking\n",
    "    uid_value_counts = dataset['User-ID'].value_counts()\n",
    "    user_interaction = uid_value_counts[uid_value_counts < 5].count()\n",
    "    print(f\"No. of users < 5 ineractions: {user_interaction}\")\n",
    "\n",
    "    users_counts = dataset['User-ID'].value_counts()\n",
    "    users_counts = users_counts.to_dict() #converts to dictionary\n",
    "    dataset['Count'] = dataset['User-ID'].map(users_counts)\n",
    "\n",
    "    dataset = filter_rows_by_values(dataset, \"Count\", list(range(5)))\n",
    "\n",
    "    # item side fewer than ds_rate cheking\n",
    "    bid_value_counts = dataset['ISBN'].value_counts()\n",
    "    item_interaction = bid_value_counts[bid_value_counts < 5].count()\n",
    "    print(f\"No. of items < 5 ineractions: {item_interaction}\")\n",
    "\n",
    "    items_counts = dataset['ISBN'].value_counts()\n",
    "    items_counts = items_counts.to_dict() #converts to dictionary\n",
    "    dataset['Count'] = dataset['ISBN'].map(items_counts)\n",
    "\n",
    "    dataset = filter_rows_by_values(dataset, \"Count\", list(range(5)))"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "34797129",
   "metadata": {},
   "source": [
    "D. Print statistics after preprocessing."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "id": "41c44750",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "No. of users: 6358\n",
      "No. of Books: 6921\n",
      "No. of Interaction: 88552\n"
     ]
    }
   ],
   "source": [
    "# statistics on 5 rate explicit dataset (after pre-processing)\n",
    "print(f\"No. of users: {len(dataset['User-ID'].unique())}\")\n",
    "print(f\"No. of Books: {len(dataset['ISBN'].unique())}\")\n",
    "print(f\"No. of Interaction: {dataset.shape[0]}\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "b461efca",
   "metadata": {},
   "source": [
    "E. Save file."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "id": "566c1ed3",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Before we save the preprocessed explicit dataset (5Rate) we first remove the added column which is `Count`\n",
    "del dataset['Count']\n",
    "dataset.to_csv(location_to_save+\"fairbook_ratings.csv\")"
   ]
  },
  {
   "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": 18,
   "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": 6,
   "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": 7,
   "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": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "books.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "61d2a111",
   "metadata": {},
   "source": [
    "B. Choose \"unique\" authors based on exact author name."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "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": 9,
   "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": 10,
   "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": 10,
     "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": 12,
   "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": 28,
   "id": "8da839c3-ee24-4d62-a06a-fa16cfaaeeb2",
   "metadata": {},
   "outputs": [],
   "source": [
    "# x = authors[authors.author.apply(lambda x : not isEnglish(x))].iloc[0].author\n",
    "# b = x.encode('utf-8')\n",
    "# b.decode('utf-16'), x"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 61,
   "id": "07063d0e-af4b-4e89-8903-45c5082c1f18",
   "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>269</th>\n",
       "      <td>Isabel-Clara SimÃ³</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>316</th>\n",
       "      <td>Antoine de Saint-ExupÃ©ry</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>350</th>\n",
       "      <td>Eugeni Perea SimÃ³n</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>352</th>\n",
       "      <td>VicenÃ§ Villatoro</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>353</th>\n",
       "      <td>VÃ­ctor Mora</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>101717</th>\n",
       "      <td>FÃ?Â©lix de AzÃ?Âºa</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>101833</th>\n",
       "      <td>Bernard-Marie KoltÃ¨s</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>101839</th>\n",
       "      <td>Vitus B DrÃ¶scher</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>102010</th>\n",
       "      <td>JÃ¶rn Kalkbrenner</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>102012</th>\n",
       "      <td>GÃ¼nther Unser</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>1381 rows × 1 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                           author\n",
       "269            Isabel-Clara SimÃ³\n",
       "316     Antoine de Saint-ExupÃ©ry\n",
       "350           Eugeni Perea SimÃ³n\n",
       "352             VicenÃ§ Villatoro\n",
       "353                  VÃ­ctor Mora\n",
       "...                           ...\n",
       "101717        FÃ?Â©lix de AzÃ?Âºa\n",
       "101833      Bernard-Marie KoltÃ¨s\n",
       "101839          Vitus B DrÃ¶scher\n",
       "102010          JÃ¶rn Kalkbrenner\n",
       "102012             GÃ¼nther Unser\n",
       "\n",
       "[1381 rows x 1 columns]"
      ]
     },
     "execution_count": 61,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "authors[authors.author.apply(lambda x : not isEnglish(x))]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "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": 15,
   "id": "4e39f91c",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "There are  100642 latin authors out of 102023.\n"
     ]
    }
   ],
   "source": [
    "print(\"There are \", num_latin_authors,\"latin authors out of\", str(len(authors))+\".\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "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": 17,
   "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": 18,
   "id": "219a0448",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "There are  2502 unique single named authors out of 102023.\n"
     ]
    }
   ],
   "source": [
    "print(\"There are \", num_single_named_authors,\"unique single named authors out of\", str(len(authors))+\".\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "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": 26,
   "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;amp; Oetinger &amp;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 &amp; Oetinger &amp; 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": 26,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "books[books.author == \"Adams\"]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "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;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 &amp; 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": 30,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "books[books.author == \"Douglas Adams\"]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "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": 32,
     "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": 46,
   "id": "89072856",
   "metadata": {},
   "outputs": [],
   "source": [
    "def get_author_title(ISBN):\n",
    "    base_api_link = \"https://www.googleapis.com/books/v1/volumes?q=isbn:\"\n",
    "\n",
    "    # I need to add a header to the request. It's a dictionary, check what is needed in the google books api documentation\n",
    "    # check how to request multiple ISBNs at once\n",
    "    with urllib.request.urlopen(base_api_link + ISBN) as f:\n",
    "        text = f.read()\n",
    "    decoded_text = text.decode(\"utf-8\")\n",
    "    obj = json.loads(decoded_text) # deserializes decoded_text to a Python object\n",
    "    try:\n",
    "        volume_info = obj[\"items\"][0] \n",
    "    except (IndexError,KeyError) as e: \n",
    "        #print(help(e))\n",
    "        #print(e. __cause__)\n",
    "        \n",
    "        return(\"\",\"\")\n",
    "    title = volume_info[\"volumeInfo\"][\"title\"]\n",
    "    try:\n",
    "        authors = volume_info[\"volumeInfo\"][\"authors\"]\n",
    "        \n",
    "    except:\n",
    "        return(\"\",title)\n",
    "\n",
    "    return(authors,title)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "214d0f6a",
   "metadata": {},
   "source": [
    "B. Initialize the entries."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "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": null,
   "id": "97fba026",
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "start = time.time()\n",
    "for index,row in books.iloc[271000:].iterrows():\n",
    "    if row[\"alt_author\"]==\"\":\n",
    "        ISBN = row[\"ISBN\"]\n",
    "        print(row.title)\n",
    "        statement = True\n",
    "        i=0\n",
    "        while statement:\n",
    "\n",
    "            try:\n",
    "                authors, title = get_author_title(ISBN)\n",
    "                statement = False\n",
    "            except:\n",
    "                if i>50:\n",
    "                    statement = False\n",
    "                    print(\"IT FAILED MORE THAN 50 TIMES!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!\")\n",
    "                time.sleep(0.1)\n",
    "            i+=1  \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",
    "    if index%100==0:\n",
    "        print(time.time()-start, index)\n",
    "        start = time.time()\n",
    "        time.sleep(5)\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
}