common_adm2_code_drought.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "39552481",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2022-10-31T06:57:57.135372Z",
     "start_time": "2022-10-31T06:57:54.864554Z"
    }
   },
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/root/miniconda3/envs/climada_env/lib/python3.8/site-packages/openpyxl/styles/stylesheet.py:226: UserWarning: Workbook contains no default style, apply openpyxl's default\n",
      "  warn(\"Workbook contains no default style, apply openpyxl's default\")\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "Int64Index: 62 entries, 77 to 4601\n",
      "Data columns (total 50 columns):\n",
      " #   Column                                     Non-Null Count  Dtype  \n",
      "---  ------                                     --------------  -----  \n",
      " 0   Dis No                                     62 non-null     object \n",
      " 1   Year                                       62 non-null     int64  \n",
      " 2   Seq                                        62 non-null     int64  \n",
      " 3   Glide                                      23 non-null     object \n",
      " 4   Disaster Group                             62 non-null     object \n",
      " 5   Disaster Subgroup                          62 non-null     object \n",
      " 6   Disaster Type                              62 non-null     object \n",
      " 7   Disaster Subtype                           62 non-null     object \n",
      " 8   Disaster Subsubtype                        0 non-null      object \n",
      " 9   Event Name                                 0 non-null      object \n",
      " 10  Country                                    62 non-null     object \n",
      " 11  ISO                                        62 non-null     object \n",
      " 12  Region                                     62 non-null     object \n",
      " 13  Continent                                  62 non-null     object \n",
      " 14  Location                                   60 non-null     object \n",
      " 15  Origin                                     21 non-null     object \n",
      " 16  Associated Dis                             39 non-null     object \n",
      " 17  Associated Dis2                            8 non-null      object \n",
      " 18  OFDA Response                              14 non-null     object \n",
      " 19  Appeal                                     14 non-null     object \n",
      " 20  Declaration                                18 non-null     object \n",
      " 21  Aid Contribution                           5 non-null      float64\n",
      " 22  Dis Mag Value                              0 non-null      float64\n",
      " 23  Dis Mag Scale                              62 non-null     object \n",
      " 24  Latitude                                   0 non-null      object \n",
      " 25  Longitude                                  0 non-null      object \n",
      " 26  Local Time                                 0 non-null      object \n",
      " 27  River Basin                                0 non-null      object \n",
      " 28  Start Year                                 62 non-null     int64  \n",
      " 29  Start Month                                49 non-null     float64\n",
      " 30  Start Day                                  0 non-null      float64\n",
      " 31  End Year                                   62 non-null     int64  \n",
      " 32  End Month                                  25 non-null     float64\n",
      " 33  End Day                                    0 non-null      float64\n",
      " 34  Total Deaths                               7 non-null      float64\n",
      " 35  No Injured                                 0 non-null      float64\n",
      " 36  No Affected                                60 non-null     float64\n",
      " 37  No Homeless                                0 non-null      float64\n",
      " 38  Total Affected                             60 non-null     float64\n",
      " 39  Reconstruction Costs ('000 US$)            0 non-null      float64\n",
      " 40  Reconstruction Costs, Adjusted ('000 US$)  0 non-null      float64\n",
      " 41  Insured Damages ('000 US$)                 0 non-null      float64\n",
      " 42  Insured Damages, Adjusted ('000 US$)       0 non-null      float64\n",
      " 43  Total Damages ('000 US$)                   5 non-null      float64\n",
      " 44  Total Damages, Adjusted ('000 US$)         4 non-null      float64\n",
      " 45  CPI                                        61 non-null     float64\n",
      " 46  Adm Level                                  48 non-null     object \n",
      " 47  Admin1 Code                                31 non-null     object \n",
      " 48  Admin2 Code                                24 non-null     object \n",
      " 49  Geo Locations                              48 non-null     object \n",
      "dtypes: float64(18), int64(4), object(28)\n",
      "memory usage: 24.7+ KB\n"
     ]
    }
   ],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "import re\n",
    "\n",
    "#db=pd.read_csv('/home/bulbul/Documents/07-2022/impact_weather_icpac/lab/ea_ibf_data_resources/impact/em-dat/emdat_public_2022_08_11_query_uid-IKGfZc.xlsx')\n",
    "\n",
    "em_data_filepath='emdat_public_2022_08_11_query_uid-IKGfZc.xlsx'\n",
    "\n",
    "dfb = pd.read_excel(em_data_filepath, engine='openpyxl',skiprows=6,sheet_name = 'emdat data')\n",
    "\n",
    "\n",
    "\n",
    "flood_grp=['Drought']\n",
    "\n",
    "#dfb1_ea=dfb1[dfb1['Country'].str.contains(ea_cntr)]\n",
    "dfb_dr=dfb[dfb['Disaster Type'].isin(flood_grp)]\n",
    "\n",
    "ea_cntr=['Djibouti', 'Eritrea','Ethiopia','Kenya','Rwanda', 'Somalia','South Sudan','Sudan (the)','Tanzania, United Republic of']\n",
    "\n",
    "#dfb1_ea=dfb1[dfb1['Country'].str.contains(ea_cntr)]\n",
    "dfb_dr_ea=dfb_dr[dfb_dr['Country'].isin(ea_cntr)]\n",
    "\n",
    "dfb_dr_ea.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "a43a58a7",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2022-10-31T06:58:03.299295Z",
     "start_time": "2022-10-31T06:58:03.270769Z"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 62 entries, 0 to 61\n",
      "Data columns (total 7 columns):\n",
      " #   Column       Non-Null Count  Dtype \n",
      "---  ------       --------------  ----- \n",
      " 0   Unnamed: 0   62 non-null     int64 \n",
      " 1   Dis No       62 non-null     object\n",
      " 2   Location     60 non-null     object\n",
      " 3   Adm Level    48 non-null     object\n",
      " 4   Admin1 Code  31 non-null     object\n",
      " 5   Admin2 Code  24 non-null     object\n",
      " 6   geob         14 non-null     object\n",
      "dtypes: int64(1), object(6)\n",
      "memory usage: 3.5+ KB\n"
     ]
    }
   ],
   "source": [
    "import pandas as pd\n",
    "\n",
    "dfb_dr_path='ea_drought_location_v1.xlsx'\n",
    "\n",
    "dfb_dr=pd.read_excel(dfb_dr_path, engine='openpyxl')\n",
    "dfb_dr.info()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "edf84828",
   "metadata": {},
   "source": [
    "# replacing GAUL Admin1 Code into GEOB admin2 codes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "153cd54d",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2022-10-31T06:58:07.700380Z",
     "start_time": "2022-10-31T06:58:07.617759Z"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "Int64Index: 31 entries, 13 to 59\n",
      "Data columns (total 2 columns):\n",
      " #   Column          Non-Null Count  Dtype \n",
      "---  ------          --------------  ----- \n",
      " 0   Dis No          31 non-null     object\n",
      " 1   gaul_adm1_geob  31 non-null     object\n",
      "dtypes: object(2)\n",
      "memory usage: 744.0+ bytes\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/tmp/ipykernel_45/2101602999.py:14: 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: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  dfb_dr_adm1['gaul_adm1_list_int']=dfb_dr_adm1['gaul_adm1_list'].apply(lambda x: [int(i) for i in x])\n",
      "/tmp/ipykernel_45/2101602999.py:17: 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: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  dfb_dr_adm1['gaul_adm1_geob'] = dfb_dr_adm1['gaul_adm1_list_int'].apply(lambda x: [gaul_adm1_dict[i] for i in x])\n"
     ]
    }
   ],
   "source": [
    "dfb_dr['gaul_adm1_list']=dfb_dr['Admin1 Code'].str.split(';')\n",
    "\n",
    "\n",
    "em_dat_path='./'\n",
    "\n",
    "\n",
    "gaul_adm1_dict_db=pd.read_csv(f'{em_dat_path}em_dat_ea_gaul_geob_adm1_codeshare.csv')\n",
    "\n",
    "gaul_adm1_dict = dict(zip(gaul_adm1_dict_db.gaul_adm1_code, gaul_adm1_dict_db.geob_adm1_shapeID))\n",
    "\n",
    "\n",
    "dfb_dr_adm1=dfb_dr[dfb_dr['gaul_adm1_list'].notna()]\n",
    "\n",
    "dfb_dr_adm1['gaul_adm1_list_int']=dfb_dr_adm1['gaul_adm1_list'].apply(lambda x: [int(i) for i in x])\n",
    "\n",
    "\n",
    "dfb_dr_adm1['gaul_adm1_geob'] = dfb_dr_adm1['gaul_adm1_list_int'].apply(lambda x: [gaul_adm1_dict[i] for i in x])\n",
    "\n",
    "#dfb_dr_adm1\n",
    "\n",
    "dfb_dr_adm1_a=dfb_dr_adm1[['Dis No','gaul_adm1_geob']]\n",
    "\n",
    "dfb_dr_adm1_a.info()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ca1a7896",
   "metadata": {},
   "source": [
    "## replace adm1 code with adm2 sub codes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "e722a5a6",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2022-10-31T06:58:14.049559Z",
     "start_time": "2022-10-31T06:58:13.493300Z"
    }
   },
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/tmp/ipykernel_45/2330873680.py:26: 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: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  dfb_dr_adm1_a['geob_adm2'] = dfb_dr_adm1_a['gaul_adm1_geob'].apply(lambda x: [geob_db3_dict[i] for i in x])\n",
      "/tmp/ipykernel_45/2330873680.py:39: 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: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  dfb_dr_adm1_a['geob_adm2_list']=aa_list\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Dis No</th>\n",
       "      <th>geob_adm2_list</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>1999-9388-ERI</td>\n",
       "      <td>[ERI-ADM2-3_0_0-B20, ERI-ADM2-3_0_0-B21, ERI-A...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>1999-9388-ETH</td>\n",
       "      <td>[ETH-ADM2-3_0_0-B5, ETH-ADM2-3_0_0-B33, ETH-AD...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>1999-9388-DJI</td>\n",
       "      <td>[DJI-ADM2-3_0_0-B10, DJI-ADM2-3_0_0-B7, DJI-AD...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>1999-9388-SDN</td>\n",
       "      <td>[SDN-ADM2-3_0_0-B78, SDN-ADM2-3_0_0-B79, SDN-A...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>1999-9388-RWA</td>\n",
       "      <td>[RWA-ADM2-3_0_0-B1, RWA-ADM2-3_0_0-B2, RWA-ADM...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>1999-9388-SOM</td>\n",
       "      <td>[SOM-ADM2-3_0_0-B5, SOM-ADM2-3_0_0-B14, SOM-AD...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>2003-9772-ETH</td>\n",
       "      <td>[ETH-ADM2-3_0_0-B70, ETH-ADM2-3_0_0-B71, ETH-A...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>2004-9288-KEN</td>\n",
       "      <td>[KEN-ADM2-3_0_0-B52, KEN-ADM2-3_0_0-B12, KEN-A...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>2003-9651-TZA</td>\n",
       "      <td>[TZA-ADM2-3_0_0-B12, TZA-ADM2-3_0_0-B35, TZA-A...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>2004-9085-SOM</td>\n",
       "      <td>[SOM-ADM2-3_0_0-B31, SOM-ADM2-3_0_0-B34, SOM-A...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25</th>\n",
       "      <td>2004-9577-TZA</td>\n",
       "      <td>[TZA-ADM2-3_0_0-B14, TZA-ADM2-3_0_0-B34, TZA-A...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28</th>\n",
       "      <td>2005-9722-SOM</td>\n",
       "      <td>[SOM-ADM2-3_0_0-B10, SOM-ADM2-3_0_0-B13, SOM-A...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>30</th>\n",
       "      <td>2006-9756-TZA</td>\n",
       "      <td>[TZA-ADM2-3_0_0-B14, TZA-ADM2-3_0_0-B34, TZA-A...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>31</th>\n",
       "      <td>2007-9676-DJI</td>\n",
       "      <td>[DJI-ADM2-3_0_0-B10, DJI-ADM2-3_0_0-B7, DJI-AD...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>32</th>\n",
       "      <td>2008-9200-ETH</td>\n",
       "      <td>[ETH-ADM2-3_0_0-B70, ETH-ADM2-3_0_0-B71, ETH-A...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>34</th>\n",
       "      <td>2008-9200-ERI</td>\n",
       "      <td>[ERI-ADM2-3_0_0-B20, ERI-ADM2-3_0_0-B21, ERI-A...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>36</th>\n",
       "      <td>2008-9302-KEN</td>\n",
       "      <td>[KEN-ADM2-3_0_0-B52, KEN-ADM2-3_0_0-B58, KEN-A...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>37</th>\n",
       "      <td>2009-9615-ETH</td>\n",
       "      <td>[ETH-ADM2-3_0_0-B70, ETH-ADM2-3_0_0-B71, ETH-A...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>39</th>\n",
       "      <td>2010-9082-ETH</td>\n",
       "      <td>[ETH-ADM2-3_0_0-B70, ETH-ADM2-3_0_0-B71, ETH-A...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>41</th>\n",
       "      <td>2010-9082-SOM</td>\n",
       "      <td>[SOM-ADM2-3_0_0-B35, SOM-ADM2-3_0_0-B58, SOM-A...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>42</th>\n",
       "      <td>2009-9633-SSD</td>\n",
       "      <td>[SSD-ADM2-3_0_0-B28, SSD-ADM2-3_0_0-B29, SSD-A...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>43</th>\n",
       "      <td>2011-9663-ETH</td>\n",
       "      <td>[ETH-ADM2-3_0_0-B48, ETH-ADM2-3_0_0-B13, ETH-A...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>46</th>\n",
       "      <td>2011-9663-SOM</td>\n",
       "      <td>[SOM-ADM2-3_0_0-B10, SOM-ADM2-3_0_0-B13, SOM-A...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>48</th>\n",
       "      <td>2015-9545-ETH</td>\n",
       "      <td>[ETH-ADM2-3_0_0-B70, ETH-ADM2-3_0_0-B71, ETH-A...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>49</th>\n",
       "      <td>2014-9231-SOM</td>\n",
       "      <td>[SOM-ADM2-3_0_0-B10, SOM-ADM2-3_0_0-B13, SOM-A...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>51</th>\n",
       "      <td>2015-9373-SDN</td>\n",
       "      <td>[SDN-ADM2-3_0_0-B73, SDN-ADM2-3_0_0-B74, SDN-A...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>52</th>\n",
       "      <td>2015-9616-SOM</td>\n",
       "      <td>[SOM-ADM2-3_0_0-B35, SOM-ADM2-3_0_0-B58, SOM-A...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>53</th>\n",
       "      <td>2016-9065-SSD</td>\n",
       "      <td>[SSD-ADM2-3_0_0-B15, SSD-ADM2-3_0_0-B16, SSD-A...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>56</th>\n",
       "      <td>2019-9109-SOM</td>\n",
       "      <td>[SOM-ADM2-3_0_0-B35, SOM-ADM2-3_0_0-B58, SOM-A...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>57</th>\n",
       "      <td>2021-9546-ETH</td>\n",
       "      <td>[ETH-ADM2-3_0_0-B70, ETH-ADM2-3_0_0-B71, ETH-A...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>59</th>\n",
       "      <td>2021-9152-SOM</td>\n",
       "      <td>[SOM-ADM2-3_0_0-B21, SOM-ADM2-3_0_0-B25, SOM-A...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "           Dis No                                     geob_adm2_list\n",
       "13  1999-9388-ERI  [ERI-ADM2-3_0_0-B20, ERI-ADM2-3_0_0-B21, ERI-A...\n",
       "14  1999-9388-ETH  [ETH-ADM2-3_0_0-B5, ETH-ADM2-3_0_0-B33, ETH-AD...\n",
       "16  1999-9388-DJI  [DJI-ADM2-3_0_0-B10, DJI-ADM2-3_0_0-B7, DJI-AD...\n",
       "17  1999-9388-SDN  [SDN-ADM2-3_0_0-B78, SDN-ADM2-3_0_0-B79, SDN-A...\n",
       "18  1999-9388-RWA  [RWA-ADM2-3_0_0-B1, RWA-ADM2-3_0_0-B2, RWA-ADM...\n",
       "19  1999-9388-SOM  [SOM-ADM2-3_0_0-B5, SOM-ADM2-3_0_0-B14, SOM-AD...\n",
       "20  2003-9772-ETH  [ETH-ADM2-3_0_0-B70, ETH-ADM2-3_0_0-B71, ETH-A...\n",
       "21  2004-9288-KEN  [KEN-ADM2-3_0_0-B52, KEN-ADM2-3_0_0-B12, KEN-A...\n",
       "23  2003-9651-TZA  [TZA-ADM2-3_0_0-B12, TZA-ADM2-3_0_0-B35, TZA-A...\n",
       "24  2004-9085-SOM  [SOM-ADM2-3_0_0-B31, SOM-ADM2-3_0_0-B34, SOM-A...\n",
       "25  2004-9577-TZA  [TZA-ADM2-3_0_0-B14, TZA-ADM2-3_0_0-B34, TZA-A...\n",
       "28  2005-9722-SOM  [SOM-ADM2-3_0_0-B10, SOM-ADM2-3_0_0-B13, SOM-A...\n",
       "30  2006-9756-TZA  [TZA-ADM2-3_0_0-B14, TZA-ADM2-3_0_0-B34, TZA-A...\n",
       "31  2007-9676-DJI  [DJI-ADM2-3_0_0-B10, DJI-ADM2-3_0_0-B7, DJI-AD...\n",
       "32  2008-9200-ETH  [ETH-ADM2-3_0_0-B70, ETH-ADM2-3_0_0-B71, ETH-A...\n",
       "34  2008-9200-ERI  [ERI-ADM2-3_0_0-B20, ERI-ADM2-3_0_0-B21, ERI-A...\n",
       "36  2008-9302-KEN  [KEN-ADM2-3_0_0-B52, KEN-ADM2-3_0_0-B58, KEN-A...\n",
       "37  2009-9615-ETH  [ETH-ADM2-3_0_0-B70, ETH-ADM2-3_0_0-B71, ETH-A...\n",
       "39  2010-9082-ETH  [ETH-ADM2-3_0_0-B70, ETH-ADM2-3_0_0-B71, ETH-A...\n",
       "41  2010-9082-SOM  [SOM-ADM2-3_0_0-B35, SOM-ADM2-3_0_0-B58, SOM-A...\n",
       "42  2009-9633-SSD  [SSD-ADM2-3_0_0-B28, SSD-ADM2-3_0_0-B29, SSD-A...\n",
       "43  2011-9663-ETH  [ETH-ADM2-3_0_0-B48, ETH-ADM2-3_0_0-B13, ETH-A...\n",
       "46  2011-9663-SOM  [SOM-ADM2-3_0_0-B10, SOM-ADM2-3_0_0-B13, SOM-A...\n",
       "48  2015-9545-ETH  [ETH-ADM2-3_0_0-B70, ETH-ADM2-3_0_0-B71, ETH-A...\n",
       "49  2014-9231-SOM  [SOM-ADM2-3_0_0-B10, SOM-ADM2-3_0_0-B13, SOM-A...\n",
       "51  2015-9373-SDN  [SDN-ADM2-3_0_0-B73, SDN-ADM2-3_0_0-B74, SDN-A...\n",
       "52  2015-9616-SOM  [SOM-ADM2-3_0_0-B35, SOM-ADM2-3_0_0-B58, SOM-A...\n",
       "53  2016-9065-SSD  [SSD-ADM2-3_0_0-B15, SSD-ADM2-3_0_0-B16, SSD-A...\n",
       "56  2019-9109-SOM  [SOM-ADM2-3_0_0-B35, SOM-ADM2-3_0_0-B58, SOM-A...\n",
       "57  2021-9546-ETH  [ETH-ADM2-3_0_0-B70, ETH-ADM2-3_0_0-B71, ETH-A...\n",
       "59  2021-9152-SOM  [SOM-ADM2-3_0_0-B21, SOM-ADM2-3_0_0-B25, SOM-A..."
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import geopandas as gp\n",
    "import numpy as np\n",
    "\n",
    "geob_adm1_adm2_filepath='/home/ea_shapefiles/'\n",
    "\n",
    "\n",
    "geob_db=gp.read_file(f'{geob_adm1_adm2_filepath}ea_adm2_geoboundaries_adm1_code.shp')\n",
    "\n",
    "\n",
    "#https://stackoverflow.com/a/54137935/2501953\n",
    "\n",
    "\n",
    "geob_db1=geob_db[['adm1_shape','shapeID']]\n",
    "#geob_db2=geob_db1.set_index('adm1_shape')\n",
    "\n",
    "\n",
    "#geob_db_dict=geob_db1['adm1_shape'].groupby(level=0).apply(lambda x: x.to_dict('shapeID')).to_dict()\n",
    "\n",
    "geob_db2=geob_db1.groupby('adm1_shape').agg({'shapeID':lambda x: list(x)})\n",
    "geob_db3=geob_db2.reset_index()\n",
    "geob_db3.columns=['gaul_adm1_geob','shapeID']\n",
    "\n",
    "geob_db3_dict = dict(zip(geob_db3.gaul_adm1_geob, geob_db3.shapeID))\n",
    "\n",
    "\n",
    "dfb_dr_adm1_a['geob_adm2'] = dfb_dr_adm1_a['gaul_adm1_geob'].apply(lambda x: [geob_db3_dict[i] for i in x])\n",
    "\n",
    "def flatten(l):\n",
    "    return [item for sublist in l for item in sublist]\n",
    "\n",
    "aa_list=[]\n",
    "for idx,row in dfb_dr_adm1_a.iterrows():\n",
    "    aa=flatten(row['geob_adm2'])\n",
    "    #print(aa)\n",
    "    #dfb_dr_adm1_a.at[idx,'geob_adm2_list'] = list(flatten(row['geob_adm2']))\n",
    "    aa_list.append(aa)\n",
    "    aa=[]\n",
    "    \n",
    "dfb_dr_adm1_a['geob_adm2_list']=aa_list\n",
    "    \n",
    "dfb_dr_part1=dfb_dr_adm1_a[['Dis No','geob_adm2_list']]\n",
    "dfb_dr_part1"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "fa5c2c4c",
   "metadata": {},
   "source": [
    "# replacing GAUL Admin2 Code into GEOB admin2 codes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "50618540",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2022-10-31T06:58:20.255734Z",
     "start_time": "2022-10-31T06:58:20.211390Z"
    }
   },
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/tmp/ipykernel_45/449400531.py:14: 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: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  dfb_dr_adm2['gaul_adm2_list_int']=dfb_dr_adm2['gaul_adm2_list'].apply(lambda x: [int(i) for i in x])\n",
      "/tmp/ipykernel_45/449400531.py:17: 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: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  dfb_dr_adm2['gaul_adm2_geob'] = dfb_dr_adm2['gaul_adm2_list_int'].apply(lambda x: [gaul_adm2_dict[i] for i in x])\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Dis No</th>\n",
       "      <th>geob_adm2_list</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>1999-9388-ETH</td>\n",
       "      <td>[ETH-ADM2-3_0_0-B45, ETH-ADM2-3_0_0-B47, ETH-A...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>1999-9388-KEN</td>\n",
       "      <td>[KEN-ADM2-3_0_0-B50, KEN-ADM2-3_0_0-B52, KEN-A...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>1999-9388-RWA</td>\n",
       "      <td>[RWA-ADM2-3_0_0-B7, RWA-ADM2-3_0_0-B10, RWA-AD...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>1999-9388-SOM</td>\n",
       "      <td>[SOM-ADM2-3_0_0-B30, SOM-ADM2-3_0_0-B10, SOM-A...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>2004-9288-KEN</td>\n",
       "      <td>[KEN-ADM2-3_0_0-B19, KEN-ADM2-3_0_0-B25, KEN-A...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>2003-9651-RWA</td>\n",
       "      <td>[RWA-ADM2-3_0_0-B29, RWA-ADM2-3_0_0-B25, RWA-A...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26</th>\n",
       "      <td>2005-9747-ETH</td>\n",
       "      <td>[ETH-ADM2-3_0_0-B1, ETH-ADM2-3_0_0-B51, ETH-AD...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27</th>\n",
       "      <td>2005-9213-DJI</td>\n",
       "      <td>[DJI-ADM2-3_0_0-B10, DJI-ADM2-3_0_0-B9, DJI-AD...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29</th>\n",
       "      <td>2005-9719-KEN</td>\n",
       "      <td>[KEN-ADM2-3_0_0-B19, KEN-ADM2-3_0_0-B25, KEN-A...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>33</th>\n",
       "      <td>2008-9303-DJI</td>\n",
       "      <td>[DJI-ADM2-3_0_0-B10, DJI-ADM2-3_0_0-B9, DJI-AD...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>35</th>\n",
       "      <td>2008-9468-SOM</td>\n",
       "      <td>[SOM-ADM2-3_0_0-B38]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>36</th>\n",
       "      <td>2008-9302-KEN</td>\n",
       "      <td>[KEN-ADM2-3_0_0-B1, KEN-ADM2-3_0_0-B54, KEN-AD...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>38</th>\n",
       "      <td>2010-9082-DJI</td>\n",
       "      <td>[DJI-ADM2-3_0_0-B10, DJI-ADM2-3_0_0-B9, DJI-AD...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>40</th>\n",
       "      <td>2010-9082-KEN</td>\n",
       "      <td>[KEN-ADM2-3_0_0-B25, KEN-ADM2-3_0_0-B27, KEN-A...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>44</th>\n",
       "      <td>2011-9663-KEN</td>\n",
       "      <td>[KEN-ADM2-3_0_0-B58, KEN-ADM2-3_0_0-B35, KEN-A...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>45</th>\n",
       "      <td>2011-9663-SDN</td>\n",
       "      <td>[SDN-ADM2-3_0_0-B126]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>47</th>\n",
       "      <td>2011-9415-TZA</td>\n",
       "      <td>[TZA-ADM2-3_0_0-B18, TZA-ADM2-3_0_0-B63, TZA-A...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>49</th>\n",
       "      <td>2014-9231-SOM</td>\n",
       "      <td>[SOM-ADM2-3_0_0-B68]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>50</th>\n",
       "      <td>2014-9404-KEN</td>\n",
       "      <td>[KEN-ADM2-3_0_0-B35, KEN-ADM2-3_0_0-B32, KEN-A...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>54</th>\n",
       "      <td>2016-9452-KEN</td>\n",
       "      <td>[KEN-ADM2-3_0_0-B19, KEN-ADM2-3_0_0-B25, KEN-A...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>55</th>\n",
       "      <td>2019-9145-KEN</td>\n",
       "      <td>[KEN-ADM2-3_0_0-B52, KEN-ADM2-3_0_0-B19, KEN-A...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>58</th>\n",
       "      <td>2021-9152-KEN</td>\n",
       "      <td>[KEN-ADM2-3_0_0-B19, KEN-ADM2-3_0_0-B27, KEN-A...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>59</th>\n",
       "      <td>2021-9152-SOM</td>\n",
       "      <td>[SOM-ADM2-3_0_0-B59, SOM-ADM2-3_0_0-B58, SOM-A...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>60</th>\n",
       "      <td>2021-9639-SSD</td>\n",
       "      <td>[SSD-ADM2-3_0_0-B24, SSD-ADM2-3_0_0-B41, SSD-A...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "           Dis No                                     geob_adm2_list\n",
       "14  1999-9388-ETH  [ETH-ADM2-3_0_0-B45, ETH-ADM2-3_0_0-B47, ETH-A...\n",
       "15  1999-9388-KEN  [KEN-ADM2-3_0_0-B50, KEN-ADM2-3_0_0-B52, KEN-A...\n",
       "18  1999-9388-RWA  [RWA-ADM2-3_0_0-B7, RWA-ADM2-3_0_0-B10, RWA-AD...\n",
       "19  1999-9388-SOM  [SOM-ADM2-3_0_0-B30, SOM-ADM2-3_0_0-B10, SOM-A...\n",
       "21  2004-9288-KEN  [KEN-ADM2-3_0_0-B19, KEN-ADM2-3_0_0-B25, KEN-A...\n",
       "22  2003-9651-RWA  [RWA-ADM2-3_0_0-B29, RWA-ADM2-3_0_0-B25, RWA-A...\n",
       "26  2005-9747-ETH  [ETH-ADM2-3_0_0-B1, ETH-ADM2-3_0_0-B51, ETH-AD...\n",
       "27  2005-9213-DJI  [DJI-ADM2-3_0_0-B10, DJI-ADM2-3_0_0-B9, DJI-AD...\n",
       "29  2005-9719-KEN  [KEN-ADM2-3_0_0-B19, KEN-ADM2-3_0_0-B25, KEN-A...\n",
       "33  2008-9303-DJI  [DJI-ADM2-3_0_0-B10, DJI-ADM2-3_0_0-B9, DJI-AD...\n",
       "35  2008-9468-SOM                               [SOM-ADM2-3_0_0-B38]\n",
       "36  2008-9302-KEN  [KEN-ADM2-3_0_0-B1, KEN-ADM2-3_0_0-B54, KEN-AD...\n",
       "38  2010-9082-DJI  [DJI-ADM2-3_0_0-B10, DJI-ADM2-3_0_0-B9, DJI-AD...\n",
       "40  2010-9082-KEN  [KEN-ADM2-3_0_0-B25, KEN-ADM2-3_0_0-B27, KEN-A...\n",
       "44  2011-9663-KEN  [KEN-ADM2-3_0_0-B58, KEN-ADM2-3_0_0-B35, KEN-A...\n",
       "45  2011-9663-SDN                              [SDN-ADM2-3_0_0-B126]\n",
       "47  2011-9415-TZA  [TZA-ADM2-3_0_0-B18, TZA-ADM2-3_0_0-B63, TZA-A...\n",
       "49  2014-9231-SOM                               [SOM-ADM2-3_0_0-B68]\n",
       "50  2014-9404-KEN  [KEN-ADM2-3_0_0-B35, KEN-ADM2-3_0_0-B32, KEN-A...\n",
       "54  2016-9452-KEN  [KEN-ADM2-3_0_0-B19, KEN-ADM2-3_0_0-B25, KEN-A...\n",
       "55  2019-9145-KEN  [KEN-ADM2-3_0_0-B52, KEN-ADM2-3_0_0-B19, KEN-A...\n",
       "58  2021-9152-KEN  [KEN-ADM2-3_0_0-B19, KEN-ADM2-3_0_0-B27, KEN-A...\n",
       "59  2021-9152-SOM  [SOM-ADM2-3_0_0-B59, SOM-ADM2-3_0_0-B58, SOM-A...\n",
       "60  2021-9639-SSD  [SSD-ADM2-3_0_0-B24, SSD-ADM2-3_0_0-B41, SSD-A..."
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "dfb_dr['gaul_adm2_list']=dfb_dr['Admin2 Code'].str.split(';')\n",
    "\n",
    "\n",
    "em_dat_path='./'\n",
    "\n",
    "\n",
    "gaul_adm2_dict_db=pd.read_excel(f'{em_dat_path}em_dat_ea_gaul_geob_adm2_codeshare_v1.xlsx', engine='openpyxl')\n",
    "\n",
    "gaul_adm2_dict = dict(zip(gaul_adm2_dict_db.gaul_adm2_code, gaul_adm2_dict_db.geob_adm2_shapeID))\n",
    "\n",
    "\n",
    "dfb_dr_adm2=dfb_dr[dfb_dr['gaul_adm2_list'].notna()]\n",
    "\n",
    "dfb_dr_adm2['gaul_adm2_list_int']=dfb_dr_adm2['gaul_adm2_list'].apply(lambda x: [int(i) for i in x])\n",
    "\n",
    "\n",
    "dfb_dr_adm2['gaul_adm2_geob'] = dfb_dr_adm2['gaul_adm2_list_int'].apply(lambda x: [gaul_adm2_dict[i] for i in x])\n",
    "\n",
    "#dfb_dr_a\n",
    "\n",
    "dfb_dr_part2=dfb_dr_adm2[['Dis No','gaul_adm2_geob']]\n",
    "\n",
    "dfb_dr_part2.columns=['Dis No','geob_adm2_list']\n",
    "dfb_dr_part2"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "44a67b28",
   "metadata": {},
   "source": [
    "# replacing manually added GEOB admin 1 and admin2 codes into common admin2 codes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "4f9b1577",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2022-10-31T06:58:42.316891Z",
     "start_time": "2022-10-31T06:58:42.270962Z"
    }
   },
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/tmp/ipykernel_45/4091450130.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: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  dfb_dr1['geob_list']=dfb_dr1['geob'].str.split(',')\n",
      "/tmp/ipykernel_45/4091450130.py:25: 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: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  dfb_dr1['geob_adm2_list']=aa_list\n",
      "/tmp/ipykernel_45/4091450130.py:26: 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: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  dfb_dr1['geob_adm1_list']=bb_list\n",
      "/tmp/ipykernel_45/4091450130.py:33: 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: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  dfb_dr1_adm1_a['geob_adm2_list'] = dfb_dr1_adm1_a['geob_adm1_list'].apply(lambda x: [geob_db3_dict[i] for i in x])\n",
      "/tmp/ipykernel_45/4091450130.py:45: 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: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  dfb_dr1_adm1_a['geob_adm2_list_a']=aa_list\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Dis No</th>\n",
       "      <th>geob_adm2_list</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1991-9224-KEN</td>\n",
       "      <td>[KEN-ADM2-3_0_0-B65, KEN-ADM2-3_0_0-B4, KEN-AD...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1993-9573-ERI</td>\n",
       "      <td>[ERI-ADM2-3_0_0-B26, ERI-ADM2-3_0_0-B22, ERI-A...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1990-9289-SDN</td>\n",
       "      <td>[SSD-ADM2-3_0_0-B24, SSD-ADM2-3_0_0-B25, SSD-A...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1990-9604-TZA</td>\n",
       "      <td>[TZA-ADM2-3_0_0-B58, TZA-ADM2-3_0_0-B137, TZA-...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1991-9686-SDN</td>\n",
       "      <td>[SDN-ADM2-3_0_0-B120, SDN-ADM2-3_0_0-B141, SSD...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>1994-9422-KEN</td>\n",
       "      <td>[KEN-ADM2-3_0_0-B44, KEN-ADM2-3_0_0-B24, KEN-A...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>1996-9119-DJI</td>\n",
       "      <td>[DJI-ADM2-3_0_0-B1, DJI-ADM2-3_0_0-B8, DJI-ADM...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>1996-9326-KEN</td>\n",
       "      <td>[KEN-ADM2-3_0_0-B44, KEN-ADM2-3_0_0-B61, KEN-A...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>1997-9091-ETH</td>\n",
       "      <td>[ETH-ADM2-3_0_0-B30, ETH-ADM2-3_0_0-B4, ETH-AD...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>1996-9326-TZA</td>\n",
       "      <td>[TZA-ADM2-3_0_0-B58, TZA-ADM2-3_0_0-B137, TZA-...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>1996-9089-RWA</td>\n",
       "      <td>[RWA-ADM2-3_0_0-B8]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>1996-9220-SDN</td>\n",
       "      <td>[SDN-ADM2-3_0_0-B51, SDN-ADM2-3_0_0-B46, SDN-A...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>1998-9237-ETH</td>\n",
       "      <td>[ETH-ADM2-3_0_0-B59, ETH-ADM2-3_0_0-B19, ETH-A...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>2022-9174-ETH</td>\n",
       "      <td>[ETH-ADM2-3_0_0-B30, ETH-ADM2-3_0_0-B4, ETH-AD...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "           Dis No                                     geob_adm2_list\n",
       "0   1991-9224-KEN  [KEN-ADM2-3_0_0-B65, KEN-ADM2-3_0_0-B4, KEN-AD...\n",
       "1   1993-9573-ERI  [ERI-ADM2-3_0_0-B26, ERI-ADM2-3_0_0-B22, ERI-A...\n",
       "2   1990-9289-SDN  [SSD-ADM2-3_0_0-B24, SSD-ADM2-3_0_0-B25, SSD-A...\n",
       "3   1990-9604-TZA  [TZA-ADM2-3_0_0-B58, TZA-ADM2-3_0_0-B137, TZA-...\n",
       "4   1991-9686-SDN  [SDN-ADM2-3_0_0-B120, SDN-ADM2-3_0_0-B141, SSD...\n",
       "5   1994-9422-KEN  [KEN-ADM2-3_0_0-B44, KEN-ADM2-3_0_0-B24, KEN-A...\n",
       "6   1996-9119-DJI  [DJI-ADM2-3_0_0-B1, DJI-ADM2-3_0_0-B8, DJI-ADM...\n",
       "7   1996-9326-KEN  [KEN-ADM2-3_0_0-B44, KEN-ADM2-3_0_0-B61, KEN-A...\n",
       "8   1997-9091-ETH  [ETH-ADM2-3_0_0-B30, ETH-ADM2-3_0_0-B4, ETH-AD...\n",
       "9   1996-9326-TZA  [TZA-ADM2-3_0_0-B58, TZA-ADM2-3_0_0-B137, TZA-...\n",
       "10  1996-9089-RWA                                [RWA-ADM2-3_0_0-B8]\n",
       "11  1996-9220-SDN  [SDN-ADM2-3_0_0-B51, SDN-ADM2-3_0_0-B46, SDN-A...\n",
       "12  1998-9237-ETH  [ETH-ADM2-3_0_0-B59, ETH-ADM2-3_0_0-B19, ETH-A...\n",
       "13  2022-9174-ETH  [ETH-ADM2-3_0_0-B30, ETH-ADM2-3_0_0-B4, ETH-AD..."
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "dfb_dr1=dfb_dr[dfb_dr['geob'].notna()]\n",
    "dfb_dr1['geob_list']=dfb_dr1['geob'].str.split(',')\n",
    "\n",
    "def get_adm1_list(my_list):\n",
    "    keys = ['ADM1','ADM2']\n",
    "    aa=[[value for value in my_list if key in value] for key in set(keys)][0]\n",
    "    bb=[[value for value in my_list if key in value] for key in set(keys)][1]\n",
    "    #if not aa:\n",
    "    #    bb=['']\n",
    "    #else:\n",
    "    #    bb=aa\n",
    "    return aa,bb\n",
    "\n",
    "\n",
    "aa_list=[]\n",
    "bb_list=[]\n",
    "for idx,row in dfb_dr1.iterrows():\n",
    "    #dfb_dr1.loc[idx,'geob_adm2_list'] = get_adm1_list(row['geob_list'])\n",
    "    aa,bb=get_adm1_list(row['geob_list'])\n",
    "    #dfb_dr1.loc[idx,'geob_adm2_list'] =aa\n",
    "    #print(aa)\n",
    "    aa_list.append(aa)\n",
    "    bb_list.append(bb)\n",
    "\n",
    "dfb_dr1['geob_adm2_list']=aa_list\n",
    "dfb_dr1['geob_adm1_list']=bb_list\n",
    "\n",
    "\n",
    "# dfb_dr1['geob_adm2'] = dfb_dr1['geob_list'].apply(lambda x: [geob_db3_dict[i] for i in x])\n",
    "dfb_dr1_adm1=dfb_dr1[['Dis No','geob_adm1_list']]\n",
    "dfb_dr1_adm1_a=dfb_dr1_adm1[dfb_dr1_adm1['geob_adm1_list'].notna()]\n",
    "dfb_dr1_adm1_a=dfb_dr1_adm1[dfb_dr1_adm1['geob_adm1_list'].map(lambda d: len(d)) > 0]\n",
    "dfb_dr1_adm1_a['geob_adm2_list'] = dfb_dr1_adm1_a['geob_adm1_list'].apply(lambda x: [geob_db3_dict[i] for i in x])\n",
    "\n",
    "\n",
    "def flatten(l):\n",
    "    return [item for sublist in l for item in sublist]\n",
    "\n",
    "aa_list=[]\n",
    "for idx,row in dfb_dr1_adm1_a.iterrows():\n",
    "    aa=flatten(row['geob_adm2_list'])\n",
    "    aa_list.append(aa)\n",
    "    #dfb_dr1_adm1_a.loc[idx,'geob_adm2_list_a'] = flatten(row['geob_adm2_list'])\n",
    "    \n",
    "dfb_dr1_adm1_a['geob_adm2_list_a']=aa_list\n",
    "\n",
    "dfb_dr1_adm1_b=dfb_dr1_adm1_a[['Dis No','geob_adm2_list_a']]\n",
    "\n",
    "dfb_dr2=pd.merge(dfb_dr1,dfb_dr1_adm1_b,on='Dis No',how='left')\n",
    "\n",
    "dfb_dr2['geob_adm2_list_a'] = dfb_dr2['geob_adm2_list_a'].apply(lambda d: d if isinstance(d, list) else [])\n",
    "\n",
    "\n",
    "geob_db1=geob_db[geob_db['shapeGroup']=='ERI']\n",
    "eri_adm2_list=geob_db1['shapeID'].tolist()\n",
    "\n",
    "geob_db1=geob_db[geob_db['shapeGroup']=='ETH']\n",
    "eth_adm2_list=geob_db1['shapeID'].tolist()\n",
    "\n",
    "geob_db1=geob_db[geob_db['shapeGroup']=='SDN']\n",
    "sdn_adm2_list=geob_db1['shapeID'].tolist()\n",
    "\n",
    "geob_db1=geob_db[geob_db['shapeGroup']=='SSD']\n",
    "ssd_adm2_list=geob_db1['shapeID'].tolist()\n",
    "\n",
    "old_sdn_list=sdn_adm2_list+ssd_adm2_list\n",
    "\n",
    "geob_db1=geob_db[geob_db['shapeGroup']=='DJI']\n",
    "dji_adm2_list=geob_db1['shapeID'].tolist()\n",
    "\n",
    "\n",
    "dfb_dr2.at[1,'geob_adm2_list']=eri_adm2_list\n",
    "\n",
    "dfb_dr2.at[4,'geob_adm2_list']=old_sdn_list\n",
    "\n",
    "dfb_dr2.at[6,'geob_adm2_list']=dji_adm2_list\n",
    "\n",
    "dfb_dr2.at[12,'geob_adm2_list']=eth_adm2_list\n",
    "\n",
    "dfb_dr2['geob_adm2_list_a']=(dfb_dr2.geob_adm2_list+dfb_dr2.geob_adm2_list_a).map(set).map(list)\n",
    "\n",
    "#from itertools import chain\n",
    "\n",
    "#dfb_dr2['geob_adm2_list_a'] = [list(dict.fromkeys(chain(x, y))) for x, y in zip(dfb_dr2['geob_adm2_list'], dfb_dr2['geob_adm2_list_a'])]\n",
    "#dfb_dr2.info()\n",
    "\n",
    "dfb_dr_part3=dfb_dr2[['Dis No','geob_adm2_list_a']]\n",
    "\n",
    "dfb_dr_part3.columns=['Dis No','geob_adm2_list']\n",
    "dfb_dr_part3"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "545dea74",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2022-10-31T06:58:51.838120Z",
     "start_time": "2022-10-31T06:58:51.818844Z"
    }
   },
   "outputs": [],
   "source": [
    "dfb_parts=pd.concat([dfb_dr_part1,dfb_dr_part2,dfb_dr_part3],axis=0)\n",
    "df=dfb_parts.groupby('Dis No').agg({'geob_adm2_list': 'sum'})\n",
    "df['geob_adm2_list'] = df['geob_adm2_list'].apply(lambda x: list(set(x)))\n",
    "\n",
    "#dfb_parts1=dfb_parts.drop_duplicates('Dis No')\n",
    "#dfb_dr1=pd.merge(dfb_dr,dfb_parts1,on='Dis No',how='left')\n",
    "#dfb_parts1.info()\n",
    "#dfb_parts1.to_excel(\"em_dat_ea_drought_adm2.xlsx\",index=False)  "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "c0e4ffc3",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2022-10-31T07:02:46.507996Z",
     "start_time": "2022-10-31T07:02:46.495308Z"
    }
   },
   "outputs": [],
   "source": [
    "df=dfb_parts.groupby('Dis No').agg({'geob_adm2_list': 'sum'})\n",
    "#df.to_csv('/home/bulbul/Documents/07-2022/impact_weather_icpac/lab/ea_climada/ea-ibf-climada/impact_catalog/test1.csv')\n",
    "df['geob_adm2_list1'] = df['geob_adm2_list'].apply(lambda x: list(set(x)))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "a0a19100",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2022-10-31T07:30:31.506943Z",
     "start_time": "2022-10-31T07:30:31.489110Z"
    }
   },
   "outputs": [],
   "source": [
    "df1=df[['geob_adm2_list1']]\n",
    "df2=df1.reset_index()\n",
    "df2.columns=['Dis No','geob_adm2_list']\n",
    "df2.to_excel(\"em_dat_ea_drought_adm2_v2.xlsx\",index=False)  "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "id": "06ea17d6",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2022-10-31T07:30:24.451800Z",
     "start_time": "2022-10-31T07:30:24.437343Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Dis No</th>\n",
       "      <th>geob_adm2_list</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1990-9289-SDN</td>\n",
       "      <td>[SSD-ADM2-3_0_0-B24, SSD-ADM2-3_0_0-B26, SSD-A...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1990-9604-TZA</td>\n",
       "      <td>[TZA-ADM2-3_0_0-B4, TZA-ADM2-3_0_0-B45, TZA-AD...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1991-9224-KEN</td>\n",
       "      <td>[KEN-ADM2-3_0_0-B63, KEN-ADM2-3_0_0-B68, KEN-A...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1991-9686-SDN</td>\n",
       "      <td>[SDN-ADM2-3_0_0-B139, SDN-ADM2-3_0_0-B126, SDN...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1993-9573-ERI</td>\n",
       "      <td>[ERI-ADM2-3_0_0-B21, ERI-ADM2-3_0_0-B28, ERI-A...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>57</th>\n",
       "      <td>2021-9152-KEN</td>\n",
       "      <td>[KEN-ADM2-3_0_0-B58, KEN-ADM2-3_0_0-B26, KEN-A...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>58</th>\n",
       "      <td>2021-9152-SOM</td>\n",
       "      <td>[SOM-ADM2-3_0_0-B58, SOM-ADM2-3_0_0-B21, SOM-A...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>59</th>\n",
       "      <td>2021-9546-ETH</td>\n",
       "      <td>[ETH-ADM2-3_0_0-B19, ETH-ADM2-3_0_0-B45, ETH-A...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>60</th>\n",
       "      <td>2021-9639-SSD</td>\n",
       "      <td>[SSD-ADM2-3_0_0-B41, SSD-ADM2-3_0_0-B24, SSD-A...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>61</th>\n",
       "      <td>2022-9174-ETH</td>\n",
       "      <td>[ETH-ADM2-3_0_0-B51, ETH-ADM2-3_0_0-B54, ETH-A...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>62 rows × 2 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "           Dis No                                     geob_adm2_list\n",
       "0   1990-9289-SDN  [SSD-ADM2-3_0_0-B24, SSD-ADM2-3_0_0-B26, SSD-A...\n",
       "1   1990-9604-TZA  [TZA-ADM2-3_0_0-B4, TZA-ADM2-3_0_0-B45, TZA-AD...\n",
       "2   1991-9224-KEN  [KEN-ADM2-3_0_0-B63, KEN-ADM2-3_0_0-B68, KEN-A...\n",
       "3   1991-9686-SDN  [SDN-ADM2-3_0_0-B139, SDN-ADM2-3_0_0-B126, SDN...\n",
       "4   1993-9573-ERI  [ERI-ADM2-3_0_0-B21, ERI-ADM2-3_0_0-B28, ERI-A...\n",
       "..            ...                                                ...\n",
       "57  2021-9152-KEN  [KEN-ADM2-3_0_0-B58, KEN-ADM2-3_0_0-B26, KEN-A...\n",
       "58  2021-9152-SOM  [SOM-ADM2-3_0_0-B58, SOM-ADM2-3_0_0-B21, SOM-A...\n",
       "59  2021-9546-ETH  [ETH-ADM2-3_0_0-B19, ETH-ADM2-3_0_0-B45, ETH-A...\n",
       "60  2021-9639-SSD  [SSD-ADM2-3_0_0-B41, SSD-ADM2-3_0_0-B24, SSD-A...\n",
       "61  2022-9174-ETH  [ETH-ADM2-3_0_0-B51, ETH-ADM2-3_0_0-B54, ETH-A...\n",
       "\n",
       "[62 rows x 2 columns]"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "343985f1",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "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.8.13"
  },
  "toc": {
   "base_numbering": 1,
   "nav_menu": {},
   "number_sections": true,
   "sideBar": true,
   "skip_h1_title": false,
   "title_cell": "Table of Contents",
   "title_sidebar": "Contents",
   "toc_cell": false,
   "toc_position": {},
   "toc_section_display": true,
   "toc_window_display": false
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}