Paradise Papers.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Paradise Papers\n",
    "\n",
    "In 2016, confidential information mostly about offshore investments related to the legal firm Appleby were leaked to the Süddeutsche Zeitung, a german newspaper. After sharing the 13.4 million documents with the International Consortium of Investigative Journalists (ICIJ), the first results were published on the 5th of November 2017. It was the second big leak after the Panama Papers two years before, showing the offshore activities of some global companies as well as politicians such as prime ministers."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "heading_collapsed": true
   },
   "source": [
    "# The Paradise Papers Database\n",
    "\n",
    "The Süddeutsche Zeitung and ICIJ will not publish all leaked documents to protect their source. However, they published a Neo4j database containing relations between companies, shareholders and legal firms mentioned in the Paradise Papers. "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "hidden": true
   },
   "source": [
    "The database consists of nodes with specific labels and relations connecting them. Each node or relation can store further properties. There are five different types / labels of nodes:\n",
    "* Officer: A shareholder, either person or company\n",
    "* Entity: An offshore company\n",
    "* Intermediary: A person or company which helps registering offshore companies\n",
    "* Address: An address with relation to one of the nodes\n",
    "* Other: Nodes that do not fit in one of these categories, e.g. used for grouping of nodes\n",
    "\n",
    "A relationship always connects two of these nodes and carries a label describing its function (e.g. REGISTERED_AT describing the relation between a company and the address of its headquarter)."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-05-21T08:10:50.804266Z",
     "start_time": "2019-05-21T08:10:50.780086Z"
    },
    "hidden": true
   },
   "source": [
    "You can either [download and execute it locally](https://offshoreleaks.icij.org/pages/database) (might take some time) or use a [sandbox provided by Neo4j](https://neo4j.com/sandbox-v2/) (sandbox expires after 3 days, discarding all changes, but a new instance can be started afterwards).\n",
    "\n",
    "To execute the commands in this notebook, you will also need the [py2neo package](https://py2neo.org/v4/index.html) which you can install via pip and which serves as a driver for the Neo4j database. If you wish to render graphs, you will need the vis.py file and the vis.js library in addition, based on the [neo4j-jupyter notebooks by Nicole White](https://github.com/nicolewhite/neo4j-jupyter). Simple table outputs can be done via py2neo and do not require additional libraries.\n",
    "\n",
    "You will need the following information to establish a connection to the database:\n",
    "* ip-address and bolt port of the computer the database is running on\n",
    "* username and password for authentication\n",
    "\n",
    "If you use a sandbox, the necessary information is displayed in the details tab after startup."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# The Case of Nike\n",
    "\n",
    "One of the global players mentioned in the Paradise Papers is Nike, the well known sneakers and sports brand. Although the journalists could not find any illegal activities within the leaked data, they still show a morally disputable system of tax avoidance.\n",
    "\n",
    "Using a system of companies located in the Netherlands and the Bermudas, Nike could reduce its effective tax rate from the normal 35% in 2005 to approximately 13.2% in 2017. The details are described in an [article by the Süddeutsche Zeitung](https://projekte.sueddeutsche.de/paradisepapers/politik/nike-and-its-system-of-tax-avoidance-e727797/) (available in German and English). A description of the shape of the graph can be found [here](https://offshoreleaks-data.icij.org/offshoreleaks/neo4j/guide/index.html).\n",
    "\n",
    "## Follow the Money\n",
    "\n",
    "In this notebook, we will investigate Nike's internal company structure and follow the money from a pair of sneakers bought in Germany up to the headquarter of Nike in the United States."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Establish a Connection to the Database"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-05-31T13:27:40.740127Z",
     "start_time": "2019-05-31T13:27:39.874394Z"
    }
   },
   "outputs": [],
   "source": [
    "from py2neo import Graph\n",
    "\n",
    "# schema of `profile`: \"bolt://ip:port\", depends on installation type\n",
    "# schema of `auth`: (\"username\", \"password\"), might be required depending on installation\n",
    "graph = Graph(profile=\"bolt://127.0.0.1:7687\") # for vagrant VM\n",
    "#graph = Graph(profile=\"bolt://neo4j:7687\") # for docker\n",
    "\n",
    "# import a function used to draw graphs in later sections\n",
    "from vis.vis import drawSubgraph\n",
    "\n",
    "# specify which property to display as label in a node\n",
    "options = {\n",
    "    \"Officer\": \"name\",\n",
    "    \"Entity\": \"name\",\n",
    "    \"Intermediary\": \"name\",\n",
    "    \"Address\": \"address\"\n",
    "}\n",
    "\n",
    "physics = True"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Find Nike Retail & Nike Innovate\n",
    "\n",
    "The article of Süddeutsche Zeitung mentions that most shoes bought in Europe are not sold by local companies (one for each country), but by central companies located in the Netherlands. The article mentions two names which we will use as starting points: Nike Retail BV and Nike Innovate CV.\n",
    "\n",
    "First check if any company with this name exists in the database. Use toLower to transform the name to lower case and perform a case insensitive search."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-05-31T13:27:41.717538Z",
     "start_time": "2019-05-31T13:27:40.744897Z"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "No record found\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table></table>"
      ],
      "text/plain": []
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "query = \"\"\"\n",
    "MATCH (n)\n",
    "WHERE toLower(n.name) CONTAINS 'nike retail' \n",
    "    OR toLower(n.name) CONTAINS 'nike innovate'\n",
    "RETURN n.name\n",
    "\"\"\"\n",
    "\n",
    "data = graph.run(query).to_table()\n",
    "if len(data) == 0:\n",
    "    print(\"No record found\")\n",
    "data"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Find Companies Registered at Colosseum 1\n",
    "\n",
    "Via the [Global Legal Entity Identifier Foundation](https://www.gleif.org/en/) we can lookup the address of the headquarter of any legal entity. Both Nike companies are registered at Colosseum 1 in Hilversum, Netherlands.\n",
    "\n",
    "Find all nodes related to this address via a path p and display it as graph."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-05-31T13:27:41.897517Z",
     "start_time": "2019-05-31T13:27:41.721937Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "\n",
       "        <iframe\n",
       "            width=\"100%\"\n",
       "            height=\"350\"\n",
       "            src=\"pics/vis_generated/nike_colosseum1_direct_relation.html\"\n",
       "            frameborder=\"0\"\n",
       "            allowfullscreen\n",
       "        ></iframe>\n",
       "        "
      ],
      "text/plain": [
       "<IPython.lib.display.IFrame at 0x1067476a0>"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "query = \"\"\"\n",
    "MATCH p = (a:Address) -- (n)\n",
    "WHERE a.address = 'Colosseum 1'\n",
    "RETURN p\n",
    "\"\"\"\n",
    "\n",
    "data = graph.run(query).to_subgraph()\n",
    "drawSubgraph(data, options, height=\"350\", filename=\"nike_colosseum1_direct_relation.html\", physics=physics)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Search for Nodes Related to Colosseum 1\n",
    "\n",
    "Search for all nodes which are related to the address Colosseum 1 via a path p with a maximum length of three."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-05-31T13:27:42.474643Z",
     "start_time": "2019-05-31T13:27:41.906046Z"
    },
    "scrolled": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "\n",
       "        <iframe\n",
       "            width=\"100%\"\n",
       "            height=\"800\"\n",
       "            src=\"pics/vis_generated/nike_colosseum1_path_length_3.html\"\n",
       "            frameborder=\"0\"\n",
       "            allowfullscreen\n",
       "        ></iframe>\n",
       "        "
      ],
      "text/plain": [
       "<IPython.lib.display.IFrame at 0x1067fcda0>"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "query = \"\"\"\n",
    "MATCH p = (a:Address) - [*0..3] - (n)\n",
    "WHERE a.address = 'Colosseum 1'\n",
    "RETURN p\n",
    "\"\"\"\n",
    "\n",
    "data = graph.run(query).to_subgraph()\n",
    "drawSubgraph(data, options, height=\"800\", filename=\"nike_colosseum1_path_length_3.html\", physics=physics)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Find All Nodes with Nike in Name\n",
    "\n",
    "Find all nodes whose names contain the term Nike."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-05-31T13:27:42.944175Z",
     "start_time": "2019-05-31T13:27:42.494387Z"
    },
    "code_folding": []
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table><tr><th>n.name</th><th>labels</th></tr><tr><td style=\"text-align:left\">VS&amp;B Niketan, Y-50, new #8, 9th Street, Annanagar; 600040 Chennai; India</td><td style=\"text-align:left\">[&#039;Address&#039;]</td></tr><tr><td style=\"text-align:left\">NIKE Jump Ltd.</td><td style=\"text-align:left\">[&#039;Entity&#039;]</td></tr><tr><td style=\"text-align:left\">NIKE INTERNATIONAL LTD.</td><td style=\"text-align:left\">[&#039;Entity&#039;]</td></tr><tr><td style=\"text-align:left\">Nike Finance Ltd.</td><td style=\"text-align:left\">[&#039;Entity&#039;]</td></tr><tr><td style=\"text-align:left\">NIKE Force</td><td style=\"text-align:left\">[&#039;Entity&#039;]</td></tr><tr><td style=\"text-align:left\">NIKE Pegasus</td><td style=\"text-align:left\">[&#039;Entity&#039;]</td></tr><tr><td style=\"text-align:left\">NIKE Flight</td><td style=\"text-align:left\">[&#039;Entity&#039;]</td></tr><tr><td style=\"text-align:left\">NIKE Lavadome</td><td style=\"text-align:left\">[&#039;Entity&#039;]</td></tr><tr><td style=\"text-align:left\">NIKE Waffle</td><td style=\"text-align:left\">[&#039;Entity&#039;]</td></tr><tr><td style=\"text-align:left\">NIKE Tailwind</td><td style=\"text-align:left\">[&#039;Entity&#039;]</td></tr><tr><td style=\"text-align:left\">NIKE Cortez</td><td style=\"text-align:left\">[&#039;Entity&#039;]</td></tr><tr><td style=\"text-align:left\">NIKE Huarache</td><td style=\"text-align:left\">[&#039;Entity&#039;]</td></tr><tr><td style=\"text-align:left\">Nike Group</td><td style=\"text-align:left\">[&#039;Intermediary&#039;]</td></tr><tr><td style=\"text-align:left\">Gores - Fiona Adenike</td><td style=\"text-align:left\">[&#039;Officer&#039;]</td></tr><tr><td style=\"text-align:left\">Henniker Smith - Ian</td><td style=\"text-align:left\">[&#039;Officer&#039;]</td></tr><tr><td style=\"text-align:left\">NIKE INTERNATIONAL LTD.</td><td style=\"text-align:left\">[&#039;Officer&#039;]</td></tr><tr><td style=\"text-align:left\">Nike Finance Ltd.</td><td style=\"text-align:left\">[&#039;Officer&#039;]</td></tr><tr><td style=\"text-align:left\">NIKE Lavadome</td><td style=\"text-align:left\">[&#039;Officer&#039;]</td></tr><tr><td style=\"text-align:left\">NIKE Tailwind</td><td style=\"text-align:left\">[&#039;Officer&#039;]</td></tr><tr><td style=\"text-align:left\">NIKE Cortez</td><td style=\"text-align:left\">[&#039;Officer&#039;]</td></tr><tr><td style=\"text-align:left\">NIKE Huarache</td><td style=\"text-align:left\">[&#039;Officer&#039;]</td></tr><tr><td style=\"text-align:left\">NIKE Pegasus</td><td style=\"text-align:left\">[&#039;Officer&#039;]</td></tr><tr><td style=\"text-align:left\">NIKE Flight</td><td style=\"text-align:left\">[&#039;Officer&#039;]</td></tr><tr><td style=\"text-align:left\">NIKE Jump Ltd.</td><td style=\"text-align:left\">[&#039;Officer&#039;]</td></tr><tr><td style=\"text-align:left\">NIKE Waffle</td><td style=\"text-align:left\">[&#039;Officer&#039;]</td></tr><tr><td style=\"text-align:left\">NIKE Air LLC</td><td style=\"text-align:left\">[&#039;Officer&#039;]</td></tr><tr><td style=\"text-align:left\">NIKE Elevate C.V.</td><td style=\"text-align:left\">[&#039;Officer&#039;]</td></tr><tr><td style=\"text-align:left\">NIKE Fundamentals C.V.</td><td style=\"text-align:left\">[&#039;Officer&#039;]</td></tr><tr><td style=\"text-align:left\">NIKE Glide C.V.</td><td style=\"text-align:left\">[&#039;Officer&#039;]</td></tr><tr><td style=\"text-align:left\">NIKE Holding, LLC</td><td style=\"text-align:left\">[&#039;Officer&#039;]</td></tr><tr><td style=\"text-align:left\">NIKE, Inc.</td><td style=\"text-align:left\">[&#039;Officer&#039;]</td></tr><tr><td style=\"text-align:left\">NIKE International Holding, Inc.</td><td style=\"text-align:left\">[&#039;Officer&#039;]</td></tr><tr><td style=\"text-align:left\">NIKE Max LLC</td><td style=\"text-align:left\">[&#039;Officer&#039;]</td></tr><tr><td style=\"text-align:left\">NIKE Sphere C.V.</td><td style=\"text-align:left\">[&#039;Officer&#039;]</td></tr><tr><td style=\"text-align:left\">NIKE Zoom LLC</td><td style=\"text-align:left\">[&#039;Officer&#039;]</td></tr><tr><td style=\"text-align:left\">Marino - Nikeline</td><td style=\"text-align:left\">[&#039;Officer&#039;]</td></tr></table>"
      ],
      "text/plain": [
       " n.name                                                                   | labels           \n",
       "--------------------------------------------------------------------------|------------------\n",
       " VS&B Niketan, Y-50, new #8, 9th Street, Annanagar; 600040 Chennai; India | ['Address']      \n",
       " NIKE Jump Ltd.                                                           | ['Entity']       \n",
       " NIKE INTERNATIONAL LTD.                                                  | ['Entity']       \n",
       " Nike Finance Ltd.                                                        | ['Entity']       \n",
       " NIKE Force                                                               | ['Entity']       \n",
       " NIKE Pegasus                                                             | ['Entity']       \n",
       " NIKE Flight                                                              | ['Entity']       \n",
       " NIKE Lavadome                                                            | ['Entity']       \n",
       " NIKE Waffle                                                              | ['Entity']       \n",
       " NIKE Tailwind                                                            | ['Entity']       \n",
       " NIKE Cortez                                                              | ['Entity']       \n",
       " NIKE Huarache                                                            | ['Entity']       \n",
       " Nike Group                                                               | ['Intermediary'] \n",
       " Gores - Fiona Adenike                                                    | ['Officer']      \n",
       " Henniker Smith - Ian                                                     | ['Officer']      \n",
       " NIKE INTERNATIONAL LTD.                                                  | ['Officer']      \n",
       " Nike Finance Ltd.                                                        | ['Officer']      \n",
       " NIKE Lavadome                                                            | ['Officer']      \n",
       " NIKE Tailwind                                                            | ['Officer']      \n",
       " NIKE Cortez                                                              | ['Officer']      \n",
       " NIKE Huarache                                                            | ['Officer']      \n",
       " NIKE Pegasus                                                             | ['Officer']      \n",
       " NIKE Flight                                                              | ['Officer']      \n",
       " NIKE Jump Ltd.                                                           | ['Officer']      \n",
       " NIKE Waffle                                                              | ['Officer']      \n",
       " NIKE Air LLC                                                             | ['Officer']      \n",
       " NIKE Elevate C.V.                                                        | ['Officer']      \n",
       " NIKE Fundamentals C.V.                                                   | ['Officer']      \n",
       " NIKE Glide C.V.                                                          | ['Officer']      \n",
       " NIKE Holding, LLC                                                        | ['Officer']      \n",
       " NIKE, Inc.                                                               | ['Officer']      \n",
       " NIKE International Holding, Inc.                                         | ['Officer']      \n",
       " NIKE Max LLC                                                             | ['Officer']      \n",
       " NIKE Sphere C.V.                                                         | ['Officer']      \n",
       " NIKE Zoom LLC                                                            | ['Officer']      \n",
       " Marino - Nikeline                                                        | ['Officer']      "
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "query = \"\"\"\n",
    "MATCH (n)\n",
    "WHERE toLower(n.name) CONTAINS 'nike'\n",
    "RETURN n.name, LABELS(n) as labels\n",
    "\"\"\"\n",
    "\n",
    "data = graph.run(query).to_table()\n",
    "if len(data) == 0:\n",
    "    print(\"No record found\")\n",
    "data"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Nike Entities and their Jurisdiction\n",
    "\n",
    "Count the number of entities with Nike in their name and group by jurisdiction."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-05-31T13:27:43.129643Z",
     "start_time": "2019-05-31T13:27:42.967258Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table><tr><th>jurisdiction</th><th>numEntities</th></tr><tr><td style=\"text-align:left\">Bermuda</td><td style=\"text-align:right\">11</td></tr></table>"
      ],
      "text/plain": [
       " jurisdiction | numEntities \n",
       "--------------|-------------\n",
       " Bermuda      |          11 "
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "query = \"\"\"\n",
    "MATCH (e:Entity)\n",
    "WHERE toLower(e.name) CONTAINS 'nike'\n",
    "RETURN e.jurisdiction_description AS jurisdiction, COUNT(*) AS numEntities\n",
    "\"\"\"\n",
    "\n",
    "data = graph.run(query).to_table()\n",
    "if len(data) == 0:\n",
    "    print(\"No record found\")\n",
    "data"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Find the Shortest Path Between Colosseum 1 and Nike Inc.\n",
    "\n",
    "Find the shortest path p connecting the address Colosseum 1 with Nike's main company named 'NIKE, Inc.'."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-05-31T13:27:43.295941Z",
     "start_time": "2019-05-31T13:27:43.143080Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "\n",
       "        <iframe\n",
       "            width=\"100%\"\n",
       "            height=\"400\"\n",
       "            src=\"pics/vis_generated/nike_shortest_path_colosseum1_nike.html\"\n",
       "            frameborder=\"0\"\n",
       "            allowfullscreen\n",
       "        ></iframe>\n",
       "        "
      ],
      "text/plain": [
       "<IPython.lib.display.IFrame at 0x106818940>"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "query = \"\"\"\n",
    "MATCH p = SHORTESTPATH((a:Address) - [*] - (o:Officer))\n",
    "WHERE a.address = 'Colosseum 1' \n",
    "    AND o.name = 'NIKE, Inc.'\n",
    "RETURN p\n",
    "\"\"\"\n",
    "\n",
    "data = graph.run(query).to_subgraph()\n",
    "drawSubgraph(data, options, height=\"400\", filename=\"nike_shortest_path_colosseum1_nike.html\", physics=physics)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### More Paths Between Colosseum 1 and Nike Inc.\n",
    "\n",
    "Show all paths p between the address Colosseum 1 and Nike Inc. with a maximum length of five and without any nodes representing adresses besides the first node Colosseum 1."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-05-31T13:27:44.375690Z",
     "start_time": "2019-05-31T13:27:43.309712Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "\n",
       "        <iframe\n",
       "            width=\"100%\"\n",
       "            height=\"800\"\n",
       "            src=\"pics/vis_generated/nike_paths_between colosseum1_nike.html\"\n",
       "            frameborder=\"0\"\n",
       "            allowfullscreen\n",
       "        ></iframe>\n",
       "        "
      ],
      "text/plain": [
       "<IPython.lib.display.IFrame at 0x106836240>"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "query = \"\"\"\n",
    "MATCH p = (a:Address) - [*0..5] - (o:Officer)\n",
    "WHERE a.address = 'Colosseum 1' \n",
    "    AND o.name = 'NIKE, Inc.' \n",
    "    AND NOT ANY(n IN NODES(p)[1..] WHERE n:Address)\n",
    "RETURN p\n",
    "\"\"\"\n",
    "\n",
    "data = graph.run(query).to_subgraph()\n",
    "drawSubgraph(data, options, height=\"800\", filename=\"nike_paths_between colosseum1_nike.html\", physics=physics)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Only Paths with Officer Names Containing Nike\n",
    "\n",
    "Restrict the previous example further to only contain paths where nodes of type Officer must have a name starting with Nike (essentially eliminating persons from the previous result) and the path contains the node Nike International Ltd."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-05-31T13:27:45.134750Z",
     "start_time": "2019-05-31T13:27:44.384103Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "\n",
       "        <iframe\n",
       "            width=\"100%\"\n",
       "            height=\"800\"\n",
       "            src=\"pics/vis_generated/nike_paths_between_colosseum1_nike_officers_nike.html\"\n",
       "            frameborder=\"0\"\n",
       "            allowfullscreen\n",
       "        ></iframe>\n",
       "        "
      ],
      "text/plain": [
       "<IPython.lib.display.IFrame at 0x106866588>"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "query = \"\"\"\n",
    "MATCH p = (a:Address) - [*0..5] - (o:Officer)\n",
    "WHERE a.address = 'Colosseum 1' \n",
    "    AND o.name = 'NIKE, Inc.' \n",
    "    AND NOT ANY(n IN NODES(p)[1..] WHERE n:Address)\n",
    "    AND NOT ANY(n IN NODES(p) WHERE (n:Officer AND NOT toLower(n.name) STARTS WITH 'nike'))\n",
    "    AND ANY(n IN NODES(p) WHERE n.name = 'NIKE INTERNATIONAL LTD.')\n",
    "RETURN p\n",
    "\"\"\"\n",
    "\n",
    "data = graph.run(query).to_subgraph()\n",
    "drawSubgraph(data, options, height=\"800\", filename=\"nike_paths_between_colosseum1_nike_officers_nike.html\", physics=physics)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Connect Nike Officers and Entities with the Same Name\n",
    "\n",
    "Connect all officers and entities with the same name starting with the term Nike with a 'SAME_NAME_AS' relationship. Use MERGE instead of CREATE to avoid duplicate relationships. Return the names of all such pairs found."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-05-31T13:27:45.769150Z",
     "start_time": "2019-05-31T13:27:45.138895Z"
    },
    "scrolled": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table><tr><th>o.name</th><th>e.name</th></tr><tr><td style=\"text-align:left\">NIKE Jump Ltd.</td><td style=\"text-align:left\">NIKE Jump Ltd.</td></tr><tr><td style=\"text-align:left\">NIKE INTERNATIONAL LTD.</td><td style=\"text-align:left\">NIKE INTERNATIONAL LTD.</td></tr><tr><td style=\"text-align:left\">Nike Finance Ltd.</td><td style=\"text-align:left\">Nike Finance Ltd.</td></tr><tr><td style=\"text-align:left\">NIKE Pegasus</td><td style=\"text-align:left\">NIKE Pegasus</td></tr><tr><td style=\"text-align:left\">NIKE Flight</td><td style=\"text-align:left\">NIKE Flight</td></tr><tr><td style=\"text-align:left\">NIKE Lavadome</td><td style=\"text-align:left\">NIKE Lavadome</td></tr><tr><td style=\"text-align:left\">NIKE Waffle</td><td style=\"text-align:left\">NIKE Waffle</td></tr><tr><td style=\"text-align:left\">NIKE Tailwind</td><td style=\"text-align:left\">NIKE Tailwind</td></tr><tr><td style=\"text-align:left\">NIKE Cortez</td><td style=\"text-align:left\">NIKE Cortez</td></tr><tr><td style=\"text-align:left\">NIKE Huarache</td><td style=\"text-align:left\">NIKE Huarache</td></tr></table>"
      ],
      "text/plain": [
       " o.name                  | e.name                  \n",
       "-------------------------|-------------------------\n",
       " NIKE Jump Ltd.          | NIKE Jump Ltd.          \n",
       " NIKE INTERNATIONAL LTD. | NIKE INTERNATIONAL LTD. \n",
       " Nike Finance Ltd.       | Nike Finance Ltd.       \n",
       " NIKE Pegasus            | NIKE Pegasus            \n",
       " NIKE Flight             | NIKE Flight             \n",
       " NIKE Lavadome           | NIKE Lavadome           \n",
       " NIKE Waffle             | NIKE Waffle             \n",
       " NIKE Tailwind           | NIKE Tailwind           \n",
       " NIKE Cortez             | NIKE Cortez             \n",
       " NIKE Huarache           | NIKE Huarache           "
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "query = \"\"\"\n",
    "MATCH (o:Officer), (e:Entity)\n",
    "WHERE toLower(o.name) STARTS WITH 'nike'\n",
    "    AND o.name = e.name\n",
    "MERGE (o) - [r:SAME_NAME_AS] -> (e)\n",
    "RETURN o.name, e.name\n",
    "\"\"\"\n",
    "\n",
    "data = graph.run(query).to_table()\n",
    "if len(data) == 0:\n",
    "    print(\"No record found\")\n",
    "data"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Find Nike's Management Board\n",
    "\n",
    "Find all persons which are directly related to a Nike company and the address of the headquarter at One Bowerman Drive. Order the results by names."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-05-31T13:27:45.911919Z",
     "start_time": "2019-05-31T13:27:45.772468Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table><tr><th>o.name</th></tr><tr><td style=\"text-align:left\">Blair - Donald Warren</td></tr><tr><td style=\"text-align:left\">Carter - James Cecil</td></tr><tr><td style=\"text-align:left\">Coburn III - John F</td></tr><tr><td style=\"text-align:left\">Denson - Charles</td></tr><tr><td style=\"text-align:left\">Hall - Kelly K</td></tr><tr><td style=\"text-align:left\">Knight - Philip H.</td></tr><tr><td style=\"text-align:left\">Krane - Hilary K</td></tr><tr><td style=\"text-align:left\">Miller - Ann M.</td></tr><tr><td style=\"text-align:left\">Peddie - Anthony</td></tr><tr><td style=\"text-align:left\">Pliska - Bernard F.</td></tr><tr><td style=\"text-align:left\">Stewart - Lindsay D.</td></tr><tr><td style=\"text-align:left\">Stilwell - Marcia</td></tr><tr><td style=\"text-align:left\">Woodruff - Robert</td></tr><tr><td style=\"text-align:left\">van Paasschen - Frits</td></tr></table>"
      ],
      "text/plain": [
       " o.name                \n",
       "-----------------------\n",
       " Blair - Donald Warren \n",
       " Carter - James Cecil  \n",
       " Coburn III - John F   \n",
       " Denson - Charles      \n",
       " Hall - Kelly K        \n",
       " Knight - Philip H.    \n",
       " Krane - Hilary K      \n",
       " Miller - Ann M.       \n",
       " Peddie - Anthony      \n",
       " Pliska - Bernard F.   \n",
       " Stewart - Lindsay D.  \n",
       " Stilwell - Marcia     \n",
       " Woodruff - Robert     \n",
       " van Paasschen - Frits "
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "query = \"\"\"\n",
    "MATCH (n) -- (o:Officer) -- (a:Address)\n",
    "WHERE toLower(n.name) STARTS WITH 'nike'\n",
    "    AND a.address = 'One Bowerman Drive'\n",
    "    AND NOT toLower(o.name) STARTS WITH 'nike'\n",
    "RETURN DISTINCT o.name\n",
    "ORDER BY o.name\n",
    "\"\"\"\n",
    "\n",
    "data = graph.run(query).to_table()\n",
    "if len(data) == 0:\n",
    "    print(\"No record found\")\n",
    "data"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "A quick web search reveals that most of these people had a high position at Nike, for example:\n",
    "* Philip H. Knight: founder and former CEO\n",
    "* Marcia Stilwell: former Vice President and Corporate Treasurer\n",
    "* Lindsay D. Stewart: former Vice President and Chief of Staff\n",
    "* Kelly K. Hall: former Vice President and CFO"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Explore the Complete Nike Universe\n",
    "\n",
    "Detect all nodes which are directly related to a node with Nike in the beginning of its name.\n",
    "In a first query, count the number of distinct relationships per type and in a second query show the resulting graph."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-05-31T13:27:46.274794Z",
     "start_time": "2019-05-31T13:27:45.917764Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table><tr><th>type</th><th>num</th></tr><tr><td style=\"text-align:left\">OFFICER_OF</td><td style=\"text-align:right\">113</td></tr><tr><td style=\"text-align:left\">REGISTERED_ADDRESS</td><td style=\"text-align:right\">37</td></tr><tr><td style=\"text-align:left\">CONNECTED_TO</td><td style=\"text-align:right\">12</td></tr><tr><td style=\"text-align:left\">SAME_NAME_AS</td><td style=\"text-align:right\">10</td></tr></table>"
      ],
      "text/plain": [
       " type               | num \n",
       "--------------------|-----\n",
       " OFFICER_OF         | 113 \n",
       " REGISTERED_ADDRESS |  37 \n",
       " CONNECTED_TO       |  12 \n",
       " SAME_NAME_AS       |  10 "
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "query1 = \"\"\"\n",
    "MATCH (n1) - [r] - (n2)\n",
    "WHERE toLower(n1.name) STARTS WITH 'nike' \n",
    "RETURN TYPE(r) AS type, COUNT(DISTINCT r) AS num\n",
    "ORDER BY num DESC\n",
    "\"\"\"\n",
    "\n",
    "data = graph.run(query1).to_table()\n",
    "if len(data) == 0:\n",
    "    print(\"No record found\")\n",
    "data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-05-31T13:27:47.490834Z",
     "start_time": "2019-05-31T13:27:46.300696Z"
    },
    "scrolled": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "\n",
       "        <iframe\n",
       "            width=\"100%\"\n",
       "            height=\"800\"\n",
       "            src=\"pics/vis_generated/nike_universe_directly_related.html\"\n",
       "            frameborder=\"0\"\n",
       "            allowfullscreen\n",
       "        ></iframe>\n",
       "        "
      ],
      "text/plain": [
       "<IPython.lib.display.IFrame at 0x106807e48>"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "query2 = \"\"\"\n",
    "MATCH p = (n1) -- (n2)\n",
    "WHERE toLower(n1.name) STARTS WITH 'nike'\n",
    "RETURN p\n",
    "\"\"\"\n",
    "\n",
    "data = graph.run(query2).to_subgraph()\n",
    "drawSubgraph(data, options, height=\"800\", filename=\"nike_universe_directly_related.html\", physics=physics)"
   ]
  }
 ],
 "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.7.3"
  },
  "varInspector": {
   "cols": {
    "lenName": 16,
    "lenType": 16,
    "lenVar": 40
   },
   "kernels_config": {
    "python": {
     "delete_cmd_postfix": "",
     "delete_cmd_prefix": "del ",
     "library": "var_list.py",
     "varRefreshCmd": "print(var_dic_list())"
    },
    "r": {
     "delete_cmd_postfix": ") ",
     "delete_cmd_prefix": "rm(",
     "library": "var_list.r",
     "varRefreshCmd": "cat(var_dic_list()) "
    }
   },
   "types_to_exclude": [
    "module",
    "function",
    "builtin_function_or_method",
    "instance",
    "_Feature"
   ],
   "window_display": false
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}