Josh_data_challenge_2.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Key Insights:"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Despite the enormous number of numbers in the data, relatively few named individuals are the actual beneficiaries of offshore accounts. Just 13,800 unique names appear as beneficiaries for entities (out of 2160,000 named officers), of which I estimate 10-20% are themselves shell companies. "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Although China has the most beneficiaries in absolute terms, the top offenders for their populations are Peru, Ecuador, the UK, Guatemala, Argentina, the Dominican Republic, and Russia."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Relative to their populations, most wealthy countries have few beneficiaries of offshore accounts. Britain is a striking exception. Most of the named beneficiaries appear to be real people, and a large percentage have Anglophone names, so neither shell companies nor notional residency can fully explain this discrepancy. It's not clear why Brits make such frequent use of offshore accounts. It may be that the particular companies whose information was leaked are tend to serve more Brits (and Peruvians, etc). Or it may be that the UK has an unusual tax evasion problem."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "import os\n",
    "import plotly\n",
    "plotly.offline.init_notebook_mode() "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "path = 'offshore_leaks_csvs-20160621/'\n",
    "files = os.listdir(path)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 87,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "tables = {}\n",
    "for name in files:\n",
    "    if name[-4:] == '.csv':\n",
    "        tables[name[:-4]] = pd.DataFrame.from_csv(path+name, index_col=False)\n",
    "locals().update(tables)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 88,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "for key, v in tables.items():\n",
    "    if key == 'all_edges':\n",
    "        pass\n",
    "    else:\n",
    "        v.set_index('node_id', inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 91,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "benef_edges = all_edges[all_edges.rel_type.str.contains('benef')]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 244,
   "metadata": {
    "collapsed": false,
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Number of unique beneficiaries in the data: 13112\n"
     ]
    }
   ],
   "source": [
    "print \"Number of unique beneficiaries in the data:\",len(benef_edges.node_1.unique())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 249,
   "metadata": {
    "collapsed": false,
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "beneficiaries = Officers.loc[benef_edges.node_1.unique()]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 246,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "country_beneficiaries = beneficiaries.groupby(['country_codes']).count()['name']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 202,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "population = pd.DataFrame.from_csv('population.csv', index_col=None)\n",
    "population = population[population.Year == 2014]\n",
    "population.set_index('Country Code', inplace=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "df = pd.DataFrame(country_beneficiaries)\n",
    "df = df.merge(population, how='right', left_index=True, right_index=True)\n",
    "df['normalized'] = df.name/df.Value*10**6\n",
    "df['normalized'].fillna(0, inplace=True)\n",
    "df['normalized'][df.Value < 10**7] = 0"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 243,
   "metadata": {
    "collapsed": false,
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Most Offshore Beneficiaries:\n",
      "Country Code\n",
      "ARG    7.750617\n",
      "DOM    6.078477\n",
      "ECU    9.197530\n",
      "GTM    7.818552\n",
      "PER    9.360047\n",
      "RUS    5.986668\n",
      "GBR    8.448253\n",
      "Name: normalized, dtype: float64\n"
     ]
    }
   ],
   "source": [
    "print 'Most Offshore Beneficiaries:'\n",
    "print df['normalized'][df['normalized']>5]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 188,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "import plotly.offline as ply"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 241,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div id=\"c4396ee2-aa3e-4e94-8e3c-3ddac4d48577\" style=\"height: 525px; width: 100%;\" class=\"plotly-graph-div\"></div><script type=\"text/javascript\">require([\"plotly\"], function(Plotly) { window.PLOTLYENV=window.PLOTLYENV || {};window.PLOTLYENV.BASE_URL=\"https://plot.ly\";Plotly.newPlot(\"c4396ee2-aa3e-4e94-8e3c-3ddac4d48577\", [{\"reversescale\": true, \"autocolorscale\": false, \"colorscale\": [[0, \"rgb(5, 10, 172)\"], [0.5, \"rgb(40, 60, 190)\"], [1.0, \"rgb(70, 100, 245)\"], [1.5, \"rgb(90, 120, 245)\"], [3.0, \"rgb(106, 137, 247)\"], [5.0, \"rgb(220, 220, 220)\"]], \"text\": [\"ARB\", \"CSS\", \"CEB\", \"EAS\", \"EAP\", \"EMU\", \"ECS\", \"ECA\", \"EUU\", \"FCS\", \"HPC\", \"HIC\", \"NOC\", \"OEC\", \"LCN\", \"LAC\", \"LDC\", \"LMY\", \"LIC\", \"LMC\", \"MEA\", \"MNA\", \"MIC\", \"NAC\", \"OED\", \"OSS\", \"PSS\", \"SST\", \"SAS\", \"SSF\", \"SSA\", \"UMC\", \"WLD\", \"AFG\", \"ALB\", \"DZA\", \"ASM\", \"AND\", \"AGO\", \"ATG\", \"ARG\", \"ARM\", \"ABW\", \"AUS\", \"AUT\", \"AZE\", \"BHS\", \"BHR\", \"BGD\", \"BRB\", \"BLR\", \"BEL\", \"BLZ\", \"BEN\", \"BMU\", \"BTN\", \"BOL\", \"BIH\", \"BWA\", \"BRA\", \"BRN\", \"BGR\", \"BFA\", \"BDI\", \"CPV\", \"KHM\", \"CMR\", \"CAN\", \"CYM\", \"CAF\", \"TCD\", \"CHI\", \"CHL\", \"CHN\", \"COL\", \"COM\", \"COD\", \"COG\", \"CRI\", \"CIV\", \"HRV\", \"CUB\", \"CUW\", \"CYP\", \"CZE\", \"DNK\", \"DJI\", \"DMA\", \"DOM\", \"ECU\", \"EGY\", \"SLV\", \"GNQ\", \"ERI\", \"EST\", \"ETH\", \"FRO\", \"FJI\", \"FIN\", \"FRA\", \"PYF\", \"GAB\", \"GMB\", \"GEO\", \"DEU\", \"GHA\", \"GRC\", \"GRL\", \"GRD\", \"GUM\", \"GTM\", \"GIN\", \"GNB\", \"GUY\", \"HTI\", \"HND\", \"HKG\", \"HUN\", \"ISL\", \"IND\", \"IDN\", \"IRN\", \"IRQ\", \"IRL\", \"IMN\", \"ISR\", \"ITA\", \"JAM\", \"JPN\", \"JOR\", \"KAZ\", \"KEN\", \"KIR\", \"PRK\", \"KOR\", null, \"KWT\", \"KGZ\", \"LAO\", \"LVA\", \"LBN\", \"LSO\", \"LBR\", \"LBY\", \"LIE\", \"LTU\", \"LUX\", \"MAC\", \"MKD\", \"MDG\", \"MWI\", \"MYS\", \"MDV\", \"MLI\", \"MLT\", \"MHL\", \"MRT\", \"MUS\", \"MEX\", \"FSM\", \"MDA\", \"MCO\", \"MNG\", \"MNE\", \"MAR\", \"MOZ\", \"MMR\", \"NAM\", \"NPL\", \"NLD\", \"NCL\", \"NZL\", \"NIC\", \"NER\", \"NGA\", \"MNP\", \"NOR\", \"OMN\", \"PAK\", \"PLW\", \"PAN\", \"PNG\", \"PRY\", \"PER\", \"PHL\", \"POL\", \"PRT\", \"PRI\", \"QAT\", \"ROU\", \"RUS\", \"RWA\", \"WSM\", \"SMR\", \"STP\", \"SAU\", \"SEN\", \"SRB\", \"SYC\", \"SLE\", \"SGP\", \"SXM\", \"SVK\", \"SVN\", \"SLB\", \"SOM\", \"ZAF\", \"SSD\", \"ESP\", \"LKA\", \"KNA\", \"LCA\", \"MAF\", \"VCT\", \"SDN\", \"SUR\", \"SWZ\", \"SWE\", \"CHE\", \"SYR\", \"TJK\", \"TZA\", \"THA\", \"TLS\", \"TGO\", \"TON\", \"TTO\", \"TUN\", \"TUR\", \"TKM\", \"TCA\", \"TUV\", \"UGA\", \"UKR\", \"ARE\", \"GBR\", \"USA\", \"URY\", \"UZB\", \"VUT\", \"VEN\", \"VNM\", \"VIR\", \"PSE\", \"YEM\", \"ZMB\", \"ZWE\"], \"locations\": [\"ARB\", \"CSS\", \"CEB\", \"EAS\", \"EAP\", \"EMU\", \"ECS\", \"ECA\", \"EUU\", \"FCS\", \"HPC\", \"HIC\", \"NOC\", \"OEC\", \"LCN\", \"LAC\", \"LDC\", \"LMY\", \"LIC\", \"LMC\", \"MEA\", \"MNA\", \"MIC\", \"NAC\", \"OED\", \"OSS\", \"PSS\", \"SST\", \"SAS\", \"SSF\", \"SSA\", \"UMC\", \"WLD\", \"AFG\", \"ALB\", \"DZA\", \"ASM\", \"AND\", \"AGO\", \"ATG\", \"ARG\", \"ARM\", \"ABW\", \"AUS\", \"AUT\", \"AZE\", \"BHS\", \"BHR\", \"BGD\", \"BRB\", \"BLR\", \"BEL\", \"BLZ\", \"BEN\", \"BMU\", \"BTN\", \"BOL\", \"BIH\", \"BWA\", \"BRA\", \"BRN\", \"BGR\", \"BFA\", \"BDI\", \"CPV\", \"KHM\", \"CMR\", \"CAN\", \"CYM\", \"CAF\", \"TCD\", \"CHI\", \"CHL\", \"CHN\", \"COL\", \"COM\", \"COD\", \"COG\", \"CRI\", \"CIV\", \"HRV\", \"CUB\", \"CUW\", \"CYP\", \"CZE\", \"DNK\", \"DJI\", \"DMA\", \"DOM\", \"ECU\", \"EGY\", \"SLV\", \"GNQ\", \"ERI\", \"EST\", \"ETH\", \"FRO\", \"FJI\", \"FIN\", \"FRA\", \"PYF\", \"GAB\", \"GMB\", \"GEO\", \"DEU\", \"GHA\", \"GRC\", \"GRL\", \"GRD\", \"GUM\", \"GTM\", \"GIN\", \"GNB\", \"GUY\", \"HTI\", \"HND\", \"HKG\", \"HUN\", \"ISL\", \"IND\", \"IDN\", \"IRN\", \"IRQ\", \"IRL\", \"IMN\", \"ISR\", \"ITA\", \"JAM\", \"JPN\", \"JOR\", \"KAZ\", \"KEN\", \"KIR\", \"PRK\", \"KOR\", null, \"KWT\", \"KGZ\", \"LAO\", \"LVA\", \"LBN\", \"LSO\", \"LBR\", \"LBY\", \"LIE\", \"LTU\", \"LUX\", \"MAC\", \"MKD\", \"MDG\", \"MWI\", \"MYS\", \"MDV\", \"MLI\", \"MLT\", \"MHL\", \"MRT\", \"MUS\", \"MEX\", \"FSM\", \"MDA\", \"MCO\", \"MNG\", \"MNE\", \"MAR\", \"MOZ\", \"MMR\", \"NAM\", \"NPL\", \"NLD\", \"NCL\", \"NZL\", \"NIC\", \"NER\", \"NGA\", \"MNP\", \"NOR\", \"OMN\", \"PAK\", \"PLW\", \"PAN\", \"PNG\", \"PRY\", \"PER\", \"PHL\", \"POL\", \"PRT\", \"PRI\", \"QAT\", \"ROU\", \"RUS\", \"RWA\", \"WSM\", \"SMR\", \"STP\", \"SAU\", \"SEN\", \"SRB\", \"SYC\", \"SLE\", \"SGP\", \"SXM\", \"SVK\", \"SVN\", \"SLB\", \"SOM\", \"ZAF\", \"SSD\", \"ESP\", \"LKA\", \"KNA\", \"LCA\", \"MAF\", \"VCT\", \"SDN\", \"SUR\", \"SWZ\", \"SWE\", \"CHE\", \"SYR\", \"TJK\", \"TZA\", \"THA\", \"TLS\", \"TGO\", \"TON\", \"TTO\", \"TUN\", \"TUR\", \"TKM\", \"TCA\", \"TUV\", \"UGA\", \"UKR\", \"ARE\", \"GBR\", \"USA\", \"URY\", \"UZB\", \"VUT\", \"VEN\", \"VNM\", \"VIR\", \"PSE\", \"YEM\", \"ZMB\", \"ZWE\"], \"colorbar\": {\"tickprefix\": \"\", \"autotick\": false, \"title\": \"Number of Beneficiaries per Million Residents\"}, \"marker\": {\"line\": {\"color\": \"rgb(0,0,0)\", \"width\": 0.5}}, \"z\": [0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.7679360152098311, 0.0, 7.750616730208567, 0.0, 0.0, 2.8521881987861084, 0.0, 0.0, 0.0, 0.0, 0.006308647951389596, 0.0, 0.0, 2.9398121566934132, 0.0, 0.0943439838256674, 0.0, 0.0, 0.7374859693294336, 0.0, 0.0, 1.4403539766416162, 0.0, 0.0, 0.0, 0.0, 0.0, 0.12980042535599387, 0.0, 2.02586244129536, 0.0, 0.0, 0.0, 0.0, 1.7442314187730277, 1.7137370168661628, 4.843683303553878, 0.0, 0.11534005752412359, 0.0, 0.0, 0.048065891030587504, 0.0, 0.5329260830634581, 0.0, 0.0, 2.5688435808309467, 0.0, 0.0, 0.0, 6.078476551421917, 9.197530481836099, 0.23984629018769996, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 1.6162808727584395, 0.0, 0.0, 0.0, 0.0, 0.33378866640363297, 0.3781836730695936, 4.836763785233087, 0.0, 0.0, 0.0, 7.818552087383165, 0.08302959722840562, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.031560629354896896, 0.23733027646663238, 0.14018056429099945, 0.14586460427341283, 0.0, 0.0, 0.0, 2.4292268796334544, 0.0, 0.35396336715125565, 0.0, 2.5449544513885067, 0.3952050213871784, 0.0, 0.0, 0.13882290669186897, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.04242328237250679, 0.23768291482917966, 2.583817037133028, 0.0, 0.06341867097676866, 0.0, 0.0, 0.0, 0.0, 0.3634918040473843, 0.0, 0.0, 0.0, 0.0, 0.0, 0.29857066162870477, 0.3777436893478206, 0.0, 0.0, 0.0, 1.4239788425223578, 0.0, 0.0, 0.0, 0.0, 0.1288393394947069, 0.0, 0.0, 0.0, 0.1404396320079768, 0.0, 0.0, 0.0, 0.0, 9.360046776833768, 0.41959510750506196, 0.4474210636730443, 3.1738725274691455, 0.0, 0.0, 0.6529055931157635, 5.986667919996339, 0.0, 0.0, 0.0, 0.0, 2.11103873047851, 0.20621148871566053, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0925441697126809, 2.3517667962860527, 0.0, 1.9179132276578947, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.17166838234909126, 0.0, 0.059104613570194676, 0.5652829511911494, 0.0, 0.0, 0.0, 0.0, 0.45468599385264535, 1.9647396482614954, 0.0, 0.0, 0.0, 0.0, 2.2705779392410976, 0.0, 8.448253347647517, 0.6460575236572466, 0.0, 0.4553956249491746, 0.0, 2.65790698317412, 0.08817370219332084, 0.0, 0.0, 0.0, 0.1997203648598143, 4.315268000404128], \"type\": \"choropleth\"}], {\"geo\": {\"projection\": {\"type\": \"Mercator\"}, \"showcoastlines\": false, \"showframe\": false}, \"title\": \"Panama Papers Offshore Beneficiaries\"}, {\"linkText\": \"Export to plot.ly\", \"showLink\": true})});</script>"
      ],
      "text/plain": [
       "<IPython.core.display.HTML object>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "\n",
    "data = [ dict(\n",
    "        type = 'choropleth',\n",
    "        locations = df.index.values,\n",
    "        z = df['normalized'],\n",
    "        text = df.index.values,\n",
    "        colorscale = [[0,\"rgb(5, 10, 172)\"],[.5,\"rgb(40, 60, 190)\"],[1.0,\"rgb(70, 100, 245)\"],\\\n",
    "            [1.5,\"rgb(90, 120, 245)\"],[3.0,\"rgb(106, 137, 247)\"],[5.0,\"rgb(220, 220, 220)\"]],\n",
    "        autocolorscale = False,\n",
    "        reversescale = True,\n",
    "        marker = dict(\n",
    "            line = dict (\n",
    "                color = 'rgb(0,0,0)',\n",
    "                width = 0.5\n",
    "            ) ),\n",
    "        colorbar = dict(\n",
    "            autotick = False,\n",
    "            tickprefix = '',\n",
    "            title = 'Number of Beneficiaries per Million Residents'),\n",
    "      ) ]\n",
    "layout = dict(\n",
    "    title = 'Panama Papers Offshore Beneficiaries',\n",
    "    geo = dict(\n",
    "        showframe = False,\n",
    "        showcoastlines = False,\n",
    "        projection = dict(\n",
    "            type = 'Mercator'\n",
    "        )\n",
    "    )\n",
    ")\n",
    "\n",
    "fig = dict( data=data, layout=layout )\n",
    "ply.offline.iplot( fig, validate=False, filename='d3-world-map' )"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 2",
   "language": "python",
   "name": "python2"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 2
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython2",
   "version": "2.7.11+"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 0
}