04.Inserting-human-PSCs.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Inserting Individual PSCs into the graph\n",
    "\n",
    "The largest class of PSC are the individual \"human\" owners. Here we load in the full dataset and select out the individuals before converting the raw data into a form that will be stored within the neo4j graph."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "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": 5,
   "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": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "all_records_psc.kind.value_counts()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Filter the data to only handle individual people with control"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "human_psc = all_records_psc[all_records_psc.kind == \"individual-person-with-significant-control\"]\n",
    "del all_records_psc"
   ]
  },
  {
   "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>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>10903</th>\n",
       "      <td>10262086</td>\n",
       "      <td>Wimborne Road</td>\n",
       "      <td>Colehill</td>\n",
       "      <td>NaN</td>\n",
       "      <td>England</td>\n",
       "      <td>Wimborne</td>\n",
       "      <td>NaN</td>\n",
       "      <td>BH21 2QS</td>\n",
       "      <td>1 Richmond Villas</td>\n",
       "      <td>Dorset</td>\n",
       "      <td>...</td>\n",
       "      <td>Louise</td>\n",
       "      <td>Wilkey</td>\n",
       "      <td>Mrs</td>\n",
       "      <td>English</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",
       "  </tbody>\n",
       "</table>\n",
       "<p>1 rows × 46 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "      company_number address.address_line_1 address.address_line_2  \\\n",
       "10903       10262086          Wimborne Road               Colehill   \n",
       "\n",
       "      address.care_of address.country address.locality address.po_box  \\\n",
       "10903             NaN         England         Wimborne            NaN   \n",
       "\n",
       "      address.postal_code   address.premises address.region       ...         \\\n",
       "10903            BH21 2QS  1 Richmond Villas         Dorset       ...          \n",
       "\n",
       "       name_elements.middle_name name_elements.surname name_elements.title  \\\n",
       "10903                     Louise                Wilkey                 Mrs   \n",
       "\n",
       "       nationality                                 natures_of_control  \\\n",
       "10903      English  [ownership-of-shares-25-to-50-percent, voting-...   \n",
       "\n",
       "      notified_on persons_of_significant_control_count  \\\n",
       "10903  2016-04-06                                  NaN   \n",
       "\n",
       "      restrictions_notice_withdrawal_reason statement statements_count  \n",
       "10903                                   NaN       NaN              NaN  \n",
       "\n",
       "[1 rows x 46 columns]"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "human_psc[human_psc['company_number'] == '10262086']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "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": 11,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "human_psc['DICTIONARY_OF_CONTROLS'] = human_psc['natures_of_control'].map(convert_control_list)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "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",
       "    <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>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",
       "      <td>{'ownership-of-shares-50-to-75-percent': 1}</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>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",
       "      <td>{'ownership-of-shares-25-to-50-percent': 1, 'o...</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>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",
       "      <td>{'ownership-of-shares-25-to-50-percent': 1, 'v...</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>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",
       "      <td>{'ownership-of-shares-50-to-75-percent': 1}</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>10259081</td>\n",
       "      <td>Albion Dockside Estate</td>\n",
       "      <td>Hanover Place</td>\n",
       "      <td>NaN</td>\n",
       "      <td>United Kingdom</td>\n",
       "      <td>Bristol</td>\n",
       "      <td>NaN</td>\n",
       "      <td>BS1 6UT</td>\n",
       "      <td>Albion Dockside Building</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>Dance</td>\n",
       "      <td>Mr</td>\n",
       "      <td>British</td>\n",
       "      <td>[ownership-of-shares-50-to-75-percent, voting-...</td>\n",
       "      <td>2016-07-18</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': 1, 'v...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 47 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",
       "4       10259080          38 Church Road         Worcester Park   \n",
       "6       10259081  Albion Dockside Estate          Hanover Place   \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",
       "4             NaN             NaN            Surrey            NaN   \n",
       "6             NaN  United Kingdom           Bristol            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",
       "4             KT4 7RD                       NaN              NaN   \n",
       "6             BS1 6UT  Albion Dockside Building              NaN   \n",
       "\n",
       "                         ...                          name_elements.surname  \\\n",
       "0                        ...                                        Wildman   \n",
       "1                        ...                                         Davies   \n",
       "2                        ...                                        Tarrant   \n",
       "4                        ...                                         Ollett   \n",
       "6                        ...                                          Dance   \n",
       "\n",
       "  name_elements.title nationality  \\\n",
       "0                 Mrs  Vietnamese   \n",
       "1                  Mr     British   \n",
       "2                 NaN     British   \n",
       "4                  Mr     British   \n",
       "6                  Mr     British   \n",
       "\n",
       "                                  natures_of_control  notified_on  \\\n",
       "0             [ownership-of-shares-50-to-75-percent]   2016-04-06   \n",
       "1  [ownership-of-shares-25-to-50-percent, ownersh...   2016-06-30   \n",
       "2  [ownership-of-shares-25-to-50-percent, voting-...   2016-04-06   \n",
       "4             [ownership-of-shares-50-to-75-percent]   2016-06-30   \n",
       "6  [ownership-of-shares-50-to-75-percent, voting-...   2016-07-18   \n",
       "\n",
       "  persons_of_significant_control_count restrictions_notice_withdrawal_reason  \\\n",
       "0                                  NaN                                   NaN   \n",
       "1                                  NaN                                   NaN   \n",
       "2                                  NaN                                   NaN   \n",
       "4                                  NaN                                   NaN   \n",
       "6                                  NaN                                   NaN   \n",
       "\n",
       "  statement statements_count  \\\n",
       "0       NaN              NaN   \n",
       "1       NaN              NaN   \n",
       "2       NaN              NaN   \n",
       "4       NaN              NaN   \n",
       "6       NaN              NaN   \n",
       "\n",
       "                              DICTIONARY_OF_CONTROLS  \n",
       "0        {'ownership-of-shares-50-to-75-percent': 1}  \n",
       "1  {'ownership-of-shares-25-to-50-percent': 1, 'o...  \n",
       "2  {'ownership-of-shares-25-to-50-percent': 1, 'v...  \n",
       "4        {'ownership-of-shares-50-to-75-percent': 1}  \n",
       "6  {'ownership-of-shares-50-to-75-percent': 1, 'v...  \n",
       "\n",
       "[5 rows x 47 columns]"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "human_psc.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "WARNING: Missing company number for 1 companies in list\n",
      "Removing these rows from data; if significant you should investigate\n"
     ]
    }
   ],
   "source": [
    "number_missing_companyID = human_psc[human_psc['company_number'].isnull()].shape[0]\n",
    "print(\"WARNING: Missing company number for {} companies in list\".format(number_missing_companyID))\n",
    "print(\"Removing these rows from data; if significant you should investigate\")\n",
    "human_psc = human_psc[human_psc['company_number'].notnull()]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "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": 28,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "def psc_dob(record):\n",
    "    \"Function to create dob string identifier for person\"\n",
    "    try:\n",
    "        dob_segment = \"{}/{}\".format(str(int(record['date_of_birth.month'])).zfill(2), int(record['date_of_birth.year']))\n",
    "        return dob_segment\n",
    "    except TypeError as e:\n",
    "        return '00/0000'\n",
    "    except ValueError as e:\n",
    "        return '00/0000'\n",
    "\n",
    "def psc_name_id(record):\n",
    "    \"Function to create name string identifier for person\"\n",
    "    try:\n",
    "        name_segment = \"{}_{}_{}\".format(str(record['name_elements.surname']).upper(), \n",
    "                                         str(record['name_elements.middle_name']).lower(),\n",
    "                                         str(record['name_elements.forename']).lower())\n",
    "        return name_segment.replace(' ', '-').replace('nan', '')\n",
    "    except TypeError as e:\n",
    "        return 'NONAME__BLANK'\n",
    "    except AttributeError:\n",
    "        return 'NONAME__BLANK'\n",
    "    \n",
    "def psc_uid(record):\n",
    "    \"Function to create a unique ID for someone from their name and dob\"\n",
    "    components = [psc_name_id(record), psc_dob(record)]\n",
    "    return {'uid': ':'.join(components)}\n",
    "\n",
    "def psc_name(record):\n",
    "    \"Function to create name record of person\"\n",
    "    try:\n",
    "        name_record = {\n",
    "            'surname': str(record['name_elements.surname']),\n",
    "            'middle_name': str(record['name_elements.middle_name']),\n",
    "            'forename': str(record['name_elements.forename']),\n",
    "            'title': str(record['name_elements.title']),\n",
    "            'name': record.get('name', '').upper()\n",
    "        }\n",
    "        return {k: v.replace('nan', '') for k,v in name_record.items()}\n",
    "    except TypeError as e:\n",
    "        return {}\n",
    "    except AttributeError:\n",
    "        return {}\n",
    "\n",
    "def psc_address(record):\n",
    "    \"Function to create a address details for person\"\n",
    "    data = record\n",
    "    try:\n",
    "        new_address = {\n",
    "            'premises': str(data.get('address.premises', '')).replace('nan', ''),\n",
    "            'address_Line1': str(data.get('address.address_line_1', '')).replace('nan', ''),\n",
    "            'address_Line2': str(data.get('address.address_line_2', '')).replace('nan', ''),\n",
    "            'address_PostTown': str(data.get('address.locality', '').replace('nan', '')).upper(),\n",
    "            'address_POBox': str(data.get('address.po_box', '')).replace('nan', ''),\n",
    "            'address_County': str(data.get('address.region', '')).replace('nan', '').upper(),\n",
    "            'address_PostCode': str(data.get('address.postal_code', '')).replace('nan', '').upper(),\n",
    "            'address_Country': str(data.get('address.country', '')).replace('nan', '').upper(),\n",
    "            'address_CareOf': str(data.get('address.care_of', '')).replace('nan', '')\n",
    "        }\n",
    "        return new_address\n",
    "    except TypeError as e:\n",
    "        return {'address_Country': \"UNKNOWN\"}\n",
    "    except AttributeError:\n",
    "        return {'address_Country': \"UNKNOWN\"}\n",
    "    \n",
    "def 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",
    "    details['nationality'] = record['nationality']\n",
    "    details['ceased_on'] = record['ceased_on']\n",
    "    dob = psc_dob(record)\n",
    "    if dob != '00/0000':\n",
    "        details['DOB'] = dob\n",
    "    return details\n",
    "    \n",
    "\n",
    "def new_record(record):\n",
    "    \"Function to create a new record that is flat\"\n",
    "    uid = psc_uid(record)\n",
    "    address = psc_address(record)\n",
    "    return {**uid, **address, \n",
    "            **psc_details(record), \n",
    "            **psc_name(record), \n",
    "            'natures_of_control': record['DICTIONARY_OF_CONTROLS']}\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "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>DOB</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>ceased_on</th>\n",
       "      <th>...</th>\n",
       "      <th>control_kind</th>\n",
       "      <th>forename</th>\n",
       "      <th>middle_name</th>\n",
       "      <th>name</th>\n",
       "      <th>nationality</th>\n",
       "      <th>natures_of_control</th>\n",
       "      <th>premises</th>\n",
       "      <th>surname</th>\n",
       "      <th>title</th>\n",
       "      <th>uid</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>02/1977</td>\n",
       "      <td></td>\n",
       "      <td>ENGLAND</td>\n",
       "      <td></td>\n",
       "      <td>St. Andrews Road</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>RG9 1HP</td>\n",
       "      <td>HENLEY-ON-THAMES</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>individual-person-with-significant-control</td>\n",
       "      <td>Nga</td>\n",
       "      <td>Thanh</td>\n",
       "      <td>MRS NGA THANH WILDMAN</td>\n",
       "      <td>Vietnamese</td>\n",
       "      <td>{'ownership-of-shares-50-to-75-percent': 1}</td>\n",
       "      <td>2</td>\n",
       "      <td>Wildman</td>\n",
       "      <td>Mrs</td>\n",
       "      <td>WILDMAN_thanh_nga:02/1977</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>09/1947</td>\n",
       "      <td></td>\n",
       "      <td>ENGLAND</td>\n",
       "      <td>BUCKINGHAMSHIRE</td>\n",
       "      <td>High Street</td>\n",
       "      <td>Wendover</td>\n",
       "      <td></td>\n",
       "      <td>HP22 6EA</td>\n",
       "      <td>AYLESBURY</td>\n",
       "      <td>2016-07-01</td>\n",
       "      <td>...</td>\n",
       "      <td>individual-person-with-significant-control</td>\n",
       "      <td>Stephen</td>\n",
       "      <td>Robert Charles</td>\n",
       "      <td>MR STEPHEN ROBERT CHARLES DAVIES</td>\n",
       "      <td>British</td>\n",
       "      <td>{'ownership-of-shares-25-to-50-percent': 1, 'o...</td>\n",
       "      <td>14a</td>\n",
       "      <td>Davies</td>\n",
       "      <td>Mr</td>\n",
       "      <td>DAVIES_robert-charles_stephen:09/1947</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>01/1966</td>\n",
       "      <td></td>\n",
       "      <td>UNITED KINGDOM</td>\n",
       "      <td>SUFFOLK</td>\n",
       "      <td>Holywells Road</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>IP3 0DL</td>\n",
       "      <td>IPSWICH</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>individual-person-with-significant-control</td>\n",
       "      <td>Gayle</td>\n",
       "      <td>Fiona</td>\n",
       "      <td>GAYLE FIONA TARRANT</td>\n",
       "      <td>British</td>\n",
       "      <td>{'ownership-of-shares-25-to-50-percent': 1, 'v...</td>\n",
       "      <td>37-41</td>\n",
       "      <td>Tarrant</td>\n",
       "      <td></td>\n",
       "      <td>TARRANT_fiona_gayle:01/1966</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>06/1948</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>38 Church Road</td>\n",
       "      <td>Worcester Park</td>\n",
       "      <td></td>\n",
       "      <td>KT4 7RD</td>\n",
       "      <td>SURREY</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>individual-person-with-significant-control</td>\n",
       "      <td>Jocelyn</td>\n",
       "      <td>Peter</td>\n",
       "      <td>MR JOCELYN PETER OLLETT</td>\n",
       "      <td>British</td>\n",
       "      <td>{'ownership-of-shares-50-to-75-percent': 1}</td>\n",
       "      <td></td>\n",
       "      <td>Ollett</td>\n",
       "      <td>Mr</td>\n",
       "      <td>OLLETT_peter_jocelyn:06/1948</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>12/1959</td>\n",
       "      <td></td>\n",
       "      <td>UNITED KINGDOM</td>\n",
       "      <td></td>\n",
       "      <td>Albion Dockside Estate</td>\n",
       "      <td>Hanover Place</td>\n",
       "      <td></td>\n",
       "      <td>BS1 6UT</td>\n",
       "      <td>BRISTOL</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>individual-person-with-significant-control</td>\n",
       "      <td>Nigel</td>\n",
       "      <td>Keith</td>\n",
       "      <td>MR NIGEL KEITH DANCE</td>\n",
       "      <td>British</td>\n",
       "      <td>{'ownership-of-shares-50-to-75-percent': 1, 'v...</td>\n",
       "      <td>Albion Dockside Building</td>\n",
       "      <td>Dance</td>\n",
       "      <td>Mr</td>\n",
       "      <td>DANCE_keith_nigel:12/1959</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 21 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "       DOB address_CareOf address_Country   address_County  \\\n",
       "0  02/1977                        ENGLAND                    \n",
       "1  09/1947                        ENGLAND  BUCKINGHAMSHIRE   \n",
       "2  01/1966                 UNITED KINGDOM          SUFFOLK   \n",
       "4  06/1948                                                   \n",
       "6  12/1959                 UNITED KINGDOM                    \n",
       "\n",
       "            address_Line1   address_Line2 address_POBox address_PostCode  \\\n",
       "0        St. Andrews Road                                        RG9 1HP   \n",
       "1             High Street        Wendover                       HP22 6EA   \n",
       "2          Holywells Road                                        IP3 0DL   \n",
       "4          38 Church Road  Worcester Park                        KT4 7RD   \n",
       "6  Albion Dockside Estate   Hanover Place                        BS1 6UT   \n",
       "\n",
       "   address_PostTown   ceased_on                  ...                    \\\n",
       "0  HENLEY-ON-THAMES         NaN                  ...                     \n",
       "1         AYLESBURY  2016-07-01                  ...                     \n",
       "2           IPSWICH         NaN                  ...                     \n",
       "4            SURREY         NaN                  ...                     \n",
       "6           BRISTOL         NaN                  ...                     \n",
       "\n",
       "                                 control_kind forename     middle_name  \\\n",
       "0  individual-person-with-significant-control      Nga           Thanh   \n",
       "1  individual-person-with-significant-control  Stephen  Robert Charles   \n",
       "2  individual-person-with-significant-control    Gayle           Fiona   \n",
       "4  individual-person-with-significant-control  Jocelyn           Peter   \n",
       "6  individual-person-with-significant-control    Nigel           Keith   \n",
       "\n",
       "                               name nationality  \\\n",
       "0             MRS NGA THANH WILDMAN  Vietnamese   \n",
       "1  MR STEPHEN ROBERT CHARLES DAVIES     British   \n",
       "2               GAYLE FIONA TARRANT     British   \n",
       "4           MR JOCELYN PETER OLLETT     British   \n",
       "6              MR NIGEL KEITH DANCE     British   \n",
       "\n",
       "                                  natures_of_control  \\\n",
       "0        {'ownership-of-shares-50-to-75-percent': 1}   \n",
       "1  {'ownership-of-shares-25-to-50-percent': 1, 'o...   \n",
       "2  {'ownership-of-shares-25-to-50-percent': 1, 'v...   \n",
       "4        {'ownership-of-shares-50-to-75-percent': 1}   \n",
       "6  {'ownership-of-shares-50-to-75-percent': 1, 'v...   \n",
       "\n",
       "                   premises  surname title  \\\n",
       "0                         2  Wildman   Mrs   \n",
       "1                       14a   Davies    Mr   \n",
       "2                     37-41  Tarrant         \n",
       "4                             Ollett    Mr   \n",
       "6  Albion Dockside Building    Dance    Mr   \n",
       "\n",
       "                                     uid  \n",
       "0              WILDMAN_thanh_nga:02/1977  \n",
       "1  DAVIES_robert-charles_stephen:09/1947  \n",
       "2            TARRANT_fiona_gayle:01/1966  \n",
       "4           OLLETT_peter_jocelyn:06/1948  \n",
       "6              DANCE_keith_nigel:12/1959  \n",
       "\n",
       "[5 rows x 21 columns]"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "human_psc.iloc[0:5].apply(lambda s: pd.Series(new_record(s)), axis=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "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>DOB</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>ceased_on</th>\n",
       "      <th>...</th>\n",
       "      <th>middle_name</th>\n",
       "      <th>name</th>\n",
       "      <th>nationality</th>\n",
       "      <th>natures_of_control</th>\n",
       "      <th>premises</th>\n",
       "      <th>surname</th>\n",
       "      <th>title</th>\n",
       "      <th>uid</th>\n",
       "      <th>Citizen_of</th>\n",
       "      <th>Registered_in</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>04/1960</td>\n",
       "      <td></td>\n",
       "      <td>UNITED KINGDOM</td>\n",
       "      <td></td>\n",
       "      <td>41 Walsingham Road</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>EN2 6EY</td>\n",
       "      <td>ENFIELD</td>\n",
       "      <td>2016-10-11</td>\n",
       "      <td>...</td>\n",
       "      <td>Donald</td>\n",
       "      <td>MR CLIFFORD DONALD WING</td>\n",
       "      <td>British</td>\n",
       "      <td>{'ownership-of-shares-75-to-100-percent': 1, '...</td>\n",
       "      <td>Parkside House</td>\n",
       "      <td>Wing</td>\n",
       "      <td>Mr</td>\n",
       "      <td>WING_donald_clifford:04/1960</td>\n",
       "      <td>GB</td>\n",
       "      <td>GB</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>07/1962</td>\n",
       "      <td></td>\n",
       "      <td>SCOTLAND</td>\n",
       "      <td></td>\n",
       "      <td>85 East London Street</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>EH7 4BQ</td>\n",
       "      <td>EDINBURGH</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td></td>\n",
       "      <td>MR MARK HARRISON</td>\n",
       "      <td>British</td>\n",
       "      <td>{'ownership-of-shares-75-to-100-percent': 1}</td>\n",
       "      <td>Flat 7</td>\n",
       "      <td>Harrison</td>\n",
       "      <td>Mr</td>\n",
       "      <td>HARRISON__mark:07/1962</td>\n",
       "      <td>GB</td>\n",
       "      <td>GB</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>07/1942</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>250 York Road</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>SW11 3SJ</td>\n",
       "      <td>LONDON</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>Van</td>\n",
       "      <td>MR JOHN VAN SOMEREN</td>\n",
       "      <td>British</td>\n",
       "      <td>{'significant-influence-or-control': 1}</td>\n",
       "      <td>Office 209</td>\n",
       "      <td>Someren</td>\n",
       "      <td>Mr</td>\n",
       "      <td>SOMEREN_van_john:07/1942</td>\n",
       "      <td>GB</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>08/1971</td>\n",
       "      <td></td>\n",
       "      <td>UNITED KINGDOM</td>\n",
       "      <td></td>\n",
       "      <td>Battlefield Enterprise Park</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>SY1 3AF</td>\n",
       "      <td>SHREWSBURY</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>Kikis</td>\n",
       "      <td>MS MARIA KIKIS ZACHARIADES</td>\n",
       "      <td>British</td>\n",
       "      <td>{'ownership-of-shares-25-to-50-percent': 1, 'v...</td>\n",
       "      <td>10 Park Plaza</td>\n",
       "      <td>Zachariades</td>\n",
       "      <td>Ms</td>\n",
       "      <td>ZACHARIADES_kikis_maria:08/1971</td>\n",
       "      <td>GB</td>\n",
       "      <td>GB</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>10/1979</td>\n",
       "      <td></td>\n",
       "      <td>ENGLAND</td>\n",
       "      <td>HAMPSHIRE</td>\n",
       "      <td>6 St. Cross Road</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>SO23 9HX</td>\n",
       "      <td>WINCHESTER</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td></td>\n",
       "      <td>MR RAJAN VENUGOPAL SOUNDRA</td>\n",
       "      <td>British</td>\n",
       "      <td>{'ownership-of-shares-75-to-100-percent': 1, '...</td>\n",
       "      <td>Sg House</td>\n",
       "      <td>Venugopal Soundra</td>\n",
       "      <td>Mr</td>\n",
       "      <td>VENUGOPAL-SOUNDRA__rajan:10/1979</td>\n",
       "      <td>GB</td>\n",
       "      <td>GB</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 23 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "        DOB address_CareOf address_Country address_County  \\\n",
       "7   04/1960                 UNITED KINGDOM                  \n",
       "8   07/1962                       SCOTLAND                  \n",
       "9   07/1942                                                 \n",
       "10  08/1971                 UNITED KINGDOM                  \n",
       "12  10/1979                        ENGLAND      HAMPSHIRE   \n",
       "\n",
       "                  address_Line1 address_Line2 address_POBox address_PostCode  \\\n",
       "7            41 Walsingham Road                                      EN2 6EY   \n",
       "8         85 East London Street                                      EH7 4BQ   \n",
       "9                 250 York Road                                     SW11 3SJ   \n",
       "10  Battlefield Enterprise Park                                      SY1 3AF   \n",
       "12             6 St. Cross Road                                     SO23 9HX   \n",
       "\n",
       "   address_PostTown   ceased_on      ...      middle_name  \\\n",
       "7           ENFIELD  2016-10-11      ...           Donald   \n",
       "8         EDINBURGH         NaN      ...                    \n",
       "9            LONDON         NaN      ...              Van   \n",
       "10       SHREWSBURY         NaN      ...            Kikis   \n",
       "12       WINCHESTER         NaN      ...                    \n",
       "\n",
       "                          name nationality  \\\n",
       "7      MR CLIFFORD DONALD WING     British   \n",
       "8             MR MARK HARRISON     British   \n",
       "9          MR JOHN VAN SOMEREN     British   \n",
       "10  MS MARIA KIKIS ZACHARIADES     British   \n",
       "12  MR RAJAN VENUGOPAL SOUNDRA     British   \n",
       "\n",
       "                                   natures_of_control        premises  \\\n",
       "7   {'ownership-of-shares-75-to-100-percent': 1, '...  Parkside House   \n",
       "8        {'ownership-of-shares-75-to-100-percent': 1}          Flat 7   \n",
       "9             {'significant-influence-or-control': 1}      Office 209   \n",
       "10  {'ownership-of-shares-25-to-50-percent': 1, 'v...   10 Park Plaza   \n",
       "12  {'ownership-of-shares-75-to-100-percent': 1, '...        Sg House   \n",
       "\n",
       "              surname title                               uid Citizen_of  \\\n",
       "7                Wing    Mr      WING_donald_clifford:04/1960         GB   \n",
       "8            Harrison    Mr            HARRISON__mark:07/1962         GB   \n",
       "9             Someren    Mr          SOMEREN_van_john:07/1942         GB   \n",
       "10        Zachariades    Ms   ZACHARIADES_kikis_maria:08/1971         GB   \n",
       "12  Venugopal Soundra    Mr  VENUGOPAL-SOUNDRA__rajan:10/1979         GB   \n",
       "\n",
       "   Registered_in  \n",
       "7             GB  \n",
       "8             GB  \n",
       "9                 \n",
       "10            GB  \n",
       "12            GB  \n",
       "\n",
       "[5 rows x 23 columns]"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "neo_records_df = human_psc.head(10).apply(lambda s: pd.Series(new_record(s)), axis=1)\n",
    "\n",
    "neo_records_df['Citizen_of'] = neo_records_df.nationality.apply(lambda x: nationality_map.get(x.lower(), ''))\n",
    "neo_records_df['Registered_in'] = neo_records_df.address_Country.apply(lambda x: combined_map.get(x.upper(), ''))\n",
    "\n",
    "neo_records_df.tail(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 82,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "59144"
      ]
     },
     "execution_count": 82,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "human_psc.ceased_on.value_counts().sum()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Let us first insert old PSCs that have now ceased controlling a company\n",
    "\n",
    "If we we have \"ceased_on\" date then we know we have a former PSC."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(59144, 24)\n"
     ]
    }
   ],
   "source": [
    "neo_records_df = human_psc[human_psc.ceased_on.notnull()].apply(lambda s: pd.Series(new_record(s)), axis=1)\n",
    "\n",
    "neo_records_df['Citizen_of'] = neo_records_df.nationality.apply(lambda x: nationality_map.get(x.lower(), ''))\n",
    "neo_records_df['Registered_in'] = neo_records_df.address_Country.apply(lambda x: combined_map.get(x.upper(), ''))\n",
    "\n",
    "neo_records_df.tail(5)\n",
    "print(neo_records_df.shape)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Need a little more enrichment for when the control relationship ceased"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "def extend_control_relations(row):\n",
    "    original = row['natures_of_control']\n",
    "    original.update({'ceased_on': row['ceased_on']})\n",
    "    return original\n",
    "\n",
    "neo_records_df['natures_of_control'] = neo_records_df.apply(lambda x: extend_control_relations(x), axis=1)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We need to convert the dataframe into a list of record details before we start to insert into the Neo4j database"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "input_data = [v for k,v in neo_records_df.T.to_dict().items()]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "driver = GraphDatabase.driver(\"bolt://10.0.0.1:7687\", auth=(\"myusername\", \"mypassword\"))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Now** we are ready to run over the data and insert the people and relationships"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 103,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "with driver.session() as session:\n",
    "    session.run((\"UNWIND {list} AS d \"\n",
    "                 \"MERGE (c:Person {uid: d.uid}) \"\n",
    "                 \"ON CREATE SET c:PSC, c.forename=d.forename, \"\n",
    "                 \"c.middle_name=d.middle_name, \"\n",
    "                 \"c.surname=d.surname, \"\n",
    "                 \"c.title=d.title, \"\n",
    "                 \"c.name=d.name, \"\n",
    "                 \"c.dob=d.DOB, \"\n",
    "                 \"c.nationality=d.nationality, \"\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.uri=d.URI;\"), {\"list\": input_data})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 104,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "with driver.session() as session:\n",
    "    session.run((\"UNWIND {list} AS d \"\n",
    "                 \"MATCH (c:Person {uid: d.uid}) \"\n",
    "                 \"MERGE (country:Country {code: d.Registered_in}) \"\n",
    "                 \"MERGE (c)-[:REGISTERED_IN]->(country);\"), {\"list\": input_data})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 105,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "with driver.session() as session:\n",
    "    session.run((\"UNWIND {list} AS d \"\n",
    "                 \"MATCH (c:Person {uid: d.uid}) \"\n",
    "                 \"MERGE (country:Country {code: d.Citizen_of}) \"\n",
    "                 \"MERGE (c)-[:CITIZEN_OF]->(country);\"), {\"list\": input_data})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 106,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "with driver.session() as session:\n",
    "    session.run((\"UNWIND {list} AS d \"\n",
    "                 \"MATCH (p:Person {uid: d.uid}) \"\n",
    "                 \"MERGE (ce:ControllingEntity {type: d.control_kind}) \"\n",
    "                 \"MERGE (p)-[:HAS_CONTROL_KIND]->(ce);\"), {\"list\": input_data})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 120,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "with driver.session() as session:\n",
    "    session.run((\"UNWIND {list} AS d \"\n",
    "                 \"MATCH (p:Person {uid: d.uid}) \"\n",
    "                 \"MERGE (c:Company {uid: d.company_id}) \"\n",
    "                 \"MERGE (p)-[r:CONTROLLED]->(c) ON CREATE SET r=d.natures_of_control;\"), {\"list\": input_data})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "with driver.session() as session:\n",
    "    session.run((\"UNWIND {list} AS d \"\n",
    "                 \"MATCH (p:Person {uid: d.uid}) \"\n",
    "                 \"MERGE (pc:Postcode {uid: d.address_PostCode}) \"\n",
    "                 \"MERGE (p)-[:REGISTERED_IN]->(pc);\"), {\"list\": input_data})"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Let's check how many people, controlled relationships and companies we've connected together"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 122,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "[{'COUNT(distinct p)': 47705, 'COUNT(distinct r)': 59131, 'COUNT(distinct c)': 58485}]\n"
     ]
    }
   ],
   "source": [
    "with driver.session() as session:\n",
    "    result = session.run(\"MATCH (p:Person)-[r:CONTROLLED]-(c:Company) RETURN COUNT(distinct p), COUNT(distinct r), COUNT(distinct c);\")\n",
    "    print(result.data())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Now for active PSCs\n",
    "\n",
    "Let's turn our CYPHER writes into a function we can use in a loop to move over the data. \n",
    "\n",
    "Now we can loop over the larger set of data and insert all of the active PSCs"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "def insert_individual_PSC_nodes_and_relationships(input_data, driver):\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",
    "    # Make sure nodes exist for Person\n",
    "    with driver.session() as session:\n",
    "        session.run((\"UNWIND {list} AS d \"\n",
    "                     \"MERGE (c:Person {uid: d.uid}) \"\n",
    "                     \"ON CREATE SET c.forename=d.forename, \"\n",
    "                     \"c.middle_name=d.middle_name, \"\n",
    "                     \"c.surname=d.surname, \"\n",
    "                     \"c.title=d.title, \"\n",
    "                     \"c.name=d.name, \"\n",
    "                     \"c.dob=d.DOB, \"\n",
    "                     \"c.nationality=d.nationality, \"\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.uri=d.URI;\"), {\"list\": input_data})\n",
    "        \n",
    "    # Connect Person with Country of registration\n",
    "    with driver.session() as session:\n",
    "        session.run((\"UNWIND {list} AS d \"\n",
    "                     \"MATCH (c:Person {uid: d.uid}) \"\n",
    "                     \"MERGE (country:Country {code: d.Registered_in}) \"\n",
    "                     \"MERGE (c)-[:REGISTERED_IN]->(country);\"), {\"list\": input_data})\n",
    "    \n",
    "    # Connect Person with Country of citizenship\n",
    "    with driver.session() as session:\n",
    "        session.run((\"UNWIND {list} AS d \"\n",
    "                     \"MATCH (c:Person {uid: d.uid}) \"\n",
    "                     \"MERGE (country:Country {code: d.Citizen_of}) \"\n",
    "                     \"MERGE (c)-[:CITIZEN_OF]->(country);\"), {\"list\": input_data})\n",
    "        \n",
    "    # Connect person to control_entity\n",
    "    with driver.session() as session:\n",
    "        session.run((\"UNWIND {list} AS d \"\n",
    "                     \"MATCH (p:Person {uid: d.uid}) \"\n",
    "                     \"MERGE (ce:ControllingEntity {type: d.control_kind}) \"\n",
    "                     \"MERGE (p)-[:HAS_CONTROL_KIND]->(ce);\"), {\"list\": input_data})\n",
    "        \n",
    "    # Connect Person to Company it controls\n",
    "    with driver.session() as session:\n",
    "        session.run((\"UNWIND {list} AS d \"\n",
    "                     \"MATCH (p:Person {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",
    "    # Connect Person to Postcode it controls    \n",
    "    with driver.session() as session:\n",
    "        session.run((\"UNWIND {list} AS d \"\n",
    "                     \"MATCH (p:Person {uid: d.uid}) \"\n",
    "                     \"MERGE (pc:Postcode {uid: d.address_PostCode}) \"\n",
    "                     \"MERGE (p)-[: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": null,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Processed 100000 of 4165996 ....  2.40% complete\n",
      "Processed 200000 of 4165996 ....  4.80% complete\n",
      "Processed 300000 of 4165996 ....  7.20% complete\n",
      "Processed 400000 of 4165996 ....  9.60% complete\n",
      "Processed 500000 of 4165996 .... 12.00% complete\n",
      "Processed 600000 of 4165996 .... 14.40% complete\n",
      "Processed 700000 of 4165996 .... 16.80% complete\n",
      "Processed 800000 of 4165996 .... 19.20% complete\n",
      "Processed 900000 of 4165996 .... 21.60% complete\n",
      "Processed 2600000 of 4165996 .... 62.41% complete\n",
      "Processed 2700000 of 4165996 .... 64.81% complete\n",
      "Processed 2800000 of 4165996 .... 67.21% complete\n",
      "Processed 2900000 of 4165996 .... 69.61% complete\n",
      "Processed 3000000 of 4165996 .... 72.01% complete\n",
      "Processed 3100000 of 4165996 .... 74.41% complete\n",
      "Processed 3200000 of 4165996 .... 76.81% complete\n",
      "Processed 3300000 of 4165996 .... 79.21% complete\n",
      "Processed 3400000 of 4165996 .... 81.61% complete\n",
      "Processed 3500000 of 4165996 .... 84.01% complete\n",
      "Processed 3600000 of 4165996 .... 86.41% complete\n",
      "Processed 3700000 of 4165996 .... 88.81% complete\n",
      "Processed 3800000 of 4165996 .... 91.21% complete\n",
      "Processed 3900000 of 4165996 .... 93.62% complete\n",
      "Processed 4000000 of 4165996 .... 96.02% complete\n",
      "Processed 4100000 of 4165996 .... 98.42% complete\n"
     ]
    },
    {
     "ename": "ClientError",
     "evalue": "Cannot merge node using null property value for uid",
     "output_type": "error",
     "traceback": [
      "\u001b[0;31m\u001b[0m",
      "\u001b[0;31mClientError\u001b[0mTraceback (most recent call last)",
      "\u001b[0;32m<ipython-input-140-e322379fb00f>\u001b[0m in \u001b[0;36m<module>\u001b[0;34m()\u001b[0m\n\u001b[1;32m     11\u001b[0m     \u001b[0;32mdel\u001b[0m \u001b[0mneo_records_df\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m     12\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m---> 13\u001b[0;31m     \u001b[0minsert_individual_PSC_nodes_and_relationships\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0minput_data\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mdriver\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m     14\u001b[0m     \u001b[0mproc_records\u001b[0m \u001b[0;34m+=\u001b[0m \u001b[0mlen\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0minput_data\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m     15\u001b[0m     \u001b[0mprint\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m\"Processed {} of {} .... {:5.2f}% complete\"\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mformat\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mproc_records\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mtotal_records\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0;36m100\u001b[0m\u001b[0;34m*\u001b[0m\u001b[0mproc_records\u001b[0m\u001b[0;34m/\u001b[0m\u001b[0mtotal_records\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m<ipython-input-127-03466d4f99b7>\u001b[0m in \u001b[0;36minsert_individual_PSC_nodes_and_relationships\u001b[0;34m(input_data, driver)\u001b[0m\n\u001b[1;32m     51\u001b[0m                      \u001b[0;34m\"MATCH (p:Person {uid: d.uid}) \"\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m     52\u001b[0m                      \u001b[0;34m\"MERGE (c:Company {uid: d.company_id}) \"\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m---> 53\u001b[0;31m                      \"MERGE (p)-[r:CONTROLS]->(c) ON CREATE SET r=d.natures_of_control;\"), {\"list\": input_data})\n\u001b[0m",
      "\u001b[0;32m/home/ec2-user/anaconda3/lib/python3.6/site-packages/neo4j/v1/api.py\u001b[0m in \u001b[0;36mrun\u001b[0;34m(self, statement, parameters, **kwparameters)\u001b[0m\n\u001b[1;32m    338\u001b[0m         \u001b[0;32mif\u001b[0m \u001b[0;32mnot\u001b[0m \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mhas_transaction\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    339\u001b[0m             \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_connection\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0msend\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m--> 340\u001b[0;31m             \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_connection\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mfetch\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m    341\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    342\u001b[0m         \u001b[0;32mreturn\u001b[0m \u001b[0mresult\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m/home/ec2-user/anaconda3/lib/python3.6/site-packages/neo4j/bolt/connection.py\u001b[0m in \u001b[0;36mfetch\u001b[0;34m(self)\u001b[0m\n\u001b[1;32m    285\u001b[0m             \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_last_run_statement\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0;32mNone\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    286\u001b[0m             \u001b[0mlog_info\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m\"S: FAILURE (%r)\"\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0msummary_metadata\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m--> 287\u001b[0;31m             \u001b[0mresponse\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mon_failure\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0msummary_metadata\u001b[0m \u001b[0;32mor\u001b[0m \u001b[0;34m{\u001b[0m\u001b[0;34m}\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m    288\u001b[0m         \u001b[0;32melse\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    289\u001b[0m             \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_last_run_statement\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0;32mNone\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m/home/ec2-user/anaconda3/lib/python3.6/site-packages/neo4j/v1/result.py\u001b[0m in \u001b[0;36mon_failure\u001b[0;34m(metadata)\u001b[0m\n\u001b[1;32m     67\u001b[0m             \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_session\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_connection\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0macknowledge_failure\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m     68\u001b[0m             \u001b[0mon_footer\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mmetadata\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m---> 69\u001b[0;31m             \u001b[0;32mraise\u001b[0m \u001b[0mCypherError\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mhydrate\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m**\u001b[0m\u001b[0mmetadata\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m     70\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m     71\u001b[0m         \u001b[0mrun_response\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mon_success\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mon_header\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;31mClientError\u001b[0m: Cannot merge node using null property value for uid"
     ]
    }
   ],
   "source": [
    "import blaze as bz\n",
    "\n",
    "proc_records = 0\n",
    "total_records = human_psc.shape[0] - 59144\n",
    "for chunk in bz.odo(human_psc[human_psc.ceased_on.isnull()], target=bz.chunks(pd.DataFrame), chunksize=100000):\n",
    "    neo_records_df = chunk.apply(lambda s: pd.Series(new_record(s)), axis=1)\n",
    "    neo_records_df['Citizen_of'] = neo_records_df.nationality.apply(lambda x: nationality_map.get(x.lower(), 'UNKNOWN'))\n",
    "    neo_records_df['Registered_in'] = neo_records_df.address_Country.apply(lambda x: combined_map.get(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",
    "    insert_individual_PSC_nodes_and_relationships(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": []
  },
  {
   "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
}