processing_geonames_altnames.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import duckdb as db\n",
    "import json"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Download the geonames.org alternative Name Database\n",
    "\n",
    "- Go to https://download.geonames.org/export/dump/\n",
    "- Download the `alternateNamesV2.zip`"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "alternate_names_columns = ['alternateNameId', 'geonameid', 'isolanguage', 'alternate name', 'isPreferredName', 'isShortName', 'isColloquial', 'isHistoric', 'from', 'to']\n",
    "alternate_names_dtypes = {'alternateNameId': int, 'geonameid': int, 'isolanguage': str, 'alternate name': str, 'isPreferredName':str, 'isShortName':str, 'isColloquial':str, 'isHistoric':str, 'from':str, 'to': str}\n",
    "filename = './alternateNamesV2.txt'\n",
    "alname_df = pd.read_csv(filename, sep='\\t', header=None, names=alternate_names_columns, dtype=alternate_names_dtypes)\n",
    "alname_df.fillna('', inplace=True)"
   ]
  },
  {
   "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>alternateNameId</th>\n",
       "      <th>geonameid</th>\n",
       "      <th>isolanguage</th>\n",
       "      <th>alternate name</th>\n",
       "      <th>isPreferredName</th>\n",
       "      <th>isShortName</th>\n",
       "      <th>isColloquial</th>\n",
       "      <th>isHistoric</th>\n",
       "      <th>from</th>\n",
       "      <th>to</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1284819</td>\n",
       "      <td>2994701</td>\n",
       "      <td></td>\n",
       "      <td>Roc Mélé</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>1</th>\n",
       "      <td>1284820</td>\n",
       "      <td>2994701</td>\n",
       "      <td></td>\n",
       "      <td>Roc Meler</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>1291197</td>\n",
       "      <td>3017832</td>\n",
       "      <td></td>\n",
       "      <td>Pic de les Abelletes</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>3</th>\n",
       "      <td>4290387</td>\n",
       "      <td>3017832</td>\n",
       "      <td></td>\n",
       "      <td>Pic de la Font-Nègre</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>4</th>\n",
       "      <td>1291198</td>\n",
       "      <td>3017833</td>\n",
       "      <td></td>\n",
       "      <td>Estany de les Abelletes</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",
       "</div>"
      ],
      "text/plain": [
       "   alternateNameId  geonameid isolanguage           alternate name  \\\n",
       "0          1284819    2994701                             Roc Mélé   \n",
       "1          1284820    2994701                            Roc Meler   \n",
       "2          1291197    3017832                 Pic de les Abelletes   \n",
       "3          4290387    3017832                 Pic de la Font-Nègre   \n",
       "4          1291198    3017833              Estany de les Abelletes   \n",
       "\n",
       "  isPreferredName isShortName isColloquial isHistoric from to  \n",
       "0                                                              \n",
       "1                                                              \n",
       "2                                                              \n",
       "3                                                              \n",
       "4                                                              "
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "alname_df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Processing the alternative/foreign language database"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Preparsing\n",
    "- ISO language code are 2 letters (Major filter)\n",
    "- We need to remove where ISO language values are empty\n",
    "- We need to remove where Geoname ID values are empty\n",
    "- We need to remove where Alternative name values are empty"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "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>alternateNameId</th>\n",
       "      <th>geonameid</th>\n",
       "      <th>isolanguage</th>\n",
       "      <th>alternate name</th>\n",
       "      <th>isPreferredName</th>\n",
       "      <th>isShortName</th>\n",
       "      <th>isColloquial</th>\n",
       "      <th>isHistoric</th>\n",
       "      <th>from</th>\n",
       "      <th>to</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2181137</td>\n",
       "      <td>3038886</td>\n",
       "      <td>ca</td>\n",
       "      <td>Pic de Tristaina</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>1</th>\n",
       "      <td>13898832</td>\n",
       "      <td>3038899</td>\n",
       "      <td>ca</td>\n",
       "      <td>Tossalet i Vinyals</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>13898833</td>\n",
       "      <td>3038914</td>\n",
       "      <td>ca</td>\n",
       "      <td>Canal de la Tosa</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>3</th>\n",
       "      <td>13898834</td>\n",
       "      <td>3038995</td>\n",
       "      <td>ca</td>\n",
       "      <td>Bosc del Solobre</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>4</th>\n",
       "      <td>13898836</td>\n",
       "      <td>3039037</td>\n",
       "      <td>ca</td>\n",
       "      <td>Solà d’Engordany</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",
       "</div>"
      ],
      "text/plain": [
       "   alternateNameId  geonameid isolanguage      alternate name isPreferredName  \\\n",
       "0          2181137    3038886          ca    Pic de Tristaina               1   \n",
       "1         13898832    3038899          ca  Tossalet i Vinyals                   \n",
       "2         13898833    3038914          ca    Canal de la Tosa                   \n",
       "3         13898834    3038995          ca    Bosc del Solobre                   \n",
       "4         13898836    3039037          ca    Solà d’Engordany                   \n",
       "\n",
       "  isShortName isColloquial isHistoric from to  \n",
       "0                                              \n",
       "1                                              \n",
       "2                                              \n",
       "3                                              \n",
       "4                                              "
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "alname_df_all_values_imported = db.sql(\"\"\"SELECT *\n",
    "FROM alname_df\n",
    "WHERE geonameid is not null\n",
    "AND isolanguage !=''\n",
    "AND \"alternate name\" !='' \n",
    "AND LENGTH(isolanguage) <= 2\n",
    "\"\"\").df()\n",
    "alname_df_all_values_imported.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Bringing shortname and preferred name in the isolanguage column."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Filter rows where isPreferredName equals 1 (except for all values `en`)\n",
    "preferred_row = alname_df_all_values_imported[(alname_df_all_values_imported['isPreferredName'] == \"1\") & (alname_df_all_values_imported['isolanguage'] != \"en\")].copy()\n",
    "\n",
    "# Update the isolanguage column to 'preferred'\n",
    "preferred_row['isolanguage'] = 'preferred'\n",
    "\n",
    "# Filter rows where isShortName equals 1\n",
    "shortname_row = alname_df_all_values_imported[alname_df_all_values_imported['isShortName'] == \"1\"].copy()\n",
    "\n",
    "# Update the isolanguage column to 'shortname'\n",
    "shortname_row['isolanguage'] = 'shortname'\n",
    "\n",
    "# Concatenate the original DataFrame with the preferred row DataFrame\n",
    "alname_df_all_values_imported_pref_shortname = pd.concat([alname_df_all_values_imported, preferred_row, shortname_row], ignore_index=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "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>geonameid</th>\n",
       "      <th>isolanguage</th>\n",
       "      <th>alt_name</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>3038886</td>\n",
       "      <td>ca</td>\n",
       "      <td>Pic de Tristaina</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>3038899</td>\n",
       "      <td>ca</td>\n",
       "      <td>Tossalet i Vinyals</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3038914</td>\n",
       "      <td>ca</td>\n",
       "      <td>Canal de la Tosa</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>3038995</td>\n",
       "      <td>ca</td>\n",
       "      <td>Bosc del Solobre</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>3039037</td>\n",
       "      <td>ca</td>\n",
       "      <td>Solà d’Engordany</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7551309</th>\n",
       "      <td>7729890</td>\n",
       "      <td>shortname</td>\n",
       "      <td>North America</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7551310</th>\n",
       "      <td>8354411</td>\n",
       "      <td>shortname</td>\n",
       "      <td>Soviet Union</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7551311</th>\n",
       "      <td>8354411</td>\n",
       "      <td>shortname</td>\n",
       "      <td>СССР</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7551312</th>\n",
       "      <td>8354411</td>\n",
       "      <td>shortname</td>\n",
       "      <td>USSR</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7551313</th>\n",
       "      <td>8378500</td>\n",
       "      <td>shortname</td>\n",
       "      <td>Belgica</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>7551314 rows × 3 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "         geonameid isolanguage            alt_name\n",
       "0          3038886          ca    Pic de Tristaina\n",
       "1          3038899          ca  Tossalet i Vinyals\n",
       "2          3038914          ca    Canal de la Tosa\n",
       "3          3038995          ca    Bosc del Solobre\n",
       "4          3039037          ca    Solà d’Engordany\n",
       "...            ...         ...                 ...\n",
       "7551309    7729890   shortname       North America\n",
       "7551310    8354411   shortname        Soviet Union\n",
       "7551311    8354411   shortname                СССР\n",
       "7551312    8354411   shortname                USSR\n",
       "7551313    8378500   shortname             Belgica\n",
       "\n",
       "[7551314 rows x 3 columns]"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "alname_df_all_values = db.sql(\"\"\"SELECT geonameid, isolanguage, \"alternate name\" as alt_name\n",
    "FROM alname_df_all_values_imported_pref_shortname\n",
    "\"\"\").df()\n",
    "alname_df_all_values"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Optional Step: Taking a look at the top languages in the database (Disclaimer in step 3)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "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>isolanguage</th>\n",
       "      <th>lng_count</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>zh</td>\n",
       "      <td>1008392</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>en</td>\n",
       "      <td>728195</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>no</td>\n",
       "      <td>691172</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>es</td>\n",
       "      <td>556830</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>ru</td>\n",
       "      <td>546593</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>fa</td>\n",
       "      <td>517259</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>id</td>\n",
       "      <td>396136</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>ar</td>\n",
       "      <td>304521</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>th</td>\n",
       "      <td>265978</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>ja</td>\n",
       "      <td>196185</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>preferred</td>\n",
       "      <td>188012</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>fr</td>\n",
       "      <td>152656</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>fi</td>\n",
       "      <td>139919</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>ko</td>\n",
       "      <td>139003</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>uk</td>\n",
       "      <td>126704</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>pt</td>\n",
       "      <td>95756</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>de</td>\n",
       "      <td>87443</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>shortname</td>\n",
       "      <td>87329</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>hy</td>\n",
       "      <td>77986</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>sr</td>\n",
       "      <td>64587</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>el</td>\n",
       "      <td>56444</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>mk</td>\n",
       "      <td>56342</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>tr</td>\n",
       "      <td>54982</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>kk</td>\n",
       "      <td>52791</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>it</td>\n",
       "      <td>51753</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   isolanguage  lng_count\n",
       "0           zh    1008392\n",
       "1           en     728195\n",
       "2           no     691172\n",
       "3           es     556830\n",
       "4           ru     546593\n",
       "5           fa     517259\n",
       "6           id     396136\n",
       "7           ar     304521\n",
       "8           th     265978\n",
       "9           ja     196185\n",
       "10   preferred     188012\n",
       "11          fr     152656\n",
       "12          fi     139919\n",
       "13          ko     139003\n",
       "14          uk     126704\n",
       "15          pt      95756\n",
       "16          de      87443\n",
       "17   shortname      87329\n",
       "18          hy      77986\n",
       "19          sr      64587\n",
       "20          el      56444\n",
       "21          mk      56342\n",
       "22          tr      54982\n",
       "23          kk      52791\n",
       "24          it      51753"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "db.sql('''\n",
    "SELECT isolanguage, count(*) lng_count\n",
    "FROM alname_df_all_values\n",
    "GROUP BY isolanguage\n",
    "ORDER BY lng_count DESC\n",
    "LIMIT 25\n",
    "''').df()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Only picking out 10 languages + shortname + preferred"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "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>geonameid</th>\n",
       "      <th>isolanguage</th>\n",
       "      <th>alt_name</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>9089920</td>\n",
       "      <td>zh</td>\n",
       "      <td>拿司</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>9089921</td>\n",
       "      <td>zh</td>\n",
       "      <td>素地屋</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>9089923</td>\n",
       "      <td>zh</td>\n",
       "      <td>日阿洞</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>9089925</td>\n",
       "      <td>zh</td>\n",
       "      <td>白扭洽色</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>9089927</td>\n",
       "      <td>zh</td>\n",
       "      <td>强波隔勒</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4090910</th>\n",
       "      <td>30521</td>\n",
       "      <td>ar</td>\n",
       "      <td>Āl ‘Īsá</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4090911</th>\n",
       "      <td>30521</td>\n",
       "      <td>ar</td>\n",
       "      <td>ال عيسى</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4090912</th>\n",
       "      <td>30524</td>\n",
       "      <td>ar</td>\n",
       "      <td>الضحاكي</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4090913</th>\n",
       "      <td>30527</td>\n",
       "      <td>ar</td>\n",
       "      <td>Ḩijlān</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4090914</th>\n",
       "      <td>30530</td>\n",
       "      <td>ar</td>\n",
       "      <td>الرومية</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>4090915 rows × 3 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "         geonameid isolanguage alt_name\n",
       "0          9089920          zh       拿司\n",
       "1          9089921          zh      素地屋\n",
       "2          9089923          zh      日阿洞\n",
       "3          9089925          zh     白扭洽色\n",
       "4          9089927          zh     强波隔勒\n",
       "...            ...         ...      ...\n",
       "4090910      30521          ar  Āl ‘Īsá\n",
       "4090911      30521          ar  ال عيسى\n",
       "4090912      30524          ar  الضحاكي\n",
       "4090913      30527          ar   Ḩijlān\n",
       "4090914      30530          ar  الرومية\n",
       "\n",
       "[4090915 rows x 3 columns]"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 10 Languages selected: 'zh', 'en', 'es', 'fr', 'ja', 'pt', 'ru', 'de', 'ar', 'ko'\n",
    "alname_df_all_values_selected = db.sql('''\n",
    "SELECT *\n",
    "FROM alname_df_all_values\n",
    "WHERE isolanguage in ('zh', 'en', 'es', 'fr', 'ja', 'pt', 'ru', 'de', 'ar', 'ko', 'shortname', 'preferred')\n",
    "''').df()\n",
    "alname_df_all_values_selected"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Taking the first alt name by each language\n",
    "- Geonames have multiple entries for the same language and same location. There could be dozens of alternative names for the same location within the same language."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "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>geonameid</th>\n",
       "      <th>isolanguage</th>\n",
       "      <th>alt_name</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1727</td>\n",
       "      <td>ar</td>\n",
       "      <td>معوبرة</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1766</td>\n",
       "      <td>ar</td>\n",
       "      <td>خان خيام الوليد</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1807</td>\n",
       "      <td>ar</td>\n",
       "      <td>خشام</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1813</td>\n",
       "      <td>ar</td>\n",
       "      <td>عبود الجدعان</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2028</td>\n",
       "      <td>ar</td>\n",
       "      <td>درة بیداد العلیا</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3652220</th>\n",
       "      <td>12740426</td>\n",
       "      <td>ru</td>\n",
       "      <td>Bol’shoy Naergen</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3652221</th>\n",
       "      <td>12746331</td>\n",
       "      <td>preferred</td>\n",
       "      <td>桶屋町通り</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3652222</th>\n",
       "      <td>12746428</td>\n",
       "      <td>zh</td>\n",
       "      <td>鄂城区</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3652223</th>\n",
       "      <td>12746467</td>\n",
       "      <td>en</td>\n",
       "      <td>Masjid Mubarak</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3652224</th>\n",
       "      <td>12746811</td>\n",
       "      <td>ar</td>\n",
       "      <td>ذا لاين</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>3652225 rows × 3 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "         geonameid isolanguage          alt_name\n",
       "0             1727          ar            معوبرة\n",
       "1             1766          ar   خان خيام الوليد\n",
       "2             1807          ar              خشام\n",
       "3             1813          ar      عبود الجدعان\n",
       "4             2028          ar  درة بیداد العلیا\n",
       "...            ...         ...               ...\n",
       "3652220   12740426          ru  Bol’shoy Naergen\n",
       "3652221   12746331   preferred             桶屋町通り\n",
       "3652222   12746428          zh               鄂城区\n",
       "3652223   12746467          en    Masjid Mubarak\n",
       "3652224   12746811          ar           ذا لاين\n",
       "\n",
       "[3652225 rows x 3 columns]"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "alname_df_all_values_selected_only_first_loc = db.sql('''WITH RankedRows AS (\n",
    "    SELECT *,\n",
    "           ROW_NUMBER() OVER (PARTITION BY geonameid, isolanguage) AS row_num\n",
    "    FROM alname_df_all_values_selected\n",
    ")\n",
    "SELECT geonameid, isolanguage, alt_name\n",
    "FROM RankedRows\n",
    "WHERE row_num = 1;\n",
    "''').df()\n",
    "alname_df_all_values_selected_only_first_loc"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Choose English as one of the mandatory listed languages.\n",
    "- If English is not listed for a particular geoname entry, we will not select it."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "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>geonameid</th>\n",
       "      <th>isolanguage</th>\n",
       "      <th>alt_name</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>12181875</td>\n",
       "      <td>ru</td>\n",
       "      <td>Станция Новгород-Северский</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>12182202</td>\n",
       "      <td>en</td>\n",
       "      <td>Kamkusa Msiska</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>12182211</td>\n",
       "      <td>en</td>\n",
       "      <td>Mwafulirwa Mwambelo</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>12182323</td>\n",
       "      <td>en</td>\n",
       "      <td>Junju</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>12182457</td>\n",
       "      <td>en</td>\n",
       "      <td>Kanyenda Masanja</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1046753</th>\n",
       "      <td>6578827</td>\n",
       "      <td>zh</td>\n",
       "      <td>第三號隧道</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1046754</th>\n",
       "      <td>6586550</td>\n",
       "      <td>zh</td>\n",
       "      <td>Ti-shih-hao Ch’iao</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1046755</th>\n",
       "      <td>6589562</td>\n",
       "      <td>zh</td>\n",
       "      <td>省立臺南醫院</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1046756</th>\n",
       "      <td>6607371</td>\n",
       "      <td>en</td>\n",
       "      <td>Pushkino</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1046757</th>\n",
       "      <td>6461416</td>\n",
       "      <td>preferred</td>\n",
       "      <td>Козма презвитер</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>1046758 rows × 3 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "         geonameid isolanguage                    alt_name\n",
       "0         12181875          ru  Станция Новгород-Северский\n",
       "1         12182202          en              Kamkusa Msiska\n",
       "2         12182211          en         Mwafulirwa Mwambelo\n",
       "3         12182323          en                       Junju\n",
       "4         12182457          en            Kanyenda Masanja\n",
       "...            ...         ...                         ...\n",
       "1046753    6578827          zh                       第三號隧道\n",
       "1046754    6586550          zh          Ti-shih-hao Ch’iao\n",
       "1046755    6589562          zh                      省立臺南醫院\n",
       "1046756    6607371          en                    Pushkino\n",
       "1046757    6461416   preferred             Козма презвитер\n",
       "\n",
       "[1046758 rows x 3 columns]"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "alname_df_all_values_selected_only_first_loc_en_filtered =  db.sql('''\n",
    "SELECT t2.*\n",
    "FROM alname_df_all_values_selected_only_first_loc t2\n",
    "JOIN (\n",
    "SELECT geonameid\n",
    "FROM alname_df_all_values_selected_only_first_loc\n",
    "WHERE isolanguage = 'en') t1\n",
    "ON t2.geonameid = t1.geonameid\n",
    "''').df()\n",
    "alname_df_all_values_selected_only_first_loc_en_filtered"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Optional Step: Test how many languages are there for each geoname entry"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "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>count_lang</th>\n",
       "      <th>lang_group_count</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>12</td>\n",
       "      <td>857</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>11</td>\n",
       "      <td>1511</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>10</td>\n",
       "      <td>2407</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>9</td>\n",
       "      <td>4122</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>8</td>\n",
       "      <td>2246</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>7</td>\n",
       "      <td>2372</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>6</td>\n",
       "      <td>2990</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>5</td>\n",
       "      <td>4238</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>4</td>\n",
       "      <td>18787</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>3</td>\n",
       "      <td>55159</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>2</td>\n",
       "      <td>55981</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>1</td>\n",
       "      <td>532396</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    count_lang  lang_group_count\n",
       "0           12               857\n",
       "1           11              1511\n",
       "2           10              2407\n",
       "3            9              4122\n",
       "4            8              2246\n",
       "5            7              2372\n",
       "6            6              2990\n",
       "7            5              4238\n",
       "8            4             18787\n",
       "9            3             55159\n",
       "10           2             55981\n",
       "11           1            532396"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "db.sql('''\n",
    "SELECT count_lang, count(*) as lang_group_count\n",
    "FROM (\n",
    "\tSELECT geonameid, count(*) as count_lang\n",
    "\tFROM alname_df_all_values_selected_only_first_loc_en_filtered\n",
    "\tGROUP BY geonameid\n",
    "\tORDER BY count_lang DESC\n",
    ")\n",
    "GROUP BY count_lang\n",
    "ORDER BY count_lang DESC\n",
    "''').df()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Optional Step: Minimum number of required languages\n",
    "- If you want a tabular output select the max languges in the input language array (which is 10).\n",
    "- Here as an example we can select geonames with more than 5 alt names"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "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>geonameid</th>\n",
       "      <th>isolanguage</th>\n",
       "      <th>alt_name</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>4231976</td>\n",
       "      <td>shortname</td>\n",
       "      <td>Worth</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>4234477</td>\n",
       "      <td>ar</td>\n",
       "      <td>بروكبورت</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>4235954</td>\n",
       "      <td>ar</td>\n",
       "      <td>مقاطعة كلارك</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4237730</td>\n",
       "      <td>fr</td>\n",
       "      <td>Comté d'Effingham</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>4247802</td>\n",
       "      <td>shortname</td>\n",
       "      <td>Randolph</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>402395</th>\n",
       "      <td>1835329</td>\n",
       "      <td>preferred</td>\n",
       "      <td>대구광역시</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>402396</th>\n",
       "      <td>1839118</td>\n",
       "      <td>en</td>\n",
       "      <td>Biyangdo Island</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>402397</th>\n",
       "      <td>1855274</td>\n",
       "      <td>ja</td>\n",
       "      <td>西蒲原郡</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>402398</th>\n",
       "      <td>1503219</td>\n",
       "      <td>en</td>\n",
       "      <td>Kizhi-Aksy</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>402399</th>\n",
       "      <td>2023427</td>\n",
       "      <td>ru</td>\n",
       "      <td>Исток</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>402400 rows × 3 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "        geonameid isolanguage           alt_name\n",
       "0         4231976   shortname              Worth\n",
       "1         4234477          ar           بروكبورت\n",
       "2         4235954          ar       مقاطعة كلارك\n",
       "3         4237730          fr  Comté d'Effingham\n",
       "4         4247802   shortname           Randolph\n",
       "...           ...         ...                ...\n",
       "402395    1835329   preferred              대구광역시\n",
       "402396    1839118          en    Biyangdo Island\n",
       "402397    1855274          ja               西蒲原郡\n",
       "402398    1503219          en         Kizhi-Aksy\n",
       "402399    2023427          ru              Исток\n",
       "\n",
       "[402400 rows x 3 columns]"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "db.sql('''\n",
    "SELECT t1.*\n",
    "FROM alname_df_all_values_selected_only_first_loc_en_filtered t1\n",
    "JOIN (\n",
    "\tSELECT geonameid, count(*) as count_lang\n",
    "\tFROM alname_df_all_values_selected_only_first_loc_en_filtered\n",
    "\tGROUP BY geonameid\n",
    ") t2\n",
    "ON t1.geonameid = t2.geonameid\n",
    "WHERE t2.count_lang > 2\n",
    "''').df()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Optional Step: JSONification of the language and alt_names\n",
    "- The dictionary represented in a JSON like string"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "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>geonameid</th>\n",
       "      <th>iso_language_alt_names</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>14</td>\n",
       "      <td>{\"en\": \"Takht Arreh Do\", \"ar\": \"تخت أرة دو\"}</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>254</td>\n",
       "      <td>{\"en\": \"Pol-e Bala Rud\", \"ar\": \"جسر بالارود\"}</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>301</td>\n",
       "      <td>{\"fr\": \"Shahr-e ziba\", \"en\": \"Shahr-e Ziba\"}</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>470</td>\n",
       "      <td>{\"fr\": \"place Azadi\", \"pt\": \"Praça Azadi\", \"zh...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>566</td>\n",
       "      <td>{\"de\": \"Ghar-e-Roodafshan\", \"fr\": \"Ghar-e-Rood...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>683061</th>\n",
       "      <td>12746714</td>\n",
       "      <td>{\"en\": \"Bosiljevo interchange\"}</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>683062</th>\n",
       "      <td>12746729</td>\n",
       "      <td>{\"en\": \"Ringwood Magistrates Court\"}</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>683063</th>\n",
       "      <td>12746744</td>\n",
       "      <td>{\"en\": \"Appleton Dock\"}</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>683064</th>\n",
       "      <td>12746795</td>\n",
       "      <td>{\"ja\": \"葛城地蔵尊\", \"en\": \"Katsuragi Kṣitigarbha\",...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>683065</th>\n",
       "      <td>12746811</td>\n",
       "      <td>{\"de\": \"The Line\", \"en\": \"The Line\", \"ru\": \"Ли...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>683066 rows × 2 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "        geonameid                             iso_language_alt_names\n",
       "0              14       {\"en\": \"Takht Arreh Do\", \"ar\": \"تخت أرة دو\"}\n",
       "1             254      {\"en\": \"Pol-e Bala Rud\", \"ar\": \"جسر بالارود\"}\n",
       "2             301       {\"fr\": \"Shahr-e ziba\", \"en\": \"Shahr-e Ziba\"}\n",
       "3             470  {\"fr\": \"place Azadi\", \"pt\": \"Praça Azadi\", \"zh...\n",
       "4             566  {\"de\": \"Ghar-e-Roodafshan\", \"fr\": \"Ghar-e-Rood...\n",
       "...           ...                                                ...\n",
       "683061   12746714                    {\"en\": \"Bosiljevo interchange\"}\n",
       "683062   12746729               {\"en\": \"Ringwood Magistrates Court\"}\n",
       "683063   12746744                            {\"en\": \"Appleton Dock\"}\n",
       "683064   12746795  {\"ja\": \"葛城地蔵尊\", \"en\": \"Katsuragi Kṣitigarbha\",...\n",
       "683065   12746811  {\"de\": \"The Line\", \"en\": \"The Line\", \"ru\": \"Ли...\n",
       "\n",
       "[683066 rows x 2 columns]"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "alname_df_all_values_selected_only_first_loc_en_filtered_jsonified  = alname_df_all_values_selected_only_first_loc_en_filtered.groupby('geonameid').apply(lambda x: json.dumps(dict(zip(x['isolanguage'], x['alt_name'])), ensure_ascii=False)).reset_index()\n",
    "alname_df_all_values_selected_only_first_loc_en_filtered_jsonified.columns = ['geonameid', 'iso_language_alt_names']\n",
    "alname_df_all_values_selected_only_first_loc_en_filtered_jsonified"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Converting the dataset in to Python Dictionary for JSON output"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [],
   "source": [
    "alname_df_all_values_selected_only_first_loc_en_filtered_dictionariezed_df  = alname_df_all_values_selected_only_first_loc_en_filtered.groupby('geonameid').apply(lambda x: dict(zip(x['isolanguage'], x['alt_name']))).reset_index()\n",
    "alname_df_all_values_selected_only_first_loc_en_filtered_dictionariezed_df.columns = ['geonameid', 'iso_language_alt_names']\n",
    "\n",
    "alname_df_all_values_selected_only_first_loc_en_filtered_dict = alname_df_all_values_selected_only_first_loc_en_filtered_dictionariezed_df.set_index('geonameid')['iso_language_alt_names'].to_dict()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Testing out the different process dataset format"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Test 1: Dataset output"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "|        |   geonameid | isolanguage   | alt_name    |\n",
      "|-------:|------------:|:--------------|:------------|\n",
      "|  62447 |     1269750 | fr            | Inde        |\n",
      "| 111100 |     1269750 | ar            | الهند       |\n",
      "| 245786 |     1269750 | ru            | Индия       |\n",
      "| 278989 |     1269750 | es            | Unión India |\n",
      "| 314823 |     1269750 | zh            | 印度        |\n",
      "| 444853 |     1269750 | preferred     | Ινδία       |\n",
      "| 581391 |     1269750 | ja            | インド      |\n",
      "| 614529 |     1269750 | en            | India       |\n",
      "| 659641 |     1269750 | ko            | 인도        |\n",
      "| 817618 |     1269750 | de            | Indien      |\n",
      "| 993566 |     1269750 | pt            | Índia       |\n"
     ]
    }
   ],
   "source": [
    "print(alname_df_all_values_selected_only_first_loc_en_filtered[alname_df_all_values_selected_only_first_loc_en_filtered[\"geonameid\"]==1269750].to_markdown())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Test 2: JSONified table output"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "┌───────────┬──────────────────────────────────────────────────────────────────────────────────────────────────────────┐\n",
       "│ geonameid │                                          iso_language_alt_names                                          │\n",
       "│   int32   │                                                 varchar                                                  │\n",
       "├───────────┼──────────────────────────────────────────────────────────────────────────────────────────────────────────┤\n",
       "│   1269750 │ {\"fr\": \"Inde\", \"ar\": \"الهند\", \"ru\": \"Индия\", \"es\": \"Unión India\", \"zh\": \"印度\", \"preferred\": \"Ινδία\", …  │\n",
       "└───────────┴──────────────────────────────────────────────────────────────────────────────────────────────────────────┘"
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "db.sql('''\n",
    "SELECT *\n",
    "FROM alname_df_all_values_selected_only_first_loc_en_filtered_jsonified\n",
    "WHERE geonameid==1269750\n",
    "''')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Test 3: Dictionary (/JSON) output"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{'fr': 'Inde',\n",
       " 'ar': 'الهند',\n",
       " 'ru': 'Индия',\n",
       " 'es': 'Unión India',\n",
       " 'zh': '印度',\n",
       " 'preferred': 'Ινδία',\n",
       " 'ja': 'インド',\n",
       " 'en': 'India',\n",
       " 'ko': '인도',\n",
       " 'de': 'Indien',\n",
       " 'pt': 'Índia'}"
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "alname_df_all_values_selected_only_first_loc_en_filtered_dict[1269750]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Saving the files\n",
    "\n",
    "The dataset in CSV and the JSON in JSON is available in the zip file `geoname_alt_names.zip`"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [],
   "source": [
    "alname_df_all_values_selected_only_first_loc_en_filtered.to_csv(\"geoname_alt_names.csv\", index=False, encoding='utf-8')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [],
   "source": [
    "with open(\"geoname_alt_names.json\", \"w\", encoding='utf-8') as file:\n",
    "    json.dump(alname_df_all_values_selected_only_first_loc_en_filtered_dict, file, ensure_ascii=False)"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.11.5"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}