companies_we_keep_transform_and_load.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {
    "toc": true
   },
   "source": [
    "<h1>Table of Contents<span class=\"tocSkip\"></span></h1>\n",
    "<div class=\"toc\"><ul class=\"toc-item\"><li><span><a href=\"#Import-libraries\" data-toc-modified-id=\"Import-libraries-1\"><span class=\"toc-item-num\">1&nbsp;&nbsp;</span>Import libraries</a></span></li><li><span><a href=\"#Define-helper-functions\" data-toc-modified-id=\"Define-helper-functions-2\"><span class=\"toc-item-num\">2&nbsp;&nbsp;</span>Define helper functions</a></span></li><li><span><a href=\"#Load-and-process-data\" data-toc-modified-id=\"Load-and-process-data-3\"><span class=\"toc-item-num\">3&nbsp;&nbsp;</span>Load and process data</a></span><ul class=\"toc-item\"><li><span><a href=\"#Financial-Serecy-Index\" data-toc-modified-id=\"Financial-Serecy-Index-3.1\"><span class=\"toc-item-num\">3.1&nbsp;&nbsp;</span>Financial Serecy Index</a></span></li><li><span><a href=\"#PSC-snapshot-data\" data-toc-modified-id=\"PSC-snapshot-data-3.2\"><span class=\"toc-item-num\">3.2&nbsp;&nbsp;</span>PSC snapshot data</a></span></li><li><span><a href=\"#Active-companies-data\" data-toc-modified-id=\"Active-companies-data-3.3\"><span class=\"toc-item-num\">3.3&nbsp;&nbsp;</span>Active companies data</a></span></li><li><span><a href=\"#Officers-data\" data-toc-modified-id=\"Officers-data-3.4\"><span class=\"toc-item-num\">3.4&nbsp;&nbsp;</span>Officers data</a></span></li><li><span><a href=\"#Politicans-data\" data-toc-modified-id=\"Politicans-data-3.5\"><span class=\"toc-item-num\">3.5&nbsp;&nbsp;</span>Politicans data</a></span></li><li><span><a href=\"#Disqualified-directors\" data-toc-modified-id=\"Disqualified-directors-3.6\"><span class=\"toc-item-num\">3.6&nbsp;&nbsp;</span>Disqualified directors</a></span></li></ul></li><li><span><a href=\"#Output-to-CSV-files-for-use-in-analysis-and-network-database\" data-toc-modified-id=\"Output-to-CSV-files-for-use-in-analysis-and-network-database-4\"><span class=\"toc-item-num\">4&nbsp;&nbsp;</span>Output to CSV files for use in analysis and network database</a></span></li></ul></div>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Import libraries"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "##Load in libraries\n",
    "import pandas as pd\n",
    "import numpy as np\n",
    "import json\n",
    "import re\n",
    "import datetime\n",
    "import requests as r\n",
    "from dateutil.relativedelta import relativedelta\n",
    "from pandas.io.json import json_normalize\n",
    "import seaborn as sns\n",
    "pd.options.display.float_format = '{:20,.2f}'.format\n",
    "pd.set_option('display.max_columns', 500)\n",
    "%matplotlib inline"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Define helper functions"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "def secret_function(x):\n",
    "    if x['country_of_residence_normal'] in secret_jurisdictions or x['address_country_normal'] in secret_jurisdictions or x['registered_country_normal'] in secret_jurisdictions:\n",
    "        return True\n",
    "    else:\n",
    "        return False\n",
    "def non_rle_function(x):\n",
    "    if x['kind'] == 'corporate-entity-person-with-significant-control' and ~pd.isnull(x['registered_country_normal']) and (x['address_country_normal'] not in rec_list and x['registered_country_normal'] not in rec_list):\n",
    "        return True\n",
    "    else:\n",
    "        return False\n",
    "def secret_officer_function(x):\n",
    "    if x['country_of_residence_normal'] in secret_jurisdictions or x['address_country_normal'] in secret_jurisdictions:\n",
    "        return True\n",
    "    else:\n",
    "        return False\n",
    "def date_check(date):\n",
    "    if re.match(('^\\d{4}-\\d{2}-\\d{2}$'), date): return 'full date'\n",
    "    if re.match(('^\\d{4}-\\d{2}$'), date): return 'year and month'\n",
    "    if re.match(('^\\d{4}$'), date): return 'year only'\n",
    "    return ('unknown format')\n",
    "def flatten_legislatures(legislatures):\n",
    "    if isinstance(legislatures, list):\n",
    "        return pd.Series({'legislatures.popolo_url': legis.get('popolo_url', 'blank') for legis in legislatures})\n",
    "    else:\n",
    "        return pd.Series({})\n",
    "def flatten_identifiers(identifiers):\n",
    "    if isinstance(identifiers, list):\n",
    "        return pd.Series({'identifier.{}'.format(ident.get('scheme', 'blank')): ident.get('identifier') for ident in identifiers})\n",
    "    else:\n",
    "        return pd.Series({})\n",
    "def flatten_identifiers(identifiers):\n",
    "    if isinstance(identifiers, list):\n",
    "        return pd.Series({'identifier.{}'.format(ident.get('scheme', 'blank')): ident.get('identifier') for ident in identifiers})\n",
    "    else:\n",
    "        return pd.Series({})\n",
    "def flatten_contacts(contact_details):\n",
    "    if isinstance(contact_details, list):\n",
    "        return pd.Series({'contact_details.{}'.format(contact.get('type', 'blank')): contact.get('value', '') for contact in contact_details})\n",
    "        #return #pd.Series({'contact_details.{}'.format(contact.get('type', 'blank')): contact.get('value', '')for contact in contact_details})\n",
    "    else:\n",
    "        return pd.Series({})\n",
    "def load_every_country_json(url):\n",
    "    \"\"\"Function that takes an everypolitician.org JSON input and gives a flat dataframe\"\"\"\n",
    "    json_data = r.get(url).json()\n",
    "    df_country = pd.DataFrame(json_data)\n",
    "\n",
    "    # Flatten the contact details\n",
    "    df2 = df_country.merge(df_country.legislatures.apply(flatten_legislatures),\n",
    "                   left_index=True,\n",
    "                   right_index=True).drop('legislatures', axis=1)\n",
    "    return df2\n",
    "def process_url(url):\n",
    "    res = r.get(url)\n",
    "    df_persons = pd.DataFrame(res.json().get('persons'))\n",
    "    \n",
    "    try:\n",
    "        # Flatten the contact details\n",
    "        df2 = df_persons.merge(df_persons.contact_details.apply(flatten_contacts), \n",
    "                       left_index=True, \n",
    "                       right_index=True).drop('contact_details', axis=1)\n",
    "    except:\n",
    "        pass\n",
    "    \n",
    "    try:\n",
    "        # Flatten the identifier details\n",
    "        df3 = df2.merge(df2.identifiers.apply(flatten_identifiers), \n",
    "                        left_index=True, \n",
    "                        right_index=True).drop('identifiers', axis=1)\n",
    "    except:\n",
    "        pass\n",
    "    \n",
    "    try:\n",
    "        df3['date_format'] = df3['birth_date'].astype(str).apply(date_check)\n",
    "    except:\n",
    "        return pd.DataFrame()\n",
    "\n",
    "    df_subset = df3[df3['date_format'].isin(['full date', 'year and month'])]\n",
    "    return df_subset"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Load and process data"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Financial Serecy Index"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "This data is sourced from the Tax Justice Initiative *Financial Secrecy Index 2018* which is available [here](https://www.financialsecrecyindex.com/)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "##Load data\n",
    "secrecy_index = pd.read_csv('data/original_data/financial_secrecy_index_2018.csv')\n",
    "secrecy_index['normalized_jurisdiction'] = secrecy_index.Jurisdiction.str.upper()\n",
    "##Secrecy jurisdictions are those with secrecy socres of 60 and above and not USA\n",
    "secret_jurisdictions_series = secrecy_index[(secrecy_index['Secrecy Score'] >= 60) & (secrecy_index.normalized_jurisdiction != 'USA')]['normalized_jurisdiction']\n",
    "secret_jurisdictions = secret_jurisdictions_series.tolist()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## PSC snapshot data"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "PSC data is accurate as of 2018-3-1. It is sourced from Companies House. The most recent version is available to download [here](http://download.companieshouse.gov.uk/en_pscdata.html)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {},
   "outputs": [],
   "source": [
    "original_psc_data = pd.read_json('data/original_data/persons-with-significant-control-snapshot-2018-03-01.txt',lines=True)\n",
    "all_records_psc = pd.concat([original_psc_data['company_number'],json_normalize(original_psc_data['data'])],axis=1)\n",
    "##Remove last line of DataFrame which is not a record\n",
    "all_records_psc = all_records_psc.iloc[:-1].copy()\n",
    "##Remove summary totals before of PSC not including statemnts\n",
    "all_records_psc = all_records_psc[all_records_psc.kind != 'totals#persons-of-significant-control-snapshot']\n",
    "##Create a DataFrame of ways of controlling companies. Other methods are inefficient, so best to use JSON normalize.\n",
    "temp_df = all_records_psc[['company_number','natures_of_control']].dropna(subset=['natures_of_control'])\n",
    "##Expensive operation, write to CSV to save time. Need to re-generate when PSC file updated\n",
    "list_of_lists = []\n",
    "for index,row in temp_df.iterrows():\n",
    "    for item in row['natures_of_control']:\n",
    "        list_of_lists.append([row['company_number'],item])\n",
    "psc_controls = pd.DataFrame(list_of_lists)\n",
    "psc_controls.columns = ['company_number','nature_of_control']\n",
    "##Create additional columns\n",
    "all_records_psc['month_year_birth'] = all_records_psc['date_of_birth.month'].fillna('').astype(str).str.replace('\\.0','') + '-' + all_records_psc['date_of_birth.year'].fillna('').astype(str).str.replace('\\.0','')\n",
    "all_records_psc['month_year_birth'] = pd.to_datetime(all_records_psc['month_year_birth'],format='%m-%Y',errors='coerce')\n",
    "all_records_psc['join_id'] = all_records_psc['name_elements.forename'].str.upper() + '_' + all_records_psc['name_elements.surname'].str.upper() + '_' + all_records_psc['month_year_birth'].astype(str)\n",
    "##Create DataFrames for statment records and PSC records. Records DF is for where a PSC has been filed, statements DF is for\n",
    "##statements that have been made by a company that do not involve actually disclosing a PSC e.g. no PSC.\n",
    "psc_records = all_records_psc[pd.isnull(all_records_psc.statement)].copy()\n",
    "psc_statements = all_records_psc[~ pd.isnull(all_records_psc.statement)].copy()\n",
    "##Add in new field to mark any company or individual as likely based or incorporated in a secrecy jurisdiction\n",
    "psc_records['address_country_normal'] = psc_records['address.country'].str.upper()\n",
    "psc_records['address_country_normal'] = psc_records['address_country_normal'].fillna('')\n",
    "psc_records['registered_country_normal'] = psc_records['identification.country_registered'].str.upper()\n",
    "psc_records['registered_country_normal'] = psc_records['registered_country_normal'].fillna('')\n",
    "psc_records['country_of_residence_normal'] = psc_records['country_of_residence'].str.upper()\n",
    "psc_records['country_of_residence_normal'] = psc_records['country_of_residence_normal'].fillna('')\n",
    "registered_country_clean_map = pd.read_csv('data/original_data/registered_country_cleaner_map.csv')\n",
    "registered_country_clean_map.drop_duplicates(inplace=True)\n",
    "registered_country_clean_map = pd.Series(registered_country_clean_map.clean.values,index=registered_country_clean_map.original)\n",
    "psc_records['registered_country_normal'] = psc_records['registered_country_normal'].map(registered_country_clean_map)\n",
    "address_country_clean_map = pd.read_csv('data/original_data/address_country_cleaner_map.csv')\n",
    "address_country_clean_map.drop_duplicates(inplace=True)\n",
    "address_country_clean_map = pd.Series(address_country_clean_map.clean.values,index=address_country_clean_map.original)\n",
    "psc_records['address_country_normal'] = psc_records['address_country_normal'].map(address_country_clean_map)\n",
    "psc_records['secret_base'] = psc_records.apply(secret_function,axis=1)\n",
    "rec = pd.read_csv('data/original_data/recognised_stock_exchange_countries.csv')\n",
    "rec_list = rec['country_name'].str.upper().tolist()\n",
    "rec_list.extend(['ENGLAND','SCOTLAND','NORTHERN IRELAND','GREAT BRITAIN','UK','WALES','UNITED STATES OF AMERICA','UNITED STATES','ENGLAND & WALES',\\\n",
    "                'REPUBLIC OF IRELAND','IRELAND','ENGLAND AND WALES'])\n",
    "psc_records['non_rle_country'] = psc_records.apply(non_rle_function,axis=1)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Active companies data"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Active companies data is accurate as of 2018-3-1. It is sourced from the Companies House, the most recent version is available [here](http://download.companieshouse.gov.uk/en_output.html) as the Free Company Data product."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/usr/local/lib/python3.5/dist-packages/IPython/core/interactiveshell.py:2728: DtypeWarning: Columns (1,2,3,7,8,27,28,29,54) have mixed types. Specify dtype option on import or set low_memory=False.\n",
      "  interactivity=interactivity, compiler=compiler, result=result)\n"
     ]
    }
   ],
   "source": [
    "##Load in Companies House Free data product\n",
    "active_companies = pd.read_csv('data/original_data/BasicCompanyDataAsOneFile-2018-03-01.csv')\n",
    "##Take leading whitespace out of column names\n",
    "active_companies.columns = [x.strip() for x in active_companies.columns]\n",
    "##Create column which combines first line of address and postcode for easier analysis\n",
    "active_companies['first_and_postcode'] = active_companies['RegAddress.AddressLine1'] + '-' + active_companies['RegAddress.PostCode']\n",
    "##Use Companies House 10 year file of dissolved and active companies from October 2017 (check date)\n",
    "all_companies = pd.read_csv('data/original_data/live_comps_and_dissolved_10yrs.csv')\n",
    "all_companies.columns = [x.strip() for x in all_companies.columns]\n",
    "all_companies['first_and_postcode'] = all_companies['RegAddress.AddressLine1'] + '-' + all_companies['RegAddress.PostCode']\n",
    "dissolved_companies = all_companies[~ pd.isnull(all_companies['DissolutionDate'])]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Officers data"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Officers data is accurate as of about March 2018 and sourced from OpenCorporates.com in bulk. Contact OpenCorporates for more information on how to acquire this data."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "all_officers = pd.read_csv('data/original_data/officers.csv',dtype={'id': int,'company_number': str,\\\n",
    "            'jurisdiction_code': str,'name': str ,'title': str, 'first_name': str , 'last_name': str,\\\n",
    "                'position': str, 'start_date': str, 'person_number': str, 'person_uid': str, 'end_date': str, 'current_status': str,\\\n",
    "                    'occupation': str, 'nationality': str, 'country_of_residence': str, 'partial_date_of_birth': str, 'type': str,\\\n",
    "                        'address.in_full': str, 'address.street_address': str, 'address.locality': str,\\\n",
    "                            'address.region,address.postal_code': str,'address.country': str, 'retrieved_at': str, 'source_url': str})\n",
    "all_officers['country_of_residence_normal'] = all_officers['country_of_residence'].str.upper()  \n",
    "all_officers['address_country_normal'] = all_officers['address.country'].str.upper()  \n",
    "all_officers['secret_base'] = all_officers.apply(secret_officer_function,axis=1)\n",
    "\n",
    "active_officers = (all_officers[pd.isnull(all_officers.end_date)]).copy()\n",
    "\n",
    "active_officers.first_name = active_officers.first_name.fillna('')\n",
    "active_officers['first_name_single'] = active_officers.first_name.apply(lambda x: x.split(' ')[0] if len(x) > 0 else '')\n",
    "\n",
    "active_officers['join_id'] =  active_officers.first_name_single.str.upper() + '_' + active_officers.last_name.str.upper() + '_' + active_officers.partial_date_of_birth.fillna('') + '-01'"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Politicans data"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Data on politicians is updated each time the script is run. The data is avaialble from [EveryPolitician.org](http://everypolitician.org/countries.html)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Last run: 2018-03-28 13:27:18\n"
     ]
    }
   ],
   "source": [
    "print('Last run: ' + datetime.datetime.now().strftime(\"%Y-%m-%d %H:%M:%S\"))\n",
    "df = load_every_country_json('https://raw.githubusercontent.com/everypolitician/everypolitician-data/master/countries.json')\n",
    "df_full = pd.DataFrame(columns=['birth_date'])\n",
    "for index, row in df.iterrows():\n",
    "    df_persons = process_url(row['legislatures.popolo_url'])\n",
    "    df_persons['country'] = row['country']\n",
    "    df_full = pd.concat([df_full, df_persons])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_full['birth_date'] = pd.to_datetime(df_full['birth_date'],format='%Y-%m-%d',exact=True,errors='coerce')\n",
    "df_full['year'] = df_full.birth_date.apply(lambda x: str(x.year))\n",
    "df_full['month'] = df_full.birth_date.apply(lambda x: str(x.month))\n",
    "df_full['month_year'] = df_full.birth_date.apply(lambda x: str(x.year)) + '-' + df_full.birth_date.apply(lambda x: str(x.month)) + '-01'\n",
    "df_full['first_name'] = df_full.name.apply(lambda x: x.split(' ')[0] if len(x.split(' ')) > 1 else '')\n",
    "df_full['last_name'] = df_full.name.apply(lambda x: x.split(' ')[-1] if len(x.split(' ')) > 1 else '')\n",
    "df_full['join_id'] = df_full.first_name.str.upper() + '_' + df_full.last_name.str.upper() + '_' + df_full['month_year']"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Disqualified directors"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "This data was sourced from [OpenSanctions.org](https://www.opensanctions.org/) on 2018-3-28."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "d_directors = pd.read_json('data/original_data/gb-coh-disqualified.ijson',lines=True)\n",
    "d_directors = d_directors.dropna(subset=['birth_dates'])\n",
    "d_directors['birth_date_formatted'] = pd.to_datetime(d_directors.birth_dates.apply(lambda x: x[0]['date']))\n",
    "d_directors['year'] = d_directors.birth_date_formatted.apply(lambda x: str(x.year))\n",
    "d_directors['month'] = d_directors.birth_date_formatted.apply(lambda x: str(x.month))\n",
    "d_directors['month_year'] = d_directors.birth_date_formatted.apply(lambda x: str(x.year)) + '-' + d_directors.birth_date_formatted.apply(lambda x: str(x.month)) + '-01'\n",
    "d_directors['join_id'] = d_directors.first_name.str.upper() + '_' + d_directors.last_name.str.upper() + '_' + d_directors['month_year']\n",
    "d_directors.to_csv('data/outputs/disqualified_directors.csv',index=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Output to CSV files for use in analysis and network database "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "active_companies.to_csv('data/outputs/active_companies.csv',index=False)\n",
    "psc_records.to_csv('data/outputs/psc_records.csv',index=False)\n",
    "psc_statements.to_csv('data/outputs/psc_statements.csv',index=False)\n",
    "psc_controls.to_csv('data/outputs/psc_controls.csv',index=False)\n",
    "secret_jurisdictions_series.to_csv('data/outputs/secret_jurisdictions.csv',index=False)\n",
    "active_officers.to_csv('data/outputs/active_officers.csv',index=False)\n",
    "dissolved_companies.to_csv('data/outputs/dissolved_companies.csv',index=False)\n",
    "df_full.to_csv('data/outputs/every_politician.csv',index=False)\n",
    "d_directors.to_csv('data/outputs/disqualified_directors.csv',index=False)"
   ]
  }
 ],
 "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": true,
   "toc_position": {
    "height": "calc(100% - 180px)",
    "left": "10px",
    "top": "150px",
    "width": "221px"
   },
   "toc_section_display": true,
   "toc_window_display": false
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}