companies_we_keep_neo4j_load.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import py2neo\n",
    "from py2neo import Graph\n",
    "import pandas as pd\n",
    "import numpy as np\n",
    "import os\n",
    "import dropbox\n",
    "import config\n",
    "from titlecase import titlecase\n",
    "dbx = dropbox.Dropbox(config.dropbox_api_key)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "##Define helper functions\n",
    "def get_cypher_from_columns(columns_list):\n",
    "    cypher = []\n",
    "    for name in columns_list[1:]:\n",
    "        cypher.append('n.' + name + ' = line.' + name)\n",
    "    cypher = ', '.join(cypher)\n",
    "    return cypher\n",
    "def get_label(x):\n",
    "    if 'company' in x:\n",
    "        return 'Company'\n",
    "    elif 'companies' in x:\n",
    "        return 'Company'\n",
    "    elif 'human' in x:\n",
    "        return 'Person'\n",
    "    elif 'super_secure' in x:\n",
    "        return 'SuperSecurePerson'\n",
    "    elif 'legal'in x:\n",
    "        return 'LegalPerson'\n",
    "    elif 'exemption' in x:\n",
    "        return 'Exemption'\n",
    "    elif 'statement' in x:\n",
    "        return 'Statement'\n",
    "    elif 'address' in x:\n",
    "        return 'Postcode'\n",
    "    elif 'politician' in x:\n",
    "        return 'Legislature'\n",
    "def create_target_company_uid(x):\n",
    "    uk_identifiers = ['Companies House','England', 'Wales', 'Companies House', 'United Kingdom', 'Scotland']\n",
    "    if any(identifier in x['identification.place_registered'] for identifier in uk_identifiers) and x['identification.registration_number'] != '':\n",
    "        return x['identification.registration_number'].upper()\n",
    "    elif any(identifier in x['identification.place_registered'] for identifier in uk_identifiers) and x['identification.registration_number'] == '':\n",
    "        return x['name'] + '_' + x['identification.place_registered'].replace(' ','_')\n",
    "    else:\n",
    "        return x['name'] + '_' + x['identification.place_registered'].replace(' ','_') + '_' + x['identification.registration_number'].upper()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "##Load in data from pre-prepped CSV files. See Github repo for initial transformation steps\n",
    "all_records_psc = pd.read_csv('data/outputs/all_records_psc.csv')\n",
    "active_companies = pd.read_csv('data/outputs/active_companies.csv')\n",
    "psc_records = pd.read_csv('data/outputs/psc_records.csv')\n",
    "psc_statements = pd.read_csv('data/outputs/psc_statements.csv')\n",
    "psc_controls = pd.read_csv('data/outputs/psc_controls.csv')\n",
    "active_officers = pd.read_csv('data/outputs/active_officers.csv',parse_dates=['partial_date_of_birth'],dtype={'company_number': str})\n",
    "#Make sure only officers that appear in the active_companies dataset are used\n",
    "active_officers = active_officers[active_officers.company_number.isin(active_companies.CompanyNumber.unique())].copy()\n",
    "secret_jurisdictions = pd.read_csv('data/outputs/secret_jurisdictions.csv',header=None)[0].tolist()\n",
    "##Create DataFrames for active PSC records, statements and controls\n",
    "active_psc_all_records = all_records_psc[pd.isnull(all_records_psc.ceased_on)].copy()\n",
    "active_psc_records = psc_records[pd.isnull(psc_records.ceased_on)].copy()\n",
    "active_psc_statements = psc_statements[pd.isnull(psc_statements.ceased_on)].copy()\n",
    "active_psc_controls = psc_controls[psc_controls.company_number.isin(all_records_psc[pd.isnull(all_records_psc.ceased_on)].company_number)].copy()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "##Create CSV files for nodes and edges that will be loaded into Neo4J\n",
    "##Create company nodes for filing companies\n",
    "active_filing_company_psc = pd.merge(active_companies,active_psc_all_records,\\\n",
    "                                   left_on='CompanyNumber',right_on='company_number',how='right')\n",
    "active_filing_company_psc['uid'] = active_filing_company_psc.company_number\n",
    "active_filing_company_psc.fillna('',inplace=True)\n",
    "active_filing_company_psc_nodes = active_filing_company_psc[['uid','company_number','RegAddress.AddressLine1', 'RegAddress.AddressLine2',\\\n",
    "       'RegAddress.PostTown', 'RegAddress.County', 'RegAddress.Country',\\\n",
    "       'RegAddress.PostCode', 'CompanyCategory',\\\n",
    "       'CountryOfOrigin', 'DissolutionDate', 'IncorporationDate', 'CompanyName']].drop_duplicates()\n",
    "active_filing_company_psc_nodes.columns = ['uid','company_number','address_line_1','address_line_2','town','county',\\\n",
    "                                          'country', 'postcode', 'company_category', 'country_of_origin', 'dissolution_date',\\\n",
    "                                          'incorporation_date', 'name']\n",
    "active_filing_company_psc_nodes.to_csv('data/neo4j/imports/active_filing_company_psc_nodes.csv')\n",
    "print('Created active company nodes CSV')\n",
    "##Create company nodes for target companies\n",
    "active_target_company_psc = active_psc_records[active_psc_records.kind == 'corporate-entity-person-with-significant-control'].copy()\n",
    "active_target_company_psc = active_target_company_psc[['company_number','address.address_line_1', 'address.address_line_2', 'address.country','address.postal_code',\\\n",
    "       'exemptions_count', 'generated_at', 'identification.country_registered',\\\n",
    "       'identification.legal_authority', 'identification.legal_form',\\\n",
    "       'identification.place_registered', 'identification.registration_number', 'name', 'natures_of_control','secret_base']].copy()\n",
    "active_target_company_psc.fillna('',inplace=True)\n",
    "active_target_company_psc['uid'] = active_target_company_psc.apply(create_target_company_uid,axis=1)\n",
    "active_target_company_psc_nodes = active_target_company_psc.drop_duplicates()\n",
    "active_target_company_psc_nodes.columns = ['company_number_of_filing_company','address_line_1','address_line_2','country','postcode',\\\n",
    "       'exemptions_count', 'generated_at', 'country_registered',\\\n",
    "       'legal_authority', 'legal_form',\\\n",
    "       'place_registered', 'company_number', 'name', 'natures_of_control','secret_base','uid']\n",
    "active_target_company_psc_nodes.to_csv('data/neo4j/imports/active_target_company_psc_nodes.csv')\n",
    "print('Created target company nodes CSV')\n",
    "##Create indivdual nodes\n",
    "active_human_psc = active_psc_records[active_psc_records.kind == 'individual-person-with-significant-control'].copy()\n",
    "active_human_psc.fillna('',inplace=True)\n",
    "active_human_psc['uid'] = active_human_psc['name_elements.forename'] + '_' + active_human_psc['name_elements.surname'] + '_' + active_human_psc['month_year_birth'].astype(str) + '_' +\\\n",
    "                 active_human_psc['address.postal_code']\n",
    "active_human_psc_nodes = active_human_psc[['uid','name','address.address_line_1', 'address.address_line_2',\\\n",
    "       'address.care_of', 'address.country', 'address.locality',\\\n",
    "       'address.po_box', 'address.postal_code','nationality','month_year_birth','country_of_residence_normal',\\\n",
    "                                                      'address_country_normal','secret_base','join_id']].copy()\n",
    "active_human_psc_nodes.drop_duplicates(inplace=True)\n",
    "active_human_psc_nodes.columns = ['uid','name', 'address_line_1', 'address_line_2',\\\n",
    "       'care_of', 'country', 'locality',\\\n",
    "       'po_box', 'post_code','nationality','month_year_birth','country_of_residence_normal',\\\n",
    "                                                      'address_country_normal','secret_base','join_id']\n",
    "active_human_psc_nodes.to_csv('data/neo4j/imports/active_human_psc_nodes.csv')\n",
    "print('Created human nodes CSV')\n",
    "##Create legal person nodes\n",
    "active_legal_psc = active_psc_records[active_psc_records.kind == 'legal-person-person-with-significant-control'].copy()\n",
    "active_legal_psc['uid'] = active_legal_psc['name'].copy()\n",
    "active_legal_psc_nodes = active_legal_psc[['name','address.address_line_1', 'address.address_line_2',\\\n",
    "       'address.care_of', 'address.country', 'address.locality',\\\n",
    "       'address.po_box', 'address.postal_code','nationality','month_year_birth','country_of_residence_normal',\\\n",
    "                                                      'address_country_normal','uid']].copy()\n",
    "active_legal_psc_nodes.drop_duplicates(inplace=True)\n",
    "active_legal_psc_nodes.columns  = ['name','address_line_1', 'address_line_2',\\\n",
    "       'care_of', 'country', 'address.locality',\\\n",
    "       'po_box', 'post_code','nationality','month_year_birth','country_of_residence_normal',\\\n",
    "                                                      'address_country_normal','uid']\n",
    "active_legal_psc_nodes.to_csv('data/neo4j/imports/active_legal_psc_nodes.csv')\n",
    "##Create super secure node\n",
    "active_super_secure_psc = active_psc_records[active_psc_records.kind == 'super-secure-person-with-significant-control'].copy()\n",
    "active_super_secure_psc['uid'] = active_super_secure_psc['etag'].copy()\n",
    "active_super_secure_psc.fillna('',inplace=True)\n",
    "active_super_secure_psc_nodes = active_super_secure_psc[['uid']].copy()\n",
    "active_super_secure_psc_nodes.drop_duplicates(inplace=True)\n",
    "active_super_secure_psc_nodes.to_csv('data/neo4j/imports/active_super_secure_psc_nodes.csv')\n",
    "##Create exemptions nodes\n",
    "active_exemptions_psc = active_psc_records[active_psc_records.kind == 'exemptions'].copy()\n",
    "active_exemptions_psc['uid'] = active_exemptions_psc['etag'].copy()\n",
    "active_exemptions_psc.fillna('',inplace=True)\n",
    "active_exemptions_psc_nodes = active_exemptions_psc[['uid']].drop_duplicates()\n",
    "active_exemptions_psc_nodes.to_csv('data/neo4j/imports/active_exemptions_psc_nodes.csv')\n",
    "##Create statement nodes\n",
    "active_psc_statements['uid'] = active_psc_statements['etag']\n",
    "active_psc_statements.drop_duplicates(inplace=True)\n",
    "active_psc_statements_nodes = active_psc_statements[['statement','uid']].drop_duplicates()\n",
    "active_psc_statements_nodes.to_csv('data/neo4j/imports/active_psc_statement_nodes.csv')\n",
    "print('Created statement nodes CSV')\n",
    "##Create address nodes\n",
    "active_addresses = active_companies.melt(id_vars=['RegAddress.PostCode'],value_vars=['CompanyNumber'])\n",
    "active_addresses['uid'] = active_addresses['RegAddress.PostCode'].copy()\n",
    "active_addresses.dropna(subset=['uid'],inplace=True)\n",
    "active_address_nodes = active_addresses[['RegAddress.PostCode','uid']].copy()\n",
    "active_address_nodes.columns = ['postcode','uid']\n",
    "active_address_nodes.drop_duplicates(inplace=True)\n",
    "active_address_nodes.to_csv('data/neo4j/imports/active_address_nodes.csv')\n",
    "print('Created postcode nodes')\n",
    "##Create human edges\n",
    "human_edges = active_human_psc[['company_number','uid','natures_of_control']].copy()\n",
    "human_edges.fillna('',inplace=True)\n",
    "human_edges.to_csv('data/neo4j/imports/psc_human_edges.csv')\n",
    "print('Created human edges')\n",
    "##Create company edges\n",
    "company_edges = active_target_company_psc[['company_number','uid','natures_of_control']].copy()\n",
    "company_edges.fillna('',inplace=True)\n",
    "company_edges.to_csv('data/neo4j/imports/psc_company_edges.csv')\n",
    "print('Created company edges')\n",
    "##Create super secure person edges\n",
    "super_secure_edges = active_super_secure_psc[['company_number','uid','natures_of_control']].copy()\n",
    "super_secure_edges.fillna('',inplace=True)\n",
    "print('Created super secure person edges')\n",
    "super_secure_edges.to_csv('data/neo4j/imports/super_secure_edges.csv')\n",
    "##Create legal persons edges\n",
    "legal_person_edges = active_legal_psc[['company_number','uid','natures_of_control']].copy()\n",
    "legal_person_edges.fillna('',inplace=True)\n",
    "legal_person_edges.to_csv('data/neo4j/imports/legal_person_edges.csv')\n",
    "print('Created legal person edges')\n",
    "##Create exemptions edges\n",
    "exemptions_edges = active_exemptions_psc[['company_number','uid','natures_of_control']].copy()\n",
    "exemptions_edges.fillna('',inplace=True)\n",
    "exemptions_edges.to_csv('data/neo4j/imports/exemption_edges.csv')\n",
    "print('Created exemptions edges')\n",
    "##Statement edges\n",
    "statement_edges = active_psc_statements[['company_number','uid']].copy()\n",
    "statement_edges.fillna('',inplace=True)\n",
    "statement_edges.to_csv('data/neo4j/imports/statement_edges.csv')\n",
    "##Create address edges\n",
    "active_addresses_edges = active_addresses[['value','uid']].drop_duplicates()\n",
    "active_addresses_edges.to_csv('data/neo4j/imports/address_edges.csv')\n",
    "print('Created address edges and nodes')\n",
    "##Human officers\n",
    "active_officers_humans = active_officers[active_officers.type == 'Person'].copy()\n",
    "active_officers_humans.fillna('',inplace=True)\n",
    "single_first_series = active_officers_humans['first_name'].apply(lambda x: x.split(' ')[0])\n",
    "active_officers_humans['address.in_full'] = active_officers_humans['address.in_full'].str.replace('\\\\','')\n",
    "active_officers_humans['address.street_address'] = active_officers_humans['address.street_address'].str.replace('\\\\','')\n",
    "active_officers_humans['address.postal_code'] = active_officers_humans['address.postal_code'].str.replace('\\\\','')\n",
    "active_officers_humans\n",
    "active_officers_humans['uid'] = single_first_series.str.title() + '_' + active_officers_humans['last_name'].str.title() + '_' +\\\n",
    "active_officers_humans['partial_date_of_birth'] + '-01_'\n",
    "active_officers_humans_nodes = active_officers_humans[['name', 'title',\n",
    "       'first_name', 'last_name','person_number',\n",
    "       'person_uid', 'current_status', 'occupation', 'nationality',\n",
    "       'country_of_residence', 'partial_date_of_birth', 'type',\n",
    "       'address.in_full', 'address.street_address', 'address.locality',\n",
    "       'address.region', 'address.postal_code', 'address.country',\n",
    "       'retrieved_at', 'source_url', 'country_of_residence_normal',\n",
    "       'address_country_normal', 'secret_base', 'uid', 'join_id']]\n",
    "active_officers_humans_nodes.columns = [x.replace('.','_') for x in active_officers_humans_nodes.columns] \n",
    "##Create edges table\n",
    "active_officers_humans_edges = active_officers_humans[['uid','company_number', 'position', 'start_date', 'end_date']]\n",
    "##Export\n",
    "active_officers_humans_nodes.to_csv('data/neo4j/imports/active_officers_humans_nodes.csv')\n",
    "active_officers_humans_edges.to_csv('data/neo4j/imports/active_officers_humans_edges.csv')\n",
    "print('Created human officers nodes and edges')\n",
    "##Company officers\n",
    "active_officers_companies = active_officers[active_officers.type == 'Company'].copy()\n",
    "active_officers_companies.fillna('',inplace=True)\n",
    "active_officers_companies['uid'] = active_officers_companies['name'] + active_officers_companies['address.postal_code']\n",
    "##Get company IDs from active_company file using name and postcode\n",
    "active_companies['fake_id'] = active_companies['CompanyName'] + '_' + active_companies['RegAddress.PostCode']\n",
    "fake_id_map = pd.Series(active_companies['CompanyNumber'].values,index=active_companies['fake_id'])\n",
    "fake_id_map = fake_id_map.drop_duplicates(keep=False).to_dict()\n",
    "active_officers_companies['fake_id'] = active_officers_companies['name'] + '_' + active_officers_companies['address.postal_code']\n",
    "active_officers_companies['uid'] = active_officers_companies.apply(lambda x: fake_id_map[x['fake_id']] if x['fake_id'] in fake_id_map.keys() else titlecase(x['name']) + '_' + titlecase(x['address_country_normal'].replace(' ','_')),axis=1)\n",
    "##Create nodes\n",
    "active_officers_companies_nodes = active_officers_companies[['name','nationality',\n",
    "       'country_of_residence', 'type',\n",
    "       'address.in_full', 'address.street_address', 'address.locality',\n",
    "       'address.region', 'address.postal_code', 'address.country',\n",
    "       'retrieved_at', 'source_url', 'country_of_residence_normal',\n",
    "       'address_country_normal', 'secret_base', 'uid']].drop_duplicates(subset=['uid'])\n",
    "active_officers_companies_nodes.columns = [x.replace('.','_') for x in active_officers_companies_nodes.columns] \n",
    "##Create edges\n",
    "active_officers_companies_edges = active_officers_companies[['company_number','uid','start_date', 'end_date','position']]\n",
    "##Export\n",
    "active_officers_companies_nodes.to_csv('data/neo4j/imports/active_officers_companies_nodes.csv')\n",
    "active_officers_companies_edges.to_csv('data/neo4j/imports/active_officers_companies_edges.csv')\n",
    "print('Created corporate officers edges and nodes')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "##Create edges between likely connected edges\n",
    "active_human_psc['probable_id'] = active_human_psc['name_elements.forename'] + '_' + active_human_psc['name_elements.surname'] + '_' + active_human_psc['month_year_birth'].astype(str) + '_'\n",
    "probable_id_edges = active_human_psc[['uid','probable_id']]\n",
    "probable_id_edges = probable_id_edges.dropna()\n",
    "probable_id_edges = probable_id_edges[~(probable_id_edges.uid == probable_id_edges.probable_id)]\n",
    "probable_id_edges.to_csv('data/neo4j/imports/probable_id_edges.csv')\n",
    "print(\"Created probable match edges\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "##Create legislatures and links\n",
    "every_politician = pd.read_csv('data/outputs/every_politician.csv')\n",
    "##Create DataFrame with only ones that occur in either officers or PSCs\n",
    "every_politician_merged = every_politician[(every_politician.join_id.isin(active_officers.join_id))|(every_politician.join_id.isin(active_psc_records.join_id))].copy()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "every_politician_merged = every_politican_merged.dropna(axis=1).copy()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "every_politician_nodes = pd.DataFrame(every_politician_merged.country.unique(),columns=['country'])\n",
    "every_politician_nodes['uid'] = every_politician_nodes.country\n",
    "every_politician_nodes.to_csv('data/neo4j/imports/every_politician_nodes.csv')\n",
    "print(\"Created every_politician nodes\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "every_politician_edges = every_politician_merged[['join_id','country']].copy()\n",
    "every_politician_edges.to_csv('data/neo4j/imports/every_politician_edges.csv',index=False)\n",
    "print(\"Created every_politician edges\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "##Chunk upload files to Dropbox \n",
    "files = []\n",
    "for file in os.listdir(\"data/neo4j/imports/\"):\n",
    "    if file.endswith(\".csv\"):\n",
    "        files.append(os.path.join(\"data/neo4j/imports/\", file))\n",
    "for item in files:\n",
    "    print(item)\n",
    "    with open(item, 'rb') as f:\n",
    "        file_size = os.path.getsize(item)\n",
    "        CHUNK_SIZE = 8 * 1024 * 1024\n",
    "        if file_size <= CHUNK_SIZE:\n",
    "            print(dbx.files_upload(f.read(), item.replace('data/neo4j/imports','')))\n",
    "        else:\n",
    "            upload_session_start_result = dbx.files_upload_session_start(f.read(CHUNK_SIZE))\n",
    "            cursor = dropbox.files.UploadSessionCursor(session_id=upload_session_start_result.session_id,\n",
    "                                                       offset=f.tell())\n",
    "            commit = dropbox.files.CommitInfo(path=item.replace('data/neo4j/imports',''),mode=dropbox.files.WriteMode.overwrite)\n",
    "            while f.tell() < file_size:\n",
    "                if ((file_size - f.tell()) <= CHUNK_SIZE):\n",
    "                    print(dbx.files_upload_session_finish(f.read(CHUNK_SIZE),\n",
    "                                                    cursor,\n",
    "                                                    commit))\n",
    "                else:\n",
    "                    dbx.files_upload_session_append(f.read(CHUNK_SIZE),\n",
    "                                                    cursor.session_id,\n",
    "                                                    cursor.offset)\n",
    "                    cursor.offset = f.tell()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "##Connect to graph\n",
    "graph = Graph(config.neo4j_auth_graph_url)\n",
    "graph.run(\"CREATE CONSTRAINT ON (company:Company) ASSERT company.uid IS UNIQUE\")\n",
    "graph.run(\"CREATE CONSTRAINT ON (person:Person) ASSERT person.uid IS UNIQUE\")\n",
    "graph.run(\"CREATE CONSTRAINT ON (legal_person:LegalPerson) ASSERT legal_person.uid IS UNIQUE\")\n",
    "graph.run(\"CREATE CONSTRAINT ON (super_secure_person:SuperSecurePerson) ASSERT super_secure_person.uid IS UNIQUE\")\n",
    "graph.run(\"CREATE CONSTRAINT ON (exemption:Exemption) ASSERT exemption.uid IS UNIQUE\")\n",
    "graph.run(\"CREATE CONSTRAINT ON (statement:Statement) ASSERT statement.uid IS UNIQUE\")\n",
    "graph.run(\"CREATE CONSTRAINT ON (postcode:Postcode) ASSERT postcode.uid IS UNIQUE\")\n",
    "graph.run(\"CREATE CONSTRAINT ON (legislature:Legislature) ASSERT legislature.uid IS UNIQUE\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "##Create shared links and get list of them\n",
    "shared_node_links = []\n",
    "shared_edges_links = []\n",
    "for entry in dbx.files_list_folder('').entries:\n",
    "    link = dbx.sharing_create_shared_link('/' + entry.name, short_url=False, pending_upload=None).url.replace('dl=0','dl=1')\n",
    "    if 'nodes' in link:\n",
    "        shared_node_links.append(link)\n",
    "    elif 'edges' in link:\n",
    "        shared_edges_links.append(link)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "##Create nodes in graph\n",
    "for file in shared_node_links:\n",
    "    cypher = get_cypher_from_columns(pd.read_csv(file,nrows=1).columns)\n",
    "    q = \"USING PERIODIC COMMIT 100000 LOAD CSV WITH HEADERS FROM {} AS line MERGE ({}) ON CREATE SET {} ON MATCH SET {}\".format(\"'\" + file + \"'\", 'n:' + get_label(file) + ' {uid: line.uid}', cypher, cypher) \n",
    "    print(q)\n",
    "    graph.run(q)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "##Create edges in graph\n",
    "for file in shared_edges_links:\n",
    "    #Need to make explicit re: PSC when officers re-isnerted\n",
    "    if 'human' in file and 'psc' in file: \n",
    "        q = \"USING PERIODIC COMMIT LOAD CSV WITH HEADERS FROM {} AS line MATCH (n:Person {}),(c:Company {}) MERGE (n)-[r:CONTROLS]->(c) ON CREATE SET r.natures_of_control = line.natures_of_control\".format(\"'\" + file + \"'\",'{uid: line.uid}','{uid: line.company_number}')\n",
    "        print(q)\n",
    "        graph.run(q)\n",
    "    #Need to make explicit re: PSC when officers re-isnerted    \n",
    "    elif 'company' in file and 'psc' in file:\n",
    "        q = \"USING PERIODIC COMMIT LOAD CSV WITH HEADERS FROM {} AS line MATCH (n:Company {}),(c:Company {}) MERGE (n)-[r:CONTROLS]->(c) ON CREATE SET r.natures_of_control = line.natures_of_control\".format(\"'\" + file + \"'\",'{uid: line.uid}','{uid: line.company_number}')\n",
    "        print(q)\n",
    "        graph.run(q)\n",
    "    elif 'super_secure' in file:\n",
    "        q = \"USING PERIODIC COMMIT LOAD CSV WITH HEADERS FROM {} AS line MATCH (n:SuperSecurePerson {}),(c:Company {}) MERGE (n)-[r:CONTROLS]->(c) ON CREATE SET r.natures_of_control = line.natures_of_control\".format(\"'\" + file + \"'\",'{uid: line.uid}','{uid: line.company_number}')\n",
    "        print(q)\n",
    "        graph.run(q)\n",
    "    elif 'exemption' in file:\n",
    "        q = \"USING PERIODIC COMMIT LOAD CSV WITH HEADERS FROM {} AS line MATCH (n:Exemption {}),(c:Company {}) MERGE (n)<-[r:IS_EXEMPT]-(c) ON CREATE SET r.natures_of_control = line.natures_of_control\".format(\"'\" + file + \"'\",'{uid: line.uid}','{uid: line.company_number}')\n",
    "        print(q)\n",
    "        graph.run(q)\n",
    "    elif 'legal_person' in file:     \n",
    "        q = \"USING PERIODIC COMMIT LOAD CSV WITH HEADERS FROM {} AS line MATCH (n:LegalPerson {}),(c:Company {}) MERGE (n)-[r:CONTROLS]->(c) ON CREATE SET r.natures_of_control = line.natures_of_control\".format(\"'\" + file + \"'\",'{uid: line.uid}','{uid: line.company_number}')\n",
    "        print(q)\n",
    "        graph.run(q)\n",
    "    elif 'address_edges' in file:\n",
    "        q = \"USING PERIODIC COMMIT LOAD CSV WITH HEADERS FROM {} AS line MATCH (n:Postcode {}),(c:Company {}) MERGE (c)-[r:REGISTERED]->(n)\".format(\"'\" + file + \"'\",'{uid: line.uid}','{uid: line.value}')\n",
    "        print(q)\n",
    "        graph.run(q)\n",
    "    elif 'statement_edges' in file:\n",
    "        q = \"USING PERIODIC COMMIT LOAD CSV WITH HEADERS FROM {} AS line MATCH (n:Statement {}),(c:Company {}) MERGE (c)-[r:STATES]->(n)\".format(\"'\" + file + \"'\",'{uid: line.uid}','{uid: line.company_number}')\n",
    "        print(q)\n",
    "        graph.run(q)\n",
    "    elif 'officers' and 'companies' in file:\n",
    "        q = 'USING PERIODIC COMMIT LOAD CSV WITH HEADERS FROM {} AS line MATCH (n:Company {}),(c:Company {}) MERGE (n)-[r:OFFICER_OF]->(c) ON CREATE SET r.position = line.position, r.start_date = line.start_date, r.end_date = line.end_date'.format(\"'\" + file + \"'\",'{uid: line.uid}','{uid: line.company_number}')\n",
    "        print(q)\n",
    "        graph.run(q)\n",
    "    elif 'officers' in file and 'humans' in file:\n",
    "        q = 'USING PERIODIC COMMIT LOAD CSV WITH HEADERS FROM {} AS line MATCH (n:Person {}),(c:Company {}) MERGE (n)-[r:OFFICER_OF]->(c) ON CREATE SET r.position = line.position, r.start_date = line.start_date, r.end_date = line.end_date'.format(\"'\" + file + \"'\",'{uid: line.uid}','{uid: line.company_number}')\n",
    "        print(q)\n",
    "        graph.run(q)\n",
    "    elif 'probable' in file and 'id' in file:\n",
    "        q = 'USING PERIODIC COMMIT LOAD CSV WITH HEADERS FROM {} AS line MATCH (n1:Person {}),(n2:Person {}) MERGE (n1)-[r:PROBABLY_SAME_PERSON]->(n2)'.format(\"'\" + file + \"'\",'{uid: line.uid}','{uid: line.probable_id}')\n",
    "        print(q)\n",
    "        graph.run(q)\n",
    "        graph.run('MATCH (p:Person)-[r:PROBABLY_SAME_PERSON]->(p:Person) DELETE r')\n",
    "        print('MATCH (p:Person)-[r:PROBABLY_SAME_PERSON]->(p:Person) DELETE r')\n",
    "    elif 'politician' in file:\n",
    "        q = 'USING PERIODIC COMMIT LOAD CSV WITH HEADERS FROM {} AS line MATCH (n1:Person {}),(n2:Legislature {}) MERGE (n1)-[r:POLITICIAN_IN]->(n2)'.format(\"'\" + file + \"'\",'{join_id: line.join_id}','{uid: line.country}')\n",
    "        print(q)\n",
    "        graph.run(q)\n",
    "    else:\n",
    "        print(\"Couldn't find the right query for \" + file)\n",
    "        continue"
   ]
  }
 ],
 "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.5.2"
  },
  "toc": {
   "nav_menu": {},
   "number_sections": true,
   "sideBar": true,
   "skip_h1_title": false,
   "title_cell": "Table of Contents",
   "title_sidebar": "Contents",
   "toc_cell": false,
   "toc_position": {},
   "toc_section_display": true,
   "toc_window_display": false
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}