clean_extend_pip_data.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Cleaning and preparing the data\n",
    "\n",
    "The objectives of this notebook are\n",
    "- 'See' the data available\n",
    "- Nowcast the consumption data by the PIP (poverty and inequality database by the worldbank)\n",
    "\n",
    "Specifically the outputs and goals are \n",
    "1. Load variables from the PIP and the World Bank Development Indicators\n",
    "2. Clean and extend them\n",
    "3. Finalize a dataframe of 150 countries roughly with all its parameters for a global climate-constrained convergence model"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## First step is to load PIP itself"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "['AGO' 'ALB' 'ARE' 'ARG' 'ARM' 'AUS' 'AUT' 'AZE' 'BDI' 'BEL' 'BEN' 'BFA'\n",
      " 'BGD' 'BGR' 'BIH' 'BLR' 'BLZ' 'BOL' 'BRA' 'BTN' 'BWA' 'CAF' 'CAN' 'CHE'\n",
      " 'CHL' 'CHN' 'CIV' 'CMR' 'COD' 'COG' 'COL' 'COM' 'CPV' 'CRI' 'CYP' 'CZE'\n",
      " 'DEU' 'DJI' 'DNK' 'DOM' 'DZA' 'ECU' 'EGY' 'ESP' 'EST' 'ETH' 'FIN' 'FJI'\n",
      " 'FRA' 'FSM' 'GAB' 'GBR' 'GEO' 'GHA' 'GIN' 'GMB' 'GNB' 'GRC' 'GTM' 'GUY'\n",
      " 'HND' 'HRV' 'HTI' 'HUN' 'IDN' 'IND' 'IRL' 'IRN' 'IRQ' 'ISL' 'ISR' 'ITA'\n",
      " 'JAM' 'JOR' 'JPN' 'KAZ' 'KEN' 'KGZ' 'KIR' 'KOR' 'LAO' 'LBN' 'LBR' 'LCA'\n",
      " 'LKA' 'LSO' 'LTU' 'LUX' 'LVA' 'MAR' 'MDA' 'MDG' 'MDV' 'MEX' 'MHL' 'MKD'\n",
      " 'MLI' 'MLT' 'MMR' 'MNE' 'MNG' 'MOZ' 'MRT' 'MUS' 'MWI' 'MYS' 'NAM' 'NER'\n",
      " 'NGA' 'NIC' 'NLD' 'NOR' 'NPL' 'NRU' 'PAK' 'PAN' 'PER' 'PHL' 'PNG' 'POL'\n",
      " 'PRT' 'PRY' 'PSE' 'ROU' 'RUS' 'RWA' 'SDN' 'SEN' 'SLB' 'SLE' 'SLV' 'SRB'\n",
      " 'SSD' 'STP' 'SUR' 'SVK' 'SVN' 'SWE' 'SWZ' 'SYC' 'SYR' 'TCD' 'TGO' 'THA'\n",
      " 'TJK' 'TKM' 'TLS' 'TON' 'TTO' 'TUN' 'TUR' 'TUV' 'TWN' 'TZA' 'UGA' 'UKR'\n",
      " 'URY' 'USA' 'UZB' 'VEN' 'VNM' 'VUT' 'WSM' 'XKX' 'YEM' 'ZAF' 'ZMB' 'ZWE']\n",
      "168\n",
      "[2000.21 2008.5  2018.17 1996.   2002.   2005.   2008.   2012.   2014.\n",
      " 2015.   2016.   2017.   2018.   2019.   2020.   2013.42 1980.   1986.\n",
      " 1987.   1991.   1992.   1993.   1994.   1995.   1997.   1998.   1999.\n",
      " 2000.   2001.   2003.   2004.   2006.   2007.   2009.   2010.   2011.\n",
      " 2013.   2021.   1981.   1985.   1989.   1998.5  2013.5  1988.   2011.33\n",
      " 2018.5  1994.25 1983.5  1985.5  1988.5  1991.5  1995.5  2022.   1990.5\n",
      " 1982.   1983.   1984.   1990.   1985.57 1993.33 2002.53 2009.25 2015.85\n",
      " 1992.43 1971.   1973.   1975.   1977.   1979.   1985.08 1986.08 1987.17\n",
      " 1988.33 1992.57 2004.89 2012.4  2011.67 2001.77 2007.33 2011.17 1995.75\n",
      " 1999.75 2004.49 2008.24 2010.49 2012.49 2017.75 2019.43 1981.33 1995.25\n",
      " 1999.5  2004.5  2010.5  2015.5  2002.5  2013.24 2019.15 1970.   1968.\n",
      " 1969.   1972.   1974.   1976.   1978.   1987.5  1991.73 1998.18 2005.67\n",
      " 2012.8  2016.75 1994.08 2002.25 2003.29 2010.08 2015.31 2020.08 2018.43\n",
      " 1986.5  1992.5  1977.5  1993.5  2009.5  2011.5  2015.25 2016.25 2017.25\n",
      " 2018.25 2019.25 2020.25 2021.25 2005.23 2006.23 2009.23 2013.23 2014.23\n",
      " 2015.23 2016.23 2017.23 2018.23 2019.23 2006.85 2006.14 2008.16 2010.24\n",
      " 2005.38 2015.67 2019.27 1992.2  1997.2  2007.23 2012.25 2018.42 2011.77\n",
      " 2006.5  2012.5  1986.54 1994.45 2002.75 2017.14 1984.5  1990.77 1998.15\n",
      " 2000.83 2006.91 1993.3  2012.73 2019.6  2019.5  2001.08 2006.25 2009.89\n",
      " 2018.57 2002.15 2007.5  1996.27 2008.67 2014.44 2019.92 1995.57 2000.5\n",
      " 2004.55 2008.2  1997.83 2004.23 2010.23 2016.31 2019.31 2008.25 2015.88\n",
      " 2018.46 1993.79 2003.67 2009.54 2015.27 1992.85 1994.36 2007.31 1985.25\n",
      " 1996.25 2018.75 2003.25 2010.17 2012.69 1996.5  2001.5  2005.5  2009.67\n",
      " 2000.38 2005.75 2010.83 2013.75 2016.77 1991.25 1994.33 2001.31 2005.8\n",
      " 2011.29 2012.79 1989.75 2016.5  1994.83 2000.92 2016.17 1999.62 2006.43\n",
      " 2018.08 1996.75 2003.5  2000.75 2005.34 2010.41 2015.42 1991.92 2000.33\n",
      " 2017.92 1992.23 1996.2  1999.55 2002.36 2005.33 2009.4  2012.45 2019.64\n",
      " 1963.   1964.   1965.   1966.   1967.   1992.77 1997.92 2019.21 2013.25\n",
      " 2005.25 2014.83 2002.83 2004.67]\n",
      "[2000 2008 2018 1996 2002 2005 2012 2014 2015 2016 2017 2019 2020 2013\n",
      " 1980 1986 1987 1991 1992 1993 1994 1995 1997 1998 1999 2001 2003 2004\n",
      " 2006 2007 2009 2010 2011 2021 1981 1985 1989 1988 1983 2022 1990 1982\n",
      " 1984 1971 1973 1975 1977 1979 1970 1968 1969 1972 1974 1976 1978 1963\n",
      " 1964 1965 1966 1967]\n",
      "60\n",
      "99\n"
     ]
    }
   ],
   "source": [
    "'''This notebook converts the data from the PIP survey into a format that is easier to work with for modelling purposes.\n",
    "The goal is to have an easy to handle but comprehensive dataframe that contains all necessary parameters for a global model of income convergence with emissions.\n",
    "this notebook heavily draws from the folder data/pip_all_data which contains all data from the PIP survey in the original format.'''\n",
    "\n",
    "# load all pip data and analyse\n",
    "import os\n",
    "import pandas as pd\n",
    "import numpy as np\n",
    "\n",
    "# load all data from pip which is in this file path and subfolders\n",
    "\n",
    "file_path = os.path.join('.', 'data/pip_all_data', 'pip.csv')\n",
    "\n",
    "# load data into pandas dataframe\n",
    "pip_data = pd.read_csv(file_path)\n",
    "\n",
    "\n",
    "# find all unique values in the column \"country_code\" and count them\n",
    "country_codes = pip_data['country_code'].unique()\n",
    "print(country_codes)\n",
    "print(len(country_codes))\n",
    "\n",
    "# find all unique values in the column \"survey year\" and count them but subject to the condition that if a value has decimal value after . then cut this off and count only the integer value\n",
    "survey_years = pip_data['survey_year'].unique()\n",
    "print(survey_years) # this shows that there are some values with decimal values after . so we need to cut them off\n",
    "survey_years = pip_data['survey_year'].astype(int).unique()\n",
    "print(survey_years)\n",
    "print(len(survey_years))\n",
    "\n",
    "# clean the data such that the column \"survey_year\" only contains integer values\n",
    "pip_data['survey_year'] = pip_data['survey_year'].astype(int)\n",
    "\n",
    "# now count how often the value 2018 appears by looping over the column \"survey_year\" and counting how often the value 2018 appears in this column and also count if there is a value with decimal \n",
    "count = 0   \n",
    "for year in pip_data['survey_year']:\n",
    "    if int(year) == 2018:\n",
    "        count += 1\n",
    "print(count)\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Check which countries can be actually used well based on data availability"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "{'AGO': 2018, 'ALB': 2020, 'ARE': 2018, 'ARG': 2021, 'ARM': 2021, 'AUS': 2018, 'AUT': 2020, 'AZE': 2005, 'BDI': 2013, 'BEL': 2020, 'BEN': 2018, 'BFA': 2018, 'BGD': 2022, 'BGR': 2020, 'BIH': 2011, 'BLR': 2020, 'BLZ': 1999, 'BOL': 2021, 'BRA': 2021, 'BTN': 2022, 'BWA': 2015, 'CAF': 2021, 'CAN': 2019, 'CHE': 2018, 'CHL': 2020, 'CHN': 2020, 'CIV': 2018, 'CMR': 2014, 'COD': 2012, 'COG': 2011, 'COL': 2021, 'COM': 2014, 'CPV': 2015, 'CRI': 2022, 'CYP': 2020, 'CZE': 2020, 'DEU': 2019, 'DJI': 2017, 'DNK': 2020, 'DOM': 2021, 'DZA': 2011, 'ECU': 2022, 'EGY': 2019, 'ESP': 2020, 'EST': 2020, 'ETH': 2015, 'FIN': 2020, 'FJI': 2019, 'FRA': 2020, 'FSM': 2013, 'GAB': 2017, 'GBR': 2020, 'GEO': 2021, 'GHA': 2016, 'GIN': 2018, 'GMB': 2020, 'GNB': 2018, 'GRC': 2020, 'GTM': 2014, 'GUY': 1998, 'HND': 2019, 'HRV': 2020, 'HTI': 2012, 'HUN': 2020, 'IDN': 2022, 'IND': 2021, 'IRL': 2020, 'IRN': 2019, 'IRQ': 2012, 'ISL': 2017, 'ISR': 2018, 'ITA': 2020, 'JAM': 2004, 'JOR': 2010, 'JPN': 2013, 'KAZ': 2018, 'KEN': 2021, 'KGZ': 2020, 'KIR': 2019, 'KOR': 2016, 'LAO': 2018, 'LBN': 2011, 'LBR': 2016, 'LCA': 2016, 'LKA': 2019, 'LSO': 2017, 'LTU': 2020, 'LUX': 2020, 'LVA': 2020, 'MAR': 2013, 'MDA': 2021, 'MDG': 2012, 'MDV': 2019, 'MEX': 2020, 'MHL': 2019, 'MKD': 2019, 'MLI': 2018, 'MLT': 2020, 'MMR': 2017, 'MNE': 2018, 'MNG': 2018, 'MOZ': 2019, 'MRT': 2014, 'MUS': 2017, 'MWI': 2019, 'MYS': 2018, 'NAM': 2015, 'NER': 2018, 'NGA': 2018, 'NIC': 2014, 'NLD': 2020, 'NOR': 2019, 'NPL': 2010, 'NRU': 2012, 'PAK': 2018, 'PAN': 2021, 'PER': 2021, 'PHL': 2021, 'PNG': 2009, 'POL': 2019, 'PRT': 2020, 'PRY': 2022, 'PSE': 2016, 'ROU': 2020, 'RUS': 2020, 'RWA': 2016, 'SDN': 2014, 'SEN': 2018, 'SLB': 2012, 'SLE': 2018, 'SLV': 2022, 'SRB': 2020, 'SSD': 2016, 'STP': 2017, 'SUR': 1999, 'SVK': 2019, 'SVN': 2020, 'SWE': 2020, 'SWZ': 2016, 'SYC': 2018, 'SYR': 2003, 'TCD': 2018, 'TGO': 2018, 'THA': 2021, 'TJK': 2015, 'TKM': 1998, 'TLS': 2014, 'TON': 2015, 'TTO': 1992, 'TUN': 2015, 'TUR': 2019, 'TUV': 2010, 'TWN': 2016, 'TZA': 2017, 'UGA': 2019, 'UKR': 2020, 'URY': 2021, 'USA': 2021, 'UZB': 2003, 'VEN': 2006, 'VNM': 2020, 'VUT': 2019, 'WSM': 2013, 'XKX': 2017, 'YEM': 2014, 'ZAF': 2014, 'ZMB': 2015, 'ZWE': 2019}\n",
      "        0\n",
      "AGO  2018\n",
      "ALB  2020\n",
      "ARE  2018\n",
      "ARG  2021\n",
      "ARM  2021\n",
      "..    ...\n",
      "XKX  2017\n",
      "YEM  2014\n",
      "ZAF  2014\n",
      "ZMB  2015\n",
      "ZWE  2019\n",
      "\n",
      "[168 rows x 1 columns]\n"
     ]
    }
   ],
   "source": [
    "# find all countries for which there is data for the year 2018 and if there is no data for 2018 then which is the next year for which data is available\n",
    "# loop over all countries and check if there is data for 2018 and make a dictionary indicating which is the most recent year with data availability\n",
    "# Find all countries for which there is data for the year 2018,\n",
    "# and if there is no data for 2018, then find the next year for which data is available.\n",
    "country_year_available = {}\n",
    "\n",
    "for country in country_codes:\n",
    "    # Filter the dataframe for the specific country\n",
    "    country_data = pip_data[pip_data['country_code'] == country]\n",
    "\n",
    "    # Iterate over the years in descending order\n",
    "    for year in sorted(survey_years, reverse=True):\n",
    "        # Check if there is data for the current year\n",
    "        if len(country_data[country_data['survey_year'] == year]) > 0:\n",
    "            country_year_available[country] = year\n",
    "            break  # Stop as soon as data is found for a year\n",
    "\n",
    "print(country_year_available)\n",
    "\n",
    "# store the dict country_year_available as a csv file\n",
    "country_year_available_df = pd.DataFrame.from_dict(country_year_available, orient='index')\n",
    "print(country_year_available_df)\n",
    "# the path should be the file path from above but with the file name country_year_available.csv\n",
    "country_year_available_df.to_csv('./data/pip_all_data/country_year_available.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "    country  year welfare_type\n",
      "0       AGO  2018  consumption\n",
      "1       ALB  2020  consumption\n",
      "2       ARE  2018       income\n",
      "3       ARG  2021       income\n",
      "4       ARM  2021  consumption\n",
      "..      ...   ...          ...\n",
      "163     XKX  2017  consumption\n",
      "164     YEM  2014  consumption\n",
      "165     ZAF  2014  consumption\n",
      "166     ZMB  2015  consumption\n",
      "167     ZWE  2019  consumption\n",
      "\n",
      "[168 rows x 3 columns]\n"
     ]
    }
   ],
   "source": [
    "# find all countries for which there is data for the year 2018 and if there is no data for 2018 then which is the next year for which data is available\n",
    "# loop over all countries and check if there is data for 2018 and make a dictionary indicating which is the most recent year with data availability\n",
    "# Find all countries for which there is data for the year 2018,\n",
    "# and if there is no data for 2018, then find the next year for which data is available.\n",
    "# also make sure the welfare type is properly considered based on the following rule\n",
    "# use income only if there is no consumption data available and only if the consumption is three or more years older than the income data\n",
    "\n",
    "# Assuming pip_data and country_codes are already defined\n",
    "# Assuming survey_years is a list of years in the dataset\n",
    "\n",
    "# Initialize an empty DataFrame for country_year_available\n",
    "country_year_available = pd.DataFrame(columns=['country', 'year', 'welfare_type'])\n",
    "\n",
    "for country in country_codes:\n",
    "    # Filter the dataframe for the specific country\n",
    "    country_data = pip_data[pip_data['country_code'] == country]\n",
    "\n",
    "    # Initialize variables to store the latest year for each welfare type\n",
    "    latest_year_consumption = None\n",
    "    latest_year_income = None\n",
    "\n",
    "    # Iterate over the years in descending order for each welfare type\n",
    "    for year in sorted(survey_years, reverse=True):\n",
    "        # Check for consumption data\n",
    "        if len(country_data[(country_data['survey_year'] == year) & (country_data['welfare_type'] == 'consumption')]) > 0:\n",
    "            latest_year_consumption = year\n",
    "            break\n",
    "\n",
    "    for year in sorted(survey_years, reverse=True):\n",
    "        # Check for income data\n",
    "        if len(country_data[(country_data['survey_year'] == year) & (country_data['welfare_type'] == 'income')]) > 0:\n",
    "            latest_year_income = year\n",
    "            break\n",
    "\n",
    "    # Determine which year to use based on the availability and comparison of welfare types\n",
    "    if latest_year_consumption is not None and (latest_year_income is None or latest_year_consumption >= latest_year_income - 3):\n",
    "        new_row = pd.DataFrame({'country': [country], 'year': [latest_year_consumption], 'welfare_type': ['consumption']})\n",
    "        country_year_available = pd.concat([country_year_available, new_row], ignore_index=True)\n",
    "    elif latest_year_income is not None:\n",
    "        new_row = pd.DataFrame({'country': [country], 'year': [latest_year_income], 'welfare_type': ['income']})\n",
    "        country_year_available = pd.concat([country_year_available, new_row], ignore_index=True)\n",
    "\n",
    "# Output the results\n",
    "print(country_year_available)\n",
    "\n",
    "# Store the DataFrame as a csv file\n",
    "country_year_available.to_csv('./data/pip_all_data/country_year_available.csv', index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "image/png": "iVBORw0KGgoAAAANSUhEUgAAAjIAAAHHCAYAAACle7JuAAAAOXRFWHRTb2Z0d2FyZQBNYXRwbG90bGliIHZlcnNpb24zLjguMiwgaHR0cHM6Ly9tYXRwbG90bGliLm9yZy8g+/7EAAAACXBIWXMAAA9hAAAPYQGoP6dpAABNmklEQVR4nO3deVxU1f8/8NcMwoAgw74loqKJu4amKOaGormLa6a4peUumsmn3BdccqtcsgxcU9GyxXJDslI0JbewEBdEZSsVUIx1zu8Pf9yvI4sMMsxcej0fj3nonHvm3Pc9M3d4cefeQSGEECAiIiKSIaWhCyAiIiIqKwYZIiIiki0GGSIiIpItBhkiIiKSLQYZIiIiki0GGSIiIpItBhkiIiKSLQYZIiIiki0GGSIiIpItBpkS1KxZEyNHjjR0GZXeypUrUbt2bZiYmKBZs2aGLsfo/PTTT1AoFPjpp58MXUqRKmo/iY+Ph0KhwIcffqj3dZW0/rCwMKlt/vz5UCgUBqmHqCyKes2WdR8ueG/at2/fc/uOHDkSNWvW1HkdpfGfCTJhYWFQKBQ4d+5ckcs7dOiARo0avfB6fvjhB8yfP/+Fx/mvOHLkCGbNmoW2bdsiNDQUS5cuNXRJZGD/hX1ow4YNWoFI7irb9pC8/GeCTFnExsbis88+0+kxP/zwAxYsWKCniiqf48ePQ6lUYsuWLRgxYgRef/11Q5dEOirLflISOe1DH3zwAf7991+dH1fZfvBXtu2pzMr6mjVmDDIlUKlUMDU1NXQZOsnMzDR0CTpJTU2FhYUFzMzMDF2KTuQ2z/okx/2kvFSpUgXm5uaGLkNWKvO+o9FokJWVZegySlQZX7MMMiV49nPD3NxcLFiwAHXr1oW5uTns7e3h6+uLo0ePAnjyGeD69esBAAqFQroVyMzMxIwZM+Du7g6VSoV69erhww8/xLN/gPzff//FlClT4ODggGrVqqF37964e/cuFAqF1iH3gs86r1y5gjfeeAO2trbw9fUFAFy6dAkjR45E7dq1YW5uDhcXF4wePRr37t3TWlfBGFevXsWbb74JtVoNR0dHzJkzB0II3L59G3369IG1tTVcXFywatWqUs1dXl4eFi1aBE9PT6hUKtSsWRP/+9//kJ2dLfVRKBQIDQ1FZmamNFfF/VY3b948mJqa4u+//y60bNy4cbCxsdF6A/nxxx/Rrl07WFpaolq1aujRowdiYmK0HqfrHBU1z886d+4cFAoFtm7dWmjZ4cOHoVAo8P333wMAbt26hQkTJqBevXqwsLCAvb09Bg4ciPj4+CLHflpxn2l36NABHTp00GrLzs7GvHnzUKdOHahUKri7u2PWrFlazwUAHD16FL6+vrCxsYGVlRXq1auH//3vfzrXUvAx7smTJxEUFARHR0dYWlqiX79+RT5/T3vePlRg8+bN0murZcuWOHv2bKE+f/31FwYMGAA7OzuYm5ujRYsW+Pbbb5+7PQCQlpaGkSNHQq1Ww8bGBoGBgUhLSyvUr7hzZHbs2IFXX30VVatWha2tLV577TUcOXIEwJP5iomJwYkTJ6Tte/Y5e5ZGo8G6devQuHFjmJubw9HREd26ddP6qLw0+xyAQu8jBcr6PJa0PQVjnDhxAhMmTICTkxOqV6+OyMhIKBQKfP3114Xq2LVrFxQKBaKiokqcEwB49OgRLC0tMXXq1ELL7ty5AxMTE4SEhEhtaWlpmDZtmvQeXKdOHSxfvhwajUbrsR9++CHatGkDe3t7WFhYwNvbu8jzQBQKBSZNmoSdO3eiYcOGUKlUOHTo0HPrBoBffvkFAwcORI0aNaT9cvr06VpHSz788EMoFArcunWr0OODg4NhZmaGBw8elHo8oHTndd2/fx8zZ85E48aNYWVlBWtra3Tv3h0XL14ssn9+fj7+97//wcXFBZaWlujduzdu37793DnQaDRYu3YtGjZsCHNzczg7O2P8+PHSNpVWFZ16VwLp6en4559/CrXn5uY+97Hz589HSEgIxo4di1dffRUZGRk4d+4cfv/9d3Tp0gXjx49HYmIijh49iu3bt2s9VgiB3r17IzIyEmPGjEGzZs1w+PBhvPvuu7h79y7WrFkj9R05ciT27t2L4cOHo3Xr1jhx4gR69OhRbF0DBw5E3bp1sXTpUikUHT16FDdu3MCoUaPg4uKCmJgYbN68GTExMTh9+nShF/LgwYNRv359LFu2DAcPHsTixYthZ2eHTz/9FJ06dcLy5cuxc+dOzJw5Ey1btsRrr71W4lyNHTsWW7duxYABAzBjxgycOXMGISEh+PPPP6U3r+3bt2Pz5s347bff8PnnnwMA2rRpU+R4w4cPx8KFC7Fnzx5MmjRJas/JycG+ffsQEBAg/Zaxfft2BAYGwt/fH8uXL8fjx4+xceNG+Pr64vz589IJZ7rOUVHz/KwWLVqgdu3a2Lt3LwIDA7WW7dmzB7a2tvD39wcAnD17FqdOncKQIUNQvXp1xMfHY+PGjejQoQOuXLmCqlWrljjHpaHRaNC7d2/8+uuvGDduHOrXr4/Lly9jzZo1uHr1Kg4cOAAAiImJQc+ePdGkSRMsXLgQKpUK165dw8mTJ8u87smTJ8PW1hbz5s1DfHw81q5di0mTJmHPnj3FPqakfajArl278PDhQ4wfPx4KhQIrVqxA//79cePGDenIUExMDNq2bYuXXnoJs2fPhqWlJfbu3Yu+ffti//796NevX7E1CCHQp08f/Prrr3j77bdRv359fP3114Wez+IsWLAA8+fPR5s2bbBw4UKYmZnhzJkzOH78OLp27Yq1a9di8uTJsLKywvvvvw8AcHZ2LnHMMWPGICwsDN27d8fYsWORl5eHX375BadPn0aLFi0AlG6fK4vnPY+l2Z4JEybA0dERc+fORWZmJjp06AB3d3fs3Lmz0HOxc+dOeHp6wsfH57m1WVlZoV+/ftizZw9Wr14NExMTadmXX34JIQSGDRsGAHj8+DHat2+Pu3fvYvz48ahRowZOnTqF4OBgJCUlYe3atdJj161bh969e2PYsGHIycnB7t27MXDgQHz//feF3ouPHz+OvXv3YtKkSXBwcCj1Ca3h4eF4/Pgx3nnnHdjb2+O3337Dxx9/jDt37iA8PBwAMGjQIMyaNQt79+7Fu+++q/X4vXv3omvXrrC1tS31eKV148YNHDhwAAMHDkStWrWQkpKCTz/9FO3bt8eVK1fg5uam1X/JkiVQKBR47733kJqairVr18LPzw8XLlyAhYVFsesZP348wsLCMGrUKEyZMgU3b97EJ598gvPnz+PkyZOlP9Ir/iNCQ0MFgBJvDRs21HqMh4eHCAwMlO43bdpU9OjRo8T1TJw4URQ1rQcOHBAAxOLFi7XaBwwYIBQKhbh27ZoQQojo6GgBQEybNk2r38iRIwUAMW/ePKlt3rx5AoAYOnRoofU9fvy4UNuXX34pAIiff/650Bjjxo2T2vLy8kT16tWFQqEQy5Ytk9ofPHggLCwstOakKBcuXBAAxNixY7XaZ86cKQCI48ePS22BgYHC0tKyxPEK+Pj4iFatWmm1ffXVVwKAiIyMFEII8fDhQ2FjYyPeeustrX7JyclCrVZrtes6R0XNc1GCg4OFqampuH//vtSWnZ0tbGxsxOjRo0tcf1RUlAAgtm3bJrVFRkZqbaMQhV+bBdq3by/at28v3d++fbtQKpXil19+0eq3adMmAUCcPHlSCCHEmjVrBADx999/l2obn/ZsLQX7mp+fn9BoNFL79OnThYmJiUhLSytxvOL2oZs3bwoAwt7eXmtuv/nmGwFAfPfdd1Jb586dRePGjUVWVpbUptFoRJs2bUTdunVLXH/BvrpixQqpLS8vT7Rr104AEKGhoVJ7wWujQFxcnFAqlaJfv34iPz9fa9yn56Jhw4Zaz1NJjh8/LgCIKVOmFFpWMKYu+9yz7yMFXuR5LG57Csbw9fUVeXl5WsuCg4OFSqXSGic1NVVUqVKlyPqKc/jwYQFA/Pjjj1rtTZo00app0aJFwtLSUly9elWr3+zZs4WJiYlISEiQ2p7dN3NyckSjRo1Ep06dtNoBCKVSKWJiYkpdb3HrEEKIkJAQoVAoxK1bt6Q2Hx8f4e3trdXvt99+K/Q+Udrxnn3NClH4uc/Kyir0+r1586ZQqVRi4cKFUlvBe9NLL70kMjIypPa9e/cKAGLdunVSW2BgoPDw8JDu//LLLwKA2Llzp9Z6Dh06VGR7Sf5zHy2tX78eR48eLXRr0qTJcx9rY2ODmJgYxMXF6bzeH374ASYmJpgyZYpW+4wZMyCEwI8//ggA0mHJCRMmaPWbPHlysWO//fbbhdqeTsFZWVn4559/0Lp1awDA77//Xqj/2LFjpf+bmJigRYsWEEJgzJgxUruNjQ3q1auHGzduFFsL8GRbASAoKEirfcaMGQCAgwcPlvj44owYMQJnzpzB9evXpbadO3fC3d0d7du3B/DkKEtaWhqGDh2Kf/75R7qZmJigVatWiIyMlB6r6xwVNc9FGTx4MHJzc/HVV19JbUeOHEFaWhoGDx5c5Ppzc3Nx79491KlTBzY2NkWuvyzCw8NRv359eHl5ac1Hp06dAECaDxsbGwDAN998U+gwe1mNGzdO66hWu3btkJ+fX+Rhcl0MHjxY+i20YFwA0uvy/v37OH78OAYNGoSHDx9K23zv3j34+/sjLi4Od+/eLXb8H374AVWqVME777wjtZmYmJS4DxY4cOAANBoN5s6dC6VS++21rJdp79+/HwqFAvPmzSu0rGBMfe1zQPk8j2+99ZbW0RLgyf6cnZ2t9ZHNnj17kJeXhzfffLPUY/v5+cHNzQ07d+6U2v744w9cunRJa5zw8HC0a9cOtra2WvuCn58f8vPz8fPPP0t9n943Hzx4gPT0dLRr167I/bJ9+/Zo0KBBqestah2ZmZn4559/0KZNGwghcP78eWnZ4MGDER0drfW+t2fPHqhUKvTp00fn8UpDpVJJr9/8/Hzcu3dP+ri5qDkYMWIEqlWrJt0fMGAAXF1dpddlUcLDw6FWq9GlSxet58Pb2xtWVlZa79XP858LMq+++ir8/PwK3Z5+YyzOwoULkZaWhpdffhmNGzfGu+++i0uXLpVqvbdu3YKbm5vWkw0A9evXl5YX/KtUKlGrVi2tfnXq1Cl27Gf7Ak/ezKdOnQpnZ2dYWFjA0dFR6peenl6of40aNbTuq9VqmJubw8HBoVD78z6/LNiGZ2t2cXGBjY1NmX+QDR48GCqVSnrDSk9Px/fff49hw4ZJb7QFIbNTp05wdHTUuh05cgSpqanSeLrOUVHzXJSmTZvCy8tL6yOUPXv2wMHBQQoQwJNzoebOnSt9Xu/g4ABHR0ekpaUVuf6yiIuLQ0xMTKG5ePnllwFAmo/Bgwejbdu2GDt2LJydnTFkyBDs3bv3hULNs6+pgn1M18+/dR332rVrEEJgzpw5hba7IAw8/Tp41q1bt+Dq6gorKyut9nr16j23tuvXr0OpVJbpB1tJY7q5ucHOzq7YPvra54DyeR6L2ne8vLzQsmVLrQCyc+dOtG7dusT3u2cplUoMGzYMBw4cwOPHj6VxzM3NMXDgQKlfXFwcDh06VOg14efnB0D7NfH999+jdevWMDc3h52dHRwdHbFx48YXel94VkJCAkaOHAk7OztYWVnB0dFR+oXs6fUMHDgQSqVSej8RQiA8PBzdu3eHtbW1zuOVhkajwZo1a1C3bl2t96ZLly4VOVbdunW17isUCtSpU6fE8/3i4uKQnp4OJyenQs/Jo0ePStxHn/WfO0fmRbz22mu4fv06vvnmGxw5cgSff/451qxZg02bNmkd0ahoRX0GOWjQIJw6dQrvvvsumjVrBisrK2g0GnTr1q3IH07P/rZUXBuAYs8PeVZ5f1GYra0tevbsiZ07d2Lu3LnYt28fsrOztX7rKti27du3w8XFpdAYVar830te1zkq6bPeZw0ePBhLlizBP//8g2rVquHbb7/F0KFDtdY/efJkhIaGYtq0afDx8YFarYZCocCQIUOeGyCKm9v8/Hyt502j0aBx48ZYvXp1kf3d3d2lbfv5558RGRmJgwcP4tChQ9izZw86deqEI0eOFPtaKMmLvn7KOm7B3M2cOVM6H+lZuvyglJMX2efy8/OLbC+P57G4fWfEiBGYOnUq7ty5g+zsbJw+fRqffPJJqcd9epyVK1fiwIEDGDp0KHbt2oWePXtCrVZLfTQaDbp06YJZs2YVOUZBuP/ll1/Qu3dvvPbaa9iwYQNcXV1hamqK0NBQ7Nq1q9TbVpL8/Hx06dIF9+/fx3vvvQcvLy9YWlri7t27GDlypNb+7+bmhnbt2mHv3r343//+h9OnTyMhIQHLly8v03ilsXTpUsyZMwejR4/GokWLYGdnB6VSiWnTppXbEVuNRgMnJyetIPs0R0fHUo/FIKMjOzs7jBo1CqNGjcKjR4/w2muvYf78+VKQKe6NxMPDA8eOHcPDhw+1jsr89ddf0vKCfzUaDW7evKmVcq9du1bqGh88eICIiAgsWLAAc+fOldrL8pFYWRRsQ1xcnHTECQBSUlKQlpYmbWtZjBgxAn369MHZs2exc+dONG/eHA0bNpSWe3p6AgCcnJyk37SKou85Gjx4MBYsWID9+/fD2dkZGRkZGDJkiFafffv2ITAwUOtKsKysrCKvjnmWra1tkf1u3bqF2rVrS/c9PT1x8eJFdO7c+bk/5JRKJTp37ozOnTtj9erVWLp0Kd5//31ERkaWOJfl7UUDcMH2m5qalqluDw8PRERE4NGjR1pHZWJjY5/7WE9PT2g0Gly5cqXEb6nWZRs9PT1x+PBh3L9/v9ijMrrsc0W9dnJycpCUlFTqmp5V1udsyJAhCAoKwpdffol///0XpqamWh+/llajRo3QvHlz7Ny5E9WrV0dCQgI+/vhjrT6enp549OjRc18T+/fvh7m5OQ4fPgyVSiW1h4aG6lxXcS5fvoyrV69i69atGDFihNRecAXsswYPHowJEyYgNjYWe/bsQdWqVdGrV68yj/c8+/btQ8eOHbFlyxat9rS0tEJH6YHC75tCCFy7dq3EUzY8PT1x7NgxtG3btkxh8Gn/uY+WXsSzl+VaWVmhTp06Wpc3WlpaAkChN4rXX38d+fn5hX7bWLNmDRQKBbp37w4A0m+QGzZs0Or37E5ZkoLfoJ79jenps/L1qeBL7Z5dX8FRgZKuwHqe7t27w8HBAcuXL8eJEycKfZbu7+8Pa2trLF26tMgr0QouG9X3HNWvXx+NGzfGnj17sGfPHri6uha60svExKTQ+j/++ONifzN+mqenJ06fPo2cnByp7fvvvy90yeOgQYNw9+7dIr+w7t9//5W+0+P+/fuFlhf8IH728l19K24fKi0nJyd06NABn376aZE/nJ93Cfjrr7+OvLw8bNy4UWrLz88v1T7Yt29fKJVKLFy4sNBvrk8/15aWlqXevoCAAAghivySwIIxddnnPD09tc4HAZ5czl6a111xdNmepzk4OKB79+7YsWMHdu7ciW7duhX5g7I0hg8fjiNHjmDt2rWwt7eX3lMLDBo0CFFRUTh8+HChx6alpSEvLw/Ak/1SoVBozUd8fLx0hV95KOr9RwiBdevWFdk/ICAAJiYm+PLLLxEeHo6ePXtK+0lZxitNfc++N4WHhxd7btm2bdvw8OFD6f6+ffuQlJRU6Dl42qBBg5Cfn49FixYVWpaXl6fT64lHZHTQoEEDdOjQAd7e3rCzs8O5c+ewb98+rcuBvb29AQBTpkyBv78/TExMMGTIEPTq1QsdO3bE+++/j/j4eDRt2hRHjhzBN998g2nTpklHEry9vREQEIC1a9fi3r170uXXV69eBVC633ysra3x2muvYcWKFcjNzcVLL72EI0eO4ObNm3qYlcKaNm2KwMBAbN68GWlpaWjfvj1+++03bN26FX379kXHjh3LPLapqSmGDBmCTz75BCYmJhg6dKjWcmtra2zcuBHDhw/HK6+8giFDhsDR0REJCQk4ePAg2rZti08++aRC5mjw4MGYO3cuzM3NMWbMmEInf/bs2RPbt2+HWq1GgwYNEBUVhWPHjsHe3v65Y48dOxb79u1Dt27dMGjQIFy/fh07duyQXkcFhg8fjr179+Ltt99GZGQk2rZti/z8fPz111/Yu3cvDh8+jBYtWmDhwoX4+eef0aNHD3h4eCA1NRUbNmxA9erVi/3OHH0pbh/Sxfr16+Hr64vGjRvjrbfeQu3atZGSkoKoqCjcuXOn2O/DAIBevXqhbdu2mD17NuLj49GgQQN89dVXpTrPoE6dOnj//fexaNEitGvXDv3794dKpcLZs2fh5uYmfaeJt7c3Nm7ciMWLF6NOnTpwcnLSOn/qaR07dsTw4cPx0UcfIS4uTvro85dffkHHjh0xadIknfa5sWPH4u2330ZAQAC6dOmCixcv4vDhw2UOELpuz7NGjBiBAQMGAECRP9RK64033sCsWbPw9ddf45133il06e67776Lb7/9Fj179sTIkSPh7e2NzMxMXL58Gfv27UN8fDwcHBzQo0cPrF69Gt26dcMbb7yB1NRUrF+/HnXq1Cn1OZHP4+XlBU9PT8ycORN3796FtbU19u/fX+x5R05OTujYsSNWr16Nhw8fFjpqpet4z9OzZ08sXLgQo0aNQps2bXD58mXs3LlT62jv0+zs7ODr64tRo0YhJSUFa9euRZ06dfDWW28Vu4727dtj/PjxCAkJwYULF9C1a1eYmpoiLi4O4eHhWLdunfS6eK5SX98kcwWXAZ49e7bI5e3bt3/u5deLFy8Wr776qrCxsREWFhbCy8tLLFmyROTk5Eh98vLyxOTJk4Wjo6NQKBRal7k9fPhQTJ8+Xbi5uQlTU1NRt25dsXLlSq1LG4UQIjMzU0ycOFHY2dkJKysr0bdvXxEbGysAaF0OXXAZXVGXzN65c0f069dP2NjYCLVaLQYOHCgSExOLvYT72TGKuyy6qHkqSm5urliwYIGoVauWMDU1Fe7u7iI4OFjrctiS1lOSgksPu3btWmyfyMhI4e/vL9RqtTA3Nxeenp5i5MiR4ty5c1KfF52j54mLi5Mu7f/1118LLX/w4IEYNWqUcHBwEFZWVsLf31/89ddfhV53RV1+LYQQq1atEi+99JJQqVSibdu24ty5c4UuvxbiyaWjy5cvFw0bNhQqlUrY2toKb29vsWDBApGeni6EECIiIkL06dNHuLm5CTMzM+Hm5iaGDh1a6FLVohR32e6z+1px2/Gs4vahgsuvV65cWegxzz5nQghx/fp1MWLECOHi4iJMTU3FSy+9JHr27Cn27dv33G26d++eGD58uLC2thZqtVoMHz5cnD9//rmXXxf44osvRPPmzaX5bt++vTh69Ki0PDk5WfTo0UNUq1ZNAHjupdh5eXli5cqVwsvLS5iZmQlHR0fRvXt3ER0dLfUp7T6Xn58v3nvvPeHg4CCqVq0q/P39xbVr117oeSxue573vivEk68msLW1FWq1Wvz7778lzsPzvP766wKAOHXqVJHLHz58KIKDg0WdOnWEmZmZcHBwEG3atBEffvih1vv4li1bRN26dYVKpRJeXl4iNDS0yOcagJg4cWKZar1y5Yrw8/MTVlZWwsHBQbz11lvi4sWLhV5jBT777DMBQFSrVq3IeSrteKW9/HrGjBnC1dVVWFhYiLZt24qoqKhC7y8Fr4Uvv/xSBAcHCycnJ2FhYSF69Oihdcm3EIUvvy6wefNm4e3tLSwsLES1atVE48aNxaxZs0RiYmKp5lEIIRRCvOCZd1QhLly4gObNm2PHjh3SFzz9V128eBHNmjXDtm3bMHz4cEOXQ0QvIC8vD25ubujVq1ehczJ01a9fP1y+fFmncwpJ/niOjBEq6g96rV27Fkql8rnfqPtf8Nlnn8HKygr9+/c3dClE9IIOHDiAv//+W+sk1bJISkrCwYMH+cvNfxDPkTFCK1asQHR0NDp27IgqVargxx9/xI8//ohx48ZJl8v+F3333Xe4cuUKNm/ejEmTJmmd7EZE8nLmzBlcunQJixYtQvPmzaXvPCmQk5NT5EnoT1Or1UhOTsbJkyfx+eefw9TUFOPHj9dn2c91//59rZPwn2ViYqLTpcVUCqX+EIoqzJEjR0Tbtm2Fra2tMDU1FZ6enmL+/PkiNzfX0KUZlIeHhzA3Nxd9+vTR+jpsIpKfwMBAYWJiIry9vcXly5cLLS84/6KkW2hoqHQeTo0aNUR4eLgBtkRb+/btS6y5qPNE6MXwHBkiIjI6Dx48QHR0dIl9GjZsCFdX1wqqqHSio6NLvFrIwsICbdu2rcCKKj8GGSIiIpItnuxLREREslXpT/bVaDRITExEtWrVyv1v/xAREZF+CCHw8OFDuLm5FfpC0adV+iCTmJj4n77Sh4iISM5u376N6tWrF7u80geZgj/QePv2ba0/eU5ERETGKyMjA+7u7lp/aLkolT7IFHycZG1tzSBDREQkM887LYQn+xIREZFsMcgQERGRbDHIEBERkWwxyBAREZFsMcgQERGRbDHIEBERkWwZNMjk5+djzpw5qFWrFiwsLODp6YlFixbh6T//JITA3Llz4erqCgsLC/j5+SEuLs6AVRMREZGxMGiQWb58OTZu3IhPPvkEf/75J5YvX44VK1bg448/lvqsWLECH330ETZt2oQzZ87A0tIS/v7+yMrKMmDlREREZAwM+teve/bsCWdnZ2zZskVqCwgIgIWFBXbs2AEhBNzc3DBjxgzMnDkTAJCeng5nZ2eEhYVhyJAhz11HRkYG1Go10tPT+YV4REREMlHan98GPSLTpk0bRERE4OrVqwCAixcv4tdff0X37t0BADdv3kRycjL8/Pykx6jVarRq1QpRUVFFjpmdnY2MjAytGxEREVVOBv0TBbNnz0ZGRga8vLxgYmKC/Px8LFmyBMOGDQMAJCcnAwCcnZ21Hufs7Cwte1ZISAgWLFig38KJiIjIKBj0iMzevXuxc+dO7Nq1C7///ju2bt2KDz/8EFu3bi3zmMHBwUhPT5dut2/fLseKiYiIyJgY9IjMu+++i9mzZ0vnujRu3Bi3bt1CSEgIAgMD4eLiAgBISUmBq6ur9LiUlBQ0a9asyDFVKhVUKpXeayciIiLDM+gRmcePH0Op1C7BxMQEGo0GAFCrVi24uLggIiJCWp6RkYEzZ87Ax8enQmslIiIi42PQIzK9evXCkiVLUKNGDTRs2BDnz5/H6tWrMXr0aABP/nT3tGnTsHjxYtStWxe1atXCnDlz4Obmhr59+xqydCIiIjICBg0yH3/8MebMmYMJEyYgNTUVbm5uGD9+PObOnSv1mTVrFjIzMzFu3DikpaXB19cXhw4dgrm5uQErJyIiKn81Zx80dAk6i1/Ww6DrN+j3yFQEfo8MERHJBYPM/5HF98gQERERvQgGGSIiIpItBhkiIiKSLQYZIiIiki0GGSIiIpItBhkiIiKSLQYZIiIiki0GGSIiIpItBhkiIiKSLQYZIiIiki0GGSIiIpItBhkiIiKSLQYZIiIiki0GGSIiIpItBhkiIiKSLQYZIiIiki0GGSIiIpItBhkiIiKSLQYZIiIiki0GGSIiIpItBhkiIiKSLQYZIiIiki0GGSIiIpItBhkiIiKSLQYZIiIiki0GGSIiIpItBhkiIiKSLQYZIiIiki0GGSIiIpItBhkiIiKSLQYZIiIiki0GGSIiIpItBhkiIiKSLQYZIiIiki2DBpmaNWtCoVAUuk2cOBEAkJWVhYkTJ8Le3h5WVlYICAhASkqKIUsmIiIiI2LQIHP27FkkJSVJt6NHjwIABg4cCACYPn06vvvuO4SHh+PEiRNITExE//79DVkyERERGZEqhly5o6Oj1v1ly5bB09MT7du3R3p6OrZs2YJdu3ahU6dOAIDQ0FDUr18fp0+fRuvWrQ1RMhERERkRozlHJicnBzt27MDo0aOhUCgQHR2N3Nxc+Pn5SX28vLxQo0YNREVFFTtOdnY2MjIytG5ERERUORlNkDlw4ADS0tIwcuRIAEBycjLMzMxgY2Oj1c/Z2RnJycnFjhMSEgK1Wi3d3N3d9Vg1ERERGZLRBJktW7age/fucHNze6FxgoODkZ6eLt1u375dThUSERGRsTHoOTIFbt26hWPHjuGrr76S2lxcXJCTk4O0tDStozIpKSlwcXEpdiyVSgWVSqXPcomIiMhIGMURmdDQUDg5OaFHjx5Sm7e3N0xNTRERESG1xcbGIiEhAT4+PoYok4iIiIyMwY/IaDQahIaGIjAwEFWq/F85arUaY8aMQVBQEOzs7GBtbY3JkyfDx8eHVywRERERACMIMseOHUNCQgJGjx5daNmaNWugVCoREBCA7Oxs+Pv7Y8OGDQaokoiIiIyRQgghDF2EPmVkZECtViM9PR3W1taGLoeIiKhYNWcfNHQJOotf1uP5ncqgtD+/jeIcGSIiIqKyYJAhIiIi2WKQISIiItlikCEiIiLZYpAhIiIi2WKQISIiItlikCEiIiLZYpAhIiIi2WKQISIiItlikCEiIiLZYpAhIiIi2WKQISIiItlikCEiIiLZYpAhIiIi2WKQISIiItlikCEiIiLZYpAhIiIi2WKQISIiItlikCEiIiLZYpAhIiIi2WKQISIiItlikCEiIiLZYpAhIiIi2WKQISIiItlikCEiIiLZYpAhIiIi2WKQISIiItlikCEiIiLZYpAhIiIi2WKQISIiItlikCEiIiLZYpAhIiIi2WKQISIiItkyeJC5e/cu3nzzTdjb28PCwgKNGzfGuXPnpOVCCMydOxeurq6wsLCAn58f4uLiDFgxERERGQuDBpkHDx6gbdu2MDU1xY8//ogrV65g1apVsLW1lfqsWLECH330ETZt2oQzZ87A0tIS/v7+yMrKMmDlREREZAyqGHLly5cvh7u7O0JDQ6W2WrVqSf8XQmDt2rX44IMP0KdPHwDAtm3b4OzsjAMHDmDIkCEVXjMREREZD4Mekfn222/RokULDBw4EE5OTmjevDk+++wzafnNmzeRnJwMPz8/qU2tVqNVq1aIiooqcszs7GxkZGRo3YiIiKhyMmiQuXHjBjZu3Ii6devi8OHDeOeddzBlyhRs3boVAJCcnAwAcHZ21nqcs7OztOxZISEhUKvV0s3d3V2/G0FEREQGY9Ago9Fo8Morr2Dp0qVo3rw5xo0bh7feegubNm0q85jBwcFIT0+Xbrdv3y7HiomIiMiYGDTIuLq6okGDBlpt9evXR0JCAgDAxcUFAJCSkqLVJyUlRVr2LJVKBWtra60bERERVU4GDTJt27ZFbGysVtvVq1fh4eEB4MmJvy4uLoiIiJCWZ2Rk4MyZM/Dx8anQWomIiMj4GPSqpenTp6NNmzZYunQpBg0ahN9++w2bN2/G5s2bAQAKhQLTpk3D4sWLUbduXdSqVQtz5syBm5sb+vbta8jSiYiIyAgYNMi0bNkSX3/9NYKDg7Fw4ULUqlULa9euxbBhw6Q+s2bNQmZmJsaNG4e0tDT4+vri0KFDMDc3N2DlREREZAwUQghh6CL0KSMjA2q1Gunp6TxfhoiIjFrN2QcNXYLO4pf10Mu4pf35bfA/UUBERERUVgwyREREJFsMMkRERCRbDDJEREQkWwwyREREJFsMMkRERCRbDDJEREQkWwwyREREJFsMMkRERCRbDDJEREQkWwwyREREJFsMMkRERCRbDDJEREQkWwwyREREJFsMMkRERCRbDDJEREQkWwwyREREJFsMMkRERCRbDDJEREQkWwwyREREJFsMMkRERCRbDDJEREQkWwwyREREJFsMMkRERCRbDDJEREQkWwwyREREJFsMMkRERCRbDDJEREQkWwwyREREJFsMMkRERCRbDDJEREQkWwwyREREJFsMMkRERCRbDDJEREQkWwYNMvPnz4dCodC6eXl5ScuzsrIwceJE2Nvbw8rKCgEBAUhJSTFgxURERGRMDH5EpmHDhkhKSpJuv/76q7Rs+vTp+O677xAeHo4TJ04gMTER/fv3N2C1REREZEyqGLyAKlXg4uJSqD09PR1btmzBrl270KlTJwBAaGgo6tevj9OnT6N169YVXSoREREZGYMfkYmLi4Obmxtq166NYcOGISEhAQAQHR2N3Nxc+Pn5SX29vLxQo0YNREVFFTtednY2MjIytG5ERERUORk0yLRq1QphYWE4dOgQNm7ciJs3b6Jdu3Z4+PAhkpOTYWZmBhsbG63HODs7Izk5udgxQ0JCoFarpZu7u7uet4KIiIgMxaAfLXXv3l36f5MmTdCqVSt4eHhg7969sLCwKNOYwcHBCAoKku5nZGQwzBAREVVSBv9o6Wk2NjZ4+eWXce3aNbi4uCAnJwdpaWlafVJSUoo8p6aASqWCtbW11o2IiIgqJ6MKMo8ePcL169fh6uoKb29vmJqaIiIiQloeGxuLhIQE+Pj4GLBKIiIiMhYG/Whp5syZ6NWrFzw8PJCYmIh58+bBxMQEQ4cOhVqtxpgxYxAUFAQ7OztYW1tj8uTJ8PHx4RVLREREBKAMQebGjRuoXbt2uaz8zp07GDp0KO7duwdHR0f4+vri9OnTcHR0BACsWbMGSqUSAQEByM7Ohr+/PzZs2FAu6yYiIiL5UwghhC4PUCqVaN++PcaMGYMBAwbA3NxcX7WVi4yMDKjVaqSnp/N8GSIiMmo1Zx80dAk6i1/WQy/jlvbnt87nyPz+++9o0qQJgoKC4OLigvHjx+O33357oWKJiIiIykLnINOsWTOsW7cOiYmJ+OKLL5CUlARfX180atQIq1evxt9//62POomIiIgKKfNVS1WqVEH//v0RHh6O5cuX49q1a5g5cybc3d0xYsQIJCUllWedRERERIWUOcicO3cOEyZMgKurK1avXo2ZM2fi+vXrOHr0KBITE9GnT5/yrJOIiIioEJ2vWlq9ejVCQ0MRGxuL119/Hdu2bcPrr78OpfJJJqpVqxbCwsJQs2bN8q6ViIiISIvOQWbjxo0YPXo0Ro4cCVdX1yL7ODk5YcuWLS9cHBEREVFJdA4ycXFxz+1jZmaGwMDAMhVEREREVFo6nyMTGhqK8PDwQu3h4eHYunVruRRFREREVBo6B5mQkBA4ODgUandycsLSpUvLpSgiIiKi0tA5yCQkJKBWrVqF2j08PJCQkFAuRRERERGVhs5BxsnJCZcuXSrUfvHiRdjb25dLUURERESloXOQGTp0KKZMmYLIyEjk5+cjPz8fx48fx9SpUzFkyBB91EhERERUJJ2vWlq0aBHi4+PRuXNnVKny5OEajQYjRozgOTJERERUoXQOMmZmZtizZw8WLVqEixcvwsLCAo0bN4aHh4c+6iMiIiIqls5BpsDLL7+Ml19+uTxrISIiItKJzkEmPz8fYWFhiIiIQGpqKjQajdby48ePl1txRERERCXROchMnToVYWFh6NGjBxo1agSFQqGPuoiIiIieS+cgs3v3buzduxevv/66PuohIiIiKjWdL782MzNDnTp19FELERERkU50DjIzZszAunXrIITQRz1EREREpabzR0u//vorIiMj8eOPP6Jhw4YwNTXVWv7VV1+VW3FEREREJdE5yNjY2KBfv376qIWIiIhIJzoHmdDQUH3UQURERKQznc+RAYC8vDwcO3YMn376KR4+fAgASExMxKNHj8q1OCIiIqKS6HxE5tatW+jWrRsSEhKQnZ2NLl26oFq1ali+fDmys7OxadMmfdRJRESkk5qzDxq6BKoAOh+RmTp1Klq0aIEHDx7AwsJCau/Xrx8iIiLKtTgiIiKikuh8ROaXX37BqVOnYGZmptVes2ZN3L17t9wKIyIiInoenY/IaDQa5OfnF2q/c+cOqlWrVi5FEREREZWGzkGma9euWLt2rXRfoVDg0aNHmDdvHv9sAREREVUonT9aWrVqFfz9/dGgQQNkZWXhjTfeQFxcHBwcHPDll1/qo0YiIiKiIukcZKpXr46LFy9i9+7duHTpEh49eoQxY8Zg2LBhWif/EhEREembzkEGAKpUqYI333yzvGshIiIi0onOQWbbtm0lLh8xYkSZiyEiIiLShc5BZurUqVr3c3Nz8fjxY5iZmaFq1aplDjLLli1DcHAwpk6dKp1MnJWVhRkzZmD37t3Izs6Gv78/NmzYAGdn5zKtg4iIiCoXna9aevDggdbt0aNHiI2Nha+vb5lP9j179iw+/fRTNGnSRKt9+vTp+O677xAeHo4TJ04gMTER/fv3L9M6iIiIqPIp099aelbdunWxbNmyQkdrSuPRo0cYNmwYPvvsM9ja2krt6enp2LJlC1avXo1OnTrB29sboaGhOHXqFE6fPl0eZRMREZHMlUuQAZ6cAJyYmKjz4yZOnIgePXrAz89Pqz06Ohq5ubla7V5eXqhRowaioqKKHS87OxsZGRlaNyIiIqqcdD5H5ttvv9W6L4RAUlISPvnkE7Rt21ansXbv3o3ff/8dZ8+eLbQsOTkZZmZmsLGx0Wp3dnZGcnJysWOGhIRgwYIFOtVBRERE8qRzkOnbt6/WfYVCAUdHR3Tq1AmrVq0q9Ti3b9/G1KlTcfToUZibm+taRrGCg4MRFBQk3c/IyIC7u3u5jU9ERETGQ+cgo9FoymXF0dHRSE1NxSuvvCK15efn4+eff8Ynn3yCw4cPIycnB2lpaVpHZVJSUuDi4lLsuCqVCiqVqlxqJCIiIuNWpi/EKw+dO3fG5cuXtdpGjRoFLy8vvPfee3B3d4epqSkiIiIQEBAAAIiNjUVCQgJ8fHwMUTIREREZGZ2DzNMf2zzP6tWri11WrVo1NGrUSKvN0tIS9vb2UvuYMWMQFBQEOzs7WFtbY/LkyfDx8UHr1q11LZuIiIgqIZ2DzPnz53H+/Hnk5uaiXr16AICrV6/CxMRE62MihULxwsWtWbMGSqUSAQEBWl+IR0RERASUIcj06tUL1apVw9atW6XvfXnw4AFGjRqFdu3aYcaMGWUu5qefftK6b25ujvXr12P9+vVlHpOIiIgqL52/R2bVqlUICQnR+vI6W1tbLF68WKerloiIiIhelM5BJiMjA3///Xeh9r///hsPHz4sl6KIiIiISkPnINOvXz+MGjUKX331Fe7cuYM7d+5g//79GDNmDP8OEhEREVUonc+R2bRpE2bOnIk33ngDubm5TwapUgVjxozBypUry71AIiIiouLoHGSqVq2KDRs2YOXKlbh+/ToAwNPTE5aWluVeHBEREVFJyvxHI5OSkpCUlIS6devC0tISQojyrIuIiIjouXQOMvfu3UPnzp3x8ssv4/XXX0dSUhKAJ19e9yKXXhMRERHpSucgM336dJiamiIhIQFVq1aV2gcPHoxDhw6Va3FEREREJdH5HJkjR47g8OHDqF69ulZ73bp1cevWrXIrjIiIiOh5dD4ik5mZqXUkpsD9+/f5V6eJiIioQukcZNq1a4dt27ZJ9xUKBTQaDVasWIGOHTuWa3FEREREJdH5o6UVK1agc+fOOHfuHHJycjBr1izExMTg/v37OHnypD5qJCIiIiqSzkdkGjVqhKtXr8LX1xd9+vRBZmYm+vfvj/Pnz8PT01MfNRIREREVSacjMrm5uejWrRs2bdqE999/X181EREREZWKTkdkTE1NcenSJX3VQkRERKQTnT9aevPNN7FlyxZ91EJERESkE51P9s3Ly8MXX3yBY8eOwdvbu9DfWFq9enW5FUdERERUklIFmUuXLqFRo0ZQKpX4448/8MorrwAArl69qtVPoVCUf4VERERExShVkGnevDmSkpLg5OSEW7du4ezZs7C3t9d3bUREREQlKtU5MjY2Nrh58yYAID4+HhqNRq9FEREREZVGqY7IBAQEoH379nB1dYVCoUCLFi1gYmJSZN8bN26Ua4FERERExSlVkNm8eTP69++Pa9euYcqUKXjrrbdQrVo1fddGREREVKJSX7XUrVs3AEB0dDSmTp3KIENEREQGp/Pl16Ghofqog4iIiEhnOn8hHhEREZGxYJAhIiIi2WKQISIiItlikCEiIiLZYpAhIiIi2WKQISIiItlikCEiIiLZYpAhIiIi2WKQISIiItlikCEiIiLZMmiQ2bhxI5o0aQJra2tYW1vDx8cHP/74o7Q8KysLEydOhL29PaysrBAQEICUlBQDVkxERETGxKBBpnr16li2bBmio6Nx7tw5dOrUCX369EFMTAwAYPr06fjuu+8QHh6OEydOIDExEf379zdkyURERGREFEIIYeginmZnZ4eVK1diwIABcHR0xK5duzBgwAAAwF9//YX69esjKioKrVu3LtV4GRkZUKvVSE9Ph7W1tT5LJyIiI1Jz9kFDl/CfEL+sh17GLe3Pb6M5RyY/Px+7d+9GZmYmfHx8EB0djdzcXPj5+Ul9vLy8UKNGDURFRRU7TnZ2NjIyMrRuREREVDkZPMhcvnwZVlZWUKlUePvtt/H111+jQYMGSE5OhpmZGWxsbLT6Ozs7Izk5udjxQkJCoFarpZu7u7uet4CIiIgMxeBBpl69erhw4QLOnDmDd955B4GBgbhy5UqZxwsODkZ6erp0u337djlWS0RERMakiqELMDMzQ506dQAA3t7eOHv2LNatW4fBgwcjJycHaWlpWkdlUlJS4OLiUux4KpUKKpVK32UTERGRETD4EZlnaTQaZGdnw9vbG6ampoiIiJCWxcbGIiEhAT4+PgaskIiIiIyFQY/IBAcHo3v37qhRowYePnyIXbt24aeffsLhw4ehVqsxZswYBAUFwc7ODtbW1pg8eTJ8fHxKfcUSERERVW4GDTKpqakYMWIEkpKSoFar0aRJExw+fBhdunQBAKxZswZKpRIBAQHIzs6Gv78/NmzYYMiSiYiIyIgY3ffIlDd+jwwR0X8Tv0emYvB7ZIiIiIjKiEGGiIiIZItBhoiIiGSLQYaIiIhki0GGiIiIZItBhoiIiGSLQYaIiIhki0GGiIiIZItBhoiIiGSLQYaIiIhki0GGiIiIZItBhoiIiGSLQYaIiIhki0GGiIiIZItBhoiIiGSLQYaIiIhki0GGiIiIZItBhoiIiGSLQYaIiIhki0GGiIiIZItBhoiIiGSLQYaIiIhki0GGiIiIZItBhoiIiGSLQYaIiIhki0GGiIiIZItBhoiIiGSLQYaIiIhki0GGiIiIZKuKoQsgIiLjV3P2QUOXQFQkHpEhIiIi2WKQISIiItlikCEiIiLZMmiQCQkJQcuWLVGtWjU4OTmhb9++iI2N1eqTlZWFiRMnwt7eHlZWVggICEBKSoqBKiYiIiJjYtAgc+LECUycOBGnT5/G0aNHkZubi65duyIzM1PqM336dHz33XcIDw/HiRMnkJiYiP79+xuwaiIiIjIWBr1q6dChQ1r3w8LC4OTkhOjoaLz22mtIT0/Hli1bsGvXLnTq1AkAEBoaivr16+P06dNo3bq1IcomIiIiI2FU58ikp6cDAOzs7AAA0dHRyM3NhZ+fn9THy8sLNWrUQFRUlEFqJCIiIuNhNN8jo9FoMG3aNLRt2xaNGjUCACQnJ8PMzAw2NjZafZ2dnZGcnFzkONnZ2cjOzpbuZ2Rk6K1mIiIiMiyjOSIzceJE/PHHH9i9e/cLjRMSEgK1Wi3d3N3dy6lCIiIiMjZGEWQmTZqE77//HpGRkahevbrU7uLigpycHKSlpWn1T0lJgYuLS5FjBQcHIz09Xbrdvn1bn6UTERGRARk0yAghMGnSJHz99dc4fvw4atWqpbXc29sbpqamiIiIkNpiY2ORkJAAHx+fIsdUqVSwtrbWuhEREVHlZNBzZCZOnIhdu3bhm2++QbVq1aTzXtRqNSwsLKBWqzFmzBgEBQXBzs4O1tbWmDx5Mnx8fHjFEhERERk2yGzcuBEA0KFDB6320NBQjBw5EgCwZs0aKJVKBAQEIDs7G/7+/tiwYUMFV0pERETGyKBBRgjx3D7m5uZYv3491q9fXwEVERERkZwYxcm+RERERGXBIENERESyxSBDREREssUgQ0RERLLFIENERESyxSBDREREssUgQ0RERLLFIENERESyxSBDREREssUgQ0RERLLFIENERESyxSBDREREssUgQ0RERLLFIENERESyxSBDREREssUgQ0RERLLFIENERESyxSBDREREssUgQ0RERLLFIENERESyxSBDREREssUgQ0RERLLFIENERESyxSBDREREssUgQ0RERLJVxdAFEBH919ScfdDQJRBVGjwiQ0RERLLFIENERESyxSBDREREssUgQ0RERLLFIENERESyxSBDREREssUgQ0RERLLFIENERESyZdAg8/PPP6NXr15wc3ODQqHAgQMHtJYLITB37ly4urrCwsICfn5+iIuLM0yxREREZHQMGmQyMzPRtGlTrF+/vsjlK1aswEcffYRNmzbhzJkzsLS0hL+/P7Kysiq4UiIiIjJGBv0TBd27d0f37t2LXCaEwNq1a/HBBx+gT58+AIBt27bB2dkZBw4cwJAhQyqyVCIiIjJCRnuOzM2bN5GcnAw/Pz+pTa1Wo1WrVoiKiir2cdnZ2cjIyNC6ERERUeVktEEmOTkZAODs7KzV7uzsLC0rSkhICNRqtXRzd3fXa51ERERkOEYbZMoqODgY6enp0u327duGLomIiIj0xGiDjIuLCwAgJSVFqz0lJUVaVhSVSgVra2utGxEREVVORhtkatWqBRcXF0REREhtGRkZOHPmDHx8fAxYGRERERkLg1619OjRI1y7dk26f/PmTVy4cAF2dnaoUaMGpk2bhsWLF6Nu3bqoVasW5syZAzc3N/Tt29dwRRMREZHRMGiQOXfuHDp27CjdDwoKAgAEBgYiLCwMs2bNQmZmJsaNG4e0tDT4+vri0KFDMDc3N1TJREREZEQUQghh6CL0KSMjA2q1Gunp6TxfhoiMQs3ZBw1dAlG5iV/WQy/jlvbnt9GeI0NERET0PAwyREREJFsMMkRERCRbDDJEREQkWwwyREREJFsMMkRERCRbDDJEREQkWwwyREREJFsMMkRERCRbDDJEREQkWwwyREREJFsMMkRERCRbDDJEREQkWwwyREREJFsMMkRERCRbDDJEREQkWwwyREREJFsMMkRERCRbDDJEREQkWwwyREREJFtVDF0AUWnUnH3Q0CXoLH5ZD0OXQERU6fGIDBEREckWgwwRERHJFoMMERERyRbPkSEiCc9FIiK54REZIiIiki0GGSIiIpItBhkiIiKSLQYZIiIiki2e7EukJ3I8cZaISG54RIaIiIhki0GGiIiIZItBhoiIiGRLFufIrF+/HitXrkRycjKaNm2Kjz/+GK+++qqhy5LtORD8AjGqTOS6HxJR+TD6IzJ79uxBUFAQ5s2bh99//x1NmzaFv78/UlNTDV0aERERGZjRB5nVq1fjrbfewqhRo9CgQQNs2rQJVatWxRdffGHo0oiIiMjAjDrI5OTkIDo6Gn5+flKbUqmEn58foqKiDFgZERERGQOjPkfmn3/+QX5+PpydnbXanZ2d8ddffxX5mOzsbGRnZ0v309PTAQAZGRnlXp8m+3G5j1kR9DEX+ibXuSYiquz09TOlYFwhRIn9jDrIlEVISAgWLFhQqN3d3d0A1Rgn9VpDV0BERJWFvn+mPHz4EGq1utjlRh1kHBwcYGJigpSUFK32lJQUuLi4FPmY4OBgBAUFSfc1Gg3u378Pe3t7KBQKvdYrBxkZGXB3d8ft27dhbW1t6HKMGueq9DhXpce5Kj3OVelVxrkSQuDhw4dwc3MrsZ9RBxkzMzN4e3sjIiICffv2BfAkmERERGDSpElFPkalUkGlUmm12djY6LlS+bG2tq40L3Z941yVHueq9DhXpce5Kr3KNlclHYkpYNRBBgCCgoIQGBiIFi1a4NVXX8XatWuRmZmJUaNGGbo0IiIiMjCjDzKDBw/G33//jblz5yI5ORnNmjXDoUOHCp0ATERERP89Rh9kAGDSpEnFfpREulGpVJg3b16hj9+oMM5V6XGuSo9zVXqcq9L7L8+VQjzvuiYiIiIiI2XUX4hHREREVBIGGSIiIpItBhkiIiKSLQYZIiIiki0GGZn5+eef0atXL7i5uUGhUODAgQNay1NSUjBy5Ei4ubmhatWq6NatG+Li4rT6XL9+Hf369YOjoyOsra0xaNCgQt+eXLNmTSgUCq3bsmXL9L155SokJAQtW7ZEtWrV4OTkhL59+yI2NlarT1ZWFiZOnAh7e3tYWVkhICCg0FwkJCSgR48eqFq1KpycnPDuu+8iLy9Pq89PP/2EV155BSqVCnXq1EFYWJi+N69cVdRc/fTTT4VeVwqFAsnJyRWyneWhvOZqypQp8Pb2hkqlQrNmzYpc16VLl9CuXTuYm5vD3d0dK1as0Ndm6U1FzVd8fHyRr63Tp0/rc/PKVXnM1cWLFzF06FC4u7vDwsIC9evXx7p16wqtS+7vWU9jkJGZzMxMNG3aFOvXry+0TAiBvn374saNG/jmm29w/vx5eHh4wM/PD5mZmdLju3btCoVCgePHj+PkyZPIyclBr169oNFotMZbuHAhkpKSpNvkyZMrZBvLy4kTJzBx4kScPn0aR48eRW5uLrp27SrNBQBMnz4d3333HcLDw3HixAkkJiaif//+0vL8/Hz06NEDOTk5OHXqFLZu3YqwsDDMnTtX6nPz5k306NEDHTt2xIULFzBt2jSMHTsWhw8frtDtfREVNVcFYmNjtV5bTk5OFbKd5aE85qrA6NGjMXjw4CLXk5GRga5du8LDwwPR0dFYuXIl5s+fj82bN+tt2/ShouarwLFjx7ReW97e3uW+TfpSHnMVHR0NJycn7NixAzExMXj//fcRHByMTz75ROpTGd6ztAiSLQDi66+/lu7HxsYKAOKPP/6Q2vLz84Wjo6P47LPPhBBCHD58WCiVSpGeni71SUtLEwqFQhw9elRq8/DwEGvWrNH7NlSk1NRUAUCcOHFCCPFku01NTUV4eLjU588//xQARFRUlBBCiB9++EEolUqRnJws9dm4caOwtrYW2dnZQgghZs2aJRo2bKi1rsGDBwt/f399b5Le6GuuIiMjBQDx4MGDitsYPSvLXD1t3rx5omnTpoXaN2zYIGxtbaW5E0KI9957T9SrV6/8N6IC6Wu+bt68KQCI8+fP66v0Cveic1VgwoQJomPHjtL9yvaexSMylUh2djYAwNzcXGpTKpVQqVT49ddfpT4KhULrS5PMzc2hVCqlPgWWLVsGe3t7NG/eHCtXriz0cYrcpKenAwDs7OwAPPnNJTc3F35+flIfLy8v1KhRA1FRUQCAqKgoNG7cWOubpP39/ZGRkYGYmBipz9NjFPQpGEOO9DVXBZo1awZXV1d06dIFJ0+e1Pfm6FVZ5qo0oqKi8Nprr8HMzExq8/f3R2xsLB48eFBO1Vc8fc1Xgd69e8PJyQm+vr749ttvy6doAymvuUpPT5fGACrfexaDTCVS8IIODg7GgwcPkJOTg+XLl+POnTtISkoCALRu3RqWlpZ477338PjxY2RmZmLmzJnIz8+X+gBPPo/evXs3IiMjMX78eCxduhSzZs0y1Ka9MI1Gg2nTpqFt27Zo1KgRACA5ORlmZmaF/qios7OzdM5GcnJyoT+HUXD/eX0yMjLw77//6mNz9Eqfc+Xq6opNmzZh//792L9/P9zd3dGhQwf8/vvvet4q/SjrXJVGaeZTbvQ5X1ZWVli1ahXCw8Nx8OBB+Pr6om/fvrINM+U1V6dOncKePXswbtw4qa2yvWfJ4k8UUOmYmpriq6++wpgxY2BnZwcTExP4+fmhe/fuEP//C5wdHR0RHh6Od955Bx999BGUSiWGDh2KV155BUrl/+XaoKAg6f9NmjSBmZkZxo8fj5CQEFl+BfbEiRPxxx9/FDrqRIXpc67q1auHevXqSffbtGmD69evY82aNdi+fXu5r0/f+LrSjT7ny8HBQet9q2XLlkhMTMTKlSvRu3fvcl+fvpXHXP3xxx/o06cP5s2bh65du5ZjdcaFR2QqGW9vb1y4cAFpaWlISkrCoUOHcO/ePdSuXVvq07VrV1y/fh2pqan4559/sH37dty9e1erz7NatWqFvLw8xMfHV8BWlK9Jkybh+++/R2RkJKpXry61u7i4ICcnB2lpaVr9U1JS4OLiIvV59uqJgvvP62NtbQ0LC4vy3hy90vdcFeXVV1/FtWvXymkLKs6LzFVplHU+jZW+56sorVq1+s++tq5cuYLOnTtj3Lhx+OCDD7SWVab3LIBBptJSq9VwdHREXFwczp07hz59+hTq4+DgABsbGxw/fhypqakl/tZy4cIFKJVKWV1dIoTApEmT8PXXX+P48eOoVauW1nJvb2+YmpoiIiJCaouNjUVCQgJ8fHwAAD4+Prh8+TJSU1OlPkePHoW1tTUaNGgg9Xl6jII+BWPIQUXNVVEuXLgAV1fXct4i/SmPuSoNHx8f/Pzzz8jNzZXajh49inr16sHW1vbFN6SCVNR8FeW/+tqKiYlBx44dERgYiCVLlhRaT2V4z9JiyDONSXcPHz4U58+fF+fPnxcAxOrVq8X58+fFrVu3hBBC7N27V0RGRorr16+LAwcOCA8PD9G/f3+tMb744gsRFRUlrl27JrZv3y7s7OxEUFCQtPzUqVNizZo14sKFC+L69etix44dwtHRUYwYMaJCt/VFvfPOO0KtVouffvpJJCUlSbfHjx9Lfd5++21Ro0YNcfz4cXHu3Dnh4+MjfHx8pOV5eXmiUaNGomvXruLChQvi0KFDwtHRUQQHB0t9bty4IapWrSreffdd8eeff4r169cLExMTcejQoQrd3hdRUXO1Zs0aceDAAREXFycuX74spk6dKpRKpTh27FiFbu+LKI+5EkKIuLg4cf78eTF+/Hjx8ssvS/t1wVVKaWlpwtnZWQwfPlz88ccfYvfu3aJq1ari008/rdDtfVEVNV9hYWFi165d4s8//xR//vmnWLJkiVAqleKLL76o0O19EeUxV5cvXxaOjo7izTff1BojNTVV6lMZ3rOexiAjMwWXrz57CwwMFEIIsW7dOlG9enVhamoqatSoIT744AOtyzeFeHIJp7OzszA1NRV169YVq1atEhqNRloeHR0tWrVqJdRqtTA3Nxf169cXS5cuFVlZWRW5qS+sqHkCIEJDQ6U+//77r5gwYYKwtbUVVatWFf369RNJSUla48THx4vu3bsLCwsL4eDgIGbMmCFyc3O1+kRGRopmzZoJMzMzUbt2ba11yEFFzdXy5cuFp6enMDc3F3Z2dqJDhw7i+PHjFbWZ5aK85qp9+/ZFjnPz5k2pz8WLF4Wvr69QqVTipZdeEsuWLaugrSw/FTVfYWFhon79+qJq1arC2tpavPrqq1qXKctBeczVvHnzihzDw8NDa11yf896mkKI/38WKBEREZHM8BwZIiIiki0GGSIiIpItBhkiIiKSLQYZIiIiki0GGSIiIpItBhkiIiKSLQYZIiIiki0GGSIiIpItBhkiIiKSLQYZIvpPys/Ph0ajMXQZRPSCGGSIyOC2bdsGe3t7ZGdna7X37dsXw4cPBwB88803eOWVV2Bubo7atWtjwYIFyMvLk/quXr0ajRs3hqWlJdzd3TFhwgQ8evRIWh4WFgYbGxt8++23aNCgAVQqFRISEipmA4lIbxhkiMjgBg4ciPz8fHz77bdSW2pqKg4ePIjRo0fjl19+wYgRIzB16lRcuXIFn376KcLCwrBkyRKpv1KpxEcffYSYmBhs3boVx48fx6xZs7TW8/jxYyxfvhyff/45YmJi4OTkVGHbSET6wT8aSURGYcKECYiPj8cPP/wA4MkRlvXr1+PatWvo0qULOnfujODgYKn/jh07MGvWLCQmJhY53r59+/D222/jn3/+AfDkiMyoUaNw4cIFNG3aVP8bREQVgkGGiIzC+fPn0bJlS9y6dQsvvfQSmjRpgoEDB2LOnDlwdHTEo0ePYGJiIvXPz89HVlYWMjMzUbVqVRw7dgwhISH466+/kJGRgby8PK3lYWFhGD9+PLKysqBQKAy4pURUnqoYugAiIgBo3rw5mjZtim3btqFr166IiYnBwYMHAQCPHj3CggUL0L9//0KPMzc3R3x8PHr27Il33nkHS5YsgZ2dHX799VeMGTMGOTk5qFq1KgDAwsKCIYaokmGQISKjMXbsWKxduxZ3796Fn58f3N3dAQCvvPIKYmNjUadOnSIfFx0dDY1Gg1WrVkGpfHLq3969eyusbiIyHAYZIjIab7zxBmbOnInPPvsM27Ztk9rnzp2Lnj17okaNGhgwYACUSiUuXryIP/74A4sXL0adOnWQm5uLjz/+GL169cLJkyexadMmA24JEVUUXrVEREZDrVYjICAAVlZW6Nu3r9Tu7++P77//HkeOHEHLli3RunVrrFmzBh4eHgCApk2bYvXq1Vi+fDkaNWqEnTt3IiQkxEBbQUQViSf7EpFR6dy5Mxo2bIiPPvrI0KUQkQwwyBCRUXjw4AF++uknDBgwAFeuXEG9evUMXRIRyQDPkSEio9C8eXM8ePAAy5cvZ4gholLjERkiIiKSLZ7sS0RERLLFIENERESyxSBDREREssUgQ0RERLLFIENERESyxSBDREREssUgQ0RERLLFIENERESyxSBDREREsvX/AEGQUzEv+yrbAAAAAElFTkSuQmCC",
      "text/plain": [
       "<Figure size 640x480 with 1 Axes>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "# plot histogram of the year values in the dataframe country_year_available year column including correct axes labelling\n",
    "import matplotlib.pyplot as plt\n",
    "plt.hist(country_year_available['year'])\n",
    "plt.xlabel('year')\n",
    "plt.ylabel('frequency')\n",
    "plt.title('Histogram of year values in the dict country_year_available')\n",
    "plt.show()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Discard all countries with data that is too old i.e. older than 2010"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "['AGO', 'ALB', 'ARE', 'ARG', 'ARM', 'AUS', 'AUT', 'BDI', 'BEL', 'BEN', 'BFA', 'BGD', 'BGR', 'BIH', 'BLR', 'BOL', 'BRA', 'BTN', 'BWA', 'CAF', 'CAN', 'CHE', 'CHL', 'CHN', 'CIV', 'CMR', 'COD', 'COG', 'COL', 'COM', 'CPV', 'CRI', 'CYP', 'CZE', 'DEU', 'DJI', 'DNK', 'DOM', 'DZA', 'ECU', 'EGY', 'ESP', 'EST', 'ETH', 'FIN', 'FJI', 'FRA', 'FSM', 'GAB', 'GBR', 'GEO', 'GHA', 'GIN', 'GMB', 'GNB', 'GRC', 'GTM', 'HND', 'HRV', 'HTI', 'HUN', 'IDN', 'IND', 'IRL', 'IRN', 'IRQ', 'ISL', 'ISR', 'ITA', 'JPN', 'KAZ', 'KEN', 'KGZ', 'KIR', 'KOR', 'LAO', 'LBN', 'LBR', 'LCA', 'LKA', 'LSO', 'LTU', 'LUX', 'LVA', 'MAR', 'MDA', 'MDG', 'MDV', 'MEX', 'MHL', 'MKD', 'MLI', 'MLT', 'MMR', 'MNE', 'MNG', 'MOZ', 'MRT', 'MUS', 'MWI', 'MYS', 'NAM', 'NER', 'NGA', 'NIC', 'NLD', 'NOR', 'NRU', 'PAK', 'PAN', 'PER', 'PHL', 'POL', 'PRT', 'PRY', 'PSE', 'ROU', 'RUS', 'RWA', 'SDN', 'SEN', 'SLB', 'SLE', 'SLV', 'SRB', 'SSD', 'STP', 'SVK', 'SVN', 'SWE', 'SWZ', 'SYC', 'TCD', 'TGO', 'THA', 'TJK', 'TLS', 'TON', 'TUN', 'TUR', 'TWN', 'TZA', 'UGA', 'UKR', 'URY', 'USA', 'VNM', 'VUT', 'WSM', 'XKX', 'YEM', 'ZAF', 'ZMB', 'ZWE']\n",
      "154\n",
      "       0\n",
      "0    AGO\n",
      "1    ALB\n",
      "2    ARE\n",
      "3    ARG\n",
      "4    ARM\n",
      "..   ...\n",
      "149  XKX\n",
      "150  YEM\n",
      "151  ZAF\n",
      "152  ZMB\n",
      "153  ZWE\n",
      "\n",
      "[154 rows x 1 columns]\n"
     ]
    }
   ],
   "source": [
    "# now make# Assuming country_year_available is already populated with data\n",
    "# Create a list of countries where the year in the 'year' column is greater than 2010 that is discard all countries with data older than 2010\n",
    "countries_with_new_data = [row['country'] for index, row in country_year_available.iterrows() if row['year'] > 2010]\n",
    "\n",
    "print(countries_with_new_data)  # List of countries with data newer than 2010\n",
    "print(len(countries_with_new_data))  # Number of countries with data newer than 2010\n",
    "\n",
    "# store this list as a csv file\n",
    "countries_with_new_data_df = pd.DataFrame(countries_with_new_data)\n",
    "print(countries_with_new_data_df)\n",
    "# the path should be the file path from above but with the file name countries_with_new_data.csv\n",
    "countries_with_new_data_df.to_csv('./data/pip_all_data/countries_with_new_data.csv')\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "                      region_name region_code          country_name  \\\n",
      "2              Sub-Saharan Africa         SSA                Angola   \n",
      "17          Europe & Central Asia         ECA               Albania   \n",
      "19    Other High Income Countries         OHI  United Arab Emirates   \n",
      "52      Latin America & Caribbean         LAC             Argentina   \n",
      "75          Europe & Central Asia         ECA               Armenia   \n",
      "...                           ...         ...                   ...   \n",
      "2367        Europe & Central Asia         ECA                Kosovo   \n",
      "2370   Middle East & North Africa         MNA           Yemen, Rep.   \n",
      "2376           Sub-Saharan Africa         SSA          South Africa   \n",
      "2385           Sub-Saharan Africa         SSA                Zambia   \n",
      "2388           Sub-Saharan Africa         SSA              Zimbabwe   \n",
      "\n",
      "     country_code  reporting_year reporting_level survey_acronym  \\\n",
      "2             AGO            2018        national          IDREA   \n",
      "17            ALB            2020        national            HBS   \n",
      "19            ARE            2018        national           HIES   \n",
      "52            ARG            2021           urban        EPHC-S2   \n",
      "75            ARM            2021        national           ILCS   \n",
      "...           ...             ...             ...            ...   \n",
      "2367          XKX            2017        national            HBS   \n",
      "2370          YEM            2014        national            HBS   \n",
      "2376          ZAF            2014        national            LCS   \n",
      "2385          ZMB            2015        national       LCMS-VII   \n",
      "2388          ZWE            2019        national          PICES   \n",
      "\n",
      "     survey_coverage  survey_year welfare_type  ...       cpi         ppp  \\\n",
      "2           national         2018  consumption  ...  1.231024  107.269989   \n",
      "17          national         2020  consumption  ...  1.051449   50.357372   \n",
      "19          national         2018       income  ...  1.030686    2.834844   \n",
      "52             urban         2021       income  ...  4.765005   10.807940   \n",
      "75          national         2021  consumption  ...  1.128227  167.312210   \n",
      "...              ...          ...          ...  ...       ...         ...   \n",
      "2367        national         2017  consumption  ...  1.000000    0.356561   \n",
      "2370        national         2014  consumption  ...  0.518205  255.683585   \n",
      "2376        national         2014  consumption  ...  0.893796    6.549363   \n",
      "2385        national         2015  consumption  ...  0.796036    4.223818   \n",
      "2388        national         2019  consumption  ...  1.000000    0.485028   \n",
      "\n",
      "      reporting_pop  reporting_gdp  reporting_pce  is_interpolated  \\\n",
      "2          31273533    2677.249833    1548.153535            False   \n",
      "17          2837849    4418.660885    3489.233443            False   \n",
      "19          9140169   43644.147050   16365.320170            False   \n",
      "52         42248949   12402.490790    7702.277523            False   \n",
      "75          2790974    4522.319045    3428.784471            False   \n",
      "...             ...            ...            ...              ...   \n",
      "2367        1791003    3890.211606    3196.991736            False   \n",
      "2370       27753304    2123.935040            NaN            False   \n",
      "2376       54729551    6252.317977    3948.971709            False   \n",
      "2385       16248230    1307.909649     662.412898            False   \n",
      "2388       15354608    1342.989586     990.451210            False   \n",
      "\n",
      "      distribution_type  estimation_type        spl       spr  \n",
      "2                 micro           survey   2.860275  0.422226  \n",
      "17                micro           survey   7.182353  0.159824  \n",
      "19                group           survey  37.210707  0.103839  \n",
      "52                micro           survey  10.370100  0.235500  \n",
      "75                micro           survey   4.520945  0.182955  \n",
      "...                 ...              ...        ...       ...  \n",
      "2367              micro           survey   5.204412  0.156395  \n",
      "2370              micro           survey   2.867859  0.371192  \n",
      "2376              micro           survey   3.526711  0.385672  \n",
      "2385              micro           survey   2.150000  0.607931  \n",
      "2388            imputed           survey   2.477409  0.467403  \n",
      "\n",
      "[160 rows x 42 columns]\n",
      "160\n"
     ]
    }
   ],
   "source": [
    "# now subset all data for the countries in the list countries_with_new_data and their latest available year based on the dictionar country year available\n",
    "# and the correctly chosen welfare type and store as a dataframe\n",
    "# Assuming country_year_available is already populated with data\n",
    "# Create a list of countries with data newer than 2010\n",
    "countries_with_new_data = [row['country'] for index, row in country_year_available.iterrows() if row['year'] > 2010]\n",
    "\n",
    "# Convert country_year_available DataFrame into a dictionary for mapping\n",
    "# The dictionary will have country codes as keys and tuples of (year, welfare_type) as values\n",
    "country_year_welfare_mapping = {row['country']: (row['year'], row['welfare_type']) for index, row in country_year_available.iterrows()}\n",
    "\n",
    "# Subset all data for the countries in countries_with_new_data and their latest available year and welfare type\n",
    "subset_pip_data = pip_data[\n",
    "    (pip_data['country_code'].isin(countries_with_new_data)) &\n",
    "    (pip_data['survey_year'] == pip_data['country_code'].apply(lambda x: country_year_welfare_mapping.get(x, (None, None))[0])) &\n",
    "    (pip_data['welfare_type'] == pip_data['country_code'].apply(lambda x: country_year_welfare_mapping.get(x, (None, None))[1]))\n",
    "]\n",
    "\n",
    "print(subset_pip_data)\n",
    "print(len(subset_pip_data))\n",
    "\n",
    "# based on column reporting level in subset_pip_data throw out all rows that are not \"national\"\" and store as a dataframe\n",
    "# Assuming subset_pip_data is already populated with data\n",
    "national_subset_pip_data = subset_pip_data[subset_pip_data['reporting_level'] == 'national']\n",
    "\n",
    "# store the dataframe subset_pip_data as a csv file\n",
    "national_subset_pip_data.to_csv('./data/pip_all_data/national_subset_pip_data.csv')\n",
    "\n",
    "# argentina does not appear in the final subset of the data because it does not have any national level data"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Subset only the necessary variables"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [],
   "source": [
    "# reload dataframe national_subset_pip_data from csv file and store in new dataframe call data_modified\n",
    "data_modified = pd.read_csv('./data/pip_all_data/national_subset_pip_data.csv')\n",
    "\n",
    "#now kick out all columns except the following columns and store as a dataframe\n",
    "columns_to_keep = [\n",
    "    \"region_name\", \"region_code\", \"country_name\", \"country_code\", \n",
    "    \"survey_year\", \"welfare_type\", \"mean\", \"median\", \"gini\",\n",
    "    \"decile1\", \"decile2\", \"decile3\", \"decile4\", \"decile5\",\n",
    "    \"decile6\", \"decile7\", \"decile8\", \"decile9\", \"decile10\"\n",
    "]\n",
    "\n",
    "data_modified = data_modified[columns_to_keep]\n",
    "\n",
    "\n",
    "# now delte the country Taiwan with code TWN from the dataframe data_modified since it is not recognised by the world bank data\n",
    "data_modified = data_modified[data_modified['country_code'] != 'TWN']\n",
    "# also delete the country code YEM, that is Yemen, since it is not recognised by the world bank data and does not have data outside of the PIP\n",
    "data_modified = data_modified[data_modified['country_code'] != 'YEM']\n",
    "\n",
    "# store the dataframe data_modified as a csv file\n",
    "data_modified.to_csv('./data/pip_all_data/data_modified.csv')\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "                    Country Name Country Code  \\\n",
      "0                          Aruba          ABW   \n",
      "1    Africa Eastern and Southern          AFE   \n",
      "2                    Afghanistan          AFG   \n",
      "3     Africa Western and Central          AFW   \n",
      "4                         Angola          AGO   \n",
      "..                           ...          ...   \n",
      "261                       Kosovo          XKX   \n",
      "262                        Yemen          YEM   \n",
      "263                 South Africa          ZAF   \n",
      "264                       Zambia          ZMB   \n",
      "265                     Zimbabwe          ZWE   \n",
      "\n",
      "                                        Indicator Name     Indicator Code  \\\n",
      "0    GDP per capita, PPP (constant 2017 internation...  NY.GDP.PCAP.PP.KD   \n",
      "1    GDP per capita, PPP (constant 2017 internation...  NY.GDP.PCAP.PP.KD   \n",
      "2    GDP per capita, PPP (constant 2017 internation...  NY.GDP.PCAP.PP.KD   \n",
      "3    GDP per capita, PPP (constant 2017 internation...  NY.GDP.PCAP.PP.KD   \n",
      "4    GDP per capita, PPP (constant 2017 internation...  NY.GDP.PCAP.PP.KD   \n",
      "..                                                 ...                ...   \n",
      "261  GDP per capita, PPP (constant 2017 internation...  NY.GDP.PCAP.PP.KD   \n",
      "262  GDP per capita, PPP (constant 2017 internation...  NY.GDP.PCAP.PP.KD   \n",
      "263  GDP per capita, PPP (constant 2017 internation...  NY.GDP.PCAP.PP.KD   \n",
      "264  GDP per capita, PPP (constant 2017 internation...  NY.GDP.PCAP.PP.KD   \n",
      "265  GDP per capita, PPP (constant 2017 internation...  NY.GDP.PCAP.PP.KD   \n",
      "\n",
      "     1960  1961  1962  1963  1964  1965  ...          2014          2015  \\\n",
      "0     NaN   NaN   NaN   NaN   NaN   NaN  ...  36558.972840  36099.942640   \n",
      "1     NaN   NaN   NaN   NaN   NaN   NaN  ...   3652.772991   3668.400279   \n",
      "2     NaN   NaN   NaN   NaN   NaN   NaN  ...   2144.449634   2108.714173   \n",
      "3     NaN   NaN   NaN   NaN   NaN   NaN  ...   4148.495951   4152.506646   \n",
      "4     NaN   NaN   NaN   NaN   NaN   NaN  ...   8183.164577   7966.885556   \n",
      "..    ...   ...   ...   ...   ...   ...  ...           ...           ...   \n",
      "261   NaN   NaN   NaN   NaN   NaN   NaN  ...   8796.634130   9445.106933   \n",
      "262   NaN   NaN   NaN   NaN   NaN   NaN  ...           NaN           NaN   \n",
      "263   NaN   NaN   NaN   NaN   NaN   NaN  ...  13993.270140  13887.211220   \n",
      "264   NaN   NaN   NaN   NaN   NaN   NaN  ...   3375.941270   3365.379259   \n",
      "265   NaN   NaN   NaN   NaN   NaN   NaN  ...   2316.954536   2313.878553   \n",
      "\n",
      "             2016          2017          2018          2019          2020  \\\n",
      "0    36504.688730  38868.333920  39597.659880  38511.336880  29236.047260   \n",
      "1     3664.810708   3670.972022   3672.750831   3658.128493   3467.484700   \n",
      "2     2101.422187   2096.093111   2060.698973   2079.921861   1968.341002   \n",
      "3     4060.345071   4054.572453   4068.321122   4096.827363   3960.847898   \n",
      "4     7487.925098   7216.061373   6878.590025   6602.269211   6029.691895   \n",
      "..            ...           ...           ...           ...           ...   \n",
      "261  10031.047430  10436.168850  10755.167540  11318.459010  10706.510920   \n",
      "262           NaN           NaN           NaN           NaN           NaN   \n",
      "263  13844.275960  13950.453480  13995.062900  13850.895150  12866.568990   \n",
      "264   3384.268144   3395.479686   3425.948936   3372.358980   3183.650773   \n",
      "265   2286.623549   2331.780833   2399.621551   2203.396809   1990.319419   \n",
      "\n",
      "             2021          2022  Unnamed: 67  \n",
      "0    37333.515680  41273.615050          NaN  \n",
      "1     3531.172747   3566.269439          NaN  \n",
      "2     1516.273265           NaN          NaN  \n",
      "3     4016.874461   4066.483230          NaN  \n",
      "4     5911.835657   5906.115677          NaN  \n",
      "..            ...           ...          ...  \n",
      "261  11884.181370  12675.067930          NaN  \n",
      "262           NaN           NaN          NaN  \n",
      "263  13337.790130  13478.753860          NaN  \n",
      "264   3287.420524   3365.873780          NaN  \n",
      "265   2115.144555   2207.957033          NaN  \n",
      "\n",
      "[266 rows x 68 columns]\n",
      "Index(['Country Name', 'Country Code', 'Indicator Name', 'Indicator Code',\n",
      "       '1960', '1961', '1962', '1963', '1964', '1965', '1966', '1967', '1968',\n",
      "       '1969', '1970', '1971', '1972', '1973', '1974', '1975', '1976', '1977',\n",
      "       '1978', '1979', '1980', '1981', '1982', '1983', '1984', '1985', '1986',\n",
      "       '1987', '1988', '1989', '1990', '1991', '1992', '1993', '1994', '1995',\n",
      "       '1996', '1997', '1998', '1999', '2000', '2001', '2002', '2003', '2004',\n",
      "       '2005', '2006', '2007', '2008', '2009', '2010', '2011', '2012', '2013',\n",
      "       '2014', '2015', '2016', '2017', '2018', '2019', '2020', '2021', '2022',\n",
      "       'Unnamed: 67'],\n",
      "      dtype='object')\n"
     ]
    }
   ],
   "source": [
    "# now load new data gdp_pc_ppp.csv and store as a dataframe\n",
    "gdp_pc_ppp = pd.read_csv('./data/pip_all_data/gdp_pc_ppp.csv', sep = \";\")\n",
    "print(gdp_pc_ppp)\n",
    "print(gdp_pc_ppp.columns)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Get country economic growth rates to nowcast all data to 2022 including the average household consumption"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "    Country Code        GR\n",
      "0            AGO  0.858623\n",
      "1            ALB  1.166709\n",
      "2            ARE  1.047059\n",
      "3            ARM  1.130254\n",
      "4            AUS  1.040833\n",
      "..           ...       ...\n",
      "138          WSM  0.910895\n",
      "139          XKX  1.214533\n",
      "140          ZAF  0.963231\n",
      "141          ZMB  1.000147\n",
      "142          ZWE  1.002070\n",
      "\n",
      "[143 rows x 2 columns]\n"
     ]
    }
   ],
   "source": [
    "# Assuming 'gdp_pc_ppp' is a DataFrame with country codes as index (rows) and years as columns\n",
    "# Assuming 'data_modified' contains 'country_code' and 'survey_year'\n",
    "\n",
    "gr_data = {}\n",
    "\n",
    "# Ensure the country codes in gdp_pc_ppp are the index\n",
    "gdp_pc_ppp.set_index('Country Code', inplace=True)\n",
    "\n",
    "for country_code in data_modified['country_code']:\n",
    "    # Retrieve the corresponding survey year for the country\n",
    "    latest_year = data_modified.loc[data_modified['country_code'] == country_code, 'survey_year'].iloc[0]\n",
    "\n",
    "    if country_code in gdp_pc_ppp.index:\n",
    "        year = 2022\n",
    "        while year > latest_year:\n",
    "            if str(year) in gdp_pc_ppp.columns and str(latest_year) in gdp_pc_ppp.columns:\n",
    "                initial_value = gdp_pc_ppp.loc[country_code, str(latest_year)]\n",
    "                final_value = gdp_pc_ppp.loc[country_code, str(year)]\n",
    "\n",
    "                # Check if final value is not NaN\n",
    "                if not pd.isna(final_value):\n",
    "                    num_years = year - latest_year\n",
    "\n",
    "                    # Calculate GR, avoiding division by zero and NaN values\n",
    "                    if initial_value > 0 and final_value > 0 and num_years > 0:\n",
    "                        gr = (final_value / initial_value)\n",
    "                        gr_data[country_code] = gr  # Convert to percentage\n",
    "                        break\n",
    "\n",
    "            year -= 1  # Decrement the year\n",
    "\n",
    "# Convert the CAGR data into a DataFrame for better visualization\n",
    "gr_df = pd.DataFrame(list(gr_data.items()), columns=['Country Code', 'GR'])\n",
    "\n",
    "print(gr_df)\n",
    "\n",
    "# store the dataframe cagr_df as a csv file\n",
    "gr_df.to_csv('./data/pip_all_data/gr_df.csv')\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Do the nowcasting"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "AGO 5.482150726 3.420550539\n",
      "0.8586230107528467\n",
      "ALB 13.95218978 12.06470661\n",
      "1.1667088749555394\n",
      "ARE 78.77590282 72.12141495\n",
      "1.0470593108820714\n",
      "ARM 7.779151681 6.741889338\n",
      "1.1302541853778874\n",
      "AUS 64.60964342 53.05049471\n",
      "1.040833082359701\n",
      "AUT 66.33993192 59.10353403\n",
      "1.0773783406591668\n",
      "BDI 2.290275519 1.70075649\n",
      "0.8588033914084812\n",
      "BEL 58.74781503 53.04003039\n",
      "1.0868055531414078\n",
      "BEN 4.658511326 3.481832568\n",
      "1.129928460181735\n",
      "BFA 4.284381087 2.883932261\n",
      "1.052476168979977\n",
      "BGD 4.941591323 4.039827609\n",
      "BGR 30.87964727 22.82247518\n",
      "1.2000440192097068\n",
      "BIH 21.18461778 17.60119079\n",
      "1.5308754045482795\n",
      "BLR 19.26502611 17.2380521\n",
      "0.992336517957194\n",
      "BOL 19.54968931 14.91199872\n",
      "1.023797982189759\n",
      "BRA 19.17796735 11.59439867\n",
      "1.02428701800466\n",
      "BTN 15.75962471 13.86246955\n",
      "BWA 8.82108724 4.842954987\n",
      "1.134221930649225\n",
      "CAF 2.240665199 1.566592566\n",
      "0.9830459503428068\n",
      "CAN 65.73275249 56.30329447\n",
      "0.9959107739422574\n",
      "CHE 77.10288515 64.34562469\n",
      "1.0380424513425506\n",
      "CHL 25.48751316 17.01272501\n",
      "1.126926472175095\n",
      "CHN 13.62209667 10.39384443\n",
      "1.1160506272133524\n",
      "CIV 5.65988051 4.349199775\n",
      "1.1205181700770883\n",
      "CMR 5.752897808 3.889154974\n",
      "1.054740409067653\n",
      "COD 2.024651365 1.458797963\n",
      "1.2339680429558684\n",
      "COG 4.591497235 2.98037989\n",
      "0.732686201374139\n",
      "COL 14.16004361 8.621794474\n",
      "1.0651746185229076\n",
      "COM 6.423948174 4.389313058\n",
      "1.0196696755423122\n",
      "CPV 9.503320745 6.727809315\n",
      "1.102708185220389\n",
      "CRI 24.46399742 15.86023025\n",
      "CYP 52.92831589 43.55058637\n",
      "1.128522820221628\n",
      "CZE 40.03665711 35.21275172\n",
      "1.0624396699990797\n",
      "DEU 65.88020006 55.68428654\n",
      "1.0015820970294318\n",
      "DJI 5.443802427 4.085132447\n",
      "1.1207913444893811\n",
      "DNK 64.51866426 56.41872386\n",
      "1.0843066923909253\n",
      "DOM 14.04361086 10.56639926\n",
      "1.0382225610785405\n",
      "DZA 9.225503374 8.043640163\n",
      "1.007581863044067\n",
      "ECU 15.20287898 10.65527279\n",
      "EGY 6.758395642 5.422778906\n",
      "1.0719097739376655\n",
      "ESP 46.99941892 40.26365038\n",
      "1.1157123387736607\n",
      "EST 42.84302612 35.99889323\n",
      "1.0509583919741898\n",
      "ETH 3.738985838 3.06188474\n",
      "1.36014329910757\n",
      "FIN 58.82331537 51.20898965\n",
      "1.0432237534738122\n",
      "FJI 7.907094894 6.643386944\n",
      "0.9355485002984956\n",
      "FRA 57.39693033 50.13696093\n",
      "1.0840588017102788\n",
      "FSM 5.557104227 4.22649242\n",
      "0.9425175615061138\n",
      "GAB 12.33692283 9.673192231\n",
      "0.9628167803111864\n",
      "GBR 54.88597286 46.31554201\n",
      "1.1358450914768534\n",
      "GEO 7.546757689 6.343375879\n",
      "1.1027776218263443\n",
      "GHA 5.089204459 3.73792719\n",
      "1.1755290637021714\n",
      "GIN 4.398163346 3.803831128\n",
      "1.0920047354962237\n",
      "GMB 5.084615632 3.819652312\n",
      "0.9967635027678164\n",
      "GNB 4.206295693 3.313596181\n",
      "1.0017416979495677\n",
      "GRC 28.8075373 24.8130051\n",
      "1.1738376938593085\n",
      "GTM 9.400288583 6.169566921\n",
      "1.1540018585580296\n",
      "HND 10.12391792 6.927711695\n",
      "1.0170055917174736\n",
      "HRV 30.29176338 26.99778584\n",
      "1.270353886322141\n",
      "HTI 4.238259216 3.198856446\n",
      "0.9245775574022034\n",
      "HUN 29.9200249 26.53098558\n",
      "1.1320652337617243\n",
      "IDN 7.805834767 5.804181255\n",
      "IND 4.898386472 3.828473573\n",
      "1.0651254030023487\n",
      "IRL 58.01933515 49.20635999\n",
      "1.2250098740225095\n",
      "IRN 14.45230311 10.58808878\n",
      "1.0213395666532936\n",
      "IRQ 11.30091101 9.614322877\n",
      "0.994275636648117\n",
      "ISL 62.97519736 56.52166585\n",
      "0.9987229019448736\n",
      "ISR 40.95950097 33.64546574\n",
      "1.102379758354479\n",
      "ITA 49.76040928 41.83478837\n",
      "1.132980233377056\n",
      "JPN 49.75940779 41.49211239\n",
      "1.052351464899997\n",
      "KAZ 12.42641288 10.41767381\n",
      "1.021490072866324\n",
      "KEN 3.591901927 2.613244235\n",
      "1.02863571214044\n",
      "KGZ 6.453587247 5.433074452\n",
      "1.05834091768512\n",
      "KIR 6.249744875 5.326873454\n",
      "1.0199555069376602\n",
      "KOR 43.20893204 37.62030842\n",
      "1.1409051377689532\n",
      "LAO 6.521311869 4.757384479\n",
      "1.053181389730686\n",
      "LBN 30.78607816 26.11667908\n",
      "0.6766159465915601\n",
      "LBR 3.883071563 3.095038582\n",
      "0.9576949201813264\n",
      "LCA 20.05346927 13.04611707\n",
      "1.019556175468779\n",
      "LKA 9.248450093 6.913039966\n",
      "0.894502369549685\n",
      "LSO 4.643397075 3.237574\n",
      "0.8713631842075311\n",
      "LTU 43.67447155 34.04034278\n",
      "1.0746502977078076\n",
      "LUX 93.32779385 77.76415713\n",
      "1.048739083281114\n",
      "LVA 35.50226949 28.56620703\n",
      "1.11552827900221\n",
      "MAR 10.5652158 7.712148873\n",
      "1.0979547334619226\n",
      "MDA 12.11185709 10.60900954\n",
      "0.9711380850063692\n",
      "MDG 1.590663091 1.118723779\n",
      "1.003655728249937\n",
      "MDV 18.69046708 15.83059972\n",
      "1.0134946012898003\n",
      "MEX 13.9727008 9.551501913\n",
      "1.0866496316468754\n",
      "MHL 11.56380982 9.189688521\n",
      "1.0498589099112876\n",
      "MKD 14.94910816 13.47555167\n",
      "1.0212016359648826\n",
      "MLI 4.926420606 3.768479032\n",
      "0.9757719311022448\n",
      "MLT 56.98932859 48.10663771\n",
      "1.164967824743047\n",
      "MMR 6.579838943 5.471380418\n",
      "0.9100219884014772\n",
      "MNE 16.97131007 14.61829785\n",
      "1.0687406144467588\n",
      "MNG 9.86178665 8.099155896\n",
      "1.0017383952242034\n",
      "MOZ 2.102977999 1.298211364\n",
      "0.969465203063949\n",
      "MRT 6.306488486 5.300380081\n",
      "1.0617672185855909\n",
      "MUS 16.50926277 12.73115478\n",
      "1.0314997048240515\n",
      "MWI 2.06936285 1.533902763\n",
      "0.9665127193260808\n",
      "MYS 32.81152015 24.21878145\n",
      "1.0576771279518258\n",
      "NAM 11.65441383 5.684173542\n",
      "0.9028551075508162\n",
      "NER 2.851470197 2.131262032\n",
      "1.068189756433911\n",
      "NGA 3.655930119 2.953270161\n",
      "0.9751232116400944\n",
      "NIC 11.71520108 8.074234202\n",
      "1.0811151560413372\n",
      "NLD 64.17478288 58.84873393\n",
      "1.091647419078312\n",
      "NOR 74.0588418 65.2998402\n",
      "1.0381162853181691\n",
      "NRU 6.968660817 5.651992421\n",
      "1.2085534060228935\n",
      "PAK 4.975486052 4.073170441\n",
      "1.0515372935196017\n",
      "PAN 30.86682863 19.19984289\n",
      "1.0936878283722815\n",
      "PER 12.08745044 9.126669279\n",
      "1.0167626957258886\n",
      "PHL 6.556416667 4.976315439\n",
      "1.0600769990554526\n",
      "POL 20.49929469 17.24107752\n",
      "1.1371197681829983\n",
      "PRT 36.5825067 29.73526039\n",
      "1.117345895627467\n",
      "PRY 18.45687473 12.63964386\n",
      "PSE 13.82565282 11.42100759\n",
      "0.8887200109426814\n",
      "ROU 14.34463673 12.66054955\n",
      "1.130834612274861\n",
      "RUS 22.53016062 17.40707936\n",
      "1.0324936623068703\n",
      "RWA 3.086795569 2.073804885\n",
      "1.239055563441377\n",
      "SDN 4.563741866 3.661623371\n",
      "0.7475175614849925\n",
      "SEN 5.98155848 4.43355621\n",
      "1.0581115340467084\n",
      "SLB 4.024132832 3.103404029\n",
      "0.8897922810086762\n",
      "SLE 3.885994758 2.959349694\n",
      "1.0151774510700904\n",
      "SLV 13.10262646 10.35562829\n",
      "SRB 15.29070407 13.20072283\n",
      "1.140874954483703\n",
      "SSD 2.150079634 1.52081173\n",
      "STP 5.4618509 4.002456853\n",
      "1.0194457437151452\n",
      "SVK 26.04363931 24.7624344\n",
      "1.0349194604083982\n",
      "SVN 45.5951014 41.31364957\n",
      "1.103894445981168\n",
      "SWE 58.80760952 51.81282494\n",
      "1.0776340708365808\n",
      "SWZ 5.632313921 2.953730301\n",
      "1.116518631581333\n",
      "SYC 22.2515614 19.49796447\n",
      "0.8976198806096375\n",
      "TCD 3.81303182 2.879394803\n",
      "0.9035056119526385\n",
      "TGO 4.547689358 3.205116137\n",
      "1.0931962866703824\n",
      "THA 16.62163487 12.93836271\n",
      "1.0246037367496772\n",
      "TJK 6.518495279 5.334834108\n",
      "1.3977427424057574\n",
      "TLS 3.425857691 2.896919514\n",
      "1.2303904181884384\n",
      "TON 8.450248206 7.010862015\n",
      "1.088283971065033\n",
      "TUN 13.77578985 11.37564768\n",
      "0.983145814868392\n",
      "TUR 22.20290192 16.18479989\n",
      "1.1640926861550145\n",
      "TZA 3.294749942 2.330755442\n",
      "1.06766532832423\n",
      "UGA 3.515108243 2.46764289\n",
      "1.0133593874005429\n",
      "UKR 14.31595693 12.59921156\n",
      "0.8648953919597434\n",
      "URY 27.02047119 20.20895232\n",
      "1.0502812076964154\n",
      "USA 87.60349193 66.64518423\n",
      "1.015514874498173\n",
      "VNM 15.0499187 11.91696027\n",
      "1.0905123011105131\n",
      "VUT 5.43159825 4.674257239\n",
      "0.9073411233938412\n",
      "WSM 10.20471801 7.577042426\n",
      "0.910895056971302\n",
      "XKX 9.534210969 8.108823121\n",
      "1.2145326615714922\n",
      "ZAF 11.9430583 4.753422088\n",
      "0.9632311622049484\n",
      "ZMB 2.974415875 1.608134731\n",
      "1.0001469436167345\n",
      "ZWE 4.500191627 2.654817388\n",
      "1.002069633568213\n",
      "     Unnamed: 0                  region_name region_code  \\\n",
      "0             0           Sub-Saharan Africa         SSA   \n",
      "1             1        Europe & Central Asia         ECA   \n",
      "2             2  Other High Income Countries         OHI   \n",
      "3             3        Europe & Central Asia         ECA   \n",
      "4             4  Other High Income Countries         OHI   \n",
      "..          ...                          ...         ...   \n",
      "146         147          East Asia & Pacific         EAP   \n",
      "147         148        Europe & Central Asia         ECA   \n",
      "148         150           Sub-Saharan Africa         SSA   \n",
      "149         151           Sub-Saharan Africa         SSA   \n",
      "150         152           Sub-Saharan Africa         SSA   \n",
      "\n",
      "             country_name country_code  survey_year welfare_type       mean  \\\n",
      "0                  Angola          AGO         2018  consumption   4.707101   \n",
      "1                 Albania          ALB         2020  consumption  16.278144   \n",
      "2    United Arab Emirates          ARE         2018       income  82.483043   \n",
      "3                 Armenia          ARM         2021  consumption   8.792419   \n",
      "4               Australia          AUS         2018       income  67.247854   \n",
      "..                    ...          ...          ...          ...        ...   \n",
      "146                 Samoa          WSM         2013  consumption   9.295427   \n",
      "147                Kosovo          XKX         2017  consumption  11.579611   \n",
      "148          South Africa          ZAF         2014  consumption  11.503926   \n",
      "149                Zambia          ZMB         2015  consumption   2.974853   \n",
      "150              Zimbabwe          ZWE         2019  consumption   4.509505   \n",
      "\n",
      "        median      gini   decile1   decile2   decile3   decile4   decile5  \\\n",
      "0     2.936963  0.512721  0.013295  0.024281  0.033329  0.043429  0.055843   \n",
      "1    14.076000  0.294196  0.034134  0.050295  0.060906  0.071085  0.081072   \n",
      "2    75.515399  0.259663  0.039759  0.052413  0.063296  0.074222  0.085614   \n",
      "3     7.620049  0.279401  0.039655  0.054532  0.064443  0.073143  0.082082   \n",
      "4    55.216710  0.343338  0.026835  0.045859  0.056267  0.065388  0.075544   \n",
      "..         ...       ...       ...       ...       ...       ...       ...   \n",
      "146   6.901890  0.387318  0.027364  0.041061  0.050724  0.059788  0.069138   \n",
      "147   9.848431  0.290129  0.037878  0.053771  0.063437  0.072504  0.080627   \n",
      "148   4.578644  0.630261  0.008572  0.015071  0.020742  0.027092  0.035043   \n",
      "149   1.608371  0.559366  0.010955  0.019803  0.027443  0.035858  0.047451   \n",
      "150   2.660312  0.502565  0.019302  0.028315  0.035713  0.043637  0.053426   \n",
      "\n",
      "      decile6   decile7   decile8   decile9  decile10  \n",
      "0    0.069814  0.088688  0.115687  0.159390  0.396244  \n",
      "1    0.092196  0.105650  0.124423  0.152643  0.227596  \n",
      "2    0.097839  0.111422  0.127383  0.148486  0.199565  \n",
      "3    0.090937  0.101741  0.116539  0.141399  0.235529  \n",
      "4    0.088060  0.102666  0.121659  0.151588  0.266134  \n",
      "..        ...       ...       ...       ...       ...  \n",
      "146  0.079920  0.093974  0.114448  0.150747  0.312836  \n",
      "147  0.089841  0.100717  0.115593  0.140038  0.245595  \n",
      "148  0.046496  0.065332  0.099568  0.177095  0.504990  \n",
      "149  0.061739  0.082244  0.111624  0.168343  0.434540  \n",
      "150  0.065919  0.082803  0.108676  0.155953  0.406255  \n",
      "\n",
      "[151 rows x 20 columns]\n",
      "Index(['region_name', 'region_code', 'country_name', 'country_code',\n",
      "       'survey_year', 'welfare_type', 'mean', 'median', 'gini', 'decile1',\n",
      "       'decile2', 'decile3', 'decile4', 'decile5', 'decile6', 'decile7',\n",
      "       'decile8', 'decile9', 'decile10'],\n",
      "      dtype='object')\n",
      "Index(['region_name', 'region_code', 'country_name', 'country_code',\n",
      "       'survey_year', 'nowcasted_year', 'welfare_type', 'mean', 'median',\n",
      "       'gini', 'decile1', 'decile2', 'decile3', 'decile4', 'decile5',\n",
      "       'decile6', 'decile7', 'decile8', 'decile9', 'decile10'],\n",
      "      dtype='object')\n"
     ]
    }
   ],
   "source": [
    "# Load data_modified from csv file and store as a dataframe\n",
    "data_modified = pd.read_csv('./data/pip_all_data/data_modified.csv')\n",
    "# Load gr_df from csv file and store as a dataframe\n",
    "gr_df = pd.read_csv('./data/pip_all_data/gr_df.csv')\n",
    "# Iterate over each row in data_modified\n",
    "for index, row in data_modified.iterrows():\n",
    "    country_code = row['country_code']\n",
    "    mean = row['mean']\n",
    "    median = row['median']\n",
    "    print(country_code, mean, median)\n",
    "\n",
    "    # Find the corresponding growth rate (GR) for the country code in gr_df\n",
    "    # check also whether country_code exists in gr_df\n",
    "    if country_code in gr_df['Country Code'].values: \n",
    "        gr = gr_df.loc[gr_df['Country Code'] == country_code, 'GR'].values[0]\n",
    "        print(gr)\n",
    "\n",
    "        # Adjust the mean and median values by multiplying with the growth rate\n",
    "        data_modified.at[index, 'mean'] = mean * gr\n",
    "        data_modified.at[index, 'median'] = median * gr\n",
    "\n",
    "# Print the updated data_modified dataframe\n",
    "print(data_modified)\n",
    "#delete first column in data_modified\n",
    "data_modified = data_modified.drop(data_modified.columns[[0]], axis=1)\n",
    "print(data_modified.columns)    \n",
    "# first save the new dataframe data_modified as a new dataframe data_nowcasted\n",
    "data_nowcasted = data_modified\n",
    "# add a new column in data_nowcasted between the columns survey_year and welfare_type and call it nowcasted_year\n",
    "data_nowcasted.insert(5, 'nowcasted_year', 0)\n",
    "# set all values in this column to 2022\n",
    "data_nowcasted['nowcasted_year'] = 2022\n",
    "print(data_nowcasted.columns)\n",
    "# store the dataframe data_modified as a csv file but under different name data_nowcasted\n",
    "data_modified.to_csv('./data/pip_all_data/data_nowcasted.csv')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Extend the data via the important variables population and gdp growth rate"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "AGO\n",
      "5906.115677\n",
      "ALB\n",
      "15492.0674\n",
      "ARE\n",
      "74917.67411\n",
      "ARM\n",
      "16057.00646\n",
      "AUS\n",
      "51090.26333\n",
      "AUT\n",
      "55867.18381\n",
      "BDI\n",
      "708.1783134\n",
      "BEL\n",
      "53287.15361\n",
      "BEN\n",
      "3435.171617\n",
      "BFA\n",
      "2158.858516\n",
      "BGD\n",
      "6263.004635\n",
      "BGR\n",
      "26961.29126\n",
      "BIH\n",
      "16737.22601\n",
      "BLR\n",
      "19092.11117\n",
      "BOL\n",
      "8244.235658\n",
      "BRA\n",
      "15093.46512\n",
      "BTN\n",
      "BWA\n",
      "15518.65105\n",
      "CAF\n",
      "823.9821853\n",
      "CAN\n",
      "48974.58659\n",
      "CHE\n",
      "72278.20726\n",
      "CHL\n",
      "25886.12136\n",
      "CHN\n",
      "18187.84112\n",
      "CIV\n",
      "5537.369758\n",
      "CMR\n",
      "3723.532337\n",
      "COD\n",
      "1073.643271\n",
      "COG\n",
      "3700.270102\n",
      "COL\n",
      "15616.75222\n",
      "COM\n",
      "3245.768697\n",
      "CPV\n",
      "7379.247565\n",
      "CRI\n",
      "22070.92097\n",
      "CYP\n",
      "44996.31641\n",
      "CZE\n",
      "41052.64709\n",
      "DEU\n",
      "53969.62555\n",
      "DJI\n",
      "4989.408723\n",
      "DNK\n",
      "59935.11992\n",
      "DOM\n",
      "19338.01511\n",
      "DZA\n",
      "11198.23348\n",
      "ECU\n",
      "10859.21783\n",
      "EGY\n",
      "12180.45165\n",
      "ESP\n",
      "40223.01254\n",
      "EST\n",
      "37711.81883\n",
      "ETH\n",
      "2381.165784\n",
      "FIN\n",
      "49418.7794\n",
      "FJI\n",
      "12388.10853\n",
      "FRA\n",
      "45904.4113\n",
      "FSM\n",
      "3320.91129\n",
      "GAB\n",
      "13939.78681\n",
      "GBR\n",
      "47587.16735\n",
      "GEO\n",
      "17078.34096\n",
      "GHA\n",
      "5480.329983\n",
      "GIN\n",
      "2699.128855\n",
      "GMB\n",
      "2076.566434\n",
      "GNB\n",
      "1855.111973\n",
      "GRC\n",
      "31704.42561\n",
      "GTM\n",
      "9162.052788\n",
      "HND\n",
      "5709.124208\n",
      "HRV\n",
      "34302.34882\n",
      "HTI\n",
      "2799.112561\n",
      "HUN\n",
      "35356.77581\n",
      "IDN\n",
      "12409.75885\n",
      "IND\n",
      "7112.039396\n",
      "IRL\n",
      "112445.421\n",
      "IRN\n",
      "15004.68797\n",
      "IRQ\n",
      "9199.020694\n",
      "ISL\n",
      "55567.43625\n",
      "ISR\n",
      "44393.3003\n",
      "ITA\n",
      "44292.19118\n",
      "JPN\n",
      "41641.16501\n",
      "KAZ\n",
      "26093.29458\n",
      "KEN\n",
      "4881.531729\n",
      "KGZ\n",
      "5070.150236\n",
      "KIR\n",
      "2002.718218\n",
      "KOR\n",
      "44299.9101\n",
      "LAO\n",
      "7947.658797\n",
      "LBN\n",
      "LBR\n",
      "1460.92339\n",
      "LCA\n",
      "15100.28601\n",
      "LKA\n",
      "12200.13739\n",
      "LSO\n",
      "2240.339251\n",
      "LTU\n",
      "39955.24616\n",
      "LUX\n",
      "117746.9896\n",
      "LVA\n",
      "32992.38236\n",
      "MAR\n",
      "8083.365234\n",
      "MDA\n",
      "13308.41733\n",
      "MDG\n",
      "1502.482182\n",
      "MDV\n",
      "21271.31768\n",
      "MEX\n",
      "20254.78114\n",
      "MHL\n",
      "6004.342769\n",
      "MKD\n",
      "17128.7605\n",
      "MLI\n",
      "2132.623315\n",
      "MLT\n",
      "48641.85008\n",
      "MMR\n",
      "4249.876151\n",
      "MNE\n",
      "22108.73079\n",
      "MNG\n",
      "12073.24546\n",
      "MOZ\n",
      "1250.772414\n",
      "MRT\n",
      "5330.214503\n",
      "MUS\n",
      "22841.35055\n",
      "MWI\n",
      "1466.879417\n",
      "MYS\n",
      "28383.61865\n",
      "NAM\n",
      "9762.776892\n",
      "NER\n",
      "1274.641741\n",
      "NGA\n",
      "4963.160313\n",
      "NIC\n",
      "5822.353525\n",
      "NLD\n",
      "59249.167\n",
      "NOR\n",
      "67460.27828\n",
      "NRU\n",
      "11024.34299\n",
      "PAK\n",
      "5376.966856\n",
      "PAN\n",
      "33266.47684\n",
      "PER\n",
      "12743.94239\n",
      "PHL\n",
      "8581.937693\n",
      "POL\n",
      "37706.60613\n",
      "PRT\n",
      "35767.72258\n",
      "PRY\n",
      "13531.39666\n",
      "PSE\n",
      "5722.409175\n",
      "ROU\n",
      "32495.90916\n",
      "RUS\n",
      "27450.44922\n",
      "RWA\n",
      "2364.80197\n",
      "SDN\n",
      "3570.607422\n",
      "SEN\n",
      "3564.628137\n",
      "SLB\n",
      "2247.784882\n",
      "SLE\n",
      "1634.76383\n",
      "SLV\n",
      "9396.01378\n",
      "SRB\n",
      "20885.88861\n",
      "SSD\n",
      "STP\n",
      "3438.859825\n",
      "SVK\n",
      "33176.18457\n",
      "SVN\n",
      "41015.22523\n",
      "SWE\n",
      "55316.33862\n",
      "SWZ\n",
      "9058.583177\n",
      "SYC\n",
      "25205.73629\n",
      "TCD\n",
      "1412.670718\n",
      "TGO\n",
      "2202.726816\n",
      "THA\n",
      "17507.62019\n",
      "TJK\n",
      "4137.2776\n",
      "TLS\n",
      "3943.092434\n",
      "TON\n",
      "TUN\n",
      "10569.05083\n",
      "TUR\n",
      "33149.49822\n",
      "TZA\n",
      "2623.861572\n",
      "UGA\n",
      "2280.073743\n",
      "UKR\n",
      "10731.44043\n",
      "URY\n",
      "24426.65935\n",
      "USA\n",
      "64623.12563\n",
      "VNM\n",
      "11396.5313\n",
      "VUT\n",
      "2785.90386\n",
      "WSM\n",
      "5155.620076\n",
      "XKX\n",
      "12675.06793\n",
      "ZAF\n",
      "13478.75386\n",
      "ZMB\n",
      "3365.87378\n",
      "ZWE\n",
      "2207.957033\n"
     ]
    }
   ],
   "source": [
    "# import the data from the world bank data file gdp_pc_ppp.csv and store as a dataframe and from population.csv and store as a dataframe\n",
    "gdp_pc_ppp = pd.read_csv('./data/pip_all_data/gdp_pc_ppp.csv', sep = \";\")\n",
    "population = pd.read_csv('./data/pip_all_data/population.csv')\n",
    "\n",
    "# add a new empty column to data_nowcasted called gdp_pc_ppp\n",
    "data_nowcasted['gdp_pc_ppp_2017'] = 0.0\n",
    "# add a new empty column to data_nowcasted called population\n",
    "data_nowcasted['population'] = 0.0\n",
    "# iterate over all countries in data_nowcasted in the column country_code\n",
    "for country in data_nowcasted['country_code']:\n",
    "    print(country)\n",
    "\n",
    "    # check whether value for 2022 exists in gdp_pc_ppp for this country\n",
    "    if not np.isnan(gdp_pc_ppp[gdp_pc_ppp['Country Code'] == country]['2022'].values[0]):\n",
    "        sub_data = float(gdp_pc_ppp[gdp_pc_ppp['Country Code'] == country]['2022'].values[0])\n",
    "        print(sub_data)\n",
    "        # if yes then store this value in data_nowcasted in the column gdp_pc_ppp\n",
    "        data_nowcasted.loc[data_nowcasted['country_code'] == country, 'gdp_pc_ppp_2017'] = sub_data\n",
    "    else:\n",
    "        sub_data = float(gdp_pc_ppp[gdp_pc_ppp['Country Code'] == country]['2021'].values[0])\n",
    "        #print(sub_data)\n",
    "        # if yes then store this value in data_nowcasted in the column gdp_pc_ppp\n",
    "        data_nowcasted.loc[data_nowcasted['country_code'] == country, 'gdp_pc_ppp_2017'] = sub_data\n",
    "\n",
    "    # check whether value for 2022 exists in population for this country\n",
    "    if not np.isnan(population[population['Country Code'] == country]['2022'].values[0]):\n",
    "        sub_data = float(population[population['Country Code'] == country]['2022'].values[0])\n",
    "        # if yes then store this value in data_nowcasted in the column population\n",
    "        data_nowcasted.loc[data_nowcasted['country_code'] == country, 'population'] = sub_data\n",
    "    else:\n",
    "        # find the most recent year for which population data for country is available in population df\n",
    "        sub_data = float(population[population['Country Code'] == country]['2021'].values[0])\n",
    "        # if yes then store this value in data_nowcasted in the column population\n",
    "        data_nowcasted.loc[data_nowcasted['country_code'] == country, 'population'] = sub_data\n",
    "        \n",
    "\n",
    "# south sudan there is no data available so set the data based on values from the IMF https://www.imf.org/external/datamapper/profile/SSD\n",
    "# but since this value is smaller than the value for South Sudan in terms for yearl consumption which is 2.150079634*365 = 784.5\n",
    "# we set the value for south sudan to 784.5\n",
    "data_nowcasted.loc[data_nowcasted['country_code'] == 'SSD', 'gdp_pc_ppp_2017'] = 785\n",
    "# store the dataframe data_nowcasted as a csv file\n",
    "data_nowcasted.to_csv('./data/pip_all_data/data_nowcasted_extended.csv')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## We also compute the trend in carbon intensity from 2010 to 2020 \n",
    "### and if that is not possible that from a year > 2010 to 2020"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "                    Country Name Country Code  \\\n",
      "0                          Aruba          ABW   \n",
      "1    Africa Eastern and Southern          AFE   \n",
      "2                    Afghanistan          AFG   \n",
      "3     Africa Western and Central          AFW   \n",
      "4                         Angola          AGO   \n",
      "..                           ...          ...   \n",
      "261                       Kosovo          XKX   \n",
      "262                  Yemen, Rep.          YEM   \n",
      "263                 South Africa          ZAF   \n",
      "264                       Zambia          ZMB   \n",
      "265                     Zimbabwe          ZWE   \n",
      "\n",
      "                               Indicator Name        Indicator Code  1960  \\\n",
      "0    CO2 emissions (kg per 2017 PPP $ of GDP)  EN.ATM.CO2E.PP.GD.KD   NaN   \n",
      "1    CO2 emissions (kg per 2017 PPP $ of GDP)  EN.ATM.CO2E.PP.GD.KD   NaN   \n",
      "2    CO2 emissions (kg per 2017 PPP $ of GDP)  EN.ATM.CO2E.PP.GD.KD   NaN   \n",
      "3    CO2 emissions (kg per 2017 PPP $ of GDP)  EN.ATM.CO2E.PP.GD.KD   NaN   \n",
      "4    CO2 emissions (kg per 2017 PPP $ of GDP)  EN.ATM.CO2E.PP.GD.KD   NaN   \n",
      "..                                        ...                   ...   ...   \n",
      "261  CO2 emissions (kg per 2017 PPP $ of GDP)  EN.ATM.CO2E.PP.GD.KD   NaN   \n",
      "262  CO2 emissions (kg per 2017 PPP $ of GDP)  EN.ATM.CO2E.PP.GD.KD   NaN   \n",
      "263  CO2 emissions (kg per 2017 PPP $ of GDP)  EN.ATM.CO2E.PP.GD.KD   NaN   \n",
      "264  CO2 emissions (kg per 2017 PPP $ of GDP)  EN.ATM.CO2E.PP.GD.KD   NaN   \n",
      "265  CO2 emissions (kg per 2017 PPP $ of GDP)  EN.ATM.CO2E.PP.GD.KD   NaN   \n",
      "\n",
      "     1961  1962  1963  1964  1965  ...      2013      2014      2015  \\\n",
      "0     NaN   NaN   NaN   NaN   NaN  ...       NaN       NaN       NaN   \n",
      "1     NaN   NaN   NaN   NaN   NaN  ...  0.277892  0.277531  0.261812   \n",
      "2     NaN   NaN   NaN   NaN   NaN  ...  0.137663  0.132291  0.141305   \n",
      "3     NaN   NaN   NaN   NaN   NaN  ...  0.119574  0.118960  0.114528   \n",
      "4     NaN   NaN   NaN   NaN   NaN  ...  0.127294  0.133383  0.141233   \n",
      "..    ...   ...   ...   ...   ...  ...       ...       ...       ...   \n",
      "261   NaN   NaN   NaN   NaN   NaN  ...       NaN       NaN       NaN   \n",
      "262   NaN   NaN   NaN   NaN   NaN  ...       NaN       NaN       NaN   \n",
      "263   NaN   NaN   NaN   NaN   NaN  ...  0.579025  0.585364  0.547784   \n",
      "264   NaN   NaN   NaN   NaN   NaN  ...  0.083526  0.088199  0.090645   \n",
      "265   NaN   NaN   NaN   NaN   NaN  ...  0.386198  0.374128  0.366036   \n",
      "\n",
      "         2016      2017      2018      2019      2020  2021  2022  \n",
      "0         NaN       NaN       NaN       NaN       NaN   NaN   NaN  \n",
      "1    0.256858  0.254394  0.250889  0.250208  0.229394   NaN   NaN  \n",
      "2    0.127703  0.134152  0.145136  0.143065  0.113537   NaN   NaN  \n",
      "3    0.118161  0.114726  0.116957  0.119809  0.116932   NaN   NaN  \n",
      "4    0.135225  0.114983  0.109881  0.114148  0.098304   NaN   NaN  \n",
      "..        ...       ...       ...       ...       ...   ...   ...  \n",
      "261       NaN       NaN       NaN       NaN       NaN   NaN   NaN  \n",
      "262       NaN       NaN       NaN       NaN       NaN   NaN   NaN  \n",
      "263  0.544961  0.550786  0.547863  0.555120  0.519763   NaN   NaN  \n",
      "264  0.093667  0.115956  0.128586  0.122862  0.126240   NaN   NaN  \n",
      "265  0.316214  0.284362  0.306479  0.301053  0.266532   NaN   NaN  \n",
      "\n",
      "[266 rows x 67 columns]\n",
      "not the right data available\n",
      "0.22939392\n",
      "0.113536665\n",
      "0.116932103\n",
      "0.098304023\n",
      "0.116320197\n",
      "not the right data available\n",
      "0.291491684\n",
      "0.29928749\n",
      "0.172921733\n",
      "0.180022331\n",
      "not the right data available\n",
      "0.268682157\n",
      "0.303712435\n",
      "0.127908163\n",
      "0.247607387\n",
      "0.082074351\n",
      "0.150886826\n",
      "0.198088046\n",
      "0.121332837\n",
      "0.091327745\n",
      "0.219134652\n",
      "0.457905532\n",
      "0.231218563\n",
      "0.432718119\n",
      "0.303666476\n",
      "0.229515198\n",
      "not the right data available\n",
      "0.200452876\n",
      "0.137672279\n",
      "0.278841252\n",
      "0.352346775\n",
      "0.11990153\n",
      "0.167106326\n",
      "0.0522339\n",
      "0.29447504\n",
      "0.185221578\n",
      "0.059505085\n",
      "not the right data available\n",
      "0.191338517\n",
      "0.475935682\n",
      "0.079798569\n",
      "0.10224569\n",
      "0.031209347\n",
      "0.334653033\n",
      "0.116201885\n",
      "0.126339206\n",
      "0.175380375\n",
      "0.068761703\n",
      "0.292774644\n",
      "not the right data available\n",
      "not the right data available\n",
      "not the right data available\n",
      "0.190401227\n",
      "0.214907399\n",
      "0.140116208\n",
      "0.082434928\n",
      "0.220485481\n",
      "0.084870774\n",
      "0.124053487\n",
      "0.342858641\n",
      "0.413928816\n",
      "0.225572748\n",
      "0.353551645\n",
      "0.330584212\n",
      "0.182432516\n",
      "0.189010353\n",
      "0.163568651\n",
      "0.124075137\n",
      "not the right data available\n",
      "0.118708077\n",
      "0.148771294\n",
      "0.068537777\n",
      "0.131647694\n",
      "0.178124177\n",
      "0.138694876\n",
      "0.101907671\n",
      "0.093368462\n",
      "not the right data available\n",
      "0.280341877\n",
      "0.167821699\n",
      "0.109823407\n",
      "0.197239355\n",
      "0.114540592\n",
      "not the right data available\n",
      "0.131962627\n",
      "0.116286713\n",
      "0.090488801\n",
      "0.181165508\n",
      "0.176502216\n",
      "0.199162285\n",
      "not the right data available\n",
      "0.119250758\n",
      "not the right data available\n",
      "0.188424183\n",
      "0.183588475\n",
      "not the right data available\n",
      "0.173605008\n",
      "0.107159196\n",
      "0.142977442\n",
      "0.095545724\n",
      "0.147017099\n",
      "0.329717139\n",
      "0.311241496\n",
      "0.135464917\n",
      "0.163271165\n",
      "0.179898958\n",
      "0.114378941\n",
      "not the right data available\n",
      "0.255360079\n",
      "not the right data available\n",
      "0.073734844\n",
      "0.489410602\n",
      "0.434236707\n",
      "0.076376338\n",
      "0.159906994\n",
      "0.121052598\n",
      "0.224742155\n",
      "0.20790434\n",
      "0.200839722\n",
      "0.445468121\n",
      "0.083177458\n",
      "0.28805545\n",
      "0.266027728\n",
      "0.23717411\n",
      "0.181745374\n",
      "0.259218607\n",
      "0.481975013\n",
      "0.147018779\n",
      "0.337493649\n",
      "0.274970576\n",
      "0.167534482\n",
      "0.427809068\n",
      "0.239043707\n",
      "0.147608153\n",
      "0.106844988\n",
      "0.13900486\n",
      "not the right data available\n",
      "0.077026765\n",
      "0.236371005\n",
      "0.317922211\n",
      "0.460910375\n",
      "0.385331439\n",
      "0.112534341\n",
      "0.110950554\n",
      "0.123264317\n",
      "not the right data available\n",
      "not the right data available\n",
      "0.237371661\n",
      "not the right data available\n",
      "0.275793238\n",
      "0.067726688\n",
      "0.204671806\n",
      "0.323039531\n",
      "0.163134207\n",
      "0.442606614\n",
      "0.321154722\n",
      "0.207824106\n",
      "0.092081822\n",
      "0.074856823\n",
      "0.134665456\n",
      "0.325024273\n",
      "0.222769171\n",
      "0.551192831\n",
      "not the right data available\n",
      "0.179949323\n",
      "0.163313485\n",
      "0.145241976\n",
      "0.056817991\n",
      "0.285612766\n",
      "0.222886278\n",
      "0.175634325\n",
      "not the right data available\n",
      "0.074370136\n",
      "0.110483162\n",
      "0.129670682\n",
      "0.137661042\n",
      "0.105448179\n",
      "0.135404825\n",
      "0.310902467\n",
      "0.146503817\n",
      "0.181181447\n",
      "0.472418329\n",
      "0.26724603\n",
      "0.16193109\n",
      "0.083868988\n",
      "0.125013233\n",
      "0.153047681\n",
      "0.536593983\n",
      "0.150535144\n",
      "0.226368107\n",
      "0.14253753\n",
      "not the right data available\n",
      "not the right data available\n",
      "0.118236518\n",
      "0.085947555\n",
      "not the right data available\n",
      "0.136066845\n",
      "0.183231811\n",
      "not the right data available\n",
      "0.35640501\n",
      "0.122658479\n",
      "0.415595397\n",
      "0.050871319\n",
      "0.225300429\n",
      "0.318657492\n",
      "0.120768527\n",
      "0.191996377\n",
      "0.0809891\n",
      "0.130839512\n",
      "0.080232742\n",
      "0.122162358\n",
      "not the right data available\n",
      "0.027415297\n",
      "0.367660911\n",
      "0.180254984\n",
      "not the right data available\n",
      "0.180284747\n",
      "0.268184706\n",
      "0.183998761\n",
      "0.279540571\n",
      "0.171798773\n",
      "0.159729007\n",
      "0.063177638\n",
      "0.117254466\n",
      "not the right data available\n",
      "0.22769276\n",
      "not the right data available\n",
      "not the right data available\n",
      "0.063274931\n",
      "0.412731961\n",
      "0.305219454\n",
      "0.13896177\n",
      "0.220227391\n",
      "0.267674801\n",
      "0.713689033\n",
      "0.149078377\n",
      "0.070583465\n",
      "0.326997192\n",
      "0.176503898\n",
      "0.225300429\n",
      "0.180284747\n",
      "0.434194937\n",
      "0.23989967\n",
      "0.170083942\n",
      "0.130245588\n",
      "0.094513931\n",
      "0.057037979\n",
      "0.319833683\n",
      "0.359231229\n",
      "0.086063824\n",
      "0.216640026\n",
      "0.455781307\n",
      "0.153437368\n",
      "not the right data available\n",
      "not the right data available\n",
      "not the right data available\n",
      "0.351791526\n",
      "0.136601977\n",
      "0.264751778\n",
      "0.15848031\n",
      "not the right data available\n",
      "not the right data available\n",
      "0.51976274\n",
      "0.126239572\n",
      "0.266531865\n",
      "0.22939392\n",
      "0.113536665\n",
      "0.116932103\n",
      "0.098304023\n",
      "0.116320197\n",
      "0.291491684\n",
      "0.29928749\n",
      "0.172921733\n",
      "0.180022331\n",
      "0.268682157\n",
      "0.303712435\n",
      "0.127908163\n",
      "0.247607387\n",
      "0.082074351\n",
      "0.150886826\n",
      "0.198088046\n",
      "0.121332837\n",
      "0.091327745\n",
      "0.219134652\n",
      "0.457905532\n",
      "0.231218563\n",
      "0.432718119\n",
      "0.303666476\n",
      "0.229515198\n",
      "0.200452876\n",
      "0.137672279\n",
      "0.278841252\n",
      "0.352346775\n",
      "0.11990153\n",
      "0.167106326\n",
      "0.0522339\n",
      "0.29447504\n",
      "0.185221578\n",
      "0.059505085\n",
      "0.191338517\n",
      "0.475935682\n",
      "0.079798569\n",
      "0.10224569\n",
      "0.031209347\n",
      "0.334653033\n",
      "0.116201885\n",
      "0.126339206\n",
      "0.175380375\n",
      "0.068761703\n",
      "0.292774644\n",
      "0.190401227\n",
      "0.214907399\n",
      "0.140116208\n",
      "0.082434928\n",
      "0.220485481\n",
      "0.084870774\n",
      "0.124053487\n",
      "0.342858641\n",
      "0.413928816\n",
      "0.225572748\n",
      "0.353551645\n",
      "0.330584212\n",
      "0.182432516\n",
      "0.189010353\n",
      "0.163568651\n",
      "0.124075137\n",
      "0.118708077\n",
      "0.148771294\n",
      "0.068537777\n",
      "0.131647694\n",
      "0.178124177\n",
      "0.138694876\n",
      "0.101907671\n",
      "0.093368462\n",
      "0.280341877\n",
      "0.167821699\n",
      "0.109823407\n",
      "0.197239355\n",
      "0.114540592\n",
      "0.131962627\n",
      "0.116286713\n",
      "0.090488801\n",
      "0.181165508\n",
      "0.176502216\n",
      "0.199162285\n",
      "0.119250758\n",
      "0.188424183\n",
      "0.183588475\n",
      "0.173605008\n",
      "0.107159196\n",
      "0.142977442\n",
      "0.095545724\n",
      "0.147017099\n",
      "0.329717139\n",
      "0.311241496\n",
      "0.135464917\n",
      "0.163271165\n",
      "0.179898958\n",
      "0.114378941\n",
      "0.255360079\n",
      "0.073734844\n",
      "0.489410602\n",
      "0.434236707\n",
      "0.076376338\n",
      "0.159906994\n",
      "0.121052598\n",
      "0.224742155\n",
      "0.20790434\n",
      "0.200839722\n",
      "0.445468121\n",
      "0.083177458\n",
      "0.28805545\n",
      "0.266027728\n",
      "0.23717411\n",
      "0.181745374\n",
      "0.259218607\n",
      "0.481975013\n",
      "0.147018779\n",
      "0.337493649\n",
      "0.274970576\n",
      "0.167534482\n",
      "0.427809068\n",
      "0.239043707\n",
      "0.147608153\n",
      "0.106844988\n",
      "0.13900486\n",
      "0.077026765\n",
      "0.236371005\n",
      "0.317922211\n",
      "0.460910375\n",
      "0.385331439\n",
      "0.112534341\n",
      "0.110950554\n",
      "0.123264317\n",
      "0.237371661\n",
      "0.275793238\n",
      "0.067726688\n",
      "0.204671806\n",
      "0.323039531\n",
      "0.163134207\n",
      "0.442606614\n",
      "0.321154722\n",
      "0.207824106\n",
      "0.092081822\n",
      "0.074856823\n",
      "0.134665456\n",
      "0.325024273\n",
      "0.222769171\n",
      "0.551192831\n",
      "0.179949323\n",
      "0.163313485\n",
      "0.145241976\n",
      "0.056817991\n",
      "0.285612766\n",
      "0.222886278\n",
      "0.175634325\n",
      "0.074370136\n",
      "0.110483162\n",
      "0.129670682\n",
      "0.137661042\n",
      "0.105448179\n",
      "0.135404825\n",
      "0.310902467\n",
      "0.146503817\n",
      "0.181181447\n",
      "0.472418329\n",
      "0.26724603\n",
      "0.16193109\n",
      "0.083868988\n",
      "0.125013233\n",
      "0.153047681\n",
      "0.536593983\n",
      "0.150535144\n",
      "0.226368107\n",
      "0.14253753\n",
      "0.118236518\n",
      "0.085947555\n",
      "0.136066845\n",
      "0.183231811\n",
      "0.35640501\n",
      "0.122658479\n",
      "0.415595397\n",
      "0.050871319\n",
      "0.225300429\n",
      "0.318657492\n",
      "0.120768527\n",
      "0.191996377\n",
      "0.0809891\n",
      "0.130839512\n",
      "0.080232742\n",
      "0.122162358\n",
      "0.027415297\n",
      "0.367660911\n",
      "0.180254984\n",
      "0.180284747\n",
      "0.268184706\n",
      "0.183998761\n",
      "0.279540571\n",
      "0.171798773\n",
      "0.159729007\n",
      "0.063177638\n",
      "0.117254466\n",
      "0.22769276\n",
      "0.063274931\n",
      "0.412731961\n",
      "0.305219454\n",
      "0.13896177\n",
      "0.220227391\n",
      "0.267674801\n",
      "0.713689033\n",
      "0.149078377\n",
      "0.070583465\n",
      "0.326997192\n",
      "0.176503898\n",
      "0.225300429\n",
      "0.180284747\n",
      "0.434194937\n",
      "0.23989967\n",
      "0.170083942\n",
      "0.130245588\n",
      "0.094513931\n",
      "0.057037979\n",
      "0.319833683\n",
      "0.359231229\n",
      "0.086063824\n",
      "0.216640026\n",
      "0.455781307\n",
      "0.153437368\n",
      "0.351791526\n",
      "0.136601977\n",
      "0.264751778\n",
      "0.15848031\n",
      "0.51976274\n",
      "0.126239572\n",
      "0.266531865\n",
      "0.0934084652205355\n",
      "0.1101410047997678\n",
      "0.2919328289180993\n",
      "0.1837920904196085\n",
      "0.2873693712649685\n",
      "0.1221003700123916\n",
      "0.0929563804183898\n",
      "0.1419897012106034\n",
      "0.1989313230243595\n",
      "0.1324773905633184\n",
      "0.0896489520840285\n",
      "0.2015348424138672\n",
      "0.4180446543668307\n",
      "0.2911992841115966\n",
      "0.1965334565514499\n",
      "0.1378666983645912\n",
      "0.1290364799010859\n",
      "0.1764250395221867\n",
      "0.0572002072254206\n",
      "0.2841406320914139\n",
      "0.0549604917537986\n",
      "0.1909572598931938\n",
      "0.4389654095379153\n",
      "0.0796680269129554\n",
      "0.1013251323251862\n",
      "0.028696866456562\n",
      "0.3570741935672722\n",
      "0.1153842253272436\n",
      "0.13690876629016\n",
      "0.1792547185462388\n",
      "0.0648394493175476\n",
      "0.1805038389724909\n",
      "0.1977808370831048\n",
      "0.1303427005928282\n",
      "not the right data available\n",
      "0.0738030852610372\n",
      "0.1169097642189568\n",
      "0.3547100176087491\n",
      "0.1801950981483169\n",
      "0.1538513178224334\n",
      "0.1121503609256813\n",
      "0.1152127667969037\n",
      "0.0707032458849421\n",
      "0.1228744676600418\n",
      "0.0979561755183143\n",
      "0.0878038350762865\n",
      "0.2805418317440433\n",
      "0.1554505841822495\n",
      "0.0991589579712911\n",
      "0.2099405583004141\n",
      "0.1155880379924542\n",
      "0.1321036115457598\n",
      "0.1191011952950897\n",
      "0.0904686512676145\n",
      "0.1668632150618661\n",
      "0.1212542286932364\n",
      "0.1694748039993876\n",
      "0.1359706042926966\n",
      "0.0978427771031872\n",
      "0.1388509856432474\n",
      "0.174860913044598\n",
      "0.2444645896163986\n",
      "0.0627200833998038\n",
      "0.4922407439568781\n",
      "0.4362039923228049\n",
      "0.0686903977860792\n",
      "0.1440776527838004\n",
      "0.114448589541516\n",
      "0.1942408941471849\n",
      "0.4095527077149105\n",
      "0.0825185180945587\n",
      "0.2889880248052986\n",
      "0.2300271819992771\n",
      "0.2458934590182215\n",
      "0.4349543289655034\n",
      "0.288216293336536\n",
      "0.1768112648002172\n",
      "0.2457450894046842\n",
      "0.0788930659255878\n",
      "0.4608759627091696\n",
      "0.1036282806779676\n",
      "0.0996055554460238\n",
      "0.1128332508848856\n",
      "0.2384014529684051\n",
      "0.2639210966847187\n",
      "0.0701259122547337\n",
      "0.2146821062671647\n",
      "0.1542469943424887\n",
      "0.4038037835294398\n",
      "0.1931240808572427\n",
      "0.0976336154666827\n",
      "0.0621611554075429\n",
      "0.1621470956807931\n",
      "0.2178898394251521\n",
      "0.5248237964370028\n",
      "0.1980895200471163\n",
      "0.1717278551159667\n",
      "0.1408798939738612\n",
      "0.058436562080275\n",
      "0.2750199579001756\n",
      "0.1784558493570291\n",
      "0.0733318039622282\n",
      "0.1095649571180989\n",
      "0.1231213991640136\n",
      "0.12801940685023\n",
      "0.1006583839829314\n",
      "0.2685490410599883\n",
      "0.1591041438298699\n",
      "0.0788673045333201\n",
      "0.119907255251462\n",
      "0.1542239388368244\n",
      "0.2077029748862818\n",
      "0.112516346733876\n",
      "0.0877451338454806\n",
      "not the right data available\n",
      "0.1126488480222891\n",
      "0.4050381620772299\n",
      "0.0517970877858777\n",
      "0.1312472152184273\n",
      "0.1907921850936774\n",
      "0.1137795322037226\n",
      "0.0844487389663949\n",
      "0.1181548949767778\n",
      "0.3538572033326327\n",
      "not the right data available\n",
      "0.1830918259003668\n",
      "0.1585756022871811\n",
      "0.1483768143582107\n",
      "0.0568521860615849\n",
      "0.1194397826614946\n",
      "0.2278703712806439\n",
      "0.0645062757105562\n",
      "0.12412574513834\n",
      "0.2149116245983166\n",
      "0.3064779800894122\n",
      "0.0693768438112533\n",
      "0.1690356069345138\n",
      "0.238294514462109\n",
      "0.1637544481347324\n",
      "0.0977249777348225\n",
      "0.0574872860909905\n",
      "0.2949270194689121\n",
      "0.0842198074805144\n",
      "0.2004076449353323\n",
      "0.3698523259037651\n",
      "0.1301168311353124\n",
      "0.1562433100031071\n",
      "not the right data available\n",
      "0.5033689727732257\n",
      "0.145308252843654\n",
      "0.2443056964140614\n"
     ]
    }
   ],
   "source": [
    "\n",
    "# load the dataframe data_nowcasted_extended from csv file\n",
    "data_nowcasted_extended = pd.read_csv('./data/pip_all_data/data_nowcasted_extended.csv')\n",
    "# cast all numerical columns in data_nowcasted_extended to float\n",
    "data_nowcasted_extended = data_nowcasted_extended.astype({'mean': float, 'median': float, 'gini': float, 'decile1': float, 'decile2': float, 'decile3': float, 'decile4': float, 'decile5': float, 'decile6': float, 'decile7': float, 'decile8': float, 'decile9': float, 'decile10': float, 'gdp_pc_ppp_2017': float, 'population': float})\n",
    "# import the csv carbon_intensity.csv and store as a dataframe\n",
    "carbon_intensity = pd.read_csv('./data/pip_all_data/carbon_intensity.csv')\n",
    "# cast all numerical columns in carbon_intensity to float\n",
    "carbon_intensity = carbon_intensity.astype({'2010': float, '2011': float, '2012': float, '2013': float, '2014': float, '2015': float, '2016': float, '2017': float, '2018': float, '2019': float, '2020': float})\n",
    "print(carbon_intensity)\n",
    "# for all countries in data_nowcasted_extended find the corresponding value for carbon intensity in the dataframe carbon_intensity\n",
    "# for all countries in df carbon_intensity calculate the compound annual growth rate of the carbon intensity 2010-2020\n",
    "# for all countries in the column country_code in df carbon_intensity calculate the compound annual growth rate of the carbon intensity 2010-2020\n",
    "# store the compound annual growth rate together with the country code in a dictionary\n",
    "carbon_intensity_cagr = {}\n",
    "for country in carbon_intensity[\"Country Code\"]:\n",
    "    # check the value in the column 2010 and 2020 and calculate the compound annual growth rate\n",
    "    # check whether value for 2020 exists in carbon_intensity for this country\n",
    "    if not np.isnan(carbon_intensity[carbon_intensity['Country Code'] == country]['2020'].values[0]):\n",
    "        sub_data = carbon_intensity[carbon_intensity['Country Code'] == country]['2020'].values[0]\n",
    "        print(sub_data)\n",
    "        # if yes then store this value in data_nowcasted in the column gdp_pc_ppp\n",
    "        carbon_intensity_cagr[country] = float((sub_data / carbon_intensity[carbon_intensity['Country Code'] == country]['2010'].values[0])**(1/10) - 1)\n",
    "    else:\n",
    "        print(\"not the right data available\")\n",
    "\n",
    "# use this dictionary carbon_intensity_cagr to forecast the carbon intensity values for 2021 and 2022 in the carbon_intensity dataframe and store as a new dataframe\n",
    "for country in carbon_intensity_cagr:\n",
    "    # check whether value for 2020 exists in carbon_intensity for this country\n",
    "    if not np.isnan(carbon_intensity[carbon_intensity['Country Code'] == country]['2020'].values[0]):\n",
    "        sub_data = carbon_intensity[carbon_intensity['Country Code'] == country]['2020'].values[0]\n",
    "        print(sub_data)\n",
    "        # if yes then store this value in data_nowcasted in the column gdp_pc_ppp\n",
    "        carbon_intensity.loc[carbon_intensity['Country Code'] == country, '2021'] = sub_data * (1 + carbon_intensity_cagr[country])\n",
    "        carbon_intensity.loc[carbon_intensity['Country Code'] == country, '2022'] = sub_data * (1 + carbon_intensity_cagr[country])**2\n",
    "    else:\n",
    "        print(\"not the right data available\")\n",
    "\n",
    "#store the carbon_intensity  now as carbon_intensity_nowcasted.csv\n",
    "carbon_intensity.to_csv('./data/pip_all_data/carbon_intensity_nowcasted.csv')\n",
    "\n",
    "# import the csv carbon_intensity_nowcasted.csv' and store as a dataframe\n",
    "# make new column carbon_intensity in data_nowcasted_extended and fill with the values from carbon_intensity_nowcasted.csv for 2022\n",
    "carbon_intensity = pd.read_csv('./data/pip_all_data/carbon_intensity_nowcasted.csv')\n",
    "data_nowcasted_extended['carbon_intensity'] = 0.0\n",
    "# add another column to data_nowcasted_extended called carbon_intensity_trend\n",
    "data_nowcasted_extended['carbon_intensity_trend'] = 0.0\n",
    "\n",
    "\n",
    "# iterate over all countries in data_nowcasted_extended and find the corresponding value for carbon intensity in the dataframe carbon_intensity\n",
    "for country in data_nowcasted_extended['country_code']:\n",
    "    # check whether value for 2022 exists in carbon_intensity for this country\n",
    "    if not np.isnan(carbon_intensity[carbon_intensity['Country Code'] == country]['2022'].values[0]):\n",
    "        sub_data = float(carbon_intensity[carbon_intensity['Country Code'] == country]['2022'].values[0])\n",
    "        print(sub_data)\n",
    "        # if yes then store this value in data_nowcasted in the column gdp_pc_ppp\n",
    "        data_nowcasted_extended.loc[data_nowcasted_extended['country_code'] == country, 'carbon_intensity'] = sub_data\n",
    "        # set the values in the df data_nowcasted_extended in the column carbon_intensity_trend to the values from the df carbon_intensity_cagr\n",
    "        data_nowcasted_extended.loc[data_nowcasted_extended['country_code'] == country, 'carbon_intensity_trend'] = carbon_intensity_cagr[country]\n",
    "    else:\n",
    "        print(\"not the right data available\")\n",
    "\n",
    "\n",
    "# save again the dataframe data_nowcasted_extended as a csv file\n",
    "data_nowcasted_extended.to_csv('./data/pip_all_data/data_nowcasted_extended.csv')\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [],
   "source": [
    "# set extra countries in terms of carbon intensities for which methods above do not work\n",
    "# South Sudan from OWID https://ourworldindata.org/co2/country/south-sudan\n",
    "# set south sudan carbon intensity in data_nowcasted_extended to 0.22\n",
    "data_nowcasted_extended.loc[data_nowcasted_extended['country_code'] == 'SSD', 'carbon_intensity'] = 0.22\n",
    "# set the values in the df data_nowcasted_extended in the column carbon_intensity_trend to 0.1% increase per year\n",
    "data_nowcasted_extended.loc[data_nowcasted_extended['country_code'] == 'SSD', 'carbon_intensity_trend'] = 0.001\n",
    "\n",
    "# delete dijbouti from data_nowcasted_extended since there is not enough data available and it is rather small\n",
    "data_nowcasted_extended = data_nowcasted_extended[data_nowcasted_extended['country_code'] != 'DJI']\n",
    "\n",
    "# for kosovo use the values from serbia since kosovo is not recognised by the world bank and there is not enough data\n",
    "data_nowcasted_extended.loc[data_nowcasted_extended['country_code'] == 'XKX', 'carbon_intensity'] = data_nowcasted_extended.loc[data_nowcasted_extended['country_code'] == 'SRB', 'carbon_intensity'].values[0]\n",
    "data_nowcasted_extended.loc[data_nowcasted_extended['country_code'] == 'XKX', 'carbon_intensity_trend'] = data_nowcasted_extended.loc[data_nowcasted_extended['country_code'] == 'SRB', 'carbon_intensity_trend'].values[0]\n",
    "\n",
    "# for west bank and gaza use values from lebanon since there is not enough data available even though this is not a perfect match\n",
    "data_nowcasted_extended.loc[data_nowcasted_extended['country_code'] == 'PSE', 'carbon_intensity'] = data_nowcasted_extended.loc[data_nowcasted_extended['country_code'] == 'LBN', 'carbon_intensity'].values[0]\n",
    "data_nowcasted_extended.loc[data_nowcasted_extended['country_code'] == 'PSE', 'carbon_intensity_trend'] = data_nowcasted_extended.loc[data_nowcasted_extended['country_code'] == 'LBN', 'carbon_intensity_trend'].values[0]\n",
    "\n",
    "# save again the dataframe data_nowcasted_extended as a csv file\n",
    "data_nowcasted_extended.to_csv('./data/pip_all_data/data_nowcasted_extended.csv')\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Compute the absolute decile mean household consumption expenditure or income values"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [],
   "source": [
    "# add ten more columns to the data_nowcasted_extended dataframe and call them decile1_abs, decile2_abs, decile3_abs, decile4_abs, decile5_abs, decile6_abs, decile7_abs, decile8_abs, decile9_abs, decile10_abs\n",
    "# and fill them with the absolute mean household incomes per decile which can be calculated via the formula decile1_abs = (mean*365)*population*decile1*(population/10) where mean and population and decile1 are columns in the dataframe \n",
    "\n",
    "# Define a function to calculate the absolute mean household income for a decile\n",
    "def calculate_decile_abs(row, decile_number):\n",
    "    mean = row['mean']\n",
    "    population = row['population']\n",
    "    decile = row[f'decile{decile_number}']\n",
    "    return (mean * 365) * decile * 10\n",
    "\n",
    "# Loop through each decile to add the new columns and calculate their values\n",
    "for i in range(1, 11):\n",
    "    decile_col = f'decile{i}'\n",
    "    abs_decile_col = f'{decile_col}_abs'\n",
    "    \n",
    "    # Ensure the decile column exists in the DataFrame\n",
    "    if decile_col not in data_nowcasted_extended.columns:\n",
    "        raise KeyError(f\"The decile column '{decile_col}' does not exist in the DataFrame.\")\n",
    "    \n",
    "    # Apply the calculation to each row\n",
    "    data_nowcasted_extended[abs_decile_col] = data_nowcasted_extended.apply(calculate_decile_abs, axis=1, decile_number=i)\n",
    "\n",
    "\n",
    "# make sure the first column in data_nowcasted_extended is called index and contains the row numbers\n",
    "data_nowcasted_extended.index.name = 'index'\n",
    "\n",
    "# delete the second column\n",
    "data_nowcasted_extended = data_nowcasted_extended.drop(data_nowcasted_extended.columns[[0]], axis=1)\n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Lastly also add a column on the GDP to mean hh income ratio in 2022 as this is a crucial model parameter\n",
    "#### it is used later on for estimating the size of the entire economy needed to support hh cons. and so important for estimating emissions too"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [],
   "source": [
    "# add another column to data_nowcasted_extended called gdp_to_mean_hh_income_ratio and fill with the values from the column mean divided by the column gdp_pc_ppp_2017\n",
    "data_nowcasted_extended['gdp_to_mean_hh_income_ratio'] = data_nowcasted_extended['mean']*365 / data_nowcasted_extended['gdp_pc_ppp_2017'] # times 365 to get annual income\n",
    "\n",
    "\n",
    "# save again the dataframe data_nowcasted_extended as a csv file\n",
    "data_nowcasted_extended.to_csv('./data/pip_all_data/data_nowcasted_extended.csv')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Add another column to data_now_casted_extended for total emissions (production-based) by country"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Add another column to data_now_casted_extended for total emissions (production-based) by country by multiplying carbon intensity with GDP per capita and population and convert to tonnes\n",
    "data_nowcasted_extended['total_emissions'] = data_nowcasted_extended['gdp_pc_ppp_2017'] * data_nowcasted_extended['carbon_intensity'] * data_nowcasted_extended['population'] / 1e3\n",
    "\n",
    "# Save the updated data_nowcasted_extended dataframe as a csv file\n",
    "data_nowcasted_extended.to_csv('./data/pip_all_data/data_nowcasted_extended.csv')\n",
    "\n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Add another column to data_nowcasted_extended for historic growth trend"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "AGO\n",
      "ALB\n",
      "ARE\n",
      "ARM\n",
      "AUS\n",
      "AUT\n",
      "BDI\n",
      "BEL\n",
      "BEN\n",
      "BFA\n",
      "BGD\n",
      "BGR\n",
      "BIH\n",
      "BLR\n",
      "BOL\n",
      "BRA\n",
      "BTN\n",
      "BWA\n",
      "CAF\n",
      "CAN\n",
      "CHE\n",
      "CHL\n",
      "CHN\n",
      "CIV\n",
      "CMR\n",
      "COD\n",
      "COG\n",
      "COL\n",
      "COM\n",
      "CPV\n",
      "CRI\n",
      "CYP\n",
      "CZE\n",
      "DEU\n",
      "DNK\n",
      "DOM\n",
      "DZA\n",
      "ECU\n",
      "EGY\n",
      "ESP\n",
      "EST\n",
      "ETH\n",
      "FIN\n",
      "FJI\n",
      "FRA\n",
      "FSM\n",
      "GAB\n",
      "GBR\n",
      "GEO\n",
      "GHA\n",
      "GIN\n",
      "GMB\n",
      "GNB\n",
      "GRC\n",
      "GTM\n",
      "HND\n",
      "HRV\n",
      "HTI\n",
      "HUN\n",
      "IDN\n",
      "IND\n",
      "IRL\n",
      "IRN\n",
      "IRQ\n",
      "ISL\n",
      "ISR\n",
      "ITA\n",
      "JPN\n",
      "KAZ\n",
      "KEN\n",
      "KGZ\n",
      "KIR\n",
      "KOR\n",
      "LAO\n",
      "LBN\n",
      "LBR\n",
      "LCA\n",
      "LKA\n",
      "LSO\n",
      "LTU\n",
      "LUX\n",
      "LVA\n",
      "MAR\n",
      "MDA\n",
      "MDG\n",
      "MDV\n",
      "MEX\n",
      "MHL\n",
      "MKD\n",
      "MLI\n",
      "MLT\n",
      "MMR\n",
      "MNE\n",
      "MNG\n",
      "MOZ\n",
      "MRT\n",
      "MUS\n",
      "MWI\n",
      "MYS\n",
      "NAM\n",
      "NER\n",
      "NGA\n",
      "NIC\n",
      "NLD\n",
      "NOR\n",
      "NRU\n",
      "PAK\n",
      "PAN\n",
      "PER\n",
      "PHL\n",
      "POL\n",
      "PRT\n",
      "PRY\n",
      "PSE\n",
      "ROU\n",
      "RUS\n",
      "RWA\n",
      "SDN\n",
      "SEN\n",
      "SLB\n",
      "SLE\n",
      "SLV\n",
      "SRB\n",
      "SSD\n",
      "STP\n",
      "SVK\n",
      "SVN\n",
      "SWE\n",
      "SWZ\n",
      "SYC\n",
      "TCD\n",
      "TGO\n",
      "THA\n",
      "TJK\n",
      "TLS\n",
      "TON\n",
      "TUN\n",
      "TUR\n",
      "TZA\n",
      "UGA\n",
      "UKR\n",
      "URY\n",
      "USA\n",
      "VNM\n",
      "VUT\n",
      "WSM\n",
      "XKX\n",
      "ZAF\n",
      "ZMB\n",
      "ZWE\n"
     ]
    }
   ],
   "source": [
    "# import gdp_pc_empirical_trend.csv and store as a dataframe\n",
    "gdp_pc_empirical_trend = pd.read_csv('./data/pip_all_data/gdp_pc_empirical_trend.csv', sep=\";\")\n",
    "# add the column growth_trend_2012_to_2022 to data_nowcasted_extended and fill with the values from the column gdp_pc_empirical_trend\n",
    "data_nowcasted_extended['growth_trend_2012_to_2022'] = 0.0\n",
    "# iterate over all countries in data_nowcasted_extended and find the corresponding value for gdp_pc_empirical_trend in the dataframe gdp_pc_empirical_trend\n",
    "for country in data_nowcasted_extended['country_code']:\n",
    "        print(country)\n",
    "        sub_data = gdp_pc_empirical_trend[gdp_pc_empirical_trend['country_code'] == country].values[0][1]\n",
    "        # if yes then store this value in data_nowcasted in the column gdp_pc_ppp\n",
    "        data_nowcasted_extended.loc[data_nowcasted_extended['country_code'] == country, 'growth_trend_2012_to_2022'] = sub_data\n",
    "\n",
    "# save again the dataframe data_nowcasted_extended as a csv file\n",
    "data_nowcasted_extended.to_csv('./data/pip_all_data/data_nowcasted_extended.csv')\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## We also need to cast an entirely different dataframes that contains the population projections from the UN\n",
    "#### we just use the medium variant for all countries, that is it"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "           2022        2023        2024        2025        2026        2027  \\\n",
      "AGO  35050000.0  36128000.0  37240000.0  38369000.0  39516000.0  40683000.0   \n",
      "ALB   2848000.0   2837000.0   2828000.0   2824000.0   2819000.0   2814000.0   \n",
      "ARE   9403000.0   9479000.0   9555000.0   9629000.0   9702000.0   9772000.0   \n",
      "ARM   2783000.0   2778000.0   2778000.0   2778000.0   2776000.0   2774000.0   \n",
      "AUS  26046000.0  26309000.0  26570000.0  26829000.0  27087000.0  27341000.0   \n",
      "\n",
      "           2028        2029        2030        2031  ...         2091  \\\n",
      "AGO  41867000.0  43071000.0  44291000.0  45532000.0  ...  124665000.0   \n",
      "ALB   2808000.0   2801000.0   2794000.0   2785000.0  ...    1366000.0   \n",
      "ARE   9841000.0   9908000.0   9973000.0  10036000.0  ...   13451000.0   \n",
      "ARM   2771000.0   2767000.0   2762000.0   2757000.0  ...    1790000.0   \n",
      "AUS  27593000.0  27841000.0  28084000.0  28321000.0  ...   37200000.0   \n",
      "\n",
      "            2092         2093         2094         2095         2096  \\\n",
      "AGO  125629000.0  126572000.0  127495000.0  128392000.0  129268000.0   \n",
      "ALB    1336000.0    1306000.0    1277000.0    1248000.0    1220000.0   \n",
      "ARE   13517000.0   13583000.0   13650000.0   13718000.0   13786000.0   \n",
      "ARM    1770000.0    1749000.0    1729000.0    1709000.0    1689000.0   \n",
      "AUS   37288000.0   37378000.0   37470000.0   37563000.0   37657000.0   \n",
      "\n",
      "            2097         2098         2099         2100  \n",
      "AGO  130120000.0  130943000.0  131742000.0  132522000.0  \n",
      "ALB    1192000.0    1164000.0    1137000.0    1110000.0  \n",
      "ARE   13853000.0   13920000.0   13988000.0   14055000.0  \n",
      "ARM    1669000.0    1649000.0    1629000.0    1609000.0  \n",
      "AUS   37753000.0   37849000.0   37944000.0   38041000.0  \n",
      "\n",
      "[5 rows x 79 columns]\n"
     ]
    }
   ],
   "source": [
    "# Load the CSV file\n",
    "un_pop_projections = pd.read_csv('./data/pip_all_data/un_pop_projections.csv', sep=';', encoding='latin1')\n",
    "\n",
    "# Remove rows where 'iso3_code' is NaN\n",
    "un_pop_projections = un_pop_projections[un_pop_projections['iso3_code'].notna()]\n",
    "\n",
    "# Ensure 'year' is numeric, convert to integer, and filter out invalid years\n",
    "un_pop_projections['year'] = pd.to_numeric(un_pop_projections['year'], errors='coerce')\n",
    "un_pop_projections = un_pop_projections.dropna(subset=['year'])  # Drop rows where year conversion failed\n",
    "un_pop_projections['year'] = un_pop_projections['year'].astype(int)\n",
    "un_pop_projections = un_pop_projections[(un_pop_projections['year'] >= 2022) & (un_pop_projections['year'] <= 2100)]\n",
    "\n",
    "# Assuming data_nowcasted_extended is already defined and contains a 'country_code' column\n",
    "population_projections = pd.DataFrame(index=data_nowcasted_extended['country_code'].unique())\n",
    "years = list(range(2022, 2101))\n",
    "population_projections = population_projections.reindex(columns=years, fill_value=0.0)\n",
    "\n",
    "# Loop over all countries and years to update population projections\n",
    "for country in population_projections.index:\n",
    "    for year in years:\n",
    "        # Find matching rows for current country and year\n",
    "        sub_data = un_pop_projections[(un_pop_projections['iso3_code'] == country) & (un_pop_projections['year'] == year)]\n",
    "        if not sub_data.empty:\n",
    "            # Update population value using the 'total_pop' column\n",
    "            population_value = float(sub_data['total_pop'].values[0])\n",
    "            population_projections.at[country, year] = population_value\n",
    "\n",
    "# Display or further process the population_projections DataFrame as needed\n",
    "print(population_projections.head())\n",
    "# store the dataframe population_projections as a csv file\n",
    "population_projections.to_csv('./data/pip_all_data/population_projections.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "image/png": "iVBORw0KGgoAAAANSUhEUgAAAjcAAAHHCAYAAABDUnkqAAAAOXRFWHRTb2Z0d2FyZQBNYXRwbG90bGliIHZlcnNpb24zLjguMiwgaHR0cHM6Ly9tYXRwbG90bGliLm9yZy8g+/7EAAAACXBIWXMAAA9hAAAPYQGoP6dpAABfb0lEQVR4nO3deVhU5d8G8HsYYNj3XRZZFBREULPUSi2V3LXdLPefmZa5pGXlnpmZZvlmm7m22Gqa5r7mvi8ooCgIsojIMuwwM8/7Bzk1gcrgwBmG+3Ndc+mcOcv3mTMwN+c85zwyIYQAERERkYkwk7oAIiIiIkNiuCEiIiKTwnBDREREJoXhhoiIiEwKww0RERGZFIYbIiIiMikMN0RERGRSGG6IiIjIpDDcEBERkUlhuCEyUnv37oVMJsPevXulLuW+denSBV26dDHoOmfNmgWZTGbQdRobKdsok8kwa9YsSbZNdL8Yboj+RSaT1ehRk8Dx/vvv4/fff6/zmletWqVTm5WVFZo3b45XX30VN27cqPPt16Xi4mLMmjXL6ALev99vMzMz+Pj4oEePHkZX5738+eefDDBkkmQcW4roH99++63O8zVr1mDHjh1Yu3atzvTu3bvD09Pzruuys7PD008/jVWrVtWqlr1796Jr167Ys2fPXY96rFq1CsOHD8ecOXMQGBiI0tJSHDhwAGvXrkVAQABiY2NhY2NTqxoM5Xb9+n75Z2dnw93dHTNnzqzyJaxSqaBSqWBlZWWYIvUgk8nQvXt3DBkyBEIIJCUlYdmyZcjKysLmzZvRs2dPg2ynrtv46quv4rPPPkN1XwOlpaUwNzeHubl5nWybqC7xU0v0Ly+++KLO8yNHjmDHjh1Vphujnj17ol27dgCAUaNGwdXVFYsXL8aGDRswaNAgiaszPKm/eJs3b67zuRg4cCAiIyOxZMmSO4ab0tJSWFpawsysZgfNpWyjFKGRyFB4WopIT0VFRZg8eTL8/PygUCgQGhqKjz76SOevX5lMhqKiIqxevVp7+mLYsGEAgGvXrmHs2LEIDQ2FtbU1XF1d8cwzzyA5OdmgdT722GMAgKSkJACVRwHmzp2L4OBgKBQKNG3aFG+//TbKysp0lmvatCn69OmD7du3IyoqClZWVmjZsiV+++03nfnu1B/k9mmyu7WnvLwcM2bMQNu2beHo6AhbW1s88sgj2LNnj3ae5ORkuLu7AwBmz56tfR9vH8Gpbvv6tvHAgQNo3749rKysEBQUhDVr1tzlHb27Vq1awc3NTft+3+4ztW7dOrz77rto0qQJbGxsoFQqAQA///wz2rZtC2tra7i5ueHFF19EWlqazjrv9B5/++232mVdXFzw/PPPIzU1tcp8R48eRa9eveDs7AxbW1tERkbik08+AQAMGzYMn332GQDd02y3Vdfn5vTp0+jZsyccHBxgZ2eHxx9/HEeOHNGZ5/b+P3jwICZNmgR3d3fY2tpi4MCBuHnzps68J06cQExMDNzc3GBtbY3AwECMGDGiJm830V3xyA2RHoQQ6NevH/bs2YORI0ciKioK27Ztw5QpU5CWloaPP/4YALB27VqMGjUK7du3x+jRowEAwcHBAIDjx4/j0KFDeP755+Hr64vk5GR8/vnn6NKlCy5evGiwU0hXrlwBALi6ugKoPJqzevVqPP3005g8eTKOHj2K+fPnIy4uDuvXr9dZ9vLly3juuecwZswYDB06FCtXrsQzzzyDrVu3onv37vddm1KpxPLlyzFo0CD873//Q0FBAb755hvExMTg2LFjiIqKgru7Oz7//HO88sorGDhwIJ588kkAQGRk5B3Xq08bExMT8fTTT2PkyJEYOnQoVqxYgWHDhqFt27YIDw/Xu025ubnIzc1FSEiIzvS5c+fC0tISb7zxBsrKymBpaak9lfjAAw9g/vz5uHHjBj755BMcPHgQp0+fhpOT0x23M2/ePEyfPh3PPvssRo0ahZs3b2Lp0qV49NFHdZbdsWMH+vTpA29vb7z++uvw8vJCXFwcNm3ahNdffx0vv/wy0tPTqz3tWp0LFy7gkUcegYODA6ZOnQoLCwt8+eWX6NKlC/bt24cHH3xQZ/7XXnsNzs7OmDlzJpKTk7FkyRK8+uqr+PHHHwEAWVlZ6NGjB9zd3fHWW2/ByckJycnJVUI0Ua0IIrqjcePGiX//mPz+++8CgHjvvfd05nv66aeFTCYTiYmJ2mm2trZi6NChVdZZXFxcZdrhw4cFALFmzRrttD179ggAYs+ePXetceXKlQKA2Llzp7h586ZITU0V69atE66ursLa2lpcv35dnDlzRgAQo0aN0ln2jTfeEADE7t27tdMCAgIEAPHrr79qp+Xn5wtvb28RHR2tnTZz5kxR3a+Q2/UkJSVpp3Xu3Fl07txZ+1ylUomysjKd5XJzc4Wnp6cYMWKEdtrNmzcFADFz5swq2/nv9mvTxv3792unZWVlCYVCISZPnlxlW/8FQIwcOVLcvHlTZGVliaNHj4rHH39cABCLFi0SQvyz/4KCgnT2eXl5ufDw8BARERGipKREO33Tpk0CgJgxY8Yd25icnCzkcrmYN2+eTj3nz58X5ubm2ukqlUoEBgaKgIAAkZubqzOvRqPR/v+/n+//tvHf7/uAAQOEpaWluHLlinZaenq6sLe3F48++qh22u39361bN51tTZw4UcjlcpGXlyeEEGL9+vUCgDh+/Hi12ye6HzwtRaSHP//8E3K5HOPHj9eZPnnyZAghsGXLlnuuw9raWvv/iooK3Lp1CyEhIXBycsKpU6dqXVu3bt3g7u4OPz8/PP/887Czs8P69evRpEkT/PnnnwCASZMmVakbADZv3qwz3cfHBwMHDtQ+d3BwwJAhQ3D69GlkZmbWusbb5HI5LC0tAQAajQY5OTlQqVRo165drd8DfdvYsmVLPPLII9rn7u7uCA0NxdWrV2u0vW+++Qbu7u7w8PDAgw8+qD0NM2HCBJ35hg4dqrPPT5w4gaysLIwdO1anX0vv3r0RFhZWpc5/++2336DRaPDss88iOztb+/Dy8kKzZs20p/VOnz6NpKQkTJgwocpRoNpcWq5Wq7F9+3YMGDAAQUFB2une3t544YUXcODAAe3ptttGjx6ts61HHnkEarUa165dAwBtXZs2bUJFRYXeNRHdTaMON/v370ffvn3h4+MDmUxWq8t2f/rpJ0RFRcHGxgYBAQFYuHCh4Qslo3Ht2jX4+PjA3t5eZ3qLFi20r99LSUkJZsyYoe2z4+bmBnd3d+Tl5SE/P7/WtX322WfYsWMH9uzZg4sXL+Lq1auIiYnR1mVmZlbllImXlxecnJyq1B0SElLlS7B58+YAYLC+QatXr0ZkZCSsrKzg6uoKd3d3bN68udbvgb5t9Pf3r7IOZ2dn5Obm1mh7/fv3x44dO7Bz504cPXoU2dnZWLRoUZXOwoGBgVXqBIDQ0NAq6wwLC7vrZ+jy5csQQqBZs2Zwd3fXecTFxSErKwvAP6ckIyIiatSWe7l58yaKi4urrblFixbQaDRV+vz89/11dnYGAO3727lzZzz11FOYPXs23Nzc0L9/f6xcubJK/yii2mjUfW6KiorQunVrjBgxQns+Xx9btmzB4MGDsXTpUvTo0QNxcXH43//+B2tra7z66qt1UDGZgtdeew0rV67EhAkT0KFDBzg6OkImk+H555+HRqOp9Xrbt2+vvVrqTgx5Q7g7rUutVt9z2W+//RbDhg3DgAEDMGXKFHh4eEAul2P+/PnaL2ZD1/Vfcrm82umihnfH8PX1Rbdu3e4537+P2twvjUYDmUyGLVu2VFu/nZ2dwbZ1v+71/spkMvzyyy84cuQI/vjjD2zbtg0jRozAokWLcOTIEaNqCzU8jTrc9OzZ8673oygrK8M777yDH374AXl5eYiIiMCCBQu09+xYu3YtBgwYgDFjxgAAgoKCMG3aNCxYsADjxo0z+bunNkYBAQHYuXMnCgoKdI7exMfHa1+/7U77/5dffsHQoUOxaNEi7bTS0lLk5eXVTdF/16XRaHD58mXtUSYAuHHjBvLy8nTqBio72wohdNpw6dIlAJVXGgH//CWel5enc+qjJkevfvnlFwQFBeG3337T2cbMmTN15tPnZ0jfNkrldh0JCQnaK9puS0hIuGudwcHBEEIgMDBQeyTtTvMBQGxs7F0DWE3fX3d3d9jY2CAhIaHKa/Hx8TAzM4Ofn1+N1vVfDz30EB566CHMmzcP33//PQYPHox169Zh1KhRtVofEdDIT0vdy6uvvorDhw9j3bp1OHfuHJ555hk88cQTuHz5MoDK8PPfe0FYW1vj+vXrNfoFTw1Pr169oFar8X//93860z/++GPIZDKdsGxra1ttYJHL5VWODixdurRGRzxqq1evXgCAJUuW6ExfvHgxgMr+Hv+Wnp6uc3WRUqnEmjVrEBUVBS8vLwD/fIHu379fO9/ty9/v5fZf9f9+H44ePYrDhw/rzHf7yrGaBD992yiVdu3awcPDA1988YXOKZgtW7YgLi7urnU++eSTkMvlmD17dpXPkBACt27dAgC0adMGgYGBWLJkSZX37t/L2draArj3+yuXy9GjRw9s2LBB57TkjRs38P333+Phhx+Gg4PDXdfxX7m5uVXaEBUVBQA8NUX3rVEfubmblJQUrFy5EikpKfDx8QEAvPHGG9i6dStWrlyJ999/HzExMZg4cSKGDRuGrl27IjExUfvXeEZGhvYvXDIdffv2RdeuXfHOO+8gOTkZrVu3xvbt27FhwwZMmDBB+4UPAG3btsXOnTuxePFi+Pj4IDAwEA8++CD69OmDtWvXwtHRES1btsThw4exc+dO7SXbdaF169YYOnQovvrqK+Tl5aFz5844duwYVq9ejQEDBqBr16468zdv3hwjR47E8ePH4enpiRUrVuDGjRtYuXKldp4ePXrA398fI0eOxJQpUyCXy7FixQq4u7sjJSXlrvX06dMHv/32GwYOHIjevXsjKSkJX3zxBVq2bInCwkLtfNbW1mjZsiV+/PFHNG/eHC4uLoiIiKi2L4m+bZSKhYUFFixYgOHDh6Nz584YNGiQ9lLwpk2bYuLEiXdcNjg4GO+99x6mTZuG5ORkDBgwAPb29khKSsL69esxevRovPHGGzAzM8Pnn3+Ovn37IioqCsOHD4e3tzfi4+Nx4cIFbNu2DUDlZxQAxo8fj5iYGMjlcjz//PPVbvu9997Djh078PDDD2Ps2LEwNzfHl19+ibKyMnz44Yd6vw+rV6/GsmXLMHDgQAQHB6OgoABff/01HBwctEGVqNakuUjL+AAQ69ev1z6/fVmmra2tzsPc3Fw8++yzQojKSyqnTp0qrKyshFwuF87OzmLWrFkCgDhy5IhELSFDqu5S2YKCAjFx4kTh4+MjLCwsRLNmzcTChQt1LnsVQoj4+Hjx6KOPCmtrawFAe1l4bm6uGD58uHBzcxN2dnYiJiZGxMfHi4CAAJ1Lx/W9FPxel9RWVFSI2bNni8DAQGFhYSH8/PzEtGnTRGlpqc58AQEBonfv3mLbtm0iMjJSKBQKERYWJn7++ecq6zx58qR48MEHhaWlpfD39xeLFy+u0aXgGo1GvP/++yIgIEAoFAoRHR0tNm3aJIYOHSoCAgJ0tnHo0CHRtm1bYWlpqXN5cnWXouvbxv/6b513AkCMGzfurvPc3n/VvW9CCPHjjz+K6OhooVAohIuLixg8eLC4fv26zjx3utz+119/FQ8//LD291JYWJgYN26cSEhI0JnvwIEDonv37sLe3l7Y2tqKyMhIsXTpUu3rKpVKvPbaa8Ld3V3IZDKdbQFVL8E/deqUiImJEXZ2dsLGxkZ07dpVHDp0SGeeO30e//t5PnXqlBg0aJDw9/cXCoVCeHh4iD59+ogTJ05U/4YS6YFjS/1NJpNh/fr1GDBgAADgxx9/xODBg3HhwoUqHePs7Oy0h+aByg6UmZmZcHd3x65du9CrVy9kZWVp765K1JA0bdoUERER2LRpk9SlNHrTp0/H/PnzoVKppC6FqEHhaak7iI6OhlqtRlZWls69MKojl8vRpEkTAMAPP/yADh06MNgQ0X3LyMiAm5ub1GUQNTiNOtwUFhYiMTFR+zwpKQlnzpyBi4sLmjdvjsGDB2PIkCFYtGgRoqOjcfPmTezatQuRkZHo3bs3srOz8csvv6BLly4oLS3FypUr8fPPP2Pfvn0StoqIGrqrV69i/fr1+Pnnn9GnTx+pyyFqcBr11VInTpxAdHQ0oqOjAVTe2TQ6OhozZswAAKxcuRJDhgzB5MmTERoaigEDBuD48eM6N6davXo12rVrh06dOuHChQvYu3cv2rdvL0l7iMg07N+/H7Nnz0bnzp21V3sRUc2xzw0RERGZlEZ95IaIiIhMD8MNERERmZRG16FYo9EgPT0d9vb2HB6BiIiogRBCoKCgAD4+PlUGqP2vRhdu0tPTaz0GChEREUkrNTUVvr6+d52n0YWb24Mdpqam6j0WChEREUlDqVTCz89PZ9DiO2l04eb2qSgHBweGGyIiogamJl1K2KGYiIiITArDDREREZkUhhsiIiIyKQw3REREZFIYboiIiMikMNwQERGRSWG4ISIiIpPCcENEREQmheGGiIiITArDDREREZkUhhsiIiIyKQw3REREZFIa3cCZRETUOAkhoNIIlKk0KFdpoNJoIAQgBKARAuLvecxkMpjLZbAwM4P873/N5TKYm8lqNGgjSY/hhoiIGgSNRiCnuBw3C8qQW1SOnOJy5BZXIK/o739LylFYqkJRuQqFZWoUlalQWKpCcbkK5WoNylSVYaa25GYyWJmbwdpSDoW5HNaWclhbyGGrkMNOYQF7K3PYKcxhZ2UOeytzOFlbwtnGAk42lnC2tYCzjSVcbC1hIedJk7rGcENERJITQiC7sBzXc4txPbfk70cxbihLkVVQhixlGbILy6DS3Ec6+Q+ZDDCTySD7+/+yv/+vEQIV6qrbUWsEisrVKCpX39d2nWws4G6ngLu9Am5//+vlYAVvJyt4O1rDx8kKHvZWkJvxKFFtMdwQEVG9yS+uwJXsQly9WYSrN//+N7sQ124Vo0ylqdE6XG0rj4A421jCyabyiIjT30dG7BSVR09s//7XTmH+95EWMygszKCQy6GwMIOl3Axm9wgPao2ASqOBSi1Q8feRn9IKNUoq1Cit0KCkXI2SisqjRIWlKhSVqVDw99EiZWkF8v4+spRbXI684srnGoG//1+By1mFd9y23EwGT3sFfF1s4OdsAz8X67//tYG/iw08HRQ8RXYXDDdERGRw5SoNrtwsRHymEvEZBYjPLEB8phI3lGV3XEYmA7wdrODrbIMmztbwdbaGl2PlUQwPewU8HCqPdNTXaR25mQxyMzkUBvqm1GgE8ksqcLOwDDcL/vUoLENGfiky8kqQkV+KTGUp1BqB9PxSpOeX4lhSTpV12VjK0dTVFkHutghys0Wguy2C3e0Q7G4HW0MV3IDxHSAiovuiUmtwOasQ567n4ez1fJy7noeEzIJqT+0AgJeDVeWXsrstgtzsEORui0A3W3g7WsPS3HT7o5iZyeBsawlnW0s097S/43xqjUB2YRnS8kqQmlN5mi41pxipucVIzSlBWl4JisvVuJihxMUMZZXlmzhZI8TDDs087NDM0w7NPe0R5uUAa0t5XTbPqMiEuJ/uVQ2PUqmEo6Mj8vPz4eDgIHU5REQNjrK0Aiev5eJ4Ug5OJOfifFo+Siqq9kNxsDJHmJcDwrwrv1xDvewR6mUPOx5ZuC8Vag1ScoqRdLMISdmVp/Wu3CzC1ZtFyC6s/siYmQxo6maLFt4OaPn3I6KJI9ztFfVcfe3p8/3NcENERHeVV1yOw1du4cjVWziWnIv4TGWVq47sFOaIaOKA1r5OaO3nhFZNHOHrbM1+IfUst6gciTcLcflGIS5nFeDyjcpTg9mF5dXO7+1ohVZNHBHp64hWvpX7zcXWsp6rrhmGm7tguCEiursylRonr+XiwOVsHEzMxrm0/CphJsDVBg80dUH7pi5oE+CEIDe7e3bQJelkFZTiYroScRkFiPv7dNaVm4XVXhrf1NUGbfydER3gjDb+Tgj1tIe5EVy+znBzFww3RERVpeeVYHd8FnbHZ+HQlWyUVuheudTMww4dg13xQGBloPFwsJKoUjKUwjIVLqYrce56Hs6n5eP89XxczS6qMp+NpRxRfk5oH+iC9oEuiPZzlqT/DsPNXTDcEBFVXrlz9noedsVlYVd8FuL+0zHV3V6Bh0Pc8HCIGzqFuMHLkWGmMcgvrsDp1FycSsnDqWu5OJOah8Iylc48FnIZIn0rw86DgS54oKlLvVyhxXBzFww3RNRYqTUCx5JysCU2A9suZOpclm0mA9r4O+OxFh54LMwDoZ727C9DUGsELmcV4HhyZQfyo0m3qlzOb24mQ5SfEzoGu6JjiBui/Z2gMDf8kR2Gm7tguCGixkSl1uDw1Vv483wmdlzM1OlYaq8wx6Oh7ujWwgOdm3sYbUdSMh5CCKTmlOBo0i0cS8rB4au3cD23RGcehbkZOjd3x1dD2hl02/p8f/N6PCIiEyOEwOnUPGw8k45N59J1Ao2TjQW6t/BEr1be6BjiWid/YZPpkslk8He1gb+rDZ5p5wcASM0pxqEr2Th05RYOXbmFmwVlNb7bdF1huCEiMhGJWYXYcCYNG86kIyWnWDvd2cYCT0R4o1crLzwU5MqBG8mg/Fxs8JyLP557wB9CCCRmFTLcEBFR7eWXVOCPs+n45eR1nEnN0063sZSjR0tP9I9ugodD3BhoqF7IZDI0u8vdl+sLww0RUQOj1ggcSMzGLyevY9uFTJT//Vey3EyGzs3d0T/KB91besLGkr/iqXHiJ5+IqIHIzC/Fj8dT8ePxFKTnl2qnh3ra45l2vugf1aRB3U6fqK4w3BARGTG1RmD/5Zv4/mgKdsdnQa2pvMDV0doC/aN88ExbP0Q0ceBl20T/wnBDRGSEcorKse54Cr47koK0vH8utW3f1AUvPOiPJyK8YGXBK52IqsNwQ0RkRC6mK7H6UDJ+P5OmveLEwcocT7X1xQvt/Y2isyaRsWO4ISKSmFojsONiJlYeTMbRpBzt9HAfBwzr2BR9W/vwKA2RHhhuiIgkUlyuws8nrmP5gatIzak89SQ3k+GJCC8M79gUbQOc2ZeGqBYYboiI6llWQSnWHLqGtUeuIb+kAkDljfZeeNAfLz4UAG9Ha4krJGrYGG6IiOpJUnYRvtx3Bb+dSkO5urI/TYCrDUY9EoSn2/jC2pKnnogMgeGGiKiOxWcq8dmeK9h8Lh1/X8mNaH8nvPxoELq39ILcjKeeiAyJ4YaIqI6cTsnFZ3sSsTMuSzvtsTAPjO0SjHZNXSSsjMi0MdwQERnYieQcLNl5GQcSswEAMhnQK8IbY7sGI9zHUeLqiEwfww0RkYGcvFYZav66XBlqzM1kGBDdBK90CUawu53E1RE1Hgw3RET3qbpQ80w7X4ztEgI/FxuJqyNqfBhuiIhqKTYtHwu3JWDfpZsAKkPN0219Ma4rQw2RlBhuiIj0lJhViMU7EvDn+UwADDVExsZMyo0XFBRgwoQJCAgIgLW1NTp27Ijjx4/fcf69e/dCJpNVeWRmZtZj1UTUWKXllWDqL2fR4+N9+PN8JmQyYECUD3ZN7owPnopksCEyEpIeuRk1ahRiY2Oxdu1a+Pj44Ntvv0W3bt1w8eJFNGnS5I7LJSQkwMHBQfvcw8OjPsolokYqv7gC/7fnMlYfuqa9+V63Fp54I6Y5wrwc7rE0EdU3mRBCSLHhkpIS2NvbY8OGDejdu7d2etu2bdGzZ0+89957VZbZu3cvunbtitzcXDg5OdVqu0qlEo6OjsjPz9cJSERE/1Wu0mDtkWv4dNdl7TAJDwW5YEpMGNoGOEtcHVHjos/3t2RHblQqFdRqNaysrHSmW1tb48CBA3ddNioqCmVlZYiIiMCsWbPQqVOnO85bVlaGsrIy7XOlUnl/hRORyRNCYEtsJhZsjce1W8UAgOaedpjWqwW6NHfnYJZERk6ycGNvb48OHTpg7ty5aNGiBTw9PfHDDz/g8OHDCAkJqXYZb29vfPHFF2jXrh3KysqwfPlydOnSBUePHkWbNm2qXWb+/PmYPXt2XTaFiEzImdQ8zN10ESev5QIA3O0VmNy9OZ5u6wtzuaTdFImohiQ7LQUAV65cwYgRI7B//37I5XK0adMGzZs3x8mTJxEXF1ejdXTu3Bn+/v5Yu3Ztta9Xd+TGz8+Pp6WISEeWshQLtibg11PXAQDWFnKMfjQIox8Ngq2CF5YSSa1BnJYCgODgYOzbtw9FRUVQKpXw9vbGc889h6CgoBqvo3379nc9jaVQKKBQKAxRLhGZoNIKNVYcTMJnuxNRVK4GADzVxhdTnwiFp4PVPZYmImNkFH+O2NrawtbWFrm5udi2bRs+/PDDGi975swZeHt712F1RGSKhBDYfvEG5m2OQ0pOZb+aKD8nzOoXjig/J2mLI6L7Imm42bZtG4QQCA0NRWJiIqZMmYKwsDAMHz4cADBt2jSkpaVhzZo1AIAlS5YgMDAQ4eHhKC0txfLly7F7925s375dymYQUQOTnF2EmRsvaO8s7OmgwFs9w9C/dROYmbGzMFFDJ2m4yc/Px7Rp03D9+nW4uLjgqaeewrx582BhYQEAyMjIQEpKinb+8vJyTJ48GWlpabCxsUFkZCR27tyJrl27StUEImpASivUWLb3Cr7YdwXlKg0s5WYY9UggxnUNYb8aIhMiaYdiKfA+N0SN0+74G5i58QJSc0oAAI80c8PsfuEI4mjdRA1Cg+lQTERU19LzSjBr4wVsv3gDAODlYIUZfVuiZ4QX71dDZKIYbojIJKk1AmsOJ+OjbQkoKlfD3EyGkQ8HYvzjzXgKisjE8SeciEzOhfR8vP3beZy9ng8AaBvgjPcHtkKol73ElRFRfWC4ISKTUVyuwpKdl/HNgSSoNQL2VuZ484kwvNDen1dBETUiDDdEZBIOJWbjzd/OaTsM9470xsw+LeHBG/ERNToMN0TUoBWUVmD+lnh8f7TythE+jlZ4b2AEHgvzlLgyIpIKww0RNVh7ErLw9m/nkZFfCgB48SF/vNWzBezYYZioUeNvACJqcPKLKzBn00XtIJf+LjZY8FQkOgS7SlwZERkDhhsialD2X7qJqb+cQ6ayFDIZMKJTICb3aA4bS/46I6JK/G1ARA1CcbkK7/8Zh2+PVPatCXSzxUfPRKJtgIvElRGRsWG4ISKjdyI5B5N/PotrtypH7x7WsSnefCIM1pZyiSsjImPEcENERqtcpcHiHZfw1f4r0IjKK6EWPtManULcpC6NiIwYww0RGaUrNwvx+rrTiE1TAgCeauOLmf1awsHKQuLKiMjYMdwQkVERQuDnE9cxc+MFlFSo4WRjgQ+ejMQTEV5Sl0ZEDQTDDREZjfziCrz9+3lsPpcBAOgY7IrFz0bBy5F3GSaimmO4ISKjcDw5BxPWnUFaXgnMzWSY1KM5Xn40GHKOCUVEemK4ISJJaTQCn++7gkXbE6ARQICrDT55PhpRfk5Sl0ZEDRTDDRFJJqeoHBN/PIN9l24CAAZE+eC9ga04fAIR3Rf+BiEiSZxIzsFrP5xGRn4pFOZmmNM/HM+284NMxtNQRHR/GG6IqF4JIfD1X1exYGsC1BqBIDdbfDa4DVp4O0hdGhGZCIYbIqo3ytIKTPrxLHbG3QAA9G3tg/lP8jQUERkWf6MQUb24dKMAL689iaTsIliam2Fm35Z4ob0/T0MRkcEx3BBRndt8LgNTfjmL4nI1fByt8MVLbRHp6yR1WURkohhuiKjOqNQaLNyegC/3XQVQeVO+pYOi4WqnkLgyIjJlDDdEVCdyisrx2g+ncDDxFgDg5UeDMCUmFOZyM4krIyJTx3BDRAYXn6nEqNUncD23BDaWcix8ujV6R3pLXRYRNRIMN0RkUDsu3sCEdadRVK5GgKsNvnqpHUK97KUui4gaEYYbIjIIIQSW7b2Cj7YnQIjK/jWfvdAGzraWUpdGRI0Mww0R3bfSCjXe/PUcNpxJBwC89FAAZvRtCQv2ryEiCTDcENF9yVKW4n9rT+Jsah7kZjLM6heOlx4KkLosImrEGG6IqNYu3SjA8JXHkZZXAicbCywb3AYdg92kLouIGjmGGyKqlYOJ2Riz9iQKylQIcrPFimEPoKmbrdRlEREx3BCR/n4+kYppv52HSiPwQFNnfPVSO3YcJiKjwXBDRDUmhMDHOy7h092JAIB+rX3w4dORsLKQS1wZEdE/GG6IqEbKVRq8+es5rD+dBgB4tWsIJnVvDjMzDnxJRMaF4YaI7qmwTIUxa0/iQGI25GYyvD8wAs894C91WURE1WK4IaK7yi4sw/CVx3E+LR82lnJ8/mJbdG7uLnVZRER3xHBDRHeUcqsYQ1YcRfKtYrjYWmLlsAfQ2s9J6rKIiO6K4YaIqnUhPR/DVh7HzYIy+DpbY82I9ghyt5O6LCKie2K4IaIqDl+5hdFrTqCgTIUwL3usGdEeHg5WUpdFRFQjDDdEpGPnxRsY+/0plKs0eDDQBV8PbQcHKwupyyIiqjGGGyLS2ng2HZN+PAOVRqBHS098Oiia97AhogaH4YaIAAA/Hk/BW7+dhxDAwOgmWPh0JMw5qjcRNUAMN0SEFQeSMGfTRQDA4Af9Mbd/BG/OR0QNFsMNUSMmhMBnexLx0fZLAIDRjwZhWs8wyGQMNkTUcDHcEDVSQgh8uC0Bn++9AgCY1L05XnsshMGGiBo8hhuiRkgIgff/jMPXfyUBAN7t3QKjHgmSuCoiIsNguCFqZIQQmLspDisOVgabuQMi8NJDARJXRURkOAw3RI2IEAKzNl7A6sPXAADvD2yFFx7kAJhEZFoYbogaCY1GYMbGWHx7JAUyGfDBk604sjcRmSSGG6JGQKMReOf3WPxwrDLYfPhUJJ5p5yd1WUREdYLhhsjECfFPsDGTAR890xpPtvGVuiwiojrDcENkwoQQmP3HRW2wWfxsFAZEN5G6LCKiOsV7qxOZKCEE5m+Jx6pDyQCAD59uzWBDRI0Cww2Rifp4xyV8tf8qgMqrop5uy1NRRNQ4MNwQmaD/230Zn+5OBADM7NuSl3sTUaPCcENkYr7ef1U7VtS0nmEY3ilQ4oqIiOoXww2RCfnu6DXM+zMOQOVYUS93Dpa4IiKi+sdwQ2QiNp5Nx7u/xwIAXukSjNceC5G4IiIiaTDcEJmAPfFZmPTjGQgBvPiQP6bGhHJ0byJqtBhuiBq4Y0k5eOW7k1BpBPq19sGcfhEMNkTUqDHcEDVgsWn5GLnqOEorNHgszAOLnm0NMzMGGyJq3BhuiBqoqzcLMXTFMRSUqdC+qQs+e6ENLOT8kSYikvQ3YUFBASZMmICAgABYW1ujY8eOOH78+F2X2bt3L9q0aQOFQoGQkBCsWrWqfoolMiKZ+aV46ZtjuFVUjnAfBywf1g7WlnKpyyIiMgqShptRo0Zhx44dWLt2Lc6fP48ePXqgW7duSEtLq3b+pKQk9O7dG127dsWZM2cwYcIEjBo1Ctu2bavnyomkk19SgWErjyEtrwRBbrZYPaI9HKwspC6LiMhoyIQQQooNl5SUwN7eHhs2bEDv3r2109u2bYuePXvivffeq7LMm2++ic2bNyM2NlY77fnnn0deXh62bt1ao+0qlUo4OjoiPz8fDg4O998QonpUWqHG0BXHcDQpB+72Cvz2Skf4udhIXRYRUZ3T5/tbsiM3KpUKarUaVlZWOtOtra1x4MCBapc5fPgwunXrpjMtJiYGhw8frrM6iYyFRiMw+aezOJqUAzuFOVYNf4DBhoioGpKFG3t7e3To0AFz585Feno61Go1vv32Wxw+fBgZGRnVLpOZmQlPT0+daZ6enlAqlSgpKal2mbKyMiiVSp0HUUMjhMCcTRex+XwGLOQyfPlSW4T7OEpdFhGRUZK0z83atWshhECTJk2gUCjw6aefYtCgQTAzM1xZ8+fPh6Ojo/bh5+dnsHUT1Zcv91/FqkPJAICPnmmNTiFu0hZERGTEJA03wcHB2LdvHwoLC5Gamopjx46hoqICQUFB1c7v5eWFGzdu6Ey7ceMGHBwcYG1tXe0y06ZNQ35+vvaRmppq8HYQ1aX1p6/jgy3xAIB3erVA/6gmEldERGTczKUuAABsbW1ha2uL3NxcbNu2DR9++GG183Xo0AF//vmnzrQdO3agQ4cOd1y3QqGAQqEwaL1E9eXQlWxM/eUcAGDkw4H436PVB38iIvqHpEdutm3bhq1btyIpKQk7duxA165dERYWhuHDhwOoPOoyZMgQ7fxjxozB1atXMXXqVMTHx2PZsmX46aefMHHiRKmaQFRnErMK8PLak6hQC/Ru5Y13erWQuiQiogZB0nCTn5+PcePGISwsDEOGDMHDDz+Mbdu2wcKi8p4dGRkZSElJ0c4fGBiIzZs3Y8eOHWjdujUWLVqE5cuXIyYmRqomENWJmwVlGLbyOApKVWgb4MxhFYiI9CDZfW6kwvvckLErKVfj+a+P4GxqHgJcbbB+bCe42FpKXRYRkaTq9D43N27cwEsvvQQfHx+Ym5tDLpfrPIio9jQagYk/nsHZ1Dw42Vhg5bAHGGyIiPSkd4fiYcOGISUlBdOnT4e3tzdkMh4qJzKU+VvisPVCJizlZvjqpXYIcreTuiQiogZH73Bz4MAB/PXXX4iKiqqDcogar7VHruHrv5IAAAufiUT7QBeJKyIiapj0Pi3l5+eHRtZNh6jO/XX5JmZtvAAAeKNHc97LhojoPugdbpYsWYK33noLycnJdVAOUeOTmFWIsd+dgloj8GR0E4zrGiJ1SUREDZrep6Wee+45FBcXIzg4GDY2NtrLtm/LyckxWHFEpi6vuByjVv9zyff8p1qxHxsR0X3SO9wsWbKkDsoganwq1Bq88u0pJN8qRhMna3z5UlsozHnFIRHR/dI73AwdOrQu6iBqVIQQmLHhAg5fvQVbSzm+GdYObnYcJoSIyBBqNbaUWq3G77//jri4OABAeHg4+vXrx/vcENXQqkPJ+OFYCmQy4NNB0Qjz4g0liYgMRe9wk5iYiF69eiEtLQ2hoaEAgPnz58PPzw+bN29GcHCwwYskMiX7Lt3E3E0XAQBv92yBx1t4SlwREZFp0ftqqfHjxyM4OBipqak4deoUTp06hZSUFAQGBmL8+PF1USORybh6sxCvfn8KGgE8284Xox4JlLokIiKTo/eRm3379uHIkSNwcfnnBmOurq744IMP0KlTJ4MWR2RKCkor8L81J7RXRr03gFdGERHVBb2P3CgUChQUFFSZXlhYCEtLjoFDVB2NRmDCujO4crMIXg5W+PzFNrA01/vHj4iIakDv3659+vTB6NGjcfToUQghIITAkSNHMGbMGPTr168uaiRq8BbvuIRd8VlQmJvhqyFt4WFvJXVJREQmS+9w8+mnnyI4OBgdOnSAlZUVrKys0KlTJ4SEhOCTTz6pixqJGrRN59Lxf3sSAQAfPNUKkb5O0hZERGTi9O5z4+TkhA0bNuDy5cuIj48HALRo0QIhIbxlPNF/XUjPx5SfzwEARj8ahIHRvhJXRERk+mp1nxsAaNasGZo1a2bIWohMSk5ROUavOYmSCjUeaeaGN58Ik7okIqJGoUbhZtKkSZg7dy5sbW0xadKku867ePFigxRG1JCp1Bq89sMppOWVoKmrDf5vUBvIzXhlFBFRfahRuDl9+jQqKiq0/yeiu1u4PQEHE2/BxlKOL19qB0cbi3svREREBlGjcLNnz55q/09EVW0+l4Ev910FAHz4dCRCvewlroiIqHHR+2qpESNGVHufm6KiIowYMcIgRRE1VJdvFGDKL2cBVHYg7hPpI3FFRESNj97hZvXq1SgpKakyvaSkBGvWrDFIUUQNkbK0Ai+vPYnicjU6Brtiakyo1CURETVKNb5aSqlUam/aV1BQACurf25Cplar8eeff8LDw6NOiiQydhqNwKQfz+JqdhF8HK2wdFA0zOW8AzERkRRqHG6cnJwgk8kgk8nQvHnzKq/LZDLMnj3boMURNRSf7UnEzrgbsDQ3wxcvtYWrnULqkoiIGq0ah5s9e/ZACIHHHnsMv/76q87AmZaWlggICICPD/sXUOOz/9JNLN55CQDwXv8I3oGYiEhiNQ43nTt3BgAkJSXBz88PZmY85E6UnleC19edhhDAoPZ+ePYBP6lLIiJq9PS+Q3FAQAAAoLi4GCkpKSgvL9d5PTIy0jCVERm5cpUGY787hdziCkQ0ccDMvuFSl0RERKhFuLl58yaGDx+OLVu2VPu6Wq2+76KIGoL3/4zDmdQ8OFiZ4/PBbWFlIZe6JCIiQi0uBZ8wYQLy8vJw9OhRWFtbY+vWrVi9ejWaNWuGjRs31kWNREZn49l0rDqUDABY/GwU/FxspC2IiIi09D5ys3v3bmzYsAHt2rWDmZkZAgIC0L17dzg4OGD+/Pno3bt3XdRJZDQSswrw1q+VI32/0iUY3Vp6SlwRERH9m95HboqKirT3s3F2dsbNmzcBAK1atcKpU6cMWx2RkSkqU2HMt6dQXK5GhyBXTO5e9bYIREQkLb3DTWhoKBISEgAArVu3xpdffom0tDR88cUX8Pb2NniBRMZCCIG3159HYlYhPOwV+JQ36iMiMkp6n5Z6/fXXkZGRAQCYOXMmnnjiCXz33XewtLTEqlWrDF0fkdFYdzwVG86kQ24mw/+90Abu9rxRHxGRMZIJIcT9rKC4uBjx8fHw9/eHm5uboeqqM0qlEo6OjsjPz4eDg4PU5VADcTFdiQHLDqJcpcFbPcMwpnOw1CURETUq+nx/633k5r9sbGzQpk2b+10NkdEqLFPh1e9PoVylQddQd4x+JEjqkoiI6C5qFG4mTZpU4xUuXry41sUQGRshBN7+7TyuZhfB29EKi56NgpmZTOqyiIjoLmoUbk6fPl2jlclk/KVPpmXd8VRsPFvZz2bpoGi42FpKXRIREd1DjcLNnj176roOIqNzMV2JmRsvAACmxISiXVOXeyxBRETGgNexElWD/WyIiBouvTsUd+3a9a6nn3bv3n1fBRFJTQiBd9eznw0RUUOld7iJiorSeV5RUYEzZ84gNjYWQ4cONVRdRJL59VQafj/DfjZERA2V3uHm448/rnb6rFmzUFhYeN8FEUnpys1CTP89FgAwqXtz9rMhImqADNbn5sUXX8SKFSsMtTqieldaoca4706hpEKNTiGuvFEfEVEDZbBwc/jwYVhZWRlqdUT17v0/4xCfWQBXW0t8/GwU5OxnQ0TUIOl9WurJJ5/UeS6EQEZGBk6cOIHp06cbrDCi+rQ1NhNrDl8DACx6tjU8HBjUiYgaKr3DjaOjo85zMzMzhIaGYs6cOejRo4fBCiOqL9dzizH1l7MAgNGPBqFLqIfEFRER0f3QO9ysXLmyLuogkoRKrcHr685AWapCa19HvNEjVOqSiIjoPtV64MwTJ04gLi4OANCyZUu0bdvWYEUR1ZdPd13GyWu5sFeYY+mgNrA0530tiYgaOr3DzfXr1zFo0CAcPHgQTk5OAIC8vDx07NgR69atg6+vr6FrJKoTR6/ewv/tSQQAzHuyFfxdbSSuiIiIDEHvP1NHjRqFiooKxMXFIScnBzk5OYiLi4NGo8GoUaPqokYig8svrsDEH89AI4Cn2/qiX2sfqUsiIiID0fvIzb59+3Do0CGEhv7TNyE0NBRLly7FI488YtDiiOqCEALT1p9Den4pmrraYFa/cKlLIiIiA9L7yI2fnx8qKiqqTFer1fDx4V+/ZPx+OpGKP89nwtxMhk+ej4adotZdz4iIyAjpHW4WLlyI1157DSdOnNBOO3HiBF5//XV89NFHBi2OyNCu3CzErI0XAQCTe4SitZ+TtAUREZHByYQQQp8FnJ2dUVxcDJVKBXPzyr94b//f1tZWZ96cnBzDVWogSqUSjo6OyM/Ph4ODg9TlUD0qU6nx5LJDuJCuRMdgV3w78kGO9k1E1EDo8/2t9/H4JUuW1LYuIkkt2n4JF9KVcLaxwOJnoxhsiIhMlN7hZujQoXVRB1GdOnA5G1/tvwoAWPBUJLwcObwCEZGpqlVPSrVajd9//117E7/w8HD069cPcrncoMURGUJuUTkm/3wGADD4QX/0CPeStiAiIqpTeoebxMRE9OrVC2lpadrLwefPnw8/Pz9s3rwZwcHBBi+SqLaEEHh7/XncUJYhyN0W7/ZuKXVJRERUx/S+Wmr8+PEIDg5GamoqTp06hVOnTiElJQWBgYEYP358XdRIVGu/nLyOLbF/X/b9XDSsLXl0kYjI1NXqJn5HjhyBi4uLdpqrqys++OADdOrUyaDFEd2PlFvFmLXxAgBgYvfmaOXreI8liIjIFOh95EahUKCgoKDK9MLCQlhaWhqkKKL7pVJrMOHH0ygqV6N9UxeM6czTpUREjYXe4aZPnz4YPXo0jh49CiEEhBA4cuQIxowZg379+tVFjUR6W7b3Ck6l5MFeYY7Fz7WGnJd9ExE1GnqHm08//RTBwcHo0KEDrKysYGVlhU6dOiEkJASffPJJXdRIpJfTKbn4ZNdlAMDcARHwdeZo30REjYnefW6cnJywYcMGJCYmai8Fb9GiBUJCQgxeHJG+istVmPjjGag1Av1a+2BAdBOpSyIionpW4yM3Go0GCxYsQKdOnfDAAw9g+fLl6NatG/r27VvrYKNWqzF9+nQEBgbC2toawcHBmDt3Lu42IsTevXshk8mqPDIzM2tVA5mWeZvjkHyrGN6OVpg7IELqcoiISAI1PnIzb948zJo1C926dYO1tTU++eQTZGVlYcWKFbXe+IIFC/D5559j9erVCA8Px4kTJzB8+HA4Ojre87LyhIQEnbElPDw8al0HmYY9CVn47mgKAOCjZ1rD0dpC4oqIiEgKNQ43a9aswbJly/Dyyy8DAHbu3InevXtj+fLlMDPTu+sOAODQoUPo378/evfuDQBo2rQpfvjhBxw7duyey3p4eMDJyalW2yXTk1tUjqm/nAMADO/UFJ1C3CSuiIiIpFLjVJKSkoJevXppn3fr1g0ymQzp6em13njHjh2xa9cuXLp0CQBw9uxZHDhwAD179rznslFRUfD29kb37t1x8ODBO85XVlYGpVKp8yDTIoTAO7+fx82CMoR42OHNJ8KkLomIiCRU4yM3KpUKVla6gw1aWFigoqKi1ht/6623oFQqERYWBrlcDrVajXnz5mHw4MF3XMbb2xtffPEF2rVrh7KyMixfvhxdunTB0aNH0aZNmyrzz58/H7Nnz651jWT8fj+Thj/PV96F+ONno2BlwbsQExE1ZjJxt967/2JmZoaePXtCoVBop/3xxx947LHHYGtrq53222+/1Xjj69atw5QpU7Bw4UKEh4fjzJkzmDBhAhYvXqzX6OOdO3eGv78/1q5dW+W1srIylJWVaZ8rlUr4+fkhPz9fp88ONUzpeSWIWbIfBaUqTOreHOMfbyZ1SUREVAeUSiUcHR1r9P1d4yM31YWNF198Uf/q/mXKlCl466238PzzzwMAWrVqhWvXrmH+/Pl6hZv27dvjwIED1b6mUCh0AhmZDo1G4I2fz6KgVIUoPyeM7cK7EBMRkR7hZuXKlQbfeHFxcZXOyHK5HBqNRq/1nDlzBt7e3oYsjRqAVYeScejKLVhZmGHxs61hLq9dx3YiIjItet/Ez5D69u2LefPmwd/fH+Hh4Th9+jQWL16MESNGaOeZNm0a0tLSsGbNGgDAkiVLEBgYiPDwcJSWlmL58uXYvXs3tm/fLlUzSAJXbhZiwdZ4AMA7vVogyN1O4oqIiMhYSBpuli5diunTp2Ps2LHIysqCj48PXn75ZcyYMUM7T0ZGBlJSUrTPy8vLMXnyZKSlpcHGxgaRkZHYuXMnunbtKkUTSAIqtQaTfjqLMpUGjzRzw4sPBUhdEhERGZEadyg2Ffp0SCLj9NmeRCzclgB7K3Nsm/AofJyspS6JiIjqmD7f3+ykQA3KxXQlluysvC/SrL7hDDZERFQFww01GOUqDSb9dAYVaoHuLT3xZBsOiklERFXVqM/Nxo0ba7zCfv361boYorv5dNdlxGcWwMXWEu8PbAWZTCZ1SUREZIRqFG4GDBhQo5XJZDKo1er7qYeoWqdTcrFsbyIA4L0BEXC3572LiIioejUKN/red4bIkErK1Zj801loBNA/yge9WvGeRkREdGfsc0NG76PtCbiaXQQPewVm9wuXuhwiIjJytbrPTVFREfbt24eUlBSUl5frvDZ+/HiDFEYEAMeScrDiYBIAYMFTkXCysZS4IiIiMnZ6h5vTp0+jV69eKC4uRlFREVxcXJCdnQ0bGxt4eHgw3JDBFJerMOWXsxACeLadL7qGeUhdEhERNQB6n5aaOHEi+vbti9zcXFhbW+PIkSO4du0a2rZti48++qguaqRG6sOtCbh2qxjejlZ4t09LqcshIqIGQu9wc+bMGUyePBlmZmaQy+UoKyuDn58fPvzwQ7z99tt1USM1Qkeu3sKqQ8kAgA+eioSDlYW0BRERUYOhd7ixsLDQjuTt4eGhHffJ0dERqamphq2OGqWissrTUQAwqL0fOjd3l7giIiJqSPTucxMdHY3jx4+jWbNm6Ny5M2bMmIHs7GysXbsWERERdVEjNTILtsYjNacETZys8XavFlKXQ0REDYzeR27ef/99eHtX3mdk3rx5cHZ2xiuvvIKbN2/iyy+/NHiB1LgcSszGmsPXAFReHWXP01FERKQnvY/ctGvXTvt/Dw8PbN261aAFUeNVWKbC1F/PAQAGP+iPh5u5SVwRERE1RHofuXnssceQl5dXZbpSqcRjjz1miJqokVqwJR7Xc0vg62yNaTwdRUREtaR3uNm7d2+VG/cBQGlpKf766y+DFEWNz+Ert7D2yD+no+wUtbq/JBERUc1PS507d077/4sXLyIzM1P7XK1WY+vWrWjSpIlhq6NGobhchTf/Ph01qL0/OoXwdBQREdVejcNNVFQUZDIZZDJZtaefrK2tsXTpUoMWR43Dwm0JSMkpho+jFd7uFSZ1OURE1MDVONwkJSVBCIGgoCAcO3YM7u7/3HvE0tISHh4ekMvldVIkma4TyTnam/W9/2QrXh1FRET3rcbhJiAgAACg0WjqrBhqXEor1Jj6yzkIATzd1hddQjl2FBER3b9a9dq8cuUKlixZgri4OABAy5Yt8frrryM4ONigxZFpW7zjEq5mF8HDXoHpvTl2FBERGYbeV0tt27YNLVu2xLFjxxAZGYnIyEgcPXoU4eHh2LFjR13USCbodEoulv91FQDw/sBWcLTh6SgiIjIMmRBC6LNAdHQ0YmJi8MEHH+hMf+utt7B9+3acOnXKoAUamlKphKOjI/Lz8+Hg4CB1OY1SmUqNPp8ewOWsQgyI8sGS56OlLomIiIycPt/feh+5iYuLw8iRI6tMHzFiBC5evKjv6qgR+mx3Ii5nFcLNzhIz+4ZLXQ4REZkYvcONu7s7zpw5U2X6mTNn4OHBDqF0dxfTlVi29woAYHa/CDjbWkpcERERmZoadyieM2cO3njjDfzvf//D6NGjcfXqVXTs2BEAcPDgQSxYsACTJk2qs0Kp4VOpNXjz13NQaQRiwj3Rq5WX1CUREZEJqnGfG7lcjoyMDLi7u2PJkiVYtGgR0tPTAQA+Pj6YMmUKxo8fD5lMVqcF3y/2uZHOl/uuYP6WeNhbmWPXpM7wcLCSuiQiImog9Pn+rnG4MTMzQ2Zmps6pp4KCAgCAvb39fZRbvxhupJGUXYQnluxHmUqDD5+KxLMP+EldEhERNSD6fH/rdZ+b/x6VaUihhqSj0Qi89es5lKk0eDjEDc+085W6JCIiMmF6hZvmzZvf87RTTk7OfRVEpueH4yk4mpQDaws55j/ZyuhPXRIRUcOmV7iZPXs2HB0d66oWMkHpeSWY/2c8AGBKTCj8XGwkroiIiEydXuHm+eef5+XeVGNCCLz7eywKy1Ro4++EoR2bSl0SERE1AjW+zw1PJZC+/jiXgd3xWbCUm2HBU5GQm/EzREREda/G4UbPURqokcstKsfsjRcAAOO6hqCZJzufExFR/ajxaSmNRlOXdZCJeW9zHG4VlaO5px1e6cLR4omIqP7oPfwC0b38dfkmfj11HTIZMP/JSFia82NGRET1h986ZFDF5Sq8vf48AGBoh6ZoG+AscUVERNTYMNyQQX284xJSc0rg42iFN2JCpS6HiIgaIYYbMphz1/PwzYEkAMB7AyNgp9DrTgNEREQGwXBDBlGh1uDNX89DI4B+rX3wWJin1CUREVEjxXBDBrH8ryTEZSjhZGOBGX1bSl0OERE1Ygw3dN+u3SrCkp2XAADv9m4JNzuFxBUREVFjxnBD90UIgXfWx6JMpUGnEFc81aaJ1CUREVEjx3BD92X96TQcSMyGwtwM8wZwxG8iIpIeww3VWk5ROeZuuggAGP94MzR1s5W4IiIiIoYbug/zNscht7gCoZ72GP1okNTlEBERAWC4oVo6mJj9zxALT7WChZwfJSIiMg78RiK9lVaotUMsvPRQANr4c4gFIiIyHgw3pLdPd13GtVvF8HKwwhQOsUBEREaG4Yb0Ep+pxFf7rwIAZvcPh72VhcQVERER6WK4oRrTaATe/u08VBqBHi09ERPuJXVJREREVTDcUI39cDwFp1LyYGspx+z+4VKXQ0REVC2GG6qRrIJSfLAlHgDwRkwovB2tJa6IiIioegw3VCNzN8WhoFSFVk0cMaRDU6nLISIiuiOGG7qnvQlZ+ONsOsxkwPwnW0FuxiEWiIjIeDHc0F2VlKsxfUMsAGB4p0BENHGUuCIiIqK7Y7ihu/p092Wk5pTAx9EKk7o3l7ocIiKie2K4oTuKz1Tia+09bSJgqzCXuCIiIqJ7Y7ihav37njYx4Z7o3tJT6pKIiIhqhOGGqrXueCpOpeTBTmGO2f0ipC6HiIioxhhuqIrswjJ8sCUOADCpe3N4OVpJXBEREVHNMdxQFe9vjoOyVIWIJg4Y0iFA6nKIiIj0wnBDOg5dycZvp9MgkwHzBrSCuZwfESIialj4zUVaZSo13v298p42Lz0UgNZ+TtIWREREVAuShhu1Wo3p06cjMDAQ1tbWCA4Oxty5cyGEuOtye/fuRZs2baBQKBASEoJVq1bVT8Em7qt9V3H1ZhHc7RV4IyZU6nKIiIhqRdIblyxYsACff/45Vq9ejfDwcJw4cQLDhw+Ho6Mjxo8fX+0ySUlJ6N27N8aMGYPvvvsOu3btwqhRo+Dt7Y2YmJh6boHpSM4uwtI9iQCA6X1awsHKQuKKiIiIakfScHPo0CH0798fvXv3BgA0bdoUP/zwA44dO3bHZb744gsEBgZi0aJFAIAWLVrgwIED+PjjjxluakkIgekbYlGu0uCRZm7oG+ktdUlERES1JulpqY4dO2LXrl24dOkSAODs2bM4cOAAevbsecdlDh8+jG7duulMi4mJweHDh6udv6ysDEqlUudBujafz8Bfl7NhaW6GOf0jIJNxYEwiImq4JD1y89Zbb0GpVCIsLAxyuRxqtRrz5s3D4MGD77hMZmYmPD1175br6ekJpVKJkpISWFtb67w2f/58zJ49u07qNwUFpRWY88dFAMC4LiEIdLOVuCIiIqL7I+mRm59++gnfffcdvv/+e5w6dQqrV6/GRx99hNWrVxtsG9OmTUN+fr72kZqaarB1m4LFOy4hq6AMgW62GNMlSOpyiIiI7pukR26mTJmCt956C88//zwAoFWrVrh27Rrmz5+PoUOHVruMl5cXbty4oTPtxo0bcHBwqHLUBgAUCgUUCoXhizcBsWn5WH0oGQAwp384FOZyaQsiIiIyAEmP3BQXF8PMTLcEuVwOjUZzx2U6dOiAXbt26UzbsWMHOnToUCc1miqNRuDd32OhEUCfSG880sxd6pKIiIgMQtJw07dvX8ybNw+bN29GcnIy1q9fj8WLF2PgwIHaeaZNm4YhQ4Zon48ZMwZXr17F1KlTER8fj2XLluGnn37CxIkTpWhCg7XueCrOpFYOjDm9T0upyyEiIjIYSU9LLV26FNOnT8fYsWORlZUFHx8fvPzyy5gxY4Z2noyMDKSkpGifBwYGYvPmzZg4cSI++eQT+Pr6Yvny5bwMXA+3CsuwYGs8gMqBMT0dODAmERGZDpm41+2ATYxSqYSjoyPy8/Ph4OAgdTmSmPLzWfx88jpaeDvgj1c7cfwoIiIyevp8f/NbrZE5npyDn09eBwC8NyCCwYaIiEwOv9kakQq1Bu+urxwYc1B7P7QNcJa4IiIiIsNjuGlEVh1MRsKNAjjbWGBqTJjU5RAREdUJhptGIiO/BB/vrBzmYlrPFnC2tZS4IiIiorrBcNNIvLcpDsXlarQNcMbTbX2lLoeIiKjOMNw0An9dvonN5zNgJqu8E7GZGQfGJCIi08VwY+LKVGrM3HABADCkQ1OE+zhKXBEREVHdYrgxccv/SsLV7CK42yswqUdzqcshIiKqcww3Jux6bjGW7r4MAHinVws4WFlIXBEREVHdY7gxYXP+uIjSCg0eDHRB/ygfqcshIiKqFww3JmpPfBa2X7wBczMZ5g6IgEzGTsRERNQ4MNyYoNIKNWZurOxEPOLhQDT3tJe4IiIiovrDcGOCvth3BSk5xfBysMLrjzeTuhwiIqJ6xXBjYlJuFePzvVcAAO/2aQFbhbnEFREREdUvhhsTM2fTBZSpNOgU4orerbylLoeIiKjeMdyYkN3xN7AzLgvmZjLM7hfOTsRERNQoMdyYiNIKNWZtvAgAGPlwIEI82ImYiIgaJ4YbE/HV/qtIySmGp4MCr7ETMRERNWIMNyYgNacYn+1JBAC807sl7NiJmIiIGjGGGxMwZ9NFlKk06BDkir6R7ERMRESNG8NNA7cnIQs7/r4T8Zz+7ERMRETEcNOAlanUmP33nYiHd2qKZrwTMREREcNNQ7b8ryQk3yqGh70Cr3drLnU5RERERoHhpoFKyyvB0t2XAQBv92rBTsRERER/Y7hpoN7fHIfSCg3aN3VB/ygfqcshIiIyGgw3DdDBxGxsPp8BMxkwm52IiYiIdDDcNDAVag1m/t2JeEiHpmjh7SBxRURERMaF4aaBWX0oGYlZhXCxtcREdiImIiKqguGmAckqKMWSnZWdiN98IhSONhYSV0RERGR8GG4akA+2xKOwTIXWvo54pq2f1OUQEREZJYabBuJEcg5+O5UGmQyY0z8CZmbsRExERFQdhpsGQK0R2k7Ez7XzQ2s/J2kLIiIiMmIMNw3AuuMpuJCuhIOVOabEhEpdDhERkVFjuDFyecXl+GhbAgBgUvfmcLVTSFwRERGRcWO4MXKLd1xCbnEFQj3t8eJDAVKXQ0REZPQYboxYXIYS3x65BgCY2a8lzOXcXURERPfCb0sjJURlJ2KNAHq38kbHYDepSyIiImoQGG6M1KZzGTiWlAMrCzNM6xUmdTlEREQNBsONESouV+H9P+MAAK90DoGvs43EFRERETUcDDdGaNmeK8jIL4WvszVe7hwkdTlEREQNCsONkbl2qwhf7b8KAHi3d0tYWcglroiIiKhhYbgxMu9tjkO5WoOHQ9wQE+4pdTlEREQNDsONEdl/6SZ2XLwBuZkMM/u2hEzG8aOIiIj0xXBjJCrUGszZdBEAMKRDAJp52ktcERERUcPEcGMk1hy+hsSsQrjYWmJCt+ZSl0NERNRgMdwYgVuFZViy8xIA4I0eoXC0tpC4IiIiooaL4cYIfLQ9AQWlKoT7OOC5B/ykLoeIiKhBY7iRWGxaPtYdTwUAzOwbDrkZOxETERHdD4YbCQkhMGvjBQgB9G3tg/aBLlKXRERE1OAx3Eho49l0nLiWWzl+VE+OH0VERGQIDDcSKS5XYf6f8QCAsV1C4ONkLXFFREREpoHhRiKf772CTGXl+FGjH+X4UURERIbCcCOB1JxifPn3+FHv9GrB8aOIiIgMiOFGAvM2x6FcpUGHIFc8EeEldTlEREQmheGmnh1KzMbWC5mV40f14/hRREREhsZwU49Uag1m/1E5ftSLD/ojzMtB4oqIiIhMD8NNPfruaAoSbhTA2cYCE7tz/CgiIqK6wHBTT3KKyrFoewIAYFKPUDjZWEpcERERkWliuKkni7YnQFmqQpiXPV5o7y91OURERCaL4aYeXExX4odjKQCAWf04fhQREVFdYripY0IIzPrjAjQC6B3pjYeCXKUuiYiIyKQx3NSxjWfTcSwpB1YWZni7VwupyyEiIjJ5DDd1KLeoHHP+vvR7bJcQNOH4UURERHVO0nDTtGlTyGSyKo9x48ZVO/+qVauqzGtlZVXPVdfc3E0XcauoHM097TCmc7DU5RARETUK5lJu/Pjx41Cr1drnsbGx6N69O5555pk7LuPg4ICEhATtc2O9w+/ehCz8djoNMhnwwVORsDTnQTIiIqL6IGm4cXd313n+wQcfIDg4GJ07d77jMjKZDF5exj0eU1GZCu+sjwUADOvYFG38nSWuiIiIqPEwmsMJ5eXl+PbbbzFixIi7Ho0pLCxEQEAA/Pz80L9/f1y4cKEeq6yZhdsSkJZXAl9na7zRI1TqcoiIiBoVowk3v//+O/Ly8jBs2LA7zhMaGooVK1Zgw4YN+Pbbb6HRaNCxY0dcv379jsuUlZVBqVTqPOrSyWu5WH04GQDw/sBWsFVIenCMiIio0ZEJIYTURQBATEwMLC0t8ccff9R4mYqKCrRo0QKDBg3C3Llzq51n1qxZmD17dpXp+fn5cHAw7MCVZSo1+nx6AJezCvFUG18sera1QddPRETUWCmVSjg6Otbo+9sojtxcu3YNO3fuxKhRo/RazsLCAtHR0UhMTLzjPNOmTUN+fr72kZqaer/l3tFne67gclYh3OwsMb0P72lDREQkBaMINytXroSHhwd69+6t13JqtRrnz5+Ht7f3HedRKBRwcHDQedSFhMwCfL63MmTN6hfOgTGJiIgkInmHEI1Gg5UrV2Lo0KEwN9ctZ8iQIWjSpAnmz58PAJgzZw4eeughhISEIC8vDwsXLsS1a9f0PuJTF24VlcHZxhKRvk7o3erOYYuIiIjqluThZufOnUhJScGIESOqvJaSkgIzs38OLuXm5uJ///sfMjMz4ezsjLZt2+LQoUNo2bJlfZZcrY7BbtgxqTMq1BqjvfcOERFRY2A0HYrriz4dkoiIiMg4NLgOxURERESGwnBDREREJoXhhoiIiEwKww0RERGZFIYbIiIiMikMN0RERGRSGG6IiIjIpDDcEBERkUlhuCEiIiKTwnBDREREJoXhhoiIiEwKww0RERGZFIYbIiIiMinmUhdQ324Pgq5UKiWuhIiIiGrq9vf27e/xu2l04aagoAAA4OfnJ3ElREREpK+CggI4OjredR6ZqEkEMiEajQbp6emwt7eHTCa767xKpRJ+fn5ITU2Fg4NDPVVY/9hO09IY2tkY2giwnaaG7bw/QggUFBTAx8cHZmZ371XT6I7cmJmZwdfXV69lHBwcTPqDeBvbaVoaQzsbQxsBttPUsJ21d68jNrexQzERERGZFIYbIiIiMikMN3ehUCgwc+ZMKBQKqUupU2ynaWkM7WwMbQTYTlPDdtafRtehmIiIiEwbj9wQERGRSWG4ISIiIpPCcENEREQmheGGiIiITIrJh5v58+fjgQcegL29PTw8PDBgwAAkJCTozFNaWopx48bB1dUVdnZ2eOqpp3Djxg3t62fPnsWgQYPg5+cHa2trtGjRAp988kmVbe3duxdt2rSBQqFASEgIVq1aVdfN0zJEO2/duoUnnngCPj4+UCgU8PPzw6uvvlplHC6p2mmINv7brVu34OvrC5lMhry8PJ3XGvq+BACZTFblsW7dOp15TKGdALBq1SpERkbCysoKHh4eGDdunM7r586dwyOPPAIrKyv4+fnhww8/rNO2/Zsh2rlq1apq96dMJkNWVpZ2PlPYn8ePH8fjjz8OJycnODs7IyYmBmfPntWZR6r9aag27tq1Cx07doS9vT28vLzw5ptvQqVS6cxj7J/Zr776Cl26dIGDg0O1v0MBICcnB4MHD4aDgwOcnJwwcuRIFBYW6sxTZ+0UJi4mJkasXLlSxMbGijNnzohevXoJf39/UVhYqJ1nzJgxws/PT+zatUucOHFCPPTQQ6Jjx47a17/55hsxfvx4sXfvXnHlyhWxdu1aYW1tLZYuXaqd5+rVq8LGxkZMmjRJXLx4USxdulTI5XKxdevWBtPOnJwcsWzZMnH8+HGRnJwsdu7cKUJDQ8WgQYOMop2GaOO/9e/fX/Ts2VMAELm5udrpprAvhRACgFi5cqXIyMjQPkpKSkyunYsWLRI+Pj7iu+++E4mJieLs2bNiw4YN2tfz8/OFp6enGDx4sIiNjRU//PCDsLa2Fl9++WWDaWdxcbHOfszIyBAxMTGic+fO2nlMYX8WFBQIFxcXMWzYMBEfHy9iY2PFU089JTw9PUV5ebkQQtr9aYg2njlzRlhaWorZs2eLy5cvi71794qwsDAxefJk7TwN4TP78ccfi/nz54v58+dX+R162xNPPCFat24tjhw5Iv766y8REhKi831Sl+00+XDzX1lZWQKA2LdvnxBCiLy8PGFhYSF+/vln7TxxcXECgDh8+PAd1zN27FjRtWtX7fOpU6eK8PBwnXmee+45ERMTY+AW1Iyh2vnJJ58IX19f7XNjauf9tHHZsmWic+fOYteuXVV+MI2pjULUvp0AxPr16++4XlNoZ05OjrC2thY7d+6843qXLVsmnJ2dRVlZmXbam2++KUJDQ+uoJXdniJ/NrKwsYWFhIdasWaOdZgr78/jx4wKASElJ0c5z7tw5AUBcvnxZCGFc+7M2bZw2bZpo166dzno2btworKyshFKpFEIYVxuFqNrOf9uzZ0+14ebixYsCgDh+/Lh22pYtW4RMJhNpaWlCiLptp8mflvqv/Px8AICLiwsA4OTJk6ioqEC3bt2084SFhcHf3x+HDx++63purwMADh8+rLMOAIiJibnrOuqSIdqZnp6O3377DZ07d9ZOM6Z21raNFy9exJw5c7BmzZpqB18zpjYC97cvx40bBzc3N7Rv3x4rVqyA+NdtrUyhnTt27IBGo0FaWhpatGgBX19fPPvss0hNTdUuc/jwYTz66KOwtLTUTouJiUFCQgJyc3Pro2k6DPGzuWbNGtjY2ODpp5/WTjOF/RkaGgpXV1d88803KC8vR0lJCb755hu0aNECTZs2BWBc+7M2bSwrK4OVlZXOeqytrVFaWoqTJ08CMK42AlXbWROHDx+Gk5MT2rVrp53WrVs3mJmZ4ejRo9p56qqdjSrcaDQaTJgwAZ06dUJERAQAIDMzE5aWlnByctKZ19PTE5mZmdWu59ChQ/jxxx8xevRo7bTMzEx4enpWWYdSqURJSYlhG3IP99vOQYMGwcbGBk2aNIGDgwOWL1+ufc1Y2lnbNpaVlWHQoEFYuHAh/P39q123sbQRuL99OWfOHPz000/YsWMHnnrqKYwdOxZLly7Vvm4K7bx69So0Gg3ef/99LFmyBL/88gtycnLQvXt3lJeXa9dTXTtvv1afDPU76JtvvsELL7wAa2tr7TRT2J/29vbYu3cvvv32W1hbW8POzg5bt27Fli1bYG5url2PMezP2rYxJiYGhw4dwg8//AC1Wo20tDTMmTMHAJCRkaFdjzG0Eai+nTWRmZkJDw8PnWnm5uZwcXHRtqEu29mows24ceMQGxtbpVOlPmJjY9G/f3/MnDkTPXr0MGB1hnO/7fz4449x6tQpbNiwAVeuXMGkSZMMXOH9q20bp02bhhYtWuDFF1+so8oM63725fTp09GpUydER0fjzTffxNSpU7Fw4cI6qPL+1badGo0GFRUV+PTTTxETE4OHHnoIP/zwAy5fvow9e/bUUbW1Z4jfQYcPH0ZcXBxGjhxpwMoMq7btLCkpwciRI9GpUyccOXIEBw8eREREBHr37l3vAe1eatvGHj16YOHChRgzZgwUCgWaN2+OXr16AUC1R5KlZojPrBSM752sI6+++io2bdqEPXv2wNfXVzvdy8sL5eXlVXp637hxA15eXjrTLl68iMcffxyjR4/Gu+++q/Oal5dXlR7xN27cgIODg85fV3XNEO308vJCWFgY+vXrhy+//BKff/659i8KY2jn/bRx9+7d+Pnnn2Fubg5zc3M8/vjjAAA3NzfMnDlTux6p2wgYZl/+24MPPojr16+jrKxMu56G3k5vb28AQMuWLbWvu7u7w83NDSkpKdr1VNfO26/VF0Ptz+XLlyMqKgpt27bVmW4K+/P7779HcnIyVq5ciQceeAAPPfQQvv/+eyQlJWHDhg3a9Ui9P+93X06aNAl5eXlISUlBdnY2+vfvDwAICgrSrkfqNgJ3bmdNeHl56VzJBwAqlQo5OTnaNtRpO++7146R02g0Yty4ccLHx0dcunSpyuu3O4D98ssv2mnx8fFVOvPFxsYKDw8PMWXKlGq3M3XqVBEREaEzbdCgQfXWmc9Q7fyvffv2CQAiKSlJCCFtOw3RxsTERHH+/HntY8WKFQKAOHTokLhx44YQwnT35XvvvSecnZ21z02hnQkJCQKATofiW7duCTMzM7Ft2zYhxD+dFm9fbSNEZafO+uqcacj9WVBQIOzs7HSu1LzNFPbnp59+Kry8vIRGo9HOU1FRIWxtbcV3330nhJB2f9bVz+b06dOFn5+fUKlUQgjj/8z+2706FJ84cUI7bdu2bdV2KK6Ldpp8uHnllVeEo6Oj2Lt3r85llMXFxdp5xowZI/z9/cXu3bvFiRMnRIcOHUSHDh20r58/f164u7uLF198UWcdWVlZ2nluX4Y5ZcoUERcXJz777LN6vQzTEO3cvHmzWLFihTh//rxISkoSmzZtEi1atBCdOnUyinYaoo3/Vd0Ppinsy40bN4qvv/5anD9/Xly+fFksW7ZM2NjYiBkzZphUO4WovKQ/PDxcHDx4UJw/f1706dNHtGzZUvsLMy8vT3h6eoqXXnpJxMbGinXr1gkbG5t6u6zWkJ/b5cuXCysrq2ovuzWF/RkXFycUCoV45ZVXxMWLF0VsbKx48cUXhaOjo0hPTxdCSLs/DbUvP/zwQ3Hu3DkRGxsr5syZIywsLHSubGwIn9mMjAxx+vRp8fXXXwsAYv/+/eL06dPi1q1b2nmeeOIJER0dLY4ePSoOHDggmjVrpnMpeF220+TDDYBqHytXrtTOU1JSIsaOHSucnZ2FjY2NGDhwoMjIyNC+PnPmzGrXERAQoLOtPXv2iKioKGFpaSmCgoJ0tlHXDNHO3bt3iw4dOghHR0dhZWUlmjVrJt58880qv0ilaqch2vhfd/qro6Hvyy1btoioqChhZ2cnbG1tRevWrcUXX3wh1Gq1zrYaejuFqLxXxogRI4STk5NwcXERAwcO1LmUWAghzp49Kx5++GGhUChEkyZNxAcffFAfTRRCGPZz26FDB/HCCy/ccVumsD+3b98uOnXqJBwdHYWzs7N47LHHqhz1kGp/GqqNXbt21f6effDBB8Wff/5ZZVvG/pm90/fiv+e5deuWGDRokLCzsxMODg5i+PDhoqCgQGdbddVO2d8NISIiIjIJjaZDMRERETUODDdERERkUhhuiIiIyKQw3BAREZFJYbghIiIik8JwQ0RERCaF4YaIiIhMCsMNERERmRSGGyIySkIIdOvWDTExMVVeW7ZsGZycnHD9+nUJKiMiY8dwQ0RGSSaTYeXKlTh69Ci+/PJL7fSkpCRMnToVS5cu1Xuk4nupqKgw6PqISBoMN0RktPz8/PDJJ5/gjTfeQFJSEoQQGDlyJHr06IHo6Gj07NkTdnZ28PT0xEsvvYTs7Gztslu3bsXDDz8MJycnuLq6ok+fPrhy5Yr29eTkZMhkMvz444/o3LkzrKys8N1330nRTCIyMI4tRURGb8CAAcjPz8eTTz6JuXPn4sKFCwgPD8eoUaMwZMgQlJSU4M0334RKpcLu3bsBAL/++itkMhkiIyNRWFiIGTNmIDk5GWfOnIGZmRmSk5MRGBiIpk2bYtGiRYiOjoaVlRW8vb0lbi0R3S+GGyIyellZWQgPD0dOTg5+/fVXxMbG4q+//sK2bdu081y/fh1+fn5ISEhA8+bNq6wjOzsb7u7uOH/+PCIiIrThZsmSJXj99dfrszlEVMd4WoqIjJ6HhwdefvlltGjRAgMGDMDZs2exZ88e2NnZaR9hYWEAoD31dPnyZQwaNAhBQUFwcHBA06ZNAQApKSk6627Xrl29toWI6p651AUQEdWEubk5zM0rf2UVFhaib9++WLBgQZX5bp9W6tu3LwICAvD111/Dx8cHGo0GERERKC8v15nf1ta27osnonrFcENEDU6bNm3w66+/omnTptrA82+3bt1CQkICvv76azzyyCMAgAMHDtR3mUQkEZ6WIqIGZ9y4ccjJycGgQYNw/PhxXLlyBdu2bcPw4cOhVqvh7OwMV1dXfPXVV0hMTMTu3bsxadIkqcsmonrCcENEDY6Pjw8OHjwItVqNHj16oFWrVpgwYQKcnJxgZmYGMzMzrFu3DidPnkRERAQmTpyIhQsXSl02EdUTXi1FREREJoVHboiIiMikMNwQERGRSWG4ISIiIpPCcENEREQmheGGiIiITArDDREREZkUhhsiIiIyKQw3REREZFIYboiIiMikMNwQERGRSWG4ISIiIpPCcENEREQm5f8B/pGuPMg5troAAAAASUVORK5CYII=",
      "text/plain": [
       "<Figure size 640x480 with 1 Axes>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "# check the total of all countries in the dataframe population_projections to see whether it makes sense. so sum up all rows and check whether the sum is reasonable\n",
    "# Sum the population values for each year\n",
    "total_population = population_projections.sum()\n",
    "# plot the total population over the years\n",
    "plt.plot(total_population)\n",
    "plt.xlabel('Year')\n",
    "plt.ylabel('Total Population')\n",
    "plt.title('Total Population Projections')\n",
    "plt.show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "     Projections   Nowcasted\n",
      "AGO   35050000.0  35588987.0\n",
      "ALB    2848000.0   2777689.0\n",
      "ARE    9403000.0   9441129.0\n",
      "ARM    2783000.0   2780469.0\n",
      "AUS   26046000.0  26005540.0\n",
      "..           ...         ...\n",
      "WSM     221000.0    222382.0\n",
      "XKX    1658000.0   1761985.0\n",
      "ZAF   59647000.0  59893885.0\n",
      "ZMB   19746000.0  20017675.0\n",
      "ZWE   16153000.0  16320537.0\n",
      "\n",
      "[150 rows x 2 columns]\n",
      "     Projections   Nowcasted  Difference\n",
      "AGO   35050000.0  35588987.0   -1.514477\n",
      "ALB    2848000.0   2777689.0    2.531277\n",
      "ARE    9403000.0   9441129.0   -0.403861\n",
      "ARM    2783000.0   2780469.0    0.091028\n",
      "AUS   26046000.0  26005540.0    0.155582\n",
      "..           ...         ...         ...\n",
      "WSM     221000.0    222382.0   -0.621453\n",
      "XKX    1658000.0   1761985.0   -5.901583\n",
      "ZAF   59647000.0  59893885.0   -0.412204\n",
      "ZMB   19746000.0  20017675.0   -1.357176\n",
      "ZWE   16153000.0  16320537.0   -1.026541\n",
      "\n",
      "[150 rows x 3 columns]\n"
     ]
    }
   ],
   "source": [
    "# compare population projection in the year 2022 with the population data in the dataframe data_nowcasted_extended in the column \"population\" which is also for the year 2022. do they overlap?\n",
    "# Compare the population projections for 2022 with the population data in data_nowcasted_extended\n",
    "population_2022_projections = population_projections[2022]\n",
    "population_2022_nowcasted = data_nowcasted_extended.set_index('country_code')['population']\n",
    "population_comparison = pd.concat([population_2022_projections, population_2022_nowcasted], axis=1, keys=['Projections', 'Nowcasted'])\n",
    "print(population_comparison)\n",
    "# compute the percentage difference between the two values\n",
    "population_comparison['Difference'] = (population_comparison['Projections'] - population_comparison['Nowcasted']) / population_comparison['Nowcasted'] * 100\n",
    "# print the percentage difference\n",
    "print(population_comparison)\n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Last thing we do is we transform the population projections dataframe in a growthrate per year frame from 2023 to 2100 and save that"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Last thing we do is we transform the population projections dataframe in a growthrate per year frame from 2023 to 2100 and save that\n",
    "# Create a new DataFrame for population growth rates\n",
    "population_growth_rates = population_projections.diff(axis=1) / population_projections.shift(axis=1)\n",
    "# Drop the first column (2022) since it contains NaN values\n",
    "population_growth_rates = population_growth_rates.drop(columns=2022)\n",
    "# but then make sure that all the columns from 2023 to 2100 are renamed to 2022 to 2099\n",
    "population_growth_rates.columns = range(2022, 2100)\n",
    "# make sure the first column is called code\n",
    "population_growth_rates.index.name = 'code'\n",
    "# Save the DataFrame as a CSV file\n",
    "population_growth_rates.to_csv('./data/pip_all_data/population_growth_rates.csv')"
   ]
  }
 ],
 "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.11.7"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}