Merging_referendum_population.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {
    "collapsed": false,
    "run_control": {
     "read_only": false
    }
   },
   "outputs": [],
   "source": [
    "import pymongo\n",
    "import datetime\n",
    "import collections\n",
    "\n",
    "import matplotlib as mpl\n",
    "import matplotlib.pyplot as plt\n",
    "%matplotlib inline\n",
    "\n",
    "import numpy as np\n",
    "import pandas as pd\n",
    "import scipy.stats\n",
    "from collections import Counter\n",
    "from sklearn import cluster"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Importing our datasets"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {
    "collapsed": false,
    "run_control": {
     "read_only": false
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>id</th>\n",
       "      <th>Region_Code</th>\n",
       "      <th>Region</th>\n",
       "      <th>Area_Code</th>\n",
       "      <th>Area</th>\n",
       "      <th>Electorate</th>\n",
       "      <th>ExpectedBallots</th>\n",
       "      <th>VerifiedBallotPapers</th>\n",
       "      <th>Pct_Turnout</th>\n",
       "      <th>Votes_Cast</th>\n",
       "      <th>...</th>\n",
       "      <th>Remain</th>\n",
       "      <th>Leave</th>\n",
       "      <th>Rejected_Ballots</th>\n",
       "      <th>No_official_mark</th>\n",
       "      <th>Voting_for_both_answers</th>\n",
       "      <th>Writing_or_mark</th>\n",
       "      <th>Unmarked_or_void</th>\n",
       "      <th>Pct_Remain</th>\n",
       "      <th>Pct_Leave</th>\n",
       "      <th>Pct_Rejected</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>108</td>\n",
       "      <td>E12000006</td>\n",
       "      <td>East</td>\n",
       "      <td>E06000031</td>\n",
       "      <td>Peterborough</td>\n",
       "      <td>120892</td>\n",
       "      <td>87474</td>\n",
       "      <td>87469</td>\n",
       "      <td>72.35</td>\n",
       "      <td>87469</td>\n",
       "      <td>...</td>\n",
       "      <td>34176</td>\n",
       "      <td>53216</td>\n",
       "      <td>77</td>\n",
       "      <td>0</td>\n",
       "      <td>32</td>\n",
       "      <td>7</td>\n",
       "      <td>38</td>\n",
       "      <td>39.11</td>\n",
       "      <td>60.89</td>\n",
       "      <td>0.09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>109</td>\n",
       "      <td>E12000006</td>\n",
       "      <td>East</td>\n",
       "      <td>E06000032</td>\n",
       "      <td>Luton</td>\n",
       "      <td>127612</td>\n",
       "      <td>84633</td>\n",
       "      <td>84636</td>\n",
       "      <td>66.31</td>\n",
       "      <td>84616</td>\n",
       "      <td>...</td>\n",
       "      <td>36708</td>\n",
       "      <td>47773</td>\n",
       "      <td>135</td>\n",
       "      <td>0</td>\n",
       "      <td>85</td>\n",
       "      <td>0</td>\n",
       "      <td>50</td>\n",
       "      <td>43.45</td>\n",
       "      <td>56.55</td>\n",
       "      <td>0.16</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>112</td>\n",
       "      <td>E12000006</td>\n",
       "      <td>East</td>\n",
       "      <td>E06000033</td>\n",
       "      <td>Southend-on-Sea</td>\n",
       "      <td>128856</td>\n",
       "      <td>93948</td>\n",
       "      <td>93939</td>\n",
       "      <td>72.90</td>\n",
       "      <td>93939</td>\n",
       "      <td>...</td>\n",
       "      <td>39348</td>\n",
       "      <td>54522</td>\n",
       "      <td>69</td>\n",
       "      <td>0</td>\n",
       "      <td>21</td>\n",
       "      <td>0</td>\n",
       "      <td>48</td>\n",
       "      <td>41.92</td>\n",
       "      <td>58.08</td>\n",
       "      <td>0.07</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>113</td>\n",
       "      <td>E12000006</td>\n",
       "      <td>East</td>\n",
       "      <td>E06000034</td>\n",
       "      <td>Thurrock</td>\n",
       "      <td>109897</td>\n",
       "      <td>79969</td>\n",
       "      <td>79954</td>\n",
       "      <td>72.75</td>\n",
       "      <td>79950</td>\n",
       "      <td>...</td>\n",
       "      <td>22151</td>\n",
       "      <td>57765</td>\n",
       "      <td>34</td>\n",
       "      <td>0</td>\n",
       "      <td>8</td>\n",
       "      <td>3</td>\n",
       "      <td>23</td>\n",
       "      <td>27.72</td>\n",
       "      <td>72.28</td>\n",
       "      <td>0.04</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>110</td>\n",
       "      <td>E12000006</td>\n",
       "      <td>East</td>\n",
       "      <td>E06000055</td>\n",
       "      <td>Bedford</td>\n",
       "      <td>119530</td>\n",
       "      <td>86136</td>\n",
       "      <td>86136</td>\n",
       "      <td>72.06</td>\n",
       "      <td>86135</td>\n",
       "      <td>...</td>\n",
       "      <td>41497</td>\n",
       "      <td>44569</td>\n",
       "      <td>69</td>\n",
       "      <td>0</td>\n",
       "      <td>26</td>\n",
       "      <td>1</td>\n",
       "      <td>42</td>\n",
       "      <td>48.22</td>\n",
       "      <td>51.78</td>\n",
       "      <td>0.08</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 21 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "    id Region_Code Region  Area_Code             Area  Electorate  \\\n",
       "0  108   E12000006   East  E06000031     Peterborough      120892   \n",
       "1  109   E12000006   East  E06000032            Luton      127612   \n",
       "2  112   E12000006   East  E06000033  Southend-on-Sea      128856   \n",
       "3  113   E12000006   East  E06000034         Thurrock      109897   \n",
       "4  110   E12000006   East  E06000055          Bedford      119530   \n",
       "\n",
       "   ExpectedBallots  VerifiedBallotPapers  Pct_Turnout  Votes_Cast  \\\n",
       "0            87474                 87469        72.35       87469   \n",
       "1            84633                 84636        66.31       84616   \n",
       "2            93948                 93939        72.90       93939   \n",
       "3            79969                 79954        72.75       79950   \n",
       "4            86136                 86136        72.06       86135   \n",
       "\n",
       "       ...       Remain  Leave  Rejected_Ballots  No_official_mark  \\\n",
       "0      ...        34176  53216                77                 0   \n",
       "1      ...        36708  47773               135                 0   \n",
       "2      ...        39348  54522                69                 0   \n",
       "3      ...        22151  57765                34                 0   \n",
       "4      ...        41497  44569                69                 0   \n",
       "\n",
       "   Voting_for_both_answers  Writing_or_mark  Unmarked_or_void  Pct_Remain  \\\n",
       "0                       32                7                38       39.11   \n",
       "1                       85                0                50       43.45   \n",
       "2                       21                0                48       41.92   \n",
       "3                        8                3                23       27.72   \n",
       "4                       26                1                42       48.22   \n",
       "\n",
       "   Pct_Leave  Pct_Rejected  \n",
       "0      60.89          0.09  \n",
       "1      56.55          0.16  \n",
       "2      58.08          0.07  \n",
       "3      72.28          0.04  \n",
       "4      51.78          0.08  \n",
       "\n",
       "[5 rows x 21 columns]"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "referendum_df = pd.read_csv(\"data/EU-referendum-result-data.csv\")\n",
    "referendum_df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {
    "collapsed": false,
    "run_control": {
     "read_only": false
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Area_Code</th>\n",
       "      <th>Area</th>\n",
       "      <th>country</th>\n",
       "      <th>population_2001</th>\n",
       "      <th>births_2002</th>\n",
       "      <th>deaths_2002</th>\n",
       "      <th>natchange_2002</th>\n",
       "      <th>internal_in_2002</th>\n",
       "      <th>internal_out_2002</th>\n",
       "      <th>internal_net_2002</th>\n",
       "      <th>...</th>\n",
       "      <th>internal_out_2015</th>\n",
       "      <th>internal_net_2015</th>\n",
       "      <th>international_in_2015</th>\n",
       "      <th>international_out_2015</th>\n",
       "      <th>international_net_2015</th>\n",
       "      <th>other_change_2015</th>\n",
       "      <th>population_2015</th>\n",
       "      <th>international_in_total</th>\n",
       "      <th>international_net_total</th>\n",
       "      <th>population_change</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>E06000001</td>\n",
       "      <td>Hartlepool</td>\n",
       "      <td>E</td>\n",
       "      <td>90152</td>\n",
       "      <td>1017</td>\n",
       "      <td>1042</td>\n",
       "      <td>-25</td>\n",
       "      <td>2140</td>\n",
       "      <td>2301</td>\n",
       "      <td>-161</td>\n",
       "      <td>...</td>\n",
       "      <td>2281</td>\n",
       "      <td>-144</td>\n",
       "      <td>248</td>\n",
       "      <td>135</td>\n",
       "      <td>113</td>\n",
       "      <td>15</td>\n",
       "      <td>92493</td>\n",
       "      <td>2524</td>\n",
       "      <td>546</td>\n",
       "      <td>2438</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>E06000002</td>\n",
       "      <td>Middlesbrough</td>\n",
       "      <td>E</td>\n",
       "      <td>141233</td>\n",
       "      <td>1663</td>\n",
       "      <td>1396</td>\n",
       "      <td>267</td>\n",
       "      <td>5109</td>\n",
       "      <td>6737</td>\n",
       "      <td>-1628</td>\n",
       "      <td>...</td>\n",
       "      <td>7071</td>\n",
       "      <td>-887</td>\n",
       "      <td>1250</td>\n",
       "      <td>450</td>\n",
       "      <td>800</td>\n",
       "      <td>12</td>\n",
       "      <td>139509</td>\n",
       "      <td>15883</td>\n",
       "      <td>8376</td>\n",
       "      <td>-2114</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>E06000003</td>\n",
       "      <td>Redcar and Cleveland</td>\n",
       "      <td>E</td>\n",
       "      <td>139159</td>\n",
       "      <td>1336</td>\n",
       "      <td>1523</td>\n",
       "      <td>-187</td>\n",
       "      <td>3951</td>\n",
       "      <td>4259</td>\n",
       "      <td>-308</td>\n",
       "      <td>...</td>\n",
       "      <td>4019</td>\n",
       "      <td>195</td>\n",
       "      <td>202</td>\n",
       "      <td>109</td>\n",
       "      <td>93</td>\n",
       "      <td>25</td>\n",
       "      <td>135275</td>\n",
       "      <td>3501</td>\n",
       "      <td>40</td>\n",
       "      <td>-4117</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>E06000004</td>\n",
       "      <td>Stockton-on-Tees</td>\n",
       "      <td>E</td>\n",
       "      <td>183795</td>\n",
       "      <td>1938</td>\n",
       "      <td>1719</td>\n",
       "      <td>219</td>\n",
       "      <td>6269</td>\n",
       "      <td>5531</td>\n",
       "      <td>738</td>\n",
       "      <td>...</td>\n",
       "      <td>6659</td>\n",
       "      <td>-217</td>\n",
       "      <td>994</td>\n",
       "      <td>557</td>\n",
       "      <td>437</td>\n",
       "      <td>-23</td>\n",
       "      <td>194803</td>\n",
       "      <td>9454</td>\n",
       "      <td>1953</td>\n",
       "      <td>10324</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>E06000005</td>\n",
       "      <td>Darlington</td>\n",
       "      <td>E</td>\n",
       "      <td>97894</td>\n",
       "      <td>1102</td>\n",
       "      <td>1173</td>\n",
       "      <td>-71</td>\n",
       "      <td>3815</td>\n",
       "      <td>3521</td>\n",
       "      <td>294</td>\n",
       "      <td>...</td>\n",
       "      <td>3779</td>\n",
       "      <td>-36</td>\n",
       "      <td>347</td>\n",
       "      <td>407</td>\n",
       "      <td>-60</td>\n",
       "      <td>19</td>\n",
       "      <td>105389</td>\n",
       "      <td>4671</td>\n",
       "      <td>304</td>\n",
       "      <td>7473</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 161 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "   Area_Code                  Area country  population_2001  births_2002  \\\n",
       "0  E06000001            Hartlepool       E            90152         1017   \n",
       "1  E06000002         Middlesbrough       E           141233         1663   \n",
       "2  E06000003  Redcar and Cleveland       E           139159         1336   \n",
       "3  E06000004      Stockton-on-Tees       E           183795         1938   \n",
       "4  E06000005            Darlington       E            97894         1102   \n",
       "\n",
       "   deaths_2002  natchange_2002  internal_in_2002  internal_out_2002  \\\n",
       "0         1042             -25              2140               2301   \n",
       "1         1396             267              5109               6737   \n",
       "2         1523            -187              3951               4259   \n",
       "3         1719             219              6269               5531   \n",
       "4         1173             -71              3815               3521   \n",
       "\n",
       "   internal_net_2002        ...          internal_out_2015  internal_net_2015  \\\n",
       "0               -161        ...                       2281               -144   \n",
       "1              -1628        ...                       7071               -887   \n",
       "2               -308        ...                       4019                195   \n",
       "3                738        ...                       6659               -217   \n",
       "4                294        ...                       3779                -36   \n",
       "\n",
       "   international_in_2015  international_out_2015  international_net_2015  \\\n",
       "0                    248                     135                     113   \n",
       "1                   1250                     450                     800   \n",
       "2                    202                     109                      93   \n",
       "3                    994                     557                     437   \n",
       "4                    347                     407                     -60   \n",
       "\n",
       "   other_change_2015  population_2015  international_in_total  \\\n",
       "0                 15            92493                    2524   \n",
       "1                 12           139509                   15883   \n",
       "2                 25           135275                    3501   \n",
       "3                -23           194803                    9454   \n",
       "4                 19           105389                    4671   \n",
       "\n",
       "   international_net_total  population_change  \n",
       "0                      546               2438  \n",
       "1                     8376              -2114  \n",
       "2                       40              -4117  \n",
       "3                     1953              10324  \n",
       "4                      304               7473  \n",
       "\n",
       "[5 rows x 161 columns]"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "immigration_df = pd.read_csv(\"data/immigration_df.csv\",index_col=\"Unnamed: 0\")\n",
    "immigration_df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Merging both DataFrames"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {
    "collapsed": false,
    "run_control": {
     "read_only": false
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>0</th>\n",
       "      <th>1</th>\n",
       "      <th>2</th>\n",
       "      <th>3</th>\n",
       "      <th>4</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>Area_Code</th>\n",
       "      <td>E06000001</td>\n",
       "      <td>E06000002</td>\n",
       "      <td>E06000003</td>\n",
       "      <td>E06000004</td>\n",
       "      <td>E06000005</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Area_x</th>\n",
       "      <td>Hartlepool</td>\n",
       "      <td>Middlesbrough</td>\n",
       "      <td>Redcar and Cleveland</td>\n",
       "      <td>Stockton-on-Tees</td>\n",
       "      <td>Darlington</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>country</th>\n",
       "      <td>E</td>\n",
       "      <td>E</td>\n",
       "      <td>E</td>\n",
       "      <td>E</td>\n",
       "      <td>E</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>population_2001</th>\n",
       "      <td>90152</td>\n",
       "      <td>141233</td>\n",
       "      <td>139159</td>\n",
       "      <td>183795</td>\n",
       "      <td>97894</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>births_2002</th>\n",
       "      <td>1017</td>\n",
       "      <td>1663</td>\n",
       "      <td>1336</td>\n",
       "      <td>1938</td>\n",
       "      <td>1102</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>deaths_2002</th>\n",
       "      <td>1042</td>\n",
       "      <td>1396</td>\n",
       "      <td>1523</td>\n",
       "      <td>1719</td>\n",
       "      <td>1173</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>natchange_2002</th>\n",
       "      <td>-25</td>\n",
       "      <td>267</td>\n",
       "      <td>-187</td>\n",
       "      <td>219</td>\n",
       "      <td>-71</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>internal_in_2002</th>\n",
       "      <td>2140</td>\n",
       "      <td>5109</td>\n",
       "      <td>3951</td>\n",
       "      <td>6269</td>\n",
       "      <td>3815</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>internal_out_2002</th>\n",
       "      <td>2301</td>\n",
       "      <td>6737</td>\n",
       "      <td>4259</td>\n",
       "      <td>5531</td>\n",
       "      <td>3521</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>internal_net_2002</th>\n",
       "      <td>-161</td>\n",
       "      <td>-1628</td>\n",
       "      <td>-308</td>\n",
       "      <td>738</td>\n",
       "      <td>294</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>international_in_2002</th>\n",
       "      <td>156</td>\n",
       "      <td>990</td>\n",
       "      <td>478</td>\n",
       "      <td>805</td>\n",
       "      <td>209</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>international_out_2002</th>\n",
       "      <td>190</td>\n",
       "      <td>395</td>\n",
       "      <td>415</td>\n",
       "      <td>463</td>\n",
       "      <td>252</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>international_net_2002</th>\n",
       "      <td>-34</td>\n",
       "      <td>595</td>\n",
       "      <td>63</td>\n",
       "      <td>342</td>\n",
       "      <td>-43</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>other_change_2002</th>\n",
       "      <td>61</td>\n",
       "      <td>-377</td>\n",
       "      <td>-207</td>\n",
       "      <td>-154</td>\n",
       "      <td>400</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>population_2002</th>\n",
       "      <td>89993</td>\n",
       "      <td>140090</td>\n",
       "      <td>138520</td>\n",
       "      <td>184940</td>\n",
       "      <td>98474</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>births_2003</th>\n",
       "      <td>1022</td>\n",
       "      <td>1699</td>\n",
       "      <td>1409</td>\n",
       "      <td>2072</td>\n",
       "      <td>1185</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>deaths_2003</th>\n",
       "      <td>1016</td>\n",
       "      <td>1374</td>\n",
       "      <td>1525</td>\n",
       "      <td>1700</td>\n",
       "      <td>1048</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>natchange_2003</th>\n",
       "      <td>6</td>\n",
       "      <td>325</td>\n",
       "      <td>-116</td>\n",
       "      <td>372</td>\n",
       "      <td>137</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>internal_in_2003</th>\n",
       "      <td>2252</td>\n",
       "      <td>5330</td>\n",
       "      <td>4542</td>\n",
       "      <td>6181</td>\n",
       "      <td>3653</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>internal_out_2003</th>\n",
       "      <td>2093</td>\n",
       "      <td>6601</td>\n",
       "      <td>4079</td>\n",
       "      <td>5688</td>\n",
       "      <td>3657</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>internal_net_2003</th>\n",
       "      <td>159</td>\n",
       "      <td>-1271</td>\n",
       "      <td>463</td>\n",
       "      <td>493</td>\n",
       "      <td>-4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>international_in_2003</th>\n",
       "      <td>163</td>\n",
       "      <td>930</td>\n",
       "      <td>365</td>\n",
       "      <td>727</td>\n",
       "      <td>206</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>international_out_2003</th>\n",
       "      <td>241</td>\n",
       "      <td>629</td>\n",
       "      <td>440</td>\n",
       "      <td>683</td>\n",
       "      <td>305</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>international_net_2003</th>\n",
       "      <td>-78</td>\n",
       "      <td>301</td>\n",
       "      <td>-75</td>\n",
       "      <td>44</td>\n",
       "      <td>-99</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>other_change_2003</th>\n",
       "      <td>54</td>\n",
       "      <td>-395</td>\n",
       "      <td>-227</td>\n",
       "      <td>-150</td>\n",
       "      <td>403</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>population_2003</th>\n",
       "      <td>90134</td>\n",
       "      <td>139050</td>\n",
       "      <td>138565</td>\n",
       "      <td>185699</td>\n",
       "      <td>98911</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>births_2004</th>\n",
       "      <td>1077</td>\n",
       "      <td>1863</td>\n",
       "      <td>1508</td>\n",
       "      <td>2165</td>\n",
       "      <td>1175</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>deaths_2004</th>\n",
       "      <td>998</td>\n",
       "      <td>1463</td>\n",
       "      <td>1520</td>\n",
       "      <td>1752</td>\n",
       "      <td>1197</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>natchange_2004</th>\n",
       "      <td>79</td>\n",
       "      <td>400</td>\n",
       "      <td>-12</td>\n",
       "      <td>413</td>\n",
       "      <td>-22</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>internal_in_2004</th>\n",
       "      <td>2164</td>\n",
       "      <td>5264</td>\n",
       "      <td>4184</td>\n",
       "      <td>5893</td>\n",
       "      <td>3767</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>internal_out_2015</th>\n",
       "      <td>2281</td>\n",
       "      <td>7071</td>\n",
       "      <td>4019</td>\n",
       "      <td>6659</td>\n",
       "      <td>3779</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>internal_net_2015</th>\n",
       "      <td>-144</td>\n",
       "      <td>-887</td>\n",
       "      <td>195</td>\n",
       "      <td>-217</td>\n",
       "      <td>-36</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>international_in_2015</th>\n",
       "      <td>248</td>\n",
       "      <td>1250</td>\n",
       "      <td>202</td>\n",
       "      <td>994</td>\n",
       "      <td>347</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>international_out_2015</th>\n",
       "      <td>135</td>\n",
       "      <td>450</td>\n",
       "      <td>109</td>\n",
       "      <td>557</td>\n",
       "      <td>407</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>international_net_2015</th>\n",
       "      <td>113</td>\n",
       "      <td>800</td>\n",
       "      <td>93</td>\n",
       "      <td>437</td>\n",
       "      <td>-60</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>other_change_2015</th>\n",
       "      <td>15</td>\n",
       "      <td>12</td>\n",
       "      <td>25</td>\n",
       "      <td>-23</td>\n",
       "      <td>19</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>population_2015</th>\n",
       "      <td>92493</td>\n",
       "      <td>139509</td>\n",
       "      <td>135275</td>\n",
       "      <td>194803</td>\n",
       "      <td>105389</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>international_in_total</th>\n",
       "      <td>2524</td>\n",
       "      <td>15883</td>\n",
       "      <td>3501</td>\n",
       "      <td>9454</td>\n",
       "      <td>4671</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>international_net_total</th>\n",
       "      <td>546</td>\n",
       "      <td>8376</td>\n",
       "      <td>40</td>\n",
       "      <td>1953</td>\n",
       "      <td>304</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>population_change</th>\n",
       "      <td>2438</td>\n",
       "      <td>-2114</td>\n",
       "      <td>-4117</td>\n",
       "      <td>10324</td>\n",
       "      <td>7473</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>id</th>\n",
       "      <td>70</td>\n",
       "      <td>71</td>\n",
       "      <td>72</td>\n",
       "      <td>73</td>\n",
       "      <td>74</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Region_Code</th>\n",
       "      <td>E12000001</td>\n",
       "      <td>E12000001</td>\n",
       "      <td>E12000001</td>\n",
       "      <td>E12000001</td>\n",
       "      <td>E12000001</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Region</th>\n",
       "      <td>North East</td>\n",
       "      <td>North East</td>\n",
       "      <td>North East</td>\n",
       "      <td>North East</td>\n",
       "      <td>North East</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Area_y</th>\n",
       "      <td>Hartlepool</td>\n",
       "      <td>Middlesbrough</td>\n",
       "      <td>Redcar and Cleveland</td>\n",
       "      <td>Stockton-on-Tees</td>\n",
       "      <td>Darlington</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Electorate</th>\n",
       "      <td>70341</td>\n",
       "      <td>94612</td>\n",
       "      <td>103529</td>\n",
       "      <td>141486</td>\n",
       "      <td>77662</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>ExpectedBallots</th>\n",
       "      <td>46137</td>\n",
       "      <td>61395</td>\n",
       "      <td>72741</td>\n",
       "      <td>100462</td>\n",
       "      <td>55194</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>VerifiedBallotPapers</th>\n",
       "      <td>46134</td>\n",
       "      <td>61393</td>\n",
       "      <td>72741</td>\n",
       "      <td>100460</td>\n",
       "      <td>55195</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Pct_Turnout</th>\n",
       "      <td>65.59</td>\n",
       "      <td>64.89</td>\n",
       "      <td>70.26</td>\n",
       "      <td>71</td>\n",
       "      <td>71.07</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Votes_Cast</th>\n",
       "      <td>46134</td>\n",
       "      <td>61393</td>\n",
       "      <td>72741</td>\n",
       "      <td>100460</td>\n",
       "      <td>55195</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Valid_Votes</th>\n",
       "      <td>46100</td>\n",
       "      <td>61358</td>\n",
       "      <td>72714</td>\n",
       "      <td>100415</td>\n",
       "      <td>55166</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Remain</th>\n",
       "      <td>14029</td>\n",
       "      <td>21181</td>\n",
       "      <td>24586</td>\n",
       "      <td>38433</td>\n",
       "      <td>24172</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Leave</th>\n",
       "      <td>32071</td>\n",
       "      <td>40177</td>\n",
       "      <td>48128</td>\n",
       "      <td>61982</td>\n",
       "      <td>30994</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Rejected_Ballots</th>\n",
       "      <td>34</td>\n",
       "      <td>35</td>\n",
       "      <td>27</td>\n",
       "      <td>45</td>\n",
       "      <td>29</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>No_official_mark</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Voting_for_both_answers</th>\n",
       "      <td>12</td>\n",
       "      <td>16</td>\n",
       "      <td>6</td>\n",
       "      <td>13</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Writing_or_mark</th>\n",
       "      <td>6</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Unmarked_or_void</th>\n",
       "      <td>16</td>\n",
       "      <td>18</td>\n",
       "      <td>21</td>\n",
       "      <td>28</td>\n",
       "      <td>29</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Pct_Remain</th>\n",
       "      <td>30.43</td>\n",
       "      <td>34.52</td>\n",
       "      <td>33.81</td>\n",
       "      <td>38.27</td>\n",
       "      <td>43.82</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Pct_Leave</th>\n",
       "      <td>69.57</td>\n",
       "      <td>65.48</td>\n",
       "      <td>66.19</td>\n",
       "      <td>61.73</td>\n",
       "      <td>56.18</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Pct_Rejected</th>\n",
       "      <td>0.07</td>\n",
       "      <td>0.06</td>\n",
       "      <td>0.04</td>\n",
       "      <td>0.04</td>\n",
       "      <td>0.05</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>181 rows × 5 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                                  0              1                     2  \\\n",
       "Area_Code                 E06000001      E06000002             E06000003   \n",
       "Area_x                   Hartlepool  Middlesbrough  Redcar and Cleveland   \n",
       "country                           E              E                     E   \n",
       "population_2001               90152         141233                139159   \n",
       "births_2002                    1017           1663                  1336   \n",
       "deaths_2002                    1042           1396                  1523   \n",
       "natchange_2002                  -25            267                  -187   \n",
       "internal_in_2002               2140           5109                  3951   \n",
       "internal_out_2002              2301           6737                  4259   \n",
       "internal_net_2002              -161          -1628                  -308   \n",
       "international_in_2002           156            990                   478   \n",
       "international_out_2002          190            395                   415   \n",
       "international_net_2002          -34            595                    63   \n",
       "other_change_2002                61           -377                  -207   \n",
       "population_2002               89993         140090                138520   \n",
       "births_2003                    1022           1699                  1409   \n",
       "deaths_2003                    1016           1374                  1525   \n",
       "natchange_2003                    6            325                  -116   \n",
       "internal_in_2003               2252           5330                  4542   \n",
       "internal_out_2003              2093           6601                  4079   \n",
       "internal_net_2003               159          -1271                   463   \n",
       "international_in_2003           163            930                   365   \n",
       "international_out_2003          241            629                   440   \n",
       "international_net_2003          -78            301                   -75   \n",
       "other_change_2003                54           -395                  -227   \n",
       "population_2003               90134         139050                138565   \n",
       "births_2004                    1077           1863                  1508   \n",
       "deaths_2004                     998           1463                  1520   \n",
       "natchange_2004                   79            400                   -12   \n",
       "internal_in_2004               2164           5264                  4184   \n",
       "...                             ...            ...                   ...   \n",
       "internal_out_2015              2281           7071                  4019   \n",
       "internal_net_2015              -144           -887                   195   \n",
       "international_in_2015           248           1250                   202   \n",
       "international_out_2015          135            450                   109   \n",
       "international_net_2015          113            800                    93   \n",
       "other_change_2015                15             12                    25   \n",
       "population_2015               92493         139509                135275   \n",
       "international_in_total         2524          15883                  3501   \n",
       "international_net_total         546           8376                    40   \n",
       "population_change              2438          -2114                 -4117   \n",
       "id                               70             71                    72   \n",
       "Region_Code               E12000001      E12000001             E12000001   \n",
       "Region                   North East     North East            North East   \n",
       "Area_y                   Hartlepool  Middlesbrough  Redcar and Cleveland   \n",
       "Electorate                    70341          94612                103529   \n",
       "ExpectedBallots               46137          61395                 72741   \n",
       "VerifiedBallotPapers          46134          61393                 72741   \n",
       "Pct_Turnout                   65.59          64.89                 70.26   \n",
       "Votes_Cast                    46134          61393                 72741   \n",
       "Valid_Votes                   46100          61358                 72714   \n",
       "Remain                        14029          21181                 24586   \n",
       "Leave                         32071          40177                 48128   \n",
       "Rejected_Ballots                 34             35                    27   \n",
       "No_official_mark                  0              0                     0   \n",
       "Voting_for_both_answers          12             16                     6   \n",
       "Writing_or_mark                   6              1                     0   \n",
       "Unmarked_or_void                 16             18                    21   \n",
       "Pct_Remain                    30.43          34.52                 33.81   \n",
       "Pct_Leave                     69.57          65.48                 66.19   \n",
       "Pct_Rejected                   0.07           0.06                  0.04   \n",
       "\n",
       "                                        3           4  \n",
       "Area_Code                       E06000004   E06000005  \n",
       "Area_x                   Stockton-on-Tees  Darlington  \n",
       "country                                 E           E  \n",
       "population_2001                    183795       97894  \n",
       "births_2002                          1938        1102  \n",
       "deaths_2002                          1719        1173  \n",
       "natchange_2002                        219         -71  \n",
       "internal_in_2002                     6269        3815  \n",
       "internal_out_2002                    5531        3521  \n",
       "internal_net_2002                     738         294  \n",
       "international_in_2002                 805         209  \n",
       "international_out_2002                463         252  \n",
       "international_net_2002                342         -43  \n",
       "other_change_2002                    -154         400  \n",
       "population_2002                    184940       98474  \n",
       "births_2003                          2072        1185  \n",
       "deaths_2003                          1700        1048  \n",
       "natchange_2003                        372         137  \n",
       "internal_in_2003                     6181        3653  \n",
       "internal_out_2003                    5688        3657  \n",
       "internal_net_2003                     493          -4  \n",
       "international_in_2003                 727         206  \n",
       "international_out_2003                683         305  \n",
       "international_net_2003                 44         -99  \n",
       "other_change_2003                    -150         403  \n",
       "population_2003                    185699       98911  \n",
       "births_2004                          2165        1175  \n",
       "deaths_2004                          1752        1197  \n",
       "natchange_2004                        413         -22  \n",
       "internal_in_2004                     5893        3767  \n",
       "...                                   ...         ...  \n",
       "internal_out_2015                    6659        3779  \n",
       "internal_net_2015                    -217         -36  \n",
       "international_in_2015                 994         347  \n",
       "international_out_2015                557         407  \n",
       "international_net_2015                437         -60  \n",
       "other_change_2015                     -23          19  \n",
       "population_2015                    194803      105389  \n",
       "international_in_total               9454        4671  \n",
       "international_net_total              1953         304  \n",
       "population_change                   10324        7473  \n",
       "id                                     73          74  \n",
       "Region_Code                     E12000001   E12000001  \n",
       "Region                         North East  North East  \n",
       "Area_y                   Stockton-on-Tees  Darlington  \n",
       "Electorate                         141486       77662  \n",
       "ExpectedBallots                    100462       55194  \n",
       "VerifiedBallotPapers               100460       55195  \n",
       "Pct_Turnout                            71       71.07  \n",
       "Votes_Cast                         100460       55195  \n",
       "Valid_Votes                        100415       55166  \n",
       "Remain                              38433       24172  \n",
       "Leave                               61982       30994  \n",
       "Rejected_Ballots                       45          29  \n",
       "No_official_mark                        2           0  \n",
       "Voting_for_both_answers                13           0  \n",
       "Writing_or_mark                         2           0  \n",
       "Unmarked_or_void                       28          29  \n",
       "Pct_Remain                          38.27       43.82  \n",
       "Pct_Leave                           61.73       56.18  \n",
       "Pct_Rejected                         0.04        0.05  \n",
       "\n",
       "[181 rows x 5 columns]"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "immigration_referendum_df = pd.merge(immigration_df,referendum_df, on=\"Area_Code\")\n",
    "immigration_referendum_df.head().transpose()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Exporting merged dataframe to a new CSV file"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {
    "collapsed": false,
    "run_control": {
     "read_only": false
    }
   },
   "outputs": [],
   "source": [
    "immigration_referendum_df.to_csv(\"data/immigration_referendum_df.csv\")"
   ]
  }
 ],
 "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.4.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 0
}