05.Inserting-Corporate-PSCs.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Inserting Corporate PSCs\n",
    "\n",
    "A significant number of controlling entities are the corporations and companies that control other companies. Here we load the data and prepare it for insertion into the graph."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import json\n",
    "from pandas.io.json import json_normalize\n",
    "import numpy as np\n",
    "from neo4j.v1 import GraphDatabase"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "collapsed": true
   },
   "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": 3,
   "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>address.address_line_1</th>\n",
       "      <th>address.address_line_2</th>\n",
       "      <th>address.care_of</th>\n",
       "      <th>address.country</th>\n",
       "      <th>address.locality</th>\n",
       "      <th>address.po_box</th>\n",
       "      <th>address.postal_code</th>\n",
       "      <th>address.premises</th>\n",
       "      <th>address.region</th>\n",
       "      <th>...</th>\n",
       "      <th>name_elements.middle_name</th>\n",
       "      <th>name_elements.surname</th>\n",
       "      <th>name_elements.title</th>\n",
       "      <th>nationality</th>\n",
       "      <th>natures_of_control</th>\n",
       "      <th>notified_on</th>\n",
       "      <th>persons_of_significant_control_count</th>\n",
       "      <th>restrictions_notice_withdrawal_reason</th>\n",
       "      <th>statement</th>\n",
       "      <th>statements_count</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>09145694</td>\n",
       "      <td>St. Andrews Road</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>England</td>\n",
       "      <td>Henley-On-Thames</td>\n",
       "      <td>NaN</td>\n",
       "      <td>RG9 1HP</td>\n",
       "      <td>2</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>Thanh</td>\n",
       "      <td>Wildman</td>\n",
       "      <td>Mrs</td>\n",
       "      <td>Vietnamese</td>\n",
       "      <td>[ownership-of-shares-50-to-75-percent]</td>\n",
       "      <td>2016-04-06</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>08581893</td>\n",
       "      <td>High Street</td>\n",
       "      <td>Wendover</td>\n",
       "      <td>NaN</td>\n",
       "      <td>England</td>\n",
       "      <td>Aylesbury</td>\n",
       "      <td>NaN</td>\n",
       "      <td>HP22 6EA</td>\n",
       "      <td>14a</td>\n",
       "      <td>Buckinghamshire</td>\n",
       "      <td>...</td>\n",
       "      <td>Robert Charles</td>\n",
       "      <td>Davies</td>\n",
       "      <td>Mr</td>\n",
       "      <td>British</td>\n",
       "      <td>[ownership-of-shares-25-to-50-percent, ownersh...</td>\n",
       "      <td>2016-06-30</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>08581893</td>\n",
       "      <td>Holywells Road</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>United Kingdom</td>\n",
       "      <td>Ipswich</td>\n",
       "      <td>NaN</td>\n",
       "      <td>IP3 0DL</td>\n",
       "      <td>37-41</td>\n",
       "      <td>Suffolk</td>\n",
       "      <td>...</td>\n",
       "      <td>Fiona</td>\n",
       "      <td>Tarrant</td>\n",
       "      <td>NaN</td>\n",
       "      <td>British</td>\n",
       "      <td>[ownership-of-shares-25-to-50-percent, voting-...</td>\n",
       "      <td>2016-04-06</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>01605766</td>\n",
       "      <td>20-22 Wenlock Road</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>England</td>\n",
       "      <td>London</td>\n",
       "      <td>NaN</td>\n",
       "      <td>N1 7GU</td>\n",
       "      <td>Suite Lp33221</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>[ownership-of-shares-75-to-100-percent]</td>\n",
       "      <td>2016-04-06</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>10259080</td>\n",
       "      <td>38 Church Road</td>\n",
       "      <td>Worcester Park</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Surrey</td>\n",
       "      <td>NaN</td>\n",
       "      <td>KT4 7RD</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>Peter</td>\n",
       "      <td>Ollett</td>\n",
       "      <td>Mr</td>\n",
       "      <td>British</td>\n",
       "      <td>[ownership-of-shares-50-to-75-percent]</td>\n",
       "      <td>2016-06-30</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 46 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "  company_number address.address_line_1 address.address_line_2  \\\n",
       "0       09145694       St. Andrews Road                    NaN   \n",
       "1       08581893            High Street               Wendover   \n",
       "2       08581893         Holywells Road                    NaN   \n",
       "3       01605766     20-22 Wenlock Road                    NaN   \n",
       "4       10259080         38 Church Road         Worcester Park   \n",
       "\n",
       "  address.care_of address.country  address.locality address.po_box  \\\n",
       "0             NaN         England  Henley-On-Thames            NaN   \n",
       "1             NaN         England         Aylesbury            NaN   \n",
       "2             NaN  United Kingdom           Ipswich            NaN   \n",
       "3             NaN         England            London            NaN   \n",
       "4             NaN             NaN            Surrey            NaN   \n",
       "\n",
       "  address.postal_code address.premises   address.region       ...         \\\n",
       "0             RG9 1HP                2              NaN       ...          \n",
       "1            HP22 6EA              14a  Buckinghamshire       ...          \n",
       "2             IP3 0DL            37-41          Suffolk       ...          \n",
       "3              N1 7GU    Suite Lp33221              NaN       ...          \n",
       "4             KT4 7RD              NaN              NaN       ...          \n",
       "\n",
       "   name_elements.middle_name name_elements.surname name_elements.title  \\\n",
       "0                      Thanh               Wildman                 Mrs   \n",
       "1             Robert Charles                Davies                  Mr   \n",
       "2                      Fiona               Tarrant                 NaN   \n",
       "3                        NaN                   NaN                 NaN   \n",
       "4                      Peter                Ollett                  Mr   \n",
       "\n",
       "   nationality                                 natures_of_control notified_on  \\\n",
       "0   Vietnamese             [ownership-of-shares-50-to-75-percent]  2016-04-06   \n",
       "1      British  [ownership-of-shares-25-to-50-percent, ownersh...  2016-06-30   \n",
       "2      British  [ownership-of-shares-25-to-50-percent, voting-...  2016-04-06   \n",
       "3          NaN            [ownership-of-shares-75-to-100-percent]  2016-04-06   \n",
       "4      British             [ownership-of-shares-50-to-75-percent]  2016-06-30   \n",
       "\n",
       "  persons_of_significant_control_count restrictions_notice_withdrawal_reason  \\\n",
       "0                                  NaN                                   NaN   \n",
       "1                                  NaN                                   NaN   \n",
       "2                                  NaN                                   NaN   \n",
       "3                                  NaN                                   NaN   \n",
       "4                                  NaN                                   NaN   \n",
       "\n",
       "  statement statements_count  \n",
       "0       NaN              NaN  \n",
       "1       NaN              NaN  \n",
       "2       NaN              NaN  \n",
       "3       NaN              NaN  \n",
       "4       NaN              NaN  \n",
       "\n",
       "[5 rows x 46 columns]"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "all_records_psc.head()"
   ]
  },
  {
   "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": [
    "## Filter the data to only handle corporate control"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "corporate_psc = all_records_psc[all_records_psc.kind == \"corporate-entity-person-with-significant-control\"]\n",
    "del all_records_psc"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "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>address.address_line_1</th>\n",
       "      <th>address.address_line_2</th>\n",
       "      <th>address.care_of</th>\n",
       "      <th>address.country</th>\n",
       "      <th>address.locality</th>\n",
       "      <th>address.po_box</th>\n",
       "      <th>address.postal_code</th>\n",
       "      <th>address.premises</th>\n",
       "      <th>address.region</th>\n",
       "      <th>...</th>\n",
       "      <th>name_elements.middle_name</th>\n",
       "      <th>name_elements.surname</th>\n",
       "      <th>name_elements.title</th>\n",
       "      <th>nationality</th>\n",
       "      <th>natures_of_control</th>\n",
       "      <th>notified_on</th>\n",
       "      <th>persons_of_significant_control_count</th>\n",
       "      <th>restrictions_notice_withdrawal_reason</th>\n",
       "      <th>statement</th>\n",
       "      <th>statements_count</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>01605766</td>\n",
       "      <td>20-22 Wenlock Road</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>England</td>\n",
       "      <td>London</td>\n",
       "      <td>NaN</td>\n",
       "      <td>N1 7GU</td>\n",
       "      <td>Suite Lp33221</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>[ownership-of-shares-75-to-100-percent]</td>\n",
       "      <td>2016-04-06</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>10259079</td>\n",
       "      <td>Hilgrove Street</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Jersey</td>\n",
       "      <td>St. Helier</td>\n",
       "      <td>NaN</td>\n",
       "      <td>JE1 1ES</td>\n",
       "      <td>Queensway House</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>[ownership-of-shares-25-to-50-percent, voting-...</td>\n",
       "      <td>2016-04-06</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>01943460</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>United Kingdom</td>\n",
       "      <td>Eastleigh</td>\n",
       "      <td>NaN</td>\n",
       "      <td>SO50 6YU</td>\n",
       "      <td>Chickenhall Lane</td>\n",
       "      <td>Hampshire</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>[ownership-of-shares-25-to-50-percent]</td>\n",
       "      <td>2016-04-06</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>05495577</td>\n",
       "      <td>12-18 Queens Road</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>England</td>\n",
       "      <td>Weybridge</td>\n",
       "      <td>NaN</td>\n",
       "      <td>KT13 9XB</td>\n",
       "      <td>Clive House</td>\n",
       "      <td>Surrey</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>[ownership-of-shares-50-to-75-percent]</td>\n",
       "      <td>2016-04-06</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28</th>\n",
       "      <td>NI065219</td>\n",
       "      <td>Parkway</td>\n",
       "      <td>Porters Wood</td>\n",
       "      <td>NaN</td>\n",
       "      <td>England</td>\n",
       "      <td>St. Albans</td>\n",
       "      <td>NaN</td>\n",
       "      <td>AL3 6PA</td>\n",
       "      <td>5b</td>\n",
       "      <td>Hertfordshire</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>[ownership-of-shares-75-to-100-percent]</td>\n",
       "      <td>2016-04-06</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 46 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "   company_number address.address_line_1 address.address_line_2  \\\n",
       "3        01605766     20-22 Wenlock Road                    NaN   \n",
       "5        10259079        Hilgrove Street                    NaN   \n",
       "11       01943460                    NaN                    NaN   \n",
       "13       05495577      12-18 Queens Road                    NaN   \n",
       "28       NI065219                Parkway           Porters Wood   \n",
       "\n",
       "   address.care_of address.country address.locality address.po_box  \\\n",
       "3              NaN         England           London            NaN   \n",
       "5              NaN          Jersey       St. Helier            NaN   \n",
       "11             NaN  United Kingdom        Eastleigh            NaN   \n",
       "13             NaN         England        Weybridge            NaN   \n",
       "28             NaN         England       St. Albans            NaN   \n",
       "\n",
       "   address.postal_code  address.premises address.region       ...         \\\n",
       "3               N1 7GU     Suite Lp33221            NaN       ...          \n",
       "5              JE1 1ES   Queensway House            NaN       ...          \n",
       "11            SO50 6YU  Chickenhall Lane      Hampshire       ...          \n",
       "13            KT13 9XB       Clive House         Surrey       ...          \n",
       "28             AL3 6PA                5b  Hertfordshire       ...          \n",
       "\n",
       "    name_elements.middle_name name_elements.surname name_elements.title  \\\n",
       "3                         NaN                   NaN                 NaN   \n",
       "5                         NaN                   NaN                 NaN   \n",
       "11                        NaN                   NaN                 NaN   \n",
       "13                        NaN                   NaN                 NaN   \n",
       "28                        NaN                   NaN                 NaN   \n",
       "\n",
       "    nationality                                 natures_of_control  \\\n",
       "3           NaN            [ownership-of-shares-75-to-100-percent]   \n",
       "5           NaN  [ownership-of-shares-25-to-50-percent, voting-...   \n",
       "11          NaN             [ownership-of-shares-25-to-50-percent]   \n",
       "13          NaN             [ownership-of-shares-50-to-75-percent]   \n",
       "28          NaN            [ownership-of-shares-75-to-100-percent]   \n",
       "\n",
       "   notified_on persons_of_significant_control_count  \\\n",
       "3   2016-04-06                                  NaN   \n",
       "5   2016-04-06                                  NaN   \n",
       "11  2016-04-06                                  NaN   \n",
       "13  2016-04-06                                  NaN   \n",
       "28  2016-04-06                                  NaN   \n",
       "\n",
       "   restrictions_notice_withdrawal_reason statement statements_count  \n",
       "3                                    NaN       NaN              NaN  \n",
       "5                                    NaN       NaN              NaN  \n",
       "11                                   NaN       NaN              NaN  \n",
       "13                                   NaN       NaN              NaN  \n",
       "28                                   NaN       NaN              NaN  \n",
       "\n",
       "[5 rows x 46 columns]"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "corporate_psc.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "def convert_control_list(control_list):\n",
    "    \"\"\"Function to convert a list of controls into a dictionary with the controls as a key and a value of 1\"\"\"\n",
    "    if isinstance(control_list, list):\n",
    "        return {key: 1 for key in control_list}\n",
    "    else:\n",
    "        return {}"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "corporate_psc['DICTIONARY_OF_CONTROLS'] = corporate_psc['natures_of_control'].map(convert_control_list)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## It appears that the company_number does NOT correspond to the company_id for corporate PSCs\n",
    "** INSTEAD we must extract it from the links.self path\n",
    "e.g. links.self /company/07804290/persons-with-significant-control/corporate-entity/jc9QvYXKVQMFGOTWsXH-XVt25eU **"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "company_number 05868878\n",
      "address.address_line_1 Navigo House\n",
      "address.address_line_2 nan\n",
      "address.care_of nan\n",
      "address.country England\n",
      "address.locality Grimsby\n",
      "address.po_box nan\n",
      "address.postal_code DN32 0QE\n",
      "address.premises 3-7 Brighowgate\n",
      "address.region nan\n",
      "ceased nan\n",
      "ceased_on nan\n",
      "country_of_residence nan\n",
      "date_of_birth.month nan\n",
      "date_of_birth.year nan\n",
      "description nan\n",
      "etag 4886a53b47aebc6b9c8344009124d41f1523ac0b\n",
      "exemptions.psc_exempt_as_shares_admitted_on_market.exemption_type nan\n",
      "exemptions.psc_exempt_as_shares_admitted_on_market.items nan\n",
      "exemptions.psc_exempt_as_trading_on_regulated_market.exemption_type nan\n",
      "exemptions.psc_exempt_as_trading_on_regulated_market.items nan\n",
      "exemptions_count nan\n",
      "generated_at nan\n",
      "identification.country_registered nan\n",
      "identification.legal_authority Companies Act 2006\n",
      "identification.legal_form Community Interest Company\n",
      "identification.place_registered nan\n",
      "identification.registration_number nan\n",
      "kind corporate-entity-person-with-significant-control\n",
      "linked_psc_name nan\n",
      "links.person_with_significant_control nan\n",
      "links.self /company/07804290/persons-with-significant-control/corporate-entity/jc9QvYXKVQMFGOTWsXH-XVt25eU\n",
      "links.statement nan\n",
      "name Navigo Health And Social Care Cic\n",
      "name_elements.forename nan\n",
      "name_elements.honours nan\n",
      "name_elements.middle_name nan\n",
      "name_elements.surname nan\n",
      "name_elements.title nan\n",
      "nationality nan\n",
      "natures_of_control ['right-to-appoint-and-remove-directors']\n",
      "notified_on 2016-04-06\n",
      "persons_of_significant_control_count nan\n",
      "restrictions_notice_withdrawal_reason nan\n",
      "statement nan\n",
      "statements_count nan\n",
      "DICTIONARY_OF_CONTROLS {'right-to-appoint-and-remove-directors': 1}\n"
     ]
    }
   ],
   "source": [
    "for k, v in corporate_psc[corporate_psc['identification.registration_number'].isnull()].iloc[1].items():\n",
    "    print(k, v)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "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>address.address_line_1</th>\n",
       "      <th>address.address_line_2</th>\n",
       "      <th>address.care_of</th>\n",
       "      <th>address.country</th>\n",
       "      <th>address.locality</th>\n",
       "      <th>address.po_box</th>\n",
       "      <th>address.postal_code</th>\n",
       "      <th>address.premises</th>\n",
       "      <th>address.region</th>\n",
       "      <th>...</th>\n",
       "      <th>name_elements.surname</th>\n",
       "      <th>name_elements.title</th>\n",
       "      <th>nationality</th>\n",
       "      <th>natures_of_control</th>\n",
       "      <th>notified_on</th>\n",
       "      <th>persons_of_significant_control_count</th>\n",
       "      <th>restrictions_notice_withdrawal_reason</th>\n",
       "      <th>statement</th>\n",
       "      <th>statements_count</th>\n",
       "      <th>DICTIONARY_OF_CONTROLS</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>0 rows × 47 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "Empty DataFrame\n",
       "Columns: [company_number, address.address_line_1, address.address_line_2, address.care_of, address.country, address.locality, address.po_box, address.postal_code, address.premises, address.region, ceased, ceased_on, country_of_residence, date_of_birth.month, date_of_birth.year, description, etag, exemptions.psc_exempt_as_shares_admitted_on_market.exemption_type, exemptions.psc_exempt_as_shares_admitted_on_market.items, exemptions.psc_exempt_as_trading_on_regulated_market.exemption_type, exemptions.psc_exempt_as_trading_on_regulated_market.items, exemptions_count, generated_at, identification.country_registered, identification.legal_authority, identification.legal_form, identification.place_registered, identification.registration_number, kind, linked_psc_name, links.person_with_significant_control, links.self, links.statement, name, name_elements.forename, name_elements.honours, name_elements.middle_name, name_elements.surname, name_elements.title, nationality, natures_of_control, notified_on, persons_of_significant_control_count, restrictions_notice_withdrawal_reason, statement, statements_count, DICTIONARY_OF_CONTROLS]\n",
       "Index: []\n",
       "\n",
       "[0 rows x 47 columns]"
      ]
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "corporate_psc[corporate_psc.company_number == '07804290']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "country_code_map = pd.read_pickle('./data/clean_country_code_map.pkl')\n",
    "combined_map = pd.read_pickle('./data/combined_country_map.pkl')\n",
    "nationality_map = pd.read_pickle('./data/nation_map.pkl')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Defining specific functions to handle human PSCs\n",
    "\n",
    "These functions will convert the raw data for human PSCs into a format that can then be inderted into Neo4j in an easy fashion."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "def corporate_psc_name(record):\n",
    "    \"Function to create name string identifier for corporation\"\n",
    "    data = record['name']\n",
    "    try:\n",
    "        return data.upper()\n",
    "    except TypeError as e:\n",
    "        return 'NONAME__BLANK'\n",
    "    except AttributeError:\n",
    "        return 'NONAME__BLANK'\n",
    "    \n",
    "def corporate_psc_uid(record):\n",
    "    \"Function to create a unique ID for someone from their name and dob\"\n",
    "    uid = str(record['identification.registration_number'])\n",
    "    if uid == 'nan':\n",
    "        uid = '-'.join(corporate_psc_name(record).split()) \n",
    "        \n",
    "    country = str(record['identification.country_registered'])\n",
    "    if country == 'nan':\n",
    "        country = record['address.country']\n",
    "        if country == 'nan':\n",
    "            country = 'UNKNOWN'\n",
    "    if isinstance(country, tuple):\n",
    "        country = 'UNKNOWN'\n",
    "        \n",
    "    return {'uid': uid,\n",
    "            'registered_country': country,\n",
    "            'legal_authority': str(record['identification.legal_authority']).replace('nan', 'MISSING'),\n",
    "            'place_registered': str(record['identification.place_registered']).replace('nan', 'MISSING')}\n",
    "\n",
    "\n",
    "def corporate_psc_address(record):\n",
    "    \"Function to create a address details for person\"\n",
    "    try:\n",
    "        new_address = {\n",
    "            'premises': str(record['address.premises']),\n",
    "            'address_Line1': str(record['address.address_line_1']),\n",
    "            'address_Line2': str(record['address.address_line_2']),\n",
    "            'address_PostTown': str(record['address.locality']).upper(),\n",
    "            'address_POBox': str(record['address.po_box']),\n",
    "            'address_County': str(record['address.region']).upper(),\n",
    "            'address_PostCode': str(record['address.postal_code']).upper(),\n",
    "            'address_Country': str(record['address.country']).upper(),\n",
    "            'address_CareOf': str(record['address.care_of'])\n",
    "        }\n",
    "        return {k: v.replace('nan', '').replace('NAN', '') for k,v in new_address.items()}\n",
    "    except TypeError as e:\n",
    "        return {'address_Country': \"UNKNOWN\"}\n",
    "    except AttributeError:\n",
    "        return {'address_Country': \"UNKNOWN\"}\n",
    "    \n",
    "    \n",
    "def corporate_psc_details(record):\n",
    "    \"Function to create a relationship details for person\"\n",
    "    details = {}\n",
    "    details['company_id'] = record['links.self'].split('/')[2]\n",
    "    details['control_kind'] = record['kind']\n",
    "    return details\n",
    "    \n",
    "    \n",
    "def new_corporate_record(record):\n",
    "    \"Function to create a new record that is flat\"\n",
    "    uid = corporate_psc_uid(record)\n",
    "    address = corporate_psc_address(record)\n",
    "    return {**uid, **address, \n",
    "            **corporate_psc_details(record), \n",
    "            'name': corporate_psc_name(record), \n",
    "            'natures_of_control': record['DICTIONARY_OF_CONTROLS']}"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Time to test some of these functions"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "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>address_CareOf</th>\n",
       "      <th>address_Country</th>\n",
       "      <th>address_County</th>\n",
       "      <th>address_Line1</th>\n",
       "      <th>address_Line2</th>\n",
       "      <th>address_POBox</th>\n",
       "      <th>address_PostCode</th>\n",
       "      <th>address_PostTown</th>\n",
       "      <th>company_id</th>\n",
       "      <th>control_kind</th>\n",
       "      <th>legal_authority</th>\n",
       "      <th>name</th>\n",
       "      <th>natures_of_control</th>\n",
       "      <th>place_registered</th>\n",
       "      <th>premises</th>\n",
       "      <th>registered_country</th>\n",
       "      <th>uid</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td></td>\n",
       "      <td>ENGLAND</td>\n",
       "      <td></td>\n",
       "      <td>20-22 Wenlock Road</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>N1 7GU</td>\n",
       "      <td>LONDON</td>\n",
       "      <td>07296272</td>\n",
       "      <td>corporate-entity-person-with-significant-control</td>\n",
       "      <td>Companies Act 2006</td>\n",
       "      <td>ANDERSON MANN GROUP LTD</td>\n",
       "      <td>{'ownership-of-shares-75-to-100-percent': 1}</td>\n",
       "      <td>Companies House Uk</td>\n",
       "      <td>Suite Lp33221</td>\n",
       "      <td>England</td>\n",
       "      <td>07500933</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td></td>\n",
       "      <td>JERSEY</td>\n",
       "      <td></td>\n",
       "      <td>Hilgrove Street</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>JE1 1ES</td>\n",
       "      <td>ST. HELIER</td>\n",
       "      <td>09804615</td>\n",
       "      <td>corporate-entity-person-with-significant-control</td>\n",
       "      <td>Companies (Jersey) Law 1991</td>\n",
       "      <td>GLENCORE PLC</td>\n",
       "      <td>{'ownership-of-shares-25-to-50-percent': 1, 'v...</td>\n",
       "      <td>Register Of Jersey Companies</td>\n",
       "      <td>Queensway House</td>\n",
       "      <td>Jersey</td>\n",
       "      <td>107710</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td></td>\n",
       "      <td>UNITED KINGDOM</td>\n",
       "      <td>HAMPSHIRE</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>SO50 6YU</td>\n",
       "      <td>EASTLEIGH</td>\n",
       "      <td>01493788</td>\n",
       "      <td>corporate-entity-person-with-significant-control</td>\n",
       "      <td>United Kingdom (England)</td>\n",
       "      <td>PRYSMIAN CABLES &amp; SYSTEMS LIMITED</td>\n",
       "      <td>{'ownership-of-shares-25-to-50-percent': 1}</td>\n",
       "      <td>Companies House</td>\n",
       "      <td>Chickenhall Lane</td>\n",
       "      <td>England</td>\n",
       "      <td>958507</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td></td>\n",
       "      <td>ENGLAND</td>\n",
       "      <td>SURREY</td>\n",
       "      <td>12-18 Queens Road</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>KT13 9XB</td>\n",
       "      <td>WEYBRIDGE</td>\n",
       "      <td>01493788</td>\n",
       "      <td>corporate-entity-person-with-significant-control</td>\n",
       "      <td>United Kingdom (England)</td>\n",
       "      <td>TT ELECTRONICS PLC</td>\n",
       "      <td>{'ownership-of-shares-50-to-75-percent': 1}</td>\n",
       "      <td>Companies House</td>\n",
       "      <td>Clive House</td>\n",
       "      <td>England</td>\n",
       "      <td>87249</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28</th>\n",
       "      <td></td>\n",
       "      <td>ENGLAND</td>\n",
       "      <td>HERTFORDSHIRE</td>\n",
       "      <td>Parkway</td>\n",
       "      <td>Porters Wood</td>\n",
       "      <td></td>\n",
       "      <td>AL3 6PA</td>\n",
       "      <td>ST. ALBANS</td>\n",
       "      <td>01605823</td>\n",
       "      <td>corporate-entity-person-with-significant-control</td>\n",
       "      <td>Companies Act</td>\n",
       "      <td>WOODLAND SOFTWARE SOLUTIONS LIMITED</td>\n",
       "      <td>{'ownership-of-shares-75-to-100-percent': 1}</td>\n",
       "      <td>England &amp; Wales</td>\n",
       "      <td>5b</td>\n",
       "      <td>England</td>\n",
       "      <td>03647273</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   address_CareOf address_Country address_County       address_Line1  \\\n",
       "3                         ENGLAND                 20-22 Wenlock Road   \n",
       "5                          JERSEY                    Hilgrove Street   \n",
       "11                 UNITED KINGDOM      HAMPSHIRE                       \n",
       "13                        ENGLAND         SURREY   12-18 Queens Road   \n",
       "28                        ENGLAND  HERTFORDSHIRE             Parkway   \n",
       "\n",
       "   address_Line2 address_POBox address_PostCode address_PostTown company_id  \\\n",
       "3                                        N1 7GU           LONDON   07296272   \n",
       "5                                       JE1 1ES       ST. HELIER   09804615   \n",
       "11                                     SO50 6YU        EASTLEIGH   01493788   \n",
       "13                                     KT13 9XB        WEYBRIDGE   01493788   \n",
       "28  Porters Wood                        AL3 6PA       ST. ALBANS   01605823   \n",
       "\n",
       "                                        control_kind  \\\n",
       "3   corporate-entity-person-with-significant-control   \n",
       "5   corporate-entity-person-with-significant-control   \n",
       "11  corporate-entity-person-with-significant-control   \n",
       "13  corporate-entity-person-with-significant-control   \n",
       "28  corporate-entity-person-with-significant-control   \n",
       "\n",
       "                legal_authority                                 name  \\\n",
       "3            Companies Act 2006              ANDERSON MANN GROUP LTD   \n",
       "5   Companies (Jersey) Law 1991                         GLENCORE PLC   \n",
       "11     United Kingdom (England)    PRYSMIAN CABLES & SYSTEMS LIMITED   \n",
       "13     United Kingdom (England)                   TT ELECTRONICS PLC   \n",
       "28                Companies Act  WOODLAND SOFTWARE SOLUTIONS LIMITED   \n",
       "\n",
       "                                   natures_of_control  \\\n",
       "3        {'ownership-of-shares-75-to-100-percent': 1}   \n",
       "5   {'ownership-of-shares-25-to-50-percent': 1, 'v...   \n",
       "11        {'ownership-of-shares-25-to-50-percent': 1}   \n",
       "13        {'ownership-of-shares-50-to-75-percent': 1}   \n",
       "28       {'ownership-of-shares-75-to-100-percent': 1}   \n",
       "\n",
       "                place_registered          premises registered_country  \\\n",
       "3             Companies House Uk     Suite Lp33221            England   \n",
       "5   Register Of Jersey Companies   Queensway House             Jersey   \n",
       "11               Companies House  Chickenhall Lane            England   \n",
       "13               Companies House       Clive House            England   \n",
       "28               England & Wales                5b            England   \n",
       "\n",
       "         uid  \n",
       "3   07500933  \n",
       "5     107710  \n",
       "11    958507  \n",
       "13     87249  \n",
       "28  03647273  "
      ]
     },
     "execution_count": 35,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "corporate_psc.iloc[0:5].apply(lambda s: pd.Series(new_corporate_record(s)), axis=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "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>address_CareOf</th>\n",
       "      <th>address_Country</th>\n",
       "      <th>address_County</th>\n",
       "      <th>address_Line1</th>\n",
       "      <th>address_Line2</th>\n",
       "      <th>address_POBox</th>\n",
       "      <th>address_PostCode</th>\n",
       "      <th>address_PostTown</th>\n",
       "      <th>company_id</th>\n",
       "      <th>control_kind</th>\n",
       "      <th>legal_authority</th>\n",
       "      <th>name</th>\n",
       "      <th>natures_of_control</th>\n",
       "      <th>place_registered</th>\n",
       "      <th>premises</th>\n",
       "      <th>registered_country</th>\n",
       "      <th>uid</th>\n",
       "      <th>Registered_in</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>37</th>\n",
       "      <td></td>\n",
       "      <td>ENGLAND</td>\n",
       "      <td></td>\n",
       "      <td>The Airport</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>CB5 8RY</td>\n",
       "      <td>CAMBRIDGE</td>\n",
       "      <td>00287379</td>\n",
       "      <td>corporate-entity-person-with-significant-control</td>\n",
       "      <td>England</td>\n",
       "      <td>S.G. SMITH AUTOMOTIVE LIMITED</td>\n",
       "      <td>{'ownership-of-shares-75-to-100-percent': 1, '...</td>\n",
       "      <td>Registrar Of Companies (England &amp; Wales)</td>\n",
       "      <td>Airport House</td>\n",
       "      <td>England</td>\n",
       "      <td>00622112</td>\n",
       "      <td>GB</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>47</th>\n",
       "      <td></td>\n",
       "      <td>ENGLAND</td>\n",
       "      <td></td>\n",
       "      <td>Olympus Avenue</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>CV34 6BF</td>\n",
       "      <td>LEAMINGTON SPA</td>\n",
       "      <td>07790030</td>\n",
       "      <td>corporate-entity-person-with-significant-control</td>\n",
       "      <td>Companies Act 2006</td>\n",
       "      <td>1846 NOMINEES LIMITED</td>\n",
       "      <td>{'ownership-of-shares-75-to-100-percent': 1, '...</td>\n",
       "      <td>Companies House</td>\n",
       "      <td>Olympus House</td>\n",
       "      <td>England And Wales</td>\n",
       "      <td>05953366</td>\n",
       "      <td>GB</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>88</th>\n",
       "      <td></td>\n",
       "      <td>ENGLAND</td>\n",
       "      <td></td>\n",
       "      <td>Gracechurch Street</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>EC3V 0BT</td>\n",
       "      <td>LONDON</td>\n",
       "      <td>05955659</td>\n",
       "      <td>corporate-entity-person-with-significant-control</td>\n",
       "      <td>Companies Act 2006</td>\n",
       "      <td>UPP INVESTMENTS LIMITED</td>\n",
       "      <td>{'ownership-of-shares-75-to-100-percent': 1, '...</td>\n",
       "      <td>Registrar Of Companies England And Wales</td>\n",
       "      <td>40</td>\n",
       "      <td>England</td>\n",
       "      <td>5957759</td>\n",
       "      <td>GB</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>100</th>\n",
       "      <td></td>\n",
       "      <td>ENGLAND</td>\n",
       "      <td></td>\n",
       "      <td>Victoria Embankment</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>EC4Y 0DZ</td>\n",
       "      <td>LONDON</td>\n",
       "      <td>04240399</td>\n",
       "      <td>corporate-entity-person-with-significant-control</td>\n",
       "      <td>United Kingdom Law</td>\n",
       "      <td>OCTOPUS PUBLISHING GROUP LIMITED</td>\n",
       "      <td>{'ownership-of-shares-75-to-100-percent': 1}</td>\n",
       "      <td>Companies House</td>\n",
       "      <td>50</td>\n",
       "      <td>England And Wales</td>\n",
       "      <td>3597451</td>\n",
       "      <td>GB</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>121</th>\n",
       "      <td></td>\n",
       "      <td>UNITED KINGDOM</td>\n",
       "      <td>HAMPSHIRE</td>\n",
       "      <td>Hussar Court</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>PO7 7SQ</td>\n",
       "      <td>WATERLOOVILLE</td>\n",
       "      <td>08228404</td>\n",
       "      <td>corporate-entity-person-with-significant-control</td>\n",
       "      <td>Companies House Act 2006</td>\n",
       "      <td>R &amp; S YOUNG MANAGEMENT SERVICES LIMITED</td>\n",
       "      <td>{'ownership-of-shares-75-to-100-percent': 1, '...</td>\n",
       "      <td>The Register Of Companies</td>\n",
       "      <td>24 Picton House</td>\n",
       "      <td>England And Wales</td>\n",
       "      <td>08227226</td>\n",
       "      <td>GB</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    address_CareOf address_Country address_County        address_Line1  \\\n",
       "37                         ENGLAND                         The Airport   \n",
       "47                         ENGLAND                      Olympus Avenue   \n",
       "88                         ENGLAND                  Gracechurch Street   \n",
       "100                        ENGLAND                 Victoria Embankment   \n",
       "121                 UNITED KINGDOM      HAMPSHIRE         Hussar Court   \n",
       "\n",
       "    address_Line2 address_POBox address_PostCode address_PostTown company_id  \\\n",
       "37                                       CB5 8RY        CAMBRIDGE   00287379   \n",
       "47                                      CV34 6BF   LEAMINGTON SPA   07790030   \n",
       "88                                      EC3V 0BT           LONDON   05955659   \n",
       "100                                     EC4Y 0DZ           LONDON   04240399   \n",
       "121                                      PO7 7SQ    WATERLOOVILLE   08228404   \n",
       "\n",
       "                                         control_kind  \\\n",
       "37   corporate-entity-person-with-significant-control   \n",
       "47   corporate-entity-person-with-significant-control   \n",
       "88   corporate-entity-person-with-significant-control   \n",
       "100  corporate-entity-person-with-significant-control   \n",
       "121  corporate-entity-person-with-significant-control   \n",
       "\n",
       "              legal_authority                                     name  \\\n",
       "37                    England            S.G. SMITH AUTOMOTIVE LIMITED   \n",
       "47         Companies Act 2006                    1846 NOMINEES LIMITED   \n",
       "88         Companies Act 2006                  UPP INVESTMENTS LIMITED   \n",
       "100        United Kingdom Law         OCTOPUS PUBLISHING GROUP LIMITED   \n",
       "121  Companies House Act 2006  R & S YOUNG MANAGEMENT SERVICES LIMITED   \n",
       "\n",
       "                                    natures_of_control  \\\n",
       "37   {'ownership-of-shares-75-to-100-percent': 1, '...   \n",
       "47   {'ownership-of-shares-75-to-100-percent': 1, '...   \n",
       "88   {'ownership-of-shares-75-to-100-percent': 1, '...   \n",
       "100       {'ownership-of-shares-75-to-100-percent': 1}   \n",
       "121  {'ownership-of-shares-75-to-100-percent': 1, '...   \n",
       "\n",
       "                             place_registered         premises  \\\n",
       "37   Registrar Of Companies (England & Wales)    Airport House   \n",
       "47                            Companies House    Olympus House   \n",
       "88   Registrar Of Companies England And Wales               40   \n",
       "100                           Companies House               50   \n",
       "121                 The Register Of Companies  24 Picton House   \n",
       "\n",
       "    registered_country       uid Registered_in  \n",
       "37             England  00622112            GB  \n",
       "47   England And Wales  05953366            GB  \n",
       "88             England   5957759            GB  \n",
       "100  England And Wales   3597451            GB  \n",
       "121  England And Wales  08227226            GB  "
      ]
     },
     "execution_count": 39,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "neo_records_df = corporate_psc.head(10).apply(lambda s: pd.Series(new_corporate_record(s)), axis=1)\n",
    "neo_records_df['Registered_in'] = neo_records_df.registered_country.apply(lambda x: combined_map.get(x.upper(), ''))\n",
    "\n",
    "neo_records_df.tail(5)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Now to insert the Corporate PSCs\n",
    "\n",
    "Now we can loop over the larger set of data and insert all of the active PSCs"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 69,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "def write_corporate_psc_to_neo(input_data, driver):\n",
    "    \"\"\"Function writes corporate records to Neo4j database\n",
    "    From a list of dictionaries construct the appropriate nodes and relationships to be inserted into Neo4j\n",
    "    :param input_data - a list of dictionaries that have all the required information for nodes and relationships\n",
    "    :param driver - an active driver object to connect to a neo4j instance\n",
    "    :return \"\"\"\n",
    "    \n",
    "    with driver.session() as session:\n",
    "        session.run((\"UNWIND {list} AS d \"\n",
    "                     \"MERGE (c:Company {uid: d.uid}) \"\n",
    "                     \"ON CREATE SET c.name=d.name, \"\n",
    "                     \"c.address_premises=d.premises, \"\n",
    "                     \"c.address_Line1=d.address_Line1, \"\n",
    "                     \"c.address_Line2=d.address_Line2, \"\n",
    "                     \"c.address_PostTown=d.address_PostTown, \"\n",
    "                     \"c.address_POBox=d.address_POBox, \"\n",
    "                     \"c.address_County=d.address_County, \"\n",
    "                     \"c.address_PostCode=d.address_Postcode, \"                 \n",
    "                     \"c.address_Country=d.address_Country, \"\n",
    "                     \"c.legal_authority=d.legal_authority, \"\n",
    "                     \"c.place_registered=d.place_registered, \"\n",
    "                     \"c.registered_country=d.registered_country;\"), {\"list\": input_data})\n",
    "\n",
    "    with driver.session() as session:\n",
    "        session.run((\"UNWIND {list} AS d \"\n",
    "                     \"MATCH (c:Company {uid: d.uid}) \"\n",
    "                     \"MERGE (country:Country {code: d.Registered_in}) \"\n",
    "                     \"MERGE (c)-[:REGISTERED_IN]->(country);\"), {\"list\": input_data})\n",
    "\n",
    "    with driver.session() as session:\n",
    "        session.run((\"UNWIND {list} AS d \"\n",
    "                     \"MATCH (p:Company {uid: d.uid}) \"\n",
    "                     \"MERGE (ce:ControllingEntity {type: d.control_kind}) \"\n",
    "                     \"MERGE (p)-[:HAS_CONTROL_KIND]->(ce);\"), {\"list\": input_data})    \n",
    "\n",
    "    with driver.session() as session:\n",
    "        session.run((\"UNWIND {list} AS d \"\n",
    "                     \"MATCH (p:Company {uid: d.uid}) \"\n",
    "                     \"MERGE (c:Company {uid: d.company_id}) \"\n",
    "                     \"MERGE (p)-[r:CONTROLS]->(c) ON CREATE SET r=d.natures_of_control;\"), {\"list\": input_data})\n",
    "\n",
    "    with driver.session() as session:\n",
    "        session.run((\"UNWIND {list} AS d \"\n",
    "                     \"MATCH (c:Company {uid: d.uid}) \"\n",
    "                     \"MERGE (pc:Postcode {uid: d.address_PostCode}) \"\n",
    "                     \"MERGE (c)-[:REGISTERED_IN]->(pc);\"), {\"list\": input_data})"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Using the odo library within blaze we can loop over our input data in chunks"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "driver = GraphDatabase.driver(\"bolt://10.0.0.1:7687\", auth=(\"myusername\", \"mypassword\"))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 70,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Processed 50000 of 344866 .... 14.50% complete\n",
      "Processed 100000 of 344866 .... 29.00% complete\n",
      "Processed 150000 of 344866 .... 43.50% complete\n",
      "Processed 200000 of 344866 .... 57.99% complete\n",
      "Processed 250000 of 344866 .... 72.49% complete\n",
      "Processed 300000 of 344866 .... 86.99% complete\n",
      "Processed 344866 of 344866 .... 100.00% complete\n"
     ]
    }
   ],
   "source": [
    "import blaze as bz\n",
    "\n",
    "proc_records = 0\n",
    "total_records = corporate_psc.shape[0]\n",
    "for chunk in bz.odo(corporate_psc, target=bz.chunks(pd.DataFrame), chunksize=50000):\n",
    "    neo_records_df = chunk.apply(lambda s: pd.Series(new_corporate_record(s)), axis=1)\n",
    "    neo_records_df['Registered_in'] = neo_records_df.registered_country.apply(lambda x: combined_map.get(str(x).upper(), 'UNKNOWN'))\n",
    "\n",
    "    input_data = [v for k,v in neo_records_df.T.to_dict().items()]\n",
    "    del neo_records_df\n",
    "    \n",
    "    write_corporate_psc_to_neo(input_data, driver)\n",
    "    proc_records += len(input_data)\n",
    "    print(\"Processed {} of {} .... {:5.2f}% complete\".format(proc_records, total_records, 100*proc_records/total_records))"
   ]
  },
  {
   "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
}