Upsampling_GPI_score_for_all_countries.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**I work on the base environment**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Insert all GPI data with the corresponding fips_10_4 codes as created using Fips10_4.csv and gpi_all_countries.csv."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "metadata": {},
   "outputs": [],
   "source": [
    "gpifips = pd.read_csv('../../Gpi_fips_10_4j.csv')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Delete the columns with the full country names"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {},
   "outputs": [],
   "source": [
    "del gpifips['Country']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {},
   "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>FIPS_10_4</th>\n",
       "      <th>2008</th>\n",
       "      <th>2009</th>\n",
       "      <th>2010</th>\n",
       "      <th>2011</th>\n",
       "      <th>2012</th>\n",
       "      <th>2013</th>\n",
       "      <th>2014</th>\n",
       "      <th>2015</th>\n",
       "      <th>2016</th>\n",
       "      <th>2017</th>\n",
       "      <th>2018</th>\n",
       "      <th>2019</th>\n",
       "      <th>2020</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>AF</td>\n",
       "      <td>3.350</td>\n",
       "      <td>3.426</td>\n",
       "      <td>3.485</td>\n",
       "      <td>3.392</td>\n",
       "      <td>3.442</td>\n",
       "      <td>3.353</td>\n",
       "      <td>3.321</td>\n",
       "      <td>3.293</td>\n",
       "      <td>3.391</td>\n",
       "      <td>3.347</td>\n",
       "      <td>3.350</td>\n",
       "      <td>3.300</td>\n",
       "      <td>3.644</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>AL</td>\n",
       "      <td>1.943</td>\n",
       "      <td>1.916</td>\n",
       "      <td>1.912</td>\n",
       "      <td>1.938</td>\n",
       "      <td>1.976</td>\n",
       "      <td>1.995</td>\n",
       "      <td>1.960</td>\n",
       "      <td>1.948</td>\n",
       "      <td>1.900</td>\n",
       "      <td>1.920</td>\n",
       "      <td>1.843</td>\n",
       "      <td>1.821</td>\n",
       "      <td>1.872</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>AG</td>\n",
       "      <td>2.347</td>\n",
       "      <td>2.347</td>\n",
       "      <td>2.370</td>\n",
       "      <td>2.512</td>\n",
       "      <td>2.355</td>\n",
       "      <td>2.314</td>\n",
       "      <td>2.283</td>\n",
       "      <td>2.261</td>\n",
       "      <td>2.197</td>\n",
       "      <td>2.188</td>\n",
       "      <td>2.188</td>\n",
       "      <td>2.219</td>\n",
       "      <td>2.287</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  FIPS_10_4   2008   2009   2010   2011   2012   2013   2014   2015   2016  \\\n",
       "0        AF  3.350  3.426  3.485  3.392  3.442  3.353  3.321  3.293  3.391   \n",
       "1        AL  1.943  1.916  1.912  1.938  1.976  1.995  1.960  1.948  1.900   \n",
       "2        AG  2.347  2.347  2.370  2.512  2.355  2.314  2.283  2.261  2.197   \n",
       "\n",
       "    2017   2018   2019   2020  \n",
       "0  3.347  3.350  3.300  3.644  \n",
       "1  1.920  1.843  1.821  1.872  \n",
       "2  2.188  2.188  2.219  2.287  "
      ]
     },
     "execution_count": 30,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "gpifips.head(3)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Transpose the dataframe so that I have one row per each country and year.<br>  "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {},
   "outputs": [],
   "source": [
    "Ngpifips = gpifips.set_index('FIPS_10_4', append=True).stack().reset_index().drop('level_0',axis=1)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Rename columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {},
   "outputs": [],
   "source": [
    "Ngpifips.columns=['country','year','gpi_score']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {
    "scrolled": true
   },
   "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>country</th>\n",
       "      <th>year</th>\n",
       "      <th>gpi_score</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>AF</td>\n",
       "      <td>2008</td>\n",
       "      <td>3.35</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  country  year  gpi_score\n",
       "0      AF  2008       3.35"
      ]
     },
     "execution_count": 33,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "Ngpifips.head(1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array(['AF', 'AL', 'AG', 'AO', 'AR', 'AM', 'AS', 'AU', 'AJ', 'BA', 'BG',\n",
       "       'BO', 'BE', 'BN', 'BT', 'BL', 'BK', 'BC', 'BR', 'BU', 'UV', 'BY',\n",
       "       'CB', 'CM', 'CA', 'CT', 'CD', 'CI', 'CH', 'CO', 'CS', 'IV', 'HR',\n",
       "       'CU', 'CY', 'EZ', 'CG', 'DA', 'DJ', 'DR', 'TT', 'EC', 'EG', 'ES',\n",
       "       'EK', 'ER', 'EN', 'WZ', 'ET', 'FI', 'FR', 'GB', 'GA', 'GG', 'GM',\n",
       "       'GH', 'GR', 'GT', 'GV', 'PU', 'GY', 'HA', 'HO', 'HU', 'IC', 'IN',\n",
       "       'ID', 'IR', 'IZ', 'EI', 'IS', 'IT', 'JM', 'JA', 'JO', 'KZ', 'KE',\n",
       "       'KU', 'KG', 'LA', 'LG', 'LE', 'LT', 'LI', 'LY', 'LH', 'MA', 'MI',\n",
       "       'MY', 'ML', 'MR', 'MP', 'MX', 'MD', 'MG', 'MJ', 'MO', 'MZ', 'BM',\n",
       "       'WA', 'NP', 'NL', 'NZ', 'NU', 'NG', 'NI', 'KN', 'MK', 'NO', 'MU',\n",
       "       'PK', 'WE', 'PM', 'PP', 'PA', 'PE', 'RP', 'PL', 'PO', 'QA', 'CF',\n",
       "       'RO', 'RS', 'RW', 'SA', 'SG', 'RI', 'SL', 'SN', 'LO', 'SI', 'SO',\n",
       "       'SF', 'KS', 'OD', 'SP', 'CE', 'SU', 'SW', 'SZ', 'SY', 'TW', 'TI',\n",
       "       'TZ', 'TH', 'TO', 'TD', 'TS', 'TU', 'TX', 'UG', 'UP', 'AE', 'UK',\n",
       "       'US', 'UY', 'UZ', 'VE', 'VM', 'YM', 'ZA', 'ZI'], dtype=object)"
      ]
     },
     "execution_count": 34,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "countries=Ngpifips['country'].unique()\n",
    "countries"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Control whether year data are datetime data that we need for the interpolation"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "dtype('O')"
      ]
     },
     "execution_count": 35,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "Ngpifips.year.dtype"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "They are object data, so let's convert them to datetime."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "metadata": {},
   "outputs": [],
   "source": [
    "Ngpifips['year'] = pd.to_datetime(Ngpifips['year'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "dtype('<M8[ns]')"
      ]
     },
     "execution_count": 37,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "Ngpifips.year.dtype"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Verify whether my machine is accepting '<M8[ns]' as datetime"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "True"
      ]
     },
     "execution_count": 38,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "np.dtype('datetime64[ns]') == np.dtype('<M8[ns]')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Interpolation for each country"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Upsample"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 178,
   "metadata": {},
   "outputs": [],
   "source": [
    "for c in countries:\n",
    "    single_country = Ngpifips.loc[Ngpifips.country == c] \n",
    "    single_country = single_country.drop('country',axis=1)\n",
    "    single_country.set_index('year', inplace = True)  needing to rename it\n",
    "    upsampled = single_country.resample('MS') \n",
    "    gpi_interp = upsampled.interpolate(method='linear')\n",
    "    firstdate = gpi_interp.index[0]\n",
    "    extradates = pd.date_range(firstdate, periods = len(gpi_interp)+2 , freq='MS') \n",
    "    gpi_interp = gpi_interp.reindex(extradates) \n",
    "    shifted = gpi_interp.shift(2).dropna()\n",
    "    newind = shifted.index.strftime('%Y%m')\n",
    "    shifted.index = newind \n",
    "    shifted.reset_index(inplace=True)\n",
    "    shifted.columns = ['MonthYear','GPI_score']\n",
    "    shifted.to_csv('../../interpolated_gpi/interpolated_gpi_%s.csv' %c, index=False)"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.7.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}