pre_process GAR.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "collapsed": false,
    "run_control": {
     "frozen": false,
     "read_only": false
    }
   },
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "from replace_with_warning import replace_with_warning\n",
    "from pandas_helper import broadcast_simple\n",
    "import numpy as np\n",
    "\n",
    "from res_ind_lib import average_over_rp"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "collapsed": false,
    "run_control": {
     "frozen": false,
     "read_only": false
    }
   },
   "outputs": [],
   "source": [
    "iso3_to_wb = pd.read_csv(\"inputs/iso3_to_wb_name.csv\", index_col=\"iso3\", squeeze=True)\n",
    "\n",
    "\n",
    "#Names to WB names\n",
    "any_to_wb = pd.read_csv(\"inputs/any_name_to_wb_name.csv\",index_col=\"any\",squeeze=True)\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# AAL"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## agg data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {
    "collapsed": false,
    "run_control": {
     "frozen": false,
     "read_only": false
    }
   },
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\Users\\WB451855\\OneDrive - WBG\\resilience_indicator_SIDS\\replace_with_warning.py:21: UserWarning: These entries were not found in the dictionary:\n",
      "ESH\n",
      "  warnings.warn(\"These entries were not found in the dictionary:\\n\"+joiner.join(series_in[are_missing].unique()))\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "293387.32000000007"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "gar_aal_data = pd.read_csv(\"inputs/GAR15 results feb 2016_AALmundo.csv\", encoding=\"latin-1\", thousands=\",\", )\n",
    "\n",
    "# .drop([\"ISO\",\"Country\"],axis=1)\n",
    "\n",
    "#these are part of france\n",
    "gar_aal_data.ISO.replace([\"GUF\", \"GLP\", \"MTQ\", \"MYT\", \"REU\"],\"FRA\", inplace=True)\n",
    "\n",
    "#these ara part of the UK\n",
    "gar_aal_data.ISO.replace([\"FLK\", \"GIB\", \"MSR\"],\"GBR\", inplace=True)\n",
    "\n",
    "#wb spellings\n",
    "gar_aal_data = gar_aal_data.set_index(replace_with_warning(gar_aal_data.ISO,iso3_to_wb)).drop([\"ISO\",\"Country\"],axis=1)\n",
    "gar_aal_data.index.name=\"country\"\n",
    "\n",
    "\n",
    "#aggregates UK and France pieces to one country\n",
    "gar_aal_data =  gar_aal_data.sum(level=\"country\") \n",
    "\n",
    "\n",
    "#takes exposed value out\n",
    "gar_exposed_value = gar_aal_data.pop(\"EXPOSED VALUE\")\n",
    "\n",
    "\n",
    "#rename hazards\n",
    "gar_aal_data = gar_aal_data.rename(columns=lambda s:(s.lower()))\n",
    "gar_aal_data= gar_aal_data.rename(columns = {\"tropical cyclones\":\"cyclones\", \"riverine floods\":\"flood\", \"storm surge\":\"surge\"})\n",
    "\n",
    "\n",
    "gar_aal_data\n",
    "\n",
    "AAL = (gar_aal_data.T/gar_exposed_value).T\n",
    "AAL.head()\n",
    "\n",
    "gar_aal_data.sum().sum()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## wind and surge"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {
    "collapsed": false,
    "run_control": {
     "frozen": false,
     "read_only": false
    }
   },
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\Users\\WB451855\\OneDrive - WBG\\resilience_indicator_SIDS\\replace_with_warning.py:21: UserWarning: These entries were not found in the dictionary:\n",
      "ESH\n",
      "  warnings.warn(\"These entries were not found in the dictionary:\\n\"+joiner.join(series_in[are_missing].unique()))\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "Costa Rica                        1.000000e+00\n",
       "Tonga                             3.812629e-01\n",
       "St. Vincent and the Grenadines    2.115747e-16\n",
       "Venezuela, RB                     2.115004e-16\n",
       "United States                     2.085513e-16\n",
       "dtype: float64"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "\n",
    "def get_dk_over_k_from_file(path=\"inputs/GAR_data_surge.csv\"):\n",
    "    gardata_surge = pd.read_csv(path).dropna(axis=1, how=\"all\")\n",
    "\n",
    "    #these are part of france\n",
    "    gardata_surge.Country.replace([\"GUF\", \"GLP\", \"MTQ\", \"MYT\", \"REU\"],\"FRA\", inplace=True)\n",
    "\n",
    "    #these ara part of the UK\n",
    "    gardata_surge.Country.replace([\"FLK\", \"GIB\", \"MSR\"],\"GBR\", inplace=True)\n",
    "\n",
    "\n",
    "    gardata_surge = gardata_surge.set_index(replace_with_warning(gardata_surge.Country, iso3_to_wb))[[\"AAL\", \"Exposed Value (from GED)\"]] # drop([\"Country\",\"Country name\", \"Region\", \"GA\", \"Hazard\"],axis=1)\n",
    "\n",
    "    # gardata_surge.rename(columns=lambda s: float(s[4:-5]))\n",
    "\n",
    "    return gardata_surge[\"AAL\"].sum(level=0)/gardata_surge[\"Exposed Value (from GED)\"].sum(level=0)\n",
    "\n",
    "\n",
    "aal_surge = get_dk_over_k_from_file(\"inputs/GAR_data_surge.csv\")\n",
    "aal_wind = get_dk_over_k_from_file(\"inputs/GAR_data_wind.csv\")\n",
    "aal_recomputed =aal_surge +aal_wind\n",
    "\n",
    "\n",
    "((AAL.cyclones - aal_recomputed)/AAL.cyclones).abs().sort_values(ascending=False).head()\n",
    "\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {
    "collapsed": false,
    "run_control": {
     "frozen": false,
     "read_only": false
    }
   },
   "outputs": [],
   "source": [
    "c=\"Costa Rica\"\n",
    "f = (aal_surge/aal_recomputed).mean()\n",
    "aal_surge.ix[c] = f*AAL.cyclones[c]\n",
    "aal_wind.ix[c] = (1-f)*AAL.cyclones[c]\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## AAL splitted"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {
    "collapsed": false,
    "run_control": {
     "frozen": false,
     "read_only": false
    }
   },
   "outputs": [],
   "source": [
    "AAL_splitted = AAL.drop(\"cyclones\",axis=1).assign(wind= aal_wind, surge = aal_surge).rename(columns=dict(floods=\"flood\")).stack()\n",
    "AAL_splitted.index.names=[\"country\", \"hazard\"]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# PMLs and Exposed value"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {
    "collapsed": false,
    "run_control": {
     "frozen": false,
     "read_only": false
    }
   },
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\Users\\WB451855\\OneDrive - WBG\\resilience_indicator_SIDS\\replace_with_warning.py:21: UserWarning: These entries were not found in the dictionary:\n",
      "Western Sahara\n",
      "  warnings.warn(\"These entries were not found in the dictionary:\\n\"+joiner.join(series_in[are_missing].unique()))\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr>\n",
       "      <th>hazard</th>\n",
       "      <th>EXPOSED VALUE</th>\n",
       "      <th colspan=\"9\" halign=\"left\">EARTHQUAKE</th>\n",
       "      <th>...</th>\n",
       "      <th colspan=\"10\" halign=\"left\">Riverine floods</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>rp</th>\n",
       "      <th>National</th>\n",
       "      <th colspan=\"2\" halign=\"left\">20</th>\n",
       "      <th colspan=\"2\" halign=\"left\">50</th>\n",
       "      <th colspan=\"2\" halign=\"left\">100</th>\n",
       "      <th colspan=\"2\" halign=\"left\">250</th>\n",
       "      <th>500</th>\n",
       "      <th>...</th>\n",
       "      <th colspan=\"2\" halign=\"left\">100</th>\n",
       "      <th colspan=\"2\" halign=\"left\">250</th>\n",
       "      <th colspan=\"2\" halign=\"left\">500</th>\n",
       "      <th colspan=\"2\" halign=\"left\">1000</th>\n",
       "      <th colspan=\"2\" halign=\"left\">1500</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>what</th>\n",
       "      <th>million US$</th>\n",
       "      <th>million US$</th>\n",
       "      <th>[%]</th>\n",
       "      <th>million US$</th>\n",
       "      <th>[%]</th>\n",
       "      <th>million US$</th>\n",
       "      <th>[%]</th>\n",
       "      <th>million US$</th>\n",
       "      <th>[%]</th>\n",
       "      <th>million US$</th>\n",
       "      <th>...</th>\n",
       "      <th>million US$</th>\n",
       "      <th>[%]</th>\n",
       "      <th>million US$</th>\n",
       "      <th>[%]</th>\n",
       "      <th>million US$</th>\n",
       "      <th>[%]</th>\n",
       "      <th>million US$</th>\n",
       "      <th>[%]</th>\n",
       "      <th>million US$</th>\n",
       "      <th>[%]</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",
       "      <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>Afghanistan</th>\n",
       "      <td>60187.90</td>\n",
       "      <td>317.83</td>\n",
       "      <td>0.53</td>\n",
       "      <td>637.57</td>\n",
       "      <td>1.06</td>\n",
       "      <td>1034.61</td>\n",
       "      <td>1.72</td>\n",
       "      <td>1800.74</td>\n",
       "      <td>2.99</td>\n",
       "      <td>2585.25</td>\n",
       "      <td>...</td>\n",
       "      <td>1304.836191</td>\n",
       "      <td>2.167938</td>\n",
       "      <td>1662.178063</td>\n",
       "      <td>2.761648</td>\n",
       "      <td>1749.645544</td>\n",
       "      <td>2.906972</td>\n",
       "      <td>1924.580505</td>\n",
       "      <td>3.197620</td>\n",
       "      <td>2099.515466</td>\n",
       "      <td>3.488268</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Albania</th>\n",
       "      <td>40459.70</td>\n",
       "      <td>112.83</td>\n",
       "      <td>0.28</td>\n",
       "      <td>239.07</td>\n",
       "      <td>0.59</td>\n",
       "      <td>412.61</td>\n",
       "      <td>1.02</td>\n",
       "      <td>817.76</td>\n",
       "      <td>2.02</td>\n",
       "      <td>1308.38</td>\n",
       "      <td>...</td>\n",
       "      <td>559.456815</td>\n",
       "      <td>1.382751</td>\n",
       "      <td>985.990186</td>\n",
       "      <td>2.436969</td>\n",
       "      <td>1152.001339</td>\n",
       "      <td>2.847281</td>\n",
       "      <td>1342.639991</td>\n",
       "      <td>3.318463</td>\n",
       "      <td>1434.570014</td>\n",
       "      <td>3.545676</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Algeria</th>\n",
       "      <td>899206.00</td>\n",
       "      <td>2965.76</td>\n",
       "      <td>0.33</td>\n",
       "      <td>5719.85</td>\n",
       "      <td>0.64</td>\n",
       "      <td>8834.51</td>\n",
       "      <td>0.98</td>\n",
       "      <td>14487.39</td>\n",
       "      <td>1.61</td>\n",
       "      <td>19624.90</td>\n",
       "      <td>...</td>\n",
       "      <td>2455.765743</td>\n",
       "      <td>0.273104</td>\n",
       "      <td>5227.693307</td>\n",
       "      <td>0.581368</td>\n",
       "      <td>6507.867777</td>\n",
       "      <td>0.723735</td>\n",
       "      <td>7652.708961</td>\n",
       "      <td>0.851052</td>\n",
       "      <td>8032.451716</td>\n",
       "      <td>0.893283</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>American Samoa</th>\n",
       "      <td>1930.49</td>\n",
       "      <td>0.39</td>\n",
       "      <td>0.02</td>\n",
       "      <td>2.46</td>\n",
       "      <td>0.13</td>\n",
       "      <td>7.18</td>\n",
       "      <td>0.37</td>\n",
       "      <td>21.09</td>\n",
       "      <td>1.09</td>\n",
       "      <td>39.03</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Andorra</th>\n",
       "      <td>8381.65</td>\n",
       "      <td>0.19</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.39</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.64</td>\n",
       "      <td>0.01</td>\n",
       "      <td>1.59</td>\n",
       "      <td>0.02</td>\n",
       "      <td>3.66</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 71 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "hazard         EXPOSED VALUE  EARTHQUAKE                                      \\\n",
       "rp                  National          20                50               100   \n",
       "what             million US$ million US$   [%] million US$   [%] million US$   \n",
       "country                                                                        \n",
       "Afghanistan         60187.90      317.83  0.53      637.57  1.06     1034.61   \n",
       "Albania             40459.70      112.83  0.28      239.07  0.59      412.61   \n",
       "Algeria            899206.00     2965.76  0.33     5719.85  0.64     8834.51   \n",
       "American Samoa       1930.49        0.39  0.02        2.46  0.13        7.18   \n",
       "Andorra              8381.65        0.19  0.00        0.39  0.00        0.64   \n",
       "\n",
       "hazard                                                ...    Riverine floods  \\\n",
       "rp                           250               500    ...                100   \n",
       "what             [%] million US$   [%] million US$    ...        million US$   \n",
       "country                                               ...                      \n",
       "Afghanistan     1.72     1800.74  2.99     2585.25    ...        1304.836191   \n",
       "Albania         1.02      817.76  2.02     1308.38    ...         559.456815   \n",
       "Algeria         0.98    14487.39  1.61    19624.90    ...        2455.765743   \n",
       "American Samoa  0.37       21.09  1.09       39.03    ...                NaN   \n",
       "Andorra         0.01        1.59  0.02        3.66    ...                NaN   \n",
       "\n",
       "hazard                                                                  \\\n",
       "rp                                250                    500             \n",
       "what                 [%]  million US$       [%]  million US$       [%]   \n",
       "country                                                                  \n",
       "Afghanistan     2.167938  1662.178063  2.761648  1749.645544  2.906972   \n",
       "Albania         1.382751   985.990186  2.436969  1152.001339  2.847281   \n",
       "Algeria         0.273104  5227.693307  0.581368  6507.867777  0.723735   \n",
       "American Samoa       NaN          NaN       NaN          NaN       NaN   \n",
       "Andorra              NaN          NaN       NaN          NaN       NaN   \n",
       "\n",
       "hazard                                                        \n",
       "rp                     1000                   1500            \n",
       "what            million US$       [%]  million US$       [%]  \n",
       "country                                                       \n",
       "Afghanistan     1924.580505  3.197620  2099.515466  3.488268  \n",
       "Albania         1342.639991  3.318463  1434.570014  3.545676  \n",
       "Algeria         7652.708961  0.851052  8032.451716  0.893283  \n",
       "American Samoa          NaN       NaN          NaN       NaN  \n",
       "Andorra                 NaN       NaN          NaN       NaN  \n",
       "\n",
       "[5 rows x 71 columns]"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "gardata =pd.read_csv(\"inputs/GAR15 results feb 2016_PML mundo.csv\", encoding=\"latin-1\", header=[0,1,2], index_col=0)#.sort_index(axis=1)\n",
    "gardata.index.name = \"country\"\n",
    "\n",
    "gardata = gardata.reset_index()\n",
    "\n",
    "gardata.columns.names=[\"hazard\",\"rp\",\"what\"]\n",
    "\n",
    "#these are part of france\n",
    "gardata.country = gardata.country.replace([\"French Guiana\", \"Guadeloupe\", \"Martinique\", \"Mayotte\", \"Réunion\"],\"France\")\n",
    "\n",
    "#these ara part of the UK\n",
    "gardata.country = gardata.country.replace([\"Falkland Islands (Malvinas)\", \"Gibraltar\", \"Montserrat\"],\"United Kingdom\")\n",
    "\n",
    "gardata.country = replace_with_warning(gardata.country, any_to_wb.dropna())\n",
    "\n",
    "gardata =gardata.set_index(\"country\")\n",
    "\n",
    "#aggregates UK and France\n",
    "# gardata = gardata.sum(level=\"country\", axis=1)\n",
    "\n",
    "\n",
    "#Other format for zero\n",
    "gardata = gardata.replace(\"---\",0).astype(float)\n",
    "\n",
    "#aggregates france and uk\n",
    "gardata = gardata.sum(level=\"country\")\n",
    "\n",
    "gardata.head()\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {
    "collapsed": false,
    "run_control": {
     "frozen": false,
     "read_only": false
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "country      hazard      rp   \n",
       "Afghanistan  earthquake  20.0     0.005281\n",
       "                         50.0     0.010593\n",
       "                         100.0    0.017190\n",
       "                         250.0    0.029919\n",
       "                         500.0    0.042953\n",
       "dtype: float64"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "\n",
    "#Looks at the exposed value, for comparison\n",
    "exposed_value_GAR = gardata.pop(\"EXPOSED VALUE\").squeeze()\n",
    "# df[\"exposed_value_GAR\"].head()\n",
    "\n",
    "\n",
    "#rename hazards\n",
    "gardata = gardata.rename(columns=lambda s:(s.lower()))\n",
    "gardata= gardata.rename(columns = {\"wiind\":\"wind\", \"riverine floods\":\"flood\", \"storm surge\":\"surge\"})\n",
    "\n",
    "#rp to floats\n",
    "def str_to_float(s):\n",
    "    try:\n",
    "        return (float(s))\n",
    "    except ValueError:\n",
    "        return s\n",
    "gardata = gardata.rename(columns=str_to_float)\n",
    "\n",
    "gardata.head()\n",
    "\n",
    "\n",
    "\n",
    "#Asset losses per event\n",
    "dK_gar = gardata.swaplevel(\"what\",\"hazard\",axis=1)[\"million us$\"].swaplevel(\"rp\",\"hazard\",axis=1).stack(level=[\"hazard\",\"rp\"], dropna=False).sort_index()\n",
    "\n",
    "#Exposed value per event\n",
    "ev_gar=  broadcast_simple(exposed_value_GAR,dK_gar.index)\n",
    "\n",
    "#Fraction of value destroyed\n",
    "frac_value_destroyed_gar = dK_gar/ev_gar\n",
    "\n",
    "frac_value_destroyed_gar.dropna().unstack(\"country\").shape\n",
    "\n",
    "frac_value_destroyed_gar.to_csv(\"intermediate/frac_value_destroyed_gar.csv\", encoding=\"utf-8\", header=True)\n",
    "\n",
    "frac_value_destroyed_gar.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {
    "collapsed": false,
    "run_control": {
     "frozen": false,
     "read_only": false
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "country\n",
       "Afghanistan      0.0\n",
       "New Caledonia    0.0\n",
       "New Zealand      0.0\n",
       "Nicaragua        0.0\n",
       "Niger            0.0\n",
       "dtype: float64"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#Check. should be 0\n",
    "(exposed_value_GAR - gar_exposed_value).abs().sort_values(ascending=True).head(5)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Combines exposed value to frac destroyed from events"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "ename": "FileNotFoundError",
     "evalue": "File b'intermediate/capital_losses_from_GAR_events.csv' does not exist",
     "output_type": "error",
     "traceback": [
      "\u001b[0;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[0;31mFileNotFoundError\u001b[0m                         Traceback (most recent call last)",
      "\u001b[0;32m<ipython-input-10-32accfb2fd8d>\u001b[0m in \u001b[0;36m<module>\u001b[0;34m()\u001b[0m\n\u001b[0;32m----> 1\u001b[0;31m \u001b[0mcapital_losses_from_GAR_events\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mpd\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mread_csv\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;34m\"intermediate/capital_losses_from_GAR_events.csv\"\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mindex_col\u001b[0m\u001b[1;33m=\u001b[0m\u001b[1;33m[\u001b[0m\u001b[1;34m\"country\"\u001b[0m\u001b[1;33m,\u001b[0m\u001b[1;34m\"hazard\"\u001b[0m\u001b[1;33m,\u001b[0m\u001b[1;34m\"rp\"\u001b[0m\u001b[1;33m]\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0msqueeze\u001b[0m\u001b[1;33m=\u001b[0m\u001b[1;32mTrue\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m",
      "\u001b[0;32mC:\\Anaconda3\\lib\\site-packages\\pandas\\io\\parsers.py\u001b[0m in \u001b[0;36mparser_f\u001b[0;34m(filepath_or_buffer, sep, delimiter, header, names, index_col, usecols, squeeze, prefix, mangle_dupe_cols, dtype, engine, converters, true_values, false_values, skipinitialspace, skiprows, nrows, na_values, keep_default_na, na_filter, verbose, skip_blank_lines, parse_dates, infer_datetime_format, keep_date_col, date_parser, dayfirst, iterator, chunksize, compression, thousands, decimal, lineterminator, quotechar, quoting, escapechar, comment, encoding, dialect, tupleize_cols, error_bad_lines, warn_bad_lines, skipfooter, skip_footer, doublequote, delim_whitespace, as_recarray, compact_ints, use_unsigned, low_memory, buffer_lines, memory_map, float_precision)\u001b[0m\n\u001b[1;32m    644\u001b[0m                     skip_blank_lines=skip_blank_lines)\n\u001b[1;32m    645\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m--> 646\u001b[0;31m         \u001b[1;32mreturn\u001b[0m \u001b[0m_read\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mfilepath_or_buffer\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mkwds\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m    647\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m    648\u001b[0m     \u001b[0mparser_f\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0m__name__\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mname\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[0;32mC:\\Anaconda3\\lib\\site-packages\\pandas\\io\\parsers.py\u001b[0m in \u001b[0;36m_read\u001b[0;34m(filepath_or_buffer, kwds)\u001b[0m\n\u001b[1;32m    387\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m    388\u001b[0m     \u001b[1;31m# Create the parser.\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m--> 389\u001b[0;31m     \u001b[0mparser\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mTextFileReader\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mfilepath_or_buffer\u001b[0m\u001b[1;33m,\u001b[0m \u001b[1;33m**\u001b[0m\u001b[0mkwds\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m    390\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m    391\u001b[0m     \u001b[1;32mif\u001b[0m \u001b[1;33m(\u001b[0m\u001b[0mnrows\u001b[0m \u001b[1;32mis\u001b[0m \u001b[1;32mnot\u001b[0m \u001b[1;32mNone\u001b[0m\u001b[1;33m)\u001b[0m \u001b[1;32mand\u001b[0m \u001b[1;33m(\u001b[0m\u001b[0mchunksize\u001b[0m \u001b[1;32mis\u001b[0m \u001b[1;32mnot\u001b[0m \u001b[1;32mNone\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[0;32mC:\\Anaconda3\\lib\\site-packages\\pandas\\io\\parsers.py\u001b[0m in \u001b[0;36m__init__\u001b[0;34m(self, f, engine, **kwds)\u001b[0m\n\u001b[1;32m    728\u001b[0m             \u001b[0mself\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0moptions\u001b[0m\u001b[1;33m[\u001b[0m\u001b[1;34m'has_index_names'\u001b[0m\u001b[1;33m]\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mkwds\u001b[0m\u001b[1;33m[\u001b[0m\u001b[1;34m'has_index_names'\u001b[0m\u001b[1;33m]\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m    729\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m--> 730\u001b[0;31m         \u001b[0mself\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0m_make_engine\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mself\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mengine\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m    731\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m    732\u001b[0m     \u001b[1;32mdef\u001b[0m \u001b[0mclose\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mself\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[0;32mC:\\Anaconda3\\lib\\site-packages\\pandas\\io\\parsers.py\u001b[0m in \u001b[0;36m_make_engine\u001b[0;34m(self, engine)\u001b[0m\n\u001b[1;32m    921\u001b[0m     \u001b[1;32mdef\u001b[0m \u001b[0m_make_engine\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mself\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mengine\u001b[0m\u001b[1;33m=\u001b[0m\u001b[1;34m'c'\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m    922\u001b[0m         \u001b[1;32mif\u001b[0m \u001b[0mengine\u001b[0m \u001b[1;33m==\u001b[0m \u001b[1;34m'c'\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m--> 923\u001b[0;31m             \u001b[0mself\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0m_engine\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mCParserWrapper\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mself\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mf\u001b[0m\u001b[1;33m,\u001b[0m \u001b[1;33m**\u001b[0m\u001b[0mself\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0moptions\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m    924\u001b[0m         \u001b[1;32melse\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m    925\u001b[0m             \u001b[1;32mif\u001b[0m \u001b[0mengine\u001b[0m \u001b[1;33m==\u001b[0m \u001b[1;34m'python'\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[0;32mC:\\Anaconda3\\lib\\site-packages\\pandas\\io\\parsers.py\u001b[0m in \u001b[0;36m__init__\u001b[0;34m(self, src, **kwds)\u001b[0m\n\u001b[1;32m   1388\u001b[0m         \u001b[0mkwds\u001b[0m\u001b[1;33m[\u001b[0m\u001b[1;34m'allow_leading_cols'\u001b[0m\u001b[1;33m]\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mself\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mindex_col\u001b[0m \u001b[1;32mis\u001b[0m \u001b[1;32mnot\u001b[0m \u001b[1;32mFalse\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m   1389\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m-> 1390\u001b[0;31m         \u001b[0mself\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0m_reader\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0m_parser\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mTextReader\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0msrc\u001b[0m\u001b[1;33m,\u001b[0m \u001b[1;33m**\u001b[0m\u001b[0mkwds\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m   1391\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m   1392\u001b[0m         \u001b[1;31m# XXX\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[0;32mpandas\\parser.pyx\u001b[0m in \u001b[0;36mpandas.parser.TextReader.__cinit__ (pandas\\parser.c:4184)\u001b[0;34m()\u001b[0m\n",
      "\u001b[0;32mpandas\\parser.pyx\u001b[0m in \u001b[0;36mpandas.parser.TextReader._setup_parser_source (pandas\\parser.c:8449)\u001b[0;34m()\u001b[0m\n",
      "\u001b[0;31mFileNotFoundError\u001b[0m: File b'intermediate/capital_losses_from_GAR_events.csv' does not exist"
     ]
    }
   ],
   "source": [
    "capital_losses_from_GAR_events = pd.read_csv(\"intermediate/capital_losses_from_GAR_events.csv\", index_col=[\"country\",\"hazard\",\"rp\"], squeeze=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "frac_cap_distroyed_from_events = capital_losses_from_GAR_events/ev_gar.median(level=\"country\")\n",
    "frac_cap_distroyed_from_events.to_csv(\"intermediate/frac_cap_distroyed_from_events.csv\", header=True)\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "(average_over_rp(frac_cap_distroyed_from_events).squeeze()/ AAL_splitted ).replace(np.inf,np.nan).dropna().sort_values()\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": []
  },
  {
   "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": [
    "# adds event to PML to complete AAL  (old bad method)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false,
    "run_control": {
     "frozen": false,
     "read_only": false
    }
   },
   "outputs": [],
   "source": [
    "frac_value_destroyed_gar.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false,
    "run_control": {
     "frozen": false,
     "read_only": false
    }
   },
   "outputs": [],
   "source": [
    "AAL_splitted.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    " how much greater AAL is than sum of PML"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false,
    "run_control": {
     "frozen": false,
     "read_only": false
    }
   },
   "outputs": [],
   "source": [
    "paf= (AAL_splitted / average_over_rp(frac_value_destroyed_gar).squeeze()).replace(np.inf,np.nan).sort_values(ascending=False)\n",
    "paf[paf>5]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false,
    "run_control": {
     "frozen": false,
     "read_only": false
    }
   },
   "outputs": [],
   "source": [
    "paf.dropna().tail(30)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false,
    "run_control": {
     "frozen": false,
     "read_only": false
    }
   },
   "outputs": [],
   "source": [
    "frac_value_destroyed_gar[\"Japan\"][\"flood\"]*1e6"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true,
    "run_control": {
     "frozen": false,
     "read_only": false
    }
   },
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## add frequent events"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false,
    "run_control": {
     "frozen": false,
     "read_only": false
    }
   },
   "outputs": [],
   "source": [
    "last_rp = 20\n",
    "new_rp = 1\n",
    "\n",
    "added_proba = 1/new_rp - 1/last_rp\n",
    "\n",
    "new_frac_destroyed = (AAL_splitted - average_over_rp(frac_value_destroyed_gar).squeeze())/(added_proba)\n",
    "   \n",
    "#REMOVES 'tsunamis' and 'earthquakes' from this thing\n",
    "# new_frac_destroyed = pd.DataFrame(new_frac_destroyed).query(\"hazard not in ['tsunami', 'earthquake']\").squeeze()\n",
    "new_frac_destroyed.describe(), new_frac_destroyed.sample(10)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false,
    "run_control": {
     "frozen": false,
     "read_only": false
    }
   },
   "outputs": [],
   "source": [
    "hop = frac_value_destroyed_gar.unstack()\n",
    "hop[new_rp]=   new_frac_destroyed\n",
    "hop= hop.sort_index(axis=1)\n",
    "hop.head()\n",
    "\n",
    "\n",
    "frac_value_destroyed_gar_completed = hop.stack()\n",
    "frac_value_destroyed_gar_completed.head()  #this shows earthaquke was not updated"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false,
    "run_control": {
     "frozen": false,
     "read_only": false
    }
   },
   "outputs": [],
   "source": [
    "frac_value_destroyed_gar_completed.sample(10)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### double check. expecint zeroes expect for quakes and tsunamis"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false,
    "run_control": {
     "frozen": false,
     "read_only": false
    }
   },
   "outputs": [],
   "source": [
    "(average_over_rp(frac_value_destroyed_gar_completed).squeeze() - AAL_splitted).abs().sort_values(ascending=False).sample(10)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### places where new values are higher than values for 20"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false,
    "run_control": {
     "frozen": false,
     "read_only": false
    }
   },
   "outputs": [],
   "source": [
    "test = frac_value_destroyed_gar_completed.unstack().replace(0,np.nan).dropna().assign(test=lambda x:x[new_rp]/x[20]).test\n",
    "\n",
    "max_relative_exp = .8\n",
    "\n",
    "overflow_frequent_countries = test[test>max_relative_exp].index\n",
    "print(\"overflow in {n} (country, event)\".format(n=len(overflow_frequent_countries)))\n",
    "\n",
    "test[overflow_frequent_countries].sort_values(ascending=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## for this places, add infrequent events\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false,
    "run_control": {
     "frozen": false,
     "read_only": false
    }
   },
   "outputs": [],
   "source": [
    "hop=frac_value_destroyed_gar_completed.unstack()\n",
    "hop[1]=hop[1].clip(upper=max_relative_exp*hop[20])\n",
    "frac_value_destroyed_gar_completed = hop.stack()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false,
    "run_control": {
     "frozen": false,
     "read_only": false
    }
   },
   "outputs": [],
   "source": [
    "new_rp = 2000\n",
    "\n",
    "added_proba = 1/2000\n",
    "\n",
    "new_frac_destroyed = (AAL_splitted - average_over_rp(frac_value_destroyed_gar_completed).squeeze())/(added_proba)\n",
    "   \n",
    "#REMOVES 'tsunamis' and 'earthquakes' from this thing\n",
    "# new_frac_destroyed = pd.DataFrame(new_frac_destroyed).query(\"hazard in ['tsunami', 'earthquake']\").squeeze()\n",
    "new_frac_destroyed.describe(), new_frac_destroyed.sample(10)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false,
    "run_control": {
     "frozen": false,
     "read_only": false
    }
   },
   "outputs": [],
   "source": [
    "hop = frac_value_destroyed_gar.unstack()\n",
    "hop[new_rp]=   new_frac_destroyed\n",
    "hop= hop.sort_index(axis=1)\n",
    "hop.head()\n",
    "\n",
    "\n",
    "frac_value_destroyed_gar_completed = hop.stack()\n",
    "frac_value_destroyed_gar_completed.head(10)  "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false,
    "run_control": {
     "frozen": false,
     "read_only": false
    }
   },
   "outputs": [],
   "source": [
    "test = frac_value_destroyed_gar_completed.unstack().replace(0,np.nan).dropna().assign(test=lambda x:x[new_rp]/x[1500]).test\n",
    "test[test>1.1].sort_values(ascending=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false,
    "run_control": {
     "frozen": false,
     "read_only": false
    }
   },
   "outputs": [],
   "source": [
    "test[test>2].shape, test.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true,
    "run_control": {
     "frozen": false,
     "read_only": false
    }
   },
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false,
    "run_control": {
     "frozen": false,
     "read_only": false
    }
   },
   "outputs": [],
   "source": [
    "frac_value_destroyed_gar_completed[\"United States\"][\"earthquake\"]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false,
    "run_control": {
     "frozen": false,
     "read_only": false
    }
   },
   "outputs": [],
   "source": [
    "AAL_splitted.ix[\"United States\"]*100"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false,
    "run_control": {
     "frozen": false,
     "read_only": false
    }
   },
   "outputs": [],
   "source": [
    "pd.DataFrame((average_over_rp(frac_value_destroyed_gar).squeeze()/AAL_splitted).replace(0,np.nan).dropna().sort_values())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true,
    "run_control": {
     "frozen": false,
     "read_only": false
    }
   },
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true,
    "run_control": {
     "frozen": false,
     "read_only": false
    }
   },
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python [default]",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.5.2"
  },
  "nav_menu": {},
  "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": "236px",
    "width": "252px"
   },
   "navigate_menu": true,
   "number_sections": true,
   "sideBar": false,
   "threshold": 4,
   "toc_cell": false,
   "toc_section_display": "block",
   "toc_window_display": true,
   "widenNotebook": false
  },
  "toc_position": {
   "height": "300px",
   "left": "1px",
   "right": "20px",
   "top": "105.994px",
   "width": "133px"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 0
}