gather_infra_stocks_data.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 223,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "import matplotlib.pyplot as plt\n",
    "from pandas_helper import get_list_of_index_names, broadcast_simple, concat_categories\n",
    "\n",
    "%matplotlib inline"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "This is just for SIDS at the moment. Can be updated for all countries when we have the data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 224,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "#Names to WB names\n",
    "any_to_wb = pd.read_csv(\"inputs/any_name_to_wb_name.csv\",index_col=\"any\",squeeze=True)\n",
    "\n",
    "#GAR names with SIDS spec\n",
    "gar_name_sids = pd.read_csv(\"inputs/gar_name_sids.csv\")\n",
    "\n",
    "#iso3 to wb country name table\n",
    "iso3_to_wb=pd.read_csv(\"inputs/iso3_to_wb_name.csv\").set_index(\"iso3\").squeeze()\n",
    "\n",
    "#iso2 to iso3 table\n",
    "iso2_iso3 = pd.read_csv(\"inputs/names_to_iso.csv\", usecols=[\"iso2\",\"iso3\"]).drop_duplicates().set_index(\"iso2\").squeeze() #the tables has more lines than countries to account for several ways of writing country names"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 225,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "path = \"sids data\\\\\""
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Roads -- km from OSM and hard coding road costs"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 226,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "roads = pd.read_csv(path+\"km_roads_sids.csv\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 227,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "roads[['Total Length (km)', 'Total Primary',\\\n",
    "       'Total Secondary', 'Total Tertiary', 'Residential', 'Track / service',\\\n",
    "       'Total Other']] = roads[['Total Length (km)', 'Total Primary',\\\n",
    "       'Total Secondary', 'Total Tertiary', 'Residential', 'Track / service',\\\n",
    "       'Total Other']].astype(float)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 228,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "roads[\"country\"] = roads.country.replace(any_to_wb)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 229,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "#cost per km of road based on st Lucia data. \n",
    "primary_road_cost = 1200000\n",
    "secondary_road_cost = 700000\n",
    "tertiary_road_cost = 600000"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 230,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "#reduced road values because of depreciation.\n",
    "lifetime_primary = 20\n",
    "lifetime_secondary = 15\n",
    "lifetime_tertiary = 10"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 231,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "roads = roads.fillna(0)\n",
    "roads = roads.set_index(\"country\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 232,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "roads[\"average_age_primary\"] = 8\n",
    "roads[\"average_age_secondary\"] = 5\n",
    "roads[\"average_age_tertiary\"] = 5"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 233,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "roads.loc[\"Kiribati\",\"average_age_primary\"]=12"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 234,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "roads[\"transport\"] = roads[\"Total Primary\"]*primary_road_cost*(1-1/lifetime_primary*roads.average_age_primary)+\\\n",
    "                             roads[\"Total Secondary\"]*secondary_road_cost*(1-1/lifetime_secondary*roads.average_age_secondary)+\\\n",
    "                             roads[\"Total Tertiary\"]*tertiary_road_cost*(1-1/lifetime_tertiary*roads.average_age_tertiary)+\\\n",
    "                             roads[\"Residential\"]*tertiary_road_cost*(1-1/lifetime_tertiary*roads.average_age_tertiary)+\\\n",
    "                             roads[\"Track / service\"]*tertiary_road_cost*(1-1/lifetime_tertiary*roads.average_age_tertiary)+\\\n",
    "                             roads[\"Total Other\"]*tertiary_road_cost*(1-1/lifetime_tertiary*roads.average_age_tertiary)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 235,
   "metadata": {
    "collapsed": 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>ISO</th>\n",
       "      <th>Projection</th>\n",
       "      <th>EPSG</th>\n",
       "      <th>Sheet</th>\n",
       "      <th>Total Length (km)</th>\n",
       "      <th>Total Primary</th>\n",
       "      <th>Total Secondary</th>\n",
       "      <th>Total Tertiary</th>\n",
       "      <th>Residential</th>\n",
       "      <th>Track / service</th>\n",
       "      <th>Total Other</th>\n",
       "      <th>average_age_primary</th>\n",
       "      <th>average_age_secondary</th>\n",
       "      <th>average_age_tertiary</th>\n",
       "      <th>transport</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>country</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>Marshall Islands</th>\n",
       "      <td>MHL</td>\n",
       "      <td>UTM59N</td>\n",
       "      <td>32659</td>\n",
       "      <td>MHL_UTM59N</td>\n",
       "      <td>107.236929</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>6.368138</td>\n",
       "      <td>8.632900</td>\n",
       "      <td>6.989726</td>\n",
       "      <td>59.002291</td>\n",
       "      <td>26.243874</td>\n",
       "      <td>8</td>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "      <td>3.323244e+07</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Kiribati</th>\n",
       "      <td>KIR</td>\n",
       "      <td>UTM59N</td>\n",
       "      <td>32659</td>\n",
       "      <td>KIR_UTM59N</td>\n",
       "      <td>1190.900576</td>\n",
       "      <td>68.000000</td>\n",
       "      <td>87.000000</td>\n",
       "      <td>7.000000</td>\n",
       "      <td>67.000000</td>\n",
       "      <td>589.000000</td>\n",
       "      <td>374.000000</td>\n",
       "      <td>12</td>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "      <td>3.843400e+08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Antigua and Barbuda</th>\n",
       "      <td>ATG</td>\n",
       "      <td>UTM20N</td>\n",
       "      <td>32620</td>\n",
       "      <td>ATG_UTM20N</td>\n",
       "      <td>1368.449906</td>\n",
       "      <td>104.578230</td>\n",
       "      <td>70.561461</td>\n",
       "      <td>54.123172</td>\n",
       "      <td>757.917842</td>\n",
       "      <td>379.964498</td>\n",
       "      <td>1.304702</td>\n",
       "      <td>8</td>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "      <td>4.662181e+08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Jamaica</th>\n",
       "      <td>JAM</td>\n",
       "      <td>UTM18N</td>\n",
       "      <td>32618</td>\n",
       "      <td>JAM_UTM18N</td>\n",
       "      <td>14969.821590</td>\n",
       "      <td>1069.891646</td>\n",
       "      <td>1072.979872</td>\n",
       "      <td>1255.614165</td>\n",
       "      <td>7609.429350</td>\n",
       "      <td>1510.734627</td>\n",
       "      <td>2451.171930</td>\n",
       "      <td>8</td>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "      <td>5.119131e+09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>St. Vincent and the Grenadines</th>\n",
       "      <td>VCT</td>\n",
       "      <td>UTM20N</td>\n",
       "      <td>32620</td>\n",
       "      <td>VCT_UTM20N</td>\n",
       "      <td>804.311347</td>\n",
       "      <td>62.195979</td>\n",
       "      <td>42.670103</td>\n",
       "      <td>42.014011</td>\n",
       "      <td>339.296826</td>\n",
       "      <td>164.818192</td>\n",
       "      <td>153.316237</td>\n",
       "      <td>8</td>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "      <td>2.745274e+08</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                ISO Projection   EPSG       Sheet  \\\n",
       "country                                                             \n",
       "Marshall Islands                MHL     UTM59N  32659  MHL_UTM59N   \n",
       "Kiribati                        KIR     UTM59N  32659  KIR_UTM59N   \n",
       "Antigua and Barbuda             ATG     UTM20N  32620  ATG_UTM20N   \n",
       "Jamaica                         JAM     UTM18N  32618  JAM_UTM18N   \n",
       "St. Vincent and the Grenadines  VCT     UTM20N  32620  VCT_UTM20N   \n",
       "\n",
       "                                Total Length (km)  Total Primary  \\\n",
       "country                                                            \n",
       "Marshall Islands                       107.236929       0.000000   \n",
       "Kiribati                              1190.900576      68.000000   \n",
       "Antigua and Barbuda                   1368.449906     104.578230   \n",
       "Jamaica                              14969.821590    1069.891646   \n",
       "St. Vincent and the Grenadines         804.311347      62.195979   \n",
       "\n",
       "                                Total Secondary  Total Tertiary  Residential  \\\n",
       "country                                                                        \n",
       "Marshall Islands                       6.368138        8.632900     6.989726   \n",
       "Kiribati                              87.000000        7.000000    67.000000   \n",
       "Antigua and Barbuda                   70.561461       54.123172   757.917842   \n",
       "Jamaica                             1072.979872     1255.614165  7609.429350   \n",
       "St. Vincent and the Grenadines        42.670103       42.014011   339.296826   \n",
       "\n",
       "                                Track / service  Total Other  \\\n",
       "country                                                        \n",
       "Marshall Islands                      59.002291    26.243874   \n",
       "Kiribati                             589.000000   374.000000   \n",
       "Antigua and Barbuda                  379.964498     1.304702   \n",
       "Jamaica                             1510.734627  2451.171930   \n",
       "St. Vincent and the Grenadines       164.818192   153.316237   \n",
       "\n",
       "                                average_age_primary  average_age_secondary  \\\n",
       "country                                                                      \n",
       "Marshall Islands                                  8                      5   \n",
       "Kiribati                                         12                      5   \n",
       "Antigua and Barbuda                               8                      5   \n",
       "Jamaica                                           8                      5   \n",
       "St. Vincent and the Grenadines                    8                      5   \n",
       "\n",
       "                                average_age_tertiary     transport  \n",
       "country                                                             \n",
       "Marshall Islands                                   5  3.323244e+07  \n",
       "Kiribati                                           5  3.843400e+08  \n",
       "Antigua and Barbuda                                5  4.662181e+08  \n",
       "Jamaica                                            5  5.119131e+09  \n",
       "St. Vincent and the Grenadines                     5  2.745274e+08  "
      ]
     },
     "execution_count": 235,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "roads.sample(5)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Power (from Platts)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 236,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "power = pd.read_csv(path+\"power_plants_sids.csv\")\n",
    "power[\"country\"] = power.COUNTRY.apply(lambda x:x.title()).replace(any_to_wb)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 237,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "power[\"power\"] = power.CAPEX"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Buildings data from Rashmin and PCRAFI "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 238,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "buildings_pcrafi = pd.read_csv(path+\"PCRAFI_stocks.csv\")\n",
    "#values in million USD"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 239,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "#we divide by 3 because for Fiji PCRAFI is 3 times higher than Rashmin's assessment, which is consistent with GAR\n",
    "buildings_pcrafi[\"country\"] = buildings_pcrafi.country.replace(any_to_wb)\n",
    "buildings_pcrafi = 1/3*10**6*buildings_pcrafi.set_index(\"country\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 240,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "#buildings_pcrafi.rename(columns={\"building\":\"total_building_value\"},inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 241,
   "metadata": {
    "collapsed": 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>building</th>\n",
       "      <th>infrastructure</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>country</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>Cook Islands</th>\n",
       "      <td>4.322667e+08</td>\n",
       "      <td>3.920000e+07</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Fiji</th>\n",
       "      <td>6.288400e+09</td>\n",
       "      <td>1.031300e+09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Micronesia, Fed. Sts.</th>\n",
       "      <td>5.763333e+08</td>\n",
       "      <td>1.042667e+08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Kiribati</th>\n",
       "      <td>3.353667e+08</td>\n",
       "      <td>5.473333e+07</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Marshall Islands</th>\n",
       "      <td>4.680333e+08</td>\n",
       "      <td>9.530000e+07</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Nauru</th>\n",
       "      <td>5.793333e+07</td>\n",
       "      <td>2.466667e+07</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Niue</th>\n",
       "      <td>1.368667e+08</td>\n",
       "      <td>1.400000e+07</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Papua New Guinea</th>\n",
       "      <td>1.316967e+10</td>\n",
       "      <td>2.213033e+09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Palau</th>\n",
       "      <td>4.461667e+08</td>\n",
       "      <td>5.330000e+07</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Solomon Islands</th>\n",
       "      <td>1.019567e+09</td>\n",
       "      <td>1.401000e+08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Timor-Leste</th>\n",
       "      <td>5.960433e+09</td>\n",
       "      <td>7.202000e+08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Tonga</th>\n",
       "      <td>8.417333e+08</td>\n",
       "      <td>8.646667e+07</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Tuvalu</th>\n",
       "      <td>7.643333e+07</td>\n",
       "      <td>1.323333e+07</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Vanuatu</th>\n",
       "      <td>9.528000e+08</td>\n",
       "      <td>1.400000e+08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Samoa</th>\n",
       "      <td>7.159667e+08</td>\n",
       "      <td>1.558000e+08</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                           building  infrastructure\n",
       "country                                            \n",
       "Cook Islands           4.322667e+08    3.920000e+07\n",
       "Fiji                   6.288400e+09    1.031300e+09\n",
       "Micronesia, Fed. Sts.  5.763333e+08    1.042667e+08\n",
       "Kiribati               3.353667e+08    5.473333e+07\n",
       "Marshall Islands       4.680333e+08    9.530000e+07\n",
       "Nauru                  5.793333e+07    2.466667e+07\n",
       "Niue                   1.368667e+08    1.400000e+07\n",
       "Papua New Guinea       1.316967e+10    2.213033e+09\n",
       "Palau                  4.461667e+08    5.330000e+07\n",
       "Solomon Islands        1.019567e+09    1.401000e+08\n",
       "Timor-Leste            5.960433e+09    7.202000e+08\n",
       "Tonga                  8.417333e+08    8.646667e+07\n",
       "Tuvalu                 7.643333e+07    1.323333e+07\n",
       "Vanuatu                9.528000e+08    1.400000e+08\n",
       "Samoa                  7.159667e+08    1.558000e+08"
      ]
     },
     "execution_count": 241,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "buildings_pcrafi"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 242,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "buildings_rashmin = pd.read_csv(path+\"Gross_Capital_Summary.csv\")\n",
    "buildings_rashmin[\"country\"] = buildings_rashmin.country.replace(iso3_to_wb).replace(any_to_wb)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 243,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "buildings_rashmin[\"building\"] = buildings_rashmin[\"Buildings (Residential + Non-Res)\"]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 244,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "country\n",
       "Belize                4.620789e+09\n",
       "St. Lucia             3.197446e+09\n",
       "Jamaica               3.822092e+10\n",
       "Haiti                 2.656431e+10\n",
       "Grenada               2.131954e+09\n",
       "Dominican Republic    1.530000e+11\n",
       "Fiji                  6.504790e+09\n",
       "FJI est 2                      NaN\n",
       "Name: building, dtype: float64"
      ]
     },
     "execution_count": 244,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "buildings_rashmin.set_index(\"country\").building"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 245,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "#Fiji has 2 estimates. first one is rashmin, last one is PCRAFI\n",
    "buildings = pd.concat([buildings_rashmin.set_index(\"country\"),buildings_pcrafi]).reset_index().drop_duplicates(\"country\",keep=\"first\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Residential vs non residential buildings"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 246,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "buildings = buildings.set_index(\"country\")\n",
    "## hard coding Fiji data from Rashmin's file Fiji_2015_exposure_estimation_110517.xlsx\n",
    "fiji_total = 4093521135+2411268610\n",
    "fiji_residential = 4093521135/fiji_total\n",
    "\n",
    "# for the US, according to the BEA, residential is 19,199.1 billion and non residential 21,879.4 billion. so by default we put 50/50.\n",
    "buildings[\"building_residential\"] = buildings.building*0.5\n",
    "buildings[\"building_non_residential\"] = buildings.building*0.5\n",
    "buildings.loc[\"Fiji\",\"building_residential\"] = buildings.loc[\"Fiji\",\"building\"]*fiji_residential\n",
    "buildings.loc[\"Fiji\",\"building_non_residential\"] = buildings.loc[\"Fiji\",\"building\"]*(1-fiji_residential)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Water from WASH infrastructure needs"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 247,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "water = pd.read_csv(path+\"water_infra_value_sids.csv\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 248,
   "metadata": {
    "collapsed": 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>water</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>country</th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>Marshall Islands</th>\n",
       "      <td>7076136.868</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                        water\n",
       "country                      \n",
       "Marshall Islands  7076136.868"
      ]
     },
     "execution_count": 248,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "water[\"country\"] = water[\"country\"].replace(any_to_wb)\n",
    "water.set_index(\"country\").sample()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Merge all available infra "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 249,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "all_infra = pd.concat([roads.transport,power.set_index(\"country\").power,\\\n",
    "                       buildings[[\"building_residential\",\"building_non_residential\"]],water.set_index(\"country\")],axis=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 250,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "all_infra[\"all_infra_but_transport\"] = all_infra.power+all_infra.building_non_residential+all_infra.building_residential+all_infra.water"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 251,
   "metadata": {
    "collapsed": 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>transport</th>\n",
       "      <th>power</th>\n",
       "      <th>building_residential</th>\n",
       "      <th>building_non_residential</th>\n",
       "      <th>water</th>\n",
       "      <th>all_infra_but_transport</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>Cape Verde</th>\n",
       "      <td>NaN</td>\n",
       "      <td>112996443.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Antigua and Barbuda</th>\n",
       "      <td>4.662181e+08</td>\n",
       "      <td>43136405.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Marshall Islands</th>\n",
       "      <td>3.323244e+07</td>\n",
       "      <td>15285248.0</td>\n",
       "      <td>2.340167e+08</td>\n",
       "      <td>2.340167e+08</td>\n",
       "      <td>7076136.868</td>\n",
       "      <td>4.903947e+08</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                        transport        power  building_residential  \\\n",
       "Cape Verde                    NaN  112996443.0                   NaN   \n",
       "Antigua and Barbuda  4.662181e+08   43136405.0                   NaN   \n",
       "Marshall Islands     3.323244e+07   15285248.0          2.340167e+08   \n",
       "\n",
       "                     building_non_residential        water  \\\n",
       "Cape Verde                                NaN          NaN   \n",
       "Antigua and Barbuda                       NaN          NaN   \n",
       "Marshall Islands                 2.340167e+08  7076136.868   \n",
       "\n",
       "                     all_infra_but_transport  \n",
       "Cape Verde                               NaN  \n",
       "Antigua and Barbuda                      NaN  \n",
       "Marshall Islands                4.903947e+08  "
      ]
     },
     "execution_count": 251,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "all_infra.sample(3)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 252,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "all_infra = all_infra[[\"transport\",\"all_infra_but_transport\"]].dropna().unstack().reset_index().rename(columns={\"level_0\":\"sector\",\"level_1\":\"country\",0:\"infra_k\"}).set_index([\"country\",\"sector\"])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 253,
   "metadata": {
    "collapsed": 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></th>\n",
       "      <th>infra_k</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>country</th>\n",
       "      <th>sector</th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>Belize</th>\n",
       "      <th>transport</th>\n",
       "      <td>2.513105e+09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Fiji</th>\n",
       "      <th>transport</th>\n",
       "      <td>2.981647e+09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Haiti</th>\n",
       "      <th>transport</th>\n",
       "      <td>9.742686e+09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Jamaica</th>\n",
       "      <th>transport</th>\n",
       "      <td>5.119131e+09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Kiribati</th>\n",
       "      <th>transport</th>\n",
       "      <td>3.843400e+08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Marshall Islands</th>\n",
       "      <th>transport</th>\n",
       "      <td>3.323244e+07</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Micronesia, Fed. Sts.</th>\n",
       "      <th>transport</th>\n",
       "      <td>1.575350e+08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Papua New Guinea</th>\n",
       "      <th>transport</th>\n",
       "      <td>6.976123e+09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Samoa</th>\n",
       "      <th>transport</th>\n",
       "      <td>9.025303e+08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Solomon Islands</th>\n",
       "      <th>transport</th>\n",
       "      <td>5.905869e+08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>St. Lucia</th>\n",
       "      <th>transport</th>\n",
       "      <td>6.512612e+08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Tonga</th>\n",
       "      <th>transport</th>\n",
       "      <td>5.739481e+08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Belize</th>\n",
       "      <th>all_infra_but_transport</th>\n",
       "      <td>5.302831e+09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Fiji</th>\n",
       "      <th>all_infra_but_transport</th>\n",
       "      <td>7.401315e+09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Haiti</th>\n",
       "      <th>all_infra_but_transport</th>\n",
       "      <td>2.805957e+10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Jamaica</th>\n",
       "      <th>all_infra_but_transport</th>\n",
       "      <td>4.326975e+10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Kiribati</th>\n",
       "      <th>all_infra_but_transport</th>\n",
       "      <td>3.511768e+08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Marshall Islands</th>\n",
       "      <th>all_infra_but_transport</th>\n",
       "      <td>4.903947e+08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Micronesia, Fed. Sts.</th>\n",
       "      <th>all_infra_but_transport</th>\n",
       "      <td>6.126917e+08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Papua New Guinea</th>\n",
       "      <th>all_infra_but_transport</th>\n",
       "      <td>1.491247e+10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Samoa</th>\n",
       "      <th>all_infra_but_transport</th>\n",
       "      <td>8.354077e+08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Solomon Islands</th>\n",
       "      <th>all_infra_but_transport</th>\n",
       "      <td>1.097297e+09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>St. Lucia</th>\n",
       "      <th>all_infra_but_transport</th>\n",
       "      <td>3.345909e+09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Tonga</th>\n",
       "      <th>all_infra_but_transport</th>\n",
       "      <td>8.902226e+08</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                                    infra_k\n",
       "country               sector                               \n",
       "Belize                transport                2.513105e+09\n",
       "Fiji                  transport                2.981647e+09\n",
       "Haiti                 transport                9.742686e+09\n",
       "Jamaica               transport                5.119131e+09\n",
       "Kiribati              transport                3.843400e+08\n",
       "Marshall Islands      transport                3.323244e+07\n",
       "Micronesia, Fed. Sts. transport                1.575350e+08\n",
       "Papua New Guinea      transport                6.976123e+09\n",
       "Samoa                 transport                9.025303e+08\n",
       "Solomon Islands       transport                5.905869e+08\n",
       "St. Lucia             transport                6.512612e+08\n",
       "Tonga                 transport                5.739481e+08\n",
       "Belize                all_infra_but_transport  5.302831e+09\n",
       "Fiji                  all_infra_but_transport  7.401315e+09\n",
       "Haiti                 all_infra_but_transport  2.805957e+10\n",
       "Jamaica               all_infra_but_transport  4.326975e+10\n",
       "Kiribati              all_infra_but_transport  3.511768e+08\n",
       "Marshall Islands      all_infra_but_transport  4.903947e+08\n",
       "Micronesia, Fed. Sts. all_infra_but_transport  6.126917e+08\n",
       "Papua New Guinea      all_infra_but_transport  1.491247e+10\n",
       "Samoa                 all_infra_but_transport  8.354077e+08\n",
       "Solomon Islands       all_infra_but_transport  1.097297e+09\n",
       "St. Lucia             all_infra_but_transport  3.345909e+09\n",
       "Tonga                 all_infra_but_transport  8.902226e+08"
      ]
     },
     "execution_count": 253,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "all_infra"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 254,
   "metadata": {
    "collapsed": 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>infra_k</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>country</th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>Belize</th>\n",
       "      <td>7.815936e+09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Fiji</th>\n",
       "      <td>1.038296e+10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Haiti</th>\n",
       "      <td>3.780226e+10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Jamaica</th>\n",
       "      <td>4.838888e+10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Kiribati</th>\n",
       "      <td>7.355168e+08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Marshall Islands</th>\n",
       "      <td>5.236272e+08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Micronesia, Fed. Sts.</th>\n",
       "      <td>7.702267e+08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Papua New Guinea</th>\n",
       "      <td>2.188859e+10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Samoa</th>\n",
       "      <td>1.737938e+09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Solomon Islands</th>\n",
       "      <td>1.687884e+09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>St. Lucia</th>\n",
       "      <td>3.997170e+09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Tonga</th>\n",
       "      <td>1.464171e+09</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                            infra_k\n",
       "country                            \n",
       "Belize                 7.815936e+09\n",
       "Fiji                   1.038296e+10\n",
       "Haiti                  3.780226e+10\n",
       "Jamaica                4.838888e+10\n",
       "Kiribati               7.355168e+08\n",
       "Marshall Islands       5.236272e+08\n",
       "Micronesia, Fed. Sts.  7.702267e+08\n",
       "Papua New Guinea       2.188859e+10\n",
       "Samoa                  1.737938e+09\n",
       "Solomon Islands        1.687884e+09\n",
       "St. Lucia              3.997170e+09\n",
       "Tonga                  1.464171e+09"
      ]
     },
     "execution_count": 254,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "all_infra.sum(level=\"country\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "collapsed": false
   },
   "source": [
    "shares = all_infra.dropna()\n",
    "shares = shares.multiply(1/shares.total,axis=\"index\").drop(\"total\",axis=1)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "collapsed": false
   },
   "source": [
    "shares.rename(columns={'total_roads_value':\"roads\", 'total_power_value':\"power plants\", 'total_building_value':\"buildings\", 'total_water_value':\"water\"}).plot(kind=\"bar\",stacked=True)\n",
    "plt.legend(bbox_to_anchor=(1.05, 1.0))\n",
    "plt.ylabel(\"Share in total infrastructure stock\")\n",
    "plt.savefig(\"infra_share.png\",bbox_inches='tight')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Read total capital data gathered in \"gather_capital_data\" and wb data gathered in \"download_wb_data\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 255,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "K = pd.read_csv(\"intermediate/avg_prod_k_with_gar_for_sids.csv\",index_col=\"Unnamed: 0\")\n",
    "wb = pd.read_csv(\"inputs/wb_data.csv\",index_col=\"country\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## recomputes Ktot from WB GDP and average capital productivity"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 256,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "wb[\"Ktot\"] = wb.gdp_pc_pp*wb['pop']/K.avg_prod_k\n",
    "wb[\"GDP\"] = wb.gdp_pc_pp*wb['pop']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 257,
   "metadata": {
    "collapsed": 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></th>\n",
       "      <th>infra_k</th>\n",
       "      <th>Ktot</th>\n",
       "      <th>GDP</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>country</th>\n",
       "      <th>sector</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>Belize</th>\n",
       "      <th>transport</th>\n",
       "      <td>2.513105e+09</td>\n",
       "      <td>1.073572e+10</td>\n",
       "      <td>2.873784e+09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Fiji</th>\n",
       "      <th>transport</th>\n",
       "      <td>2.981647e+09</td>\n",
       "      <td>2.288247e+10</td>\n",
       "      <td>7.965477e+09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Haiti</th>\n",
       "      <th>transport</th>\n",
       "      <td>9.742686e+09</td>\n",
       "      <td>1.345242e+11</td>\n",
       "      <td>1.794108e+10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Jamaica</th>\n",
       "      <th>transport</th>\n",
       "      <td>5.119131e+09</td>\n",
       "      <td>1.161836e+11</td>\n",
       "      <td>2.359830e+10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Kiribati</th>\n",
       "      <th>transport</th>\n",
       "      <td>3.843400e+08</td>\n",
       "      <td>7.645732e+08</td>\n",
       "      <td>2.171002e+08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Marshall Islands</th>\n",
       "      <th>transport</th>\n",
       "      <td>3.323244e+07</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2.003310e+08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Micronesia, Fed. Sts.</th>\n",
       "      <th>transport</th>\n",
       "      <td>1.575350e+08</td>\n",
       "      <td>NaN</td>\n",
       "      <td>3.495423e+08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Papua New Guinea</th>\n",
       "      <th>transport</th>\n",
       "      <td>6.976123e+09</td>\n",
       "      <td>6.516308e+10</td>\n",
       "      <td>2.118981e+10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Samoa</th>\n",
       "      <th>transport</th>\n",
       "      <td>9.025303e+08</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.147754e+09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Solomon Islands</th>\n",
       "      <th>transport</th>\n",
       "      <td>5.905869e+08</td>\n",
       "      <td>4.184846e+09</td>\n",
       "      <td>1.242422e+09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>St. Lucia</th>\n",
       "      <th>transport</th>\n",
       "      <td>6.512612e+08</td>\n",
       "      <td>4.808258e+09</td>\n",
       "      <td>1.905312e+09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Tonga</th>\n",
       "      <th>transport</th>\n",
       "      <td>5.739481e+08</td>\n",
       "      <td>1.596333e+09</td>\n",
       "      <td>5.712251e+08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Belize</th>\n",
       "      <th>all_infra_but_transport</th>\n",
       "      <td>5.302831e+09</td>\n",
       "      <td>1.073572e+10</td>\n",
       "      <td>2.873784e+09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Fiji</th>\n",
       "      <th>all_infra_but_transport</th>\n",
       "      <td>7.401315e+09</td>\n",
       "      <td>2.288247e+10</td>\n",
       "      <td>7.965477e+09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Haiti</th>\n",
       "      <th>all_infra_but_transport</th>\n",
       "      <td>2.805957e+10</td>\n",
       "      <td>1.345242e+11</td>\n",
       "      <td>1.794108e+10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Jamaica</th>\n",
       "      <th>all_infra_but_transport</th>\n",
       "      <td>4.326975e+10</td>\n",
       "      <td>1.161836e+11</td>\n",
       "      <td>2.359830e+10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Kiribati</th>\n",
       "      <th>all_infra_but_transport</th>\n",
       "      <td>3.511768e+08</td>\n",
       "      <td>7.645732e+08</td>\n",
       "      <td>2.171002e+08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Marshall Islands</th>\n",
       "      <th>all_infra_but_transport</th>\n",
       "      <td>4.903947e+08</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2.003310e+08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Micronesia, Fed. Sts.</th>\n",
       "      <th>all_infra_but_transport</th>\n",
       "      <td>6.126917e+08</td>\n",
       "      <td>NaN</td>\n",
       "      <td>3.495423e+08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Papua New Guinea</th>\n",
       "      <th>all_infra_but_transport</th>\n",
       "      <td>1.491247e+10</td>\n",
       "      <td>6.516308e+10</td>\n",
       "      <td>2.118981e+10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Samoa</th>\n",
       "      <th>all_infra_but_transport</th>\n",
       "      <td>8.354077e+08</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.147754e+09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Solomon Islands</th>\n",
       "      <th>all_infra_but_transport</th>\n",
       "      <td>1.097297e+09</td>\n",
       "      <td>4.184846e+09</td>\n",
       "      <td>1.242422e+09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>St. Lucia</th>\n",
       "      <th>all_infra_but_transport</th>\n",
       "      <td>3.345909e+09</td>\n",
       "      <td>4.808258e+09</td>\n",
       "      <td>1.905312e+09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Tonga</th>\n",
       "      <th>all_infra_but_transport</th>\n",
       "      <td>8.902226e+08</td>\n",
       "      <td>1.596333e+09</td>\n",
       "      <td>5.712251e+08</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                                    infra_k          Ktot  \\\n",
       "country               sector                                                \n",
       "Belize                transport                2.513105e+09  1.073572e+10   \n",
       "Fiji                  transport                2.981647e+09  2.288247e+10   \n",
       "Haiti                 transport                9.742686e+09  1.345242e+11   \n",
       "Jamaica               transport                5.119131e+09  1.161836e+11   \n",
       "Kiribati              transport                3.843400e+08  7.645732e+08   \n",
       "Marshall Islands      transport                3.323244e+07           NaN   \n",
       "Micronesia, Fed. Sts. transport                1.575350e+08           NaN   \n",
       "Papua New Guinea      transport                6.976123e+09  6.516308e+10   \n",
       "Samoa                 transport                9.025303e+08           NaN   \n",
       "Solomon Islands       transport                5.905869e+08  4.184846e+09   \n",
       "St. Lucia             transport                6.512612e+08  4.808258e+09   \n",
       "Tonga                 transport                5.739481e+08  1.596333e+09   \n",
       "Belize                all_infra_but_transport  5.302831e+09  1.073572e+10   \n",
       "Fiji                  all_infra_but_transport  7.401315e+09  2.288247e+10   \n",
       "Haiti                 all_infra_but_transport  2.805957e+10  1.345242e+11   \n",
       "Jamaica               all_infra_but_transport  4.326975e+10  1.161836e+11   \n",
       "Kiribati              all_infra_but_transport  3.511768e+08  7.645732e+08   \n",
       "Marshall Islands      all_infra_but_transport  4.903947e+08           NaN   \n",
       "Micronesia, Fed. Sts. all_infra_but_transport  6.126917e+08           NaN   \n",
       "Papua New Guinea      all_infra_but_transport  1.491247e+10  6.516308e+10   \n",
       "Samoa                 all_infra_but_transport  8.354077e+08           NaN   \n",
       "Solomon Islands       all_infra_but_transport  1.097297e+09  4.184846e+09   \n",
       "St. Lucia             all_infra_but_transport  3.345909e+09  4.808258e+09   \n",
       "Tonga                 all_infra_but_transport  8.902226e+08  1.596333e+09   \n",
       "\n",
       "                                                        GDP  \n",
       "country               sector                                 \n",
       "Belize                transport                2.873784e+09  \n",
       "Fiji                  transport                7.965477e+09  \n",
       "Haiti                 transport                1.794108e+10  \n",
       "Jamaica               transport                2.359830e+10  \n",
       "Kiribati              transport                2.171002e+08  \n",
       "Marshall Islands      transport                2.003310e+08  \n",
       "Micronesia, Fed. Sts. transport                3.495423e+08  \n",
       "Papua New Guinea      transport                2.118981e+10  \n",
       "Samoa                 transport                1.147754e+09  \n",
       "Solomon Islands       transport                1.242422e+09  \n",
       "St. Lucia             transport                1.905312e+09  \n",
       "Tonga                 transport                5.712251e+08  \n",
       "Belize                all_infra_but_transport  2.873784e+09  \n",
       "Fiji                  all_infra_but_transport  7.965477e+09  \n",
       "Haiti                 all_infra_but_transport  1.794108e+10  \n",
       "Jamaica               all_infra_but_transport  2.359830e+10  \n",
       "Kiribati              all_infra_but_transport  2.171002e+08  \n",
       "Marshall Islands      all_infra_but_transport  2.003310e+08  \n",
       "Micronesia, Fed. Sts. all_infra_but_transport  3.495423e+08  \n",
       "Papua New Guinea      all_infra_but_transport  2.118981e+10  \n",
       "Samoa                 all_infra_but_transport  1.147754e+09  \n",
       "Solomon Islands       all_infra_but_transport  1.242422e+09  \n",
       "St. Lucia             all_infra_but_transport  1.905312e+09  \n",
       "Tonga                 all_infra_but_transport  5.712251e+08  "
      ]
     },
     "execution_count": 257,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "all_infra[['Ktot','GDP']] = broadcast_simple(wb[['Ktot','GDP']],all_infra.index)\n",
    "all_infra"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 258,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "all_infra[\"share\"] = all_infra.infra_k/all_infra.Ktot"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "collapsed": true
   },
   "source": [
    "sids_infra[\"share_transport_in_K\"] = sids_infra.total_roads_value/sids_infra.Ktot\n",
    "sids_infra[\"share_transport_infra_but_buildings\"] = sids_infra.total_roads_value/(sids_infra.total_power_value+sids_infra.total_roads_value+sids_infra.total_water_value)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "collapsed": false
   },
   "source": [
    "sids_infra.dropna(subset=[\"share_transport_in_K\"]).to_csv(\"for_sids_report.csv\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 259,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "SIDS_infra = all_infra.dropna().share.unstack(\"sector\").assign(other_k=lambda df: 1 - df.transport - df.all_infra_but_transport).stack()\n",
    "SIDS_infra.name=\"share\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 262,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "pd.DataFrame(SIDS_infra).to_csv(\"inputs/sids_infra_stocks.csv\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 261,
   "metadata": {
    "collapsed": 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></th>\n",
       "      <th>share</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>country</th>\n",
       "      <th>sector</th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th rowspan=\"3\" valign=\"top\">Belize</th>\n",
       "      <th>all_infra_but_transport</th>\n",
       "      <td>0.493943</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>transport</th>\n",
       "      <td>0.234088</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>other_k</th>\n",
       "      <td>0.271969</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"3\" valign=\"top\">Fiji</th>\n",
       "      <th>all_infra_but_transport</th>\n",
       "      <td>0.323449</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>transport</th>\n",
       "      <td>0.130303</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>other_k</th>\n",
       "      <td>0.546248</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"3\" valign=\"top\">Haiti</th>\n",
       "      <th>all_infra_but_transport</th>\n",
       "      <td>0.208584</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>transport</th>\n",
       "      <td>0.072423</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>other_k</th>\n",
       "      <td>0.718993</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"3\" valign=\"top\">Jamaica</th>\n",
       "      <th>all_infra_but_transport</th>\n",
       "      <td>0.372426</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>transport</th>\n",
       "      <td>0.044061</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>other_k</th>\n",
       "      <td>0.583514</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"3\" valign=\"top\">Kiribati</th>\n",
       "      <th>all_infra_but_transport</th>\n",
       "      <td>0.459311</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>transport</th>\n",
       "      <td>0.502686</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>other_k</th>\n",
       "      <td>0.038003</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"3\" valign=\"top\">Papua New Guinea</th>\n",
       "      <th>all_infra_but_transport</th>\n",
       "      <td>0.228848</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>transport</th>\n",
       "      <td>0.107056</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>other_k</th>\n",
       "      <td>0.664095</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"3\" valign=\"top\">Solomon Islands</th>\n",
       "      <th>all_infra_but_transport</th>\n",
       "      <td>0.262207</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>transport</th>\n",
       "      <td>0.141125</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>other_k</th>\n",
       "      <td>0.596668</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"3\" valign=\"top\">St. Lucia</th>\n",
       "      <th>all_infra_but_transport</th>\n",
       "      <td>0.695867</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>transport</th>\n",
       "      <td>0.135446</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>other_k</th>\n",
       "      <td>0.168686</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"3\" valign=\"top\">Tonga</th>\n",
       "      <th>all_infra_but_transport</th>\n",
       "      <td>0.557667</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>transport</th>\n",
       "      <td>0.359542</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>other_k</th>\n",
       "      <td>0.082791</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                             share\n",
       "country          sector                           \n",
       "Belize           all_infra_but_transport  0.493943\n",
       "                 transport                0.234088\n",
       "                 other_k                  0.271969\n",
       "Fiji             all_infra_but_transport  0.323449\n",
       "                 transport                0.130303\n",
       "                 other_k                  0.546248\n",
       "Haiti            all_infra_but_transport  0.208584\n",
       "                 transport                0.072423\n",
       "                 other_k                  0.718993\n",
       "Jamaica          all_infra_but_transport  0.372426\n",
       "                 transport                0.044061\n",
       "                 other_k                  0.583514\n",
       "Kiribati         all_infra_but_transport  0.459311\n",
       "                 transport                0.502686\n",
       "                 other_k                  0.038003\n",
       "Papua New Guinea all_infra_but_transport  0.228848\n",
       "                 transport                0.107056\n",
       "                 other_k                  0.664095\n",
       "Solomon Islands  all_infra_but_transport  0.262207\n",
       "                 transport                0.141125\n",
       "                 other_k                  0.596668\n",
       "St. Lucia        all_infra_but_transport  0.695867\n",
       "                 transport                0.135446\n",
       "                 other_k                  0.168686\n",
       "Tonga            all_infra_but_transport  0.557667\n",
       "                 transport                0.359542\n",
       "                 other_k                  0.082791"
      ]
     },
     "execution_count": 261,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.DataFrame(SIDS_infra)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python [conda root]",
   "language": "python",
   "name": "conda-root-py"
  },
  "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": {
   "colors": {
    "hover_highlight": "#DAA520",
    "navigate_num": "#000000",
    "navigate_text": "#333333",
    "running_highlight": "#FF0000",
    "selected_highlight": "#FFD700",
    "sidebar_border": "#EEEEEE",
    "wrapper_background": "#FFFFFF"
   },
   "moveMenuLeft": true,
   "nav_menu": {
    "height": "30px",
    "width": "252px"
   },
   "navigate_menu": true,
   "number_sections": true,
   "sideBar": true,
   "threshold": 4,
   "toc_cell": false,
   "toc_section_display": "block",
   "toc_window_display": false,
   "widenNotebook": false
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}