Global Risk Modeling Rebuild.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "95a7bbf7",
   "metadata": {},
   "source": [
    "# Global Risk Modeling (v2. under construction)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "3d05aceb",
   "metadata": {},
   "source": [
    "Goal: Rebuild original geopolitical instability model\n",
    "\n",
    "Improvements:\n",
    "1) Extract Data from text and clean\n",
    "2) Use mySQL to store cleared data\n",
    "4) Implement feature engineering to model interaction of variables\n",
    "5) Leverage Pytorch to better predict instability\n",
    "6) Improve mapping using GeoPandas\n",
    "\n",
    "Notes: \n",
    "1) I would like to retain gini coefficient (high priority)\n",
    "2) You only have data from 2020-2018, get rest of files from CIA WFB ad FSI\n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "530e5edc",
   "metadata": {},
   "source": [
    "In future I would like to retain gini coefficient "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "id": "da2946a7",
   "metadata": {},
   "outputs": [
    {
     "ename": "SyntaxError",
     "evalue": "invalid syntax (Temp/ipykernel_8692/3035467704.py, line 1)",
     "output_type": "error",
     "traceback": [
      "\u001b[1;36m  File \u001b[1;32m\"C:\\Users\\Ian\\AppData\\Local\\Temp/ipykernel_8692/3035467704.py\"\u001b[1;36m, line \u001b[1;32m1\u001b[0m\n\u001b[1;33m    pip install cairosvg\u001b[0m\n\u001b[1;37m        ^\u001b[0m\n\u001b[1;31mSyntaxError\u001b[0m\u001b[1;31m:\u001b[0m invalid syntax\n"
     ]
    }
   ],
   "source": [
    "#pip install cairosvg\n",
    "#pip install cffi #cairocff\n",
    "#pip install -i https://pypi.anaconda.org/t/$TOKEN/rmg/simple cairocffi"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "2abf9300",
   "metadata": {},
   "outputs": [],
   "source": [
    "# import necessary libraries\n",
    "import pandas as pd\n",
    "from string import digits\n",
    "import numpy as np\n",
    "from numpy import absolute\n",
    "from pandas import read_csv\n",
    "\n",
    "# Needed for text screening \n",
    "import glob\n",
    "import os\n",
    "\n",
    "#import matplotlib.pyplot as plt\n",
    "\n",
    "#import cairosvg\n",
    "\n",
    "# useful\n",
    "\n",
    "#pd.set_option('display.max_colwidth', None)\n",
    "#pd.set_option('display.max_columns', None)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "2ccacbca-d0c2-4aa8-ac5f-7acc40b364db",
   "metadata": {},
   "source": [
    "1) Extracting Data: The CIA archives its data from 2020 to 2000 in the form of text documents. To access this data the text documents need to be stripped of extraneous characters and formatted as dataframes. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "971e665c",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>name</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>China</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>United States</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Japan</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>France</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Germany</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>220</th>\n",
       "      <td>Kiribati</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>221</th>\n",
       "      <td>Congo, Democratic Republic of the</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>222</th>\n",
       "      <td>Guinea</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>223</th>\n",
       "      <td>Guinea-Bissau</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>224</th>\n",
       "      <td>South Sudan</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>225 rows × 1 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                                  name\n",
       "0                                China\n",
       "1                        United States\n",
       "2                                Japan\n",
       "3                               France\n",
       "4                              Germany\n",
       "..                                 ...\n",
       "220                           Kiribati\n",
       "221  Congo, Democratic Republic of the\n",
       "222                             Guinea\n",
       "223                      Guinea-Bissau\n",
       "224                        South Sudan\n",
       "\n",
       "[225 rows x 1 columns]"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# making a template for dataframe\n",
    "t = pd.read_table(\"C:\\\\Users\\\\Ian\\\\Desktop\\\\MSQE\\\\Global Risk Modeling\\\\WFB Arch\\\\2020\\\\rawdata_196.txt\")\n",
    "\n",
    "for i in t:\n",
    "    t[i] = t[i].str.lstrip(digits)\n",
    "    t[\"name\"] = t[i].str[:50].str.strip()\n",
    "\n",
    "    tem = t[\"name\"]   \n",
    "tem = pd.DataFrame(tem)\n",
    "tem\n",
    "#tem.set_index(\"name\", inplace=True)\n",
    "# creates a dataframe with country names as a template for merging"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "36f1b017-77e2-41c5-91c5-7ebb59244ad1",
   "metadata": {},
   "outputs": [],
   "source": [
    "######################## Creating temporary dataframes for 2018-2020 ###################################\n",
    "# Note: You need to integrate the text processing to remove the non-numeric characters\n",
    "\n",
    "# Creating a function to clean and organize the text data into dataframes\n",
    "def clean_txt(year,template): # you can store all of these in a dictionry of dataframes: dfs = {}\n",
    "    path = \"C:\\\\Users\\\\Ian\\\\Desktop\\\\MSQE\\\\Global Risk Modeling\\\\WFB Arch\\\\{}\".format(year) # path to files, can change year **** Make this able to itterate across all years in WFB Arch file \n",
    "    txt_files = glob.glob(os.path.join(path, \"*.txt\"))\n",
    "    tem2 = template\n",
    "    for f in txt_files:\n",
    "        test = pd.read_table(f)\n",
    "        for i in test:\n",
    "            test[i] = test[i].str.lstrip(digits) # gets rid of rank\n",
    "            test[\"name\"] = test[i].str[:50].str.strip()     # creates name \n",
    "            test[test.columns[0]] = test[i].str[50:]\n",
    "        tem2 = pd.merge(tem2,test, on = \"name\", how = \"outer\")\n",
    "    temp = tem2.iloc[:200,:]\n",
    "    return temp\n",
    "\n",
    "\n",
    "tem_2020 = clean_txt(2020, tem)\n",
    "tem_2019 = clean_txt(2019, tem)\n",
    "tem_2018 = clean_txt(2018, tem)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "8af165d4-be3a-40dc-ad62-a5cb50c7a549",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>name</th>\n",
       "      <th>Country Comparison :: Telephones - fixed lines</th>\n",
       "      <th>Country Comparison :: Telephones - mobile cellular</th>\n",
       "      <th>Country Comparison :: Internet users</th>\n",
       "      <th>Country Comparison :: Broadband - fixed subscriptions</th>\n",
       "      <th>Country Comparison :: GDP (purchasing power parity)</th>\n",
       "      <th>Country Comparison :: GDP - real growth rate</th>\n",
       "      <th>Country Comparison :: GDP - per capita (PPP)</th>\n",
       "      <th>Country Comparison :: Gross national saving</th>\n",
       "      <th>Country Comparison :: Industrial production growth rate</th>\n",
       "      <th>Country Comparison :: Labor force</th>\n",
       "      <th>Country Comparison :: Unemployment rate</th>\n",
       "      <th>Country Comparison :: Distribution of family income - Gini index</th>\n",
       "      <th>Country Comparison :: Taxes and other revenues</th>\n",
       "      <th>Country Comparison :: Budget surplus (+) or deficit (-)</th>\n",
       "      <th>Country Comparison :: Public debt</th>\n",
       "      <th>Country Comparison :: Inflation rate (consumer prices)</th>\n",
       "      <th>Country Comparison :: Central bank discount rate</th>\n",
       "      <th>Country Comparison :: Commercial bank prime lending rate</th>\n",
       "      <th>Country Comparison :: Stock of narrow money</th>\n",
       "      <th>Country Comparison :: Stock of broad money</th>\n",
       "      <th>Country Comparison :: Stock of domestic credit</th>\n",
       "      <th>Country Comparison :: Market value of publicly traded shares</th>\n",
       "      <th>Country Comparison :: Current account balance</th>\n",
       "      <th>Country Comparison :: Exports</th>\n",
       "      <th>Country Comparison :: Imports</th>\n",
       "      <th>Country Comparison :: Reserves of foreign exchange and gold</th>\n",
       "      <th>Country Comparison :: Debt - external</th>\n",
       "      <th>Country Comparison :: Stock of direct foreign investment - at home</th>\n",
       "      <th>Country Comparison :: Stock of direct foreign investment - abroad</th>\n",
       "      <th>Country Comparison :: Electricity - production</th>\n",
       "      <th>Country Comparison :: Electricity - consumption</th>\n",
       "      <th>Country Comparison :: Electricity - exports</th>\n",
       "      <th>Country Comparison :: Electricity - imports</th>\n",
       "      <th>Country Comparison :: Electricity - installed generating capacity</th>\n",
       "      <th>Country Comparison :: Electricity - from fossil fuels</th>\n",
       "      <th>Country Comparison :: Electricity - from nuclear fuels</th>\n",
       "      <th>Country Comparison :: Electricity - from hydroelectric plants</th>\n",
       "      <th>Country Comparison :: Electricity - from other renewable sources</th>\n",
       "      <th>Country Comparison :: Crude oil - production</th>\n",
       "      <th>Country Comparison :: Crude oil - exports</th>\n",
       "      <th>Country Comparison :: Crude oil - imports</th>\n",
       "      <th>Country Comparison :: Crude oil - proved reserves</th>\n",
       "      <th>Country Comparison :: Refined petroleum products - production</th>\n",
       "      <th>Country Comparison :: Refined petroleum products - consumption</th>\n",
       "      <th>Country Comparison :: Refined petroleum products - exports</th>\n",
       "      <th>Country Comparison :: Refined petroleum products - imports</th>\n",
       "      <th>Country Comparison :: Natural gas - production</th>\n",
       "      <th>Country Comparison :: Natural gas - consumption</th>\n",
       "      <th>Country Comparison :: Natural gas - exports</th>\n",
       "      <th>Country Comparison :: Natural gas - imports</th>\n",
       "      <th>Country Comparison :: Natural gas - proved reserves</th>\n",
       "      <th>Country Comparison :: Carbon dioxide emissions from consumption of energy</th>\n",
       "      <th>Country Comparison :: Area</th>\n",
       "      <th>Country Comparison :: Military expenditures</th>\n",
       "      <th>Country Comparison :: Population</th>\n",
       "      <th>Country Comparison :: Median age</th>\n",
       "      <th>Country Comparison :: Population growth rate</th>\n",
       "      <th>Country Comparison :: Birth rate</th>\n",
       "      <th>Country Comparison :: Death rate</th>\n",
       "      <th>Country Comparison :: Net migration rate</th>\n",
       "      <th>Country Comparison :: Maternal mortality rate</th>\n",
       "      <th>Country Comparison :: Infant mortality rate</th>\n",
       "      <th>Country Comparison :: Life expectancy at birth</th>\n",
       "      <th>Country Comparison :: Total fertility rate</th>\n",
       "      <th>Country Comparison :: Health expenditures</th>\n",
       "      <th>Country Comparison :: HIV/AIDS - adult prevalence rate</th>\n",
       "      <th>Country Comparison :: HIV/AIDS - people living with HIV/AIDS</th>\n",
       "      <th>Country Comparison :: HIV/AIDS - deaths</th>\n",
       "      <th>Country Comparison :: Obesity - adult prevalence rate</th>\n",
       "      <th>Country Comparison :: Children under the age of 5 years underweight</th>\n",
       "      <th>Country Comparison :: Education expenditures</th>\n",
       "      <th>Country Comparison :: Unemployment, youth ages 15-24</th>\n",
       "      <th>Country Comparison :: Airports</th>\n",
       "      <th>Country Comparison :: Railways</th>\n",
       "      <th>Country Comparison :: Roadways</th>\n",
       "      <th>Country Comparison :: Waterways</th>\n",
       "      <th>Country Comparison :: Merchant marine</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Afghanistan</td>\n",
       "      <td>118,769        2017 est.</td>\n",
       "      <td>23,929,713        2017 est.</td>\n",
       "      <td>3,531,770        July 2016 est.</td>\n",
       "      <td>16,810        2017 est.</td>\n",
       "      <td>$69,450,000,000        2017 est.</td>\n",
       "      <td>2.70        2017 est.</td>\n",
       "      <td>$2,000        2017 est.</td>\n",
       "      <td>22.70        2017 est.</td>\n",
       "      <td>-1.90        2016 est.</td>\n",
       "      <td>8,478,000        2017 est.</td>\n",
       "      <td>23.90        2017 est.</td>\n",
       "      <td>29.4        2008</td>\n",
       "      <td>11.2        2017 est.</td>\n",
       "      <td>-15.10        2017 est.</td>\n",
       "      <td>7.00        2017 est.</td>\n",
       "      <td>5.00        2017 est.</td>\n",
       "      <td>NaN</td>\n",
       "      <td>15.00        31 December ...</td>\n",
       "      <td>$6,644,000,000        31 December ...</td>\n",
       "      <td>$6,945,000,000        31 December ...</td>\n",
       "      <td>-$240,600,000        31 December 2...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>$1,014,000,000        2017 est.</td>\n",
       "      <td>$784,000,000        2017 est.</td>\n",
       "      <td>$7,616,000,000        2017 est.</td>\n",
       "      <td>$7,187,000,000        31 December ...</td>\n",
       "      <td>$2,840,000,000        FY/</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1,211,000,000        2016 est.</td>\n",
       "      <td>5,526,000,000        2016 est.</td>\n",
       "      <td>0        2016 est.</td>\n",
       "      <td>4,400,000,000        2016 est.</td>\n",
       "      <td>634,100.00        2016 est.</td>\n",
       "      <td>45.0        2016 est.</td>\n",
       "      <td>0.0        2017 est.</td>\n",
       "      <td>52.0        2017 est.</td>\n",
       "      <td>4.0        2017 est.</td>\n",
       "      <td>0        2017 est.</td>\n",
       "      <td>0        2015 est.</td>\n",
       "      <td>0        2015 est.</td>\n",
       "      <td>0        1 January 20...</td>\n",
       "      <td>0        2015 est.</td>\n",
       "      <td>35,000        2016 est.</td>\n",
       "      <td>0        2015 est.</td>\n",
       "      <td>34,210        2015 est.</td>\n",
       "      <td>164,200,000        2017 est.</td>\n",
       "      <td>164,200,000        2017 est.</td>\n",
       "      <td>0        2017 est.</td>\n",
       "      <td>0        2017 est.</td>\n",
       "      <td>49,550,000,000        1 January 20...</td>\n",
       "      <td>9,067,000.00        2017 est.</td>\n",
       "      <td>652,230.00</td>\n",
       "      <td>0.89        2016</td>\n",
       "      <td>34,940,837        July 2018 est.</td>\n",
       "      <td>19.0</td>\n",
       "      <td>2.37        2018 est.</td>\n",
       "      <td>37.50        2018 est.</td>\n",
       "      <td>13.20        2018 est.</td>\n",
       "      <td>-0.90        2017 est.</td>\n",
       "      <td>396        2015 est.</td>\n",
       "      <td>108.50        2018 est.</td>\n",
       "      <td>52.10        2018 est.</td>\n",
       "      <td>5.02        2018 est.</td>\n",
       "      <td>8.20        2014</td>\n",
       "      <td>NaN</td>\n",
       "      <td>7,500        2016 est.</td>\n",
       "      <td>NaN</td>\n",
       "      <td>5.50        2016</td>\n",
       "      <td>25.00        2013</td>\n",
       "      <td>3.20        2015</td>\n",
       "      <td>NaN</td>\n",
       "      <td>43        2016</td>\n",
       "      <td>NaN</td>\n",
       "      <td>42,150        2006</td>\n",
       "      <td>1,200        2011</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Akrotiri</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>123.00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1        2017</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>198</th>\n",
       "      <td>Saint Lucia</td>\n",
       "      <td>35,014        2017 est.</td>\n",
       "      <td>176,694        2017 est.</td>\n",
       "      <td>86,000        July 2016 est.</td>\n",
       "      <td>31,781        2017 est.</td>\n",
       "      <td>$2,542,000,000        2017 est.</td>\n",
       "      <td>3.00        2017 est.</td>\n",
       "      <td>$14,400        2017 est.</td>\n",
       "      <td>19.40        2017 est.</td>\n",
       "      <td>6.00        2017 est.</td>\n",
       "      <td>79,700        2012 est.</td>\n",
       "      <td>20.00        2003 est.</td>\n",
       "      <td>NaN</td>\n",
       "      <td>23.6        2017 est.</td>\n",
       "      <td>0.30        2017 est.</td>\n",
       "      <td>70.70        2017 est.</td>\n",
       "      <td>0.10        2017 est.</td>\n",
       "      <td>6.50        31 December ...</td>\n",
       "      <td>8.34        31 December 2...</td>\n",
       "      <td>$334,200,000        31 December 2...</td>\n",
       "      <td>$334,200,000        31 December 2...</td>\n",
       "      <td>$1,267,000,000        31 December 2...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>$21,000,000        2017 est.</td>\n",
       "      <td>$185,100,000        2017 est.</td>\n",
       "      <td>$600,000,000        2017 est.</td>\n",
       "      <td>$321,800,000        31 December 2...</td>\n",
       "      <td>$570,600,000        31 December 2...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>369,000,000        2016 est.</td>\n",
       "      <td>343,200,000        2016 est.</td>\n",
       "      <td>0        2016 est.</td>\n",
       "      <td>0        2016 est.</td>\n",
       "      <td>89,000.00        2016 est.</td>\n",
       "      <td>99.0        2016 est.</td>\n",
       "      <td>0.0        2017 est.</td>\n",
       "      <td>0.0        2017 est.</td>\n",
       "      <td>1.0        2017 est.</td>\n",
       "      <td>0        2017 est.</td>\n",
       "      <td>0        2015 est.</td>\n",
       "      <td>0        2015 est.</td>\n",
       "      <td>0        1 January 201...</td>\n",
       "      <td>0        2015 est.</td>\n",
       "      <td>3,100        2016 est.</td>\n",
       "      <td>0        2015 est.</td>\n",
       "      <td>3,113        2015 est.</td>\n",
       "      <td>0        2017 est.</td>\n",
       "      <td>0        2017 est.</td>\n",
       "      <td>0        2017 est.</td>\n",
       "      <td>0        2017 est.</td>\n",
       "      <td>0        1 January 201...</td>\n",
       "      <td>437,900.00        2017 est.</td>\n",
       "      <td>616.00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>165,510        July 2018 est.</td>\n",
       "      <td>35.5</td>\n",
       "      <td>0.31        2018 est.</td>\n",
       "      <td>13.10        2018 est.</td>\n",
       "      <td>7.80        2018 est.</td>\n",
       "      <td>-2.50        2017 est.</td>\n",
       "      <td>48        2015 est.</td>\n",
       "      <td>10.60        2018 est.</td>\n",
       "      <td>78.10        2018 est.</td>\n",
       "      <td>1.74        2018 est.</td>\n",
       "      <td>6.70        2014</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>19.70        2016</td>\n",
       "      <td>2.80        2012</td>\n",
       "      <td>5.70        2016</td>\n",
       "      <td>46.2        2016 est.</td>\n",
       "      <td>2        2013</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1,210        2011</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>199</th>\n",
       "      <td>Saint Martin</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1,100        July 2016 est.</td>\n",
       "      <td>NaN</td>\n",
       "      <td>$561,500,000        2005 est.</td>\n",
       "      <td>NaN</td>\n",
       "      <td>$19,300        2005 est.</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>17,300</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>54.40</td>\n",
       "      <td>NaN</td>\n",
       "      <td>32,284        July 2018 est.</td>\n",
       "      <td>32.8</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1        2013</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>200 rows × 78 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "             name Country Comparison :: Telephones - fixed lines  \\\n",
       "0     Afghanistan                       118,769        2017 est.   \n",
       "1        Akrotiri                                            NaN   \n",
       "..            ...                                            ...   \n",
       "198   Saint Lucia                        35,014        2017 est.   \n",
       "199  Saint Martin                                            NaN   \n",
       "\n",
       "    Country Comparison :: Telephones - mobile cellular  \\\n",
       "0                          23,929,713        2017 est.   \n",
       "1                                                  NaN   \n",
       "..                                                 ...   \n",
       "198                           176,694        2017 est.   \n",
       "199                                                NaN   \n",
       "\n",
       "                 Country Comparison :: Internet users  \\\n",
       "0                     3,531,770        July 2016 est.   \n",
       "1                                                 NaN   \n",
       "..                                                ...   \n",
       "198                      86,000        July 2016 est.   \n",
       "199                       1,100        July 2016 est.   \n",
       "\n",
       "    Country Comparison :: Broadband - fixed subscriptions  \\\n",
       "0                              16,810        2017 est.      \n",
       "1                                                  NaN      \n",
       "..                                                 ...      \n",
       "198                            31,781        2017 est.      \n",
       "199                                                NaN      \n",
       "\n",
       "    Country Comparison :: GDP (purchasing power parity)  \\\n",
       "0                     $69,450,000,000        2017 est.    \n",
       "1                                                  NaN    \n",
       "..                                                 ...    \n",
       "198                    $2,542,000,000        2017 est.    \n",
       "199                      $561,500,000        2005 est.    \n",
       "\n",
       "    Country Comparison :: GDP - real growth rate  \\\n",
       "0                          2.70        2017 est.   \n",
       "1                                            NaN   \n",
       "..                                           ...   \n",
       "198                        3.00        2017 est.   \n",
       "199                                          NaN   \n",
       "\n",
       "    Country Comparison :: GDP - per capita (PPP)  \\\n",
       "0                        $2,000        2017 est.   \n",
       "1                                            NaN   \n",
       "..                                           ...   \n",
       "198                     $14,400        2017 est.   \n",
       "199                     $19,300        2005 est.   \n",
       "\n",
       "     Country Comparison :: Gross national saving  \\\n",
       "0                         22.70        2017 est.   \n",
       "1                                            NaN   \n",
       "..                                           ...   \n",
       "198                       19.40        2017 est.   \n",
       "199                                          NaN   \n",
       "\n",
       "    Country Comparison :: Industrial production growth rate  \\\n",
       "0                               -1.90        2016 est.        \n",
       "1                                                  NaN        \n",
       "..                                                 ...        \n",
       "198                              6.00        2017 est.        \n",
       "199                                                NaN        \n",
       "\n",
       "               Country Comparison :: Labor force  \\\n",
       "0                     8,478,000        2017 est.   \n",
       "1                                            NaN   \n",
       "..                                           ...   \n",
       "198                      79,700        2012 est.   \n",
       "199                               17,300           \n",
       "\n",
       "        Country Comparison :: Unemployment rate  \\\n",
       "0                        23.90        2017 est.   \n",
       "1                                           NaN   \n",
       "..                                          ...   \n",
       "198                      20.00        2003 est.   \n",
       "199                                         NaN   \n",
       "\n",
       "    Country Comparison :: Distribution of family income - Gini index  \\\n",
       "0                                     29.4        2008                 \n",
       "1                                                  NaN                 \n",
       "..                                                 ...                 \n",
       "198                                                NaN                 \n",
       "199                                                NaN                 \n",
       "\n",
       "    Country Comparison :: Taxes and other revenues  \\\n",
       "0                            11.2        2017 est.   \n",
       "1                                              NaN   \n",
       "..                                             ...   \n",
       "198                          23.6        2017 est.   \n",
       "199                                            NaN   \n",
       "\n",
       "    Country Comparison :: Budget surplus (+) or deficit (-)  \\\n",
       "0                              -15.10        2017 est.        \n",
       "1                                                  NaN        \n",
       "..                                                 ...        \n",
       "198                              0.30        2017 est.        \n",
       "199                                                NaN        \n",
       "\n",
       "               Country Comparison :: Public debt  \\\n",
       "0                          7.00        2017 est.   \n",
       "1                                            NaN   \n",
       "..                                           ...   \n",
       "198                       70.70        2017 est.   \n",
       "199                                          NaN   \n",
       "\n",
       "    Country Comparison :: Inflation rate (consumer prices)  \\\n",
       "0                                5.00        2017 est.       \n",
       "1                                                  NaN       \n",
       "..                                                 ...       \n",
       "198                              0.10        2017 est.       \n",
       "199                                                NaN       \n",
       "\n",
       "      Country Comparison :: Central bank discount rate  \\\n",
       "0                                                  NaN   \n",
       "1                                                  NaN   \n",
       "..                                                 ...   \n",
       "198                        6.50        31 December ...   \n",
       "199                                                NaN   \n",
       "\n",
       "    Country Comparison :: Commercial bank prime lending rate  \\\n",
       "0                         15.00        31 December ...         \n",
       "1                                                  NaN         \n",
       "..                                                 ...         \n",
       "198                       8.34        31 December 2...         \n",
       "199                                                NaN         \n",
       "\n",
       "           Country Comparison :: Stock of narrow money  \\\n",
       "0                $6,644,000,000        31 December ...   \n",
       "1                                                  NaN   \n",
       "..                                                 ...   \n",
       "198               $334,200,000        31 December 2...   \n",
       "199                                                NaN   \n",
       "\n",
       "            Country Comparison :: Stock of broad money  \\\n",
       "0                $6,945,000,000        31 December ...   \n",
       "1                                                  NaN   \n",
       "..                                                 ...   \n",
       "198               $334,200,000        31 December 2...   \n",
       "199                                                NaN   \n",
       "\n",
       "        Country Comparison :: Stock of domestic credit  \\\n",
       "0                -$240,600,000        31 December 2...   \n",
       "1                                                  NaN   \n",
       "..                                                 ...   \n",
       "198             $1,267,000,000        31 December 2...   \n",
       "199                                                NaN   \n",
       "\n",
       "    Country Comparison :: Market value of publicly traded shares  \\\n",
       "0                                                  NaN             \n",
       "1                                                  NaN             \n",
       "..                                                 ...             \n",
       "198                                                NaN             \n",
       "199                                                NaN             \n",
       "\n",
       "    Country Comparison :: Current account balance  \\\n",
       "0                 $1,014,000,000        2017 est.   \n",
       "1                                             NaN   \n",
       "..                                            ...   \n",
       "198                  $21,000,000        2017 est.   \n",
       "199                                           NaN   \n",
       "\n",
       "                  Country Comparison :: Exports  \\\n",
       "0                 $784,000,000        2017 est.   \n",
       "1                                           NaN   \n",
       "..                                          ...   \n",
       "198               $185,100,000        2017 est.   \n",
       "199                                         NaN   \n",
       "\n",
       "                  Country Comparison :: Imports  \\\n",
       "0               $7,616,000,000        2017 est.   \n",
       "1                                           NaN   \n",
       "..                                          ...   \n",
       "198               $600,000,000        2017 est.   \n",
       "199                                         NaN   \n",
       "\n",
       "    Country Comparison :: Reserves of foreign exchange and gold  \\\n",
       "0                $7,187,000,000        31 December ...            \n",
       "1                                                  NaN            \n",
       "..                                                 ...            \n",
       "198               $321,800,000        31 December 2...            \n",
       "199                                                NaN            \n",
       "\n",
       "                 Country Comparison :: Debt - external  \\\n",
       "0                            $2,840,000,000        FY/   \n",
       "1                                                  NaN   \n",
       "..                                                 ...   \n",
       "198               $570,600,000        31 December 2...   \n",
       "199                                                NaN   \n",
       "\n",
       "    Country Comparison :: Stock of direct foreign investment - at home  \\\n",
       "0                                                  NaN                   \n",
       "1                                                  NaN                   \n",
       "..                                                 ...                   \n",
       "198                                                NaN                   \n",
       "199                                                NaN                   \n",
       "\n",
       "    Country Comparison :: Stock of direct foreign investment - abroad  \\\n",
       "0                                                  NaN                  \n",
       "1                                                  NaN                  \n",
       "..                                                 ...                  \n",
       "198                                                NaN                  \n",
       "199                                                NaN                  \n",
       "\n",
       "    Country Comparison :: Electricity - production  \\\n",
       "0                   1,211,000,000        2016 est.   \n",
       "1                                              NaN   \n",
       "..                                             ...   \n",
       "198                   369,000,000        2016 est.   \n",
       "199                                            NaN   \n",
       "\n",
       "    Country Comparison :: Electricity - consumption  \\\n",
       "0                    5,526,000,000        2016 est.   \n",
       "1                                               NaN   \n",
       "..                                              ...   \n",
       "198                    343,200,000        2016 est.   \n",
       "199                                             NaN   \n",
       "\n",
       "     Country Comparison :: Electricity - exports  \\\n",
       "0                             0        2016 est.   \n",
       "1                                            NaN   \n",
       "..                                           ...   \n",
       "198                           0        2016 est.   \n",
       "199                                          NaN   \n",
       "\n",
       "     Country Comparison :: Electricity - imports  \\\n",
       "0                 4,400,000,000        2016 est.   \n",
       "1                                            NaN   \n",
       "..                                           ...   \n",
       "198                           0        2016 est.   \n",
       "199                                          NaN   \n",
       "\n",
       "    Country Comparison :: Electricity - installed generating capacity  \\\n",
       "0                          634,100.00        2016 est.                  \n",
       "1                                                  NaN                  \n",
       "..                                                 ...                  \n",
       "198                         89,000.00        2016 est.                  \n",
       "199                                                NaN                  \n",
       "\n",
       "    Country Comparison :: Electricity - from fossil fuels  \\\n",
       "0                                45.0        2016 est.      \n",
       "1                                                  NaN      \n",
       "..                                                 ...      \n",
       "198                              99.0        2016 est.      \n",
       "199                                                NaN      \n",
       "\n",
       "    Country Comparison :: Electricity - from nuclear fuels  \\\n",
       "0                                 0.0        2017 est.       \n",
       "1                                                  NaN       \n",
       "..                                                 ...       \n",
       "198                               0.0        2017 est.       \n",
       "199                                                NaN       \n",
       "\n",
       "    Country Comparison :: Electricity - from hydroelectric plants  \\\n",
       "0                                52.0        2017 est.              \n",
       "1                                                  NaN              \n",
       "..                                                 ...              \n",
       "198                               0.0        2017 est.              \n",
       "199                                                NaN              \n",
       "\n",
       "    Country Comparison :: Electricity - from other renewable sources  \\\n",
       "0                                 4.0        2017 est.                 \n",
       "1                                                  NaN                 \n",
       "..                                                 ...                 \n",
       "198                               1.0        2017 est.                 \n",
       "199                                                NaN                 \n",
       "\n",
       "    Country Comparison :: Crude oil - production  \\\n",
       "0                             0        2017 est.   \n",
       "1                                            NaN   \n",
       "..                                           ...   \n",
       "198                           0        2017 est.   \n",
       "199                                          NaN   \n",
       "\n",
       "       Country Comparison :: Crude oil - exports  \\\n",
       "0                             0        2015 est.   \n",
       "1                                            NaN   \n",
       "..                                           ...   \n",
       "198                           0        2015 est.   \n",
       "199                                          NaN   \n",
       "\n",
       "       Country Comparison :: Crude oil - imports  \\\n",
       "0                             0        2015 est.   \n",
       "1                                            NaN   \n",
       "..                                           ...   \n",
       "198                           0        2015 est.   \n",
       "199                                          NaN   \n",
       "\n",
       "     Country Comparison :: Crude oil - proved reserves  \\\n",
       "0                             0        1 January 20...   \n",
       "1                                                  NaN   \n",
       "..                                                 ...   \n",
       "198                          0        1 January 201...   \n",
       "199                                                NaN   \n",
       "\n",
       "    Country Comparison :: Refined petroleum products - production  \\\n",
       "0                                   0        2015 est.              \n",
       "1                                                  NaN              \n",
       "..                                                 ...              \n",
       "198                                 0        2015 est.              \n",
       "199                                                NaN              \n",
       "\n",
       "    Country Comparison :: Refined petroleum products - consumption  \\\n",
       "0                              35,000        2016 est.               \n",
       "1                                                  NaN               \n",
       "..                                                 ...               \n",
       "198                             3,100        2016 est.               \n",
       "199                                                NaN               \n",
       "\n",
       "    Country Comparison :: Refined petroleum products - exports  \\\n",
       "0                                   0        2015 est.           \n",
       "1                                                  NaN           \n",
       "..                                                 ...           \n",
       "198                                 0        2015 est.           \n",
       "199                                                NaN           \n",
       "\n",
       "    Country Comparison :: Refined petroleum products - imports  \\\n",
       "0                              34,210        2015 est.           \n",
       "1                                                  NaN           \n",
       "..                                                 ...           \n",
       "198                             3,113        2015 est.           \n",
       "199                                                NaN           \n",
       "\n",
       "    Country Comparison :: Natural gas - production  \\\n",
       "0                     164,200,000        2017 est.   \n",
       "1                                              NaN   \n",
       "..                                             ...   \n",
       "198                             0        2017 est.   \n",
       "199                                            NaN   \n",
       "\n",
       "    Country Comparison :: Natural gas - consumption  \\\n",
       "0                      164,200,000        2017 est.   \n",
       "1                                               NaN   \n",
       "..                                              ...   \n",
       "198                              0        2017 est.   \n",
       "199                                             NaN   \n",
       "\n",
       "     Country Comparison :: Natural gas - exports  \\\n",
       "0                             0        2017 est.   \n",
       "1                                            NaN   \n",
       "..                                           ...   \n",
       "198                           0        2017 est.   \n",
       "199                                          NaN   \n",
       "\n",
       "     Country Comparison :: Natural gas - imports  \\\n",
       "0                             0        2017 est.   \n",
       "1                                            NaN   \n",
       "..                                           ...   \n",
       "198                           0        2017 est.   \n",
       "199                                          NaN   \n",
       "\n",
       "    Country Comparison :: Natural gas - proved reserves  \\\n",
       "0                49,550,000,000        1 January 20...    \n",
       "1                                                  NaN    \n",
       "..                                                 ...    \n",
       "198                          0        1 January 201...    \n",
       "199                                                NaN    \n",
       "\n",
       "    Country Comparison :: Carbon dioxide emissions from consumption of energy  \\\n",
       "0                        9,067,000.00        2017 est.                          \n",
       "1                                                  NaN                          \n",
       "..                                                 ...                          \n",
       "198                        437,900.00        2017 est.                          \n",
       "199                                                NaN                          \n",
       "\n",
       "             Country Comparison :: Area  \\\n",
       "0                    652,230.00           \n",
       "1                        123.00           \n",
       "..                                  ...   \n",
       "198                      616.00           \n",
       "199                       54.40           \n",
       "\n",
       "    Country Comparison :: Military expenditures  \\\n",
       "0                              0.89        2016   \n",
       "1                                           NaN   \n",
       "..                                          ...   \n",
       "198                                         NaN   \n",
       "199                                         NaN   \n",
       "\n",
       "                     Country Comparison :: Population  \\\n",
       "0                    34,940,837        July 2018 est.   \n",
       "1                                                 NaN   \n",
       "..                                                ...   \n",
       "198                     165,510        July 2018 est.   \n",
       "199                      32,284        July 2018 est.   \n",
       "\n",
       "       Country Comparison :: Median age  \\\n",
       "0                          19.0           \n",
       "1                                   NaN   \n",
       "..                                  ...   \n",
       "198                        35.5           \n",
       "199                        32.8           \n",
       "\n",
       "    Country Comparison :: Population growth rate  \\\n",
       "0                          2.37        2018 est.   \n",
       "1                                            NaN   \n",
       "..                                           ...   \n",
       "198                        0.31        2018 est.   \n",
       "199                                          NaN   \n",
       "\n",
       "                Country Comparison :: Birth rate  \\\n",
       "0                         37.50        2018 est.   \n",
       "1                                            NaN   \n",
       "..                                           ...   \n",
       "198                       13.10        2018 est.   \n",
       "199                                          NaN   \n",
       "\n",
       "                 Country Comparison :: Death rate  \\\n",
       "0                          13.20        2018 est.   \n",
       "1                                             NaN   \n",
       "..                                            ...   \n",
       "198                         7.80        2018 est.   \n",
       "199                                           NaN   \n",
       "\n",
       "       Country Comparison :: Net migration rate  \\\n",
       "0                        -0.90        2017 est.   \n",
       "1                                           NaN   \n",
       "..                                          ...   \n",
       "198                      -2.50        2017 est.   \n",
       "199                                         NaN   \n",
       "\n",
       "    Country Comparison :: Maternal mortality rate  \\\n",
       "0                            396        2015 est.   \n",
       "1                                             NaN   \n",
       "..                                            ...   \n",
       "198                           48        2015 est.   \n",
       "199                                           NaN   \n",
       "\n",
       "      Country Comparison :: Infant mortality rate  \\\n",
       "0                         108.50        2018 est.   \n",
       "1                                             NaN   \n",
       "..                                            ...   \n",
       "198                        10.60        2018 est.   \n",
       "199                                           NaN   \n",
       "\n",
       "    Country Comparison :: Life expectancy at birth  \\\n",
       "0                           52.10        2018 est.   \n",
       "1                                              NaN   \n",
       "..                                             ...   \n",
       "198                         78.10        2018 est.   \n",
       "199                                            NaN   \n",
       "\n",
       "      Country Comparison :: Total fertility rate  \\\n",
       "0                          5.02        2018 est.   \n",
       "1                                            NaN   \n",
       "..                                           ...   \n",
       "198                        1.74        2018 est.   \n",
       "199                                          NaN   \n",
       "\n",
       "    Country Comparison :: Health expenditures  \\\n",
       "0                            8.20        2014   \n",
       "1                                         NaN   \n",
       "..                                        ...   \n",
       "198                          6.70        2014   \n",
       "199                                       NaN   \n",
       "\n",
       "    Country Comparison :: HIV/AIDS - adult prevalence rate  \\\n",
       "0                                                  NaN       \n",
       "1                                                  NaN       \n",
       "..                                                 ...       \n",
       "198                                                NaN       \n",
       "199                                                NaN       \n",
       "\n",
       "    Country Comparison :: HIV/AIDS - people living with HIV/AIDS  \\\n",
       "0                               7,500        2016 est.             \n",
       "1                                                  NaN             \n",
       "..                                                 ...             \n",
       "198                                                NaN             \n",
       "199                                                NaN             \n",
       "\n",
       "    Country Comparison :: HIV/AIDS - deaths  \\\n",
       "0                                       NaN   \n",
       "1                                       NaN   \n",
       "..                                      ...   \n",
       "198                                     NaN   \n",
       "199                                     NaN   \n",
       "\n",
       "    Country Comparison :: Obesity - adult prevalence rate  \\\n",
       "0                                     5.50        2016      \n",
       "1                                                  NaN      \n",
       "..                                                 ...      \n",
       "198                                  19.70        2016      \n",
       "199                                                NaN      \n",
       "\n",
       "    Country Comparison :: Children under the age of 5 years underweight  \\\n",
       "0                                    25.00        2013                    \n",
       "1                                                  NaN                    \n",
       "..                                                 ...                    \n",
       "198                                   2.80        2012                    \n",
       "199                                                NaN                    \n",
       "\n",
       "    Country Comparison :: Education expenditures  \\\n",
       "0                               3.20        2015   \n",
       "1                                            NaN   \n",
       "..                                           ...   \n",
       "198                             5.70        2016   \n",
       "199                                          NaN   \n",
       "\n",
       "    Country Comparison :: Unemployment, youth ages 15-24  \\\n",
       "0                                                  NaN     \n",
       "1                                                  NaN     \n",
       "..                                                 ...     \n",
       "198                              46.2        2016 est.     \n",
       "199                                                NaN     \n",
       "\n",
       "             Country Comparison :: Airports Country Comparison :: Railways  \\\n",
       "0                            43        2016                            NaN   \n",
       "1                             1        2017                            NaN   \n",
       "..                                      ...                            ...   \n",
       "198                           2        2013                            NaN   \n",
       "199                           1        2013                            NaN   \n",
       "\n",
       "             Country Comparison :: Roadways  \\\n",
       "0                        42,150        2006   \n",
       "1                                       NaN   \n",
       "..                                      ...   \n",
       "198                       1,210        2011   \n",
       "199                                     NaN   \n",
       "\n",
       "            Country Comparison :: Waterways  \\\n",
       "0                         1,200        2011   \n",
       "1                                       NaN   \n",
       "..                                      ...   \n",
       "198                                     NaN   \n",
       "199                                     NaN   \n",
       "\n",
       "    Country Comparison :: Merchant marine  \n",
       "0                                     NaN  \n",
       "1                                     NaN  \n",
       "..                                    ...  \n",
       "198                                   NaN  \n",
       "199                                   NaN  \n",
       "\n",
       "[200 rows x 78 columns]"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# CIA Data from 2017 onwards is in txt format but is not labed in the txt file, can't use clean_text() function in current form \n",
    "# tem_2017 = clean_txt(2017, tem)\n",
    "# There appears to be a standard pattern to the file order accross years, if you can match order with columns from 2018 then you can create column names automatically\n",
    "#\n",
    "\n",
    "pd.set_option('display.max_columns', None)\n",
    "pd.set_option('display.max_rows', 5)\n",
    "tem_2018"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "8c481781-4cde-49bc-882f-cdaed0db041b",
   "metadata": {},
   "source": [
    "Instructions from CIA WFB:\n",
    "\n",
    "\"Format: The 2020 and earlier versions have been compressed in .Zip format; when uncompressed they are in standard HTML format.\n",
    "\n",
    "How to use the compressed versions of the publication: The files named index.htm and index.html open the publication’s home page. Navigate to the index file in the folder or directory you created using a file manager, such as Windows Explorer, then double-click it to open and begin using the publication.\"\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "18695d95-fb5c-4331-906a-444c0b60a736",
   "metadata": {},
   "outputs": [],
   "source": [
    "# "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "7268e45c-8f1e-4460-ae74-b1f12874807b",
   "metadata": {},
   "outputs": [],
   "source": [
    "####################################### Combine .csv files for 2021-2023 ##################################################\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 127,
   "id": "41c5593a",
   "metadata": {},
   "outputs": [],
   "source": [
    "# getting text files for 2020\n",
    "\n",
    "for f in txt_files_2020:\n",
    "    test = pd.read_table(f)\n",
    "    for i in test:\n",
    "        test[i] = test[i].str.lstrip(digits) # gets rid of rank\n",
    "        test[\"name\"] = test[i].str[:50].str.strip()     # creates name \n",
    "        test[test.columns[0]] = test[i].str[50:]\n",
    "    tem_2020 = pd.merge(tem_2020,test, on = \"name\", how = \"outer\")\n",
    "tem_2020 = tem_2020.iloc[:200,:] \n",
    "\n",
    "\n",
    "tem_2020\n",
    "\n",
    "\n",
    "#tem_2020[\"Country Comparison :: GDP real growth rate\"] = tem_2020[\"Country Comparison :: GDP real growth rate\"].str[:-9]\n",
    "\n",
    "# [:,5:18]\n",
    "att=tem_2020.iloc[:,5:18]\n",
    "for (name, data) in att.iteritems():\n",
    "    att[name] = data.str[:-9]\n",
    "    att[name] = data.str.strip('ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz .')\n",
    "    att[name]= (data.replace( '[\\$,)]','', regex=True )\n",
    "               .replace( '[(]','-',   regex=True ).astype(float))\n",
    "    \n",
    "tem_2020.iloc[:,5:18] = att\n",
    "\n",
    "# [:,20:32]\n",
    "att=tem_2020.iloc[:,20:32]\n",
    "for (name, data) in att.iteritems():\n",
    "    att[name] = data.str[:-9]\n",
    "    att[name] = data.str.strip('ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz .')\n",
    "    att[name]= (data.replace( '[\\$,)]','', regex=True )\n",
    "               .replace( '[(]','-',   regex=True ).astype(float))\n",
    "    \n",
    "tem_2020.iloc[:,20:32] = att\n",
    "\n",
    "# [:, 33:41]\n",
    "att=tem_2020.iloc[:, 33:41]\n",
    "for (name, data) in att.iteritems():\n",
    "    att[name] = data.str[:-9]\n",
    "    att[name] = data.str.strip('ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz .')\n",
    "    att[name]= (data.replace( '[\\$,)]','', regex=True )\n",
    "               .replace( '[(]','-',   regex=True ).astype(float))\n",
    "    \n",
    "tem_2020.iloc[:, 33:41] = att\n",
    "\n",
    "# [:, 47:52]\n",
    "att=tem_2020.iloc[:, 47:52]\n",
    "for (name, data) in att.iteritems():\n",
    "    att[name] = data.str[:-9]\n",
    "    att[name] = data.str.strip('ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz .')\n",
    "    att[name]= (data.replace( '[\\$,)]','', regex=True )\n",
    "               .replace( '[(]','-',   regex=True ).astype(float))\n",
    "    \n",
    "tem_2020.iloc[:, 47:52] = att\n",
    "\n",
    "# [:, 54:58]\n",
    "att=tem_2020.iloc[:, 54:58]\n",
    "for (name, data) in att.iteritems():\n",
    "    att[name] = data.str[:-9]\n",
    "    att[name] = data.str.strip('ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz .')\n",
    "    att[name]= (data.replace( '[\\$,)]','', regex=True )\n",
    "               .replace( '[(]','-',   regex=True ).astype(float))\n",
    "    \n",
    "tem_2020.iloc[:, 54:58] = att\n",
    "\n",
    "# Country Comparison :: Carbon dioxide emissions from consumption of energy\n",
    "tem_2020[\"Country Comparison :: Carbon dioxide emissions from consumption of energy\"] = tem_2020[\"Country Comparison :: Carbon dioxide emissions from consumption of energy\"].str[:-9]\n",
    "\n",
    "# [:, 18:20]\n",
    "att=tem_2020.iloc[:, 18:20]\n",
    "for (name, data) in att.iteritems():\n",
    "    att[name] = data.str[:-9]\n",
    "    att[name] = data.str.strip('ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz .$')\n",
    "    att[name] = data.str[:-2]\n",
    "#    att[name]= (data.replace( '[\\$,)]','', regex=True )\n",
    "#               .replace( '[(]','-',   regex=True ).astype(float))\n",
    "tem_2020.iloc[:, 18:20] = att\n",
    "\n",
    "\n",
    "\n",
    "# \"Country Comparison :: Crude oil - proved reserves\"\n",
    "tem_2020[\"Country Comparison :: Crude oil - proved reserves\"] = tem_2020[\"Country Comparison :: Crude oil - proved reserves\"].str[:-17]\n",
    "\n",
    "tem_2020\n",
    "\n",
    "# \"Country Comparison :: Population\"\n",
    "tem_2020[\"Country Comparison :: Population\"] = tem_2020[\"Country Comparison :: Population\"].str[:-18]\n",
    "\n",
    "# Country Comparison :: Natural gas - proved reserve\n",
    "tem_2020[\"Country Comparison :: Natural gas - proved reserves\"] = tem_2020[\"Country Comparison :: Natural gas - proved reserves\"].str[:-14]\n",
    "\n",
    "# Country Comparison :: Roadways\n",
    "tem_2020[\"Country Comparison :: Roadways\"] = tem_2020[\"Country Comparison :: Roadways\"].str[:-6]\n",
    "\n",
    "# Ugly fix for Somalia \"800\" (note this missassignes a gdp per capita to a country thtat is later dropped)\n",
    "tem_2020[\"Country Comparison :: GDP - per capita (PPP)\"].fillna(value =800, inplace = True)\n",
    "\n",
    "# Creating GDP\n",
    "tem_2020[\"Country Comparison :: Population\"] = tem_2020[\"Country Comparison :: Population\"].replace( '[\\$,)]','', regex=True ).replace( '[(]','-',   regex=True ).astype(float)\n",
    "tem_2020[\"Country Comparison :: GDP (purchasing power parity)\"]= tem_2020[\"Country Comparison :: GDP - per capita (PPP)\"]* tem_2020[\"Country Comparison :: Population\"]\n",
    "\n",
    "# Country Comparison :: Natural gas - proved reserves\n",
    "tem_2020[\"Country Comparison :: Natural gas - proved reserves\"] = tem_2020[\"Country Comparison :: Natural gas - proved reserves\"].str[:-6]\n",
    "\n",
    "# Fixing \"Country Comparison :: Median age\"\n",
    "tem_2020[\"Country Comparison :: Median age\"] = tem_2020[\"Country Comparison :: Median age\"].str.strip('ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz .$')\n",
    "\n",
    "# Fixing Country Comparison :: Obesity - adult prevalence rate\n",
    "tem_2020[\"Country Comparison :: Obesity - adult prevalence rate\"] = tem_2020[\"Country Comparison :: Obesity - adult prevalence rate\"].str[:-4]\n",
    "\n",
    "# Fixing Country Comparison :: Waterways\n",
    "tem_2020[\"Country Comparison :: Waterways\"] = tem_2020[\"Country Comparison :: Waterways\"].str[:-4]\n",
    "\n",
    "# Country Comparison :: Airports has date at end 2020\n",
    "tem_2020[\"Country Comparison :: Airports\"] = tem_2020[\"Country Comparison :: Airports\"].str[:-4]\n",
    "\n",
    "# Country Comparison :: Railways has date at end 2020\n",
    "tem_2020[\"Country Comparison :: Railways\"] = tem_2020[\"Country Comparison :: Railways\"].str[:-4]\n",
    "\n",
    "\n",
    "# fragility index 2020\n",
    "frag_2020 = pd.read_csv(\"C:\\\\Users\\\\Ian\\\\Desktop\\\\MSQE\\\\Global Risk Modeling\\\\fsi-2020.csv\") #,encoding = 'latin1'\n",
    "frag_2020 = frag_2020[[\"Country\",\"Year\",\"Total\"]]\n",
    "frag_2020 = frag_2020.iloc[0:200,:]\n",
    "frag_2020 = frag_2020.dropna()\n",
    "frag_2020 = pd.DataFrame(frag_2020)\n",
    "frag_2020 = frag_2020.rename(columns={\"Country\":\"name\"})\n",
    "\n",
    "# No Roads\n",
    "tem_2020.iloc[85,64] = 17400\n",
    "tem_2020.iloc[65,64] = 1000\n",
    "\n",
    "#dataframe \n",
    "df_2020 = pd.merge(frag_2020,tem_2020,on=[\"name\"], how='inner')\n",
    "\n",
    "df_2020 = df_2020.rename(columns={\"Total\":\"Instability_Num\"})\n",
    "\n",
    "# fragility index 2020\n",
    "frag_2020 = pd.read_csv(\"C:\\\\Users\\\\Ian\\\\Desktop\\\\Global Risk Modeling\\\\fsi-2020.csv\") #,encoding = 'latin1'\n",
    "frag_2020 = frag_2020[[\"Country\",\"Year\",\"Total\"]]\n",
    "frag_2020 = frag_2020.iloc[0:200,:]\n",
    "frag_2020 = frag_2020.dropna()\n",
    "frag_2020 = pd.DataFrame(frag_2020)\n",
    "frag_2020 = frag_2020.rename(columns={\"Country\":\"name\"})\n",
    "\n",
    "# No Roads\n",
    "tem_2020.iloc[85,64] = 17400\n",
    "tem_2020.iloc[65,64] = 1000\n",
    "\n",
    "#dataframe \n",
    "df_2020 = pd.merge(frag_2020,tem_2020,on=[\"name\"], how='inner')\n",
    "\n",
    "df_2020 = df_2020.rename(columns={\"Total\":\"Instability_Num\"})\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "66a6b947-bc33-4b55-be0a-cff3a5910ede",
   "metadata": {},
   "outputs": [],
   "source": [
    "# "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "7f01be7f-7c23-48cb-a275-da10dcee3713",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 128,
   "id": "cfc650ae",
   "metadata": {},
   "outputs": [],
   "source": [
    "# getting text files for 2019\n",
    "################ This has 10 extra columns on financials #########################################\n",
    "pd.set_option('display.max_columns', None)\n",
    "path = \"C:\\\\Users\\\\Ian\\\\Desktop\\\\Global Risk Modeling\\\\WFB Arch\\\\2019\"\n",
    "txt_files_2019 = glob.glob(os.path.join(path, \"*.txt\"))\n",
    "tem_2019 = tem\n",
    "\n",
    "# loop over the list of csv files\n",
    "for f in txt_files_2019:\n",
    "    test = pd.read_table(f)\n",
    "    for i in test:\n",
    "        test[i] = test[i].str.lstrip(digits) # gets rid of rank\n",
    "        test[\"name\"] = test[i].str[:50].str.strip()     # creates name \n",
    "        test[test.columns[0]] = test[i].str[50:]\n",
    "    tem_2019 = pd.merge(tem_2019,test, on = \"name\", how = \"outer\")\n",
    "tem_2019 = tem_2019.iloc[:200,:] \n",
    "\n",
    "\n",
    "\n",
    "\n",
    "\n",
    "# 2019 data processing \n",
    "\n",
    "\n",
    "# Remove letters\n",
    "att=tem_2019.iloc[:,1:]\n",
    "for (name, data) in att.iteritems():\n",
    "    att[name] = data.str.strip('ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz .$')   \n",
    "tem_2019.iloc[:,1:] = att\n",
    "\n",
    "# Remove dates [:,5:52]\n",
    "att=tem_2019.iloc[:,5:52]\n",
    "for (name, data) in att.iteritems():\n",
    "    att[name] = data.str[:-5]\n",
    "    att[name] = data.str.strip('ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz .')\n",
    "tem_2019.iloc[:,5:52] = att\n",
    "\n",
    "# Remove dates [:,17:22]\n",
    "att=tem_2019.iloc[:,17:22]\n",
    "for (name, data) in att.iteritems():\n",
    "    att[name] = data.str[:-2]   \n",
    "tem_2019.iloc[:,17:22] = att\n",
    "\n",
    "# \"Country Comparison :: Market value of publicly traded shares\"\n",
    "tem_2019.iloc[1:,22] = tem_2019.iloc[1:,22].str[:-3]\n",
    "\n",
    "# Remove dates [:,26:30]\n",
    "att=tem_2019.iloc[:,26:30]\n",
    "for (name, data) in att.iteritems():\n",
    "    att[name] = data.str[:-5]   \n",
    "tem_2019.iloc[:,26:30] = att\n",
    "\n",
    "# 42 : \"Country Comparison :: Crude oil - proved reserves\"\n",
    "tem_2019[\"Country Comparison :: Crude oil - proved reserves\"] = tem_2019[\"Country Comparison :: Crude oil - proved reserves\"].str[:-1]\n",
    "\n",
    "# 51 : \"Country Comparison :: Natural gas - proved reserves\"\n",
    "tem_2019[\"Country Comparison :: Natural gas - proved reserves\"] = tem_2019[\"Country Comparison :: Natural gas - proved reserves\"].str[:-1]\n",
    "\n",
    "# 52 : Country Comparison :: Carbon dioxide emissions from consumption of energy\n",
    "tem_2019[\"Country Comparison :: Carbon dioxide emissions from consumption of energy\"] = tem_2019[\"Country Comparison :: Carbon dioxide emissions from consumption of energy\"].str[:-4]\n",
    "\n",
    "# Fix Afganistan\n",
    "tem_2019.iloc[132,1] = tem_2019.iloc[132,1][:-5].strip()\n",
    "    \n",
    "# Remove Dates [:,54:70]\n",
    "att=tem_2019.iloc[:,54:71]\n",
    "for (name, data) in att.iteritems():\n",
    "    att[name] = data.str[:-4]\n",
    "    att[name] = data.str.strip('ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz .')\n",
    "tem_2019.iloc[:,54:71] = att\n",
    "\n",
    "# Remove Dates [:,73:76]\n",
    "att=tem_2019.iloc[:,73:76]\n",
    "for (name, data) in att.iteritems():\n",
    "    att[name] = data.str[:-4]\n",
    "tem_2019.iloc[:,73:76] = att\n",
    "\n",
    "# Remove Date \"Country Comparison :: Airports\"\n",
    "tem_2019[\"Country Comparison :: Airports\"] = tem_2019[\"Country Comparison :: Airports\"].str[:-4]\n",
    "\n",
    "# GDP as float\n",
    "tem_2019.rename(columns= {\"Country Comparison :: GDP - real growth rate\":\"Country Comparison :: GDP real growth rate\"}, inplace = True)\n",
    "\n",
    "# # Creating GDP\n",
    "tem_2019[\"Country Comparison :: Population\"] = tem_2019[\"Country Comparison :: Population\"].replace( '[\\$,)]','', regex=True ).replace( '[(]','-',   regex=True ).astype(float)\n",
    "tem_2019[\"Country Comparison :: GDP - per capita (PPP)\"] = tem_2019[\"Country Comparison :: GDP - per capita (PPP)\"].replace( '[\\$,)]','', regex=True ).replace( '[(]','-',   regex=True ).astype(float)\n",
    "tem_2019[\"Country Comparison :: GDP (purchasing power parity)\"]= tem_2019[\"Country Comparison :: GDP - per capita (PPP)\"]* tem_2019[\"Country Comparison :: Population\"]\n",
    "\n",
    "# Ugly fix for Somalia \"800\" (note this missassignes a gdp per capita to a country thtat is later dropped)\n",
    "tem_2019[\"Country Comparison :: GDP - per capita (PPP)\"].fillna(value =800, inplace = True)\n",
    "\n",
    "# fragility index 2019\n",
    "frag_2019 = pd.read_csv(\"C:\\\\Users\\\\Ian\\\\Desktop\\\\Global Risk Modeling\\\\fsi-2019.csv\") #,encoding = 'latin1'\n",
    "frag_2019 = frag_2019[[\"Country\",\"Year\",\"Total\"]]\n",
    "frag_2019 = frag_2019.iloc[0:200,:]\n",
    "frag_2019 = frag_2019.dropna()\n",
    "frag_2019 = pd.DataFrame(frag_2019)\n",
    "frag_2019 = frag_2019.rename(columns={\"Country\":\"name\"})\n",
    "\n",
    "# No Roads\n",
    "tem_2019.iloc[85,74] = 17400\n",
    "tem_2019.iloc[65,74] = 1000\n",
    "\n",
    "#Merger\n",
    "df_2019 = pd.merge(frag_2019,tem_2019,on=[\"name\"], how='inner')\n",
    "\n",
    "df_2019 = df_2019.rename(columns={\"Total\":\"Instability_Num\"})\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 129,
   "id": "8ae81067",
   "metadata": {},
   "outputs": [],
   "source": [
    "# getting text files for 2018\n",
    "################ This has 10 extra columns on financials #########################################\n",
    "pd.set_option('display.max_columns', None)\n",
    "path = \"C:\\\\Users\\\\Ian\\\\Desktop\\\\Global Risk Modeling\\\\WFB Arch\\\\2018\"\n",
    "txt_files_2018 = glob.glob(os.path.join(path, \"*.txt\"))\n",
    "tem_2018 = tem\n",
    "\n",
    "# loop over the list of csv files\n",
    "for f in txt_files_2018:\n",
    "    test = pd.read_table(f)\n",
    "    for i in test:\n",
    "        test[i] = test[i].str.lstrip(digits) # gets rid of rank\n",
    "        test[\"name\"] = test[i].str[:50].str.strip()     # creates name \n",
    "        test[test.columns[0]] = test[i].str[50:]\n",
    "    tem_2018 = pd.merge(tem_2018,test, on = \"name\", how = \"outer\")\n",
    "tem_2018 = tem_2018.iloc[:200,:] \n",
    "\n",
    "\n",
    "\n",
    "\n",
    "# Remove letters\n",
    "att=tem_2018.iloc[:,1:]\n",
    "for (name, data) in att.iteritems():\n",
    "    att[name] = data.str.strip('ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz .$')   \n",
    "tem_2018.iloc[:,1:] = att\n",
    "\n",
    "# Remove dates [:,5:52]\n",
    "att=tem_2018.iloc[:,1:52]\n",
    "for (name, data) in att.iteritems():\n",
    "    att[name] = data.str[:-5]\n",
    "    att[name] = data.str.strip('ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz .')\n",
    "tem_2018.iloc[:,1:52] = att\n",
    "\n",
    "# Remove dates [:,17:23]\n",
    "att=tem_2018.iloc[:,17:22]\n",
    "for (name, data) in att.iteritems():\n",
    "    att[name] = data.str[:-2]   \n",
    "tem_2018.iloc[:,17:22] = att\n",
    "\n",
    "# \"Country Comparison :: Market value of publicly traded shares\"\n",
    "tem_2018.iloc[1:,22] = tem_2018.iloc[1:,22].str[:-3]\n",
    "\n",
    "# Remove dates [:,26:30]\n",
    "att=tem_2018.iloc[:,26:30]\n",
    "for (name, data) in att.iteritems():\n",
    "    att[name] = data.str[:-5]   \n",
    "tem_2018.iloc[:,26:30] = att\n",
    "\n",
    "# 42 : \"Country Comparison :: Crude oil - proved reserves\"\n",
    "tem_2018[\"Country Comparison :: Crude oil - proved reserves\"] = tem_2018[\"Country Comparison :: Crude oil - proved reserves\"].str[:-1]\n",
    "\n",
    "# 51 : \"Country Comparison :: Natural gas - proved reserves\"\n",
    "tem_2018[\"Country Comparison :: Natural gas - proved reserves\"] = tem_2018[\"Country Comparison :: Natural gas - proved reserves\"].str[:-1]\n",
    "\n",
    "# 52 : Country Comparison :: Carbon dioxide emissions from consumption of energy\n",
    "tem_2018[\"Country Comparison :: Carbon dioxide emissions from consumption of energy\"] = tem_2018[\"Country Comparison :: Carbon dioxide emissions from consumption of energy\"].str[:-4]\n",
    "\n",
    "#53\n",
    "tem_2018.iloc[1,53] = tem_2018.iloc[1,53][:-4]\n",
    "\n",
    "# 55\n",
    "# Remove dates [:,55]\n",
    "att=tem_2018.iloc[:,54:56]\n",
    "for (name, data) in att.iteritems():\n",
    "    att[name] = data.str[:-5]\n",
    "    att[name] = data.str.strip('ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz .')\n",
    "tem_2018.iloc[:,54:56] = att\n",
    "\n",
    "# 57\n",
    "# Remove dates [:,57]\n",
    "att=tem_2018.iloc[:,57:]\n",
    "for (name, data) in att.iteritems():\n",
    "    att[name] = data.str[:-5]\n",
    "    att[name] = data.str.strip('ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz .')\n",
    "tem_2018.iloc[:,57:] = att\n",
    "\n",
    "\n",
    "tem_2018.rename(columns= {\"Country Comparison :: GDP - real growth rate\":\"Country Comparison :: GDP real growth rate\"}, inplace = True)\n",
    "tem_2018\n",
    "\n",
    "\n",
    "#pop as float\n",
    "tem_2018[\"Country Comparison :: Population\"] = tem_2018[\"Country Comparison :: Population\"].replace( '[\\$,)]','', regex=True ).replace( '[(]','-',   regex=True ).astype(float)\n",
    "\n",
    "# labor as float Country Comparison :: Labor force\n",
    "tem_2018[\"Country Comparison :: Labor force\"] = tem_2018[\"Country Comparison :: Labor force\"].replace( '[\\$,)]','', regex=True ).replace( '[(]','-',   regex=True ).astype(float)\n",
    "\n",
    "# as float Country Comparison :: GDP - per capita (PPP)\n",
    "tem_2018[\"Country Comparison :: GDP - per capita (PPP)\"] = tem_2018[\"Country Comparison :: GDP - per capita (PPP)\"].replace( '[\\$,)]','', regex=True ).replace( '[(]','-',   regex=True ).astype(float)\n",
    "\n",
    "\n",
    "\n",
    "# fragility index 2018\n",
    "frag_2018 = pd.read_csv(\"C:\\\\Users\\\\Ian\\\\Desktop\\\\Global Risk Modeling\\\\fsi-2018.csv\") #,encoding = 'latin1'\n",
    "frag_2018 = frag_2018[[\"Country\",\"Year\",\"Total\"]]\n",
    "frag_2018 = frag_2018.iloc[0:200,:]\n",
    "frag_2018 = frag_2018.dropna()\n",
    "frag_2018 = pd.DataFrame(frag_2018)\n",
    "frag_2018 = frag_2018.rename(columns={\"Country\":\"name\"})\n",
    "\n",
    "#Merger\n",
    "df_2018 = pd.merge(frag_2018,tem_2018,on=[\"name\"], how='inner')\n",
    "\n",
    "df_2018 = df_2018.rename(columns={\"Total\":\"Instability_Num\"})\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 130,
   "id": "7e81acac",
   "metadata": {},
   "outputs": [],
   "source": [
    "# concatinating the different years\n",
    "\n",
    "frames = [df_2020, df_2019, df_2018]\n",
    "df = pd.concat(frames, keys=['2020', '2019', '2018'])\n",
    "\n",
    "df = df.rename(columns={\"name\":\"Country\"})\n",
    "\n",
    "#df_G = df[[\"Country\",\"Country Comparison :: Distribution of family income - Gini index\"]]\n",
    "\n",
    "df = df.loc[:, ~df.columns.isin(['Country Comparison :: Health expenditures', \n",
    "                                 'Country Comparison :: Stock of direct foreign investment - abroad', \n",
    "                                 \"Country Comparison :: Stock of direct foreign investment - at home\",\n",
    "                                 \"Country Comparison :: Market value of publicly traded shares\", \n",
    "                                 \"Country Comparison :: Stock of domestic credit\",\n",
    "                                \"Country Comparison :: Stock of broad money\",\n",
    "                                \"Country Comparison :: Stock of narrow money\",\n",
    "                                \"Country Comparison :: Commercial bank prime lending rate\",\n",
    "                                \"Country Comparison :: Central bank discount rate\",\n",
    "                                \"Country Comparison :: Distribution of family income - Gini index\"])]\n",
    "\n",
    "# Fixing waterways \n",
    "df[\"Country Comparison :: Waterways\"].fillna(value = 0, inplace = True)\n",
    "\n",
    "#Fixing rail roads\n",
    "df[\"Country Comparison :: Railways\"].fillna(value = 0, inplace = True)\n",
    "\n",
    "#Fixing Merchant Marine\n",
    "df[\"Country Comparison :: Merchant marine\"].fillna(value = 0, inplace = True)\n",
    "\n",
    "# Fixing Oil\n",
    "df[\"Country Comparison :: Crude oil - proved reserves\"] = df[\"Country Comparison :: Crude oil - proved reserves\"].str[:-2]\n",
    "\n",
    "# Filling Roads\n",
    "df[\"Country Comparison :: Roadways\"].fillna(value = 0, inplace = True)\n",
    "\n",
    "# Uggly fix for samoa and Somalia\n",
    "df[\"Country Comparison :: Gross national saving\"].fillna(value = 0, inplace = True)\n",
    "\n",
    "# Country Comparison :: Natural gas - proved reserves\n",
    "df[\"Country Comparison :: Natural gas - proved reserves\"].fillna(value = 0, inplace = True)\n",
    "\n",
    "\n",
    "df= df.drop([\"Country Comparison :: Children under the age of 5 years underweight\", \n",
    "             \"Country Comparison :: Education expenditures\", \"Country Comparison :: Unemployment, youth ages 15-24\",\n",
    "            \"Country Comparison :: HIV/AIDS - deaths\", \"Country Comparison :: Military expenditures\"], axis=1)\n",
    "\n",
    "# Cleaning hold out dates\n",
    "df.iloc[222,5:6] = df.iloc[222,5:6].str[:-5]\n",
    "df.iloc[158,6:7] = df.iloc[158,6:7].str[:-5]\n",
    "\n",
    "df.iloc[31,43]  = 0\n",
    "df.iloc[69,43]  = 0\n",
    "df.iloc[93,43]  = 0\n",
    "df.iloc[103,43]  = 0\n",
    "df.iloc[122,43]  = 0\n",
    "df.iloc[126,43]  = 0\n",
    "df.iloc[125,43]  = 0\n",
    "df.iloc[131,43]  = 0\n",
    "df.iloc[189,43]  = 0\n",
    "df.iloc[219,43]  = 0\n",
    "df.iloc[246,43]  = 0\n",
    "df.iloc[259,43]  = 0\n",
    "df.iloc[279,43]  = 0\n",
    "df.iloc[284,43]  = 0\n",
    "df.iloc[279,43]  = 0\n",
    "df.iloc[275,43]  = 0\n",
    "df.iloc[343,43]  = 0\n",
    "df.iloc[373,43]  = 0\n",
    "df.iloc[402,43]  = 0\n",
    "df.iloc[417,43]  = 0\n",
    "df.iloc[433,43]  = 0\n",
    "df.iloc[430,43]  = 0\n",
    "df.iloc[439,43]  = 0\n",
    "df.iloc[0,47:48] = df.iloc[0,47:48].str[:-5]\n",
    "\n",
    "#not run\n",
    "# making floats\n",
    "# this works\n",
    "att=df.iloc[:,3:7]\n",
    "for (name, data) in att.iteritems():\n",
    "    att[name] = data.str.strip('ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz .')\n",
    "    att[name]= (data.replace( '[\\$,)]','', regex=True ).astype(float))\n",
    "df.iloc[:,3:7] = att\n",
    "\n",
    "#\n",
    "att=df.iloc[:154,20:22]\n",
    "for (name, data) in att.iteritems():\n",
    "    att[name]= (data.replace( '[\\$,)]','', regex=True ).astype(float))\n",
    "df.iloc[:154,20:22] = att\n",
    "\n",
    "#\n",
    "att=df.iloc[154:,17:25]\n",
    "for (name, data) in att.iteritems():\n",
    "    att[name]= (data.replace( '[\\$,)]','', regex=True ).astype(float))\n",
    "df.iloc[154:,17:25] = att\n",
    "\n",
    "#\n",
    "att=df.iloc[154:,31:34]\n",
    "for (name, data) in att.iteritems():\n",
    "    att[name]= (data.replace( '[\\$,)]','', regex=True ).astype(float))\n",
    "df.iloc[154:,31:34] = att\n",
    "\n",
    "#\n",
    "df.iloc[:154,34]=df.iloc[:154,34].replace( '[\\$,)]','', regex=True ).astype(float)\n",
    "\n",
    "#\n",
    "att=df.iloc[154:,34:44]\n",
    "for (name, data) in att.iteritems():\n",
    "    att[name]= (data.replace( '[\\$,)]','', regex=True ).astype(float))\n",
    "df.iloc[154:,34:44] = att\n",
    "\n",
    "# \n",
    "df[\"Country Comparison :: Area\"]=df[\"Country Comparison :: Area\"].replace( '[\\$,)]','', regex=True ).astype(float)\n",
    "\n",
    "df.iloc[164,54] = np.NaN\n",
    "#\n",
    "df.iloc[:319,54] = np.NaN\n",
    "#\n",
    "df.iloc[450:,60] = np.NaN\n",
    "#\n",
    "df.iloc[392,63] = np.NaN\n",
    "#\n",
    "att=df.iloc[:,44:]\n",
    "for (name, data) in att.iteritems():\n",
    "    att[name]= (data.replace( '[\\$,)]','', regex=True ).astype(float))\n",
    "df.iloc[:,44:] = att\n",
    "\n",
    "############ 46 is already float\n",
    "df[\"Country Comparison :: Railways\"].fillna(value = 0, inplace = True)\n",
    "df[\"Country Comparison :: Merchant marine\"].fillna(value = 0, inplace = True)\n",
    "\n",
    "df = df.drop([\"Country Comparison :: Life expectancy at birth\", \"Country Comparison :: HIV/AIDS - people living with HIV/AIDS\",\n",
    "        \"Country Comparison :: HIV/AIDS - adult prevalence rate\"], axis = 1)\n",
    "\n",
    "# make everything a float\n",
    "att=df.iloc[:,3:]\n",
    "for (name, data) in att.iteritems():\n",
    "    att[name]= (data.replace( '[\\$,)]','', regex=True ).astype(float))\n",
    "df.iloc[:,3:] = att\n",
    "\n",
    "# Drop NaN\n",
    "df_1 = df.dropna()\n",
    "\n",
    "df_2 = df_1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "7b696b95",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 131,
   "id": "c621ad60",
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\Users\\Ian\\AppData\\Local\\Temp/ipykernel_23248/2183463114.py:17: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  df_2[\"Wheat:GDPpc\"] = 5.4980/df_2[\"Country Comparison :: GDP - per capita (PPP)\"]\n",
      "C:\\Users\\Ian\\AppData\\Local\\Temp/ipykernel_23248/2183463114.py:18: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  df_2[\"Oil:GDPpc\"] = 39.68/df_2[\"Country Comparison :: GDP - per capita (PPP)\"]\n",
      "C:\\Users\\Ian\\AppData\\Local\\Temp/ipykernel_23248/2183463114.py:19: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  df_2[\"NatGas:GDPpc\"] = 2.03/df_2[\"Country Comparison :: GDP - per capita (PPP)\"]\n",
      "C:\\Users\\Ian\\AppData\\Local\\Temp/ipykernel_23248/2183463114.py:21: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  df_2[\"Wheat:GDPpc\"] = 4.9414/df_2[\"Country Comparison :: GDP - per capita (PPP)\"]\n",
      "C:\\Users\\Ian\\AppData\\Local\\Temp/ipykernel_23248/2183463114.py:22: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  df_2[\"Oil:GDPpc\"] = 56.99/df_2[\"Country Comparison :: GDP - per capita (PPP)\"]\n",
      "C:\\Users\\Ian\\AppData\\Local\\Temp/ipykernel_23248/2183463114.py:23: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  df_2[\"NatGas:GDPpc\"] = 2.56/df_2[\"Country Comparison :: GDP - per capita (PPP)\"]\n",
      "C:\\Users\\Ian\\AppData\\Local\\Temp/ipykernel_23248/2183463114.py:25: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  df_2[\"Wheat:GDPpc\"] = 4.9757/df_2[\"Country Comparison :: GDP - per capita (PPP)\"]\n",
      "C:\\Users\\Ian\\AppData\\Local\\Temp/ipykernel_23248/2183463114.py:26: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  df_2[\"Oil:GDPpc\"] = 65.23/df_2[\"Country Comparison :: GDP - per capita (PPP)\"]\n",
      "C:\\Users\\Ian\\AppData\\Local\\Temp/ipykernel_23248/2183463114.py:27: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  df_2[\"NatGas:GDPpc\"] = 3.15/df_2[\"Country Comparison :: GDP - per capita (PPP)\"]\n"
     ]
    }
   ],
   "source": [
    "# Commodities (Wheat and Energy) : GDP per cap\n",
    "# https://www.macrotrends.net/2534/wheat-prices-historical-chart-data\n",
    "# Wheat 2022 \t$9.5829\n",
    "# Wheat 20212021 \t$7.0402\n",
    "# Oil 2022 \t$96.02\n",
    "# Oil 2021 \t$68.17\n",
    "# Gas 2022 \t$6.53\n",
    "# Gas 2021 \t$3.89\n",
    "\n",
    "\n",
    "#df_2[\"Wheat:GDPpc\"] = []\n",
    "#df_2[\"Oil:GDPpc\"] = []\n",
    "#df_2[\"NatGas:GDPpc\"] = []\n",
    "\n",
    "for r in df_2[\"Year\"]:\n",
    "    if r == 2020.0:\n",
    "        df_2[\"Wheat:GDPpc\"] = 5.4980/df_2[\"Country Comparison :: GDP - per capita (PPP)\"]\n",
    "        df_2[\"Oil:GDPpc\"] = 39.68/df_2[\"Country Comparison :: GDP - per capita (PPP)\"]\n",
    "        df_2[\"NatGas:GDPpc\"] = 2.03/df_2[\"Country Comparison :: GDP - per capita (PPP)\"]\n",
    "    elif r == 2019.0:\n",
    "        df_2[\"Wheat:GDPpc\"] = 4.9414/df_2[\"Country Comparison :: GDP - per capita (PPP)\"]\n",
    "        df_2[\"Oil:GDPpc\"] = 56.99/df_2[\"Country Comparison :: GDP - per capita (PPP)\"]\n",
    "        df_2[\"NatGas:GDPpc\"] = 2.56/df_2[\"Country Comparison :: GDP - per capita (PPP)\"]\n",
    "    elif r == 2018.0:\n",
    "        df_2[\"Wheat:GDPpc\"] = 4.9757/df_2[\"Country Comparison :: GDP - per capita (PPP)\"]\n",
    "        df_2[\"Oil:GDPpc\"] = 65.23/df_2[\"Country Comparison :: GDP - per capita (PPP)\"]\n",
    "        df_2[\"NatGas:GDPpc\"] = 3.15/df_2[\"Country Comparison :: GDP - per capita (PPP)\"]\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 132,
   "id": "15b103c2",
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\Users\\Ian\\AppData\\Local\\Temp/ipykernel_23248/1808068478.py:14: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  df_2[\"Instability\"] = result\n"
     ]
    }
   ],
   "source": [
    "# Quintile (preforming better..)\n",
    "result = []\n",
    "for i in df_2[\"Instability_Num\"]:\n",
    "    if i >100:\n",
    "        result.append(5)\n",
    "    elif i>75:\n",
    "         result.append(4)\n",
    "    elif i>50:\n",
    "         result.append(3)\n",
    "    elif i>25:\n",
    "         result.append(2)\n",
    "    elif i>0:\n",
    "         result.append(1)\n",
    "df_2[\"Instability\"] = result \n",
    "\n",
    "\n",
    "   "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1772,
   "id": "04222216",
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\Users\\Ian\\AppData\\Local\\Temp/ipykernel_32664/3031239925.py:12: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  df_2[\"Instability\"] = result\n"
     ]
    }
   ],
   "source": [
    "# Trying 3 categories \n",
    "# slightly higher accuracy\n",
    "#result = []\n",
    "#for i in df_2[\"Instability_Num\"]:\n",
    "#    if i >80:\n",
    "#        result.append(3)\n",
    "#    elif i>40:\n",
    "#         result.append(2)\n",
    "#    elif i>0:\n",
    "#        result.append(1)\n",
    "\n",
    "#df_2[\"Instability\"] = result \n",
    " "
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.11.8"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}