preprocess_country_codes.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Preprocessing of country code\n",
    "\n",
    "raw data of country codes don't have proper indices, so some preprocessing is required"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import polars as pl"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "filepath = \"data/official_country_names.csv\"\n",
    "url = \"https://raw.githubusercontent.com/datasets/country-codes/master/data/country-codes.csv\"\n",
    "try:\n",
    "    official_country_names_df = pd.read_csv(url)\n",
    "except Exception as e: # NOTE: fallback to locally stored file if link is dead\n",
    "    official_country_names_df = pd.read_csv(filepath)\n",
    "official_country_names_df = pl.from_pandas(official_country_names_df)\n",
    "official_country_names_df.write_csv(filepath)\n",
    "official_country_names = official_country_names_df.select(\"official_name_en\").to_dict()[\"official_name_en\"].drop_nulls()\n",
    "type(official_country_names)\n",
    "\",\".join(official_country_names.to_list())\n",
    "official_country_names = official_country_names.to_list()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'Libanonská republika,Republika Kazachstán,Ukrajina,Bulharská republika,Íránská islámská republika,Chorvatská republika,Indonéská republika,Ostatní,Namibijská republika,Egyptská arabská republika,Korejská republika,Republika Uzbekistán,Arménská republika,Slovenská republika,Moldavská republika,Maďarsko,Jemenská republika,Ruská federace,Vietnamská socialistická republika,Kyrgyzská republika,Česká republika,Peruánská republika,Stát Izrael,Spolková republika Německo,Ázerbájdžánská republika,Afghánská islámská republika,Běloruská republika'"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "lf = pl.scan_csv(\"data/prf_uchazeci.csv\")\n",
    "pdf = lf.with_columns(pl.col(\"STAT_OBCANSTVI\")).collect()\n",
    "dataset_countrynames = pdf.select(\"STAT_OBCANSTVI\").unique().to_dict()[\"STAT_OBCANSTVI\"]\n",
    "\",\".join(list(dataset_countrynames))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div><style>\n",
       ".dataframe > thead > tr,\n",
       ".dataframe > tbody > tr {\n",
       "  text-align: right;\n",
       "  white-space: pre-wrap;\n",
       "}\n",
       "</style>\n",
       "<small>shape: (27, 2)</small><table border=\"1\" class=\"dataframe\"><thead><tr><th>country_name</th><th>official_name_en</th></tr><tr><td>str</td><td>str</td></tr></thead><tbody><tr><td>&quot;Stát Izrael&quot;</td><td>&quot;Israel&quot;</td></tr><tr><td>&quot;Vietnamská soc…</td><td>&quot;Viet Nam&quot;</td></tr><tr><td>&quot;Kyrgyzská repu…</td><td>&quot;Kyrgyzstan&quot;</td></tr><tr><td>&quot;Moldavská repu…</td><td>&quot;Republic of Mo…</td></tr><tr><td>&quot;Arménská repub…</td><td>&quot;Armenia&quot;</td></tr><tr><td>&hellip;</td><td>&hellip;</td></tr><tr><td>&quot;Libanonská rep…</td><td>&quot;Lebanon&quot;</td></tr><tr><td>&quot;Ostatní&quot;</td><td>null</td></tr><tr><td>&quot;Běloruská repu…</td><td>&quot;Belarus&quot;</td></tr><tr><td>&quot;Egyptská arabs…</td><td>&quot;Egypt&quot;</td></tr><tr><td>&quot;Slovenská repu…</td><td>&quot;Slovakia&quot;</td></tr></tbody></table></div>"
      ],
      "text/plain": [
       "shape: (27, 2)\n",
       "┌───────────────────────────────────┬─────────────────────┐\n",
       "│ country_name                      ┆ official_name_en    │\n",
       "│ ---                               ┆ ---                 │\n",
       "│ str                               ┆ str                 │\n",
       "╞═══════════════════════════════════╪═════════════════════╡\n",
       "│ Stát Izrael                       ┆ Israel              │\n",
       "│ Vietnamská socialistická republi… ┆ Viet Nam            │\n",
       "│ Kyrgyzská republika               ┆ Kyrgyzstan          │\n",
       "│ Moldavská republika               ┆ Republic of Moldova │\n",
       "│ Arménská republika                ┆ Armenia             │\n",
       "│ …                                 ┆ …                   │\n",
       "│ Libanonská republika              ┆ Lebanon             │\n",
       "│ Ostatní                           ┆ null                │\n",
       "│ Běloruská republika               ┆ Belarus             │\n",
       "│ Egyptská arabská republika        ┆ Egypt               │\n",
       "│ Slovenská republika               ┆ Slovakia            │\n",
       "└───────────────────────────────────┴─────────────────────┘"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "mapping = {\n",
    "    'Stát Izrael': 'Israel',\n",
    "    'Vietnamská socialistická republika': 'Viet Nam',\n",
    "    'Kyrgyzská republika': 'Kyrgyzstan',\n",
    "    'Moldavská republika': 'Republic of Moldova',\n",
    "    'Arménská republika': 'Armenia',\n",
    "    'Republika Uzbekistán': 'Uzbekistan',\n",
    "    'Namibijská republika': 'Namibia',\n",
    "    'Íránská islámská republika': 'Iran (Islamic Republic of)',\n",
    "    'Spolková republika Německo': 'Germany',\n",
    "    'Bulharská republika': 'Bulgaria',\n",
    "    'Ruská federace': 'Russian Federation',\n",
    "    'Ukrajina': 'Ukraine',\n",
    "    'Indonéská republika': 'Indonesia',\n",
    "    'Peruánská republika': 'Peru',\n",
    "    'Jemenská republika': 'Yemen',\n",
    "    'Ázerbájdžánská republika': 'Azerbaijan',\n",
    "    'Chorvatská republika': 'Croatia',\n",
    "    'Republika Kazachstán': 'Kazakhstan',\n",
    "    'Afghánská islámská republika': 'Afghanistan',\n",
    "    'Česká republika': 'Czechia',\n",
    "    'Korejská republika': 'Republic of Korea',\n",
    "    'Maďarsko': 'Hungary',\n",
    "    'Libanonská republika': 'Lebanon',\n",
    "    'Ostatní': None,\n",
    "    'Běloruská republika': 'Belarus',\n",
    "    'Egyptská arabská republika': 'Egypt',\n",
    "    'Slovenská republika': 'Slovakia',\n",
    "}\n",
    "for k, v in mapping.items():\n",
    "    assert k in dataset_countrynames, f\"{k} not in dataset_countrynames\"\n",
    "    assert v is None or v in official_country_names, f\"{v} not in official_country_names\"\n",
    "mapping = pl.from_dict({\"country_name\": list(mapping.keys()), \"official_name_en\": list(mapping.values())})\n",
    "# Print the dictionary to confirm the mapping\n",
    "mapping"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div><style>\n",
       ".dataframe > thead > tr,\n",
       ".dataframe > tbody > tr {\n",
       "  text-align: right;\n",
       "  white-space: pre-wrap;\n",
       "}\n",
       "</style>\n",
       "<small>shape: (26, 3)</small><table border=\"1\" class=\"dataframe\"><thead><tr><th>official_name_en</th><th>country_name</th><th>ISO3166-1-Alpha-3</th></tr><tr><td>str</td><td>str</td><td>str</td></tr></thead><tbody><tr><td>&quot;Afghanistan&quot;</td><td>&quot;Afghánská islá…</td><td>&quot;AFG&quot;</td></tr><tr><td>&quot;Armenia&quot;</td><td>&quot;Arménská repub…</td><td>&quot;ARM&quot;</td></tr><tr><td>&quot;Azerbaijan&quot;</td><td>&quot;Ázerbájdžánská…</td><td>&quot;AZE&quot;</td></tr><tr><td>&quot;Belarus&quot;</td><td>&quot;Běloruská repu…</td><td>&quot;BLR&quot;</td></tr><tr><td>&quot;Bulgaria&quot;</td><td>&quot;Bulharská repu…</td><td>&quot;BGR&quot;</td></tr><tr><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td></tr><tr><td>&quot;Slovakia&quot;</td><td>&quot;Slovenská repu…</td><td>&quot;SVK&quot;</td></tr><tr><td>&quot;Ukraine&quot;</td><td>&quot;Ukrajina&quot;</td><td>&quot;UKR&quot;</td></tr><tr><td>&quot;Uzbekistan&quot;</td><td>&quot;Republika Uzbe…</td><td>&quot;UZB&quot;</td></tr><tr><td>&quot;Viet Nam&quot;</td><td>&quot;Vietnamská soc…</td><td>&quot;VNM&quot;</td></tr><tr><td>&quot;Yemen&quot;</td><td>&quot;Jemenská repub…</td><td>&quot;YEM&quot;</td></tr></tbody></table></div>"
      ],
      "text/plain": [
       "shape: (26, 3)\n",
       "┌──────────────────┬───────────────────────────────────┬───────────────────┐\n",
       "│ official_name_en ┆ country_name                      ┆ ISO3166-1-Alpha-3 │\n",
       "│ ---              ┆ ---                               ┆ ---               │\n",
       "│ str              ┆ str                               ┆ str               │\n",
       "╞══════════════════╪═══════════════════════════════════╪═══════════════════╡\n",
       "│ Afghanistan      ┆ Afghánská islámská republika      ┆ AFG               │\n",
       "│ Armenia          ┆ Arménská republika                ┆ ARM               │\n",
       "│ Azerbaijan       ┆ Ázerbájdžánská republika          ┆ AZE               │\n",
       "│ Belarus          ┆ Běloruská republika               ┆ BLR               │\n",
       "│ Bulgaria         ┆ Bulharská republika               ┆ BGR               │\n",
       "│ …                ┆ …                                 ┆ …                 │\n",
       "│ Slovakia         ┆ Slovenská republika               ┆ SVK               │\n",
       "│ Ukraine          ┆ Ukrajina                          ┆ UKR               │\n",
       "│ Uzbekistan       ┆ Republika Uzbekistán              ┆ UZB               │\n",
       "│ Viet Nam         ┆ Vietnamská socialistická republi… ┆ VNM               │\n",
       "│ Yemen            ┆ Jemenská republika                ┆ YEM               │\n",
       "└──────────────────┴───────────────────────────────────┴───────────────────┘"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "country_codes_df = official_country_names_df.join(mapping, on=\"official_name_en\", how=\"left\")\n",
    "country_codes_df = country_codes_df.select([\"official_name_en\", \"country_name\", \"ISO3166-1-Alpha-3\"])\n",
    "country_codes_df = country_codes_df.filter(pl.col(\"country_name\").is_not_null())\n",
    "country_codes_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [],
   "source": [
    "country_codes_df.write_csv(\"data/country_codes.csv\")"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "3.10",
   "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.10.14"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}