relation_extraction/merge_sources_infoboxes.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import json\n",
    "import os\n",
    "import re\n",
    "import pandas as pd\n",
    "import numpy as np\n",
    "from bs4 import BeautifulSoup"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Load data  \n",
    "Load data into a dataframe to identify all the possible relationships that exist in the dataset from infoboxes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "scrolled": false
   },
   "outputs": [],
   "source": [
    "def load_infoboxes():\n",
    "\n",
    "    with open(\"info/infoboxes.wikia.json\", \"r\") as r:\n",
    "        infobox_wikia = json.load(r)\n",
    "\n",
    "    with open(\"info/infoboxes.gamepedia.json\", \"r\") as r:\n",
    "        infobox_gamepedia = json.load(r)\n",
    "\n",
    "    types = []\n",
    "    i = 0\n",
    "    for k in infobox_wikia:\n",
    "        for relation in infobox_wikia[k]:\n",
    "            values = infobox_wikia[k][relation]\n",
    "            for value in values:\n",
    "                types.append([k, relation, value[1], value[0], \"wikia\"])\n",
    "\n",
    "    for k in infobox_gamepedia:\n",
    "        for relation in infobox_gamepedia[k]:\n",
    "            values = infobox_gamepedia[k][relation]\n",
    "            for value in values:\n",
    "                types.append([k, relation, value[1], value[0], \"gamepedia\"])\n",
    "        \n",
    "    infobox = pd.DataFrame(types, columns=[\"page\", \"relation\", \"type\", \"value\", \"source\"])\n",
    "    return infobox\n",
    "\n",
    "infobox = load_infoboxes()\n",
    "infobox.relation.value_counts().describe()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Filter out rows with properties having less than `counts`"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "counts = 10\n",
    "\n",
    "def filter_(infobox, counts):\n",
    "    filtered = infobox.groupby('relation').filter(lambda x: len(x) >= counts)\n",
    "    return filtered\n",
    "\n",
    "filtered = filter_(infobox, counts)\n",
    "filtered.relation.value_counts().iloc[:5]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "filtered.head(10)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Genders  \n",
    "Let's start with genders"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "genders = infobox[(infobox[\"relation\"] == \"GENDER\")].copy() \\\n",
    "    .drop([\"relation\"], axis=1).set_index(\"page\")\n",
    "genders.columns = [\"type\",\"gender\", \"source\"]\n",
    "\n",
    "def get_tag(r):\n",
    "    if r[\"type\"] == \"string\":\n",
    "        return None\n",
    "    soup = BeautifulSoup(r[\"gender\"], \"lxml\")\n",
    "    return soup.body.next.name\n",
    "\n",
    "genders[\"tag\"] = genders.apply(get_tag,axis=1)\n",
    "print(genders.info())\n",
    "genders.sample(5)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Select properties where there are no `sup` or `br` tags, and inspect the remaining to see if they are valuable"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "genders = genders[(genders[\"tag\"] != \"sup\") & (genders[\"tag\"] != \"br\")]\n",
    "genders.tag.value_counts()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Seems like there is nothing valuable, so let's use only the strings properties, we'll need to clean them a bit though."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "genders = genders[(genders[\"type\"] == \"string\")]\n",
    "print(genders.info())\n",
    "\n",
    "def get_gender(values):\n",
    "    if len(values) == 1:\n",
    "        value = list(values)[0]\n",
    "        if value == 'Male' or value == 'Female':\n",
    "            return value\n",
    "    return 'Undefined'\n",
    "\n",
    "merged_genders = genders.groupby(genders.index)['gender'].apply(set).apply(get_gender)\n",
    "merged_genders.reset_index().sort_values(by=['page', 'gender']).to_csv(\"info/genders.csv\", index=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "```\n",
    "CREATE (:Gender {value:'Male'}), (:Gender {value:'Female'}), (:Gender {value:'Undefined'})\n",
    "```  \n",
    "\n",
    "```\n",
    "LOAD CSV FROM 'file:///genders.csv' AS line WITH line\n",
    "MATCH (p:Resource{url:line[0]}) \n",
    "MATCH (g:Gender{value:line[1]})\n",
    "MERGE (p)-[:IsA]->(g)\n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## First appereance"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "infobox = load_infoboxes()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "first_appereance = infobox[(infobox[\"relation\"] == \"FIRST_APPEARANCE\")].copy() \\\n",
    "    .drop([\"relation\"], axis=1).set_index(\"page\")\n",
    "first_appereance.columns = [\"type\",\"first\", \"source\"]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Get links"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "def get_link(r):\n",
    "    if r[\"type\"] == \"string\":\n",
    "        return np.nan\n",
    "    soup = BeautifulSoup(r[\"first\"], \"lxml\")\n",
    "    anchor = soup.find('a')\n",
    "    if not anchor:\n",
    "        return np.nan\n",
    "    href = anchor.get('href')\n",
    "    if not href:\n",
    "        return np.nan\n",
    "    pound = href.find(\"#\")\n",
    "    return href if pound == -1 else href[:pound]\n",
    "\n",
    "first_appereance[\"link\"] = first_appereance.apply(get_link, axis=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "year_re = re.compile(r\"^(\\.\\./)?([0-9]{4}.+[\\.html])\")\n",
    "\n",
    "def get_first_appereance(values):\n",
    "    page:str = None\n",
    "    year:str = None\n",
    "    for s in values:\n",
    "        s_results = year_re.search(s)\n",
    "        if s_results:\n",
    "            year = s_results.group(2)\n",
    "        else:\n",
    "            if s.startswith(\"../\"):\n",
    "                page = s[3:]\n",
    "            else:\n",
    "                page = s\n",
    "    return [page, year]\n",
    "first_appereance = first_appereance[pd.notna(first_appereance[\"link\"])]\n",
    "appereances = first_appereance.groupby(first_appereance.index)['link'].apply(set).apply(get_first_appereance)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "appereances = pd.DataFrame(appereances.values.tolist(), \n",
    "                           index=appereances.index, \n",
    "                           columns=[\"entity\",\"year\"])\n",
    "\n",
    "appereances[~pd.isna(appereances.entity)].reset_index().sort_values(by=['page', 'entity', 'year']).to_csv(\"info/first_appereance.csv\", index=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "```\n",
    "LOAD CSV WITH HEADERS FROM 'file:///first_appereance.csv' AS line WITH line\n",
    "MATCH (p1:Resource{url:line.page}) \n",
    "MATCH (p2:Resource{url:line.entity}) \n",
    "MERGE (p1)-[:FirstAppereance]->(p2)\n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Other appereances"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "infobox = load_infoboxes()\n",
    "infobox = filter_(infobox, 30)\n",
    "\n",
    "appereances_rels = [\"GAME\", \"APPEARANCES\", \"APPEARS_IN\", \"OTHER_MEDIA\"]\n",
    "other_appereances = infobox[infobox.relation.isin(appereances_rels)]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "def get_tag(r):\n",
    "    if r[\"type\"] == \"string\":\n",
    "        return None\n",
    "    soup = BeautifulSoup(r[\"value\"], \"lxml\")\n",
    "    return soup.body.next.name\n",
    "\n",
    "other_appereances[\"tag\"] = other_appereances.apply(get_tag, axis=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "other_appereances.sample(5)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Discard string and br"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "other_appereances = other_appereances[(~pd.isna(other_appereances.tag)) & (other_appereances.tag != \"br\")]\n",
    "other_appereances.tag.value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "year_re = re.compile(r\"^(\\.\\./)?([0-9]{4}.+[\\.html])\")\n",
    "\n",
    "def get_non_year_link(anchor):\n",
    "    page = anchor.get(\"href\")\n",
    "    if page:\n",
    "        if year_re.search(page):\n",
    "            return None\n",
    "        if page.startswith(\"../\"):\n",
    "            page = page[3:]\n",
    "        return page\n",
    "    return None\n",
    "\n",
    "def get_from_i(i_tags):\n",
    "    links = set()\n",
    "    for i in i_tags:\n",
    "        anchor = i.find('a')\n",
    "        if anchor:\n",
    "            page = get_non_year_link(anchor)\n",
    "            if page:\n",
    "                links.add(page)\n",
    "    return links\n",
    "\n",
    "def get_appereances(row):\n",
    "    tag = row[\"tag\"]\n",
    "    soup = BeautifulSoup(row[\"value\"], \"lxml\")\n",
    "    links = set()\n",
    "    if tag == \"i\":\n",
    "        i_tags = soup.findAll('i')\n",
    "        links.update(get_from_i(i_tags))\n",
    "    if tag == \"div\":\n",
    "        i_tags = soup.findAll('i',recursive=False)\n",
    "        if len(i_tags) > 0:\n",
    "            links.update(get_from_i(i_tags))\n",
    "        else:\n",
    "            anchors = soup.findAll('a')\n",
    "            for anchor in anchors:\n",
    "                page = get_non_year_link(anchor)\n",
    "                if page:\n",
    "                    links.add(page)\n",
    "    if tag == \"a\":\n",
    "        anchors = soup.findAll('a')\n",
    "        for anchor in anchors:\n",
    "            page = get_non_year_link(anchor)\n",
    "            if page:\n",
    "                links.add(page)\n",
    "    return links\n",
    "            \n",
    "\n",
    "#other_appereances[(other_appereances[\"tag\"] == \"i\")].apply(get_appereances, axis=1)\n",
    "other_appereances[\"apps\"] = other_appereances.apply(get_appereances, axis=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "from  itertools import chain\n",
    "\n",
    "final_appereances = pd.DataFrame({\n",
    "        \"page\": np.repeat(other_appereances.page.values, other_appereances.apps.str.len()),\n",
    "        \"source\": np.repeat(other_appereances.source.values, other_appereances.apps.str.len()),\n",
    "        \"appereance\": list(chain.from_iterable(other_appereances.apps))})\n",
    "# final_appereances.info()\n",
    "final_appereances.reset_index().sort_values(by=['page', 'appereance', 'source']).to_csv(\"info/other_appereances.csv\", index=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "```\n",
    "LOAD CSV WITH HEADERS FROM 'file:///other_appereances.csv' AS line WITH line\n",
    "MATCH (p1:Resource{url:line.page}) \n",
    "MATCH (p2:Resource{url:line.appereance}) \n",
    "MERGE (p1)-[:AppearsIn]->(p2)\n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Location"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "scrolled": false
   },
   "outputs": [],
   "source": [
    "infobox = load_infoboxes()\n",
    "list(infobox.relation.unique())\n",
    "\n",
    "\n",
    "locations = infobox[infobox.relation.isin([\"LOCATION\", \n",
    "                                           \"COUNTRY\", \n",
    "                                           \"REGION\", \n",
    "                                           \"HOMELAND\", \n",
    "                                           \"HOMETOWN\", \n",
    "                                           \"PLACE\"])]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "def get_tag(r):\n",
    "    if r[\"type\"] == \"string\":\n",
    "        return None\n",
    "    soup = BeautifulSoup(r[\"value\"], \"lxml\")\n",
    "    return soup.body.next.name\n",
    "\n",
    "locations[\"tag\"] = locations.apply(get_tag, axis=1)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Discard strings and brs, again"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "locations = locations[(~pd.isna(locations.tag)) & (locations.tag == \"a\")]\n",
    "print(locations.type.value_counts())\n",
    "locations.sample(10)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "def get_href(anchor:str):\n",
    "    href_pos = anchor.find(\"href\")\n",
    "    if href_pos == -1:\n",
    "        return None\n",
    "    html_pos = anchor.find(\".html\", href_pos)\n",
    "    if html_pos == -1:\n",
    "        return None\n",
    "    return anchor[href_pos + 6:html_pos + 5].removeprefix(\"../\")\n",
    "\n",
    "locations[\"href\"] = locations.value.apply(get_href)\n",
    "locations"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "saved_locations = locations[['page', 'relation', 'href']]\n",
    "saved_locations.columns = ['object', 'relation', 'page']\n",
    "saved_locations = saved_locations[saved_locations['page'].notna()]\n",
    "saved_locations = saved_locations[~saved_locations['page'].str.endswith(\"external.html\")]\n",
    "# saved_locations= saved_locations[saved_locations['page'].str.startswith(\".\")]\n",
    "saved_locations.sort_values(by=['object', 'relation', 'page']).to_csv(\"info/locations.csv\", index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "## Race\n",
    "race = infobox[infobox.relation.isin([\"RACE\"])]\n",
    "def get_tag(r):\n",
    "    if r[\"type\"] == \"string\":\n",
    "        return None\n",
    "    soup = BeautifulSoup(r[\"value\"], \"lxml\")\n",
    "    return soup.body.next.name\n",
    "\n",
    "race[\"tag\"] = race.apply(get_tag, axis=1)\n",
    "race = race[(~pd.isna(race.tag)) & (race.tag == \"a\")]\n",
    "\n",
    "race.sample(10)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "race['href'] = race.value.apply(get_href)\n",
    "race.sample(10)\n",
    "saved_race = race[['page', 'href']]\n",
    "saved_race.columns = ['entity', 'race']\n",
    "saved_race.sort_values(by=['entity', 'race']).to_csv(\"info/race.csv\", index=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Family"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "kindred = infobox[infobox.relation.isin([\"FAMILY\",\"KINDRED\"])]\n",
    "kindred.sample(10)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "from collections import defaultdict\n",
    "relationships = defaultdict(set)\n",
    "\n",
    "current_relationship = None\n",
    "for _, row in kindred.iterrows():\n",
    "    if row['source'] == 'wikia' and row['value'].startswith(\"<a\"):\n",
    "        current_relationship = get_href(row['value'])\n",
    "    elif row['source'] == 'wikia' and row['value'].startswith(\"(\"):\n",
    "        if current_relationship:\n",
    "            relationships[row['page']].add((current_relationship, row['value'].strip(\"()\").lower()))\n",
    "            current_relationship = None\n",
    "    elif row['source'] == 'gamepedia' and row['value'].startswith(\"<a\"):\n",
    "        current_relationship = get_href(row['value'])\n",
    "    elif row['source'] == 'gamepedia' and row['value'].startswith(\"<small>\"):\n",
    "        if current_relationship:\n",
    "            value = row['value'][len(\"<small>\"):-len(\"</small>\")]\n",
    "            if not value.startswith(\"<\"):\n",
    "                value = value.strip(\"()\").lower()\n",
    "                if not value.startswith(\"<\"):\n",
    "                    relationships[row['page']].add((current_relationship, value))\n",
    "            current_relationship = None\n",
    "        \n",
    "len(relationships)\n",
    "# relationships"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "rels = []\n",
    "for k, v in relationships.items():\n",
    "    for t in v:\n",
    "        rels.append((k, t[1], t[0]))\n",
    "\n",
    "rels_df = pd.DataFrame(rels, columns=['subject', 'relation', 'object'])\n",
    "rels_df.sort_values(by=['subject', 'relation', 'object']).to_csv(\"info/kindred.csv\", index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "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.10.12"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}