{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Add Derived Graphs To The Tutorial Graph\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import io\n",
    "import os\n",
    "import subprocess\n",
    "import sys\n",
    "\n",
    "import numpy as np\n",
    "import pandas as pd\n",
    "from IPython.display import display, HTML\n",
    "\n",
    "import papermill as pm\n",
    "\n",
    "sys.path.insert(0,'../..')\n",
    "from kgtk.configure_kgtk_notebooks import ConfigureKGTK\n",
    "\n",
    "from kgtk.functions import kgtk, kypher"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "tags": [
     "parameters"
    ]
   },
   "outputs": [],
   "source": [
    "# Parameters\n",
    "\n",
    "kgtk_path = \"/Users/pedroszekely/Documents/GitHub/kgtk\"\n",
    "\n",
    "# Folder on local machine where to create the output and temporary folders\n",
    "input_path = \"/Users/pedroszekely/Downloads/kypher/projects/build-tutorial\"\n",
    "output_path = \"/Users/pedroszekely/Downloads/kypher/projects\"\n",
    "project_name = \"tutorial-derived-graphs\"\n",
    "tutorial_files_path = \"/Users/pedroszekely/Documents/GitHub/kgtk-tutorial-files/datasets/arnold\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "User home: /Users/amandeep\n",
      "Current dir: /Users/amandeep/Github/kgtk/tutorial/build-kg\n",
      "KGTK dir: /Users/amandeep/Github/kgtk\n",
      "Use-cases dir: /Users/amandeep/Github/kgtk/use-cases\n"
     ]
    }
   ],
   "source": [
    "files = [\n",
    "    \"all\"\n",
    "]\n",
    "ck = ConfigureKGTK(files, kgtk_path=kgtk_path)\n",
    "ck.configure_kgtk(input_graph_path=input_path,\n",
    "                  output_path=output_path,\n",
    "                  project_name=project_name)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "OUT: /Volumes/saggu-ssd/arnold-2/tutorial-derived-graphs\n",
      "TEMP: /Volumes/saggu-ssd/arnold-2/tutorial-derived-graphs/temp.tutorial-derived-graphs\n",
      "STORE: /Volumes/saggu-ssd/arnold-2/tutorial-derived-graphs/temp.tutorial-derived-graphs/wikidata.sqlite3.db\n",
      "kypher: kgtk query --graph-cache /Volumes/saggu-ssd/arnold-2/tutorial-derived-graphs/temp.tutorial-derived-graphs/wikidata.sqlite3.db\n",
      "GRAPH: /Volumes/saggu-ssd/kgtk-tutorial-files/datasets/arnold\n",
      "USE_CASES_DIR: /Users/amandeep/Github/kgtk/use-cases\n",
      "kgtk: kgtk\n",
      "EXAMPLES_DIR: /Users/amandeep/Github/kgtk/examples\n",
      "all: /Volumes/saggu-ssd/kgtk-tutorial-files/datasets/arnold/all.tsv.gz\n"
     ]
    }
   ],
   "source": [
    "ck.print_env_variables()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Turn on debugging for kypher"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "os.environ['tutorial_files_path'] = tutorial_files_path\n",
    "os.environ['kgtk_path'] = kgtk_path\n",
    "os.environ['KGTK_GRAPH_CACHE'] = os.environ['STORE']\n",
    "os.environ['KGTK_LABEL_FILE'] = os.environ['OUT'] + \"/parts/labels.en.tsv.gz\"\n",
    "os.environ['KGTK_OPTION_DEBUG'] = \"true\""
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Load all my files into the kypher cache so that all graph aliases are defined"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "kgtk query --graph-cache /Users/pedroszekely/Downloads/kypher/projects/tutorial-derived-graphs/temp.tutorial-derived-graphs/wikidata.sqlite3.db -i \"/Users/pedroszekely/Downloads/kypher/projects/build-tutorial/all.tsv.gz\" --as all  --limit 3\n",
      "[2021-10-10 11:51:26 query]: SQL Translation:\n",
      "---------------------------------------------\n",
      "  SELECT *\n",
      "     FROM graph_27 AS graph_27_c1\n",
      "     LIMIT ?\n",
      "  PARAS: [3]\n",
      "---------------------------------------------\n",
      "node1\tlabel\tnode2\tid\n",
      "P10\tP31\tQ18610173\tP10-P31-Q18610173-85ef4d24-0\n",
      "P1000\tP31\tQ18608871\tP1000-P31-Q18608871-093affb5-0\n",
      "P1001\tP1647\tP276\tP1001-P1647-P276-e4e44f83-0\n"
     ]
    }
   ],
   "source": [
    "ck.load_files_into_cache()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "/Users/pedroszekely/Downloads/kypher/projects/tutorial-derived-graphs\n"
     ]
    }
   ],
   "source": [
    "%cd {os.environ['OUT']}"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Run partition notebook\n",
    "\n",
    "We need the parts to run the Useful Files notebook"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "a36e9d35693a4d3ea546ccff53b14e4b",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "Executing:   0%|          | 0/49 [00:00<?, ?cell/s]"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/plain": [
       "''"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pm.execute_notebook(\n",
    "    os.environ[\"EXAMPLES_DIR\"] + \"/partition-wikidata.ipynb\",\n",
    "    os.environ[\"TEMP\"] + \"/partition-wikidata.out.ipynb\",\n",
    "    parameters=dict(\n",
    "        wikidata_input_path = input_path + \"/all.tsv.gz\",\n",
    "        wikidata_parts_path = os.environ[\"OUT\"] + \"/parts\",\n",
    "        temp_folder_path = os.environ[\"OUT\"] + \"/parts/temp\",\n",
    "        sort_extras = \"--buffer-size 30% --temporary-directory $OUT/parts/temp\",\n",
    "        verbose = False,\n",
    "        gzip_command = 'gzip'\n",
    "    )\n",
    ")\n",
    ";"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Show the files after partition"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "aliases.en.tsv.gz                   metadata.property.datatypes.tsv.gz\n",
      "aliases.tsv.gz                      metadata.types.tsv.gz\n",
      "all.tsv.gz                          qualifiers.commonsMedia.tsv.gz\n",
      "claims.commonsMedia.tsv.gz          qualifiers.external-id.tsv.gz\n",
      "claims.external-id.tsv.gz           qualifiers.geo-shape.tsv.gz\n",
      "claims.geo-shape.tsv.gz             qualifiers.globe-coordinate.tsv.gz\n",
      "claims.globe-coordinate.tsv.gz      qualifiers.math.tsv.gz\n",
      "claims.math.tsv.gz                  qualifiers.monolingualtext.tsv.gz\n",
      "claims.monolingualtext.tsv.gz       qualifiers.musical-notation.tsv.gz\n",
      "claims.musical-notation.tsv.gz      qualifiers.quantity.tsv.gz\n",
      "claims.other.tsv.gz                 qualifiers.string.tsv.gz\n",
      "claims.quantity.tsv.gz              qualifiers.tabular-data.tsv.gz\n",
      "claims.string.tsv.gz                qualifiers.time.tsv.gz\n",
      "claims.tabular-data.tsv.gz          qualifiers.tsv.gz\n",
      "claims.time.tsv.gz                  qualifiers.url.tsv.gz\n",
      "claims.tsv.gz                       qualifiers.wikibase-form.tsv.gz\n",
      "claims.url.tsv.gz                   qualifiers.wikibase-item.tsv.gz\n",
      "claims.wikibase-form.tsv.gz         qualifiers.wikibase-lexeme.tsv.gz\n",
      "claims.wikibase-item.tsv.gz         qualifiers.wikibase-property.tsv.gz\n",
      "claims.wikibase-lexeme.tsv.gz       qualifiers.wikibase-sense.tsv.gz\n",
      "claims.wikibase-property.tsv.gz     sitelinks.en.tsv.gz\n",
      "claims.wikibase-sense.tsv.gz        sitelinks.qualifiers.en.tsv.gz\n",
      "descriptions.en.tsv.gz              sitelinks.qualifiers.tsv.gz\n",
      "descriptions.tsv.gz                 sitelinks.tsv.gz\n",
      "labels.en.tsv.gz                    \u001b[34mtemp\u001b[m\u001b[m\n",
      "labels.tsv.gz\n"
     ]
    }
   ],
   "source": [
    "!ls $OUT/parts"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Deploy the parts to `$tutorial_files_path`"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Run useful files notebook"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "f1df7d70f17c4c3eb5615830b870466c",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "Executing:   0%|          | 0/151 [00:00<?, ?cell/s]"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/plain": [
       "''"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pm.execute_notebook(\n",
    "    os.environ[\"USE_CASES_DIR\"] + \"/Wikidata Useful Files.ipynb\",\n",
    "    os.environ[\"TEMP\"] + \"/Wikidata Useful Files Out.ipynb\",\n",
    "    parameters=dict(\n",
    "        output_path = os.environ[\"OUT\"],\n",
    "        project_name = \"useful_files\",\n",
    "        kgtk_path = kgtk_path,\n",
    "        input_path = os.environ[\"OUT\"] + \"/parts\",\n",
    "        files = 'claims,label,label_all,alias,alias_all,description,description_all,item',\n",
    "        graph_cache_path = os.environ['STORE'],\n",
    "        languages = 'en',\n",
    "        compute_pagerank = True,\n",
    "        compute_degrees = True,\n",
    "        compute_hits = False, \n",
    "        compute_table_linker_files = False,\n",
    "        debug = \"false\"\n",
    "    )\n",
    ")\n",
    ";"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "total 98312\n",
      "-rw-r--r--   1 amandeep  staff   1344059 Oct 12 16:32 aliases.en.tsv.gz\n",
      "-rw-r--r--   1 amandeep  staff    390973 Oct 12 16:32 derived.P279.tsv.gz\n",
      "-rw-r--r--   1 amandeep  staff   3325552 Oct 12 16:33 derived.P279star.tsv.gz\n",
      "-rw-r--r--   1 amandeep  staff   1181395 Oct 12 16:32 derived.P31.tsv.gz\n",
      "-rw-r--r--   1 amandeep  staff  12814647 Oct 12 16:34 derived.P31P279star.tsv.gz\n",
      "-rw-r--r--   1 amandeep  staff    499128 Oct 12 16:33 derived.isa.tsv.gz\n",
      "-rw-r--r--   1 amandeep  staff  15435743 Oct 12 16:33 derived.isastar.tsv.gz\n",
      "-rw-r--r--   1 amandeep  staff   1341047 Oct 12 16:32 descriptions.en.tsv.gz\n",
      "-rw-r--r--   1 amandeep  staff   1065321 Oct 12 16:32 labels.en.tsv.gz\n",
      "-rw-r--r--   1 amandeep  staff    309510 Oct 12 16:35 metadata.in_degree.tsv.gz\n",
      "-rw-r--r--   1 amandeep  staff    585326 Oct 12 16:34 metadata.out_degree.tsv.gz\n",
      "-rw-r--r--   1 amandeep  staff   2192335 Oct 12 16:34 metadata.pagerank.directed.tsv.gz\n",
      "-rw-r--r--   1 amandeep  staff   2426762 Oct 12 16:34 metadata.pagerank.undirected.tsv.gz\n",
      "-rw-r--r--   1 amandeep  staff      4385 Oct 12 16:35 statistics.in_degree.distribution.tsv\n",
      "-rw-r--r--   1 amandeep  staff      4950 Oct 12 16:35 statistics.out_degree.distribution.tsv\n",
      "drwxr-xr-x  19 amandeep  staff       608 Oct 12 16:35 \u001b[34mtemp.useful_files\u001b[m\u001b[m\n"
     ]
    }
   ],
   "source": [
    "!ls -l $OUT/useful_files"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Enhance pagerank files to include ordinal"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Approach:\n",
    "- Load the `directed_pagerank` from the metadata file into a dataframe (using kypher because somehow cat is broken, sigh\n",
    "- Sort the file by pagerank descending\n",
    "- Add a new column with header `P1545` (ordinal) and store the ranks in this column\n",
    "- Store the result in a temporary file."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "[2021-10-12 16:35:42 sqlstore]: IMPORT graph directly into table graph_1 from /Volumes/saggu-ssd/arnold-2/tutorial-derived-graphs/useful_files/metadata.pagerank.directed.tsv.gz ...\n",
      "[2021-10-12 16:35:43 query]: SQL Translation:\n",
      "---------------------------------------------\n",
      "  SELECT *\n",
      "     FROM graph_1 AS graph_1_c1\n",
      "     WHERE graph_1_c1.\"label\" = ?\n",
      "  PARAS: ['Pdirected_pagerank']\n",
      "---------------------------------------------\n",
      "[2021-10-12 16:35:43 sqlstore]: CREATE INDEX on table graph_1 column label ...\n",
      "[2021-10-12 16:35:43 sqlstore]: ANALYZE INDEX on table graph_1 column label ...\n",
      "\n",
      "CPU times: user 658 ms, sys: 99.8 ms, total: 758 ms\n",
      "Wall time: 2.81 s\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>P1545</th>\n",
       "      <th>node1</th>\n",
       "      <th>label</th>\n",
       "      <th>node2</th>\n",
       "      <th>id</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>26562</th>\n",
       "      <td>1</td>\n",
       "      <td>Q23958852</td>\n",
       "      <td>Pdirected_pagerank</td>\n",
       "      <td>0.071410</td>\n",
       "      <td>Q23958852-Pdirected_pagerank-79688</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>42551</th>\n",
       "      <td>2</td>\n",
       "      <td>Q23960977</td>\n",
       "      <td>Pdirected_pagerank</td>\n",
       "      <td>0.032866</td>\n",
       "      <td>Q23960977-Pdirected_pagerank-127655</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14856</th>\n",
       "      <td>3</td>\n",
       "      <td>Q35120</td>\n",
       "      <td>Pdirected_pagerank</td>\n",
       "      <td>0.028596</td>\n",
       "      <td>Q35120-Pdirected_pagerank-44570</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11192</th>\n",
       "      <td>4</td>\n",
       "      <td>Q151885</td>\n",
       "      <td>Pdirected_pagerank</td>\n",
       "      <td>0.026957</td>\n",
       "      <td>Q151885-Pdirected_pagerank-33578</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>439</th>\n",
       "      <td>5</td>\n",
       "      <td>Q5</td>\n",
       "      <td>Pdirected_pagerank</td>\n",
       "      <td>0.012807</td>\n",
       "      <td>Q5-Pdirected_pagerank-1319</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>38463</th>\n",
       "      <td>66010</td>\n",
       "      <td>Q207482</td>\n",
       "      <td>Pdirected_pagerank</td>\n",
       "      <td>0.000002</td>\n",
       "      <td>Q207482-Pdirected_pagerank-115391</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>38462</th>\n",
       "      <td>66011</td>\n",
       "      <td>Q20747487</td>\n",
       "      <td>Pdirected_pagerank</td>\n",
       "      <td>0.000002</td>\n",
       "      <td>Q20747487-Pdirected_pagerank-115388</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>38455</th>\n",
       "      <td>66012</td>\n",
       "      <td>Q20746713</td>\n",
       "      <td>Pdirected_pagerank</td>\n",
       "      <td>0.000002</td>\n",
       "      <td>Q20746713-Pdirected_pagerank-115367</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>38453</th>\n",
       "      <td>66013</td>\n",
       "      <td>Q20746702</td>\n",
       "      <td>Pdirected_pagerank</td>\n",
       "      <td>0.000002</td>\n",
       "      <td>Q20746702-Pdirected_pagerank-115361</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>66013</th>\n",
       "      <td>66014</td>\n",
       "      <td>Q99975457</td>\n",
       "      <td>Pdirected_pagerank</td>\n",
       "      <td>0.000002</td>\n",
       "      <td>Q99975457-Pdirected_pagerank-198041</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>66014 rows × 5 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "       P1545      node1               label     node2  \\\n",
       "26562      1  Q23958852  Pdirected_pagerank  0.071410   \n",
       "42551      2  Q23960977  Pdirected_pagerank  0.032866   \n",
       "14856      3     Q35120  Pdirected_pagerank  0.028596   \n",
       "11192      4    Q151885  Pdirected_pagerank  0.026957   \n",
       "439        5         Q5  Pdirected_pagerank  0.012807   \n",
       "...      ...        ...                 ...       ...   \n",
       "38463  66010    Q207482  Pdirected_pagerank  0.000002   \n",
       "38462  66011  Q20747487  Pdirected_pagerank  0.000002   \n",
       "38455  66012  Q20746713  Pdirected_pagerank  0.000002   \n",
       "38453  66013  Q20746702  Pdirected_pagerank  0.000002   \n",
       "66013  66014  Q99975457  Pdirected_pagerank  0.000002   \n",
       "\n",
       "                                        id  \n",
       "26562   Q23958852-Pdirected_pagerank-79688  \n",
       "42551  Q23960977-Pdirected_pagerank-127655  \n",
       "14856      Q35120-Pdirected_pagerank-44570  \n",
       "11192     Q151885-Pdirected_pagerank-33578  \n",
       "439             Q5-Pdirected_pagerank-1319  \n",
       "...                                    ...  \n",
       "38463    Q207482-Pdirected_pagerank-115391  \n",
       "38462  Q20747487-Pdirected_pagerank-115388  \n",
       "38455  Q20746713-Pdirected_pagerank-115367  \n",
       "38453  Q20746702-Pdirected_pagerank-115361  \n",
       "66013  Q99975457-Pdirected_pagerank-198041  \n",
       "\n",
       "[66014 rows x 5 columns]"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%time\n",
    "directed_pagerank = kgtk(\"\"\"\n",
    "    query -i $OUT/useful_files/metadata.pagerank.directed.tsv.gz \n",
    "    --match '(n1)-[l:Pdirected_pagerank]->(pagerank)'\n",
    "\"\"\")\n",
    "\n",
    "directed_pagerank_sorted = directed_pagerank.sort_values(\"node2\", ascending=False)\n",
    "directed_pagerank_sorted.insert(0, 'P1545', range(1, 1 + len(directed_pagerank_sorted)))\n",
    "directed_pagerank_sorted.to_csv(f\"{os.environ['TEMP']}/directed-pagerank.ordinal.tsv\", index=False, sep='\\t')\n",
    "directed_pagerank_sorted"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The temporary file looks good, next steps:\n",
    "- `normalize` to put the qualifiers as extra edges so the file has only `node1/label/node2/id`\n",
    "- `add-ids` as we want all edges to have ids"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [],
   "source": [
    "kgtk(\"\"\"\n",
    "    normalize -i \"$TEMP\"/directed-pagerank.ordinal.tsv\n",
    "    / add-id --id-style wikidata \n",
    "    -o \"$OUT\"/useful_files/metadata.pagerank.directed.ordinal.tsv.gz\n",
    "\"\"\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Look at the result to confirm that we are generating the data we want."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "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>node1</th>\n",
       "      <th>label</th>\n",
       "      <th>node2</th>\n",
       "      <th>id</th>\n",
       "      <th>node1;label</th>\n",
       "      <th>label;label</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Q23958852</td>\n",
       "      <td>Pdirected_pagerank</td>\n",
       "      <td>0.071410</td>\n",
       "      <td>Q23958852-Pdirected_pagerank-79688</td>\n",
       "      <td>'variable-order class'@en</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Q23958852-Pdirected_pagerank-79688</td>\n",
       "      <td>P1545</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>Q23958852-Pdirected_pagerank-79688-P1545-6b86b2</td>\n",
       "      <td>NaN</td>\n",
       "      <td>'series ordinal'@en</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Q23960977</td>\n",
       "      <td>Pdirected_pagerank</td>\n",
       "      <td>0.032866</td>\n",
       "      <td>Q23960977-Pdirected_pagerank-127655</td>\n",
       "      <td>'(meta)class'@en</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Q23960977-Pdirected_pagerank-127655</td>\n",
       "      <td>P1545</td>\n",
       "      <td>2.000000</td>\n",
       "      <td>Q23960977-Pdirected_pagerank-127655-P1545-d4735e</td>\n",
       "      <td>NaN</td>\n",
       "      <td>'series ordinal'@en</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Q35120</td>\n",
       "      <td>Pdirected_pagerank</td>\n",
       "      <td>0.028596</td>\n",
       "      <td>Q35120-Pdirected_pagerank-44570</td>\n",
       "      <td>'entity'@en</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>Q35120-Pdirected_pagerank-44570</td>\n",
       "      <td>P1545</td>\n",
       "      <td>3.000000</td>\n",
       "      <td>Q35120-Pdirected_pagerank-44570-P1545-4e0740</td>\n",
       "      <td>NaN</td>\n",
       "      <td>'series ordinal'@en</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>Q151885</td>\n",
       "      <td>Pdirected_pagerank</td>\n",
       "      <td>0.026957</td>\n",
       "      <td>Q151885-Pdirected_pagerank-33578</td>\n",
       "      <td>'concept'@en</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>Q151885-Pdirected_pagerank-33578</td>\n",
       "      <td>P1545</td>\n",
       "      <td>4.000000</td>\n",
       "      <td>Q151885-Pdirected_pagerank-33578-P1545-4b2277</td>\n",
       "      <td>NaN</td>\n",
       "      <td>'series ordinal'@en</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>Q5</td>\n",
       "      <td>Pdirected_pagerank</td>\n",
       "      <td>0.012807</td>\n",
       "      <td>Q5-Pdirected_pagerank-1319</td>\n",
       "      <td>'human'@en</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>Q5-Pdirected_pagerank-1319</td>\n",
       "      <td>P1545</td>\n",
       "      <td>5.000000</td>\n",
       "      <td>Q5-Pdirected_pagerank-1319-P1545-ef2d12</td>\n",
       "      <td>NaN</td>\n",
       "      <td>'series ordinal'@en</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                 node1               label     node2  \\\n",
       "0                            Q23958852  Pdirected_pagerank  0.071410   \n",
       "1   Q23958852-Pdirected_pagerank-79688               P1545  1.000000   \n",
       "2                            Q23960977  Pdirected_pagerank  0.032866   \n",
       "3  Q23960977-Pdirected_pagerank-127655               P1545  2.000000   \n",
       "4                               Q35120  Pdirected_pagerank  0.028596   \n",
       "5      Q35120-Pdirected_pagerank-44570               P1545  3.000000   \n",
       "6                              Q151885  Pdirected_pagerank  0.026957   \n",
       "7     Q151885-Pdirected_pagerank-33578               P1545  4.000000   \n",
       "8                                   Q5  Pdirected_pagerank  0.012807   \n",
       "9           Q5-Pdirected_pagerank-1319               P1545  5.000000   \n",
       "\n",
       "                                                 id  \\\n",
       "0                Q23958852-Pdirected_pagerank-79688   \n",
       "1   Q23958852-Pdirected_pagerank-79688-P1545-6b86b2   \n",
       "2               Q23960977-Pdirected_pagerank-127655   \n",
       "3  Q23960977-Pdirected_pagerank-127655-P1545-d4735e   \n",
       "4                   Q35120-Pdirected_pagerank-44570   \n",
       "5      Q35120-Pdirected_pagerank-44570-P1545-4e0740   \n",
       "6                  Q151885-Pdirected_pagerank-33578   \n",
       "7     Q151885-Pdirected_pagerank-33578-P1545-4b2277   \n",
       "8                        Q5-Pdirected_pagerank-1319   \n",
       "9           Q5-Pdirected_pagerank-1319-P1545-ef2d12   \n",
       "\n",
       "                 node1;label          label;label  \n",
       "0  'variable-order class'@en                  NaN  \n",
       "1                        NaN  'series ordinal'@en  \n",
       "2           '(meta)class'@en                  NaN  \n",
       "3                        NaN  'series ordinal'@en  \n",
       "4                'entity'@en                  NaN  \n",
       "5                        NaN  'series ordinal'@en  \n",
       "6               'concept'@en                  NaN  \n",
       "7                        NaN  'series ordinal'@en  \n",
       "8                 'human'@en                  NaN  \n",
       "9                        NaN  'series ordinal'@en  "
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "kgtk(\"\"\"\n",
    "    head -i \"$OUT\"/useful_files/metadata.pagerank.directed.ordinal.tsv.gz / add-labels\n",
    "\"\"\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Repeat the same steps for `undirected_pagerank`"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "[2021-10-12 16:36:03 sqlstore]: IMPORT graph directly into table graph_2 from /Volumes/saggu-ssd/arnold-2/tutorial-derived-graphs/useful_files/metadata.pagerank.undirected.tsv.gz ...\n",
      "[2021-10-12 16:36:04 query]: SQL Translation:\n",
      "---------------------------------------------\n",
      "  SELECT *\n",
      "     FROM graph_2 AS graph_2_c1\n",
      "     WHERE graph_2_c1.\"label\" = ?\n",
      "  PARAS: ['Pundirected_pagerank']\n",
      "---------------------------------------------\n",
      "[2021-10-12 16:36:04 sqlstore]: CREATE INDEX on table graph_2 column label ...\n",
      "[2021-10-12 16:36:04 sqlstore]: ANALYZE INDEX on table graph_2 column label ...\n",
      "\n",
      "CPU times: user 698 ms, sys: 104 ms, total: 802 ms\n",
      "Wall time: 2.89 s\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>P1545</th>\n",
       "      <th>node1</th>\n",
       "      <th>label</th>\n",
       "      <th>node2</th>\n",
       "      <th>id</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>439</th>\n",
       "      <td>1</td>\n",
       "      <td>Q5</td>\n",
       "      <td>Pundirected_pagerank</td>\n",
       "      <td>0.022010</td>\n",
       "      <td>Q5-Pundirected_pagerank-1319</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>173</th>\n",
       "      <td>2</td>\n",
       "      <td>Q30</td>\n",
       "      <td>Pundirected_pagerank</td>\n",
       "      <td>0.012919</td>\n",
       "      <td>Q30-Pundirected_pagerank-521</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4782</th>\n",
       "      <td>3</td>\n",
       "      <td>Q6581097</td>\n",
       "      <td>Pundirected_pagerank</td>\n",
       "      <td>0.008353</td>\n",
       "      <td>Q6581097-Pundirected_pagerank-14348</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7097</th>\n",
       "      <td>4</td>\n",
       "      <td>Q15221623</td>\n",
       "      <td>Pundirected_pagerank</td>\n",
       "      <td>0.004738</td>\n",
       "      <td>Q15221623-Pundirected_pagerank-21293</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1391</th>\n",
       "      <td>5</td>\n",
       "      <td>Q1860</td>\n",
       "      <td>Pundirected_pagerank</td>\n",
       "      <td>0.004441</td>\n",
       "      <td>Q1860-Pundirected_pagerank-4175</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>64005</th>\n",
       "      <td>66010</td>\n",
       "      <td>Q7958659</td>\n",
       "      <td>Pundirected_pagerank</td>\n",
       "      <td>0.000003</td>\n",
       "      <td>Q7958659-Pundirected_pagerank-192017</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>32231</th>\n",
       "      <td>66011</td>\n",
       "      <td>Q17021934</td>\n",
       "      <td>Pundirected_pagerank</td>\n",
       "      <td>0.000003</td>\n",
       "      <td>Q17021934-Pundirected_pagerank-96695</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>45319</th>\n",
       "      <td>66012</td>\n",
       "      <td>Q27890917</td>\n",
       "      <td>Pundirected_pagerank</td>\n",
       "      <td>0.000003</td>\n",
       "      <td>Q27890917-Pundirected_pagerank-135959</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>35443</th>\n",
       "      <td>66013</td>\n",
       "      <td>Q10876480</td>\n",
       "      <td>Pundirected_pagerank</td>\n",
       "      <td>0.000003</td>\n",
       "      <td>Q10876480-Pundirected_pagerank-106331</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17697</th>\n",
       "      <td>66014</td>\n",
       "      <td>Q1199713</td>\n",
       "      <td>Pundirected_pagerank</td>\n",
       "      <td>0.000003</td>\n",
       "      <td>Q1199713-Pundirected_pagerank-53093</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>66014 rows × 5 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "       P1545      node1                 label     node2  \\\n",
       "439        1         Q5  Pundirected_pagerank  0.022010   \n",
       "173        2        Q30  Pundirected_pagerank  0.012919   \n",
       "4782       3   Q6581097  Pundirected_pagerank  0.008353   \n",
       "7097       4  Q15221623  Pundirected_pagerank  0.004738   \n",
       "1391       5      Q1860  Pundirected_pagerank  0.004441   \n",
       "...      ...        ...                   ...       ...   \n",
       "64005  66010   Q7958659  Pundirected_pagerank  0.000003   \n",
       "32231  66011  Q17021934  Pundirected_pagerank  0.000003   \n",
       "45319  66012  Q27890917  Pundirected_pagerank  0.000003   \n",
       "35443  66013  Q10876480  Pundirected_pagerank  0.000003   \n",
       "17697  66014   Q1199713  Pundirected_pagerank  0.000003   \n",
       "\n",
       "                                          id  \n",
       "439             Q5-Pundirected_pagerank-1319  \n",
       "173             Q30-Pundirected_pagerank-521  \n",
       "4782     Q6581097-Pundirected_pagerank-14348  \n",
       "7097    Q15221623-Pundirected_pagerank-21293  \n",
       "1391         Q1860-Pundirected_pagerank-4175  \n",
       "...                                      ...  \n",
       "64005   Q7958659-Pundirected_pagerank-192017  \n",
       "32231   Q17021934-Pundirected_pagerank-96695  \n",
       "45319  Q27890917-Pundirected_pagerank-135959  \n",
       "35443  Q10876480-Pundirected_pagerank-106331  \n",
       "17697    Q1199713-Pundirected_pagerank-53093  \n",
       "\n",
       "[66014 rows x 5 columns]"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%time\n",
    "undirected_pagerank = kgtk(\"\"\"\n",
    "    query -i $OUT/useful_files/metadata.pagerank.undirected.tsv.gz \n",
    "    --match '(n1)-[l:Pundirected_pagerank]->(pagerank)'\n",
    "\"\"\")\n",
    "\n",
    "undirected_pagerank = undirected_pagerank.sort_values(\"node2\", ascending=False)\n",
    "undirected_pagerank.insert(0, 'P1545', range(1, 1 + len(undirected_pagerank)))\n",
    "undirected_pagerank.to_csv(f\"{os.environ['TEMP']}/undirected-pagerank.ordinal.tsv\", index=False, sep='\\t')\n",
    "undirected_pagerank"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [],
   "source": [
    "kgtk(\"\"\"\n",
    "    normalize -i \"$TEMP\"/undirected-pagerank.ordinal.tsv\n",
    "    / add-id --id-style wikidata \n",
    "    -o \"$OUT\"/useful_files/metadata.pagerank.undirected.ordinal.tsv.gz\n",
    "\"\"\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "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>node1</th>\n",
       "      <th>label</th>\n",
       "      <th>node2</th>\n",
       "      <th>id</th>\n",
       "      <th>node1;label</th>\n",
       "      <th>label;label</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Q5</td>\n",
       "      <td>Pundirected_pagerank</td>\n",
       "      <td>0.022010</td>\n",
       "      <td>Q5-Pundirected_pagerank-1319</td>\n",
       "      <td>'human'@en</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Q5-Pundirected_pagerank-1319</td>\n",
       "      <td>P1545</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>Q5-Pundirected_pagerank-1319-P1545-6b86b2</td>\n",
       "      <td>NaN</td>\n",
       "      <td>'series ordinal'@en</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Q30</td>\n",
       "      <td>Pundirected_pagerank</td>\n",
       "      <td>0.012919</td>\n",
       "      <td>Q30-Pundirected_pagerank-521</td>\n",
       "      <td>'United States of America'@en</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Q30-Pundirected_pagerank-521</td>\n",
       "      <td>P1545</td>\n",
       "      <td>2.000000</td>\n",
       "      <td>Q30-Pundirected_pagerank-521-P1545-d4735e</td>\n",
       "      <td>NaN</td>\n",
       "      <td>'series ordinal'@en</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Q6581097</td>\n",
       "      <td>Pundirected_pagerank</td>\n",
       "      <td>0.008353</td>\n",
       "      <td>Q6581097-Pundirected_pagerank-14348</td>\n",
       "      <td>'male'@en</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>Q6581097-Pundirected_pagerank-14348</td>\n",
       "      <td>P1545</td>\n",
       "      <td>3.000000</td>\n",
       "      <td>Q6581097-Pundirected_pagerank-14348-P1545-4e0740</td>\n",
       "      <td>NaN</td>\n",
       "      <td>'series ordinal'@en</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>Q15221623</td>\n",
       "      <td>Pundirected_pagerank</td>\n",
       "      <td>0.004738</td>\n",
       "      <td>Q15221623-Pundirected_pagerank-21293</td>\n",
       "      <td>'bilateral relation'@en</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>Q15221623-Pundirected_pagerank-21293</td>\n",
       "      <td>P1545</td>\n",
       "      <td>4.000000</td>\n",
       "      <td>Q15221623-Pundirected_pagerank-21293-P1545-4b2277</td>\n",
       "      <td>NaN</td>\n",
       "      <td>'series ordinal'@en</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>Q1860</td>\n",
       "      <td>Pundirected_pagerank</td>\n",
       "      <td>0.004441</td>\n",
       "      <td>Q1860-Pundirected_pagerank-4175</td>\n",
       "      <td>'English'@en</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>Q1860-Pundirected_pagerank-4175</td>\n",
       "      <td>P1545</td>\n",
       "      <td>5.000000</td>\n",
       "      <td>Q1860-Pundirected_pagerank-4175-P1545-ef2d12</td>\n",
       "      <td>NaN</td>\n",
       "      <td>'series ordinal'@en</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                  node1                 label     node2  \\\n",
       "0                                    Q5  Pundirected_pagerank  0.022010   \n",
       "1          Q5-Pundirected_pagerank-1319                 P1545  1.000000   \n",
       "2                                   Q30  Pundirected_pagerank  0.012919   \n",
       "3          Q30-Pundirected_pagerank-521                 P1545  2.000000   \n",
       "4                              Q6581097  Pundirected_pagerank  0.008353   \n",
       "5   Q6581097-Pundirected_pagerank-14348                 P1545  3.000000   \n",
       "6                             Q15221623  Pundirected_pagerank  0.004738   \n",
       "7  Q15221623-Pundirected_pagerank-21293                 P1545  4.000000   \n",
       "8                                 Q1860  Pundirected_pagerank  0.004441   \n",
       "9       Q1860-Pundirected_pagerank-4175                 P1545  5.000000   \n",
       "\n",
       "                                                  id  \\\n",
       "0                       Q5-Pundirected_pagerank-1319   \n",
       "1          Q5-Pundirected_pagerank-1319-P1545-6b86b2   \n",
       "2                       Q30-Pundirected_pagerank-521   \n",
       "3          Q30-Pundirected_pagerank-521-P1545-d4735e   \n",
       "4                Q6581097-Pundirected_pagerank-14348   \n",
       "5   Q6581097-Pundirected_pagerank-14348-P1545-4e0740   \n",
       "6               Q15221623-Pundirected_pagerank-21293   \n",
       "7  Q15221623-Pundirected_pagerank-21293-P1545-4b2277   \n",
       "8                    Q1860-Pundirected_pagerank-4175   \n",
       "9       Q1860-Pundirected_pagerank-4175-P1545-ef2d12   \n",
       "\n",
       "                     node1;label          label;label  \n",
       "0                     'human'@en                  NaN  \n",
       "1                            NaN  'series ordinal'@en  \n",
       "2  'United States of America'@en                  NaN  \n",
       "3                            NaN  'series ordinal'@en  \n",
       "4                      'male'@en                  NaN  \n",
       "5                            NaN  'series ordinal'@en  \n",
       "6        'bilateral relation'@en                  NaN  \n",
       "7                            NaN  'series ordinal'@en  \n",
       "8                   'English'@en                  NaN  \n",
       "9                            NaN  'series ordinal'@en  "
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "kgtk(\"\"\"\n",
    "    head -i \"$OUT\"/useful_files/metadata.pagerank.undirected.ordinal.tsv.gz / add-labels\n",
    "\"\"\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Deploy the tutorial files to `$tutorial_files_path`"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Define the files we want to have in the tutorial"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [],
   "source": [
    "tutorial_files_parts = [\n",
    "    \"labels.en.tsv.gz\",\n",
    "    \"aliases.en.tsv.gz\",\n",
    "    \"descriptions.en.tsv.gz\",\n",
    "    \"claims.external-id.tsv.gz\",\n",
    "    \"claims.monolingualtext.tsv.gz\",\n",
    "    \"claims.quantity.tsv.gz\",\n",
    "    \"claims.string.tsv.gz\",\n",
    "    \"claims.time.tsv.gz\",\n",
    "    \"claims.wikibase-item.tsv.gz\",\n",
    "    \"claims.wikibase-property.tsv.gz\",\n",
    "    \"qualifiers.tsv.gz\"\n",
    "]\n",
    "\n",
    "tutorial_files_useful = [\n",
    "    \"derived.P279.tsv.gz\",\n",
    "    \"derived.P279star.tsv.gz\",\n",
    "    \"derived.P31.tsv.gz\",\n",
    "    \"metadata.in_degree.tsv.gz\",\n",
    "    \"metadata.out_degree.tsv.gz\"\n",
    "]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Deploy the files from the partition and useful notebooks. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [],
   "source": [
    "for file in tutorial_files_parts:\n",
    "    path = \"$OUT/parts/\" + file\n",
    "    !cp -p {path} $tutorial_files_path\n",
    "\n",
    "for file in tutorial_files_useful:\n",
    "    path = \"$OUT/useful_files/\" + file\n",
    "    !cp -p {path} $tutorial_files_path"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Overwrite the original pagerank files with the ones that include ordinal"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [],
   "source": [
    "!cp -p $OUT/useful_files/metadata.pagerank.directed.ordinal.tsv.gz $tutorial_files_path/metadata.pagerank.directed.tsv.gz\n",
    "!cp -p $OUT/useful_files/metadata.pagerank.undirected.ordinal.tsv.gz $tutorial_files_path/metadata.pagerank.undirected.tsv.gz "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Important to deply the custom KGTK properties file. Copy using KGTK to conveniently compress the file."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [],
   "source": [
    "kgtk(\"\"\"\n",
    "    cat \n",
    "        -i \"$kgtk_path\"/kgtk-properties/kgtk.properties.tsv \n",
    "        -i \"$OUT\"/parts/metadata.property.datatypes.tsv.gz\n",
    "        -o \"$tutorial_files_path\"/metadata.property.datatypes.tsv.gz\n",
    "\"\"\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "total 181880\n",
      "-rw-r--r--  1 pedroszekely  staff   1342345 Oct 10 11:52 aliases.en.tsv.gz\n",
      "-rw-r--r--  1 pedroszekely  staff  44564618 Oct 10 11:36 all.tsv.gz\n",
      "-rw-r--r--  1 pedroszekely  staff  13620313 Oct 10 11:52 claims.external-id.tsv.gz\n",
      "-rw-r--r--  1 pedroszekely  staff   1069769 Oct 10 11:52 claims.monolingualtext.tsv.gz\n",
      "-rw-r--r--  1 pedroszekely  staff   1936951 Oct 10 11:52 claims.quantity.tsv.gz\n",
      "-rw-r--r--  1 pedroszekely  staff   1095875 Oct 10 11:52 claims.string.tsv.gz\n",
      "-rw-r--r--  1 pedroszekely  staff    781182 Oct 10 11:52 claims.time.tsv.gz\n",
      "-rw-r--r--  1 pedroszekely  staff   6332200 Oct 10 11:52 claims.wikibase-item.tsv.gz\n",
      "-rw-r--r--  1 pedroszekely  staff     97267 Oct 10 11:52 claims.wikibase-property.tsv.gz\n",
      "-rw-r--r--  1 pedroszekely  staff    390973 Oct 10 11:53 derived.P279.tsv.gz\n",
      "-rw-r--r--  1 pedroszekely  staff   3325552 Oct 10 11:54 derived.P279star.tsv.gz\n",
      "-rw-r--r--  1 pedroszekely  staff   1181395 Oct 10 11:53 derived.P31.tsv.gz\n",
      "-rw-r--r--  1 pedroszekely  staff   1339811 Oct 10 11:52 descriptions.en.tsv.gz\n",
      "-rw-r--r--  1 pedroszekely  staff   1064283 Oct 10 11:52 labels.en.tsv.gz\n",
      "-rw-r--r--  1 pedroszekely  staff    309510 Oct 10 11:54 metadata.in_degree.tsv.gz\n",
      "-rw-r--r--  1 pedroszekely  staff    585326 Oct 10 11:54 metadata.out_degree.tsv.gz\n",
      "-rw-r--r--  1 pedroszekely  staff   1879161 Oct 10 11:55 metadata.pagerank.directed.tsv.gz\n",
      "-rw-r--r--  1 pedroszekely  staff   2198657 Oct 10 11:55 metadata.pagerank.undirected.tsv.gz\n",
      "-rw-r--r--  1 pedroszekely  staff     22631 Oct 10 11:55 metadata.property.datatypes.tsv.gz\n",
      "-rw-r--r--  1 pedroszekely  staff   6009306 Oct 10 11:52 qualifiers.tsv.gz\n"
     ]
    }
   ],
   "source": [
    "!ls -l \"$tutorial_files_path\""
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Create an `all.tsv.gz` file"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "CPU times: user 285 ms, sys: 105 ms, total: 390 ms\n",
      "Wall time: 24 s\n"
     ]
    }
   ],
   "source": [
    "%%time\n",
    "all_file_path = os.environ['tutorial_files_path'] + \"/all.tsv.gz\"\n",
    "if os.path.exists(all_file_path):\n",
    "    !rm {all_file_path}\n",
    "!kgtk cat -i \"$tutorial_files_path\"/*.tsv.gz -o {all_file_path}"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Peek at the file"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "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>node1</th>\n",
       "      <th>label</th>\n",
       "      <th>node2</th>\n",
       "      <th>id</th>\n",
       "      <th>node2;wikidatatype</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>P10</td>\n",
       "      <td>alias</td>\n",
       "      <td>'gif'@en</td>\n",
       "      <td>P10-alias-en-282226-0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>P10</td>\n",
       "      <td>alias</td>\n",
       "      <td>'animation'@en</td>\n",
       "      <td>P10-alias-en-2f86d8-0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>P10</td>\n",
       "      <td>alias</td>\n",
       "      <td>'media'@en</td>\n",
       "      <td>P10-alias-en-c1427e-0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>P10</td>\n",
       "      <td>alias</td>\n",
       "      <td>'trailer (Commons)'@en</td>\n",
       "      <td>P10-alias-en-c61ab1-0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>P1001</td>\n",
       "      <td>alias</td>\n",
       "      <td>'belongs to jurisdiction'@en</td>\n",
       "      <td>P1001-alias-en-0dd7ce-0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>P1001</td>\n",
       "      <td>alias</td>\n",
       "      <td>'linked to jurisdiction'@en</td>\n",
       "      <td>P1001-alias-en-106818-0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>P1001</td>\n",
       "      <td>alias</td>\n",
       "      <td>'of jurisdiction'@en</td>\n",
       "      <td>P1001-alias-en-7e4abe-0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>P1001</td>\n",
       "      <td>alias</td>\n",
       "      <td>'applied to jurisdiction'@en</td>\n",
       "      <td>P1001-alias-en-89ed18-0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>P1001</td>\n",
       "      <td>alias</td>\n",
       "      <td>'jurisdiction'@en</td>\n",
       "      <td>P1001-alias-en-a524ab-0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>P1001</td>\n",
       "      <td>alias</td>\n",
       "      <td>'valid in jurisdiction'@en</td>\n",
       "      <td>P1001-alias-en-ca2e7c-0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   node1  label                         node2                       id  \\\n",
       "0    P10  alias                      'gif'@en    P10-alias-en-282226-0   \n",
       "1    P10  alias                'animation'@en    P10-alias-en-2f86d8-0   \n",
       "2    P10  alias                    'media'@en    P10-alias-en-c1427e-0   \n",
       "3    P10  alias        'trailer (Commons)'@en    P10-alias-en-c61ab1-0   \n",
       "4  P1001  alias  'belongs to jurisdiction'@en  P1001-alias-en-0dd7ce-0   \n",
       "5  P1001  alias   'linked to jurisdiction'@en  P1001-alias-en-106818-0   \n",
       "6  P1001  alias          'of jurisdiction'@en  P1001-alias-en-7e4abe-0   \n",
       "7  P1001  alias  'applied to jurisdiction'@en  P1001-alias-en-89ed18-0   \n",
       "8  P1001  alias             'jurisdiction'@en  P1001-alias-en-a524ab-0   \n",
       "9  P1001  alias    'valid in jurisdiction'@en  P1001-alias-en-ca2e7c-0   \n",
       "\n",
       "   node2;wikidatatype  \n",
       "0                 NaN  \n",
       "1                 NaN  \n",
       "2                 NaN  \n",
       "3                 NaN  \n",
       "4                 NaN  \n",
       "5                 NaN  \n",
       "6                 NaN  \n",
       "7                 NaN  \n",
       "8                 NaN  \n",
       "9                 NaN  "
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "kgtk(\"\"\"\n",
    "    head -i \"$tutorial_files_path\"/all.tsv.gz\n",
    "\"\"\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Run the KGTK validator on the new knowledge graph"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\n",
      "====================================================\n",
      "Data lines read: 2614949\n",
      "Data lines passed: 2614949\n",
      "CPU times: user 1.34 s, sys: 426 ms, total: 1.76 s\n",
      "Wall time: 2min 3s\n"
     ]
    }
   ],
   "source": [
    "%%time\n",
    "!kgtk validate -i \"$tutorial_files_path\"/all.tsv.gz \\\n",
    "    --allow-wikidata-lq-strings True \\\n",
    "    --ignore-minimum-year True \\\n",
    "    --ignore-maximum-year True"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "kgtk-env",
   "language": "python",
   "name": "kgtk-env"
  },
  "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.7.9"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}