GenerateDataframe2019.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Read GraphDB"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 64,
   "metadata": {},
   "outputs": [],
   "source": [
    "from gastrodon import RemoteEndpoint,QName,ttl,URIRef,inline\n",
    "import pandas as pd\n",
    "import json\n",
    "import os"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 65,
   "metadata": {},
   "outputs": [],
   "source": [
    "from gastrodon import _parseQuery\n",
    "from SPARQLWrapper import SPARQLWrapper, N3\n",
    "from rdflib import Graph\n",
    "def describe(self, sparql:str):\n",
    "    return self._describe(sparql).serialize(format='n3').decode()\n",
    "\n",
    "def _describe(self, sparql:str):\n",
    "    that = endpoint._wrapper()\n",
    "    that.setQuery(endpoint._prepend_namespaces(sparql, _parseQuery))\n",
    "    that.setReturnFormat(N3)\n",
    "    results = that.query().convert()\n",
    "    g = Graph()\n",
    "    g.parse(data=results, format=\"n3\")\n",
    "    return g\n",
    "\n",
    "RemoteEndpoint.describe = describe\n",
    "RemoteEndpoint._describe = _describe"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 66,
   "metadata": {},
   "outputs": [],
   "source": [
    "namespaces_str = \"\"\"\n",
    "@prefix : <https://tac.nist.gov/tracks/SM-KBP/2018/ontologies/AidaDomainOntologiesCommon#> .\n",
    "@prefix aida: <https://tac.nist.gov/tracks/SM-KBP/2019/ontologies/InterchangeOntology#> .\n",
    "@prefix dc: <http://purl.org/dc/elements/1.1/> .\n",
    "@prefix domainOntology: <https://tac.nist.gov/tracks/SM-KBP/2019/ontologies/SeedlingOntology> .\n",
    "@prefix ldc: <https://tac.nist.gov/tracks/SM-KBP/2018/ontologies/LdcAnnotations#> .\n",
    "@prefix ldcOnt: <https://tac.nist.gov/tracks/SM-KBP/2019/ontologies/LDCOntology#> .\n",
    "@prefix owl: <http://www.w3.org/2002/07/owl#> .\n",
    "@prefix rdf: <http://www.w3.org/1999/02/22-rdf-syntax-ns#> .\n",
    "@prefix rdfs: <http://www.w3.org/2000/01/rdf-schema#> .\n",
    "@prefix skos: <http://www.w3.org/2004/02/skos/core#> .\n",
    "@prefix xsd: <http://www.w3.org/2001/XMLSchema#> .\n",
    "\"\"\""
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Params"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "tags": [
     "parameters"
    ]
   },
   "outputs": [],
   "source": [
    "endpoint_url = 'http://gaiadev01.isi.edu:7200/repositories'\n",
    "repo = 'jchen-test-ta1'\n",
    "version = '001'\n",
    "store_data_dir = 'store_data/' + repo # should exist already\n",
    "add_origin = False # Set True if this origin and origin labels needed by TA3 (slow)\n",
    "wikidata_sparql_endpoint = \"https://dsbox02.isi.edu:8888/bigdata/namespace/wdq/sparql\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 68,
   "metadata": {},
   "outputs": [],
   "source": [
    "if not os.path.isdir(store_data_dir):\n",
    "    os.makedirs(store_data_dir)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 69,
   "metadata": {},
   "outputs": [],
   "source": [
    "endpoint = RemoteEndpoint(url= endpoint_url + '/' + repo,\n",
    "                          prefixes=inline(namespaces_str).graph)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 70,
   "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>sys</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>http://www.rpi.edu</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>http://www.isi.edu/promoteConfidencesToTypeAss...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>http://www.rpi.edu-projectToSingleton</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>http://www.isi.edu/gaia/propagateLinksToClusters</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>http://www.isi.edu/clusters/identicalRelations</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>http://www.rpi.edu/fileType</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>http://www.rpi.edu/coreference</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>http://www.rpi.edu/EDL_Freebase</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>http://www.rpi.edu/EDL_FineGrained</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>http://www.rpi.edu/EDL_LORELEI_maxPool</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>http://www.isi.edu/compoundJustificationWrapper</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>http://www.rpi.edu/entity_representations</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                                  sys\n",
       "0                                  http://www.rpi.edu\n",
       "1   http://www.isi.edu/promoteConfidencesToTypeAss...\n",
       "2               http://www.rpi.edu-projectToSingleton\n",
       "3    http://www.isi.edu/gaia/propagateLinksToClusters\n",
       "4      http://www.isi.edu/clusters/identicalRelations\n",
       "5                         http://www.rpi.edu/fileType\n",
       "6                      http://www.rpi.edu/coreference\n",
       "7                     http://www.rpi.edu/EDL_Freebase\n",
       "8                  http://www.rpi.edu/EDL_FineGrained\n",
       "9              http://www.rpi.edu/EDL_LORELEI_maxPool\n",
       "10    http://www.isi.edu/compoundJustificationWrapper\n",
       "11          http://www.rpi.edu/entity_representations"
      ]
     },
     "execution_count": 70,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "system_list = endpoint.select('select distinct ?sys where {?s aida:system ?sys}')\n",
    "system_list"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 71,
   "metadata": {},
   "outputs": [],
   "source": [
    "def to_int(s):\n",
    "    return int(s) if isinstance(s, str) or isinstance(s, int) else -1"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Entities"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 72,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "@prefix ns1: <https://tac.nist.gov/tracks/SM-KBP/2019/ontologies/InterchangeOntology#> .\n",
      "@prefix rdf: <http://www.w3.org/1999/02/22-rdf-syntax-ns#> .\n",
      "@prefix rdfs: <http://www.w3.org/2000/01/rdf-schema#> .\n",
      "@prefix xml: <http://www.w3.org/XML/1998/namespace> .\n",
      "@prefix xsd: <http://www.w3.org/2001/XMLSchema#> .\n",
      "\n",
      "<http://www.isi.edu/gaia/assertions/00134707-ce4c-4a2f-910c-96a1e1614b49> rdf:subject <http://www.isi.edu/gaia/entities/2d26abd5-94c7-4028-a4df-3ce5d630d41c> .\n",
      "\n",
      "<http://www.isi.edu/gaia/assertions/8491415b-27c8-4651-822a-1d0eb0d83415> rdf:subject <http://www.isi.edu/gaia/entities/2d26abd5-94c7-4028-a4df-3ce5d630d41c> .\n",
      "\n",
      "<http://www.isi.edu/gaia/entities/2d26abd5-94c7-4028-a4df-3ce5d630d41c-cluster-projectedFromSingleton> ns1:prototype <http://www.isi.edu/gaia/entities/2d26abd5-94c7-4028-a4df-3ce5d630d41c> .\n",
      "\n",
      "<http://www.isi.edu/gaia/entities/2d26abd5-94c7-4028-a4df-3ce5d630d41c> a ns1:Entity ;\n",
      "    ns1:hasName \"contractor\" ;\n",
      "    ns1:informativeJustification _:f8dea7f740dc143f480f393912a28a512b2 ;\n",
      "    ns1:justifiedBy [ a ns1:TextJustification ;\n",
      "            ns1:confidence [ a ns1:Confidence ;\n",
      "                    ns1:confidenceValue 2.07588e-01 ;\n",
      "                    ns1:system <http://www.isi.edu/promoteConfidencesToTypeAssertions>,\n",
      "                        <http://www.rpi.edu> ] ;\n",
      "            ns1:endOffsetInclusive \"9847\"^^xsd:int ;\n",
      "            ns1:privateData [ a ns1:PrivateData ;\n",
      "                    ns1:jsonContent \"{\\\"fileType\\\":\\\"en\\\"}\" ;\n",
      "                    ns1:system <http://www.rpi.edu/fileType> ],\n",
      "                [ a ns1:PrivateData ;\n",
      "                    ns1:jsonContent \"{ \\\"justificationType\\\" : \\\"mention\\\"}\" ;\n",
      "                    ns1:system <http://www.rpi.edu> ] ;\n",
      "            ns1:source \"HC00017P3\" ;\n",
      "            ns1:sourceDocument \"HC0000HVC\" ;\n",
      "            ns1:startOffset \"9838\"^^xsd:int ;\n",
      "            ns1:system <http://www.rpi.edu> ],\n",
      "        _:f8dea7f740dc143f480f393912a28a512b2 ;\n",
      "    ns1:privateData [ a ns1:PrivateData ;\n",
      "            ns1:jsonContent \"{\\\"fileType\\\":\\\"en\\\"}\" ;\n",
      "            ns1:system <http://www.rpi.edu/fileType> ],\n",
      "        [ a ns1:PrivateData ;\n",
      "            ns1:jsonContent \"{\\\"coreference\\\":\\\"HC0000HVC_NIL90000427\\\"}\" ;\n",
      "            ns1:system <http://www.rpi.edu/coreference> ],\n",
      "        [ a ns1:PrivateData ;\n",
      "            ns1:jsonContent \"{\\\"coreference\\\":\\\"HC0000HVC_NIL90000427\\\"}\" ;\n",
      "            ns1:system <http://www.rpi.edu/coreference> ],\n",
      "        [ a ns1:PrivateData ;\n",
      "            ns1:jsonContent \"{\\\"fileType\\\":\\\"en\\\"}\" ;\n",
      "            ns1:system <http://www.rpi.edu/fileType> ] ;\n",
      "    ns1:system <http://www.rpi.edu> .\n",
      "\n",
      "[] ns1:clusterMember <http://www.isi.edu/gaia/entities/2d26abd5-94c7-4028-a4df-3ce5d630d41c> .\n",
      "\n",
      "_:f8dea7f740dc143f480f393912a28a512b10 a ns1:Confidence ;\n",
      "    ns1:confidenceValue 4.1518e-02 ;\n",
      "    ns1:system <http://www.isi.edu/promoteConfidencesToTypeAssertions>,\n",
      "        <http://www.rpi.edu> .\n",
      "\n",
      "_:f8dea7f740dc143f480f393912a28a512b11 a ns1:PrivateData ;\n",
      "    ns1:jsonContent \"{\\\"fileType\\\":\\\"en\\\"}\" ;\n",
      "    ns1:system <http://www.rpi.edu/fileType> .\n",
      "\n",
      "_:f8dea7f740dc143f480f393912a28a512b12 a ns1:PrivateData ;\n",
      "    ns1:jsonContent \"{ \\\"justificationType\\\" : \\\"mention\\\"}\" ;\n",
      "    ns1:system <http://www.rpi.edu> .\n",
      "\n",
      "_:f8dea7f740dc143f480f393912a28a512b2 a ns1:TextJustification ;\n",
      "    ns1:confidence _:f8dea7f740dc143f480f393912a28a512b10 ;\n",
      "    ns1:endOffsetInclusive \"9847\"^^xsd:int ;\n",
      "    ns1:privateData _:f8dea7f740dc143f480f393912a28a512b11,\n",
      "        _:f8dea7f740dc143f480f393912a28a512b12 ;\n",
      "    ns1:source \"HC00017P3\" ;\n",
      "    ns1:sourceDocument \"HC0000HVC\" ;\n",
      "    ns1:startOffset \"9838\"^^xsd:int ;\n",
      "    ns1:system <http://www.rpi.edu> .\n",
      "\n",
      "\n"
     ]
    }
   ],
   "source": [
    "print(endpoint.describe(\"\"\"\n",
    "DESCRIBE ?e {\n",
    "    ?e a aida:Entity ;\n",
    "       aida:system <http://www.rpi.edu>\n",
    "}\n",
    "LIMIT 1\n",
    "\"\"\"))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Issues:\n",
    "\n",
    "1. We don't have `prefLabel` any more.\n",
    "2. Some entities contain more than one external link. E.g. entity `<http://www.isi.edu/gaia/entities/0007bd9f-dfc2-4c5c-ab44-18a86c7be089>` contains two external link, one is `m.07t21` \"Ukraine\" and one is `m.05vz3zq` \"Soviet Union\"."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 73,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(739, 1)"
      ]
     },
     "execution_count": 73,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "entities = endpoint.select(\"\"\"\n",
    "SELECT DISTINCT ?e {\n",
    "    ?e a aida:Entity ;\n",
    "       aida:system <http://www.rpi.edu> ;\n",
    "}\n",
    "\"\"\")\n",
    "\n",
    "entities.shape"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Entity Freebase IDs"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 74,
   "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>e</th>\n",
       "      <th>fbid</th>\n",
       "      <th>fbid_score_avg</th>\n",
       "      <th>fbid_score_max</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>http://www.isi.edu/gaia/entities/2c120082-b098...</td>\n",
       "      <td>(m.09c7w0,)</td>\n",
       "      <td>(0.4483456767,)</td>\n",
       "      <td>(0.9721111059,)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>http://www.isi.edu/gaia/entities/330d72d3-d391...</td>\n",
       "      <td>(m.0840w,)</td>\n",
       "      <td>(0.7852166295,)</td>\n",
       "      <td>(0.7852166295,)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>http://www.isi.edu/gaia/entities/3523b0d0-a9e5...</td>\n",
       "      <td>(m.0840w,)</td>\n",
       "      <td>(0.7079921365,)</td>\n",
       "      <td>(0.7079921365,)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>http://www.isi.edu/gaia/entities/46cee7f7-c961...</td>\n",
       "      <td>(m.0g9zsyz,)</td>\n",
       "      <td>(0.9074373543,)</td>\n",
       "      <td>(0.9074373543,)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>http://www.isi.edu/gaia/entities/52d7e9ed-db5e...</td>\n",
       "      <td>(m.0840w,)</td>\n",
       "      <td>(0.8988551658,)</td>\n",
       "      <td>(0.9914882779,)</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                                   e          fbid  \\\n",
       "0  http://www.isi.edu/gaia/entities/2c120082-b098...   (m.09c7w0,)   \n",
       "1  http://www.isi.edu/gaia/entities/330d72d3-d391...    (m.0840w,)   \n",
       "2  http://www.isi.edu/gaia/entities/3523b0d0-a9e5...    (m.0840w,)   \n",
       "3  http://www.isi.edu/gaia/entities/46cee7f7-c961...  (m.0g9zsyz,)   \n",
       "4  http://www.isi.edu/gaia/entities/52d7e9ed-db5e...    (m.0840w,)   \n",
       "\n",
       "    fbid_score_avg   fbid_score_max  \n",
       "0  (0.4483456767,)  (0.9721111059,)  \n",
       "1  (0.7852166295,)  (0.7852166295,)  \n",
       "2  (0.7079921365,)  (0.7079921365,)  \n",
       "3  (0.9074373543,)  (0.9074373543,)  \n",
       "4  (0.8988551658,)  (0.9914882779,)  "
      ]
     },
     "execution_count": 74,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "def getFBIDs(s):\n",
    "    if s:\n",
    "        fbids = []\n",
    "        avg_scores = []\n",
    "        max_scores = []\n",
    "        fbids_json = json.loads(s).get('freebase_link')\n",
    "        fbids_keys = fbids_json.keys()\n",
    "    #     sorted_fbids = sorted(fbids, key = lambda fbid:(fbids_json.get(fbid).get('average_score')))\n",
    "    #     return tuple(sorted_fbids)\n",
    "        for fbid in fbids_keys:\n",
    "            fbids.append(fbid)\n",
    "            avg_scores.append(fbids_json.get(fbid).get('average_score'))\n",
    "            max_scores.append(fbids_json.get(fbid).get('max_score'))\n",
    "        return pd.Series({'fbid': tuple(fbids), 'fbid_score_avg': tuple(avg_scores), 'fbid_score_max': tuple(max_scores)})\n",
    "    else:\n",
    "        return pd.Series({'fbid': tuple([]), 'fbid_score_avg': tuple([]), 'fbid_score_max': tuple([])})\n",
    "    \n",
    "    \n",
    "df = endpoint.select(\"\"\"\n",
    "SELECT DISTINCT ?e ?fbid {\n",
    "    ?e a aida:Entity ;\n",
    "       aida:system <http://www.rpi.edu> ;\n",
    "       aida:privateData [\n",
    "            aida:jsonContent ?fbid ;\n",
    "            aida:system <http://www.rpi.edu/EDL_Freebase>\n",
    "        ]\n",
    "}\n",
    "\"\"\")\n",
    "\n",
    "# df.fbid = df.fbid.apply(lambda s: getFBIDs(s) if s else None)\n",
    "df[['fbid', 'fbid_score_avg', 'fbid_score_max']] = df.fbid.apply(getFBIDs)\n",
    "df = df.astype({\n",
    "    'e': str, 'fbid': object, 'fbid_score_avg': object, 'fbid_score_max': object\n",
    "})\n",
    "rpi_external = df\n",
    "rpi_external.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Find unique freebase IDs"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 75,
   "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>fbid</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>m.0c2105</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>m.02gjcx</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>m.061456</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>m.02wz7x</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>m.0j0k</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       fbid\n",
       "0  m.0c2105\n",
       "1  m.02gjcx\n",
       "2  m.061456\n",
       "3  m.02wz7x\n",
       "4    m.0j0k"
      ]
     },
     "execution_count": 75,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "fbids = set([])\n",
    "fbidss = rpi_external.fbid.tolist()\n",
    "for fbid_list in fbidss:\n",
    "    for fbid in fbid_list:\n",
    "        fbids.add(fbid)\n",
    "d = {'fbid': list(fbids)}\n",
    "rpi_fbid = pd.DataFrame(data=d)\n",
    "rpi_fbid.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Entity Targets"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 76,
   "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>e</th>\n",
       "      <th>targets</th>\n",
       "      <th>target_scores</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>http://www.isi.edu/gaia/entities/00029697-0740...</td>\n",
       "      <td>(LDC2019E43:2971316, LDC2019E43:6252001, LDC20...</td>\n",
       "      <td>(0.0003, 1.0, 0.0005066223)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>http://www.isi.edu/gaia/entities/021ed99f-8060...</td>\n",
       "      <td>(LDC2019E43:8054100, LDC2019E43:1213721, LDC20...</td>\n",
       "      <td>(0.0001, 0.0076, 1.0, 0.0001)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>http://www.isi.edu/gaia/entities/0c80ef93-559a...</td>\n",
       "      <td>(LDC2019E43:4187204, LDC2019E43:1668284, LDC20...</td>\n",
       "      <td>(0.0003028468, 0.03174603, 0.0005028437, 0.996...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>http://www.isi.edu/gaia/entities/15703b9b-2267...</td>\n",
       "      <td>(LDC2019E43:3577718, LDC2019E43:479441, LDC201...</td>\n",
       "      <td>(0.0004, 0.0004, 0.008547008, 1.0)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>http://www.isi.edu/gaia/entities/1d4273b1-b7a2...</td>\n",
       "      <td>(LDC2019E43:1268737, LDC2019E43:1252558)</td>\n",
       "      <td>(0.0027, 0.9986)</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                                   e  \\\n",
       "0  http://www.isi.edu/gaia/entities/00029697-0740...   \n",
       "1  http://www.isi.edu/gaia/entities/021ed99f-8060...   \n",
       "2  http://www.isi.edu/gaia/entities/0c80ef93-559a...   \n",
       "3  http://www.isi.edu/gaia/entities/15703b9b-2267...   \n",
       "4  http://www.isi.edu/gaia/entities/1d4273b1-b7a2...   \n",
       "\n",
       "                                             targets  \\\n",
       "0  (LDC2019E43:2971316, LDC2019E43:6252001, LDC20...   \n",
       "1  (LDC2019E43:8054100, LDC2019E43:1213721, LDC20...   \n",
       "2  (LDC2019E43:4187204, LDC2019E43:1668284, LDC20...   \n",
       "3  (LDC2019E43:3577718, LDC2019E43:479441, LDC201...   \n",
       "4           (LDC2019E43:1268737, LDC2019E43:1252558)   \n",
       "\n",
       "                                       target_scores  \n",
       "0                        (0.0003, 1.0, 0.0005066223)  \n",
       "1                      (0.0001, 0.0076, 1.0, 0.0001)  \n",
       "2  (0.0003028468, 0.03174603, 0.0005028437, 0.996...  \n",
       "3                 (0.0004, 0.0004, 0.008547008, 1.0)  \n",
       "4                                   (0.0027, 0.9986)  "
      ]
     },
     "execution_count": 76,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = endpoint.select(\"\"\"\n",
    "select ?e ?target ?score {\n",
    "SELECT distinct ?e ?target (MAX(?cv) as ?score)  {\n",
    "    ?e a aida:Entity .\n",
    "    ?e aida:system <http://www.rpi.edu> .\n",
    "    ?e aida:link ?link .\n",
    "    ?link aida:linkTarget ?target .\n",
    "    ?link aida:confidence ?conf .\n",
    "    ?conf aida:confidenceValue ?cv .\n",
    "} group by ?e ?target\n",
    "}\n",
    "\"\"\")\n",
    "\n",
    "def merge_targets(table):\n",
    "    if len(table.index) > 0:\n",
    "        targets = tuple(table['target'].tolist())\n",
    "        scores = tuple(table['score'].tolist())\n",
    "        return pd.Series({'targets': targets, 'target_scores': scores})\n",
    "    else:\n",
    "        return pd.Series({'targets': tuple([]), 'target_scores': tuple([])})\n",
    "df = df.groupby('e')['target', 'score'].apply(merge_targets).reset_index()\n",
    "df = df.astype({\n",
    "    'e': str, 'targets': object, 'target_scores': object\n",
    "})\n",
    "df_target = df\n",
    "df_target.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Entity Justifications"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 77,
   "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>e</th>\n",
       "      <th>type</th>\n",
       "      <th>label</th>\n",
       "      <th>target</th>\n",
       "      <th>source</th>\n",
       "      <th>start</th>\n",
       "      <th>end</th>\n",
       "      <th>justificationType</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>http://www.isi.edu/gaia/entities/2d26abd5-94c7...</td>\n",
       "      <td>ldcOnt:TTL</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>HC00017P3</td>\n",
       "      <td>9838</td>\n",
       "      <td>9847</td>\n",
       "      <td>mention</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>http://www.isi.edu/gaia/entities/ee5a9d74-0942...</td>\n",
       "      <td>ldcOnt:PER.Politician.HeadOfGovernment</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>HC00017P3</td>\n",
       "      <td>3280</td>\n",
       "      <td>3295</td>\n",
       "      <td>mention</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>http://www.isi.edu/gaia/entities/58fd6e68-88aa...</td>\n",
       "      <td>ldcOnt:GPE.ProvinceState.ProvinceState</td>\n",
       "      <td>None</td>\n",
       "      <td>LDC2019E43:4699848</td>\n",
       "      <td>HC00017P3</td>\n",
       "      <td>9537</td>\n",
       "      <td>9542</td>\n",
       "      <td>mention</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>http://www.isi.edu/gaia/entities/58fd6e68-88aa...</td>\n",
       "      <td>ldcOnt:GPE.ProvinceState.ProvinceState</td>\n",
       "      <td>None</td>\n",
       "      <td>LDC2019E43:1545739</td>\n",
       "      <td>HC00017P3</td>\n",
       "      <td>9537</td>\n",
       "      <td>9542</td>\n",
       "      <td>mention</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>http://www.isi.edu/gaia/entities/58fd6e68-88aa...</td>\n",
       "      <td>ldcOnt:GPE.ProvinceState.ProvinceState</td>\n",
       "      <td>None</td>\n",
       "      <td>LDC2019E43:1269750</td>\n",
       "      <td>HC00017P3</td>\n",
       "      <td>9537</td>\n",
       "      <td>9542</td>\n",
       "      <td>mention</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                                   e  \\\n",
       "0  http://www.isi.edu/gaia/entities/2d26abd5-94c7...   \n",
       "1  http://www.isi.edu/gaia/entities/ee5a9d74-0942...   \n",
       "2  http://www.isi.edu/gaia/entities/58fd6e68-88aa...   \n",
       "3  http://www.isi.edu/gaia/entities/58fd6e68-88aa...   \n",
       "4  http://www.isi.edu/gaia/entities/58fd6e68-88aa...   \n",
       "\n",
       "                                     type label              target  \\\n",
       "0                              ldcOnt:TTL  None                None   \n",
       "1  ldcOnt:PER.Politician.HeadOfGovernment  None                None   \n",
       "2  ldcOnt:GPE.ProvinceState.ProvinceState  None  LDC2019E43:4699848   \n",
       "3  ldcOnt:GPE.ProvinceState.ProvinceState  None  LDC2019E43:1545739   \n",
       "4  ldcOnt:GPE.ProvinceState.ProvinceState  None  LDC2019E43:1269750   \n",
       "\n",
       "      source  start   end justificationType  \n",
       "0  HC00017P3   9838  9847           mention  \n",
       "1  HC00017P3   3280  3295           mention  \n",
       "2  HC00017P3   9537  9542           mention  \n",
       "3  HC00017P3   9537  9542           mention  \n",
       "4  HC00017P3   9537  9542           mention  "
      ]
     },
     "execution_count": 77,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = endpoint.select(\"\"\"\n",
    "SELECT DISTINCT ?e ?type ?label ?target ?source ?start ?end ?justificationType {\n",
    "    ?e a aida:Entity ;\n",
    "       aida:system <http://www.rpi.edu> ;\n",
    "       ^rdf:subject [\n",
    "        a rdf:Statement ;\n",
    "        rdf:predicate rdf:type ;\n",
    "        rdf:object ?type ;\n",
    "        aida:justifiedBy ?justification ]\n",
    "    OPTIONAL { ?justification aida:privateData [\n",
    "            aida:jsonContent ?label ;\n",
    "            aida:system <http://www.rpi.edu/EDL_Translation> ]}\n",
    "    OPTIONAL { ?e aida:link/aida:linkTarget ?target }\n",
    "    OPTIONAL { ?justification aida:source ?source }\n",
    "    OPTIONAL { ?justification aida:startOffset ?start }\n",
    "    OPTIONAL { ?justification aida:endOffsetInclusive ?end }\n",
    "    OPTIONAL { ?justification aida:privateData [ \n",
    "            aida:system <http://www.rpi.edu> ;\n",
    "            aida:jsonContent ?justificationType ] }\n",
    "}\n",
    "\"\"\")\n",
    "df.start = df.start.apply(to_int)\n",
    "df.end = df.end.apply(to_int)\n",
    "df.justificationType = df.justificationType.apply(lambda s: json.loads(s).get('justificationType'))\n",
    "df.label = df.label.apply(lambda s: tuple(json.loads(s).get('translation')) if s else None)\n",
    "df = df.astype({\n",
    "    'e': str, 'type': str, 'target': str, 'source': str, 'start': int, 'end': int, 'justificationType': str\n",
    "})\n",
    "rpi_entity_with_justification = df\n",
    "rpi_entity_with_justification.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Entity Types"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 78,
   "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>e</th>\n",
       "      <th>type</th>\n",
       "      <th>name</th>\n",
       "      <th>text</th>\n",
       "      <th>source</th>\n",
       "      <th>target</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>http://www.isi.edu/gaia/entities/2d26abd5-94c7...</td>\n",
       "      <td>ldcOnt:TTL</td>\n",
       "      <td>contractor</td>\n",
       "      <td>None</td>\n",
       "      <td>HC00017P3</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>http://www.isi.edu/gaia/entities/ee5a9d74-0942...</td>\n",
       "      <td>ldcOnt:PER.Politician.HeadOfGovernment</td>\n",
       "      <td>Jayashree Lakhan</td>\n",
       "      <td>None</td>\n",
       "      <td>HC00017P3</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>http://www.isi.edu/gaia/entities/58fd6e68-88aa...</td>\n",
       "      <td>ldcOnt:GPE.ProvinceState.ProvinceState</td>\n",
       "      <td>India</td>\n",
       "      <td>None</td>\n",
       "      <td>HC00017P3</td>\n",
       "      <td>LDC2019E43:4699848</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>http://www.isi.edu/gaia/entities/58fd6e68-88aa...</td>\n",
       "      <td>ldcOnt:GPE.ProvinceState.ProvinceState</td>\n",
       "      <td>India</td>\n",
       "      <td>None</td>\n",
       "      <td>HC00017P3</td>\n",
       "      <td>LDC2019E43:1545739</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>http://www.isi.edu/gaia/entities/58fd6e68-88aa...</td>\n",
       "      <td>ldcOnt:GPE.ProvinceState.ProvinceState</td>\n",
       "      <td>India</td>\n",
       "      <td>None</td>\n",
       "      <td>HC00017P3</td>\n",
       "      <td>LDC2019E43:1269750</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                                   e  \\\n",
       "0  http://www.isi.edu/gaia/entities/2d26abd5-94c7...   \n",
       "1  http://www.isi.edu/gaia/entities/ee5a9d74-0942...   \n",
       "2  http://www.isi.edu/gaia/entities/58fd6e68-88aa...   \n",
       "3  http://www.isi.edu/gaia/entities/58fd6e68-88aa...   \n",
       "4  http://www.isi.edu/gaia/entities/58fd6e68-88aa...   \n",
       "\n",
       "                                     type              name  text     source  \\\n",
       "0                              ldcOnt:TTL        contractor  None  HC00017P3   \n",
       "1  ldcOnt:PER.Politician.HeadOfGovernment  Jayashree Lakhan  None  HC00017P3   \n",
       "2  ldcOnt:GPE.ProvinceState.ProvinceState             India  None  HC00017P3   \n",
       "3  ldcOnt:GPE.ProvinceState.ProvinceState             India  None  HC00017P3   \n",
       "4  ldcOnt:GPE.ProvinceState.ProvinceState             India  None  HC00017P3   \n",
       "\n",
       "               target  \n",
       "0                None  \n",
       "1                None  \n",
       "2  LDC2019E43:4699848  \n",
       "3  LDC2019E43:1545739  \n",
       "4  LDC2019E43:1269750  "
      ]
     },
     "execution_count": 78,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = endpoint.select(\"\"\"\n",
    "SELECT DISTINCT ?e ?type ?name ?text ?source ?target {\n",
    "    ?e a aida:Entity ;\n",
    "       aida:justifiedBy/aida:source ?source ;\n",
    "       aida:system <http://www.rpi.edu> .\n",
    "    ?statement a rdf:Statement ;\n",
    "               rdf:subject ?e ;\n",
    "               rdf:predicate rdf:type ;\n",
    "               rdf:object ?type .\n",
    "    OPTIONAL { ?e aida:hasName ?name }\n",
    "    OPTIONAL { ?e aida:textValue ?text }\n",
    "    OPTIONAL { ?e aida:link/aida:linkTarget ?target }\n",
    "}\n",
    "\"\"\")\n",
    "df = df.astype({\n",
    "    'e': str, 'type': str, 'name': str, 'source': str, 'text': str, 'target': str\n",
    "})\n",
    "rpi_entity_valid = df\n",
    "rpi_entity_valid.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Relations"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 79,
   "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>e</th>\n",
       "      <th>type</th>\n",
       "      <th>source</th>\n",
       "      <th>start</th>\n",
       "      <th>end</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>http://www.isi.edu/gaia/relations/f1e0222d-5ec...</td>\n",
       "      <td>ldcOnt:Physical.Resident.Resident</td>\n",
       "      <td>HC00017P3</td>\n",
       "      <td>2925</td>\n",
       "      <td>2977</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>http://www.isi.edu/gaia/relations/b4fd8acf-686...</td>\n",
       "      <td>ldcOnt:Physical.LocatedNear</td>\n",
       "      <td>HC00017P3</td>\n",
       "      <td>1552</td>\n",
       "      <td>1673</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>http://www.isi.edu/gaia/relations/6e53c7fa-d16...</td>\n",
       "      <td>ldcOnt:PersonalSocial.Unspecified</td>\n",
       "      <td>HC00017P3</td>\n",
       "      <td>1421</td>\n",
       "      <td>1549</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>http://www.isi.edu/gaia/relations/62dbcec8-068...</td>\n",
       "      <td>ldcOnt:Measurement.Size.Count</td>\n",
       "      <td>HC00017P3</td>\n",
       "      <td>4956</td>\n",
       "      <td>5148</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>http://www.isi.edu/gaia/relations/eb01c193-c7d...</td>\n",
       "      <td>ldcOnt:Physical.LocatedNear</td>\n",
       "      <td>HC00017P3</td>\n",
       "      <td>2081</td>\n",
       "      <td>2277</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                                   e  \\\n",
       "0  http://www.isi.edu/gaia/relations/f1e0222d-5ec...   \n",
       "1  http://www.isi.edu/gaia/relations/b4fd8acf-686...   \n",
       "2  http://www.isi.edu/gaia/relations/6e53c7fa-d16...   \n",
       "3  http://www.isi.edu/gaia/relations/62dbcec8-068...   \n",
       "4  http://www.isi.edu/gaia/relations/eb01c193-c7d...   \n",
       "\n",
       "                                type     source  start   end  \n",
       "0  ldcOnt:Physical.Resident.Resident  HC00017P3   2925  2977  \n",
       "1        ldcOnt:Physical.LocatedNear  HC00017P3   1552  1673  \n",
       "2  ldcOnt:PersonalSocial.Unspecified  HC00017P3   1421  1549  \n",
       "3      ldcOnt:Measurement.Size.Count  HC00017P3   4956  5148  \n",
       "4        ldcOnt:Physical.LocatedNear  HC00017P3   2081  2277  "
      ]
     },
     "execution_count": 79,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = endpoint.select(\"\"\"\n",
    "SELECT DISTINCT ?e ?type ?source ?start ?end {\n",
    "    ?e a aida:Relation ;\n",
    "       aida:system <http://www.rpi.edu> .\n",
    "    ?statement a rdf:Statement ;\n",
    "               rdf:subject ?e ;\n",
    "               rdf:predicate rdf:type ;\n",
    "               rdf:object ?type ;\n",
    "               aida:justifiedBy ?justification \n",
    "    OPTIONAL { ?justification aida:source ?source }\n",
    "    OPTIONAL { ?justification aida:startOffset ?start }\n",
    "    OPTIONAL { ?justification aida:endOffsetInclusive ?end }\n",
    "}\n",
    "\"\"\")\n",
    "df.start = df.start.apply(to_int)\n",
    "df.end = df.end.apply(to_int)\n",
    "df = df.astype({\n",
    "    'e': str, 'type': str, 'source': str, 'start': int, 'end': int\n",
    "})\n",
    "rpi_relation = df\n",
    "rpi_relation.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 80,
   "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>e</th>\n",
       "      <th>p</th>\n",
       "      <th>o</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>http://www.isi.edu/gaia/relations/f1e0222d-5ec...</td>\n",
       "      <td>ldcOnt:Physical.Resident.Resident_Resident</td>\n",
       "      <td>http://www.isi.edu/gaia/entities/f44b5651-cd1a...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>http://www.isi.edu/gaia/relations/f1e0222d-5ec...</td>\n",
       "      <td>ldcOnt:Physical.Resident.Resident_Place</td>\n",
       "      <td>http://www.isi.edu/gaia/entities/a3a208a9-a5df...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>http://www.isi.edu/gaia/relations/b4fd8acf-686...</td>\n",
       "      <td>ldcOnt:Physical.LocatedNear_Place</td>\n",
       "      <td>http://www.isi.edu/gaia/entities/491e2d52-5772...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>http://www.isi.edu/gaia/relations/b4fd8acf-686...</td>\n",
       "      <td>ldcOnt:Physical.LocatedNear_EntityOrFiller</td>\n",
       "      <td>http://www.isi.edu/gaia/entities/a3a208a9-a5df...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>http://www.isi.edu/gaia/relations/6e53c7fa-d16...</td>\n",
       "      <td>ldcOnt:PersonalSocial.Unspecified_Person</td>\n",
       "      <td>http://www.isi.edu/gaia/entities/cb9a7794-88c2...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                                   e  \\\n",
       "0  http://www.isi.edu/gaia/relations/f1e0222d-5ec...   \n",
       "1  http://www.isi.edu/gaia/relations/f1e0222d-5ec...   \n",
       "2  http://www.isi.edu/gaia/relations/b4fd8acf-686...   \n",
       "3  http://www.isi.edu/gaia/relations/b4fd8acf-686...   \n",
       "4  http://www.isi.edu/gaia/relations/6e53c7fa-d16...   \n",
       "\n",
       "                                            p  \\\n",
       "0  ldcOnt:Physical.Resident.Resident_Resident   \n",
       "1     ldcOnt:Physical.Resident.Resident_Place   \n",
       "2           ldcOnt:Physical.LocatedNear_Place   \n",
       "3  ldcOnt:Physical.LocatedNear_EntityOrFiller   \n",
       "4    ldcOnt:PersonalSocial.Unspecified_Person   \n",
       "\n",
       "                                                   o  \n",
       "0  http://www.isi.edu/gaia/entities/f44b5651-cd1a...  \n",
       "1  http://www.isi.edu/gaia/entities/a3a208a9-a5df...  \n",
       "2  http://www.isi.edu/gaia/entities/491e2d52-5772...  \n",
       "3  http://www.isi.edu/gaia/entities/a3a208a9-a5df...  \n",
       "4  http://www.isi.edu/gaia/entities/cb9a7794-88c2...  "
      ]
     },
     "execution_count": 80,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = endpoint.select(\"\"\"\n",
    "SELECT DISTINCT ?e ?p ?o {\n",
    "    ?e a aida:Relation ;\n",
    "       aida:system <http://www.rpi.edu> .\n",
    "    ?statement a rdf:Statement ;\n",
    "               rdf:subject ?e ;\n",
    "               rdf:predicate ?p ;\n",
    "               rdf:object ?o \n",
    "    FILTER (?p != rdf:type)\n",
    "}\n",
    "\"\"\")\n",
    "df = df.astype({\n",
    "    'e': str, 'p': str, 'o': str\n",
    "})\n",
    "rpi_relation_roles = df\n",
    "rpi_relation_roles.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Documents"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 81,
   "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>source</th>\n",
       "      <th>lang</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>HC00017P3</td>\n",
       "      <td>en</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>HC00017UR</td>\n",
       "      <td>en</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>HC00017RX</td>\n",
       "      <td>en</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>HC00017RT</td>\n",
       "      <td>en</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>HC00017P5</td>\n",
       "      <td>en</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      source lang\n",
       "0  HC00017P3   en\n",
       "1  HC00017UR   en\n",
       "2  HC00017RX   en\n",
       "3  HC00017RT   en\n",
       "4  HC00017P5   en"
      ]
     },
     "execution_count": 81,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = endpoint.select(\"\"\"\n",
    "SELECT DISTINCT ?source ?fileType {\n",
    "    ?justification a aida:TextJustification ;\n",
    "                   aida:system <http://www.rpi.edu> ;\n",
    "                   aida:source ?source ;\n",
    "                   aida:privateData ?filePrivate .\n",
    "    ?filePrivate aida:system <http://www.rpi.edu/fileType> ;\n",
    "                 aida:jsonContent ?fileType\n",
    "}\n",
    "\"\"\")\n",
    "df['lang'] = df.fileType.apply(lambda s: json.loads(s).get('fileType'))\n",
    "df = df.drop(columns='fileType')\n",
    "df = df.astype({\n",
    "    'source': str, 'lang': str\n",
    "})\n",
    "document_types = df\n",
    "document_types.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 82,
   "metadata": {},
   "outputs": [],
   "source": [
    "rpi_entity_with_justification.to_hdf(store_data_dir + '/entity_with_labels_' + version + '.h5', 'entity', mode='w', format='fixed')\n",
    "rpi_entity_valid.to_hdf(store_data_dir + '/entity_valid_' + version + '.h5', 'entity', mode='w', format='fixed')\n",
    "rpi_relation.to_hdf(store_data_dir + '/relation_' + version + '.h5', 'entity', mode='w', format='fixed')\n",
    "rpi_relation_roles.to_hdf(store_data_dir + '/relation_roles_' + version + '.h5', 'entity', mode='w', format='fixed')\n",
    "document_types.to_hdf(store_data_dir + '/document_' + version + '.h5', 'entity', mode='w', format='fixed')\n",
    "_ = pd.read_hdf(store_data_dir + '/entity_with_labels_' + version + '.h5')\n",
    "_ = pd.read_hdf(store_data_dir + '/entity_valid_' + version + '.h5')\n",
    "_ = pd.read_hdf(store_data_dir + '/relation_' + version + '.h5')\n",
    "_ = pd.read_hdf(store_data_dir + '/relation_roles_' + version + '.h5')\n",
    "_ = pd.read_hdf(store_data_dir + '/document_' + version + '.h5')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Transform Entities"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Entities"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 83,
   "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>e</th>\n",
       "      <th>type</th>\n",
       "      <th>source</th>\n",
       "      <th>name</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>http://www.isi.edu/gaia/entities/2d26abd5-94c7...</td>\n",
       "      <td>ldcOnt:TTL</td>\n",
       "      <td>HC00017P3</td>\n",
       "      <td>(contractor, contractor)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>http://www.isi.edu/gaia/entities/ee5a9d74-0942...</td>\n",
       "      <td>ldcOnt:PER.Politician.HeadOfGovernment</td>\n",
       "      <td>HC00017P3</td>\n",
       "      <td>(Jayashree Lakhan,)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>http://www.isi.edu/gaia/entities/58fd6e68-88aa...</td>\n",
       "      <td>ldcOnt:GPE.ProvinceState.ProvinceState</td>\n",
       "      <td>HC00017P3</td>\n",
       "      <td>(India, India, India, India, India's, India's,...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>http://www.isi.edu/gaia/entities/6329ec5d-f1a0...</td>\n",
       "      <td>ldcOnt:PER.MilitaryPersonnel</td>\n",
       "      <td>HC00017P3</td>\n",
       "      <td>(director, director)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>http://www.isi.edu/gaia/entities/086bcfa2-fdc7...</td>\n",
       "      <td>ldcOnt:PER</td>\n",
       "      <td>HC00017P3</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                                    e  \\\n",
       "0   http://www.isi.edu/gaia/entities/2d26abd5-94c7...   \n",
       "1   http://www.isi.edu/gaia/entities/ee5a9d74-0942...   \n",
       "2   http://www.isi.edu/gaia/entities/58fd6e68-88aa...   \n",
       "14  http://www.isi.edu/gaia/entities/6329ec5d-f1a0...   \n",
       "15  http://www.isi.edu/gaia/entities/086bcfa2-fdc7...   \n",
       "\n",
       "                                      type     source  \\\n",
       "0                               ldcOnt:TTL  HC00017P3   \n",
       "1   ldcOnt:PER.Politician.HeadOfGovernment  HC00017P3   \n",
       "2   ldcOnt:GPE.ProvinceState.ProvinceState  HC00017P3   \n",
       "14            ldcOnt:PER.MilitaryPersonnel  HC00017P3   \n",
       "15                              ldcOnt:PER  HC00017P3   \n",
       "\n",
       "                                                 name  \n",
       "0                            (contractor, contractor)  \n",
       "1                                 (Jayashree Lakhan,)  \n",
       "2   (India, India, India, India, India's, India's,...  \n",
       "14                               (director, director)  \n",
       "15                                               None  "
      ]
     },
     "execution_count": 83,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = rpi_entity_valid\n",
    "df['name'] = df.apply(lambda r: r['name'] if r['name'] != 'None' else None, axis=1)\n",
    "df = df.drop(columns='text')\n",
    "df = df.drop_duplicates()\n",
    "df = df[['e', 'type', 'source']].groupby('e').head(1).join(df.groupby('e')['name'].apply(tuple), on='e')\n",
    "df['name'] = df['name'].apply(lambda s: s if s[0] else None)\n",
    "df_names = df\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Origin - Slow, omit if not needed by TA3"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 84,
   "metadata": {},
   "outputs": [],
   "source": [
    "from model.source import LTFSourceContext\n",
    "\n",
    "def query_context(source, start, end):\n",
    "    if start == -1 or end == -1: return None\n",
    "    context_extractor = LTFSourceContext(source)\n",
    "    if context_extractor.doc_exists():\n",
    "        return context_extractor.query_context(start, end)\n",
    "    \n",
    "def query_label(source, start, end):\n",
    "    if start == -1 or end == -1: \n",
    "        return None\n",
    "    context_extractor = LTFSourceContext(source)\n",
    "    if context_extractor.doc_exists():\n",
    "        text = context_extractor.query(start, end)\n",
    "        return text"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 85,
   "metadata": {},
   "outputs": [],
   "source": [
    "if add_origin:\n",
    "    rpi_entity_with_justification['origin'] = rpi_entity_with_justification.apply(lambda r: query_context(r.source, r.start, r.end), axis=1)\n",
    "    rpi_entity_with_justification['originLabel'] = rpi_entity_with_justification.apply(lambda r: query_label(r.source, r.start, r.end), axis=1)\n",
    "    rpi_entity_with_justification.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 86,
   "metadata": {},
   "outputs": [],
   "source": [
    "if add_origin:\n",
    "    df_origin = df[['e', 'origin']].groupby('e')['origin'].apply(tuple).to_frame()\n",
    "    df_origin['origin'] = df_origin['origin'].apply(lambda s: s if s[0] else None)\n",
    "    df_origin.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 87,
   "metadata": {},
   "outputs": [],
   "source": [
    "if add_origin:\n",
    "    df_origin_label = df[['e', 'originLabel']].groupby('e')['originLabel'].apply(tuple).to_frame()\n",
    "    df_origin_label = df_origin_label['originLabel'].apply(lambda s: s if s[0] else None)\n",
    "    df_origin_label.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Justification Type"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 88,
   "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>e</th>\n",
       "      <th>type</th>\n",
       "      <th>label</th>\n",
       "      <th>target</th>\n",
       "      <th>source</th>\n",
       "      <th>start</th>\n",
       "      <th>end</th>\n",
       "      <th>justificationType</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>http://www.isi.edu/gaia/entities/2d26abd5-94c7...</td>\n",
       "      <td>ldcOnt:TTL</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>HC00017P3</td>\n",
       "      <td>9838</td>\n",
       "      <td>9847</td>\n",
       "      <td>mention</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>http://www.isi.edu/gaia/entities/ee5a9d74-0942...</td>\n",
       "      <td>ldcOnt:PER.Politician.HeadOfGovernment</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>HC00017P3</td>\n",
       "      <td>3280</td>\n",
       "      <td>3295</td>\n",
       "      <td>mention</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>http://www.isi.edu/gaia/entities/58fd6e68-88aa...</td>\n",
       "      <td>ldcOnt:GPE.ProvinceState.ProvinceState</td>\n",
       "      <td>None</td>\n",
       "      <td>LDC2019E43:4699848</td>\n",
       "      <td>HC00017P3</td>\n",
       "      <td>9537</td>\n",
       "      <td>9542</td>\n",
       "      <td>mention</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>http://www.isi.edu/gaia/entities/58fd6e68-88aa...</td>\n",
       "      <td>ldcOnt:GPE.ProvinceState.ProvinceState</td>\n",
       "      <td>None</td>\n",
       "      <td>LDC2019E43:1545739</td>\n",
       "      <td>HC00017P3</td>\n",
       "      <td>9537</td>\n",
       "      <td>9542</td>\n",
       "      <td>mention</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>http://www.isi.edu/gaia/entities/58fd6e68-88aa...</td>\n",
       "      <td>ldcOnt:GPE.ProvinceState.ProvinceState</td>\n",
       "      <td>None</td>\n",
       "      <td>LDC2019E43:1269750</td>\n",
       "      <td>HC00017P3</td>\n",
       "      <td>9537</td>\n",
       "      <td>9542</td>\n",
       "      <td>mention</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                                   e  \\\n",
       "0  http://www.isi.edu/gaia/entities/2d26abd5-94c7...   \n",
       "1  http://www.isi.edu/gaia/entities/ee5a9d74-0942...   \n",
       "2  http://www.isi.edu/gaia/entities/58fd6e68-88aa...   \n",
       "3  http://www.isi.edu/gaia/entities/58fd6e68-88aa...   \n",
       "4  http://www.isi.edu/gaia/entities/58fd6e68-88aa...   \n",
       "\n",
       "                                     type label              target  \\\n",
       "0                              ldcOnt:TTL  None                None   \n",
       "1  ldcOnt:PER.Politician.HeadOfGovernment  None                None   \n",
       "2  ldcOnt:GPE.ProvinceState.ProvinceState  None  LDC2019E43:4699848   \n",
       "3  ldcOnt:GPE.ProvinceState.ProvinceState  None  LDC2019E43:1545739   \n",
       "4  ldcOnt:GPE.ProvinceState.ProvinceState  None  LDC2019E43:1269750   \n",
       "\n",
       "      source  start   end justificationType  \n",
       "0  HC00017P3   9838  9847           mention  \n",
       "1  HC00017P3   3280  3295           mention  \n",
       "2  HC00017P3   9537  9542           mention  \n",
       "3  HC00017P3   9537  9542           mention  \n",
       "4  HC00017P3   9537  9542           mention  "
      ]
     },
     "execution_count": 88,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = rpi_entity_with_justification\n",
    "df = df[(df['justificationType']!='nominal_mention') & (df['justificationType']!='pronominal_mention')]\n",
    "# df['debug'] = df['justificationType'].apply(lambda s: False if s != 'nominal_mention' and s != 'pronominal_mention' else True)\n",
    "rpi_entity_with_justification_filtered = df\n",
    "rpi_entity_with_justification_filtered.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Labels"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 89,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "e\n",
       "http://www.isi.edu/gaia/entities/00029697-0740-4032-ae9b-3c264a8898ca    None\n",
       "http://www.isi.edu/gaia/entities/00bd1aed-65a6-43e5-a9b4-a5ee33180584    None\n",
       "http://www.isi.edu/gaia/entities/017b8275-edef-4bdb-a6ea-05022e9d9f4b    None\n",
       "http://www.isi.edu/gaia/entities/01b01979-b405-4ab1-94c3-97d5b141d998    None\n",
       "http://www.isi.edu/gaia/entities/01b37907-d715-4422-8fcd-1757364d77bd    None\n",
       "Name: label, dtype: object"
      ]
     },
     "execution_count": 89,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_label = rpi_entity_with_justification_filtered[['e', 'label']].drop_duplicates()\n",
    "df_label = df_label.groupby('e')['label'].apply(tuple).to_frame()\n",
    "df_label = df_label['label'].apply(lambda s: s if s and s[0] else None)\n",
    "df_label.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Wikidata from freebase ID"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 94,
   "metadata": {},
   "outputs": [],
   "source": [
    "from rdflib.plugins.stores.sparqlstore import SPARQLStore\n",
    "from rdflib.namespace import Namespace, RDFS, SKOS\n",
    "from rdflib import URIRef, Literal\n",
    "\n",
    "wikidata_sparql = SPARQLStore(wikidata_sparql_endpoint)\n",
    "# wikidata_sparql = SPARQLStore(\"https://query.wikidata.org/sparql\")\n",
    "WDT = Namespace('http://www.wikidata.org/prop/direct/')\n",
    "namespaces = {'wdt': WDT, 'skos': SKOS}\n",
    "\n",
    "def link_wikidata(fbid):\n",
    "    if not fbid or 'NIL' in fbid: return None\n",
    "    #.startswith('LDC2015E42:NIL'): return None   \n",
    "    fbid = '/' + fbid.replace('.', '/')\n",
    "    query = \"SELECT ?qid WHERE { ?qid wdt:P646 ?freebase } LIMIT 1\"\n",
    "    print('\\r', query, fbid, end='')\n",
    "    for qid, in wikidata_sparql.query(query, namespaces, {'freebase': Literal(fbid)}):\n",
    "        return str(qid)\n",
    "    \n",
    "def get_labels(pred, lang):\n",
    "    def get_labels_for_entity(qid):\n",
    "        if not qid: \n",
    "            return None\n",
    "        query = \"\"\"\n",
    "        SELECT ?label \n",
    "        WHERE { \n",
    "            ?qid pred ?label\n",
    "            FILTER (lang(?label) = \"language\") }\n",
    "        \"\"\".replace('pred', pred).replace('language', lang)\n",
    "        labels = []\n",
    "        for label, in wikidata_sparql.query(query, namespaces, {'qid': URIRef(qid)}):\n",
    "            labels.append(str(label))\n",
    "        return tuple(labels)\n",
    "    return get_labels_for_entity"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 95,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " SELECT ?qid WHERE { ?qid wdt:P646 ?freebase } LIMIT 1 /m/0djgt"
     ]
    },
    {
     "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>fbid</th>\n",
       "      <th>wikidata</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>m.0c2105</td>\n",
       "      <td>http://www.wikidata.org/entity/Q789889</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>m.02gjcx</td>\n",
       "      <td>http://www.wikidata.org/entity/Q1142402</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>m.061456</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>m.02wz7x</td>\n",
       "      <td>http://www.wikidata.org/entity/Q422404</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>m.0j0k</td>\n",
       "      <td>http://www.wikidata.org/entity/Q48</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       fbid                                 wikidata\n",
       "0  m.0c2105   http://www.wikidata.org/entity/Q789889\n",
       "1  m.02gjcx  http://www.wikidata.org/entity/Q1142402\n",
       "2  m.061456                                     None\n",
       "3  m.02wz7x   http://www.wikidata.org/entity/Q422404\n",
       "4    m.0j0k       http://www.wikidata.org/entity/Q48"
      ]
     },
     "execution_count": 95,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_fbid = rpi_fbid[['fbid']].drop_duplicates()\n",
    "df_fbid['wikidata'] = df_fbid.fbid.apply(link_wikidata)\n",
    "df_fbid.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 96,
   "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>fbid</th>\n",
       "      <th>wikidata</th>\n",
       "      <th>wiki_label_en</th>\n",
       "      <th>wiki_label_ru</th>\n",
       "      <th>wiki_label_uk</th>\n",
       "      <th>wiki_alias_en</th>\n",
       "      <th>wiki_alias_ru</th>\n",
       "      <th>wiki_alias_uk</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>m.0c2105</td>\n",
       "      <td>http://www.wikidata.org/entity/Q789889</td>\n",
       "      <td>(Ministry of Foreign Affairs,)</td>\n",
       "      <td>(Министерство иностранных дел Российской Федер...</td>\n",
       "      <td>(Міністерство закордонних справ Росії,)</td>\n",
       "      <td>(MFA Russia, Ministry of Foreign Affairs of th...</td>\n",
       "      <td>(МИД России, МИД Российской Федерации, МИД РФ,...</td>\n",
       "      <td>(МЗС Росії,)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>m.02gjcx</td>\n",
       "      <td>http://www.wikidata.org/entity/Q1142402</td>\n",
       "      <td>(Konkan,)</td>\n",
       "      <td>()</td>\n",
       "      <td>()</td>\n",
       "      <td>()</td>\n",
       "      <td>()</td>\n",
       "      <td>()</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>m.061456</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>m.02wz7x</td>\n",
       "      <td>http://www.wikidata.org/entity/Q422404</td>\n",
       "      <td>(Government of Canada,)</td>\n",
       "      <td>(правительство Канады,)</td>\n",
       "      <td>()</td>\n",
       "      <td>(Her Majesty's Government, federal government ...</td>\n",
       "      <td>()</td>\n",
       "      <td>()</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>m.0j0k</td>\n",
       "      <td>http://www.wikidata.org/entity/Q48</td>\n",
       "      <td>(Asia,)</td>\n",
       "      <td>(Азия,)</td>\n",
       "      <td>(Азія,)</td>\n",
       "      <td>()</td>\n",
       "      <td>()</td>\n",
       "      <td>()</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       fbid                                 wikidata  \\\n",
       "0  m.0c2105   http://www.wikidata.org/entity/Q789889   \n",
       "1  m.02gjcx  http://www.wikidata.org/entity/Q1142402   \n",
       "2  m.061456                                     None   \n",
       "3  m.02wz7x   http://www.wikidata.org/entity/Q422404   \n",
       "4    m.0j0k       http://www.wikidata.org/entity/Q48   \n",
       "\n",
       "                    wiki_label_en  \\\n",
       "0  (Ministry of Foreign Affairs,)   \n",
       "1                       (Konkan,)   \n",
       "2                            None   \n",
       "3         (Government of Canada,)   \n",
       "4                         (Asia,)   \n",
       "\n",
       "                                       wiki_label_ru  \\\n",
       "0  (Министерство иностранных дел Российской Федер...   \n",
       "1                                                 ()   \n",
       "2                                               None   \n",
       "3                            (правительство Канады,)   \n",
       "4                                            (Азия,)   \n",
       "\n",
       "                             wiki_label_uk  \\\n",
       "0  (Міністерство закордонних справ Росії,)   \n",
       "1                                       ()   \n",
       "2                                     None   \n",
       "3                                       ()   \n",
       "4                                  (Азія,)   \n",
       "\n",
       "                                       wiki_alias_en  \\\n",
       "0  (MFA Russia, Ministry of Foreign Affairs of th...   \n",
       "1                                                 ()   \n",
       "2                                               None   \n",
       "3  (Her Majesty's Government, federal government ...   \n",
       "4                                                 ()   \n",
       "\n",
       "                                       wiki_alias_ru wiki_alias_uk  \n",
       "0  (МИД России, МИД Российской Федерации, МИД РФ,...  (МЗС Росії,)  \n",
       "1                                                 ()            ()  \n",
       "2                                               None          None  \n",
       "3                                                 ()            ()  \n",
       "4                                                 ()            ()  "
      ]
     },
     "execution_count": 96,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_fbid['wiki_label_en'] = df_fbid['wikidata'].apply(get_labels('rdfs:label', 'en'))\n",
    "df_fbid['wiki_label_ru'] = df_fbid['wikidata'].apply(get_labels('rdfs:label', 'ru'))\n",
    "df_fbid['wiki_label_uk'] = df_fbid['wikidata'].apply(get_labels('rdfs:label', 'uk'))\n",
    "df_fbid['wiki_alias_en'] = df_fbid['wikidata'].apply(get_labels('skos:altLabel', 'en'))\n",
    "df_fbid['wiki_alias_ru'] = df_fbid['wikidata'].apply(get_labels('skos:altLabel', 'ru'))\n",
    "df_fbid['wiki_alias_uk'] = df_fbid['wikidata'].apply(get_labels('skos:altLabel', 'uk'))\n",
    "df_fbid = df_fbid.where(pd.notnull(df_fbid), None)\n",
    "df_fbid.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Combine columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 97,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Index(['fbid', 'wikidata', 'wiki_label_en', 'wiki_label_ru', 'wiki_label_uk',\n",
      "       'wiki_alias_en', 'wiki_alias_ru', 'wiki_alias_uk'],\n",
      "      dtype='object')\n",
      "Index(['e', 'type', 'label', 'target', 'source', 'start', 'end',\n",
      "       'justificationType'],\n",
      "      dtype='object')\n",
      "Index(['e', 'type', 'name', 'text', 'source', 'target'], dtype='object')\n",
      "Index(['source', 'lang'], dtype='object')\n"
     ]
    }
   ],
   "source": [
    "print(df_fbid.columns)\n",
    "print(rpi_entity_with_justification.columns)\n",
    "print(rpi_entity_valid.columns)\n",
    "print(document_types.columns)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 98,
   "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>e</th>\n",
       "      <th>type</th>\n",
       "      <th>name</th>\n",
       "      <th>source</th>\n",
       "      <th>targets</th>\n",
       "      <th>target_scores</th>\n",
       "      <th>fbid</th>\n",
       "      <th>fbid_score_avg</th>\n",
       "      <th>fbid_score_max</th>\n",
       "      <th>wikidata</th>\n",
       "      <th>wiki_label_en</th>\n",
       "      <th>wiki_label_ru</th>\n",
       "      <th>wiki_label_uk</th>\n",
       "      <th>wiki_alias_en</th>\n",
       "      <th>wiki_alias_ru</th>\n",
       "      <th>wiki_alias_uk</th>\n",
       "      <th>lang</th>\n",
       "      <th>label</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>http://www.isi.edu/gaia/entities/2d26abd5-94c7...</td>\n",
       "      <td>ldcOnt:TTL</td>\n",
       "      <td>(contractor, contractor)</td>\n",
       "      <td>HC00017P3</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>en</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>http://www.isi.edu/gaia/entities/ee5a9d74-0942...</td>\n",
       "      <td>ldcOnt:PER.Politician.HeadOfGovernment</td>\n",
       "      <td>(Jayashree Lakhan,)</td>\n",
       "      <td>HC00017P3</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>en</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>http://www.isi.edu/gaia/entities/58fd6e68-88aa...</td>\n",
       "      <td>ldcOnt:GPE.ProvinceState.ProvinceState</td>\n",
       "      <td>(India, India, India, India, India's, India's,...</td>\n",
       "      <td>HC00017P3</td>\n",
       "      <td>(LDC2019E43:4699848, LDC2019E43:1545739, LDC20...</td>\n",
       "      <td>(0.001, 1.0, 1.0, 0.0438)</td>\n",
       "      <td>(m.03rk0, m.03rz4)</td>\n",
       "      <td>(0.469724013, 0.6634847224)</td>\n",
       "      <td>(0.9556255937, 0.6634847224)</td>\n",
       "      <td>(http://www.wikidata.org/entity/Q668, http://w...</td>\n",
       "      <td>(India, Indian Ocean)</td>\n",
       "      <td>(Индия, Индийский океан)</td>\n",
       "      <td>(Індія, Індійський океан)</td>\n",
       "      <td>((IND, in, Bharat, Bharatvarsh, Hindustan, IN,...</td>\n",
       "      <td>((Республика Индия,), ())</td>\n",
       "      <td>((Бгарат, Республіка Індія), ())</td>\n",
       "      <td>en</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>http://www.isi.edu/gaia/entities/58fd6e68-88aa...</td>\n",
       "      <td>ldcOnt:GPE.ProvinceState.ProvinceState</td>\n",
       "      <td>(India, India, India, India, India's, India's,...</td>\n",
       "      <td>HC00017P3</td>\n",
       "      <td>(LDC2019E43:4699848, LDC2019E43:1545739, LDC20...</td>\n",
       "      <td>(0.001, 1.0, 1.0, 0.0438)</td>\n",
       "      <td>(m.03rk0,)</td>\n",
       "      <td>(0.6578779092,)</td>\n",
       "      <td>(1.0,)</td>\n",
       "      <td>(http://www.wikidata.org/entity/Q668,)</td>\n",
       "      <td>(India,)</td>\n",
       "      <td>(Индия,)</td>\n",
       "      <td>(Індія,)</td>\n",
       "      <td>((IND, in, Bharat, Bharatvarsh, Hindustan, IN,...</td>\n",
       "      <td>((Республика Индия,),)</td>\n",
       "      <td>((Бгарат, Республіка Індія),)</td>\n",
       "      <td>en</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>http://www.isi.edu/gaia/entities/6329ec5d-f1a0...</td>\n",
       "      <td>ldcOnt:PER.MilitaryPersonnel</td>\n",
       "      <td>(director, director)</td>\n",
       "      <td>HC00017P3</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>en</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                                    e  \\\n",
       "0   http://www.isi.edu/gaia/entities/2d26abd5-94c7...   \n",
       "1   http://www.isi.edu/gaia/entities/ee5a9d74-0942...   \n",
       "2   http://www.isi.edu/gaia/entities/58fd6e68-88aa...   \n",
       "2   http://www.isi.edu/gaia/entities/58fd6e68-88aa...   \n",
       "10  http://www.isi.edu/gaia/entities/6329ec5d-f1a0...   \n",
       "\n",
       "                                      type  \\\n",
       "0                               ldcOnt:TTL   \n",
       "1   ldcOnt:PER.Politician.HeadOfGovernment   \n",
       "2   ldcOnt:GPE.ProvinceState.ProvinceState   \n",
       "2   ldcOnt:GPE.ProvinceState.ProvinceState   \n",
       "10            ldcOnt:PER.MilitaryPersonnel   \n",
       "\n",
       "                                                 name     source  \\\n",
       "0                            (contractor, contractor)  HC00017P3   \n",
       "1                                 (Jayashree Lakhan,)  HC00017P3   \n",
       "2   (India, India, India, India, India's, India's,...  HC00017P3   \n",
       "2   (India, India, India, India, India's, India's,...  HC00017P3   \n",
       "10                               (director, director)  HC00017P3   \n",
       "\n",
       "                                              targets  \\\n",
       "0                                                None   \n",
       "1                                                None   \n",
       "2   (LDC2019E43:4699848, LDC2019E43:1545739, LDC20...   \n",
       "2   (LDC2019E43:4699848, LDC2019E43:1545739, LDC20...   \n",
       "10                                               None   \n",
       "\n",
       "                target_scores                fbid  \\\n",
       "0                        None                None   \n",
       "1                        None                None   \n",
       "2   (0.001, 1.0, 1.0, 0.0438)  (m.03rk0, m.03rz4)   \n",
       "2   (0.001, 1.0, 1.0, 0.0438)          (m.03rk0,)   \n",
       "10                       None                None   \n",
       "\n",
       "                 fbid_score_avg                fbid_score_max  \\\n",
       "0                          None                          None   \n",
       "1                          None                          None   \n",
       "2   (0.469724013, 0.6634847224)  (0.9556255937, 0.6634847224)   \n",
       "2               (0.6578779092,)                        (1.0,)   \n",
       "10                         None                          None   \n",
       "\n",
       "                                             wikidata          wiki_label_en  \\\n",
       "0                                                None                   None   \n",
       "1                                                None                   None   \n",
       "2   (http://www.wikidata.org/entity/Q668, http://w...  (India, Indian Ocean)   \n",
       "2              (http://www.wikidata.org/entity/Q668,)               (India,)   \n",
       "10                                               None                   None   \n",
       "\n",
       "               wiki_label_ru              wiki_label_uk  \\\n",
       "0                       None                       None   \n",
       "1                       None                       None   \n",
       "2   (Индия, Индийский океан)  (Індія, Індійський океан)   \n",
       "2                   (Индия,)                   (Індія,)   \n",
       "10                      None                       None   \n",
       "\n",
       "                                        wiki_alias_en  \\\n",
       "0                                                None   \n",
       "1                                                None   \n",
       "2   ((IND, in, Bharat, Bharatvarsh, Hindustan, IN,...   \n",
       "2   ((IND, in, Bharat, Bharatvarsh, Hindustan, IN,...   \n",
       "10                                               None   \n",
       "\n",
       "                wiki_alias_ru                     wiki_alias_uk lang label  \n",
       "0                        None                              None   en  None  \n",
       "1                        None                              None   en  None  \n",
       "2   ((Республика Индия,), ())  ((Бгарат, Республіка Індія), ())   en  None  \n",
       "2      ((Республика Индия,),)     ((Бгарат, Республіка Індія),)   en  None  \n",
       "10                       None                              None   en  None  "
      ]
     },
     "execution_count": 98,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = rpi_entity_with_justification_filtered[['e', 'type', 'source']].drop_duplicates()\n",
    "if add_origin:\n",
    "    df = df.join(df_origin, on='e').join(df_origin_label, on='e')\n",
    "df = df.join(df_label, on='e')\n",
    "df = df.join(df_target.set_index('e'), on='e')\n",
    "df = df.join(rpi_external.set_index('e'), on='e')\n",
    "\n",
    "def add_wd(fbids):\n",
    "    if fbids:\n",
    "        wd = []\n",
    "        label_en = []\n",
    "        label_ru = []\n",
    "        label_uk = []\n",
    "        alias_en = ()\n",
    "        alias_ru = ()\n",
    "        alias_uk = ()\n",
    "        for fbid in fbids:\n",
    "            row_df = df_fbid.loc[df_fbid.fbid==fbid]\n",
    "            \n",
    "            wikidata = row_df['wikidata'].values[0]\n",
    "            wd.append(wikidata)\n",
    "            \n",
    "            wiki_label_en = row_df['wiki_label_en'].values[0]\n",
    "            if wiki_label_en:\n",
    "                wiki_label_en = wiki_label_en[0]\n",
    "            label_en.append(wiki_label_en)\n",
    "\n",
    "            wiki_label_ru = row_df['wiki_label_ru'].values[0]\n",
    "            if wiki_label_ru:\n",
    "                wiki_label_ru = wiki_label_ru[0]\n",
    "            label_ru.append(wiki_label_ru)\n",
    "            \n",
    "            wiki_label_uk = row_df['wiki_label_uk'].values[0]\n",
    "            if wiki_label_uk:\n",
    "                wiki_label_uk = wiki_label_uk[0]\n",
    "            label_uk.append(wiki_label_uk)\n",
    "            \n",
    "            wiki_alias_en = row_df['wiki_alias_en'].values[0]\n",
    "            alias_en = alias_en + (wiki_alias_en,)\n",
    "            wiki_alias_ru = row_df['wiki_alias_ru'].values[0]\n",
    "            alias_ru = alias_ru + (wiki_alias_ru,)\n",
    "            wiki_alias_uk = row_df['wiki_alias_uk'].values[0]\n",
    "            alias_uk = alias_uk + (wiki_alias_uk,)\n",
    "        return pd.Series({'wikidata': tuple(wd), 'wiki_label_en': tuple(label_en), 'wiki_label_ru': tuple(label_ru), 'wiki_label_uk': tuple(label_uk), 'wiki_alias_en': alias_en, 'wiki_alias_ru': alias_ru, 'wiki_alias_uk': alias_uk})\n",
    "    else:\n",
    "        return pd.Series({'wikidata': None, 'wiki_label_en': None, 'wiki_label_ru': None, 'wiki_label_uk': None, 'wiki_alias_en': None, 'wiki_alias_ru': None, 'wiki_alias_uk': None})\n",
    "df = df.where(pd.notnull(df), None)\n",
    "df[['wikidata', 'wiki_label_en', 'wiki_label_ru', 'wiki_label_uk', 'wiki_alias_en', 'wiki_alias_ru', 'wiki_alias_uk']] = df['fbid'].apply(add_wd)\n",
    "\n",
    "df = df.join(document_types.set_index('source'), on='source')\n",
    "df = df.join(df_names[['e', 'name']].set_index('e'), on='e')\n",
    "\n",
    "if add_origin:\n",
    "    df = df[['e', 'type', 'name', 'source', 'targets', 'target_scores', 'fbid', 'fbid_score_avg', 'fbid_score_max', 'wikidata',\n",
    "           'wiki_label_en', 'wiki_label_ru', 'wiki_label_uk', 'wiki_alias_en',\n",
    "           'wiki_alias_ru', 'wiki_alias_uk', 'origin', 'originLabel', 'lang', 'label']]\n",
    "else:\n",
    "    df = df[['e', 'type', 'name', 'source', 'targets', 'target_scores', 'fbid', 'fbid_score_avg', 'fbid_score_max', 'wikidata',\n",
    "           'wiki_label_en', 'wiki_label_ru', 'wiki_label_uk', 'wiki_alias_en',\n",
    "           'wiki_alias_ru', 'wiki_alias_uk', 'lang', 'label']]\n",
    "\n",
    "df_all = df\n",
    "df_all.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Write out dataframe"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 99,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/lfs1/gaia/yixiangy/miniconda3/envs/py36/lib/python3.6/site-packages/pandas/core/generic.py:1993: PerformanceWarning: \n",
      "your performance may suffer as PyTables will pickle object types that it cannot\n",
      "map directly to c-types [inferred_type->mixed,key->block0_values] [items->['e', 'type', 'name', 'source', 'targets', 'target_scores', 'fbid', 'fbid_score_avg', 'fbid_score_max', 'wikidata', 'wiki_label_en', 'wiki_label_ru', 'wiki_label_uk', 'wiki_alias_en', 'wiki_alias_ru', 'wiki_alias_uk', 'lang', 'label']]\n",
      "\n",
      "  return pytables.to_hdf(path_or_buf, key, self, **kwargs)\n"
     ]
    },
    {
     "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>e</th>\n",
       "      <th>type</th>\n",
       "      <th>name</th>\n",
       "      <th>source</th>\n",
       "      <th>targets</th>\n",
       "      <th>target_scores</th>\n",
       "      <th>fbid</th>\n",
       "      <th>fbid_score_avg</th>\n",
       "      <th>fbid_score_max</th>\n",
       "      <th>wikidata</th>\n",
       "      <th>wiki_label_en</th>\n",
       "      <th>wiki_label_ru</th>\n",
       "      <th>wiki_label_uk</th>\n",
       "      <th>wiki_alias_en</th>\n",
       "      <th>wiki_alias_ru</th>\n",
       "      <th>wiki_alias_uk</th>\n",
       "      <th>lang</th>\n",
       "      <th>label</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>http://www.isi.edu/gaia/entities/2d26abd5-94c7...</td>\n",
       "      <td>ldcOnt:TTL</td>\n",
       "      <td>(contractor, contractor)</td>\n",
       "      <td>HC00017P3</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>en</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>http://www.isi.edu/gaia/entities/ee5a9d74-0942...</td>\n",
       "      <td>ldcOnt:PER.Politician.HeadOfGovernment</td>\n",
       "      <td>(Jayashree Lakhan,)</td>\n",
       "      <td>HC00017P3</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>en</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>http://www.isi.edu/gaia/entities/58fd6e68-88aa...</td>\n",
       "      <td>ldcOnt:GPE.ProvinceState.ProvinceState</td>\n",
       "      <td>(India, India, India, India, India's, India's,...</td>\n",
       "      <td>HC00017P3</td>\n",
       "      <td>(LDC2019E43:4699848, LDC2019E43:1545739, LDC20...</td>\n",
       "      <td>(0.001, 1.0, 1.0, 0.0438)</td>\n",
       "      <td>(m.03rk0, m.03rz4)</td>\n",
       "      <td>(0.469724013, 0.6634847224)</td>\n",
       "      <td>(0.9556255937, 0.6634847224)</td>\n",
       "      <td>(http://www.wikidata.org/entity/Q668, http://w...</td>\n",
       "      <td>(India, Indian Ocean)</td>\n",
       "      <td>(Индия, Индийский океан)</td>\n",
       "      <td>(Індія, Індійський океан)</td>\n",
       "      <td>((IND, in, Bharat, Bharatvarsh, Hindustan, IN,...</td>\n",
       "      <td>((Республика Индия,), ())</td>\n",
       "      <td>((Бгарат, Республіка Індія), ())</td>\n",
       "      <td>en</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>http://www.isi.edu/gaia/entities/58fd6e68-88aa...</td>\n",
       "      <td>ldcOnt:GPE.ProvinceState.ProvinceState</td>\n",
       "      <td>(India, India, India, India, India's, India's,...</td>\n",
       "      <td>HC00017P3</td>\n",
       "      <td>(LDC2019E43:4699848, LDC2019E43:1545739, LDC20...</td>\n",
       "      <td>(0.001, 1.0, 1.0, 0.0438)</td>\n",
       "      <td>(m.03rk0,)</td>\n",
       "      <td>(0.6578779092,)</td>\n",
       "      <td>(1.0,)</td>\n",
       "      <td>(http://www.wikidata.org/entity/Q668,)</td>\n",
       "      <td>(India,)</td>\n",
       "      <td>(Индия,)</td>\n",
       "      <td>(Індія,)</td>\n",
       "      <td>((IND, in, Bharat, Bharatvarsh, Hindustan, IN,...</td>\n",
       "      <td>((Республика Индия,),)</td>\n",
       "      <td>((Бгарат, Республіка Індія),)</td>\n",
       "      <td>en</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>http://www.isi.edu/gaia/entities/6329ec5d-f1a0...</td>\n",
       "      <td>ldcOnt:PER.MilitaryPersonnel</td>\n",
       "      <td>(director, director)</td>\n",
       "      <td>HC00017P3</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>en</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                                    e  \\\n",
       "0   http://www.isi.edu/gaia/entities/2d26abd5-94c7...   \n",
       "1   http://www.isi.edu/gaia/entities/ee5a9d74-0942...   \n",
       "2   http://www.isi.edu/gaia/entities/58fd6e68-88aa...   \n",
       "2   http://www.isi.edu/gaia/entities/58fd6e68-88aa...   \n",
       "10  http://www.isi.edu/gaia/entities/6329ec5d-f1a0...   \n",
       "\n",
       "                                      type  \\\n",
       "0                               ldcOnt:TTL   \n",
       "1   ldcOnt:PER.Politician.HeadOfGovernment   \n",
       "2   ldcOnt:GPE.ProvinceState.ProvinceState   \n",
       "2   ldcOnt:GPE.ProvinceState.ProvinceState   \n",
       "10            ldcOnt:PER.MilitaryPersonnel   \n",
       "\n",
       "                                                 name     source  \\\n",
       "0                            (contractor, contractor)  HC00017P3   \n",
       "1                                 (Jayashree Lakhan,)  HC00017P3   \n",
       "2   (India, India, India, India, India's, India's,...  HC00017P3   \n",
       "2   (India, India, India, India, India's, India's,...  HC00017P3   \n",
       "10                               (director, director)  HC00017P3   \n",
       "\n",
       "                                              targets  \\\n",
       "0                                                None   \n",
       "1                                                None   \n",
       "2   (LDC2019E43:4699848, LDC2019E43:1545739, LDC20...   \n",
       "2   (LDC2019E43:4699848, LDC2019E43:1545739, LDC20...   \n",
       "10                                               None   \n",
       "\n",
       "                target_scores                fbid  \\\n",
       "0                        None                None   \n",
       "1                        None                None   \n",
       "2   (0.001, 1.0, 1.0, 0.0438)  (m.03rk0, m.03rz4)   \n",
       "2   (0.001, 1.0, 1.0, 0.0438)          (m.03rk0,)   \n",
       "10                       None                None   \n",
       "\n",
       "                 fbid_score_avg                fbid_score_max  \\\n",
       "0                          None                          None   \n",
       "1                          None                          None   \n",
       "2   (0.469724013, 0.6634847224)  (0.9556255937, 0.6634847224)   \n",
       "2               (0.6578779092,)                        (1.0,)   \n",
       "10                         None                          None   \n",
       "\n",
       "                                             wikidata          wiki_label_en  \\\n",
       "0                                                None                   None   \n",
       "1                                                None                   None   \n",
       "2   (http://www.wikidata.org/entity/Q668, http://w...  (India, Indian Ocean)   \n",
       "2              (http://www.wikidata.org/entity/Q668,)               (India,)   \n",
       "10                                               None                   None   \n",
       "\n",
       "               wiki_label_ru              wiki_label_uk  \\\n",
       "0                       None                       None   \n",
       "1                       None                       None   \n",
       "2   (Индия, Индийский океан)  (Індія, Індійський океан)   \n",
       "2                   (Индия,)                   (Індія,)   \n",
       "10                      None                       None   \n",
       "\n",
       "                                        wiki_alias_en  \\\n",
       "0                                                None   \n",
       "1                                                None   \n",
       "2   ((IND, in, Bharat, Bharatvarsh, Hindustan, IN,...   \n",
       "2   ((IND, in, Bharat, Bharatvarsh, Hindustan, IN,...   \n",
       "10                                               None   \n",
       "\n",
       "                wiki_alias_ru                     wiki_alias_uk lang label  \n",
       "0                        None                              None   en  None  \n",
       "1                        None                              None   en  None  \n",
       "2   ((Республика Индия,), ())  ((Бгарат, Республіка Індія), ())   en  None  \n",
       "2      ((Республика Индия,),)     ((Бгарат, Республіка Індія),)   en  None  \n",
       "10                       None                              None   en  None  "
      ]
     },
     "execution_count": 99,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_all.to_hdf(store_data_dir + '/entity_all_' + version + '.h5', 'entity', mode='w', format='fixed')\n",
    "_ = pd.read_hdf(store_data_dir + '/entity_all_' + version + '.h5')\n",
    "df_all.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "py36",
   "language": "python",
   "name": "py36"
  },
  "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.6.8"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}