02_Cluster similiar regions & add Worldbank data.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Notebook for clustering regions & add data about birth rate and poverty\n",
    "\n",
    "* Cluster similiar provinces within nations to larger regions to reduce computational requirements\n",
    "\n",
    "\n",
    "* <font color='red'>WARNING: Clustering is arbitrary based on yearly average instead of similiarity of timeseries!</font>\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "%matplotlib inline\n",
    "# Import libraries for geomatics\n",
    "import geopandas as gp\n",
    "\n",
    "# Import libraries for data handling\n",
    "import pandas as pd\n",
    "import numpy as np"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "shapefilepath =  'preprocessing_results/shapefile/'\n",
    "\n",
    "df_shapefile_provinces = gp.read_file(filename = 'preprocessing_results/geojson/provinces_monthly_norm01.geojson')\n",
    "df_shp_norm01 = df_shapefile_provinces.copy()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index([ u'10Aegypti', u'10Albopict', u'10combi_me',  u'11Aegypti',\n",
       "       u'11Albopict', u'11combined',  u'12Aegypti', u'12Albopict',\n",
       "       u'12combined',   u'1Aegypti',  u'1Albopict',  u'1combined',\n",
       "         u'2Aegypti',  u'2Albopict',  u'2combined',   u'3Aegypti',\n",
       "        u'3Albopict',  u'3combined',   u'4Aegypti',  u'4Albopict',\n",
       "        u'4combined',   u'5Aegypti',  u'5Albopict',  u'5combined',\n",
       "         u'6Aegypti',  u'6Albopict',  u'6combined',   u'7Aegypti',\n",
       "        u'7Albopict',  u'7combined',   u'8Aegypti',  u'8Albopict',\n",
       "        u'8combined',   u'9Aegypti',  u'9Albopict',  u'9combined',\n",
       "       u'Messinamea',    u'adm0_a3',    u'country',   u'geometry',\n",
       "       u'perc_pover', u'population'],\n",
       "      dtype='object')"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_shp_norm01.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "4458"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(df_shapefile_provinces)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "geopandas.geodataframe.GeoDataFrame"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "type(df_shapefile_provinces)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Compute yearly average for  both mosquitoes  "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "import re\n",
    "def subset_df(str_filter):\n",
    "    col_Aegypti = filter(lambda k: (str_filter in k), df_shp_norm01.columns.tolist())\n",
    "    df_interactive_Aegypti = df_shp_norm01[col_Aegypti].copy()\n",
    "    lst = [ int(re.findall('\\d+', s)[0]) for s in df_interactive_Aegypti.columns ]\n",
    "    rename_dict = dict(zip(df_interactive_Aegypti.columns, lst))\n",
    "    df_interactive_Aegypti.rename(columns=rename_dict, inplace=True)\n",
    "    return df_interactive_Aegypti"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "df_shp_norm01['year_all'] = subset_df('combi').mean(axis=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "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>10Aegypti</th>\n",
       "      <th>10Albopict</th>\n",
       "      <th>10combi_me</th>\n",
       "      <th>11Aegypti</th>\n",
       "      <th>11Albopict</th>\n",
       "      <th>11combined</th>\n",
       "      <th>12Aegypti</th>\n",
       "      <th>12Albopict</th>\n",
       "      <th>12combined</th>\n",
       "      <th>1Aegypti</th>\n",
       "      <th>...</th>\n",
       "      <th>9Aegypti</th>\n",
       "      <th>9Albopict</th>\n",
       "      <th>9combined</th>\n",
       "      <th>Messinamea</th>\n",
       "      <th>adm0_a3</th>\n",
       "      <th>country</th>\n",
       "      <th>geometry</th>\n",
       "      <th>perc_pover</th>\n",
       "      <th>population</th>\n",
       "      <th>year_all</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0.308523</td>\n",
       "      <td>0.041353</td>\n",
       "      <td>0.308523</td>\n",
       "      <td>0.305445</td>\n",
       "      <td>0.100243</td>\n",
       "      <td>0.305445</td>\n",
       "      <td>0.231323</td>\n",
       "      <td>0.110166</td>\n",
       "      <td>0.231323</td>\n",
       "      <td>0.109213</td>\n",
       "      <td>...</td>\n",
       "      <td>0.122034</td>\n",
       "      <td>0.097954</td>\n",
       "      <td>0.1446</td>\n",
       "      <td>0.226241</td>\n",
       "      <td>ABW</td>\n",
       "      <td>Aruba</td>\n",
       "      <td>POLYGON ((-69.99693762899992 12.57758209800004...</td>\n",
       "      <td>12.899919</td>\n",
       "      <td>87896.878144</td>\n",
       "      <td>0.1577</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>1 rows × 43 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "   10Aegypti  10Albopict  10combi_me  11Aegypti  11Albopict  11combined  \\\n",
       "0   0.308523    0.041353    0.308523   0.305445    0.100243    0.305445   \n",
       "\n",
       "   12Aegypti  12Albopict  12combined  1Aegypti    ...     9Aegypti  9Albopict  \\\n",
       "0   0.231323    0.110166    0.231323  0.109213    ...     0.122034   0.097954   \n",
       "\n",
       "   9combined  Messinamea  adm0_a3  country  \\\n",
       "0     0.1446    0.226241      ABW    Aruba   \n",
       "\n",
       "                                            geometry  perc_pover  \\\n",
       "0  POLYGON ((-69.99693762899992 12.57758209800004...   12.899919   \n",
       "\n",
       "     population  year_all  \n",
       "0  87896.878144    0.1577  \n",
       "\n",
       "[1 rows x 43 columns]"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_shp_norm01.head(1)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Discretize elements into equal-ranged buckets based on Env_suitability. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(-8.27e-05, 0.0244]    1357\n",
       "(0.121, 0.145]          606\n",
       "(0.0971, 0.121]         482\n",
       "(0.145, 0.17]           471\n",
       "(0.0244, 0.0486]        431\n",
       "(0.0486, 0.0728]        408\n",
       "(0.0728, 0.0971]        375\n",
       "(0.17, 0.194]           241\n",
       "(0.194, 0.218]           69\n",
       "(0.218, 0.242]           18\n",
       "Name: year_all, dtype: int64"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.cut(df_shp_norm01['year_all'], 10).value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "df_shp_norm01['category'] = pd.cut(df_shp_norm01['year_all'], 10)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Group first two groups together "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "set(df_shp_norm01['category'].tolist())\n",
    "m = {'(-8.27e-05, 0.0244]': 1,  \n",
    "     '(0.0244, 0.0486]': 1, \n",
    "      '(0.0486, 0.0728]': 2, '(0.0728, 0.0971]': 3,  '(0.0971, 0.121]' :4 , \n",
    "    '(0.121, 0.145]' : 5, '(0.145, 0.17]': 6, '(0.17, 0.194]': 7, '(0.194, 0.218]': 8 , '(0.218, 0.242]':  9}\n",
    "\n",
    "dic = lambda x: m[x]\n",
    "df_shp_norm01['category'] = df_shp_norm01['category'].apply(dic)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Group all elements by the country and its Env_suitability bin (>10 min computation period)\n",
    "Compute the cumulative population, mean poverty and mean Env_suitability of each group and add as extra column to each element of df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "col_Zika = filter(lambda k: ('Aegypti' in k) or ('Albopi' in k) or  ('combi' in k) or ('Messina' in k), df_shp_norm01.columns.tolist())\n",
    "\n",
    "aggregation = dict(zip(col_Zika[::1], [np.mean]*len(col_Zika)))\n",
    "aggregation['population'] = np.sum\n",
    "aggregation['perc_pover'] = np.mean"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "df_shp_norm01 = df_shp_norm01.dissolve(by=['country', 'category'],\n",
    "                                           aggfunc=aggregation,\n",
    "                                           as_index=True)\n",
    "df_shp_norm01.reset_index(inplace=True)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "765"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(df_shp_norm01)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Add continent and country-code to shapefile with spatial join\n",
    "\n",
    "- There may be easier ways to achieve this than reading and mapping this from the country shapefile from naturalearthdata but it works.\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "shapefile = 'raw_data/shapefile/naturalearthdata/ne_50m_admin_0_countries.shp'\n",
    "df_shapefile_countries = gp.GeoDataFrame.from_file(shapefile)\n",
    "cols = ['geometry', 'continent', 'adm0_a3', 'admin']\n",
    "df_shapefile_countries = df_shapefile_countries[cols]\n",
    "\n",
    "df_shp_norm01.crs = df_shapefile_countries.crs\n",
    "\n",
    "dic_ccode_continent = dict(zip(df_shapefile_countries['adm0_a3'],df_shapefile_countries['continent']))\n",
    "dic_country_ccode = dict(zip(df_shapefile_countries.admin,df_shapefile_countries['adm0_a3']))\n",
    "\n",
    "df_shp_norm01[\"adm0_a3\"] = df_shp_norm01[\"country\"].map(dic_country_ccode)\n",
    "df_shp_norm01[\"continent\"] = df_shp_norm01[\"adm0_a3\"].map(dic_ccode_continent)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "CRS {'init': u'epsg:4326'}\n",
      "Number of map elements:  765\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>country</th>\n",
       "      <th>category</th>\n",
       "      <th>geometry</th>\n",
       "      <th>5Aegypti</th>\n",
       "      <th>population</th>\n",
       "      <th>12combined</th>\n",
       "      <th>12Aegypti</th>\n",
       "      <th>2combined</th>\n",
       "      <th>8Aegypti</th>\n",
       "      <th>9combined</th>\n",
       "      <th>...</th>\n",
       "      <th>3combined</th>\n",
       "      <th>10combi_me</th>\n",
       "      <th>4Albopict</th>\n",
       "      <th>3Aegypti</th>\n",
       "      <th>11Albopict</th>\n",
       "      <th>8Albopict</th>\n",
       "      <th>7combined</th>\n",
       "      <th>11combined</th>\n",
       "      <th>adm0_a3</th>\n",
       "      <th>continent</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Afghanistan</td>\n",
       "      <td>1</td>\n",
       "      <td>POLYGON ((69.48586754674983 34.15900055643047,...</td>\n",
       "      <td>0.013484</td>\n",
       "      <td>2.715357e+07</td>\n",
       "      <td>0.007285</td>\n",
       "      <td>0.003349</td>\n",
       "      <td>0.005082</td>\n",
       "      <td>0.022927</td>\n",
       "      <td>0.022721</td>\n",
       "      <td>...</td>\n",
       "      <td>0.005834</td>\n",
       "      <td>0.036989</td>\n",
       "      <td>0.001669</td>\n",
       "      <td>0.005626</td>\n",
       "      <td>0.01632</td>\n",
       "      <td>0.01097</td>\n",
       "      <td>0.025059</td>\n",
       "      <td>0.017434</td>\n",
       "      <td>AFG</td>\n",
       "      <td>Asia</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>1 rows × 44 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "       country  category                                           geometry  \\\n",
       "0  Afghanistan         1  POLYGON ((69.48586754674983 34.15900055643047,...   \n",
       "\n",
       "   5Aegypti    population  12combined  12Aegypti  2combined  8Aegypti  \\\n",
       "0  0.013484  2.715357e+07    0.007285   0.003349   0.005082  0.022927   \n",
       "\n",
       "   9combined    ...      3combined  10combi_me  4Albopict  3Aegypti  \\\n",
       "0   0.022721    ...       0.005834    0.036989   0.001669  0.005626   \n",
       "\n",
       "   11Albopict  8Albopict  7combined  11combined  adm0_a3  continent  \n",
       "0     0.01632    0.01097   0.025059    0.017434      AFG       Asia  \n",
       "\n",
       "[1 rows x 44 columns]"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "print 'CRS', df_shp_norm01.crs\n",
    "print 'Number of map elements: ', len(df_shp_norm01  )\n",
    "df_shp_norm01.head(1)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Mapping did not work for all provinces, hence derive from spatial join. Note spatial join does work relatively poorly\n",
    "<font color='red'>WARNING: Dirty Hack sjoin based on centroid of province!</font>\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "14"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(df_shp_norm01[df_shp_norm01.continent.isnull()])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "df_nan = df_shp_norm01[df_shp_norm01.continent.isnull()]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/usr/local/lib/python2.7/dist-packages/ipykernel/__main__.py:1: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame\n",
      "\n",
      "See the caveats in the documentation: http://pandas.pydata.org/pandas-docs/stable/indexing.html#indexing-view-versus-copy\n",
      "  if __name__ == '__main__':\n"
     ]
    }
   ],
   "source": [
    "df_nan.drop(['continent', 'adm0_a3'], axis=1, inplace=True)\n",
    "df_shapefile_countries.drop('admin', axis=1, inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/usr/local/lib/python2.7/dist-packages/ipykernel/__main__.py:1: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: http://pandas.pydata.org/pandas-docs/stable/indexing.html#indexing-view-versus-copy\n",
      "  if __name__ == '__main__':\n",
      "/usr/local/lib/python2.7/dist-packages/ipykernel/__main__.py:2: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: http://pandas.pydata.org/pandas-docs/stable/indexing.html#indexing-view-versus-copy\n",
      "  from ipykernel import kernelapp as app\n",
      "/usr/local/lib/python2.7/dist-packages/ipykernel/__main__.py:3: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: http://pandas.pydata.org/pandas-docs/stable/indexing.html#indexing-view-versus-copy\n",
      "  app.launch_new_instance()\n"
     ]
    }
   ],
   "source": [
    "df_nan['centroid'] = df_nan.geometry.centroid\n",
    "df_nan['shape'] = df_nan['geometry']\n",
    "df_nan['geometry']= df_nan['centroid'] \n",
    "df_nan = gp.sjoin(df_nan, df_shapefile_countries, how='left')\n",
    "df_nan['geometry'] = df_nan['shape']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "df_nan.drop(['index_right', 'centroid', 'shape'], axis=1, inplace=True )"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Note several small elements got dropped by this step because centroid of shape did not lie in any polygon"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "df_shp_norm01 = pd.concat([df_shp_norm01.dropna(), df_nan.dropna()])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Add data on birth rate to estimate Microcephaly cases"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "('Number of nan rows that were filled:', 65)\n"
     ]
    }
   ],
   "source": [
    "df_birth_rate_crude = pd.read_csv('raw_data/Worldbank_dataset/BirthRateCrude_WorldBank.csv', \n",
    "                                  skiprows=4,\n",
    "                                  header=0,\n",
    "                                  usecols=[\"Country Code\", '2014'])\n",
    "df_birth_rate_crude.rename(\n",
    "    columns={'Country Code': 'adm0_a3', '2014' :'Crude_Birth_rate'}, inplace=True)\n",
    "\n",
    "# keep everything from the left frame, pulling in the value from the right frame where the keys match up. \n",
    "df_shp_norm01 = df_shp_norm01.merge(df_birth_rate_crude, on='adm0_a3',how='left')\n",
    "\n",
    "print ('Number of nan rows that were filled:',\n",
    "       df_shp_norm01['Crude_Birth_rate'].isnull().sum())\n",
    "\n",
    "# replace not find key with average birth rate by each continent\n",
    "df_shp_norm01['Crude_Birth_rate'] = df_shp_norm01.groupby(\"continent\").Crude_Birth_rate.transform(\n",
    "    lambda x: x.fillna(x.mean()))\n",
    "\n",
    "df_shp_norm01['pregnancies'] = (df_shp_norm01['Crude_Birth_rate'] \n",
    "                                                * (1.0/1000) * (9.0/12) \n",
    "                                                * df_shp_norm01['population'])\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Add data on poverty  (defined as people with lessa than 3.1 Dollar per day) according to World Bank Development indicators (country level)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "('Number of nan rows that need to be filled:', 683)\n"
     ]
    }
   ],
   "source": [
    "df_birth_rate_crude = pd.read_csv('raw_data/Worldbank_dataset/Poverty_3.10Dollar_a_day.csv', \n",
    "                                  skiprows=4,\n",
    "                                  header=0,\n",
    "                                 usecols=[\"Country Code\", '2013'])\n",
    "df_birth_rate_crude.rename(columns={'Country Code': 'adm0_a3', '2013' :'Poverty_WB'}, inplace=True)\n",
    "\n",
    "# keep everything from the left frame, pulling in the value from the right frame where the keys match up. \n",
    "df_shp_norm01 = df_shp_norm01.merge(df_birth_rate_crude,\n",
    "                                                                    on='adm0_a3',\n",
    "                                                                    how='left') ## DO NOT USE PD.MERGE with gp !!!!!\n",
    "print ('Number of nan rows that need to be filled:',\n",
    "       df_shp_norm01['Poverty_WB'].isnull().sum())\n",
    "\n",
    "# replace not find key with average birth rate\n",
    "# df_shp_norm01['Poverty_WB'].fillna(\n",
    "#     (df_shp_norm01['Poverty_WB'].mean()), inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Number of map elements:  763\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>10Aegypti</th>\n",
       "      <th>10Albopict</th>\n",
       "      <th>10combi_me</th>\n",
       "      <th>11Aegypti</th>\n",
       "      <th>11Albopict</th>\n",
       "      <th>11combined</th>\n",
       "      <th>12Aegypti</th>\n",
       "      <th>12Albopict</th>\n",
       "      <th>12combined</th>\n",
       "      <th>1Aegypti</th>\n",
       "      <th>...</th>\n",
       "      <th>adm0_a3</th>\n",
       "      <th>category</th>\n",
       "      <th>continent</th>\n",
       "      <th>country</th>\n",
       "      <th>geometry</th>\n",
       "      <th>perc_pover</th>\n",
       "      <th>population</th>\n",
       "      <th>Crude_Birth_rate</th>\n",
       "      <th>pregnancies</th>\n",
       "      <th>Poverty_WB</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0.014458</td>\n",
       "      <td>0.032256</td>\n",
       "      <td>0.036989</td>\n",
       "      <td>0.003545</td>\n",
       "      <td>0.01632</td>\n",
       "      <td>0.017434</td>\n",
       "      <td>0.003349</td>\n",
       "      <td>0.005992</td>\n",
       "      <td>0.007285</td>\n",
       "      <td>0.003574</td>\n",
       "      <td>...</td>\n",
       "      <td>AFG</td>\n",
       "      <td>1</td>\n",
       "      <td>Asia</td>\n",
       "      <td>Afghanistan</td>\n",
       "      <td>POLYGON ((69.48586754674983 34.15900055643047,...</td>\n",
       "      <td>74.586433</td>\n",
       "      <td>2.715357e+07</td>\n",
       "      <td>34.225</td>\n",
       "      <td>696998.21022</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>1 rows × 47 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "   10Aegypti  10Albopict  10combi_me  11Aegypti  11Albopict  11combined  \\\n",
       "0   0.014458    0.032256    0.036989   0.003545     0.01632    0.017434   \n",
       "\n",
       "   12Aegypti  12Albopict  12combined  1Aegypti     ...      adm0_a3  category  \\\n",
       "0   0.003349    0.005992    0.007285  0.003574     ...          AFG         1   \n",
       "\n",
       "   continent      country                                           geometry  \\\n",
       "0       Asia  Afghanistan  POLYGON ((69.48586754674983 34.15900055643047,...   \n",
       "\n",
       "   perc_pover    population  Crude_Birth_rate   pregnancies  Poverty_WB  \n",
       "0   74.586433  2.715357e+07            34.225  696998.21022         NaN  \n",
       "\n",
       "[1 rows x 47 columns]"
      ]
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "print 'Number of map elements: ', len(df_shp_norm01  )\n",
    "df_shp_norm01.head(1)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Clean geodaframe from no longer needed columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "df_shp_norm01.drop(['category'], axis=1, inplace=True )"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Select columns and arrange them as pandas multindex dataframe"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "import re\n",
    "def subset_df(str_filter):\n",
    "    col_Aegypti = filter(lambda k: (str_filter in k), df_shp_norm01.columns.tolist())\n",
    "    df_interactive_Aegypti = df_shp_norm01[col_Aegypti].copy()\n",
    "    lst = [ int(re.findall('\\d+', s)[0]) for s in df_interactive_Aegypti.columns ]\n",
    "    rename_dict = dict(zip(df_interactive_Aegypti.columns, lst))\n",
    "    df_interactive_Aegypti.rename(columns=rename_dict, inplace=True)\n",
    "    df_interactive_Aegypti = df_interactive_Aegypti.append(\n",
    "                                        pd.DataFrame(1,\n",
    "                                                     index=np.arange(1),\n",
    "                                                     columns=df_interactive_Aegypti.columns))\n",
    "    return df_interactive_Aegypti"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "frames = [subset_df('Aegypti'), subset_df('Albopict'), subset_df( 'combi')]\n",
    "multiindex_plot_suitability = pd.concat(frames, keys=['Aegypti', 'Albopictus', 'Both'])\n",
    "multiindex_plot_suitability.sort_index(axis=1, inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "multiindex_plot_suitability.to_csv('input_data_simulation/csv_files/Env_suitability_monthly.csv')\n",
    "multiindex_plot_suitability.to_csv('preprocessing_results/csv_files/Env_suitability_monthly.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "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>1</th>\n",
       "      <th>2</th>\n",
       "      <th>3</th>\n",
       "      <th>4</th>\n",
       "      <th>5</th>\n",
       "      <th>6</th>\n",
       "      <th>7</th>\n",
       "      <th>8</th>\n",
       "      <th>9</th>\n",
       "      <th>10</th>\n",
       "      <th>11</th>\n",
       "      <th>12</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th rowspan=\"5\" valign=\"top\">Aegypti</th>\n",
       "      <th>0</th>\n",
       "      <td>0.003574</td>\n",
       "      <td>0.004818</td>\n",
       "      <td>0.005626</td>\n",
       "      <td>0.005685</td>\n",
       "      <td>0.013484</td>\n",
       "      <td>0.016749</td>\n",
       "      <td>0.020543</td>\n",
       "      <td>0.022927</td>\n",
       "      <td>0.016433</td>\n",
       "      <td>0.014458</td>\n",
       "      <td>0.003545</td>\n",
       "      <td>0.003349</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>0.005973</td>\n",
       "      <td>0.008236</td>\n",
       "      <td>0.012229</td>\n",
       "      <td>0.011797</td>\n",
       "      <td>0.047884</td>\n",
       "      <td>0.048799</td>\n",
       "      <td>0.082857</td>\n",
       "      <td>0.090128</td>\n",
       "      <td>0.062663</td>\n",
       "      <td>0.056822</td>\n",
       "      <td>0.006036</td>\n",
       "      <td>0.005556</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>0.003298</td>\n",
       "      <td>0.005365</td>\n",
       "      <td>0.006634</td>\n",
       "      <td>0.015468</td>\n",
       "      <td>0.042516</td>\n",
       "      <td>0.044633</td>\n",
       "      <td>0.137644</td>\n",
       "      <td>0.125031</td>\n",
       "      <td>0.065523</td>\n",
       "      <td>0.034395</td>\n",
       "      <td>0.010564</td>\n",
       "      <td>0.002492</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>0.000756</td>\n",
       "      <td>0.000721</td>\n",
       "      <td>0.000711</td>\n",
       "      <td>0.000770</td>\n",
       "      <td>0.000596</td>\n",
       "      <td>0.000714</td>\n",
       "      <td>0.000658</td>\n",
       "      <td>0.001884</td>\n",
       "      <td>0.001920</td>\n",
       "      <td>0.001142</td>\n",
       "      <td>0.001777</td>\n",
       "      <td>0.000780</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>0.002693</td>\n",
       "      <td>0.002790</td>\n",
       "      <td>0.002858</td>\n",
       "      <td>0.002982</td>\n",
       "      <td>0.002460</td>\n",
       "      <td>0.001520</td>\n",
       "      <td>0.002313</td>\n",
       "      <td>0.002365</td>\n",
       "      <td>0.007707</td>\n",
       "      <td>0.002683</td>\n",
       "      <td>0.002441</td>\n",
       "      <td>0.002547</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                 1         2         3         4         5         6   \\\n",
       "Aegypti 0  0.003574  0.004818  0.005626  0.005685  0.013484  0.016749   \n",
       "        1  0.005973  0.008236  0.012229  0.011797  0.047884  0.048799   \n",
       "        2  0.003298  0.005365  0.006634  0.015468  0.042516  0.044633   \n",
       "        3  0.000756  0.000721  0.000711  0.000770  0.000596  0.000714   \n",
       "        4  0.002693  0.002790  0.002858  0.002982  0.002460  0.001520   \n",
       "\n",
       "                 7         8         9         10        11        12  \n",
       "Aegypti 0  0.020543  0.022927  0.016433  0.014458  0.003545  0.003349  \n",
       "        1  0.082857  0.090128  0.062663  0.056822  0.006036  0.005556  \n",
       "        2  0.137644  0.125031  0.065523  0.034395  0.010564  0.002492  \n",
       "        3  0.000658  0.001884  0.001920  0.001142  0.001777  0.000780  \n",
       "        4  0.002313  0.002365  0.007707  0.002683  0.002441  0.002547  "
      ]
     },
     "execution_count": 30,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "multiindex_plot_suitability.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "df_shp_norm01['suitability_max'] = multiindex_plot_suitability.ix['Both'].max(axis=1)[:-1]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Select all columns that are not Environmental suitability per month"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "df_shp_norm01.rename(columns={'adm0_a3': 'country_code'}, inplace=True)\n",
    "col_contain_digit= df_shp_norm01.columns.to_series().apply(lambda s: \n",
    "                                                                           any(i.isdigit() for i in s))\n",
    "col_contain_digit = ~col_contain_digit\n",
    "df_shp_norm01 = df_shp_norm01.loc[:, col_contain_digit.tolist()]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index([      u'Messinamea',     u'country_code',        u'continent',\n",
       "                u'country',         u'geometry',       u'perc_pover',\n",
       "             u'population', u'Crude_Birth_rate',      u'pregnancies',\n",
       "             u'Poverty_WB',  u'suitability_max'],\n",
       "      dtype='object')"
      ]
     },
     "execution_count": 33,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_shp_norm01.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "import os\n",
    "filename = 'preprocessing_results/geojson/gdf_map_elements.geojson'\n",
    "try:\n",
    "    os.remove(filename)\n",
    "except OSError:\n",
    "    pass\n",
    "df_shp_norm01.to_file(filename, driver=\"GeoJSON\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 2",
   "language": "python",
   "name": "python2"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 2
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython2",
   "version": "2.7.6"
  },
  "widgets": {
   "state": {},
   "version": "1.1.2"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 0
}