07.Inserting-PSC-Super-Secure-Persons.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Loading the \"super-secure\" PSC control relationships\n",
    "\n",
    "Some companies make a statement that the PSC needs to be super-secure. Here we build those control relationships and match the appropriate companies to them."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/home/ec2-user/anaconda3/lib/python3.6/site-packages/odo/backends/pandas.py:94: FutureWarning: pandas.tslib is deprecated and will be removed in a future version.\n",
      "You can access NaTType as type(pandas.NaT)\n",
      "  @convert.register((pd.Timestamp, pd.Timedelta), (pd.tslib.NaTType, type(None)))\n"
     ]
    }
   ],
   "source": [
    "import pandas as pd\n",
    "import json\n",
    "from pandas.io.json import json_normalize;\n",
    "\n",
    "import blaze as bz"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "First load the data and do some tidying up so we don't waste RAM"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "original_psc_data = pd.read_json('../data/psc_snapshot-2017-09-08.json')\n",
    "all_records_psc = pd.concat([original_psc_data['company_number'],json_normalize(original_psc_data['data'])],axis=1)\n",
    "del original_psc_data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "individual-person-with-significant-control          4225140\n",
       "persons-with-significant-control-statement           404603\n",
       "corporate-entity-person-with-significant-control     344866\n",
       "legal-person-person-with-significant-control           5490\n",
       "super-secure-person-with-significant-control            186\n",
       "exemptions                                               37\n",
       "totals#persons-of-significant-control-snapshot            1\n",
       "Name: kind, dtype: int64"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "all_records_psc.kind.value_counts()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "So there are 404,603 statements of a PSC not being found for a company. Below we see the breakdown in the types of statement they can make"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>company_number</th>\n",
       "      <th>description</th>\n",
       "      <th>etag</th>\n",
       "      <th>kind</th>\n",
       "      <th>links.self</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>14869</th>\n",
       "      <td>10264175</td>\n",
       "      <td>super-secure-persons-with-significant-control</td>\n",
       "      <td>af4df16232440267ba71f671ea0ffa525133a89b</td>\n",
       "      <td>super-secure-person-with-significant-control</td>\n",
       "      <td>/company/08208688/persons-with-significant-con...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>58172</th>\n",
       "      <td>05875447</td>\n",
       "      <td>super-secure-persons-with-significant-control</td>\n",
       "      <td>2416cda91df75d6743f434d135cd957a64f0314e</td>\n",
       "      <td>super-secure-person-with-significant-control</td>\n",
       "      <td>/company/08594248/persons-with-significant-con...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>79679</th>\n",
       "      <td>04474577</td>\n",
       "      <td>super-secure-persons-with-significant-control</td>\n",
       "      <td>9f07537f806c2b7dbd35dccf641294f99642860a</td>\n",
       "      <td>super-secure-person-with-significant-control</td>\n",
       "      <td>/company/03601075/persons-with-significant-con...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>100217</th>\n",
       "      <td>10284681</td>\n",
       "      <td>super-secure-persons-with-significant-control</td>\n",
       "      <td>f9ccb32f7142e1f650013e810f9234200269caac</td>\n",
       "      <td>super-secure-person-with-significant-control</td>\n",
       "      <td>/company/09781806/persons-with-significant-con...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>241490</th>\n",
       "      <td>05889494</td>\n",
       "      <td>super-secure-persons-with-significant-control</td>\n",
       "      <td>2fa2cb97768235ddb365b0f9ff66e5aa6f330c1b</td>\n",
       "      <td>super-secure-person-with-significant-control</td>\n",
       "      <td>/company/05953764/persons-with-significant-con...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       company_number                                    description  \\\n",
       "14869        10264175  super-secure-persons-with-significant-control   \n",
       "58172        05875447  super-secure-persons-with-significant-control   \n",
       "79679        04474577  super-secure-persons-with-significant-control   \n",
       "100217       10284681  super-secure-persons-with-significant-control   \n",
       "241490       05889494  super-secure-persons-with-significant-control   \n",
       "\n",
       "                                            etag  \\\n",
       "14869   af4df16232440267ba71f671ea0ffa525133a89b   \n",
       "58172   2416cda91df75d6743f434d135cd957a64f0314e   \n",
       "79679   9f07537f806c2b7dbd35dccf641294f99642860a   \n",
       "100217  f9ccb32f7142e1f650013e810f9234200269caac   \n",
       "241490  2fa2cb97768235ddb365b0f9ff66e5aa6f330c1b   \n",
       "\n",
       "                                                kind  \\\n",
       "14869   super-secure-person-with-significant-control   \n",
       "58172   super-secure-person-with-significant-control   \n",
       "79679   super-secure-person-with-significant-control   \n",
       "100217  super-secure-person-with-significant-control   \n",
       "241490  super-secure-person-with-significant-control   \n",
       "\n",
       "                                               links.self  \n",
       "14869   /company/08208688/persons-with-significant-con...  \n",
       "58172   /company/08594248/persons-with-significant-con...  \n",
       "79679   /company/03601075/persons-with-significant-con...  \n",
       "100217  /company/09781806/persons-with-significant-con...  \n",
       "241490  /company/05953764/persons-with-significant-con...  "
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "all_records_psc[all_records_psc.kind == 'super-secure-person-with-significant-control'].dropna(axis=1).head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Inserting the Super-Secure Controlling Entity into Neo4j\n",
    "\n",
    "Adding a super-secure ControllingEnity node"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "from neo4j.v1 import GraphDatabase\n",
    "driver = GraphDatabase.driver(\"bolt://10.0.0.1:7687\", auth=(\"myusername\", \"mypassword\"))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "kind = 'super-secure-person-with-significant-control'\n",
    "with driver.session() as session:\n",
    "    session.run(\"CREATE (ce:ControllingEntity {type: {kind}})\", kind=kind)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Now to connect companies to statements\n",
    "\n",
    "1. First let's define a function that takes a company record and creates the relationship back to the super-secure ControllingEntity. \n",
    "2. Then we need to loop over all companies that have declared super-secure PSC and insert those into the neo4j database."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "def write_super_secure_control(input_data):\n",
    "    \"\"\"Function writes super_secure records to Neo4j database\"\"\"\n",
    "    with driver.session() as session:\n",
    "        session.run((\"UNWIND {list} AS d \"\n",
    "                     \"MERGE (c:Company {uid: d.company_id}) \"\n",
    "                     \"MERGE (ce:ControllingEntity {type: d.kind}) \"\n",
    "                     \"MERGE (c)-[:CONTROLS]->(ce);\"), \n",
    "                    {\"list\": input_data})"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We don't have too many super-sercure people so we can do this in a single insert"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/home/ec2-user/anaconda3/lib/python3.6/site-packages/ipykernel_launcher.py:2: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: http://pandas.pydata.org/pandas-docs/stable/indexing.html#indexing-view-versus-copy\n",
      "  \n"
     ]
    }
   ],
   "source": [
    "super_secure = all_records_psc[all_records_psc.kind == 'super-secure-person-with-significant-control']\n",
    "super_secure['company_id'] = super_secure['links.self'].map(lambda x: x.split('/')[2])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [],
   "source": [
    "input_data = [v for k,v in super_secure[['company_id', 'kind']].T.to_dict().items()]\n",
    "write_super_secure_control(input_data)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Considering the \"exemptions\"\n",
    "\n",
    "Exemptions behave in a very similar way so we will handle those here too but with a \"IS_EXEMPT\" relationship"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "kind = 'exemptions'\n",
    "with driver.session() as session:\n",
    "    session.run(\"CREATE (ce:ControllingEntity {type: {kind}})\", kind=kind)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "def write_exemption_control(input_data):\n",
    "    \"\"\"Function writes super_secure records to Neo4j database\"\"\"\n",
    "    with driver.session() as session:\n",
    "        session.run((\"UNWIND {list} AS d \"\n",
    "                     \"MERGE (c:Company {uid: d.company_id}) \"\n",
    "                     \"MERGE (ce:ControllingEntity {type: d.kind}) \"\n",
    "                     \"MERGE (c)-[:IS_EXEMPT]->(ce);\"), \n",
    "                    {\"list\": input_data})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/home/ec2-user/anaconda3/lib/python3.6/site-packages/ipykernel_launcher.py:2: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: http://pandas.pydata.org/pandas-docs/stable/indexing.html#indexing-view-versus-copy\n",
      "  \n"
     ]
    }
   ],
   "source": [
    "exempt = all_records_psc[all_records_psc.kind == 'exemptions']\n",
    "exempt['company_id'] = exempt['links.self'].map(lambda x: x.split('/')[2])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "input_data = [v for k,v in exempt[['company_id', 'kind']].T.to_dict().items()]\n",
    "write_exemption_control(input_data)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "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.6.1"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}