Migrant_stock_preprocessing.ipynb

{
  "nbformat": 4,
  "nbformat_minor": 0,
  "metadata": {
    "colab": {
      "name": "Migrant stock preprocessing.ipynb",
      "provenance": [],
      "collapsed_sections": [],
      "toc_visible": true
    },
    "kernelspec": {
      "name": "python3",
      "display_name": "Python 3",
      "language": "python"
    }
  },
  "cells": [
    {
      "cell_type": "code",
      "metadata": {
        "id": "5KjJriKeZxFR"
      },
      "source": [
        "import os\n",
        "import pandas as pd"
      ],
      "execution_count": 2,
      "outputs": []
    },
    {
      "cell_type": "code",
      "execution_count": 23,
      "metadata": {},
      "outputs": [],
      "source": [
        "XLS_DIR = './app/data/xls'\n",
        "CSV_DIR = './app/data/csv'"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {},
      "outputs": [],
      "source": [
        "# file = pd.read_excel(\"UN_MigrantStockByOriginAndDestination_2017.xlsx\");\n",
        "\n",
        "migrants_by_od_t1 = pd.read_excel(XLS_DIR + \"/migrants_by_origin_and_destination.xls\", sheet_name = \"Table 1\", header=15)\n",
        "pd.set_option('display.max_rows', 500)\n",
        "# pd.set_option('display.max_columns', 500)\n",
        "# pd.set_option('display.width', 150)"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "J0vMBE7hp-pZ"
      },
      "source": [
        "pd.set_option('display.max_rows', 500)\n",
        "# pd.set_option('display.max_columns', 500)\n",
        "# pd.set_option('display.width', 150)"
      ],
      "execution_count": 25,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 491
        },
        "id": "0jFFe8cLaXjv",
        "outputId": "f986e315-23f1-4dad-8536-46134de9f91b"
      },
      "source": [
        "migrants_by_od_t1.head()"
      ],
      "execution_count": 2,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "   Unnamed: 0  Unnamed: 1                 Unnamed: 2 Unnamed: 3  Unnamed: 4  \\\n",
              "0        1990     1990001                      WORLD        NaN       900.0   \n",
              "1        1990     1990002      UN development groups        NaN         NaN   \n",
              "2        1990     1990003     More developed regions          b       901.0   \n",
              "3        1990     1990004     Less developed regions          c       902.0   \n",
              "4        1990     1990005  Least developed countries          d       941.0   \n",
              "\n",
              "  Unnamed: 5      Total Other South Other North Afghanistan  ... Uruguay  \\\n",
              "0        NaN  153011473     6548526     2366800     6823350  ...  237486   \n",
              "1        NaN         ..          ..          ..          ..  ...      ..   \n",
              "2        NaN   82767216     3385103     1077179      119386  ...   56838   \n",
              "3        NaN   70244257     3163423     1289621     6703964  ...  180648   \n",
              "4        NaN   11060221      482753      239756           0  ...     286   \n",
              "\n",
              "  Uzbekistan Vanuatu Venezuela (Bolivarian Republic of) Viet Nam  \\\n",
              "0    1428020    5060                             185946  1237873   \n",
              "1         ..      ..                                 ..       ..   \n",
              "2    1078563    1017                             114991  1085310   \n",
              "3     349457    4043                              70955   152563   \n",
              "4       2027       9                               2510    71579   \n",
              "\n",
              "  Wallis and Futuna Islands Western Sahara   Yemen Zambia Zimbabwe  \n",
              "0                      6484         168239  455492  85203   204365  \n",
              "1                        ..             ..      ..     ..       ..  \n",
              "2                       884            333   11457  26062    40957  \n",
              "3                      5600         167906  444035  59141   163408  \n",
              "4                         0              0     357  26254    75122  \n",
              "\n",
              "[5 rows x 241 columns]"
            ],
            "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>Unnamed: 0</th>\n      <th>Unnamed: 1</th>\n      <th>Unnamed: 2</th>\n      <th>Unnamed: 3</th>\n      <th>Unnamed: 4</th>\n      <th>Unnamed: 5</th>\n      <th>Total</th>\n      <th>Other South</th>\n      <th>Other North</th>\n      <th>Afghanistan</th>\n      <th>...</th>\n      <th>Uruguay</th>\n      <th>Uzbekistan</th>\n      <th>Vanuatu</th>\n      <th>Venezuela (Bolivarian Republic of)</th>\n      <th>Viet Nam</th>\n      <th>Wallis and Futuna Islands</th>\n      <th>Western Sahara</th>\n      <th>Yemen</th>\n      <th>Zambia</th>\n      <th>Zimbabwe</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>0</th>\n      <td>1990</td>\n      <td>1990001</td>\n      <td>WORLD</td>\n      <td>NaN</td>\n      <td>900.0</td>\n      <td>NaN</td>\n      <td>153011473</td>\n      <td>6548526</td>\n      <td>2366800</td>\n      <td>6823350</td>\n      <td>...</td>\n      <td>237486</td>\n      <td>1428020</td>\n      <td>5060</td>\n      <td>185946</td>\n      <td>1237873</td>\n      <td>6484</td>\n      <td>168239</td>\n      <td>455492</td>\n      <td>85203</td>\n      <td>204365</td>\n    </tr>\n    <tr>\n      <th>1</th>\n      <td>1990</td>\n      <td>1990002</td>\n      <td>UN development groups</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>NaN</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>2</th>\n      <td>1990</td>\n      <td>1990003</td>\n      <td>More developed regions</td>\n      <td>b</td>\n      <td>901.0</td>\n      <td>NaN</td>\n      <td>82767216</td>\n      <td>3385103</td>\n      <td>1077179</td>\n      <td>119386</td>\n      <td>...</td>\n      <td>56838</td>\n      <td>1078563</td>\n      <td>1017</td>\n      <td>114991</td>\n      <td>1085310</td>\n      <td>884</td>\n      <td>333</td>\n      <td>11457</td>\n      <td>26062</td>\n      <td>40957</td>\n    </tr>\n    <tr>\n      <th>3</th>\n      <td>1990</td>\n      <td>1990004</td>\n      <td>Less developed regions</td>\n      <td>c</td>\n      <td>902.0</td>\n      <td>NaN</td>\n      <td>70244257</td>\n      <td>3163423</td>\n      <td>1289621</td>\n      <td>6703964</td>\n      <td>...</td>\n      <td>180648</td>\n      <td>349457</td>\n      <td>4043</td>\n      <td>70955</td>\n      <td>152563</td>\n      <td>5600</td>\n      <td>167906</td>\n      <td>444035</td>\n      <td>59141</td>\n      <td>163408</td>\n    </tr>\n    <tr>\n      <th>4</th>\n      <td>1990</td>\n      <td>1990005</td>\n      <td>Least developed countries</td>\n      <td>d</td>\n      <td>941.0</td>\n      <td>NaN</td>\n      <td>11060221</td>\n      <td>482753</td>\n      <td>239756</td>\n      <td>0</td>\n      <td>...</td>\n      <td>286</td>\n      <td>2027</td>\n      <td>9</td>\n      <td>2510</td>\n      <td>71579</td>\n      <td>0</td>\n      <td>0</td>\n      <td>357</td>\n      <td>26254</td>\n      <td>75122</td>\n    </tr>\n  </tbody>\n</table>\n<p>5 rows × 241 columns</p>\n</div>"
          },
          "metadata": {},
          "execution_count": 2
        }
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 28,
      "metadata": {},
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "Index(['Year', 'Destination', 'Total', 'Other South', 'Other North',\n",
              "       'Afghanistan', 'Albania', 'Algeria', 'American Samoa', 'Andorra',\n",
              "       ...\n",
              "       'Uruguay', 'Uzbekistan', 'Vanuatu',\n",
              "       'Venezuela (Bolivarian Republic of)', 'Viet Nam',\n",
              "       'Wallis and Futuna Islands', 'Western Sahara', 'Yemen', 'Zambia',\n",
              "       'Zimbabwe'],\n",
              "      dtype='object', length=237)"
            ]
          },
          "metadata": {},
          "execution_count": 28
        }
      ],
      "source": [
        "df.columns"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 11,
      "metadata": {},
      "outputs": [],
      "source": [
        "def drop_useless_columns(df, names):\n",
        "    return df.drop(names, axis=1)\n",
        "\n",
        "def convert_xls_to_csv(df, drop_names, rename_names, csv_file_name):\n",
        "    df = drop_useless_columns(df, drop_names)\n",
        "    df = df.rename(columns={rename_names[0]: \"Year\", rename_names[2]: \"Destination\"})\n",
        "    for i in range(6, len(rename_names)):\n",
        "        if \".1\" in rename_names[i]:\n",
        "            col_name = rename_names[i].replace(\".1\", \"_(m)\").strip()\n",
        "            df = df.rename(columns={rename_names[i]:col_name})\n",
        "        elif \".2\" in rename_names[i]:\n",
        "            col_name = rename_names[i].replace(\".2\", \"_(f)\").strip()\n",
        "            df = df.rename(columns={rename_names[i]:col_name})\n",
        "        else:\n",
        "            col_name = (rename_names[i] + \"_(mf)\").strip()\n",
        "            df = df.rename(columns={rename_names[i]:col_name})\n",
        "\n",
        "    df.to_csv(CSV_DIR + '/' + csv_file_name, index=False)"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 34,
      "metadata": {},
      "outputs": [],
      "source": [
        "migrants_by_od_t1 = migrants_by_od_t1.rename(columns={\"Unnamed: 0\": \"Year\", \"Unnamed: 2\": \"Destination\"})\n",
        "# migrants_by_od_t1 = drop_useless_columns(migrants_by_od_t1, [\"Unnamed: 1\", \"Unnamed: 3\", \"Unnamed: 4\", \"Unnamed: 5\"])\n",
        "migrants_by_od_t1.head()\n",
        "\n",
        "migrants_by_od_t1.to_csv(\"app/data/migrants_by_origin_and_destination.csv\", index=False)"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 36,
      "metadata": {},
      "outputs": [],
      "source": [
        "migrants_by_od_t1 = pd.read_excel(\"app/data/migrants_by_origin_and_destination.xls\", sheet_name = \"Table 2\", header=15)\n",
        "migrants_by_od_t1 = migrants_by_od_t1.rename(columns={\"Unnamed: 0\": \"Year\", \"Unnamed: 2\": \"Destination\"})\n",
        "migrants_by_od_t1 = drop_useless_columns(migrants_by_od_t1, [\"Unnamed: 1\", \"Unnamed: 3\", \"Unnamed: 4\", \"Unnamed: 5\"])\n",
        "migrants_by_od_t1.head()\n",
        "\n",
        "migrants_by_od_t1.to_csv(\"app/data/migrants_by_origin_and_destination_male.csv\", index=False)"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 37,
      "metadata": {},
      "outputs": [],
      "source": [
        "migrants_by_od_t1 = pd.read_excel(\"app/data/migrants_by_origin_and_destination.xls\", sheet_name = \"Table 3\", header=15)\n",
        "migrants_by_od_t1 = migrants_by_od_t1.rename(columns={\"Unnamed: 0\": \"Year\", \"Unnamed: 2\": \"Destination\"})\n",
        "migrants_by_od_t1 = drop_useless_columns(migrants_by_od_t1, [\"Unnamed: 1\", \"Unnamed: 3\", \"Unnamed: 4\", \"Unnamed: 5\"])\n",
        "migrants_by_od_t1.head()\n",
        "\n",
        "migrants_by_od_t1.to_csv(\"app/data/migrants_by_origin_and_destination_female.csv\", index=False)"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 12,
      "metadata": {},
      "outputs": [],
      "source": [
        "migrants_by_od_t1 = pd.read_excel(\"app/data/migrants_by_age.xls\", sheet_name = \"Table 1\", header=15)\n",
        "convert_xls_to_csv(migrants_by_od_t1, [\"Unnamed: 1\", \"Unnamed: 3\", \"Unnamed: 4\", \"Unnamed: 5\"], list(migrants_by_od_t1.columns), \"migrants_by_age_and_sex_table1.csv\")\n"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 13,
      "metadata": {},
      "outputs": [],
      "source": [
        "migrants_by_od_t1 = pd.read_excel(\"app/data/migrants_by_age.xls\", sheet_name = \"Table 2\", header=15)\n",
        "convert_xls_to_csv(migrants_by_od_t1, [\"Unnamed: 1\", \"Unnamed: 3\", \"Unnamed: 4\", \"Unnamed: 5\"], list(migrants_by_od_t1.columns), \"migrants_by_age_and_sex_table2.csv\")"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 14,
      "metadata": {},
      "outputs": [],
      "source": [
        "migrants_by_od_t1 = pd.read_excel(\"app/data/migrants_by_age.xls\", sheet_name = \"Table 3\", header=15)\n",
        "convert_xls_to_csv(migrants_by_od_t1, [\"Unnamed: 1\", \"Unnamed: 3\", \"Unnamed: 4\", \"Unnamed: 5\"], list(migrants_by_od_t1.columns), \"migrants_by_age_and_sex_table3.csv\")"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 15,
      "metadata": {},
      "outputs": [],
      "source": [
        "migrants_by_od_t1 = pd.read_excel(\"app/data/migrants_by_age.xls\", sheet_name = \"Table 4\", header=15)\n",
        "convert_xls_to_csv(migrants_by_od_t1, [\"Unnamed: 1\", \"Unnamed: 3\", \"Unnamed: 4\", \"Unnamed: 5\"], list(migrants_by_od_t1.columns), \"migrants_by_age_and_sex_table4.csv\")"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "hmlzUIJceLUW"
      },
      "source": [
        "countries_names = df.columns.values[9:]"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "4GZWg7f7f0tJ"
      },
      "source": [
        "countries_migration = df.loc[df[\"Unnamed: 2\"].isin(countries_names)]"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 304
        },
        "id": "II7c_fnfgpVx",
        "outputId": "d729c43f-815d-48ba-984d-6e3f3aae9ee8"
      },
      "source": [
        "countries_migration.head()"
      ],
      "execution_count": null,
      "outputs": [
        {
          "output_type": "execute_result",
          "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>Unnamed: 0</th>\n",
              "      <th>Unnamed: 1</th>\n",
              "      <th>Unnamed: 2</th>\n",
              "      <th>Unnamed: 3</th>\n",
              "      <th>Unnamed: 4</th>\n",
              "      <th>Unnamed: 5</th>\n",
              "      <th>Total</th>\n",
              "      <th>Other North</th>\n",
              "      <th>Other South</th>\n",
              "      <th>Afghanistan</th>\n",
              "      <th>Albania</th>\n",
              "      <th>Algeria</th>\n",
              "      <th>American Samoa</th>\n",
              "      <th>Andorra</th>\n",
              "      <th>Angola</th>\n",
              "      <th>Anguilla</th>\n",
              "      <th>Antigua and Barbuda</th>\n",
              "      <th>Argentina</th>\n",
              "      <th>Armenia</th>\n",
              "      <th>Aruba</th>\n",
              "      <th>Australia</th>\n",
              "      <th>Austria</th>\n",
              "      <th>Azerbaijan</th>\n",
              "      <th>Bahamas</th>\n",
              "      <th>Bahrain</th>\n",
              "      <th>Bangladesh</th>\n",
              "      <th>Barbados</th>\n",
              "      <th>Belarus</th>\n",
              "      <th>Belgium</th>\n",
              "      <th>Belize</th>\n",
              "      <th>Benin</th>\n",
              "      <th>Bermuda</th>\n",
              "      <th>Bhutan</th>\n",
              "      <th>Bolivia (Plurinational State of)</th>\n",
              "      <th>Bosnia and Herzegovina</th>\n",
              "      <th>Botswana</th>\n",
              "      <th>Brazil</th>\n",
              "      <th>British Virgin Islands</th>\n",
              "      <th>Brunei Darussalam</th>\n",
              "      <th>Bulgaria</th>\n",
              "      <th>...</th>\n",
              "      <th>South Sudan</th>\n",
              "      <th>Spain</th>\n",
              "      <th>Sri Lanka</th>\n",
              "      <th>State of Palestine</th>\n",
              "      <th>Sudan</th>\n",
              "      <th>Suriname</th>\n",
              "      <th>Swaziland</th>\n",
              "      <th>Sweden</th>\n",
              "      <th>Switzerland</th>\n",
              "      <th>Syrian Arab Republic</th>\n",
              "      <th>Tajikistan</th>\n",
              "      <th>TFYR Macedonia</th>\n",
              "      <th>Thailand</th>\n",
              "      <th>Timor-Leste</th>\n",
              "      <th>Togo</th>\n",
              "      <th>Tokelau</th>\n",
              "      <th>Tonga</th>\n",
              "      <th>Trinidad and Tobago</th>\n",
              "      <th>Tunisia</th>\n",
              "      <th>Turkey</th>\n",
              "      <th>Turkmenistan</th>\n",
              "      <th>Turks and Caicos Islands</th>\n",
              "      <th>Tuvalu</th>\n",
              "      <th>Uganda</th>\n",
              "      <th>Ukraine</th>\n",
              "      <th>United Arab Emirates</th>\n",
              "      <th>United Kingdom</th>\n",
              "      <th>United Republic of Tanzania</th>\n",
              "      <th>United States of America</th>\n",
              "      <th>United States Virgin Islands</th>\n",
              "      <th>Uruguay</th>\n",
              "      <th>Uzbekistan</th>\n",
              "      <th>Vanuatu</th>\n",
              "      <th>Venezuela (Bolivarian Republic of)</th>\n",
              "      <th>Viet Nam</th>\n",
              "      <th>Wallis and Futuna Islands</th>\n",
              "      <th>Western Sahara</th>\n",
              "      <th>Yemen</th>\n",
              "      <th>Zambia</th>\n",
              "      <th>Zimbabwe</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>13</th>\n",
              "      <td>1990.0</td>\n",
              "      <td>1990014.0</td>\n",
              "      <td>Burundi</td>\n",
              "      <td>NaN</td>\n",
              "      <td>108.0</td>\n",
              "      <td>B R</td>\n",
              "      <td>333110</td>\n",
              "      <td>8943</td>\n",
              "      <td>50676</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>642</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>1833</td>\n",
              "      <td>..</td>\n",
              "      <td>..</td>\n",
              "      <td>..</td>\n",
              "      <td>11912</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>14</th>\n",
              "      <td>1990.0</td>\n",
              "      <td>1990015.0</td>\n",
              "      <td>Comoros</td>\n",
              "      <td>NaN</td>\n",
              "      <td>174.0</td>\n",
              "      <td>B</td>\n",
              "      <td>14079</td>\n",
              "      <td>672</td>\n",
              "      <td>847</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>163</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>15</th>\n",
              "      <td>1990.0</td>\n",
              "      <td>1990016.0</td>\n",
              "      <td>Djibouti</td>\n",
              "      <td>NaN</td>\n",
              "      <td>262.0</td>\n",
              "      <td>B R</td>\n",
              "      <td>122221</td>\n",
              "      <td>1827</td>\n",
              "      <td>5484</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>289</td>\n",
              "      <td>..</td>\n",
              "      <td>..</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>16</th>\n",
              "      <td>1990.0</td>\n",
              "      <td>1990017.0</td>\n",
              "      <td>Eritrea</td>\n",
              "      <td>NaN</td>\n",
              "      <td>232.0</td>\n",
              "      <td>I</td>\n",
              "      <td>11848</td>\n",
              "      <td>345</td>\n",
              "      <td>737</td>\n",
              "      <td>..</td>\n",
              "      <td>..</td>\n",
              "      <td>..</td>\n",
              "      <td>..</td>\n",
              "      <td>..</td>\n",
              "      <td>247</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>933</td>\n",
              "      <td>..</td>\n",
              "      <td>..</td>\n",
              "      <td>..</td>\n",
              "      <td>131</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>175</td>\n",
              "      <td>..</td>\n",
              "      <td>..</td>\n",
              "      <td>34</td>\n",
              "      <td>218</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>91</td>\n",
              "      <td>82</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>17</th>\n",
              "      <td>1990.0</td>\n",
              "      <td>1990018.0</td>\n",
              "      <td>Ethiopia</td>\n",
              "      <td>NaN</td>\n",
              "      <td>231.0</td>\n",
              "      <td>B R</td>\n",
              "      <td>1155390</td>\n",
              "      <td>7358</td>\n",
              "      <td>22075</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>384266</td>\n",
              "      <td>..</td>\n",
              "      <td>..</td>\n",
              "      <td>..</td>\n",
              "      <td>53857</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>103</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>68</td>\n",
              "      <td>..</td>\n",
              "      <td>..</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "<p>5 rows × 241 columns</p>\n",
              "</div>"
            ],
            "text/plain": [
              "    Unnamed: 0  Unnamed: 1 Unnamed: 2  ... Yemen  Zambia Zimbabwe\n",
              "13      1990.0   1990014.0    Burundi  ...    ..      ..       ..\n",
              "14      1990.0   1990015.0    Comoros  ...    ..      ..       ..\n",
              "15      1990.0   1990016.0   Djibouti  ...   289      ..       ..\n",
              "16      1990.0   1990017.0    Eritrea  ...    ..      91       82\n",
              "17      1990.0   1990018.0   Ethiopia  ...    68      ..       ..\n",
              "\n",
              "[5 rows x 241 columns]"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 7
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "sNcGcICp6Qaa",
        "outputId": "bdb25d23-c770-4b7d-a4ae-38a1a8e2422c"
      },
      "source": [
        "# print(\"\" + countries_migration[\"Burundi\"].sum())\r\n",
        "count = countries_migration[countries_migration['Ethiopia'] != \"..\"]['Ethiopia'].sum()\r\n",
        "print(count)"
      ],
      "execution_count": null,
      "outputs": [
        {
          "output_type": "stream",
          "text": [
            "5650703\n"
          ],
          "name": "stdout"
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "POTFG7uYg-eC"
      },
      "source": [
        "countries_1990 = countries_migration.loc[countries_migration[\"Unnamed: 0\"] == 1990.0]"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "Z9LVPODrjRMy"
      },
      "source": [
        "countries_df = []\r\n",
        "years = [1990.0, 1995.0, 2000.0, 2005.0, 2010.0, 2015.0, 2017.0]\r\n",
        "\r\n",
        "for year in years:\r\n",
        "  df_temp = countries_migration.loc[countries_migration[\"Unnamed: 0\"] == year]\r\n",
        "  df_temp = df_temp.sort_values(\"Unnamed: 2\")\r\n",
        "  df_temp = df_temp[countries_names]\r\n",
        "  countries_df.append(df_temp)\r\n"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "_lLH8SbElNrE",
        "outputId": "0b88b270-b26e-4ad5-f887-8ea6515742b1"
      },
      "source": [
        "pd.set_option('display.max_rows', 500)\r\n",
        "# pd.set_option('display.max_columns', 500)\r\n",
        "# pd.set_option('display.width', 150)\r\n",
        "\r\n",
        "for i, country in enumerate(countries_df):\r\n",
        "  # print(count.isin(['..']).sum(axis=0))\r\n",
        "  # print(country[country[countries_names] == \"..\"].count())\r\n",
        "\r\n",
        "  print(years[i], country.size - country.stack().value_counts()[\"..\"])\r\n",
        "  # print(count[~count.isin([\"..\"]).sum(axis=0)])\r\n",
        "  # print(count.apply(pd.value_counts))"
      ],
      "execution_count": null,
      "outputs": [
        {
          "output_type": "stream",
          "text": [
            "1990.0 10317\n",
            "1995.0 10568\n",
            "2000.0 10559\n",
            "2005.0 10793\n",
            "2010.0 11095\n",
            "2015.0 11172\n",
            "2017.0 11137\n"
          ],
          "name": "stdout"
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 598
        },
        "id": "4Aa0-vFQm2YU",
        "outputId": "6c03b128-082d-47a7-904f-b02dd36accf0"
      },
      "source": [
        "df[\"..\"].count()"
      ],
      "execution_count": null,
      "outputs": [
        {
          "output_type": "error",
          "ename": "KeyError",
          "evalue": "ignored",
          "traceback": [
            "\u001b[0;31m---------------------------------------------------------------------------\u001b[0m",
            "\u001b[0;31mKeyError\u001b[0m                                  Traceback (most recent call last)",
            "\u001b[0;32m/usr/local/lib/python3.6/dist-packages/pandas/core/indexes/base.py\u001b[0m in \u001b[0;36mget_loc\u001b[0;34m(self, key, method, tolerance)\u001b[0m\n\u001b[1;32m   2897\u001b[0m             \u001b[0;32mtry\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m-> 2898\u001b[0;31m                 \u001b[0;32mreturn\u001b[0m \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_engine\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mget_loc\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mcasted_key\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m   2899\u001b[0m             \u001b[0;32mexcept\u001b[0m \u001b[0mKeyError\u001b[0m \u001b[0;32mas\u001b[0m \u001b[0merr\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
            "\u001b[0;32mpandas/_libs/index.pyx\u001b[0m in \u001b[0;36mpandas._libs.index.IndexEngine.get_loc\u001b[0;34m()\u001b[0m\n",
            "\u001b[0;32mpandas/_libs/index.pyx\u001b[0m in \u001b[0;36mpandas._libs.index.IndexEngine.get_loc\u001b[0;34m()\u001b[0m\n",
            "\u001b[0;32mpandas/_libs/hashtable_class_helper.pxi\u001b[0m in \u001b[0;36mpandas._libs.hashtable.PyObjectHashTable.get_item\u001b[0;34m()\u001b[0m\n",
            "\u001b[0;32mpandas/_libs/hashtable_class_helper.pxi\u001b[0m in \u001b[0;36mpandas._libs.hashtable.PyObjectHashTable.get_item\u001b[0;34m()\u001b[0m\n",
            "\u001b[0;31mKeyError\u001b[0m: '..'",
            "\nThe above exception was the direct cause of the following exception:\n",
            "\u001b[0;31mKeyError\u001b[0m                                  Traceback (most recent call last)",
            "\u001b[0;32m<ipython-input-63-61d6e0e78e7e>\u001b[0m in \u001b[0;36m<module>\u001b[0;34m()\u001b[0m\n\u001b[0;32m----> 1\u001b[0;31m \u001b[0mdf\u001b[0m\u001b[0;34m[\u001b[0m\u001b[0;34m\"..\"\u001b[0m\u001b[0;34m]\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mcount\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m",
            "\u001b[0;32m/usr/local/lib/python3.6/dist-packages/pandas/core/frame.py\u001b[0m in \u001b[0;36m__getitem__\u001b[0;34m(self, key)\u001b[0m\n\u001b[1;32m   2904\u001b[0m             \u001b[0;32mif\u001b[0m \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mcolumns\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mnlevels\u001b[0m \u001b[0;34m>\u001b[0m \u001b[0;36m1\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   2905\u001b[0m                 \u001b[0;32mreturn\u001b[0m \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_getitem_multilevel\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mkey\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m-> 2906\u001b[0;31m             \u001b[0mindexer\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mcolumns\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mget_loc\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mkey\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m   2907\u001b[0m             \u001b[0;32mif\u001b[0m \u001b[0mis_integer\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mindexer\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   2908\u001b[0m                 \u001b[0mindexer\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0;34m[\u001b[0m\u001b[0mindexer\u001b[0m\u001b[0;34m]\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
            "\u001b[0;32m/usr/local/lib/python3.6/dist-packages/pandas/core/indexes/base.py\u001b[0m in \u001b[0;36mget_loc\u001b[0;34m(self, key, method, tolerance)\u001b[0m\n\u001b[1;32m   2898\u001b[0m                 \u001b[0;32mreturn\u001b[0m \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_engine\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mget_loc\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mcasted_key\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   2899\u001b[0m             \u001b[0;32mexcept\u001b[0m \u001b[0mKeyError\u001b[0m \u001b[0;32mas\u001b[0m \u001b[0merr\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m-> 2900\u001b[0;31m                 \u001b[0;32mraise\u001b[0m \u001b[0mKeyError\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mkey\u001b[0m\u001b[0;34m)\u001b[0m \u001b[0;32mfrom\u001b[0m \u001b[0merr\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m   2901\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   2902\u001b[0m         \u001b[0;32mif\u001b[0m \u001b[0mtolerance\u001b[0m \u001b[0;32mis\u001b[0m \u001b[0;32mnot\u001b[0m \u001b[0;32mNone\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
            "\u001b[0;31mKeyError\u001b[0m: '..'"
          ]
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 320
        },
        "id": "jYv2aClkhQW6",
        "outputId": "d831b718-fe30-4602-cee7-1164cd00cc5c"
      },
      "source": [
        "countries_1990 = countries_1990.sort_values(\"Unnamed: 2\")\r\n",
        "countries_1990.head()"
      ],
      "execution_count": null,
      "outputs": [
        {
          "output_type": "execute_result",
          "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>Unnamed: 0</th>\n",
              "      <th>Unnamed: 1</th>\n",
              "      <th>Unnamed: 2</th>\n",
              "      <th>Unnamed: 3</th>\n",
              "      <th>Unnamed: 4</th>\n",
              "      <th>Unnamed: 5</th>\n",
              "      <th>Total</th>\n",
              "      <th>Other North</th>\n",
              "      <th>Other South</th>\n",
              "      <th>Afghanistan</th>\n",
              "      <th>Albania</th>\n",
              "      <th>Algeria</th>\n",
              "      <th>American Samoa</th>\n",
              "      <th>Andorra</th>\n",
              "      <th>Angola</th>\n",
              "      <th>Anguilla</th>\n",
              "      <th>Antigua and Barbuda</th>\n",
              "      <th>Argentina</th>\n",
              "      <th>Armenia</th>\n",
              "      <th>Aruba</th>\n",
              "      <th>Australia</th>\n",
              "      <th>Austria</th>\n",
              "      <th>Azerbaijan</th>\n",
              "      <th>Bahamas</th>\n",
              "      <th>Bahrain</th>\n",
              "      <th>Bangladesh</th>\n",
              "      <th>Barbados</th>\n",
              "      <th>Belarus</th>\n",
              "      <th>Belgium</th>\n",
              "      <th>Belize</th>\n",
              "      <th>Benin</th>\n",
              "      <th>Bermuda</th>\n",
              "      <th>Bhutan</th>\n",
              "      <th>Bolivia (Plurinational State of)</th>\n",
              "      <th>Bosnia and Herzegovina</th>\n",
              "      <th>Botswana</th>\n",
              "      <th>Brazil</th>\n",
              "      <th>British Virgin Islands</th>\n",
              "      <th>Brunei Darussalam</th>\n",
              "      <th>Bulgaria</th>\n",
              "      <th>...</th>\n",
              "      <th>South Sudan</th>\n",
              "      <th>Spain</th>\n",
              "      <th>Sri Lanka</th>\n",
              "      <th>State of Palestine</th>\n",
              "      <th>Sudan</th>\n",
              "      <th>Suriname</th>\n",
              "      <th>Swaziland</th>\n",
              "      <th>Sweden</th>\n",
              "      <th>Switzerland</th>\n",
              "      <th>Syrian Arab Republic</th>\n",
              "      <th>Tajikistan</th>\n",
              "      <th>TFYR Macedonia</th>\n",
              "      <th>Thailand</th>\n",
              "      <th>Timor-Leste</th>\n",
              "      <th>Togo</th>\n",
              "      <th>Tokelau</th>\n",
              "      <th>Tonga</th>\n",
              "      <th>Trinidad and Tobago</th>\n",
              "      <th>Tunisia</th>\n",
              "      <th>Turkey</th>\n",
              "      <th>Turkmenistan</th>\n",
              "      <th>Turks and Caicos Islands</th>\n",
              "      <th>Tuvalu</th>\n",
              "      <th>Uganda</th>\n",
              "      <th>Ukraine</th>\n",
              "      <th>United Arab Emirates</th>\n",
              "      <th>United Kingdom</th>\n",
              "      <th>United Republic of Tanzania</th>\n",
              "      <th>United States of America</th>\n",
              "      <th>United States Virgin Islands</th>\n",
              "      <th>Uruguay</th>\n",
              "      <th>Uzbekistan</th>\n",
              "      <th>Vanuatu</th>\n",
              "      <th>Venezuela (Bolivarian Republic of)</th>\n",
              "      <th>Viet Nam</th>\n",
              "      <th>Wallis and Futuna Islands</th>\n",
              "      <th>Western Sahara</th>\n",
              "      <th>Yemen</th>\n",
              "      <th>Zambia</th>\n",
              "      <th>Zimbabwe</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>91</th>\n",
              "      <td>1990.0</td>\n",
              "      <td>1990092.0</td>\n",
              "      <td>Afghanistan</td>\n",
              "      <td>NaN</td>\n",
              "      <td>4.0</td>\n",
              "      <td>B</td>\n",
              "      <td>57686</td>\n",
              "      <td>2338</td>\n",
              "      <td>4677</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>40537</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>2027</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>158</th>\n",
              "      <td>1990.0</td>\n",
              "      <td>1990159.0</td>\n",
              "      <td>Albania</td>\n",
              "      <td>NaN</td>\n",
              "      <td>8.0</td>\n",
              "      <td>B</td>\n",
              "      <td>66013</td>\n",
              "      <td>5869</td>\n",
              "      <td>1441</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>678</td>\n",
              "      <td>..</td>\n",
              "      <td>..</td>\n",
              "      <td>..</td>\n",
              "      <td>..</td>\n",
              "      <td>..</td>\n",
              "      <td>..</td>\n",
              "      <td>..</td>\n",
              "      <td>2489</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>3011</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>44</th>\n",
              "      <td>1990.0</td>\n",
              "      <td>1990045.0</td>\n",
              "      <td>Algeria</td>\n",
              "      <td>NaN</td>\n",
              "      <td>12.0</td>\n",
              "      <td>B R</td>\n",
              "      <td>273954</td>\n",
              "      <td>3833</td>\n",
              "      <td>11498</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>36004</td>\n",
              "      <td>9127</td>\n",
              "      <td>..</td>\n",
              "      <td>..</td>\n",
              "      <td>..</td>\n",
              "      <td>..</td>\n",
              "      <td>3550</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>2060</td>\n",
              "      <td>..</td>\n",
              "      <td>4509</td>\n",
              "      <td>..</td>\n",
              "      <td>..</td>\n",
              "      <td>..</td>\n",
              "      <td>..</td>\n",
              "      <td>..</td>\n",
              "      <td>..</td>\n",
              "      <td>..</td>\n",
              "      <td>167905</td>\n",
              "      <td>2593</td>\n",
              "      <td>..</td>\n",
              "      <td>..</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>261</th>\n",
              "      <td>1990.0</td>\n",
              "      <td>1990262.0</td>\n",
              "      <td>American Samoa</td>\n",
              "      <td>NaN</td>\n",
              "      <td>16.0</td>\n",
              "      <td>B</td>\n",
              "      <td>21283</td>\n",
              "      <td>199</td>\n",
              "      <td>598</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>30</td>\n",
              "      <td>1304</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>3510</td>\n",
              "      <td>..</td>\n",
              "      <td>..</td>\n",
              "      <td>..</td>\n",
              "      <td>..</td>\n",
              "      <td>..</td>\n",
              "      <td>1</td>\n",
              "      <td>..</td>\n",
              "      <td>..</td>\n",
              "      <td>..</td>\n",
              "      <td>..</td>\n",
              "      <td>..</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>159</th>\n",
              "      <td>1990.0</td>\n",
              "      <td>1990160.0</td>\n",
              "      <td>Andorra</td>\n",
              "      <td>NaN</td>\n",
              "      <td>20.0</td>\n",
              "      <td>C</td>\n",
              "      <td>38904</td>\n",
              "      <td>575</td>\n",
              "      <td>192</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>86</td>\n",
              "      <td>..</td>\n",
              "      <td>..</td>\n",
              "      <td>36</td>\n",
              "      <td>..</td>\n",
              "      <td>..</td>\n",
              "      <td>..</td>\n",
              "      <td>..</td>\n",
              "      <td>..</td>\n",
              "      <td>..</td>\n",
              "      <td>..</td>\n",
              "      <td>241</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>27103</td>\n",
              "      <td>..</td>\n",
              "      <td>..</td>\n",
              "      <td>..</td>\n",
              "      <td>..</td>\n",
              "      <td>..</td>\n",
              "      <td>54</td>\n",
              "      <td>64</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>1043</td>\n",
              "      <td>..</td>\n",
              "      <td>100</td>\n",
              "      <td>..</td>\n",
              "      <td>78</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",
              "  </tbody>\n",
              "</table>\n",
              "<p>5 rows × 241 columns</p>\n",
              "</div>"
            ],
            "text/plain": [
              "     Unnamed: 0  Unnamed: 1      Unnamed: 2  ... Yemen  Zambia Zimbabwe\n",
              "91       1990.0   1990092.0     Afghanistan  ...    ..      ..       ..\n",
              "158      1990.0   1990159.0         Albania  ...    ..      ..       ..\n",
              "44       1990.0   1990045.0         Algeria  ...  2593      ..       ..\n",
              "261      1990.0   1990262.0  American Samoa  ...    ..      ..       ..\n",
              "159      1990.0   1990160.0         Andorra  ...    ..      ..       ..\n",
              "\n",
              "[5 rows x 241 columns]"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 43
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "n67pKQt8iRKT"
      },
      "source": [
        "countries_1995 = countries_migration.loc[countries_migration[\"Unnamed: 0\"] == 1995.0]\r\n",
        "countries_1995 = countries_1995.sort_values(\"Unnamed: 2\")"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "Fj8BlsmMiR6W"
      },
      "source": [
        "countries_2000 = countries_migration.loc[countries_migration[\"Unnamed: 0\"] == 2000.0]\r\n",
        "countries_2000 = countries_1995.sort_values(\"Unnamed: 2\")"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "BX0He4A9iSZQ"
      },
      "source": [
        "countries_2005 = countries_migration.loc[countries_migration[\"Unnamed: 0\"] == 2005.0]\r\n",
        "countries_2 = countries_1995.sort_values(\"Unnamed: 2\")"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "ohA0y4_1iS00"
      },
      "source": [
        "countries_2010 = countries_migration.loc[countries_migration[\"Unnamed: 0\"] == 2010.0]\r\n",
        "countries_1995 = countries_1995.sort_values(\"Unnamed: 2\")"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "K_d6ZOIDiTFT"
      },
      "source": [
        "countries_2015 = countries_migration.loc[countries_migration[\"Unnamed: 0\"] == 2015.0]\r\n",
        "countries_1995 = countries_1995.sort_values(\"Unnamed: 2\")"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "gHTUsF2oic8O"
      },
      "source": [
        "countries_2017 = countries_migration.loc[countries_migration[\"Unnamed: 0\"] == 2017.0]\r\n",
        "countries_1995 = countries_1995.sort_values(\"Unnamed: 2\")"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "execution_count": 6,
      "metadata": {},
      "outputs": [],
      "source": [
        "def drop_useless_columns(df, names):\n",
        "    return df.drop(names, axis=1)"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 34,
      "metadata": {},
      "outputs": [],
      "source": [
        "df_est_refugee_stock = pd.read_excel(XLS_DIR\n",
        "            + '/total_migrant_stock.xls',\n",
        "        sheet_name = 'Table 6', header=15)\n",
        "\n",
        "df_countries_clas = pd.read_excel(XLS_DIR\n",
        "            + '/total_migrant_stock.xls',\n",
        "        sheet_name = 'ANNEX', header=15)"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 26,
      "metadata": {},
      "outputs": [],
      "source": [
        "est_refugee_stock_useless_cols = [\n",
        "    'Unnamed: 0',\n",
        "    'Unnamed: 2',\n",
        "    'Unnamed: 3',\n",
        "    'Unnamed: 4',\n",
        "]\n",
        "\n",
        "est_refugee_stock_cols = list(df_est_refugee_stock.columns)\n",
        "\n",
        "df_est_refugee_stock = df_est_refugee_stock.rename(columns={\n",
        "        est_refugee_stock_cols[1]: 'Destination'}\n",
        "        )\n",
        "\n",
        "df_est_refugee_stock = drop_useless_columns(\n",
        "        df_est_refugee_stock,\n",
        "        est_refugee_stock_useless_cols\n",
        "        )\n",
        "\n",
        "for i, col in enumerate(est_refugee_stock_cols):\n",
        "    if i < 5:\n",
        "        continue\n",
        "    \n",
        "    _col = str(col)\n",
        "\n",
        "    if '.1' in _col:  # Refugees and asylum seekers as a % of the international migrant stock\n",
        "        updt_col = _col.replace('.1', '_pct_(mf)').strip()\n",
        "    elif '-' in _col: # Annual rate of change of the refugee stock (inc. asylum seekers)\n",
        "        updt_col = _col.strip() + '_annual_rc_(mf)'\n",
        "    else:            # Refugees and asylum seekers as a % of the international migrant stock\n",
        "        updt_col = _col.strip() + '_est_(mf)'\n",
        "\n",
        "    df_est_refugee_stock = df_est_refugee_stock.rename(\n",
        "            columns={col:updt_col}\n",
        "            )\n",
        "\n",
        "df_est_refugee_stock.to_csv(CSV_DIR + '/est_refugee_stock.csv', index=False)"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 35,
      "metadata": {},
      "outputs": [],
      "source": [
        "countries_clas_useless_cols = [\n",
        "    'Index',\n",
        "    'Notes',\n",
        "    'Code',\n",
        "    'Type of data',\n",
        "]\n",
        "\n",
        "countries_clas_cols = list(df_countries_clas.columns)\n",
        "\n",
        "df_countries_clas = df_countries_clas.rename(columns={\n",
        "        countries_clas_cols[1]: 'Destination'}\n",
        "        )\n",
        "\n",
        "df_countries_clas = drop_useless_columns(\n",
        "        df_countries_clas,\n",
        "        countries_clas_useless_cols\n",
        "        )\n",
        "\n",
        "df_countries_clas = df_countries_clas.replace('Y', 1, regex=True)\n",
        "df_countries_clas.to_csv(CSV_DIR + '/countries_class_by_region.csv', index=False)"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {},
      "outputs": [],
      "source": []
    }
  ]
}