{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {
    "tags": []
   },
   "source": [
    "# P1963-computed: A graph recording the properties used in all instances of all classes\n",
    "\n",
    "Wikidata has a property `properties for this type (P1963)` where editors can manually specify the properties that should be used to describe the instance of a class. \n",
    "This property is only defined for a small number of classes, and the properties listed are incomplete. In this use-case we compute the properties used for each class from the instances.\n",
    "\n",
    "We invent a new property `P1963computed` to record the properties used in the instances of each class. In addition, we compute the counts, which we store as a quailifier `quantity (P1114)`\n",
    "\n",
    "We compute two variants of these counts:\n",
    "- `P1963computed` records the properties and counts for the direct instance of a class, includeing `P31x`\n",
    "- `P1963computed_star` records the properties including the instances of every subclass below a class.\n",
    "\n",
    "Properties computed in this notebook:\n",
    "- `P31x`, a generalization of `instance of: P31`, sotred in `derived.P31x.tsv`\n",
    "- `Pinstance_count`, counts the number of `P31/P31x` instances of a class, in `statistics.Pinstance_count.tsv.gz`\n",
    "- `Pinstance_count_star`, same as `Pinstance_count`, but includes instances of the subclasses, in `statistics.Pinstance_count.star.tsv.gz`\n",
    "- `P1963computed` in `derived.class.P1963computed.count.tsv.gz`\n",
    "- `P1963computed_star` in `derived.P1963computed.count.star.tsv.gz`\n",
    "- `Pproperty_domain` in `derived.Pproperty_domain.tsv.gz`\n",
    "\n",
    "### `derived.P31x.tsv` contains the union of `P31` and `P31x`:\n",
    "Below is an example of some of the edges for Arnold Schwarzenegger:\n",
    "\n",
    "| node1 | label | node2 | id | node1;label | label;label | node2;label |\n",
    "| -- | -- | -- | -- | -- | -- | -- |\n",
    "| Q2685 | P31 | Q5 | Q2685-P31-Q5-eb1e33fe-0 | 'Arnold Schwarzenegger'@en | 'instance of'@en | 'human'@en |\n",
    "| Q2685 | P31x | Q10800557 | Q2685-P31x-Q10800557 | 'Arnold Schwarzenegger'@en |  | 'film actor'@en |\n",
    "| Q2685 | P31x | Q124100 | Q2685-P31x-Q124100 | 'Arnold Schwarzenegger'@en |  | 'bodybuilding'@en |\n",
    "| Q2685 | P31x | Q131524 | Q2685-P31x-Q131524 | 'Arnold Schwarzenegger'@en |  | 'entrepreneur'@en |\n",
    "| Q2685 | P31x | Q5 | Q2685-P31x-Q5 | 'Arnold Schwarzenegger'@en |  | 'human'@en |\n",
    "\n",
    "\n",
    "### `Pinstance_count` and `Pinstance_count_star` examples\n",
    "\n",
    "| node1 | label | node2 | id | node1;label |\n",
    "| -- | -- | -- | -- | -- |\n",
    "| Q1000017 | Pinstance_count | 1 | Q1000017-Pinstance_count-6b86b2 | 'Brentidae'@en |\n",
    "| Q1000091 | Pinstance_count | 1 | Q1000091-Pinstance_count-6b86b2 | 'Rychnov nad Kněžnou'@en |\n",
    "| Q1000156 | Pinstance_count | 11 | Q1000156-Pinstance_count-4fc82b |  |\n",
    "| Q100023 | Pinstance_count | 1 | Q100023-Pinstance_count-6b86b2 | 'metasyntactic variable'@en |\n",
    "| Q100026 | Pinstance_count | 1 | Q100026-Pinstance_count-6b86b2 | 'F-16'@en |\n",
    "\n",
    "| node1 | label | node2 | id | node1;label |\n",
    "| -- | -- | -- | -- | -- |\n",
    "| Q1000068 | Pinstance_count_star | 13 | Q1000068-Pinstance_count_star-3fdba3 | 'Planungsverband'@en |\n",
    "| Q100023 | Pinstance_count_star | 1 | Q100023-Pinstance_count_star-6b86b2 | 'metasyntactic variable'@en |\n",
    "| Q100026 | Pinstance_count_star | 9 | Q100026-Pinstance_count_star-19581e | 'F-16'@en |\n",
    "| Q100029091 | Pinstance_count_star | 10 | Q100029091-Pinstance_count_star-4a44dc |  |\n",
    "| Q1000300 | Pinstance_count_star | 2 | Q1000300-Pinstance_count_star-d4735e | 'Land Rover Series'@en |\n",
    "\n",
    "### How to use `derived.class.P1963computed.tsv.gz` and `derived.P1963computed.star.tsv.gz`\n",
    "\n",
    "Here is an example query illustrating how to find the properties for a class, the counts and proportion:\n",
    "```\n",
    "kgtk(\"\"\"\n",
    "    query -i $OUT/derived.class.P1963computed.count.tsv.gz\n",
    "        --match '\n",
    "            (n1:Q100026)-[eid:P1963computed]->(n2), \n",
    "            (eid)-[:P1114]->(count),\n",
    "            (eid)-[:P1107]->(proportion)'\n",
    "        --return 'distinct n1 as node1, eid.label as label, n2 as node2, count as P1114, proportion as P1107'\n",
    "        --order-by 'cast(P1114, int) desc'\n",
    "        --limit 10\n",
    "    / add-labels\n",
    "    / md\n",
    "\"\"\")\n",
    "```\n",
    "The results:\n",
    "\n",
    "| node1 | label | node2 | P1114 | P1107 | node1;label | node2;label |\n",
    "| -- | -- | -- | -- | -- | -- | -- |\n",
    "| Q100026 | P1963computed | P17 | 1 | 0.015151515151515152 | 'F-16'@en | 'country'@en |\n",
    "| Q100026 | P1963computed | P18 | 1 | 0.015151515151515152 | 'F-16'@en | 'image'@en |\n",
    "| Q100026 | P1963computed | P31 | 1 | 0.015151515151515152 | 'F-16'@en | 'instance of'@en |\n",
    "| Q100026 | P1963computed | P373 | 1 | 0.015151515151515152 | 'F-16'@en | 'Commons category'@en |\n",
    "| Q100026 | P1963computed | P646 | 1 | 0.015151515151515152 | 'F-16'@en | 'Freebase ID'@en |\n",
    "\n",
    "\n",
    "Here is the equivalent query on the `derived.P1963computed.star.tsv.gz`\n",
    "```\n",
    "kgtk(\"\"\"\n",
    "    query -i $OUT/derived.P1963computed.count.star.tsv.gz\n",
    "        --match '\n",
    "            (n1:Q100026)-[eid:P1963computed_star]->(n2), \n",
    "            (eid)-[:P1114]->(count),\n",
    "            (eid)-[:P1107]->(proportion)'\n",
    "        --return 'distinct n1 as node1, eid.label as label, n2 as node2, count as P1114, proportion as P1107'\n",
    "        --order-by 'cast(P1114, int) desc'\n",
    "        --limit 10\n",
    "    / add-labels\n",
    "    / md\n",
    "\"\"\")\n",
    "```\n",
    "Note that there are many more results for the `P1963computed_star` property as it includes the instances of the subclasses of F-16. For example, there are 10 instances of subclassess of F-16 that have a `P31` property, and that is about 15% of all instances of subclasses of F-16, so many don't have a `P31`, which is add:\n",
    "\n",
    "| node1 | label | node2 | P1114 | P1107 | node1;label | node2;label |\n",
    "| -- | -- | -- | -- | -- | -- | -- |\n",
    "| Q100026 | P1963computed_star | P31 | 10 | 0.15151515151515152 | 'F-16'@en | 'instance of'@en |\n",
    "| Q100026 | P1963computed_star | P361 | 8 | 0.12121212121212122 | 'F-16'@en | 'part of'@en |\n",
    "| Q100026 | P1963computed_star | P729 | 8 | 0.12121212121212122 | 'F-16'@en | 'service entry'@en |\n",
    "| Q100026 | P1963computed_star | P426 | 7 | 0.10606060606060606 | 'F-16'@en | 'aircraft registration'@en |\n",
    "| Q100026 | P1963computed_star | P176 | 6 | 0.09090909090909091 | 'F-16'@en | 'manufacturer'@en |\n",
    "| Q100026 | P1963computed_star | P730 | 6 | 0.09090909090909091 | 'F-16'@en | 'service retirement'@en |\n",
    "| Q100026 | P1963computed_star | P137 | 4 | 0.06060606060606061 | 'F-16'@en | 'operator'@en |\n",
    "| Q100026 | P1963computed_star | P373 | 2 | 0.030303030303030304 | 'F-16'@en | 'Commons category'@en |\n",
    "| Q100026 | P1963computed_star | P17 | 1 | 0.015151515151515152 | 'F-16'@en | 'country'@en |\n",
    "| Q100026 | P1963computed_star | P18 | 1 | 0.015151515151515152 | 'F-16'@en | 'image'@en |\n",
    "\n",
    "### Example of `Pproperty_domain`\n",
    "\n",
    "Here is a query to get all the domains of `cast member (P161)`\n",
    "```\n",
    "kgtk(\"\"\"\n",
    "    query -i $OUT/derived.Pproperty_domain.tsv.gz --idx mode:graph\n",
    "        --match '\n",
    "            (property:P161)-[edge_id:Pproperty_domain]->(class),\n",
    "            (edge_id)-[:P1114]->(instance_count),\n",
    "            (edge_id)-[:P1107]->(proportion)'\n",
    "        --return 'distinct property as node1, \"Pproperty_domain\" as label, class as node2, instance_count as P1114, proportion as P1107'\n",
    "        --order-by 'property, cast(P1114, float)*cast(P1107, float)  desc'\n",
    "    / add-labels\n",
    "\"\"\")\n",
    "```\n",
    "\n",
    "For example, there are 171111 instances of subclasses of film that have cast member, and that is about 54% of all instances of subclasses of film:\n",
    "\n",
    "| node1 | label | node2 | P1114 | P1107 | node1;label | node2;label |\n",
    "| -- | -- | -- | -- | -- | -- | -- |\n",
    "| P161 | Pproperty_domain | Q11424 | 171111 | 0.5425945832818044 | 'cast member'@en | 'film'@en |\n",
    "| P161 | Pproperty_domain | Q10301427 | 197378 | 0.38757120500167885 | 'cast member'@en | 'moving image'@en |\n",
    "| P161 | Pproperty_domain | Q79782 | 197379 | 0.3420157510331742 | 'cast member'@en | 'motion'@en |\n",
    "| P161 | Pproperty_domain | Q17320256 | 197379 | 0.34154997819661803 | 'cast member'@en | 'physical process'@en |\n",
    "| P161 | Pproperty_domain | Q2431196 | 198160 | 0.3382660960616991 | 'cast member'@en | 'audiovisual work'@en |\n",
    "| P161 | Pproperty_domain | Q758901 | 198160 | 0.3382649412013731 | 'cast member'@en | 'audiovisual media'@en |\n",
    "| P161 | Pproperty_domain | Q58898430 | 198160 | 0.33826436377416724 | 'cast member'@en | 'electronic supplementary materials'@en |\n",
    "| P161 | Pproperty_domain | Q58901077 | 198160 | 0.33826320892566947 | 'cast member'@en | 'publication format'@en |"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Preamble: set up the environment and files used in the tutorial"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2021-12-18T00:25:12.851217Z",
     "iopub.status.busy": "2021-12-18T00:25:12.850914Z",
     "iopub.status.idle": "2021-12-18T00:25:27.536637Z",
     "shell.execute_reply": "2021-12-18T00:25:27.535760Z",
     "shell.execute_reply.started": "2021-12-18T00:25:12.851146Z"
    },
    "tags": []
   },
   "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",
    "import papermill as pm\n",
    "from graph_tool.all import *\n",
    "from IPython.display import HTML, display\n",
    "from kgtk.configure_kgtk_notebooks import ConfigureKGTK\n",
    "from kgtk.functions import kgtk, kypher"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2021-12-18T00:25:27.538639Z",
     "iopub.status.busy": "2021-12-18T00:25:27.538331Z",
     "iopub.status.idle": "2021-12-18T00:25:27.542951Z",
     "shell.execute_reply": "2021-12-18T00:25:27.542052Z",
     "shell.execute_reply.started": "2021-12-18T00:25:27.538582Z"
    },
    "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 = \"/Volumes/GoogleDrive/Shared drives/KGTK/datasets/wikidata-20210215/data/\"\n",
    "input_path = \"/Volumes/GoogleDrive/Shared drives/KGTK/datasets/wikidata-20210215-dwd-v2/\"\n",
    "output_path = \"/Users/pedroszekely/Downloads/kypher/projects\"\n",
    "\n",
    "graph_cache_path = \"/Volumes/crucial/wikidata-cache/wikidata.sqlite3.db\"\n",
    "\n",
    "project_name = \"p1963\""
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Tell SQLite to write the temporary files in a specific location where you have a lot of space as this notebook creates huge temporary files (not sure how large, but > 250GB)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2021-12-18T00:25:27.544082Z",
     "iopub.status.busy": "2021-12-18T00:25:27.543712Z",
     "iopub.status.idle": "2021-12-18T00:25:27.546798Z",
     "shell.execute_reply": "2021-12-18T00:25:27.546380Z",
     "shell.execute_reply.started": "2021-12-18T00:25:27.544061Z"
    },
    "tags": []
   },
   "outputs": [],
   "source": [
    "os.environ['SQLITE_TMPDIR'] = \"/Volumes/crucial/sqlite/temp/\""
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Our Wikidata distribution partitions the knowledge in Wikidata into smaller files that make it possible for you to pick and choose which files you want to use:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2021-12-18T00:25:27.548374Z",
     "iopub.status.busy": "2021-12-18T00:25:27.548203Z",
     "iopub.status.idle": "2021-12-18T00:25:27.553699Z",
     "shell.execute_reply": "2021-12-18T00:25:27.553206Z",
     "shell.execute_reply.started": "2021-12-18T00:25:27.548356Z"
    },
    "tags": []
   },
   "outputs": [],
   "source": [
    "files = [\n",
    "    \"claims\",\n",
    "    \"item\",\n",
    "    \"datatypes\",\n",
    "    \"p279\",\n",
    "    \"p279star\",\n",
    "    #\"derived.dwd.count\"\n",
    "]\n",
    "ck = ConfigureKGTK(files, kgtk_path=kgtk_path)\n",
    "ck.configure_kgtk(input_graph_path=input_path,\n",
    "                  output_path=output_path,\n",
    "                  graph_cache_path=graph_cache_path,\n",
    "                  project_name=project_name,\n",
    "                  debug=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The KGTK setup command defines environment variables for all the files so that you can reuse the Jupyter notebook when you install it on your local machine."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2021-12-18T00:25:27.554754Z",
     "iopub.status.busy": "2021-12-18T00:25:27.554556Z",
     "iopub.status.idle": "2021-12-18T00:25:27.557865Z",
     "shell.execute_reply": "2021-12-18T00:25:27.557386Z",
     "shell.execute_reply.started": "2021-12-18T00:25:27.554736Z"
    },
    "tags": []
   },
   "outputs": [],
   "source": [
    "ck.print_env_variables()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2021-12-18T00:25:27.559014Z",
     "iopub.status.busy": "2021-12-18T00:25:27.558878Z",
     "iopub.status.idle": "2021-12-18T00:25:30.526023Z",
     "shell.execute_reply": "2021-12-18T00:25:30.525208Z",
     "shell.execute_reply.started": "2021-12-18T00:25:27.558995Z"
    },
    "tags": []
   },
   "outputs": [],
   "source": [
    "ck.load_files_into_cache()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Specify the indices we want for our files as the queries we do in this notebook are very expensive:\n",
    "- `claims:` indexed as a graph because we query based on node1, label and node2\n",
    "- `p279star:` indexed as a monograph because the file has a single property."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2021-12-12T00:02:48.827215Z",
     "iopub.status.busy": "2021-12-12T00:02:48.827004Z",
     "iopub.status.idle": "2021-12-12T00:53:31.133473Z",
     "shell.execute_reply": "2021-12-12T00:53:31.129097Z",
     "shell.execute_reply.started": "2021-12-12T00:02:48.827190Z"
    },
    "tags": []
   },
   "outputs": [],
   "source": [
    "!kgtk --debug query -i claims --idx mode:graph --limit 5"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2021-12-12T01:26:39.330282Z",
     "iopub.status.busy": "2021-12-12T01:26:39.330013Z",
     "iopub.status.idle": "2021-12-12T01:43:12.590422Z",
     "shell.execute_reply": "2021-12-12T01:43:12.589398Z",
     "shell.execute_reply.started": "2021-12-12T01:26:39.330246Z"
    },
    "tags": []
   },
   "outputs": [],
   "source": [
    "!kgtk --debug query -i item --idx mode:graph --limit 5"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "# Example of how to clear the indices and reindex\n",
    "# !kgtk --debug query -i claims --idx mode:clear mode:graph -i p279star --idx mode:clear mode:monograph --limit 5"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "tags": []
   },
   "source": [
    "## Define `P31x`, a generalization of `instance of: P31`\n",
    "\n",
    "In our KG we are going to define a new property called `instance of (generalized): P31x` that behaves like DBpedia, so that we can ask for instances of `artist: Q483501`.\n",
    "We do this by generalizing `occupation: P106` abd `position held: 39` to also behave as `P31` statements.\n",
    "\n",
    "Approach:\n",
    "- Combine `x P31 y`, `x P106 y` and `x P39 y` statements using a new `P31x` predicate"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "kgtk(\"\"\"\n",
    "    query -i claims\n",
    "        --match '\n",
    "            (n1)-[:P31]->(:Q5),\n",
    "            (n1)-[r {label: property}]->(n2)'\n",
    "        --where 'property in [\"P106\", \"P39\", \"P31\"]'\n",
    "        --return 'distinct n1 as node1, \"P31x\" as label, n2 as node2'\n",
    "        --limit 10000\n",
    "    / add-id --id-style wikidata\n",
    "    / cat -i - -i \"$p31\"\n",
    "    / compact\n",
    "    -o $OUT/derived.P31x.tsv.gz\n",
    "\"\"\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "kgtk(\"\"\"\n",
    "    query -i claims\n",
    "        --match '\n",
    "            (n1)-[:P31]->(:Q5),\n",
    "            (n1)-[r {label: property}]->(n2)'\n",
    "        --where 'property in [\"P106\", \"P39\", \"P31\"]'\n",
    "        --return 'distinct n1 as node1, \"P31x\" as label, n2 as node2'\n",
    "        --limit 1000\n",
    "    / add-id --id-style wikidata\n",
    "    / cat -i - -i \"$p31\"\n",
    "    / compact\n",
    "    -o $OUT/derived.P31x.tsv.gz\n",
    "\"\"\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Load the `p31x` graph defining our generalized `instance of` property:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2021-12-12T03:30:54.901731Z",
     "iopub.status.busy": "2021-12-12T03:30:54.901442Z",
     "iopub.status.idle": "2021-12-12T03:36:04.356255Z",
     "shell.execute_reply": "2021-12-12T03:36:04.355375Z",
     "shell.execute_reply.started": "2021-12-12T03:30:54.901703Z"
    }
   },
   "outputs": [],
   "source": [
    "kgtk(\"\"\"\n",
    "    query -i $OUT/derived.P31x.tsv.gz --as p31x --limit 2\n",
    "\"\"\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2021-12-23T00:09:48.769302Z",
     "iopub.status.busy": "2021-12-23T00:09:48.769019Z",
     "iopub.status.idle": "2021-12-23T00:11:23.837717Z",
     "shell.execute_reply": "2021-12-23T00:11:23.836898Z",
     "shell.execute_reply.started": "2021-12-23T00:09:48.769273Z"
    },
    "tags": []
   },
   "outputs": [],
   "source": [
    "kgtk(\"query -i p31x --match '(:Q2685)-[edgeid]->(n2)' / head -n 14 / add-labels / md\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Instance Counts"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2021-12-12T19:13:42.044091Z",
     "iopub.status.busy": "2021-12-12T19:13:42.043850Z",
     "iopub.status.idle": "2021-12-12T19:16:11.787800Z",
     "shell.execute_reply": "2021-12-12T19:16:11.786881Z",
     "shell.execute_reply.started": "2021-12-12T19:13:42.044068Z"
    }
   },
   "outputs": [],
   "source": [
    "kgtk(\"\"\"\n",
    "    query -i p31x\n",
    "        --match '(instance)-[]->(class)'\n",
    "        --return 'distinct class as node1, \"Pinstance_count\" as label, count(distinct instance) as node2'\n",
    "        --order-by 'class'\n",
    "    / add-id --id-style wikidata\n",
    "    -o $OUT/statistics.Pinstance_count.tsv.gz\n",
    "\"\"\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2021-12-23T05:13:46.927753Z",
     "iopub.status.busy": "2021-12-23T05:13:46.927475Z",
     "iopub.status.idle": "2021-12-23T05:15:29.099374Z",
     "shell.execute_reply": "2021-12-23T05:15:29.098652Z",
     "shell.execute_reply.started": "2021-12-23T05:13:46.927726Z"
    },
    "tags": []
   },
   "outputs": [],
   "source": [
    "kgtk(\"head -i $OUT/statistics.Pinstance_count.tsv.gz -n 5 / add-labels / md\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2021-12-12T23:29:04.073578Z",
     "iopub.status.busy": "2021-12-12T23:29:04.073147Z",
     "iopub.status.idle": "2021-12-12T23:29:22.930157Z",
     "shell.execute_reply": "2021-12-12T23:29:22.929471Z",
     "shell.execute_reply.started": "2021-12-12T23:29:04.073543Z"
    }
   },
   "outputs": [],
   "source": [
    "!kgtk --debug query -i $OUT/statistics.Pinstance_count.tsv.gz --as p31xcount --idx mode:graph --limit 3"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2021-12-23T01:20:51.833474Z",
     "iopub.status.busy": "2021-12-23T01:20:51.833136Z",
     "iopub.status.idle": "2021-12-23T02:55:57.954121Z",
     "shell.execute_reply": "2021-12-23T02:55:57.949304Z",
     "shell.execute_reply.started": "2021-12-23T01:20:51.833442Z"
    },
    "tags": []
   },
   "outputs": [],
   "source": [
    "kgtk(\"\"\"\n",
    "    query -i p31x -i p279star\n",
    "        --match '\n",
    "            p31x: (instance)-[]->(hop),\n",
    "            p279star: (hop)-[]->(class)'\n",
    "        --return 'distinct class as node1, \"Pinstance_count_star\" as label, count(distinct instance) as node2'\n",
    "        --order-by 'class'\n",
    "    / add-id --id-style wikidata\n",
    "    -o $OUT/statistics.Pinstance_count.star.tsv.gz\n",
    "\"\"\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2021-12-23T05:20:04.213367Z",
     "iopub.status.busy": "2021-12-23T05:20:04.213099Z",
     "iopub.status.idle": "2021-12-23T05:20:10.098151Z",
     "shell.execute_reply": "2021-12-23T05:20:10.097278Z",
     "shell.execute_reply.started": "2021-12-23T05:20:04.213341Z"
    },
    "tags": []
   },
   "outputs": [],
   "source": [
    "!kgtk --debug query -i $OUT/statistics.Pinstance_count.star.tsv.gz --as p31xcountstar --idx mode:graph --limit 3"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2021-12-23T05:20:10.100985Z",
     "iopub.status.busy": "2021-12-23T05:20:10.100358Z",
     "iopub.status.idle": "2021-12-23T05:21:37.581007Z",
     "shell.execute_reply": "2021-12-23T05:21:37.580269Z",
     "shell.execute_reply.started": "2021-12-23T05:20:10.100952Z"
    },
    "tags": []
   },
   "outputs": [],
   "source": [
    "kgtk(\"head -i $OUT/statistics.Pinstance_count.star.tsv.gz -n 5 / add-labels / md\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "tags": []
   },
   "source": [
    "## Compute the number of times each property appears in a class\n",
    "\n",
    "In this section we will compute the distribution of the use of properties in every class in th KG. \n",
    "We want to know the count of the different properties used in all instance of a class.\n",
    "For example, if we look at `film (Q11424)` we want to see what properties are used to describe films, including all subclasses of film.\n",
    "\n",
    "Computing this distribution is challenging because we want to include the instances of subclasses, and class hierarchy in Wikidata is ver large.\n",
    "\n",
    "Approach:\n",
    "- Compute a graph that represents the properties used to describe an entity. We will use `Phas_property` to record the properties used in each entity.\n",
    "- Aggregate the counts over all the instances of a class, inlcuding instances of the subclasses.\n",
    "  - Exact method: forl each class, enumerate all the subclasses, and for each subclass enumerate all its intances, and then count the ocurrences of the properties. The exact method works for small graphs, but for DWD (Wikidata scale), the exact method will not return a result even after 2 days.\n",
    "  - Approximate method: \n",
    "    - Count the distribution of properties for all instances of a class, not including subclasses\n",
    "    - For every class, sum the property counts over the direct children of the class. This is a recursive definition, as we need to calculate the sum for the children before that we can aggregate up to the paprent. KGTK does not support recursive queries, so we will do this in a dumb way, so for every class we add the number of all the subclasses and below\n",
    "    \n",
    "  > The parent/child implementation of the approximate method is approximate because we may overcount classes: given classes `A` and `B` such that `A` is below `B`, if there are multiple distinct paths from `A` to `B`, we will double count. The advantage of the dumb method is that it does not double-count"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Compute the `Phas_property`, the set of properties used in each entity"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "scrolled": true,
    "tags": []
   },
   "outputs": [],
   "source": [
    "# use for subject properties\n",
    "kypher = \"claims: (entity)-[l {label: property}]->()\"\n",
    "# use for object properties\n",
    "# kypher = \"claims: ()-[l {label: property}]->(entity)\"\n",
    "\n",
    "kgtk(\"\"\"\n",
    "    query -i p31x -i claims\n",
    "        --match '\n",
    "            p31x: (entity)-[]->(),\n",
    "            {kypher}'\n",
    "        --return 'distinct entity as node1, \"Phas_property\" as label, property as node2'\n",
    "    / add-id --id-style wikidata\n",
    "    -o $TEMP/item_properties.tsv.gz\n",
    "\"\"\")\n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Show the results for `Terminator 2: Judgment Day (Q170564)`"
   ]
  },
  {
   "cell_type": "raw",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2021-11-23T02:37:02.430932Z",
     "iopub.status.busy": "2021-11-23T02:37:02.430692Z",
     "iopub.status.idle": "2021-11-23T02:45:26.756790Z",
     "shell.execute_reply": "2021-11-23T02:45:26.756083Z",
     "shell.execute_reply.started": "2021-11-23T02:37:02.430906Z"
    },
    "tags": []
   },
   "source": [
    "kgtk(\"filter --use-graph-cache-envar=FALSE -i $TEMP/item_properties.tsv.gz -p 'Q170564;;'\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Compute `P1963computed`\n",
    "\n",
    "For every class record the count of each property in all instances of the class according to the `P131x` graph which contains the `P131x` and `P131` statements."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "kgtk(\"\"\"\n",
    "    query -i p31x -i $TEMP/item_properties.tsv.gz\n",
    "        --match '  \n",
    "            p31x: (entity)-[]->(class),\n",
    "            item_properties: (entity)-[l]->(property)'\n",
    "        --return 'distinct class as node1, \"P1963computed\" as label, property as node2, count(distinct l) as P1114'\n",
    "    / add-id --id-style wikidata\n",
    "    / normalize --add-id True\n",
    "    -o $OUT/derived.class.P1963computed.tsv.gz\n",
    "\"\"\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Calculate the proportion of instances of a class that have each property, recording the proportion using property `proportion (P1107)`"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2021-12-23T00:48:27.890208Z",
     "iopub.status.busy": "2021-12-23T00:48:27.889950Z",
     "iopub.status.idle": "2021-12-23T00:50:10.606853Z",
     "shell.execute_reply": "2021-12-23T00:50:10.605988Z",
     "shell.execute_reply.started": "2021-12-23T00:48:27.890179Z"
    },
    "tags": []
   },
   "outputs": [],
   "source": [
    "kgtk(\"\"\"\n",
    "    query -i $OUT/derived.class.P1963computed.tsv.gz -i \"$GRAPH\"/derived.dwd.count.tsv.gz\n",
    "        --match '\n",
    "            computed:\n",
    "                (class)-[edge_id:P1963computed]->(property),\n",
    "                (edge_id)-[:P1114]->(property_count),\n",
    "            count:\n",
    "                (class)-[]->(instance_count)'\n",
    "        --return 'edge_id as node1, \"P1107\" as label, cast(property_count, float)/cast(instance_count, float) as node2'\n",
    "    / add-id --id-style wikidata\n",
    "    / cat -i - -i $OUT/derived.class.P1963computed.tsv.gz\n",
    "    / sort\n",
    "    -o $OUT/derived.class.P1963computed.count.tsv.gz\n",
    "\"\"\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "In the second step, we use `P279star` to iterate over all classes, finding all subclasses below each superclass, and we sum the counts for the properties.\n",
    "\n",
    "\n",
    "In the return clause, we list `superclass`, and the value of the `property` variable ahead of the `count` clause to tell KGTK that we want to aggregate by superclass and property. We reuse the Wikidata `quantity (P1114)` to record the counts:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "kgtk(\"\"\"\n",
    "    query -i $OUT/derived.class.P1963computed.tsv.gz -i p279star \\\n",
    "    --match ' \n",
    "        P1963computed: \n",
    "            (class)-[edge_id]->(property), \n",
    "            (edge_id)-[:P1114]->(count),\n",
    "        p279star: \n",
    "            (class)-[:P279star]->(superclass)' \n",
    "    --return 'distinct superclass as node1, \"P1963computed_star\" as label, property as node2, sum(count) as P1114'\n",
    "    / add-id --id-style wikidata\n",
    "    / normalize --add-id True\n",
    "    -o \"$OUT\"/derived.P1963computed.star.tsv.gz\n",
    "\"\"\")\n",
    "#"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The following query claculates `P1963computed_x` in one go, by counting over all instances of all sublasses of each superclass. This query is too expensive: it will not complete after 2 days when running on DWD."
   ]
  },
  {
   "cell_type": "raw",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2021-11-15T13:27:31.982873Z",
     "iopub.status.busy": "2021-11-15T13:27:31.981587Z"
    }
   },
   "source": [
    "kgtk(\"\"\"\n",
    "    query -i p279star -i p31x -i $TEMP/item_properties.tsv\n",
    "        --match ' \n",
    "            p31x: (entity)-[]->(class), \n",
    "            p279star: (class)-[:P279star]->(superclass),\n",
    "            item_properties: (entity)-[l]->(property)'\n",
    "        --return 'distinct superclass as node1, \"P1963computed_star\" as label, property as node2, count(distinct l) as P1114' \n",
    "    / add-id --id-style wikidata\n",
    "    / normalize --add-id True\n",
    "    -o $OUT/derived.P1963computed.tsv\n",
    "\"\"\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Calculate the proportion of instances of a class that have each property, recording the proportion using property `proportion (P1107)`"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "kgtk(\"\"\"\n",
    "    query -i $OUT/derived.P1963computed.star.tsv.gz -i \"$GRAPH\"/derived.dwd.count.tsv.gz\n",
    "        --match '\n",
    "            computed:\n",
    "                (class)-[edge_id:P1963computed_star]->(property),\n",
    "                (edge_id)-[:P1114]->(property_count),\n",
    "            count:\n",
    "                (class)-[]->(instance_count)'\n",
    "        --return 'edge_id as node1, \"P1107\" as label, cast(property_count, float)/cast(instance_count, float) as node2'\n",
    "    / add-id --id-style wikidata\n",
    "    / cat -i - -i $OUT/derived.P1963computed.star.tsv.gz\n",
    "    / sort\n",
    "    -o $OUT/derived.P1963computed.count.star.tsv.gz\n",
    "\"\"\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2021-12-13T00:12:36.414278Z",
     "iopub.status.busy": "2021-12-13T00:12:36.414027Z",
     "iopub.status.idle": "2021-12-13T00:12:36.418624Z",
     "shell.execute_reply": "2021-12-13T00:12:36.417724Z",
     "shell.execute_reply.started": "2021-12-13T00:12:36.414251Z"
    }
   },
   "source": [
    "Show part of the files, compute the tables to put at the top of the notebook:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2021-12-23T01:08:47.963937Z",
     "iopub.status.busy": "2021-12-23T01:08:47.963423Z",
     "iopub.status.idle": "2021-12-23T01:10:13.496269Z",
     "shell.execute_reply": "2021-12-23T01:10:13.495443Z",
     "shell.execute_reply.started": "2021-12-23T01:08:47.963907Z"
    },
    "tags": []
   },
   "outputs": [],
   "source": [
    "kgtk(\"\"\"\n",
    "    query -i $OUT/derived.class.P1963computed.count.tsv.gz\n",
    "        --match '\n",
    "            (n1:Q100026)-[eid:P1963computed]->(n2), \n",
    "            (eid)-[:P1114]->(count),\n",
    "            (eid)-[:P1107]->(proportion)'\n",
    "        --return 'distinct n1 as node1, eid.label as label, n2 as node2, count as P1114, proportion as P1107'\n",
    "        --order-by 'cast(P1114, int) desc'\n",
    "        --limit 10\n",
    "    / add-labels\n",
    "    / md\n",
    "\"\"\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2021-12-23T01:10:13.498674Z",
     "iopub.status.busy": "2021-12-23T01:10:13.498407Z",
     "iopub.status.idle": "2021-12-23T01:11:43.576917Z",
     "shell.execute_reply": "2021-12-23T01:11:43.576221Z",
     "shell.execute_reply.started": "2021-12-23T01:10:13.498644Z"
    },
    "tags": []
   },
   "outputs": [],
   "source": [
    "kgtk(\"\"\"\n",
    "    query -i $OUT/derived.P1963computed.count.star.tsv.gz\n",
    "        --match '\n",
    "            (n1:Q100026)-[eid:P1963computed_star]->(n2), \n",
    "            (eid)-[:P1114]->(count),\n",
    "            (eid)-[:P1107]->(proportion)'\n",
    "        --return 'distinct n1 as node1, eid.label as label, n2 as node2, count as P1114, proportion as P1107'\n",
    "        --order-by 'cast(P1114, int) desc'\n",
    "        --limit 10\n",
    "    / add-labels\n",
    "    / md\n",
    "\"\"\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Create a report of the property counts"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2021-12-23T05:47:31.458317Z",
     "iopub.status.busy": "2021-12-23T05:47:31.458053Z",
     "iopub.status.idle": "2021-12-23T05:50:00.022048Z",
     "shell.execute_reply": "2021-12-23T05:50:00.021391Z",
     "shell.execute_reply.started": "2021-12-23T05:47:31.458292Z"
    },
    "tags": []
   },
   "outputs": [],
   "source": [
    "kgtk(\"\"\"\n",
    "    query -i $OUT/derived.P1963computed.count.star.tsv.gz\n",
    "        --match '\n",
    "            (class)-[edge_id]->(property),\n",
    "            (edge_id)-[:P1114]->(instance_count),\n",
    "            (edge_id)-[:P1107]->(proportion)'\n",
    "        --return '\n",
    "            class as class,\n",
    "            property as property,\n",
    "            instance_count as instance_count,\n",
    "            proportion as proportion'\n",
    "        --order-by 'class, cast(proportion, float) desc, property'\n",
    "    / add-labels --mode=NONE\n",
    "    -o $OUT/P1963.report.tsv.gz\n",
    "\"\"\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2021-12-23T05:44:50.635574Z",
     "iopub.status.busy": "2021-12-23T05:44:50.635121Z",
     "iopub.status.idle": "2021-12-23T05:44:55.931709Z",
     "shell.execute_reply": "2021-12-23T05:44:55.930917Z",
     "shell.execute_reply.started": "2021-12-23T05:44:50.635545Z"
    },
    "tags": []
   },
   "outputs": [],
   "source": [
    "!kgtk head -i $OUT/P1963.report.tsv.gz"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Compute `Pproperty_domain` that maps properties to the classes whose instances use the property"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2021-12-23T05:33:49.981265Z",
     "iopub.status.busy": "2021-12-23T05:33:49.980929Z",
     "iopub.status.idle": "2021-12-23T05:37:18.557176Z",
     "shell.execute_reply": "2021-12-23T05:37:18.556258Z",
     "shell.execute_reply.started": "2021-12-23T05:33:49.981230Z"
    }
   },
   "outputs": [],
   "source": [
    "kgtk(\"\"\"\n",
    "    query -i $OUT/derived.P1963computed.count.star.tsv.gz --idx mode:graph\n",
    "        --match '\n",
    "            (class)-[edge_id:P1963computed_star]->(property),\n",
    "            (edge_id)-[:P1114]->(instance_count),\n",
    "            (edge_id)-[:P1107]->(proportion)'\n",
    "        --return 'distinct property as node1, \"Pproperty_domain\" as label, class as node2, instance_count as P1114, proportion as P1107'\n",
    "        --order-by 'property, cast(P1114, int) desc'\n",
    "    / add-id --id-style wikidata\n",
    "    / normalize --add-id True\n",
    "    -o $OUT/derived.Pproperty_domain.tsv.gz\n",
    "\"\"\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Example showing the domain of `cast member (P161)`. \n",
    "Ordering by the product of the count and proportion gives a more nuanced view of the important classes."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2021-12-23T06:07:19.388325Z",
     "iopub.status.busy": "2021-12-23T06:07:19.387844Z",
     "iopub.status.idle": "2021-12-23T06:08:49.338637Z",
     "shell.execute_reply": "2021-12-23T06:08:49.337589Z",
     "shell.execute_reply.started": "2021-12-23T06:07:19.388296Z"
    },
    "tags": []
   },
   "outputs": [],
   "source": [
    "kgtk(\"\"\"\n",
    "    query -i $OUT/derived.Pproperty_domain.tsv.gz --idx mode:graph\n",
    "        --match '\n",
    "            (property:P161)-[edge_id:Pproperty_domain]->(class),\n",
    "            (edge_id)-[:P1114]->(instance_count),\n",
    "            (edge_id)-[:P1107]->(proportion)'\n",
    "        --return 'distinct property as node1, \"Pproperty_domain\" as label, class as node2, instance_count as P1114, proportion as P1107'\n",
    "        --order-by 'property, cast(P1114, float)*cast(P1107, float)  desc'\n",
    "        --limit 12\n",
    "    / add-labels\n",
    "    / md\n",
    "\"\"\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Construct a class-to-class graph where classes are connected if their instances share many properties\n",
    "\n",
    "This is an exploration to see if we can use `P1963computed` to figure out relationships among classes."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "kgtk(\"\"\"\n",
    "    query -i $OUT/derived.P1963computed.count.star.tsv.gz\n",
    "        --match '\n",
    "            (class)-[edge_id]->(property),\n",
    "            (edge_id)-[:P1107]->(proportion)'\n",
    "        --where 'cast(proportion, float) > 0.5 and property != \"P31\" and property != \"P279\" '\n",
    "        --return 'distinct class as node1, property as node2, \"property\" as label'\n",
    "    -o $TEMP/P1963computed.prevalent.tsv.gz\n",
    "\"\"\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "kgtk(\"\"\"\n",
    "    query -i $TEMP/P1963computed.prevalent.tsv.gz\n",
    "        --match '(class1)-[]->(property), (class2)-[]->(property)'\n",
    "        --where 'class1 > class2'\n",
    "        --return 'distinct class1 as node1, \"link\" as label, class2 as node2, count(distinct property) as P1114'\n",
    "    -o $TEMP/P1963.graph.tsv.gz\n",
    "\"\"\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The resulting graph as `6,338,684,335` edges, which is too large, likely caused by links that appear only once. The computation took 1 day and 11 hours."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "kgtk(\"\"\"\n",
    "    add-id -i $TEMP/P1963.graph.tsv.gz \n",
    "        --verify-id-unique=false \n",
    "    -o $TEMP/P1963.graph.id.tsv.gz\n",
    "\"\"\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "kgtk(\"\"\"\n",
    "    query -i $TEMP/P1963.graph.id.tsv.gz \n",
    "        --match '\n",
    "            (class1)-[edgeid {P1114: quantity}]->(class2)'\n",
    "        --return 'quantity as node1, \"count_distribution\" as label, count(distinct edgeid) as node2'\n",
    "        --order-by 'cast(node2, int) desc'\n",
    "    -o $TEMP/P1963.count.distribution.tsv\n",
    "\"\"\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "It is surprising that the largest counts are 6, 6 and 5. I expected 1 to be the largest. Also, the distribution is fairly smooth, which I didn't expect either."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2021-12-22T23:49:10.785980Z",
     "iopub.status.busy": "2021-12-22T23:49:10.783547Z",
     "iopub.status.idle": "2021-12-22T23:49:14.988949Z",
     "shell.execute_reply": "2021-12-22T23:49:14.988459Z",
     "shell.execute_reply.started": "2021-12-22T23:49:10.784889Z"
    },
    "tags": []
   },
   "outputs": [],
   "source": [
    "kgtk(\"head -i $TEMP/P1963.count.distribution.tsv -n 100\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Visualize the graph"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "kgtk(\"\"\"\n",
    "    query -i $TEMP/P1963.graph.tsv.gz \n",
    "        --match '\n",
    "            (class1)-[edgeid {P1114: quantity}]->(class2)'\n",
    "        --return 'distinct class1 as node1, \"link\" as label, class2 as node2, quantity as P1114'\n",
    "        --where 'cast(quantity, int) > 9'\n",
    "    -o $OUT/P1963.graph.10.tsv.gz\n",
    "\"\"\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Try loading the large graph in its own cache"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2021-11-30T04:54:58.925508Z",
     "iopub.status.busy": "2021-11-30T04:54:58.925259Z",
     "iopub.status.idle": "2021-11-30T11:19:06.398376Z",
     "shell.execute_reply": "2021-11-30T11:19:06.391854Z",
     "shell.execute_reply.started": "2021-11-30T04:54:58.925480Z"
    },
    "tags": []
   },
   "outputs": [],
   "source": [
    "kgtk(\"\"\"\n",
    "    query -i $TEMP/P1963.graph.id.tsv.gz --idx mode:none --graph-cache /Volumes/crucial/wikidata-cache/p1963.sqlite3.db\n",
    "        --match '\n",
    "            (class1)-[edgeid {P1114: quantity}]->(class2)'\n",
    "        --return 'distinct class1 as node1, \"link\" as label, class2 as node2, quantity as P1114, edgeid as id'\n",
    "        --where 'cast(quantity, int) > 9'\n",
    "    -o $OUT/P1963.graph.10.tsv.gz\n",
    "\"\"\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2021-11-30T16:07:24.871615Z",
     "iopub.status.busy": "2021-11-30T16:07:24.870622Z",
     "iopub.status.idle": "2021-11-30T16:11:31.443474Z",
     "shell.execute_reply": "2021-11-30T16:11:31.442919Z",
     "shell.execute_reply.started": "2021-11-30T16:07:24.871576Z"
    },
    "tags": []
   },
   "outputs": [],
   "source": [
    "kgtk(\"\"\"\n",
    "    connected-components -i $OUT/P1963.graph.10.tsv.gz -o $OUT/P1963.graph.10.components.tsv.gz\n",
    "\"\"\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Look at the components. We have a large component and many thousands of isolated islands."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2021-11-30T16:11:31.445494Z",
     "iopub.status.busy": "2021-11-30T16:11:31.445191Z",
     "iopub.status.idle": "2021-11-30T16:11:32.370136Z",
     "shell.execute_reply": "2021-11-30T16:11:32.369460Z",
     "shell.execute_reply.started": "2021-11-30T16:11:31.445443Z"
    },
    "tags": []
   },
   "outputs": [],
   "source": [
    "kgtk(\"\"\"\n",
    "    unique -i $OUT/P1963.graph.10.components.tsv.gz -c node2\n",
    "    / sort -c node2 --numeric True --reverse True\n",
    "\"\"\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2021-11-30T16:13:00.843955Z",
     "iopub.status.busy": "2021-11-30T16:13:00.843653Z",
     "iopub.status.idle": "2021-11-30T16:13:01.783725Z",
     "shell.execute_reply": "2021-11-30T16:13:01.782967Z",
     "shell.execute_reply.started": "2021-11-30T16:13:00.843927Z"
    },
    "tags": []
   },
   "outputs": [],
   "source": [
    "kgtk(\"\"\"\n",
    "    query -i $OUT/P1963.graph.10.components.tsv.gz\n",
    "        --match '\n",
    "            (item)-[]->(:`CLUSB2cQvRcVJs48uwsDf88O9A==`)'\n",
    "        --return 'distinct item as node1'\n",
    "    -o $TEMP/large-connected-component.tsv.gz\n",
    "\"\"\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2021-11-30T16:13:18.896453Z",
     "iopub.status.busy": "2021-11-30T16:13:18.895905Z",
     "iopub.status.idle": "2021-11-30T16:26:40.114165Z",
     "shell.execute_reply": "2021-11-30T16:26:40.113286Z",
     "shell.execute_reply.started": "2021-11-30T16:13:18.896423Z"
    },
    "tags": []
   },
   "outputs": [],
   "source": [
    "kgtk(\"\"\"\n",
    "    query -i $TEMP/large-connected-component.tsv.gz -i $OUT/P1963.graph.10.tsv.gz\n",
    "        --match '\n",
    "            P1963: (n1)-[l]->(n2),\n",
    "            connected: (n1),\n",
    "            connected: (n2)'\n",
    "        --return 'distinct n1 as node1, l.label as label, n2 as node2, l as id'\n",
    "    -o $TEMP/large-cluster.graph.tsv.gz\n",
    "\"\"\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2021-11-30T16:26:40.116269Z",
     "iopub.status.busy": "2021-11-30T16:26:40.116020Z",
     "iopub.status.idle": "2021-11-30T16:30:29.352458Z",
     "shell.execute_reply": "2021-11-30T16:30:29.351657Z",
     "shell.execute_reply.started": "2021-11-30T16:26:40.116242Z"
    },
    "tags": []
   },
   "outputs": [],
   "source": [
    "!kgtk export-gt -i $TEMP/large-cluster.graph.tsv.gz  -o $TEMP/large-cluster.graph.gt"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2021-11-30T16:30:29.353801Z",
     "iopub.status.busy": "2021-11-30T16:30:29.353441Z",
     "iopub.status.idle": "2021-11-30T17:27:16.134803Z",
     "shell.execute_reply": "2021-11-30T17:27:16.134256Z",
     "shell.execute_reply.started": "2021-11-30T16:30:29.353777Z"
    },
    "tags": []
   },
   "outputs": [],
   "source": [
    "path = os.environ[\"TEMP\"] + \"/large-cluster.graph.gt\"\n",
    "g = load_graph(path)\n",
    "\n",
    "pos = sfdp_layout(g)\n",
    "graph_draw(g, pos=pos, output_size=(1000, 1000))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "!kgtk export-gt -i $OUT/P1963.graph.10.tsv.gz  -o $OUT/P1963.graph.10.gt"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "path = os.environ[\"OUT\"] + \"/P1963.graph.10.gt\"\n",
    "g = load_graph(path)\n",
    "\n",
    "pos = sfdp_layout(g)\n",
    "graph_draw(g, pos=pos, output_size=(1000, 1000))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "path = os.environ[\"OUT\"] + \"/P1963.graph.15.gt\"\n",
    "g = load_graph(path)\n",
    "\n",
    "pos = sfdp_layout(g)\n",
    "graph_draw(g, pos=pos, output_size=(1000, 1000))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.8.0"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}