03.Inserting-active-companies-into-neo4j.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Let's try and create the company nodes within neo4j\n",
    "\n",
    "Loop through the active company list and generate the company nodes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "from neo4j.v1 import GraphDatabase\n",
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "driver = GraphDatabase.driver(\"bolt://10.0.0.1:7687\", auth=(\"myusername\", \"mypassword\"))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Let's take a peek at the data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "company_sample = pd.read_csv('./data/BasicCompanyDataAsOneFile-2017-09-01.csv', nrows=1000)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "cols = [col.strip() for col in company_sample.columns]\n",
    "cols = [col.replace('.', '_') for col in cols]\n",
    "company_sample.columns = cols"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "CompanyName                                                           0131 TACFIT LTD\n",
       "CompanyNumber                                                                SC540913\n",
       "RegAddress_CareOf                                                                 NaN\n",
       "RegAddress_POBox                                                                  NaN\n",
       "RegAddress_AddressLine1                                  134B5 PORTOBELLO HIGH STREET\n",
       "RegAddress_AddressLine2                                                           NaN\n",
       "RegAddress_PostTown                                                         EDINBURGH\n",
       "RegAddress_County                                                                 NaN\n",
       "RegAddress_Country                                                           SCOTLAND\n",
       "RegAddress_PostCode                                                          EH15 1AH\n",
       "CompanyCategory                                               Private Limited Company\n",
       "CompanyStatus                                                                  Active\n",
       "CountryOfOrigin                                                        United Kingdom\n",
       "DissolutionDate                                                                   NaN\n",
       "IncorporationDate                                                          22/07/2016\n",
       "Accounts_AccountRefDay                                                             31\n",
       "Accounts_AccountRefMonth                                                            7\n",
       "Accounts_NextDueDate                                                       22/04/2018\n",
       "Accounts_LastMadeUpDate                                                           NaN\n",
       "Accounts_AccountCategory                                            NO ACCOUNTS FILED\n",
       "Returns_NextDueDate                                                        19/08/2017\n",
       "Returns_LastMadeUpDate                                                            NaN\n",
       "Mortgages_NumMortCharges                                                            0\n",
       "Mortgages_NumMortOutstanding                                                        0\n",
       "Mortgages_NumMortPartSatisfied                                                      0\n",
       "Mortgages_NumMortSatisfied                                                          0\n",
       "SICCode_SicText_1                                          93130 - Fitness facilities\n",
       "SICCode_SicText_2                                                                 NaN\n",
       "SICCode_SicText_3                                                                 NaN\n",
       "SICCode_SicText_4                                                                 NaN\n",
       "LimitedPartnerships_NumGenPartners                                                  0\n",
       "LimitedPartnerships_NumLimPartners                                                  0\n",
       "URI                                   http://business.data.gov.uk/id/company/SC540913\n",
       "PreviousName_1_CONDATE                                                            NaN\n",
       "PreviousName_1_CompanyName                                                        NaN\n",
       "PreviousName_2_CONDATE                                                            NaN\n",
       "PreviousName_2_CompanyName                                                        NaN\n",
       "PreviousName_3_CONDATE                                                            NaN\n",
       "PreviousName_3_CompanyName                                                        NaN\n",
       "PreviousName_4_CONDATE                                                            NaN\n",
       "PreviousName_4_CompanyName                                                        NaN\n",
       "PreviousName_5_CONDATE                                                            NaN\n",
       "PreviousName_5_CompanyName                                                        NaN\n",
       "PreviousName_6_CONDATE                                                            NaN\n",
       "PreviousName_6_CompanyName                                                        NaN\n",
       "PreviousName_7_CONDATE                                                            NaN\n",
       "PreviousName_7_CompanyName                                                        NaN\n",
       "PreviousName_8_CONDATE                                                            NaN\n",
       "PreviousName_8_CompanyName                                                        NaN\n",
       "PreviousName_9_CONDATE                                                            NaN\n",
       "PreviousName_9_CompanyName                                                        NaN\n",
       "PreviousName_10_CONDATE                                                           NaN\n",
       "PreviousName_10_CompanyName                                                       NaN\n",
       "ConfStmtNextDueDate                                                        04/08/2020\n",
       "ConfStmtLastMadeUpDate                                                            NaN\n",
       "Name: 998, dtype: object"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "company_sample.iloc[-2]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## We shall only worry about current company names.\n",
    "\n",
    "We will need out country references again."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "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')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [],
   "source": [
    "import numpy as np\n",
    "company_sample = company_sample.replace(np.nan, u'', regex=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "company_sample['CountryCode'] = company_sample.RegAddress_Country.map(lambda x: combined_map.get(str(x).upper(), 'UNKNOWN'))\n",
    "company_sample['CleanCountry'] = company_sample.apply(lambda x: country_code_map.get(x['CountryCode'], 'UNKNOWN') \n",
    "                                        if x['CountryCode'] in country_code_map.keys() \n",
    "                                        else x.RegAddress_Country, axis=1) \n",
    "company_sample['CleanCountry'] = company_sample.CleanCountry.map(lambda x: 'NO_COUNTRY_LISTED' if x == '' else x)\n",
    "\n",
    "company_sample['CountryOfOriginCode'] = company_sample.CountryOfOrigin.map(lambda x: combined_map.get(str(x).upper(), 'UNKNOWN'))\n",
    "company_sample['CleanCountryOfOrigin'] = company_sample.apply(lambda x: country_code_map.get(x['CountryOfOriginCode'], 'UNKNOWN') \n",
    "                                        if x['CountryOfOriginCode'] in country_code_map.keys() \n",
    "                                        else x.CountryOfOrigin, axis=1)\n",
    "company_sample['CleanCountryOfOrigin'] = company_sample.CleanCountryOfOrigin.map(lambda x: 'NO_COUNTRY_LISTED' if x == '' else x)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "1000"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "company_sample['CleanPostcode'] = company_sample.RegAddress_PostCode.map(lambda x: 'UNKNOWN' if x == '' else x)\n",
    "company_sample.CleanPostcode.value_counts().sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "False    1000\n",
       "Name: CompanyNumber, dtype: int64"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "company_sample.CompanyNumber.isnull().value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [],
   "source": [
    "input_data = [v for k,v in company_sample.T.to_dict().items()]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{'Accounts_AccountCategory': 'DORMANT',\n",
       " 'Accounts_AccountRefDay': 30.0,\n",
       " 'Accounts_AccountRefMonth': 9.0,\n",
       " 'Accounts_LastMadeUpDate': '30/09/2016',\n",
       " 'Accounts_NextDueDate': '30/06/2018',\n",
       " 'CleanCountry': 'NO_COUNTRY_LISTED',\n",
       " 'CleanCountryOfOrigin': 'UNITED KINGDOM OF GREAT BRITAIN AND NORTHERN IRELAND',\n",
       " 'CleanPostcode': 'LS10 2RU',\n",
       " 'CompanyCategory': 'Private Limited Company',\n",
       " 'CompanyName': '! LTD',\n",
       " 'CompanyNumber': '08209948',\n",
       " 'CompanyStatus': 'Active',\n",
       " 'ConfStmtLastMadeUpDate': '11/09/2016',\n",
       " 'ConfStmtNextDueDate': '25/09/2019',\n",
       " 'CountryCode': 'UNKNOWN',\n",
       " 'CountryOfOrigin': 'United Kingdom',\n",
       " 'CountryOfOriginCode': 'GB',\n",
       " 'DissolutionDate': '',\n",
       " 'IncorporationDate': '11/09/2012',\n",
       " 'LimitedPartnerships_NumGenPartners': 0,\n",
       " 'LimitedPartnerships_NumLimPartners': 0,\n",
       " 'Mortgages_NumMortCharges': 0,\n",
       " 'Mortgages_NumMortOutstanding': 0,\n",
       " 'Mortgages_NumMortPartSatisfied': 0,\n",
       " 'Mortgages_NumMortSatisfied': 0,\n",
       " 'PreviousName_10_CONDATE': '',\n",
       " 'PreviousName_10_CompanyName': '',\n",
       " 'PreviousName_1_CONDATE': '',\n",
       " 'PreviousName_1_CompanyName': '',\n",
       " 'PreviousName_2_CONDATE': '',\n",
       " 'PreviousName_2_CompanyName': '',\n",
       " 'PreviousName_3_CONDATE': '',\n",
       " 'PreviousName_3_CompanyName': '',\n",
       " 'PreviousName_4_CONDATE': '',\n",
       " 'PreviousName_4_CompanyName': '',\n",
       " 'PreviousName_5_CONDATE': '',\n",
       " 'PreviousName_5_CompanyName': '',\n",
       " 'PreviousName_6_CONDATE': '',\n",
       " 'PreviousName_6_CompanyName': '',\n",
       " 'PreviousName_7_CONDATE': '',\n",
       " 'PreviousName_7_CompanyName': '',\n",
       " 'PreviousName_8_CONDATE': '',\n",
       " 'PreviousName_8_CompanyName': '',\n",
       " 'PreviousName_9_CONDATE': '',\n",
       " 'PreviousName_9_CompanyName': '',\n",
       " 'RegAddress_AddressLine1': 'METROHOUSE 57 PEPPER ROAD',\n",
       " 'RegAddress_AddressLine2': 'HUNSLET',\n",
       " 'RegAddress_CareOf': '',\n",
       " 'RegAddress_Country': '',\n",
       " 'RegAddress_County': 'YORKSHIRE',\n",
       " 'RegAddress_POBox': '',\n",
       " 'RegAddress_PostCode': 'LS10 2RU',\n",
       " 'RegAddress_PostTown': 'LEEDS',\n",
       " 'Returns_LastMadeUpDate': '11/09/2015',\n",
       " 'Returns_NextDueDate': '09/10/2016',\n",
       " 'SICCode_SicText_1': '99999 - Dormant Company',\n",
       " 'SICCode_SicText_2': '',\n",
       " 'SICCode_SicText_3': '',\n",
       " 'SICCode_SicText_4': '',\n",
       " 'URI': 'http://business.data.gov.uk/id/company/08209948'}"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "input_data[0]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Company node properties:\n",
    "- name:\n",
    "- number:\n",
    "- Accounts.LastMadeUpDate:\n",
    "- Returns.LastMadeUpDate: '11/09/2015'\n",
    "- Returns.NextDueDate: '09/10/2016'\n",
    "- Address.Line1:\n",
    "- Address.Line2:\n",
    "- Address.Country\n",
    "- Address.PostCode\n",
    "- Address.PostTown\n",
    "- Address.POBox\n",
    "- Address.County\n",
    "- URI: 'http://business.data.gov.uk/id/company/08209948'\n",
    "\n",
    "status nodes:\n",
    "- type: DORMANT, Active\n",
    "\n",
    "company_category nodes:\n",
    "- type: PLC ...\n",
    "\n",
    "Country:\n",
    "- name: United Kingdom ..."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "```\n",
    "UNWIND {list} AS d\n",
    "MATCH (p:Person {user_id: d.id})\n",
    "MERGE (a:Artist {artist_name: d.name})\n",
    "MERGE (p)-[:LIKES {times: d.plays}]->(a)\n",
    "\n",
    " 'Accounts.LastMadeUpDate': '30/09/2016',\n",
    " 'Accounts.NextDueDate': '30/06/2018',\n",
    "Returns.LastMadeUpDate: '11/09/2015'\n",
    "Returns.NextDueDate:\n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Looping over all the data and inserting the data into the neo4j Database\n",
    "\n",
    "Here we will chunk over the input file in batches of 100,000 records and use the functions we've tested above to create the node properties and format to allow us to do a batch CYPHER query that will create and connect the nodes and relationships."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/home/ec2-user/anaconda3/lib/python3.6/site-packages/IPython/core/interactiveshell.py:2717: DtypeWarning: Columns (39,40,41,42,43,44,45,46) have mixed types. Specify dtype option on import or set low_memory=False.\n",
      "  interactivity=interactivity, compiler=compiler, result=result)\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Starting Insert ....\n",
      "Finished chunk...\n",
      "Starting Insert ....\n",
      "Finished chunk...\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/home/ec2-user/anaconda3/lib/python3.6/site-packages/IPython/core/interactiveshell.py:2717: DtypeWarning: Columns (3,41,42,43,44,45,46) have mixed types. Specify dtype option on import or set low_memory=False.\n",
      "  interactivity=interactivity, compiler=compiler, result=result)\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Starting Insert ....\n",
      "Finished chunk...\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/home/ec2-user/anaconda3/lib/python3.6/site-packages/IPython/core/interactiveshell.py:2717: DtypeWarning: Columns (3,45,46,47,48,49,50,51,52) have mixed types. Specify dtype option on import or set low_memory=False.\n",
      "  interactivity=interactivity, compiler=compiler, result=result)\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Starting Insert ....\n",
      "Finished chunk...\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/home/ec2-user/anaconda3/lib/python3.6/site-packages/IPython/core/interactiveshell.py:2717: DtypeWarning: Columns (39,40,41,42,43,44,45,46,47,48) have mixed types. Specify dtype option on import or set low_memory=False.\n",
      "  interactivity=interactivity, compiler=compiler, result=result)\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Starting Insert ....\n",
      "Finished chunk...\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/home/ec2-user/anaconda3/lib/python3.6/site-packages/IPython/core/interactiveshell.py:2717: DtypeWarning: Columns (3,41,42,43,44,45,46,47,48,49,50,51,52) have mixed types. Specify dtype option on import or set low_memory=False.\n",
      "  interactivity=interactivity, compiler=compiler, result=result)\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Starting Insert ....\n",
      "Finished chunk...\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/home/ec2-user/anaconda3/lib/python3.6/site-packages/IPython/core/interactiveshell.py:2717: DtypeWarning: Columns (3,41,42,43,44,45,46,47,48,49,50) have mixed types. Specify dtype option on import or set low_memory=False.\n",
      "  interactivity=interactivity, compiler=compiler, result=result)\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Starting Insert ....\n"
     ]
    }
   ],
   "source": [
    "chunks = pd.read_csv('./data/BasicCompanyDataAsOneFile-2017-09-01.csv', chunksize=100000)\n",
    "for chunk in chunks:\n",
    "    cols = [col.strip() for col in chunk.columns]\n",
    "    cols = [col.replace('.', '_') for col in cols]\n",
    "    chunk.columns = cols\n",
    "    chunk = chunk.replace(np.nan, u'', regex=True)\n",
    "    chunk['CountryCode'] = chunk.RegAddress_Country.map(lambda x: combined_map.get(str(x).upper(), 'UNKNOWN'))\n",
    "    chunk['CleanCountry'] = chunk.apply(lambda x: country_code_map.get(x['CountryCode'], 'UNKNOWN') \n",
    "                                            if x['CountryCode'] in country_code_map.keys() \n",
    "                                            else x.RegAddress_Country, axis=1) \n",
    "    chunk['CleanCountry'] = chunk.CleanCountry.map(lambda x: 'NO_COUNTRY_LISTED' if x == '' else x)\n",
    "\n",
    "    chunk['CountryOfOriginCode'] = chunk.CountryOfOrigin.map(lambda x: combined_map.get(str(x).upper(), 'UNKNOWN'))\n",
    "    chunk['CleanCountryOfOrigin'] = chunk.apply(lambda x: country_code_map.get(x['CountryOfOriginCode'], 'UNKNOWN') \n",
    "                                            if x['CountryOfOriginCode'] in country_code_map.keys() \n",
    "                                            else x.CountryOfOrigin, axis=1)\n",
    "    chunk['CleanCountryOfOrigin'] = chunk.CleanCountryOfOrigin.map(lambda x: 'NO_COUNTRY_LISTED' if x == '' else x)\n",
    "    chunk['CleanPostcode'] = chunk.RegAddress_PostCode.map(lambda x: 'UNKNOWN' if x == '' else x)\n",
    "    \n",
    "    test = {'list': [v for k,v in chunk.T.to_dict().items()]}\n",
    "\n",
    "    print('Starting Insert ....')\n",
    "    with driver.session() as session:\n",
    "        session.run((\"UNWIND {list} AS d \"\n",
    "                     \"MERGE (c:Company {uid: d.CompanyNumber}) \"\n",
    "                     \"ON CREATE SET c.name=d.CompanyName, \"\n",
    "                     \"c.accounts_LastMadeUpDate=d.Accounts_LastMadeUpDate, \"\n",
    "                     \"c.accounts_NextDueDate=d.Accounts_NextDueDate, \"\n",
    "                     \"c.returns_LastMadeUpDate=d.Returns_LastMadeUpDate, \"\n",
    "                     \"c.returns_NextDueDate=d.Returns_NextDueDate, \"\n",
    "                     \"c.address_Line1=d.RegAddress_Line1, \"\n",
    "                     \"c.address_Line2=d.RegAddress_Line2, \"\n",
    "                     \"c.address_PostTown=d.RegAddress_PostTown, \"\n",
    "                     \"c.address_POBox=d.RegAddress_POBox, \"\n",
    "                     \"c.address_County=d.RegAddress_County, \"\n",
    "                     \"c.address_PostCode=d.RegAddress_Postcode, \"                 \n",
    "                     \"c.address_Country=d.CleanCountry, \"                 \n",
    "                     \"c.uri=d.URI;\"), {\"list\": test.get('list')})\n",
    "\n",
    "    with driver.session() as session:\n",
    "        session.run((\"UNWIND {list} AS d \"\n",
    "                     \"MATCH (c:Company {uid: d.CompanyNumber}) \"\n",
    "                     \"MERGE (country:Country {code: d.CountryCode}) \"\n",
    "                     \"MERGE (c)-[:REGISTERED_IN]->(country);\"), {\"list\": test.get('list')})\n",
    "\n",
    "    with driver.session() as session:\n",
    "        session.run((\"UNWIND {list} AS d \"\n",
    "                     \"MATCH (c:Company {uid: d.CompanyNumber}) \"\n",
    "                     \"MERGE (country:Country {code: d.CountryOfOriginCode}) \"\n",
    "                     \"MERGE (c)-[:HAS_ORIGIN]->(country);\"), \n",
    "                    {\"list\": test.get('list')})\n",
    "        \n",
    "    with driver.session() as session:\n",
    "        session.run((\"UNWIND {list} AS d \"\n",
    "                     \"MATCH (c:Company {uid: d.CompanyNumber}) \"\n",
    "                     \"MERGE (pc:Postcode {uid: d.CleanPostcode}) \"\n",
    "                     \"MERGE (c)-[:REGISTERED_IN]->(pc);\"), \n",
    "                    {\"list\": test.get('list')})\n",
    "    print(\"Finished chunk...\")\n",
    "print(\"DONE!\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Quick check how any companies have been inserted"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "[{'COUNT(c)': 4077979}]\n"
     ]
    }
   ],
   "source": [
    "with driver.session() as session:\n",
    "    result = session.run(\"MATCH (c:Company) RETURN COUNT(c);\")\n",
    "    print(result.data())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.6.1"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}