Countries_dataset.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Countries/Languages dataset\n",
    "---\n",
    "\n",
    "Here we construct a pandas data frame consisting of countries' names and their characteristics (ISO code, continent they belong to and languages spoken in them).\n",
    "\n",
    "The data is imported from [this repository](https://github.com/annexare/Countries/tree/master/data) containing several json files. We will load the ```countries.json``` and ```languages.json``` files: the former giving the countries names with the languages codes while the latter associates the codes with the language name.\n",
    "\n",
    "```countries.json``` also contains ISO and continents codes to be associated with the ```continents.json``` and ```countries2to3.json``` files, respectively. However, for simplicity, we will make use of of the [country-converter](https://pypi.org/project/country-converter/) python package to get those."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import json\n",
    "import pandas as pd\n",
    "import country_converter as coco\n",
    "\n",
    "with open('Countries/countries.json', 'r', encoding='utf-8') as json_file:\n",
    "    countries_dict = json.load(json_file)\n",
    "with open('Countries/languages.json', 'r', encoding='utf-8') as json_file:\n",
    "    languages_dict = json.load(json_file)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "WARNING:root:Bonaire not found in regex\n",
      "WARNING:root:Bonaire not found in regex\n",
      "WARNING:root:Bonaire not found in regex\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Country_name</th>\n",
       "      <th>Phone_code</th>\n",
       "      <th>Continent</th>\n",
       "      <th>Capital</th>\n",
       "      <th>Currency</th>\n",
       "      <th>Language</th>\n",
       "      <th>Country_code</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Andorra</td>\n",
       "      <td>376</td>\n",
       "      <td>Europe</td>\n",
       "      <td>Andorra la Vella</td>\n",
       "      <td>EUR</td>\n",
       "      <td>[ca]</td>\n",
       "      <td>AND</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>United Arab Emirates</td>\n",
       "      <td>971</td>\n",
       "      <td>Asia</td>\n",
       "      <td>Abu Dhabi</td>\n",
       "      <td>AED</td>\n",
       "      <td>[ar]</td>\n",
       "      <td>ARE</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Afghanistan</td>\n",
       "      <td>93</td>\n",
       "      <td>Asia</td>\n",
       "      <td>Kabul</td>\n",
       "      <td>AFN</td>\n",
       "      <td>[ps, uz, tk]</td>\n",
       "      <td>AFG</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Antigua and Barbuda</td>\n",
       "      <td>1268</td>\n",
       "      <td>America</td>\n",
       "      <td>Saint John's</td>\n",
       "      <td>XCD</td>\n",
       "      <td>[en]</td>\n",
       "      <td>ATG</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Anguilla</td>\n",
       "      <td>1264</td>\n",
       "      <td>America</td>\n",
       "      <td>The Valley</td>\n",
       "      <td>XCD</td>\n",
       "      <td>[en]</td>\n",
       "      <td>AIA</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "           Country_name Phone_code Continent           Capital Currency  \\\n",
       "0               Andorra        376    Europe  Andorra la Vella      EUR   \n",
       "1  United Arab Emirates        971      Asia         Abu Dhabi      AED   \n",
       "2           Afghanistan         93      Asia             Kabul      AFN   \n",
       "3   Antigua and Barbuda       1268   America      Saint John's      XCD   \n",
       "4              Anguilla       1264   America        The Valley      XCD   \n",
       "\n",
       "       Language Country_code  \n",
       "0          [ca]          AND  \n",
       "1          [ar]          ARE  \n",
       "2  [ps, uz, tk]          AFG  \n",
       "3          [en]          ATG  \n",
       "4          [en]          AIA  "
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "## countries\n",
    "countries = pd.DataFrame(countries_dict).T\n",
    "countries.reset_index(drop=True, inplace=True)\n",
    "countries.columns = ['Country_name', 'native', 'Phone_code', \n",
    "                     'Continent', 'Capital', 'Currency', 'Language']\n",
    "countries = countries[['Country_name', 'Phone_code', 'Continent', \n",
    "                       'Capital', 'Currency', 'Language']]\n",
    "# change name to standard name\n",
    "countries['Country_name'] = countries.Country_name.apply(\n",
    "    lambda x: coco.convert(names=x, to='name_short', not_found=None))\n",
    "# Add ISO3 codes\n",
    "countries['Country_code'] = countries.Country_name.apply(\n",
    "    lambda x: coco.convert(names=x, to='iso3', not_found=None))\n",
    "# append continents\n",
    "countries['Continent'] = coco.convert(names=countries.Country_name.tolist(), \n",
    "                                      to='Continent', not_found=None)\n",
    "countries.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Notice that the Languages field is made of lists of languages as each country could have several oficial languages."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "# languages\n",
    "languages = pd.DataFrame(languages_dict).T\n",
    "languages.reset_index(inplace=True)\n",
    "languages.rename(columns={'index':'code'}, inplace=True)\n",
    "languages = languages[['code', 'name']]\n",
    "languages.head()\n",
    "languages.to_csv('Countries/languages_codes.csv')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now we replace the languages codes in ```countries``` by the languages names in ```languages```."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "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>Country_name</th>\n",
       "      <th>Phone_code</th>\n",
       "      <th>Continent</th>\n",
       "      <th>Capital</th>\n",
       "      <th>Currency</th>\n",
       "      <th>Country_code</th>\n",
       "      <th>Languages</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Andorra</td>\n",
       "      <td>376</td>\n",
       "      <td>Europe</td>\n",
       "      <td>Andorra la Vella</td>\n",
       "      <td>EUR</td>\n",
       "      <td>AND</td>\n",
       "      <td>[Catalan]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>United Arab Emirates</td>\n",
       "      <td>971</td>\n",
       "      <td>Asia</td>\n",
       "      <td>Abu Dhabi</td>\n",
       "      <td>AED</td>\n",
       "      <td>ARE</td>\n",
       "      <td>[Arabic]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Afghanistan</td>\n",
       "      <td>93</td>\n",
       "      <td>Asia</td>\n",
       "      <td>Kabul</td>\n",
       "      <td>AFN</td>\n",
       "      <td>AFG</td>\n",
       "      <td>[Pashto, Uzbek, Turkmen]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Antigua and Barbuda</td>\n",
       "      <td>1268</td>\n",
       "      <td>America</td>\n",
       "      <td>Saint John's</td>\n",
       "      <td>XCD</td>\n",
       "      <td>ATG</td>\n",
       "      <td>[English]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Anguilla</td>\n",
       "      <td>1264</td>\n",
       "      <td>America</td>\n",
       "      <td>The Valley</td>\n",
       "      <td>XCD</td>\n",
       "      <td>AIA</td>\n",
       "      <td>[English]</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "           Country_name Phone_code Continent           Capital Currency  \\\n",
       "0               Andorra        376    Europe  Andorra la Vella      EUR   \n",
       "1  United Arab Emirates        971      Asia         Abu Dhabi      AED   \n",
       "2           Afghanistan         93      Asia             Kabul      AFN   \n",
       "3   Antigua and Barbuda       1268   America      Saint John's      XCD   \n",
       "4              Anguilla       1264   America        The Valley      XCD   \n",
       "\n",
       "  Country_code                 Languages  \n",
       "0          AND                 [Catalan]  \n",
       "1          ARE                  [Arabic]  \n",
       "2          AFG  [Pashto, Uzbek, Turkmen]  \n",
       "3          ATG                 [English]  \n",
       "4          AIA                 [English]  "
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# we create a new dataframe where we keep the country names from countries\n",
    "# and the language names from languages\n",
    "langs_list = []\n",
    "for list_ in countries.Language.values:\n",
    "    int_l = []\n",
    "    for code in list_:\n",
    "        try:\n",
    "            lang = languages[languages.code == code].name.tolist()[0]\n",
    "            int_l.append(lang)\n",
    "        except: pass\n",
    "    langs_list.append(int_l)\n",
    "        \n",
    "lang_dict = dict(zip(['Languages'], [langs_list]))\n",
    "lang_df = pd.DataFrame(lang_dict)\n",
    "countries_lang = pd.concat([countries, lang_df], axis=1)\n",
    "countries_lang.drop(['Language'], axis=1, inplace=True)\n",
    "countries_lang.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "countries_lang.to_parquet('Countries/countries_lang.parquet')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The dataframe might be useful as it is but, as a final step, we will expand the _Languages_ field. In doing so, the rest of the variables are duplicated."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "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>Country_name</th>\n",
       "      <th>Phone_code</th>\n",
       "      <th>Continent</th>\n",
       "      <th>Capital</th>\n",
       "      <th>Currency</th>\n",
       "      <th>Country_code</th>\n",
       "      <th>Languages</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Andorra</td>\n",
       "      <td>376</td>\n",
       "      <td>Europe</td>\n",
       "      <td>Andorra la Vella</td>\n",
       "      <td>EUR</td>\n",
       "      <td>AND</td>\n",
       "      <td>Catalan</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>United Arab Emirates</td>\n",
       "      <td>971</td>\n",
       "      <td>Asia</td>\n",
       "      <td>Abu Dhabi</td>\n",
       "      <td>AED</td>\n",
       "      <td>ARE</td>\n",
       "      <td>Arabic</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Afghanistan</td>\n",
       "      <td>93</td>\n",
       "      <td>Asia</td>\n",
       "      <td>Kabul</td>\n",
       "      <td>AFN</td>\n",
       "      <td>AFG</td>\n",
       "      <td>Pashto</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Afghanistan</td>\n",
       "      <td>93</td>\n",
       "      <td>Asia</td>\n",
       "      <td>Kabul</td>\n",
       "      <td>AFN</td>\n",
       "      <td>AFG</td>\n",
       "      <td>Uzbek</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Afghanistan</td>\n",
       "      <td>93</td>\n",
       "      <td>Asia</td>\n",
       "      <td>Kabul</td>\n",
       "      <td>AFN</td>\n",
       "      <td>AFG</td>\n",
       "      <td>Turkmen</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "           Country_name Phone_code Continent           Capital Currency  \\\n",
       "0               Andorra        376    Europe  Andorra la Vella      EUR   \n",
       "1  United Arab Emirates        971      Asia         Abu Dhabi      AED   \n",
       "2           Afghanistan         93      Asia             Kabul      AFN   \n",
       "3           Afghanistan         93      Asia             Kabul      AFN   \n",
       "4           Afghanistan         93      Asia             Kabul      AFN   \n",
       "\n",
       "  Country_code Languages  \n",
       "0          AND   Catalan  \n",
       "1          ARE    Arabic  \n",
       "2          AFG    Pashto  \n",
       "3          AFG     Uzbek  \n",
       "4          AFG   Turkmen  "
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "countries_lang_full = pd.DataFrame(columns=countries_lang.columns)\n",
    "for i in range(len(countries_lang)):\n",
    "    cols = countries_lang.columns.tolist()\n",
    "    langs = countries_lang.iloc[i].Languages#.tolist()\n",
    "    rest = countries_lang.iloc[i,:-1].tolist()\n",
    "    for j, lang in enumerate(langs):\n",
    "        line = pd.DataFrame(dict(zip(cols, rest+[lang])), index=[float('{}.{}'.format(i,j))])\n",
    "        countries_lang_full = countries_lang_full.append(line, ignore_index=False)\n",
    "\n",
    "countries_lang_full.reset_index(drop=True, inplace=True)\n",
    "countries_lang_full.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [],
   "source": [
    "countries_lang_full.to_csv('Countries/countries_lang_full.csv')"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.7.5"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}