scripts/annotated-poi-extraction.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "552e6c62",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import os\n",
    "import xml.dom.minidom\n",
    "import xml.etree.cElementTree as et\n",
    "import numpy as np"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "bdfaf448",
   "metadata": {},
   "source": [
    "## GeoNames POI Extraction"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "78b6ac3a",
   "metadata": {},
   "outputs": [],
   "source": [
    "dir_geonames = os.path.dirname(os.getcwd())+'\\\\data\\\\gazetteers\\\\allCountries.txt'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "a09b9283",
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\Users\\Zilong Liu\\anaconda3\\lib\\site-packages\\IPython\\core\\interactiveshell.py:3165: DtypeWarning: Columns (8,9,10,11,12,13) have mixed types.Specify dtype option on import or set low_memory=False.\n",
      "  has_raised = await self.run_ast_nodes(code_ast.body, cell_name,\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>geonameid</th>\n",
       "      <th>name</th>\n",
       "      <th>asciiname</th>\n",
       "      <th>alternatenames</th>\n",
       "      <th>latitude</th>\n",
       "      <th>longitude</th>\n",
       "      <th>feature class</th>\n",
       "      <th>feature code</th>\n",
       "      <th>country code</th>\n",
       "      <th>cc2</th>\n",
       "      <th>admin1 code</th>\n",
       "      <th>admin2 code</th>\n",
       "      <th>admin3 code</th>\n",
       "      <th>admin4 code</th>\n",
       "      <th>population</th>\n",
       "      <th>elevation</th>\n",
       "      <th>dem</th>\n",
       "      <th>timezone</th>\n",
       "      <th>modification date</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2986043</td>\n",
       "      <td>Pic de Font Blanca</td>\n",
       "      <td>Pic de Font Blanca</td>\n",
       "      <td>Pic de Font Blanca,Pic du Port</td>\n",
       "      <td>42.64991</td>\n",
       "      <td>1.53335</td>\n",
       "      <td>T</td>\n",
       "      <td>PK</td>\n",
       "      <td>AD</td>\n",
       "      <td>NaN</td>\n",
       "      <td>00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2860</td>\n",
       "      <td>Europe/Andorra</td>\n",
       "      <td>2014-11-05</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2994701</td>\n",
       "      <td>Roc Mélé</td>\n",
       "      <td>Roc Mele</td>\n",
       "      <td>Roc Mele,Roc Meler,Roc Mélé</td>\n",
       "      <td>42.58765</td>\n",
       "      <td>1.74028</td>\n",
       "      <td>T</td>\n",
       "      <td>MT</td>\n",
       "      <td>AD</td>\n",
       "      <td>AD,FR</td>\n",
       "      <td>00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2803</td>\n",
       "      <td>Europe/Andorra</td>\n",
       "      <td>2020-06-10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3007683</td>\n",
       "      <td>Pic des Langounelles</td>\n",
       "      <td>Pic des Langounelles</td>\n",
       "      <td>Pic des Langounelles</td>\n",
       "      <td>42.61203</td>\n",
       "      <td>1.47364</td>\n",
       "      <td>T</td>\n",
       "      <td>PK</td>\n",
       "      <td>AD</td>\n",
       "      <td>AD,FR</td>\n",
       "      <td>00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2685</td>\n",
       "      <td>Europe/Andorra</td>\n",
       "      <td>2014-11-05</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>3017832</td>\n",
       "      <td>Pic de les Abelletes</td>\n",
       "      <td>Pic de les Abelletes</td>\n",
       "      <td>Pic de la Font-Negre,Pic de la Font-Nègre,Pic ...</td>\n",
       "      <td>42.52535</td>\n",
       "      <td>1.73343</td>\n",
       "      <td>T</td>\n",
       "      <td>PK</td>\n",
       "      <td>AD</td>\n",
       "      <td>FR</td>\n",
       "      <td>A9</td>\n",
       "      <td>66</td>\n",
       "      <td>663.0</td>\n",
       "      <td>66146.0</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2411</td>\n",
       "      <td>Europe/Andorra</td>\n",
       "      <td>2014-11-05</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>3017833</td>\n",
       "      <td>Estany de les Abelletes</td>\n",
       "      <td>Estany de les Abelletes</td>\n",
       "      <td>Estany de les Abelletes,Etang de Font-Negre,Ét...</td>\n",
       "      <td>42.52915</td>\n",
       "      <td>1.73362</td>\n",
       "      <td>H</td>\n",
       "      <td>LK</td>\n",
       "      <td>AD</td>\n",
       "      <td>FR</td>\n",
       "      <td>A9</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2260</td>\n",
       "      <td>Europe/Andorra</td>\n",
       "      <td>2014-11-05</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   geonameid                     name                asciiname  \\\n",
       "0    2986043       Pic de Font Blanca       Pic de Font Blanca   \n",
       "1    2994701                 Roc Mélé                 Roc Mele   \n",
       "2    3007683     Pic des Langounelles     Pic des Langounelles   \n",
       "3    3017832     Pic de les Abelletes     Pic de les Abelletes   \n",
       "4    3017833  Estany de les Abelletes  Estany de les Abelletes   \n",
       "\n",
       "                                      alternatenames  latitude  longitude  \\\n",
       "0                     Pic de Font Blanca,Pic du Port  42.64991    1.53335   \n",
       "1                        Roc Mele,Roc Meler,Roc Mélé  42.58765    1.74028   \n",
       "2                               Pic des Langounelles  42.61203    1.47364   \n",
       "3  Pic de la Font-Negre,Pic de la Font-Nègre,Pic ...  42.52535    1.73343   \n",
       "4  Estany de les Abelletes,Etang de Font-Negre,Ét...  42.52915    1.73362   \n",
       "\n",
       "  feature class feature code country code    cc2 admin1 code admin2 code  \\\n",
       "0             T           PK           AD    NaN          00         NaN   \n",
       "1             T           MT           AD  AD,FR          00         NaN   \n",
       "2             T           PK           AD  AD,FR          00         NaN   \n",
       "3             T           PK           AD     FR          A9          66   \n",
       "4             H           LK           AD     FR          A9         NaN   \n",
       "\n",
       "  admin3 code admin4 code  population  elevation   dem        timezone  \\\n",
       "0         NaN         NaN           0        NaN  2860  Europe/Andorra   \n",
       "1         NaN         NaN           0        NaN  2803  Europe/Andorra   \n",
       "2         NaN         NaN           0        NaN  2685  Europe/Andorra   \n",
       "3       663.0     66146.0           0        NaN  2411  Europe/Andorra   \n",
       "4         NaN         NaN           0        NaN  2260  Europe/Andorra   \n",
       "\n",
       "  modification date  \n",
       "0        2014-11-05  \n",
       "1        2020-06-10  \n",
       "2        2014-11-05  \n",
       "3        2014-11-05  \n",
       "4        2014-11-05  "
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_geonames = pd.read_csv(dir_geonames,sep='\\t',names=('geonameid','name','asciiname','alternatenames','latitude',\n",
    "                                                        'longitude','feature class','feature code','country code','cc2',\n",
    "                                                        'admin1 code','admin2 code','admin3 code','admin4 code','population',\n",
    "                                                        'elevation','dem','timezone','modification date'))\n",
    "df_geonames.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "714f2def",
   "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>name</th>\n",
       "      <th>asciiname</th>\n",
       "      <th>latitude</th>\n",
       "      <th>longitude</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2986043</td>\n",
       "      <td>Pic de Font Blanca</td>\n",
       "      <td>Pic de Font Blanca</td>\n",
       "      <td>42.64991</td>\n",
       "      <td>1.53335</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2994701</td>\n",
       "      <td>Roc Mélé</td>\n",
       "      <td>Roc Mele</td>\n",
       "      <td>42.58765</td>\n",
       "      <td>1.74028</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3007683</td>\n",
       "      <td>Pic des Langounelles</td>\n",
       "      <td>Pic des Langounelles</td>\n",
       "      <td>42.61203</td>\n",
       "      <td>1.47364</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>3017832</td>\n",
       "      <td>Pic de les Abelletes</td>\n",
       "      <td>Pic de les Abelletes</td>\n",
       "      <td>42.52535</td>\n",
       "      <td>1.73343</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>3017833</td>\n",
       "      <td>Estany de les Abelletes</td>\n",
       "      <td>Estany de les Abelletes</td>\n",
       "      <td>42.52915</td>\n",
       "      <td>1.73362</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   geonameid                     name                asciiname  latitude  \\\n",
       "0    2986043       Pic de Font Blanca       Pic de Font Blanca  42.64991   \n",
       "1    2994701                 Roc Mélé                 Roc Mele  42.58765   \n",
       "2    3007683     Pic des Langounelles     Pic des Langounelles  42.61203   \n",
       "3    3017832     Pic de les Abelletes     Pic de les Abelletes  42.52535   \n",
       "4    3017833  Estany de les Abelletes  Estany de les Abelletes  42.52915   \n",
       "\n",
       "   longitude  \n",
       "0    1.53335  \n",
       "1    1.74028  \n",
       "2    1.47364  \n",
       "3    1.73343  \n",
       "4    1.73362  "
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_geonames = df_geonames[['geonameid','name','asciiname','latitude','longitude']]\n",
    "df_geonames.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "8db67591",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_geonames.to_csv(os.path.dirname(os.getcwd())+'\\\\data\\\\extracted-annotated-locations\\\\GeoNames_poi.csv')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "70d30ec3",
   "metadata": {},
   "source": [
    "## GeoWiki POI Extraction"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "34f0d5e9",
   "metadata": {},
   "outputs": [],
   "source": [
    "dir_geowiki = os.path.dirname(os.getcwd())+'\\\\data\\\\training-corpora\\\\geowiki.txt'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "3079b4e4",
   "metadata": {},
   "outputs": [],
   "source": [
    "num = 0\n",
    "geowiki_output_file = open(os.path.dirname(os.getcwd())+'\\\\data\\\\extracted-annotated-locations\\\\GeoWiki_poi.csv','w',encoding=\"UTF-8\")\n",
    "geowiki_output_file.write('Place\\tLatitude\\tLongitude\\n')\n",
    "for line in open(dir_geowiki,'r',encoding=\"UTF-8\"):\n",
    "    num += 1\n",
    "    if (num % 2 == 0):\n",
    "        line = line.strip('\\n')\n",
    "        line = line.split('\\t')\n",
    "        geowiki_output_file.write(line[1]+'\\t'+line[2]+'\\t'+line[3]+'\\n')\n",
    "geowiki_output_file.close()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "1cf453dd",
   "metadata": {},
   "source": [
    "## Define MLG Key Calculation Function"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "5afebc75",
   "metadata": {},
   "outputs": [],
   "source": [
    "def calc_mlg_key(toponym, old_coord):\n",
    "    toponym = toponym.strip(' ')\n",
    "    old_coord_split = old_coord.split(',')\n",
    "    old_lat = old_coord_split[0].strip(' ')\n",
    "    old_lon = old_coord_split[1].strip(' ')\n",
    "    return toponym+' '+old_lat+','+old_lon"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "402a1570",
   "metadata": {},
   "source": [
    "## LGL POI Extraction"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "2ce22946",
   "metadata": {},
   "outputs": [],
   "source": [
    "xml_lgl = os.path.dirname(os.getcwd())+'\\\\data\\\\evaluation-corpora\\\\original-datasets\\\\lgl.xml'\n",
    "parsed_xml_lgl = et.parse(xml_lgl)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "e4130ef9",
   "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>url</th>\n",
       "      <th>text</th>\n",
       "      <th>toponyms</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>http://www.concordmonitor.com/apps/pbcs.dll/ar...</td>\n",
       "      <td>Alexandria woman charged in connection with Ke...</td>\n",
       "      <td>[{'name': 'Alexandria', 'start': '0', 'end': '...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>http://www.concordmonitor.com/apps/pbcs.dll/ar...</td>\n",
       "      <td>Avoyelles task force arrests 14. The Task Forc...</td>\n",
       "      <td>[{'name': 'Avoyelles Parish', 'start': '0', 'e...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>http://www.concordmonitor.com/apps/pbcs.dll/ar...</td>\n",
       "      <td>Alexandria parents charged with hitting their ...</td>\n",
       "      <td>[{'name': 'Alexandria', 'start': '0', 'end': '...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>http://www.concordmonitor.com/apps/pbcs.dll/ar...</td>\n",
       "      <td>Deputies arrest Pointe Coupee teen on weapon c...</td>\n",
       "      <td>[{'name': 'Pointe Coupee', 'start': '16', 'end...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>http://www.concordmonitor.com/apps/pbcs.dll/ar...</td>\n",
       "      <td>Water boil advisory issued in downtown Pinevil...</td>\n",
       "      <td>[{'name': 'Pineville', 'start': '39', 'end': '...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                                 url  \\\n",
       "0  http://www.concordmonitor.com/apps/pbcs.dll/ar...   \n",
       "1  http://www.concordmonitor.com/apps/pbcs.dll/ar...   \n",
       "2  http://www.concordmonitor.com/apps/pbcs.dll/ar...   \n",
       "3  http://www.concordmonitor.com/apps/pbcs.dll/ar...   \n",
       "4  http://www.concordmonitor.com/apps/pbcs.dll/ar...   \n",
       "\n",
       "                                                text  \\\n",
       "0  Alexandria woman charged in connection with Ke...   \n",
       "1  Avoyelles task force arrests 14. The Task Forc...   \n",
       "2  Alexandria parents charged with hitting their ...   \n",
       "3  Deputies arrest Pointe Coupee teen on weapon c...   \n",
       "4  Water boil advisory issued in downtown Pinevil...   \n",
       "\n",
       "                                            toponyms  \n",
       "0  [{'name': 'Alexandria', 'start': '0', 'end': '...  \n",
       "1  [{'name': 'Avoyelles Parish', 'start': '0', 'e...  \n",
       "2  [{'name': 'Alexandria', 'start': '0', 'end': '...  \n",
       "3  [{'name': 'Pointe Coupee', 'start': '16', 'end...  \n",
       "4  [{'name': 'Pineville', 'start': '39', 'end': '...  "
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "url_list = []\n",
    "text_list = []\n",
    "toponyms_list = []\n",
    "\n",
    "for article in parsed_xml_lgl.getroot():\n",
    "    url = article.find('url')\n",
    "    text = article.find('text')\n",
    "    toponyms = article.find('toponyms')\n",
    "    toponym_list = []\n",
    "    for toponym in toponyms:\n",
    "        start = toponym.find('start')\n",
    "        end = toponym.find('end')\n",
    "        gaztag = toponym.find('gaztag')\n",
    "        try:\n",
    "            name = gaztag.find('name')\n",
    "            lat = gaztag.find('lat')\n",
    "            lon = gaztag.find('lon')\n",
    "            toponym_list.append({'name':name.text, 'start':start.text, 'end': end.text, 'lat': lat.text, 'lon': lon.text})    \n",
    "        except AttributeError:\n",
    "            name = toponym.find('phrase')\n",
    "            toponym_list.append({'name':name.text, 'start':start.text, 'end': end.text, 'lat': None, 'lon': None})    \n",
    "    \n",
    "    url_list.append(url.text)\n",
    "    text_list.append(text.text)\n",
    "    toponyms_list.append(toponym_list)\n",
    "    \n",
    "df_lgl = pd.DataFrame({'url' :url.text, 'text': text_list, 'toponyms': toponyms_list})\n",
    "df_lgl.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "4bea8dc9",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 5088 entries, 0 to 5087\n",
      "Data columns (total 3 columns):\n",
      " #   Column  Non-Null Count  Dtype \n",
      "---  ------  --------------  ----- \n",
      " 0   name    5088 non-null   object\n",
      " 1   lat     4462 non-null   object\n",
      " 2   lon     4462 non-null   object\n",
      "dtypes: object(3)\n",
      "memory usage: 119.4+ KB\n"
     ]
    }
   ],
   "source": [
    "name_list = []\n",
    "lat_list = []\n",
    "lon_list = []\n",
    "\n",
    "for article in parsed_xml_lgl.getroot():\n",
    "    toponyms = article.find('toponyms')\n",
    "    for toponym in toponyms:\n",
    "        gaztag = toponym.find('gaztag')\n",
    "        try:\n",
    "            name = gaztag.find('name')\n",
    "            lat = gaztag.find('lat')\n",
    "            lon = gaztag.find('lon')\n",
    "            name_list.append(name.text)\n",
    "            lat_list.append(lat.text)\n",
    "            lon_list.append(lon.text)\n",
    "        except AttributeError:\n",
    "            name = toponym.find('phrase')\n",
    "            name_list.append(name.text)\n",
    "            lat_list.append(None)\n",
    "            lon_list.append(None)\n",
    "\n",
    "df_lgl_poi = pd.DataFrame({'name' :name_list, 'lat': lat_list, 'lon': lon_list})\n",
    "df_lgl_poi.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "id": "160b1794",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>name</th>\n",
       "      <th>lat</th>\n",
       "      <th>lon</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Alexandria</td>\n",
       "      <td>31.3113</td>\n",
       "      <td>-92.4451</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Alexandria</td>\n",
       "      <td>31.3113</td>\n",
       "      <td>-92.4451</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Rapides Parish</td>\n",
       "      <td>31.1669</td>\n",
       "      <td>-92.4835</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Avoyelles Parish</td>\n",
       "      <td>31.0669</td>\n",
       "      <td>-91.9668</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Cottonport</td>\n",
       "      <td>30.9841</td>\n",
       "      <td>-92.0535</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "               name      lat       lon\n",
       "0        Alexandria  31.3113  -92.4451\n",
       "1        Alexandria  31.3113  -92.4451\n",
       "2    Rapides Parish  31.1669  -92.4835\n",
       "3  Avoyelles Parish  31.0669  -91.9668\n",
       "4        Cottonport  30.9841  -92.0535"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_lgl_poi.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "id": "710e5577",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "Int64Index: 1571 entries, 0 to 5081\n",
      "Data columns (total 3 columns):\n",
      " #   Column  Non-Null Count  Dtype \n",
      "---  ------  --------------  ----- \n",
      " 0   name    1571 non-null   object\n",
      " 1   lat     1086 non-null   object\n",
      " 2   lon     1086 non-null   object\n",
      "dtypes: object(3)\n",
      "memory usage: 49.1+ KB\n"
     ]
    }
   ],
   "source": [
    "df_lgl_poi = df_lgl_poi.drop_duplicates()\n",
    "df_lgl_poi.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "id": "9f464d99",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>name</th>\n",
       "      <th>lat</th>\n",
       "      <th>lon</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Alexandria</td>\n",
       "      <td>31.3113</td>\n",
       "      <td>-92.4451</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Rapides Parish</td>\n",
       "      <td>31.1669</td>\n",
       "      <td>-92.4835</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Avoyelles Parish</td>\n",
       "      <td>31.0669</td>\n",
       "      <td>-91.9668</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Cottonport</td>\n",
       "      <td>30.9841</td>\n",
       "      <td>-92.0535</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>Memphis St.</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "               name      lat       lon\n",
       "0        Alexandria  31.3113  -92.4451\n",
       "2    Rapides Parish  31.1669  -92.4835\n",
       "3  Avoyelles Parish  31.0669  -91.9668\n",
       "4        Cottonport  30.9841  -92.0535\n",
       "8       Memphis St.     None      None"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_lgl_poi.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "id": "5ff1c054",
   "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>toponym</th>\n",
       "      <th>old_coord</th>\n",
       "      <th>new_coord</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Africa</td>\n",
       "      <td>7.1881 , 21.0938</td>\n",
       "      <td>1.0 , 17.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Akron</td>\n",
       "      <td>41.0814 , -81.519</td>\n",
       "      <td>41.073055555556 , -81.517777777778</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Akuressa</td>\n",
       "      <td>6.09639 , 80.4808</td>\n",
       "      <td>6.09638889 , 80.48083333</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Alabama</td>\n",
       "      <td>32.7504 , -86.7503</td>\n",
       "      <td>32.7 , -86.7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Alachua County</td>\n",
       "      <td>29.7086 , -82.3332</td>\n",
       "      <td>29.683333333333 , -82.366666666667</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "           toponym             old_coord                            new_coord\n",
       "0          Africa      7.1881 , 21.0938                            1.0 , 17.0\n",
       "1           Akron     41.0814 , -81.519    41.073055555556 , -81.517777777778\n",
       "2        Akuressa     6.09639 , 80.4808              6.09638889 , 80.48083333\n",
       "3         Alabama    32.7504 , -86.7503                          32.7 , -86.7\n",
       "4  Alachua County    29.7086 , -82.3332    29.683333333333 , -82.366666666667"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "mlg_lgl = pd.read_csv(os.path.dirname(os.getcwd())+'\\\\data\\\\evaluation-corpora\\\\data-patches\\\\lgl_patches.tsv', sep = '\\t', header = None)\n",
    "mlg_lgl = mlg_lgl.rename(columns = {0:'toponym', 1:'old_coord', 2:'new_coord'})\n",
    "mlg_lgl.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "id": "c40b8a00",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 901 entries, 0 to 900\n",
      "Data columns (total 3 columns):\n",
      " #   Column     Non-Null Count  Dtype \n",
      "---  ------     --------------  ----- \n",
      " 0   toponym    901 non-null    object\n",
      " 1   old_coord  901 non-null    object\n",
      " 2   new_coord  901 non-null    object\n",
      "dtypes: object(3)\n",
      "memory usage: 21.2+ KB\n"
     ]
    }
   ],
   "source": [
    "mlg_lgl.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "id": "c21d4809",
   "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>toponym</th>\n",
       "      <th>old_coord</th>\n",
       "      <th>new_coord</th>\n",
       "      <th>mlg_key</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Africa</td>\n",
       "      <td>7.1881 , 21.0938</td>\n",
       "      <td>1.0 , 17.0</td>\n",
       "      <td>Africa 7.1881,21.0938</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Akron</td>\n",
       "      <td>41.0814 , -81.519</td>\n",
       "      <td>41.073055555556 , -81.517777777778</td>\n",
       "      <td>Akron 41.0814,-81.519</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Akuressa</td>\n",
       "      <td>6.09639 , 80.4808</td>\n",
       "      <td>6.09638889 , 80.48083333</td>\n",
       "      <td>Akuressa 6.09639,80.4808</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Alabama</td>\n",
       "      <td>32.7504 , -86.7503</td>\n",
       "      <td>32.7 , -86.7</td>\n",
       "      <td>Alabama 32.7504,-86.7503</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Alachua County</td>\n",
       "      <td>29.7086 , -82.3332</td>\n",
       "      <td>29.683333333333 , -82.366666666667</td>\n",
       "      <td>Alachua County 29.7086,-82.3332</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "           toponym             old_coord                            new_coord  \\\n",
       "0          Africa      7.1881 , 21.0938                            1.0 , 17.0   \n",
       "1           Akron     41.0814 , -81.519    41.073055555556 , -81.517777777778   \n",
       "2        Akuressa     6.09639 , 80.4808              6.09638889 , 80.48083333   \n",
       "3         Alabama    32.7504 , -86.7503                          32.7 , -86.7   \n",
       "4  Alachua County    29.7086 , -82.3332    29.683333333333 , -82.366666666667   \n",
       "\n",
       "                           mlg_key  \n",
       "0            Africa 7.1881,21.0938  \n",
       "1            Akron 41.0814,-81.519  \n",
       "2         Akuressa 6.09639,80.4808  \n",
       "3         Alabama 32.7504,-86.7503  \n",
       "4  Alachua County 29.7086,-82.3332  "
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "mlg_lgl['mlg_key'] = mlg_lgl.apply(lambda x: calc_mlg_key(x['toponym'],x['old_coord']),axis = 1)\n",
    "mlg_lgl.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "id": "089e2b12",
   "metadata": {},
   "outputs": [],
   "source": [
    "def calc_lgl_key(name, lat, lon):\n",
    "    name = name.strip(' ')\n",
    "    if (lat != None) & (lon != None):\n",
    "        lat = lat.strip(' ')\n",
    "        lon = lon.strip(' ')\n",
    "        return name+' '+lat+','+lon"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "id": "3d548549",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>name</th>\n",
       "      <th>lat</th>\n",
       "      <th>lon</th>\n",
       "      <th>lgl_key</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Alexandria</td>\n",
       "      <td>31.3113</td>\n",
       "      <td>-92.4451</td>\n",
       "      <td>Alexandria 31.3113,-92.4451</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Rapides Parish</td>\n",
       "      <td>31.1669</td>\n",
       "      <td>-92.4835</td>\n",
       "      <td>Rapides Parish 31.1669,-92.4835</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Avoyelles Parish</td>\n",
       "      <td>31.0669</td>\n",
       "      <td>-91.9668</td>\n",
       "      <td>Avoyelles Parish 31.0669,-91.9668</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Cottonport</td>\n",
       "      <td>30.9841</td>\n",
       "      <td>-92.0535</td>\n",
       "      <td>Cottonport 30.9841,-92.0535</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>Memphis St.</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "               name      lat       lon                            lgl_key\n",
       "0        Alexandria  31.3113  -92.4451        Alexandria 31.3113,-92.4451\n",
       "2    Rapides Parish  31.1669  -92.4835    Rapides Parish 31.1669,-92.4835\n",
       "3  Avoyelles Parish  31.0669  -91.9668  Avoyelles Parish 31.0669,-91.9668\n",
       "4        Cottonport  30.9841  -92.0535        Cottonport 30.9841,-92.0535\n",
       "8       Memphis St.     None      None                               None"
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_lgl_poi['lgl_key'] = df_lgl_poi.apply(lambda x: calc_lgl_key(x['name'], x['lat'], x['lon']),axis = 1)\n",
    "df_lgl_poi.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "id": "ed0ed33d",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>name</th>\n",
       "      <th>lat</th>\n",
       "      <th>lon</th>\n",
       "      <th>lgl_key</th>\n",
       "      <th>toponym</th>\n",
       "      <th>old_coord</th>\n",
       "      <th>new_coord</th>\n",
       "      <th>mlg_key</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Alexandria</td>\n",
       "      <td>31.3113</td>\n",
       "      <td>-92.4451</td>\n",
       "      <td>Alexandria 31.3113,-92.4451</td>\n",
       "      <td>Alexandria</td>\n",
       "      <td>31.3113 , -92.4451</td>\n",
       "      <td>31.2928 , -92.4592</td>\n",
       "      <td>Alexandria 31.3113,-92.4451</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Rapides Parish</td>\n",
       "      <td>31.1669</td>\n",
       "      <td>-92.4835</td>\n",
       "      <td>Rapides Parish 31.1669,-92.4835</td>\n",
       "      <td>Rapides Parish</td>\n",
       "      <td>31.1669 , -92.4835</td>\n",
       "      <td>31.2 , -92.54</td>\n",
       "      <td>Rapides Parish 31.1669,-92.4835</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Avoyelles Parish</td>\n",
       "      <td>31.0669</td>\n",
       "      <td>-91.9668</td>\n",
       "      <td>Avoyelles Parish 31.0669,-91.9668</td>\n",
       "      <td>Avoyelles Parish</td>\n",
       "      <td>31.0669 , -91.9668</td>\n",
       "      <td>31.07 , -92.0</td>\n",
       "      <td>Avoyelles Parish 31.0669,-91.9668</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Cottonport</td>\n",
       "      <td>30.9841</td>\n",
       "      <td>-92.0535</td>\n",
       "      <td>Cottonport 30.9841,-92.0535</td>\n",
       "      <td>Cottonport</td>\n",
       "      <td>30.9841 , -92.0535</td>\n",
       "      <td>30.98408 , -92.05346</td>\n",
       "      <td>Cottonport 30.9841,-92.0535</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Memphis St.</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "               name      lat       lon                            lgl_key  \\\n",
       "0        Alexandria  31.3113  -92.4451        Alexandria 31.3113,-92.4451   \n",
       "1    Rapides Parish  31.1669  -92.4835    Rapides Parish 31.1669,-92.4835   \n",
       "2  Avoyelles Parish  31.0669  -91.9668  Avoyelles Parish 31.0669,-91.9668   \n",
       "3        Cottonport  30.9841  -92.0535        Cottonport 30.9841,-92.0535   \n",
       "4       Memphis St.     None      None                               None   \n",
       "\n",
       "             toponym             old_coord              new_coord  \\\n",
       "0        Alexandria    31.3113 , -92.4451      31.2928 , -92.4592   \n",
       "1    Rapides Parish    31.1669 , -92.4835           31.2 , -92.54   \n",
       "2  Avoyelles Parish    31.0669 , -91.9668           31.07 , -92.0   \n",
       "3        Cottonport    30.9841 , -92.0535    30.98408 , -92.05346   \n",
       "4                NaN                   NaN                    NaN   \n",
       "\n",
       "                             mlg_key  \n",
       "0        Alexandria 31.3113,-92.4451  \n",
       "1    Rapides Parish 31.1669,-92.4835  \n",
       "2  Avoyelles Parish 31.0669,-91.9668  \n",
       "3        Cottonport 30.9841,-92.0535  \n",
       "4                                NaN  "
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_lgl_poi_unified = pd.merge(df_lgl_poi, mlg_lgl, how = 'outer', left_on = 'lgl_key', right_on = 'mlg_key')\n",
    "df_lgl_poi_unified.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "id": "10c81f30",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "Int64Index: 1571 entries, 0 to 1570\n",
      "Data columns (total 8 columns):\n",
      " #   Column     Non-Null Count  Dtype \n",
      "---  ------     --------------  ----- \n",
      " 0   name       1571 non-null   object\n",
      " 1   lat        1086 non-null   object\n",
      " 2   lon        1086 non-null   object\n",
      " 3   lgl_key    1086 non-null   object\n",
      " 4   toponym    901 non-null    object\n",
      " 5   old_coord  901 non-null    object\n",
      " 6   new_coord  901 non-null    object\n",
      " 7   mlg_key    901 non-null    object\n",
      "dtypes: object(8)\n",
      "memory usage: 110.5+ KB\n"
     ]
    }
   ],
   "source": [
    "df_lgl_poi_unified.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "id": "4061555a",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "Int64Index: 901 entries, 0 to 1569\n",
      "Data columns (total 8 columns):\n",
      " #   Column     Non-Null Count  Dtype \n",
      "---  ------     --------------  ----- \n",
      " 0   name       901 non-null    object\n",
      " 1   lat        901 non-null    object\n",
      " 2   lon        901 non-null    object\n",
      " 3   lgl_key    901 non-null    object\n",
      " 4   toponym    901 non-null    object\n",
      " 5   old_coord  901 non-null    object\n",
      " 6   new_coord  901 non-null    object\n",
      " 7   mlg_key    901 non-null    object\n",
      "dtypes: object(8)\n",
      "memory usage: 63.4+ KB\n"
     ]
    }
   ],
   "source": [
    "df_lgl_poi_unified_filtered = df_lgl_poi_unified.dropna(subset=['new_coord'])\n",
    "df_lgl_poi_unified_filtered.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "id": "9e358a0c",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_lgl_poi_unified_filtered.to_csv(os.path.dirname(os.getcwd())+'\\\\data\\\\extracted-annotated-locations\\\\LGL_poi.csv')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "18897ca1",
   "metadata": {},
   "source": [
    "## GeoVirus POI Extraction"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "id": "f96e2496",
   "metadata": {},
   "outputs": [],
   "source": [
    "xml_geovirus = os.path.dirname(os.getcwd())+'\\\\data\\\\evaluation-corpora\\\\original-datasets\\\\GeoVirus.xml'\n",
    "parsed_xml_geovirus = et.parse(xml_geovirus)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "id": "14605fff",
   "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>source</th>\n",
       "      <th>text</th>\n",
       "      <th>locations</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>https://en.wikinews.org/wiki/Philippines_is_cu...</td>\n",
       "      <td>The Philippine Department of Agriculture (DA),...</td>\n",
       "      <td>[{'name': 'Pandi', 'wikipedia_name': 'Pandi, B...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>https://en.wikinews.org/wiki/Philippines_is_cu...</td>\n",
       "      <td>The agriculture officials earlier announced th...</td>\n",
       "      <td>[{'name': 'Bulacan', 'wikipedia_name': 'Bulaca...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>https://en.wikinews.org/wiki/Philippines_is_cu...</td>\n",
       "      <td>Local police has also arrested four vendors an...</td>\n",
       "      <td>[{'name': 'Balintawak', 'wikipedia_name': 'Bal...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>https://en.wikinews.org/wiki/US_soldier_who_di...</td>\n",
       "      <td>Tests yesterday proved negative for Ebola on t...</td>\n",
       "      <td>[{'name': 'Texas', 'wikipedia_name': 'Texas', ...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>https://en.wikinews.org/wiki/Patient_under_eva...</td>\n",
       "      <td>A patient was admitted to a hospital in Washin...</td>\n",
       "      <td>[{'name': 'Washington', 'wikipedia_name': 'Was...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                              source  \\\n",
       "0  https://en.wikinews.org/wiki/Philippines_is_cu...   \n",
       "1  https://en.wikinews.org/wiki/Philippines_is_cu...   \n",
       "2  https://en.wikinews.org/wiki/Philippines_is_cu...   \n",
       "3  https://en.wikinews.org/wiki/US_soldier_who_di...   \n",
       "4  https://en.wikinews.org/wiki/Patient_under_eva...   \n",
       "\n",
       "                                                text  \\\n",
       "0  The Philippine Department of Agriculture (DA),...   \n",
       "1  The agriculture officials earlier announced th...   \n",
       "2  Local police has also arrested four vendors an...   \n",
       "3  Tests yesterday proved negative for Ebola on t...   \n",
       "4  A patient was admitted to a hospital in Washin...   \n",
       "\n",
       "                                           locations  \n",
       "0  [{'name': 'Pandi', 'wikipedia_name': 'Pandi, B...  \n",
       "1  [{'name': 'Bulacan', 'wikipedia_name': 'Bulaca...  \n",
       "2  [{'name': 'Balintawak', 'wikipedia_name': 'Bal...  \n",
       "3  [{'name': 'Texas', 'wikipedia_name': 'Texas', ...  \n",
       "4  [{'name': 'Washington', 'wikipedia_name': 'Was...  "
      ]
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "source_list = []\n",
    "text_list = []\n",
    "locations_list = []\n",
    "\n",
    "for article in parsed_xml_geovirus.getroot():\n",
    "    source = article.find('source')\n",
    "    text = article.find('text')\n",
    "    locations = article.find('locations')\n",
    "    location_list = []\n",
    "    for location in locations:\n",
    "        name = location.find('name')\n",
    "        start = location.find('start')\n",
    "        end = location.find('end')\n",
    "        lat = location.find('lat')\n",
    "        lon = location.find('lon')\n",
    "        page = location.find('page')\n",
    "        \n",
    "        page_split = page.text.split('/')\n",
    "        wikipedia_name = page_split[len(page_split)-1]\n",
    "        wikipedia_name = wikipedia_name.replace(\"_\",\" \")\n",
    "        wikipedia_name = wikipedia_name.replace(\"%27\",\"\\'\")\n",
    "        \n",
    "        location_list.append({'name':name.text, 'wikipedia_name':wikipedia_name, 'start':start.text, 'end': end.text, 'lat': lat.text, 'lon': lon.text, 'page': page.text})    \n",
    "    \n",
    "    source_list.append(source.text)\n",
    "    text_list.append(text.text)\n",
    "    locations_list.append(location_list)\n",
    "    \n",
    "\n",
    "df_geovirus = pd.DataFrame({'source' :source_list, 'text': text_list, 'locations': locations_list})\n",
    "df_geovirus.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "id": "68f6538a",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 2167 entries, 0 to 2166\n",
      "Data columns (total 5 columns):\n",
      " #   Column          Non-Null Count  Dtype \n",
      "---  ------          --------------  ----- \n",
      " 0   name            2167 non-null   object\n",
      " 1   wikipedia_name  2167 non-null   object\n",
      " 2   lat             2167 non-null   object\n",
      " 3   lon             2167 non-null   object\n",
      " 4   page            2167 non-null   object\n",
      "dtypes: object(5)\n",
      "memory usage: 84.8+ KB\n"
     ]
    }
   ],
   "source": [
    "name_list = []\n",
    "lat_list = []\n",
    "lon_list = []\n",
    "page_list = []\n",
    "wikipedia_name_list = []\n",
    "\n",
    "for article in parsed_xml_geovirus.getroot():\n",
    "    locations = article.find('locations')\n",
    "    for location in locations:\n",
    "        name = location.find('name')\n",
    "        lat = location.find('lat')\n",
    "        lon = location.find('lon')\n",
    "        page = location.find('page')\n",
    "        \n",
    "        page_split = page.text.split('/')\n",
    "        wikipedia_name = page_split[len(page_split)-1]\n",
    "        wikipedia_name = wikipedia_name.replace(\"_\",\" \")\n",
    "        wikipedia_name = wikipedia_name.replace(\"%27\",\"\\'\")\n",
    "        \n",
    "        name_list.append(name.text)\n",
    "        lat_list.append(lat.text)\n",
    "        lon_list.append(lon.text)\n",
    "        page_list.append(page.text)\n",
    "        wikipedia_name_list.append(wikipedia_name)\n",
    "\n",
    "df_geovirus_poi = pd.DataFrame({'name' :name_list, 'wikipedia_name':wikipedia_name_list, 'lat': lat_list, 'lon': lon_list, 'page': page_list})\n",
    "df_geovirus_poi.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "id": "ebbd1435",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>name</th>\n",
       "      <th>wikipedia_name</th>\n",
       "      <th>lat</th>\n",
       "      <th>lon</th>\n",
       "      <th>page</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Pandi</td>\n",
       "      <td>Pandi, Bulacan</td>\n",
       "      <td>14.87</td>\n",
       "      <td>120.95</td>\n",
       "      <td>https://en.wikipedia.org/wiki/Pandi,_Bulacan</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Central Luzon</td>\n",
       "      <td>Central Luzon</td>\n",
       "      <td>15.47</td>\n",
       "      <td>120.75</td>\n",
       "      <td>https://en.wikipedia.org/wiki/Central_Luzon</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Bulacan</td>\n",
       "      <td>Bulacan</td>\n",
       "      <td>15</td>\n",
       "      <td>121.08</td>\n",
       "      <td>https://en.wikipedia.org/wiki/Bulacan</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Bulacan</td>\n",
       "      <td>Bulacan</td>\n",
       "      <td>15</td>\n",
       "      <td>121.08</td>\n",
       "      <td>https://en.wikipedia.org/wiki/Bulacan</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Manaoag</td>\n",
       "      <td>Manaoag, Pangasinan</td>\n",
       "      <td>16.04</td>\n",
       "      <td>120.48</td>\n",
       "      <td>https://en.wikipedia.org/wiki/Manaoag,_Pangasinan</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "            name       wikipedia_name    lat     lon  \\\n",
       "0          Pandi       Pandi, Bulacan  14.87  120.95   \n",
       "1  Central Luzon        Central Luzon  15.47  120.75   \n",
       "2        Bulacan              Bulacan     15  121.08   \n",
       "3        Bulacan              Bulacan     15  121.08   \n",
       "4        Manaoag  Manaoag, Pangasinan  16.04  120.48   \n",
       "\n",
       "                                                page  \n",
       "0       https://en.wikipedia.org/wiki/Pandi,_Bulacan  \n",
       "1        https://en.wikipedia.org/wiki/Central_Luzon  \n",
       "2              https://en.wikipedia.org/wiki/Bulacan  \n",
       "3              https://en.wikipedia.org/wiki/Bulacan  \n",
       "4  https://en.wikipedia.org/wiki/Manaoag,_Pangasinan  "
      ]
     },
     "execution_count": 27,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_geovirus_poi.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "id": "8b71fc74",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "Int64Index: 700 entries, 0 to 2154\n",
      "Data columns (total 5 columns):\n",
      " #   Column          Non-Null Count  Dtype \n",
      "---  ------          --------------  ----- \n",
      " 0   name            700 non-null    object\n",
      " 1   wikipedia_name  700 non-null    object\n",
      " 2   lat             700 non-null    object\n",
      " 3   lon             700 non-null    object\n",
      " 4   page            700 non-null    object\n",
      "dtypes: object(5)\n",
      "memory usage: 32.8+ KB\n"
     ]
    }
   ],
   "source": [
    "df_geovirus_poi = df_geovirus_poi.drop_duplicates()\n",
    "df_geovirus_poi.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "id": "289065fb",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>name</th>\n",
       "      <th>wikipedia_name</th>\n",
       "      <th>lat</th>\n",
       "      <th>lon</th>\n",
       "      <th>page</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Pandi</td>\n",
       "      <td>Pandi, Bulacan</td>\n",
       "      <td>14.87</td>\n",
       "      <td>120.95</td>\n",
       "      <td>https://en.wikipedia.org/wiki/Pandi,_Bulacan</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Central Luzon</td>\n",
       "      <td>Central Luzon</td>\n",
       "      <td>15.47</td>\n",
       "      <td>120.75</td>\n",
       "      <td>https://en.wikipedia.org/wiki/Central_Luzon</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Bulacan</td>\n",
       "      <td>Bulacan</td>\n",
       "      <td>15</td>\n",
       "      <td>121.08</td>\n",
       "      <td>https://en.wikipedia.org/wiki/Bulacan</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Manaoag</td>\n",
       "      <td>Manaoag, Pangasinan</td>\n",
       "      <td>16.04</td>\n",
       "      <td>120.48</td>\n",
       "      <td>https://en.wikipedia.org/wiki/Manaoag,_Pangasinan</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>Pangasinan</td>\n",
       "      <td>Pangasinan</td>\n",
       "      <td>15.92</td>\n",
       "      <td>120.33</td>\n",
       "      <td>https://en.wikipedia.org/wiki/Pangasinan</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "            name       wikipedia_name    lat     lon  \\\n",
       "0          Pandi       Pandi, Bulacan  14.87  120.95   \n",
       "1  Central Luzon        Central Luzon  15.47  120.75   \n",
       "2        Bulacan              Bulacan     15  121.08   \n",
       "4        Manaoag  Manaoag, Pangasinan  16.04  120.48   \n",
       "5     Pangasinan           Pangasinan  15.92  120.33   \n",
       "\n",
       "                                                page  \n",
       "0       https://en.wikipedia.org/wiki/Pandi,_Bulacan  \n",
       "1        https://en.wikipedia.org/wiki/Central_Luzon  \n",
       "2              https://en.wikipedia.org/wiki/Bulacan  \n",
       "4  https://en.wikipedia.org/wiki/Manaoag,_Pangasinan  \n",
       "5           https://en.wikipedia.org/wiki/Pangasinan  "
      ]
     },
     "execution_count": 29,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_geovirus_poi.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "id": "36147b52",
   "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>toponym</th>\n",
       "      <th>old_coord</th>\n",
       "      <th>new_coord</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Abilene, Texas</td>\n",
       "      <td>32.45 , -99.75</td>\n",
       "      <td>32.446388888889 , -99.745555555556</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Acapulco</td>\n",
       "      <td>16.86 , -99.88</td>\n",
       "      <td>16.8628695 , -99.8870087</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Aceh</td>\n",
       "      <td>5.55 , 95.31</td>\n",
       "      <td>4.3 , 96.9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Adhamiyah</td>\n",
       "      <td>33.36 , 44.36</td>\n",
       "      <td>33.36841667 , 44.36213889</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Afghanistan</td>\n",
       "      <td>33 , 65</td>\n",
       "      <td>34.0 , 66.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "           toponym         old_coord                            new_coord\n",
       "0  Abilene, Texas    32.45 , -99.75    32.446388888889 , -99.745555555556\n",
       "1        Acapulco    16.86 , -99.88              16.8628695 , -99.8870087\n",
       "2            Aceh      5.55 , 95.31                            4.3 , 96.9\n",
       "3       Adhamiyah     33.36 , 44.36             33.36841667 , 44.36213889\n",
       "4     Afghanistan           33 , 65                           34.0 , 66.0"
      ]
     },
     "execution_count": 30,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "mlg_geovirus = pd.read_csv(os.path.dirname(os.getcwd())+'\\\\data\\\\evaluation-corpora\\\\data-patches\\\\GeoVirus_patches.tsv', sep = '\\t', header = None)\n",
    "mlg_geovirus = mlg_geovirus.rename(columns = {0:'toponym',1: 'old_coord',2: 'new_coord'})\n",
    "mlg_geovirus.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "id": "d464dc6f",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 623 entries, 0 to 622\n",
      "Data columns (total 3 columns):\n",
      " #   Column     Non-Null Count  Dtype \n",
      "---  ------     --------------  ----- \n",
      " 0   toponym    623 non-null    object\n",
      " 1   old_coord  623 non-null    object\n",
      " 2   new_coord  623 non-null    object\n",
      "dtypes: object(3)\n",
      "memory usage: 14.7+ KB\n"
     ]
    }
   ],
   "source": [
    "mlg_geovirus.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "id": "de990798",
   "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>toponym</th>\n",
       "      <th>old_coord</th>\n",
       "      <th>new_coord</th>\n",
       "      <th>mlg_key</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Abilene, Texas</td>\n",
       "      <td>32.45 , -99.75</td>\n",
       "      <td>32.446388888889 , -99.745555555556</td>\n",
       "      <td>Abilene, Texas 32.45,-99.75</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Acapulco</td>\n",
       "      <td>16.86 , -99.88</td>\n",
       "      <td>16.8628695 , -99.8870087</td>\n",
       "      <td>Acapulco 16.86,-99.88</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Aceh</td>\n",
       "      <td>5.55 , 95.31</td>\n",
       "      <td>4.3 , 96.9</td>\n",
       "      <td>Aceh 5.55,95.31</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Adhamiyah</td>\n",
       "      <td>33.36 , 44.36</td>\n",
       "      <td>33.36841667 , 44.36213889</td>\n",
       "      <td>Adhamiyah 33.36,44.36</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Afghanistan</td>\n",
       "      <td>33 , 65</td>\n",
       "      <td>34.0 , 66.0</td>\n",
       "      <td>Afghanistan 33,65</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "           toponym         old_coord                            new_coord  \\\n",
       "0  Abilene, Texas    32.45 , -99.75    32.446388888889 , -99.745555555556   \n",
       "1        Acapulco    16.86 , -99.88              16.8628695 , -99.8870087   \n",
       "2            Aceh      5.55 , 95.31                            4.3 , 96.9   \n",
       "3       Adhamiyah     33.36 , 44.36             33.36841667 , 44.36213889   \n",
       "4     Afghanistan           33 , 65                           34.0 , 66.0   \n",
       "\n",
       "                       mlg_key  \n",
       "0  Abilene, Texas 32.45,-99.75  \n",
       "1        Acapulco 16.86,-99.88  \n",
       "2              Aceh 5.55,95.31  \n",
       "3        Adhamiyah 33.36,44.36  \n",
       "4            Afghanistan 33,65  "
      ]
     },
     "execution_count": 32,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "mlg_geovirus['mlg_key'] = mlg_geovirus.apply(lambda x: calc_mlg_key(x['toponym'],x['old_coord']),axis = 1)\n",
    "mlg_geovirus.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "id": "58acd709",
   "metadata": {},
   "outputs": [],
   "source": [
    "def calc_geovirus_key(wikipedia_name, lat, lon):\n",
    "    wikipedia_name = wikipedia_name.strip(' ')\n",
    "    if (lat != None) & (lon != None):\n",
    "        lat = lat.strip(' ')\n",
    "        lon = lon.strip(' ')\n",
    "        return wikipedia_name+' '+lat+','+lon"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "id": "dca20e92",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>name</th>\n",
       "      <th>wikipedia_name</th>\n",
       "      <th>lat</th>\n",
       "      <th>lon</th>\n",
       "      <th>page</th>\n",
       "      <th>geovirus_key</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Pandi</td>\n",
       "      <td>Pandi, Bulacan</td>\n",
       "      <td>14.87</td>\n",
       "      <td>120.95</td>\n",
       "      <td>https://en.wikipedia.org/wiki/Pandi,_Bulacan</td>\n",
       "      <td>Pandi, Bulacan 14.87,120.95</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Central Luzon</td>\n",
       "      <td>Central Luzon</td>\n",
       "      <td>15.47</td>\n",
       "      <td>120.75</td>\n",
       "      <td>https://en.wikipedia.org/wiki/Central_Luzon</td>\n",
       "      <td>Central Luzon 15.47,120.75</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Bulacan</td>\n",
       "      <td>Bulacan</td>\n",
       "      <td>15</td>\n",
       "      <td>121.08</td>\n",
       "      <td>https://en.wikipedia.org/wiki/Bulacan</td>\n",
       "      <td>Bulacan 15,121.08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Manaoag</td>\n",
       "      <td>Manaoag, Pangasinan</td>\n",
       "      <td>16.04</td>\n",
       "      <td>120.48</td>\n",
       "      <td>https://en.wikipedia.org/wiki/Manaoag,_Pangasinan</td>\n",
       "      <td>Manaoag, Pangasinan 16.04,120.48</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>Pangasinan</td>\n",
       "      <td>Pangasinan</td>\n",
       "      <td>15.92</td>\n",
       "      <td>120.33</td>\n",
       "      <td>https://en.wikipedia.org/wiki/Pangasinan</td>\n",
       "      <td>Pangasinan 15.92,120.33</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "            name       wikipedia_name    lat     lon  \\\n",
       "0          Pandi       Pandi, Bulacan  14.87  120.95   \n",
       "1  Central Luzon        Central Luzon  15.47  120.75   \n",
       "2        Bulacan              Bulacan     15  121.08   \n",
       "4        Manaoag  Manaoag, Pangasinan  16.04  120.48   \n",
       "5     Pangasinan           Pangasinan  15.92  120.33   \n",
       "\n",
       "                                                page  \\\n",
       "0       https://en.wikipedia.org/wiki/Pandi,_Bulacan   \n",
       "1        https://en.wikipedia.org/wiki/Central_Luzon   \n",
       "2              https://en.wikipedia.org/wiki/Bulacan   \n",
       "4  https://en.wikipedia.org/wiki/Manaoag,_Pangasinan   \n",
       "5           https://en.wikipedia.org/wiki/Pangasinan   \n",
       "\n",
       "                       geovirus_key  \n",
       "0       Pandi, Bulacan 14.87,120.95  \n",
       "1        Central Luzon 15.47,120.75  \n",
       "2                 Bulacan 15,121.08  \n",
       "4  Manaoag, Pangasinan 16.04,120.48  \n",
       "5           Pangasinan 15.92,120.33  "
      ]
     },
     "execution_count": 34,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_geovirus_poi['geovirus_key'] = df_geovirus_poi.apply(lambda x: calc_geovirus_key(x['wikipedia_name'], x['lat'], x['lon']),axis = 1)\n",
    "df_geovirus_poi.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "id": "6c46ab91",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "Int64Index: 700 entries, 0 to 699\n",
      "Data columns (total 10 columns):\n",
      " #   Column          Non-Null Count  Dtype \n",
      "---  ------          --------------  ----- \n",
      " 0   name            700 non-null    object\n",
      " 1   wikipedia_name  700 non-null    object\n",
      " 2   lat             700 non-null    object\n",
      " 3   lon             700 non-null    object\n",
      " 4   page            700 non-null    object\n",
      " 5   geovirus_key    700 non-null    object\n",
      " 6   toponym         664 non-null    object\n",
      " 7   old_coord       664 non-null    object\n",
      " 8   new_coord       664 non-null    object\n",
      " 9   mlg_key         664 non-null    object\n",
      "dtypes: object(10)\n",
      "memory usage: 60.2+ KB\n"
     ]
    }
   ],
   "source": [
    "df_geovirus_poi_unified = pd.merge(df_geovirus_poi, mlg_geovirus, how = 'outer', left_on = 'geovirus_key', right_on = 'mlg_key')\n",
    "df_geovirus_poi_unified.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "id": "f6817b54",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_geovirus_poi_unified.to_csv(os.path.dirname(os.getcwd())+'\\\\data\\\\extracted-annotated-locations\\\\GeoVirus_poi.csv')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "e80417c9",
   "metadata": {},
   "source": [
    "## WikToR POI Extraction"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "id": "f0236861",
   "metadata": {},
   "outputs": [],
   "source": [
    "xml_wiktor = os.path.dirname(os.getcwd())+'\\\\data\\\\evaluation-corpora\\\\original-datasets\\\\WikToR.xml'\n",
    "parsed_xml_wiktor = et.parse(xml_wiktor)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "id": "d86617aa",
   "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>url</th>\n",
       "      <th>text</th>\n",
       "      <th>toponyms</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>http://en.wikipedia.org/wiki/Santa_Maria%2C_Ca...</td>\n",
       "      <td>Santa Maria is a city near the Southern Califo...</td>\n",
       "      <td>[{'name': 'Santa Maria', 'wikipedia_name': 'Sa...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>http://en.wikipedia.org/wiki/Santa_Maria%2C_Bu...</td>\n",
       "      <td>Santa María (/ˈsæntə məˈriə/; Tagalog pronunci...</td>\n",
       "      <td>[{'name': 'Santa Maria', 'wikipedia_name': 'Sa...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>http://en.wikipedia.org/wiki/Santa_Maria%2C_Il...</td>\n",
       "      <td>Santa Maria (Nalibnos a Santa Maria) is a thir...</td>\n",
       "      <td>[{'name': 'Santa Maria', 'wikipedia_name': 'Sa...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>http://en.wikipedia.org/wiki/Santa_Maria%2C_Ro...</td>\n",
       "      <td>Santa Maria (formerly Imelda) is a fifth class...</td>\n",
       "      <td>[{'name': 'Santa Maria', 'wikipedia_name': 'Sa...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>http://en.wikipedia.org/wiki/Santa_Cruz%2C_Chile</td>\n",
       "      <td>Santa Cruz is a Chilean city and commune, loca...</td>\n",
       "      <td>[{'name': 'Santa Cruz', 'wikipedia_name': 'San...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                                 url  \\\n",
       "0  http://en.wikipedia.org/wiki/Santa_Maria%2C_Ca...   \n",
       "1  http://en.wikipedia.org/wiki/Santa_Maria%2C_Bu...   \n",
       "2  http://en.wikipedia.org/wiki/Santa_Maria%2C_Il...   \n",
       "3  http://en.wikipedia.org/wiki/Santa_Maria%2C_Ro...   \n",
       "4   http://en.wikipedia.org/wiki/Santa_Cruz%2C_Chile   \n",
       "\n",
       "                                                text  \\\n",
       "0  Santa Maria is a city near the Southern Califo...   \n",
       "1  Santa María (/ˈsæntə məˈriə/; Tagalog pronunci...   \n",
       "2  Santa Maria (Nalibnos a Santa Maria) is a thir...   \n",
       "3  Santa Maria (formerly Imelda) is a fifth class...   \n",
       "4  Santa Cruz is a Chilean city and commune, loca...   \n",
       "\n",
       "                                            toponyms  \n",
       "0  [{'name': 'Santa Maria', 'wikipedia_name': 'Sa...  \n",
       "1  [{'name': 'Santa Maria', 'wikipedia_name': 'Sa...  \n",
       "2  [{'name': 'Santa Maria', 'wikipedia_name': 'Sa...  \n",
       "3  [{'name': 'Santa Maria', 'wikipedia_name': 'Sa...  \n",
       "4  [{'name': 'Santa Cruz', 'wikipedia_name': 'San...  "
      ]
     },
     "execution_count": 38,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "url_list = []\n",
    "text_list = []\n",
    "toponyms_list = []\n",
    "\n",
    "for page in parsed_xml_wiktor.getroot():\n",
    "    url = page.find('url')\n",
    "    text = page.find('text')\n",
    "    name = page.find('toponymName')\n",
    "    wikipedia_name = page.find('pageTitle')\n",
    "    lat = page.find('lat')\n",
    "    lon = page.find('lon')\n",
    "\n",
    "    toponyms = page.find('toponymIndices')\n",
    "    toponym_list = []\n",
    "    for toponym in toponyms:\n",
    "        start = toponym.find('start')\n",
    "        end = toponym.find('end')        \n",
    "        toponym_list.append({'name':name.text, 'wikipedia_name':wikipedia_name.text, 'start':start.text, 'end': end.text, 'lat': lat.text, 'lon': lon.text, 'page': url.text})    \n",
    "    \n",
    "    url_list.append(url.text)\n",
    "    text_list.append(text.text)\n",
    "    toponyms_list.append(toponym_list)\n",
    "    \n",
    "\n",
    "df_wiktor = pd.DataFrame({'url' :url_list, 'text': text_list, 'toponyms': toponyms_list})\n",
    "df_wiktor.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "id": "29934f2e",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>name</th>\n",
       "      <th>wikipedia_name</th>\n",
       "      <th>lat</th>\n",
       "      <th>lon</th>\n",
       "      <th>page</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Santa Maria</td>\n",
       "      <td>Santa Maria, California</td>\n",
       "      <td>34.9514</td>\n",
       "      <td>-120.4333</td>\n",
       "      <td>http://en.wikipedia.org/wiki/Santa_Maria%2C_Ca...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Santa Maria</td>\n",
       "      <td>Santa Maria, Bulacan</td>\n",
       "      <td>14.8208</td>\n",
       "      <td>120.9636</td>\n",
       "      <td>http://en.wikipedia.org/wiki/Santa_Maria%2C_Bu...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Santa Maria</td>\n",
       "      <td>Santa Maria, Ilocos Sur</td>\n",
       "      <td>17.3585</td>\n",
       "      <td>120.4981</td>\n",
       "      <td>http://en.wikipedia.org/wiki/Santa_Maria%2C_Il...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Santa Maria</td>\n",
       "      <td>Santa Maria, Romblon</td>\n",
       "      <td>12.4417</td>\n",
       "      <td>122.1042</td>\n",
       "      <td>http://en.wikipedia.org/wiki/Santa_Maria%2C_Ro...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Santa Cruz</td>\n",
       "      <td>Santa Cruz, Chile</td>\n",
       "      <td>-34.6372</td>\n",
       "      <td>-71.3631</td>\n",
       "      <td>http://en.wikipedia.org/wiki/Santa_Cruz%2C_Chile</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "          name           wikipedia_name       lat        lon  \\\n",
       "0  Santa Maria  Santa Maria, California   34.9514  -120.4333   \n",
       "1  Santa Maria     Santa Maria, Bulacan   14.8208   120.9636   \n",
       "2  Santa Maria  Santa Maria, Ilocos Sur   17.3585   120.4981   \n",
       "3  Santa Maria     Santa Maria, Romblon   12.4417   122.1042   \n",
       "4   Santa Cruz        Santa Cruz, Chile  -34.6372   -71.3631   \n",
       "\n",
       "                                                page  \n",
       "0  http://en.wikipedia.org/wiki/Santa_Maria%2C_Ca...  \n",
       "1  http://en.wikipedia.org/wiki/Santa_Maria%2C_Bu...  \n",
       "2  http://en.wikipedia.org/wiki/Santa_Maria%2C_Il...  \n",
       "3  http://en.wikipedia.org/wiki/Santa_Maria%2C_Ro...  \n",
       "4   http://en.wikipedia.org/wiki/Santa_Cruz%2C_Chile  "
      ]
     },
     "execution_count": 39,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "name_list = []\n",
    "lat_list = []\n",
    "lon_list = []\n",
    "page_list = []\n",
    "wikipedia_name_list = []\n",
    "\n",
    "for page in parsed_xml_wiktor.getroot():\n",
    "    url = page.find('url')\n",
    "    name = page.find('toponymName')\n",
    "    wikipedia_name = page.find('pageTitle')\n",
    "    lat = page.find('lat')\n",
    "    lon = page.find('lon')\n",
    "\n",
    "    name_list.append(name.text)\n",
    "    lat_list.append(lat.text)\n",
    "    lon_list.append(lon.text)\n",
    "    page_list.append(url.text)\n",
    "    wikipedia_name_list.append(wikipedia_name.text)\n",
    "\n",
    "df_wiktor_poi = pd.DataFrame({'name' :name_list, 'wikipedia_name':wikipedia_name_list, 'lat': lat_list, 'lon': lon_list, 'page': url_list})\n",
    "df_wiktor_poi.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "id": "b400967c",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 5000 entries, 0 to 4999\n",
      "Data columns (total 5 columns):\n",
      " #   Column          Non-Null Count  Dtype \n",
      "---  ------          --------------  ----- \n",
      " 0   name            5000 non-null   object\n",
      " 1   wikipedia_name  5000 non-null   object\n",
      " 2   lat             5000 non-null   object\n",
      " 3   lon             5000 non-null   object\n",
      " 4   page            5000 non-null   object\n",
      "dtypes: object(5)\n",
      "memory usage: 195.4+ KB\n"
     ]
    }
   ],
   "source": [
    "df_wiktor_poi.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "id": "108fa825",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "Int64Index: 5000 entries, 0 to 4999\n",
      "Data columns (total 5 columns):\n",
      " #   Column          Non-Null Count  Dtype \n",
      "---  ------          --------------  ----- \n",
      " 0   name            5000 non-null   object\n",
      " 1   wikipedia_name  5000 non-null   object\n",
      " 2   lat             5000 non-null   object\n",
      " 3   lon             5000 non-null   object\n",
      " 4   page            5000 non-null   object\n",
      "dtypes: object(5)\n",
      "memory usage: 234.4+ KB\n"
     ]
    }
   ],
   "source": [
    "df_wiktor_poi = df_wiktor_poi.drop_duplicates()\n",
    "df_wiktor_poi.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "id": "3756db16",
   "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>toponym</th>\n",
       "      <th>old_coord</th>\n",
       "      <th>new_coord</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Aba, Abia</td>\n",
       "      <td>5.1167 , 7.3667</td>\n",
       "      <td>5.1166666666667 , 7.3666666666667</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Abak</td>\n",
       "      <td>4.9833 , 7.7833</td>\n",
       "      <td>4.9833333333333 , 7.7833333333333</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Abbotsford, British Columbia</td>\n",
       "      <td>49.0489 , -122.2903</td>\n",
       "      <td>49.054611111111 , -122.328</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Abbotsford, New South Wales</td>\n",
       "      <td>-33.8522 , 151.1273</td>\n",
       "      <td>-33.8521 , 151.127</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Abbotsford, Victoria</td>\n",
       "      <td>-37.803 , 145.002</td>\n",
       "      <td>-37.8 , 145.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                         toponym              old_coord  \\\n",
       "0                     Aba, Abia        5.1167 , 7.3667    \n",
       "1                          Abak        4.9833 , 7.7833    \n",
       "2  Abbotsford, British Columbia    49.0489 , -122.2903    \n",
       "3   Abbotsford, New South Wales    -33.8522 , 151.1273    \n",
       "4          Abbotsford, Victoria      -37.803 , 145.002    \n",
       "\n",
       "                            new_coord  \n",
       "0   5.1166666666667 , 7.3666666666667  \n",
       "1   4.9833333333333 , 7.7833333333333  \n",
       "2          49.054611111111 , -122.328  \n",
       "3                  -33.8521 , 151.127  \n",
       "4                       -37.8 , 145.0  "
      ]
     },
     "execution_count": 42,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "mlg_wiktor = pd.read_csv(os.path.dirname(os.getcwd())+'\\\\data\\\\evaluation-corpora\\\\data-patches\\\\WikToR_patches.tsv', sep = '\\t', header = None)\n",
    "mlg_wiktor = mlg_wiktor.rename(columns = {0:'toponym',1: 'old_coord',2: 'new_coord'})\n",
    "mlg_wiktor.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "id": "2345bac3",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 9803 entries, 0 to 9802\n",
      "Data columns (total 3 columns):\n",
      " #   Column     Non-Null Count  Dtype \n",
      "---  ------     --------------  ----- \n",
      " 0   toponym    9803 non-null   object\n",
      " 1   old_coord  9803 non-null   object\n",
      " 2   new_coord  9803 non-null   object\n",
      "dtypes: object(3)\n",
      "memory usage: 229.9+ KB\n"
     ]
    }
   ],
   "source": [
    "mlg_wiktor.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "id": "04761c25",
   "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>toponym</th>\n",
       "      <th>old_coord</th>\n",
       "      <th>new_coord</th>\n",
       "      <th>mlg_key</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Aba, Abia</td>\n",
       "      <td>5.1167 , 7.3667</td>\n",
       "      <td>5.1166666666667 , 7.3666666666667</td>\n",
       "      <td>Aba, Abia 5.1167,7.3667</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Abak</td>\n",
       "      <td>4.9833 , 7.7833</td>\n",
       "      <td>4.9833333333333 , 7.7833333333333</td>\n",
       "      <td>Abak 4.9833,7.7833</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Abbotsford, British Columbia</td>\n",
       "      <td>49.0489 , -122.2903</td>\n",
       "      <td>49.054611111111 , -122.328</td>\n",
       "      <td>Abbotsford, British Columbia 49.0489,-122.2903</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Abbotsford, New South Wales</td>\n",
       "      <td>-33.8522 , 151.1273</td>\n",
       "      <td>-33.8521 , 151.127</td>\n",
       "      <td>Abbotsford, New South Wales -33.8522,151.1273</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Abbotsford, Victoria</td>\n",
       "      <td>-37.803 , 145.002</td>\n",
       "      <td>-37.8 , 145.0</td>\n",
       "      <td>Abbotsford, Victoria -37.803,145.002</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                         toponym              old_coord  \\\n",
       "0                     Aba, Abia        5.1167 , 7.3667    \n",
       "1                          Abak        4.9833 , 7.7833    \n",
       "2  Abbotsford, British Columbia    49.0489 , -122.2903    \n",
       "3   Abbotsford, New South Wales    -33.8522 , 151.1273    \n",
       "4          Abbotsford, Victoria      -37.803 , 145.002    \n",
       "\n",
       "                            new_coord  \\\n",
       "0   5.1166666666667 , 7.3666666666667   \n",
       "1   4.9833333333333 , 7.7833333333333   \n",
       "2          49.054611111111 , -122.328   \n",
       "3                  -33.8521 , 151.127   \n",
       "4                       -37.8 , 145.0   \n",
       "\n",
       "                                          mlg_key  \n",
       "0                         Aba, Abia 5.1167,7.3667  \n",
       "1                              Abak 4.9833,7.7833  \n",
       "2  Abbotsford, British Columbia 49.0489,-122.2903  \n",
       "3   Abbotsford, New South Wales -33.8522,151.1273  \n",
       "4            Abbotsford, Victoria -37.803,145.002  "
      ]
     },
     "execution_count": 44,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "mlg_wiktor['mlg_key'] = mlg_wiktor.apply(lambda x: calc_mlg_key(x['toponym'],x['old_coord']),axis = 1)\n",
    "mlg_wiktor.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "id": "0e96b59a",
   "metadata": {},
   "outputs": [],
   "source": [
    "def calc_wiktor_key(wikipedia_name, lat, lon):\n",
    "    wikipedia_name = wikipedia_name.strip(' ')\n",
    "    if (lat != None) & (lon != None):\n",
    "        lat = lat.strip(' ')\n",
    "        lon = lon.strip(' ')\n",
    "        return wikipedia_name+' '+lat+','+lon"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 46,
   "id": "00510f31",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>name</th>\n",
       "      <th>wikipedia_name</th>\n",
       "      <th>lat</th>\n",
       "      <th>lon</th>\n",
       "      <th>page</th>\n",
       "      <th>wiktor_key</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Santa Maria</td>\n",
       "      <td>Santa Maria, California</td>\n",
       "      <td>34.9514</td>\n",
       "      <td>-120.4333</td>\n",
       "      <td>http://en.wikipedia.org/wiki/Santa_Maria%2C_Ca...</td>\n",
       "      <td>Santa Maria, California 34.9514,-120.4333</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Santa Maria</td>\n",
       "      <td>Santa Maria, Bulacan</td>\n",
       "      <td>14.8208</td>\n",
       "      <td>120.9636</td>\n",
       "      <td>http://en.wikipedia.org/wiki/Santa_Maria%2C_Bu...</td>\n",
       "      <td>Santa Maria, Bulacan 14.8208,120.9636</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Santa Maria</td>\n",
       "      <td>Santa Maria, Ilocos Sur</td>\n",
       "      <td>17.3585</td>\n",
       "      <td>120.4981</td>\n",
       "      <td>http://en.wikipedia.org/wiki/Santa_Maria%2C_Il...</td>\n",
       "      <td>Santa Maria, Ilocos Sur 17.3585,120.4981</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Santa Maria</td>\n",
       "      <td>Santa Maria, Romblon</td>\n",
       "      <td>12.4417</td>\n",
       "      <td>122.1042</td>\n",
       "      <td>http://en.wikipedia.org/wiki/Santa_Maria%2C_Ro...</td>\n",
       "      <td>Santa Maria, Romblon 12.4417,122.1042</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Santa Cruz</td>\n",
       "      <td>Santa Cruz, Chile</td>\n",
       "      <td>-34.6372</td>\n",
       "      <td>-71.3631</td>\n",
       "      <td>http://en.wikipedia.org/wiki/Santa_Cruz%2C_Chile</td>\n",
       "      <td>Santa Cruz, Chile -34.6372,-71.3631</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "          name           wikipedia_name       lat        lon  \\\n",
       "0  Santa Maria  Santa Maria, California   34.9514  -120.4333   \n",
       "1  Santa Maria     Santa Maria, Bulacan   14.8208   120.9636   \n",
       "2  Santa Maria  Santa Maria, Ilocos Sur   17.3585   120.4981   \n",
       "3  Santa Maria     Santa Maria, Romblon   12.4417   122.1042   \n",
       "4   Santa Cruz        Santa Cruz, Chile  -34.6372   -71.3631   \n",
       "\n",
       "                                                page  \\\n",
       "0  http://en.wikipedia.org/wiki/Santa_Maria%2C_Ca...   \n",
       "1  http://en.wikipedia.org/wiki/Santa_Maria%2C_Bu...   \n",
       "2  http://en.wikipedia.org/wiki/Santa_Maria%2C_Il...   \n",
       "3  http://en.wikipedia.org/wiki/Santa_Maria%2C_Ro...   \n",
       "4   http://en.wikipedia.org/wiki/Santa_Cruz%2C_Chile   \n",
       "\n",
       "                                  wiktor_key  \n",
       "0  Santa Maria, California 34.9514,-120.4333  \n",
       "1      Santa Maria, Bulacan 14.8208,120.9636  \n",
       "2   Santa Maria, Ilocos Sur 17.3585,120.4981  \n",
       "3      Santa Maria, Romblon 12.4417,122.1042  \n",
       "4        Santa Cruz, Chile -34.6372,-71.3631  "
      ]
     },
     "execution_count": 46,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_wiktor_poi['wiktor_key'] = df_wiktor_poi.apply(lambda x: calc_wiktor_key(x['wikipedia_name'], x['lat'], x['lon']),axis = 1)\n",
    "df_wiktor_poi.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "id": "ad507208",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>name</th>\n",
       "      <th>wikipedia_name</th>\n",
       "      <th>lat</th>\n",
       "      <th>lon</th>\n",
       "      <th>page</th>\n",
       "      <th>wiktor_key</th>\n",
       "      <th>toponym</th>\n",
       "      <th>old_coord</th>\n",
       "      <th>new_coord</th>\n",
       "      <th>mlg_key</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Santa Maria</td>\n",
       "      <td>Santa Maria, California</td>\n",
       "      <td>34.9514</td>\n",
       "      <td>-120.4333</td>\n",
       "      <td>http://en.wikipedia.org/wiki/Santa_Maria%2C_Ca...</td>\n",
       "      <td>Santa Maria, California 34.9514,-120.4333</td>\n",
       "      <td>Santa Maria, California</td>\n",
       "      <td>34.9514 , -120.4333</td>\n",
       "      <td>34.951388888889 , -120.43333333333</td>\n",
       "      <td>Santa Maria, California 34.9514,-120.4333</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Santa Maria</td>\n",
       "      <td>Santa Maria, Bulacan</td>\n",
       "      <td>14.8208</td>\n",
       "      <td>120.9636</td>\n",
       "      <td>http://en.wikipedia.org/wiki/Santa_Maria%2C_Bu...</td>\n",
       "      <td>Santa Maria, Bulacan 14.8208,120.9636</td>\n",
       "      <td>Santa Maria, Bulacan</td>\n",
       "      <td>14.8208 , 120.9636</td>\n",
       "      <td>14.818333333333 , 120.95633333333</td>\n",
       "      <td>Santa Maria, Bulacan 14.8208,120.9636</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Santa Maria</td>\n",
       "      <td>Santa Maria, Ilocos Sur</td>\n",
       "      <td>17.3585</td>\n",
       "      <td>120.4981</td>\n",
       "      <td>http://en.wikipedia.org/wiki/Santa_Maria%2C_Il...</td>\n",
       "      <td>Santa Maria, Ilocos Sur 17.3585,120.4981</td>\n",
       "      <td>Santa Maria, Ilocos Sur</td>\n",
       "      <td>17.3585 , 120.4981</td>\n",
       "      <td>17.366666666667 , 120.48333333333</td>\n",
       "      <td>Santa Maria, Ilocos Sur 17.3585,120.4981</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Santa Maria</td>\n",
       "      <td>Santa Maria, Romblon</td>\n",
       "      <td>12.4417</td>\n",
       "      <td>122.1042</td>\n",
       "      <td>http://en.wikipedia.org/wiki/Santa_Maria%2C_Ro...</td>\n",
       "      <td>Santa Maria, Romblon 12.4417,122.1042</td>\n",
       "      <td>Santa Maria, Romblon</td>\n",
       "      <td>12.4417 , 122.1042</td>\n",
       "      <td>12.441667 , 122.104167</td>\n",
       "      <td>Santa Maria, Romblon 12.4417,122.1042</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Santa Cruz</td>\n",
       "      <td>Santa Cruz, Chile</td>\n",
       "      <td>-34.6372</td>\n",
       "      <td>-71.3631</td>\n",
       "      <td>http://en.wikipedia.org/wiki/Santa_Cruz%2C_Chile</td>\n",
       "      <td>Santa Cruz, Chile -34.6372,-71.3631</td>\n",
       "      <td>Santa Cruz, Chile</td>\n",
       "      <td>-34.6372 , -71.3631</td>\n",
       "      <td>-34.633333333333 , -71.366666666667</td>\n",
       "      <td>Santa Cruz, Chile -34.6372,-71.3631</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "          name           wikipedia_name       lat        lon  \\\n",
       "0  Santa Maria  Santa Maria, California   34.9514  -120.4333   \n",
       "1  Santa Maria     Santa Maria, Bulacan   14.8208   120.9636   \n",
       "2  Santa Maria  Santa Maria, Ilocos Sur   17.3585   120.4981   \n",
       "3  Santa Maria     Santa Maria, Romblon   12.4417   122.1042   \n",
       "4   Santa Cruz        Santa Cruz, Chile  -34.6372   -71.3631   \n",
       "\n",
       "                                                page  \\\n",
       "0  http://en.wikipedia.org/wiki/Santa_Maria%2C_Ca...   \n",
       "1  http://en.wikipedia.org/wiki/Santa_Maria%2C_Bu...   \n",
       "2  http://en.wikipedia.org/wiki/Santa_Maria%2C_Il...   \n",
       "3  http://en.wikipedia.org/wiki/Santa_Maria%2C_Ro...   \n",
       "4   http://en.wikipedia.org/wiki/Santa_Cruz%2C_Chile   \n",
       "\n",
       "                                  wiktor_key                   toponym  \\\n",
       "0  Santa Maria, California 34.9514,-120.4333  Santa Maria, California    \n",
       "1      Santa Maria, Bulacan 14.8208,120.9636     Santa Maria, Bulacan    \n",
       "2   Santa Maria, Ilocos Sur 17.3585,120.4981  Santa Maria, Ilocos Sur    \n",
       "3      Santa Maria, Romblon 12.4417,122.1042     Santa Maria, Romblon    \n",
       "4        Santa Cruz, Chile -34.6372,-71.3631        Santa Cruz, Chile    \n",
       "\n",
       "               old_coord                             new_coord  \\\n",
       "0   34.9514 , -120.4333     34.951388888889 , -120.43333333333   \n",
       "1    14.8208 , 120.9636      14.818333333333 , 120.95633333333   \n",
       "2    17.3585 , 120.4981      17.366666666667 , 120.48333333333   \n",
       "3    12.4417 , 122.1042                 12.441667 , 122.104167   \n",
       "4   -34.6372 , -71.3631    -34.633333333333 , -71.366666666667   \n",
       "\n",
       "                                     mlg_key  \n",
       "0  Santa Maria, California 34.9514,-120.4333  \n",
       "1      Santa Maria, Bulacan 14.8208,120.9636  \n",
       "2   Santa Maria, Ilocos Sur 17.3585,120.4981  \n",
       "3      Santa Maria, Romblon 12.4417,122.1042  \n",
       "4        Santa Cruz, Chile -34.6372,-71.3631  "
      ]
     },
     "execution_count": 47,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_wiktor_poi_unified = pd.merge(df_wiktor_poi, mlg_wiktor, how = 'outer', left_on = 'wiktor_key', right_on = 'mlg_key')\n",
    "df_wiktor_poi_unified.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 48,
   "id": "90c00dc5",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "Int64Index: 9896 entries, 0 to 9895\n",
      "Data columns (total 10 columns):\n",
      " #   Column          Non-Null Count  Dtype \n",
      "---  ------          --------------  ----- \n",
      " 0   name            5000 non-null   object\n",
      " 1   wikipedia_name  5000 non-null   object\n",
      " 2   lat             5000 non-null   object\n",
      " 3   lon             5000 non-null   object\n",
      " 4   page            5000 non-null   object\n",
      " 5   wiktor_key      5000 non-null   object\n",
      " 6   toponym         9803 non-null   object\n",
      " 7   old_coord       9803 non-null   object\n",
      " 8   new_coord       9803 non-null   object\n",
      " 9   mlg_key         9803 non-null   object\n",
      "dtypes: object(10)\n",
      "memory usage: 850.4+ KB\n"
     ]
    }
   ],
   "source": [
    "df_wiktor_poi_unified.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 49,
   "id": "72868d36",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_wiktor_poi_unified.to_csv(os.path.dirname(os.getcwd())+'\\\\data\\\\extracted-annotated-locations\\\\WikToR_poi.csv')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "55d2fe3f",
   "metadata": {},
   "source": [
    "## GeoCorpora POI Extraction"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 50,
   "id": "9941b9c5",
   "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>feature_id</th>\n",
       "      <th>tweet_id_str</th>\n",
       "      <th>tweet_text</th>\n",
       "      <th>tweet_created_at</th>\n",
       "      <th>tweet_user_location</th>\n",
       "      <th>tweet_user_timezone</th>\n",
       "      <th>tweet_coordinates_latitude</th>\n",
       "      <th>tweet_coordinates_longitude</th>\n",
       "      <th>tweet_placeid</th>\n",
       "      <th>char_position</th>\n",
       "      <th>...</th>\n",
       "      <th>toponym</th>\n",
       "      <th>country_code</th>\n",
       "      <th>longitude</th>\n",
       "      <th>latitude</th>\n",
       "      <th>surrogate_geojson</th>\n",
       "      <th>uncertain_semantics</th>\n",
       "      <th>vague</th>\n",
       "      <th>overlapping_ambiguous</th>\n",
       "      <th>non_overlapping_ambiguous</th>\n",
       "      <th>not_in_geonames</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>557916969433718785</td>\n",
       "      <td>Bird Flu: Rivers Government confirms outbreak ...</td>\n",
       "      <td>Wed Jan 21 15:05:45 +0000 2015</td>\n",
       "      <td>Nigeria</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>10.0</td>\n",
       "      <td>...</td>\n",
       "      <td>Rivers State</td>\n",
       "      <td>NG</td>\n",
       "      <td>6.82766</td>\n",
       "      <td>4.74974</td>\n",
       "      <td>NaN</td>\n",
       "      <td>True</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>557916969433718785</td>\n",
       "      <td>Bird Flu: Rivers Government confirms outbreak ...</td>\n",
       "      <td>Wed Jan 21 15:05:45 +0000 2015</td>\n",
       "      <td>Nigeria</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>85.0</td>\n",
       "      <td>...</td>\n",
       "      <td>Federal Republic of Nigeria</td>\n",
       "      <td>NG</td>\n",
       "      <td>8.00000</td>\n",
       "      <td>10.00000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>557916969433718785</td>\n",
       "      <td>Bird Flu: Rivers Government confirms outbreak ...</td>\n",
       "      <td>Wed Jan 21 15:05:45 +0000 2015</td>\n",
       "      <td>Nigeria</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>99.0</td>\n",
       "      <td>...</td>\n",
       "      <td>Federal Republic of Nigeria</td>\n",
       "      <td>NG</td>\n",
       "      <td>8.00000</td>\n",
       "      <td>10.00000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>559125629711708161</td>\n",
       "      <td>RT @JohnFugelsang: Lando Calrissian got a bett...</td>\n",
       "      <td>Sat Jan 24 23:08:32 +0000 2015</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5</td>\n",
       "      <td>493108823859924992</td>\n",
       "      <td>Tosi oudot albumit -artikkelisarja alkaa! Ensi...</td>\n",
       "      <td>Sat Jul 26 19:01:19 +0000 2014</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Helsinki</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 22 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "   feature_id        tweet_id_str  \\\n",
       "0           1  557916969433718785   \n",
       "1           2  557916969433718785   \n",
       "2           3  557916969433718785   \n",
       "3           4  559125629711708161   \n",
       "4           5  493108823859924992   \n",
       "\n",
       "                                          tweet_text  \\\n",
       "0  Bird Flu: Rivers Government confirms outbreak ...   \n",
       "1  Bird Flu: Rivers Government confirms outbreak ...   \n",
       "2  Bird Flu: Rivers Government confirms outbreak ...   \n",
       "3  RT @JohnFugelsang: Lando Calrissian got a bett...   \n",
       "4  Tosi oudot albumit -artikkelisarja alkaa! Ensi...   \n",
       "\n",
       "                 tweet_created_at tweet_user_location tweet_user_timezone  \\\n",
       "0  Wed Jan 21 15:05:45 +0000 2015             Nigeria                 NaN   \n",
       "1  Wed Jan 21 15:05:45 +0000 2015             Nigeria                 NaN   \n",
       "2  Wed Jan 21 15:05:45 +0000 2015             Nigeria                 NaN   \n",
       "3  Sat Jan 24 23:08:32 +0000 2015                 NaN                 NaN   \n",
       "4  Sat Jul 26 19:01:19 +0000 2014                 NaN            Helsinki   \n",
       "\n",
       "   tweet_coordinates_latitude  tweet_coordinates_longitude tweet_placeid  \\\n",
       "0                         NaN                          NaN           NaN   \n",
       "1                         NaN                          NaN           NaN   \n",
       "2                         NaN                          NaN           NaN   \n",
       "3                         NaN                          NaN           NaN   \n",
       "4                         NaN                          NaN           NaN   \n",
       "\n",
       "   char_position  ...                      toponym  country_code longitude  \\\n",
       "0           10.0  ...                 Rivers State            NG   6.82766   \n",
       "1           85.0  ...  Federal Republic of Nigeria            NG   8.00000   \n",
       "2           99.0  ...  Federal Republic of Nigeria            NG   8.00000   \n",
       "3            NaN  ...                          NaN           NaN       NaN   \n",
       "4            NaN  ...                          NaN           NaN       NaN   \n",
       "\n",
       "   latitude  surrogate_geojson  uncertain_semantics vague  \\\n",
       "0   4.74974                NaN                 True   NaN   \n",
       "1  10.00000                NaN                  NaN   NaN   \n",
       "2  10.00000                NaN                  NaN   NaN   \n",
       "3       NaN                NaN                  NaN   NaN   \n",
       "4       NaN                NaN                  NaN   NaN   \n",
       "\n",
       "  overlapping_ambiguous non_overlapping_ambiguous not_in_geonames  \n",
       "0                   NaN                       NaN             NaN  \n",
       "1                   NaN                       NaN             NaN  \n",
       "2                   NaN                       NaN             NaN  \n",
       "3                   NaN                       NaN             NaN  \n",
       "4                   NaN                       NaN             NaN  \n",
       "\n",
       "[5 rows x 22 columns]"
      ]
     },
     "execution_count": 50,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_geocorpora = pd.read_csv(os.path.dirname(os.getcwd())+'\\\\data\\\\evaluation-corpora\\\\original-datasets\\\\geocorpora.tsv', sep = '\\t')\n",
    "df_geocorpora.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 51,
   "id": "4b66374b",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_geocorpora_poi = df_geocorpora[['geoNameId','toponym','longitude','latitude']]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 52,
   "id": "edd4a231",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "Int64Index: 3100 entries, 0 to 7630\n",
      "Data columns (total 4 columns):\n",
      " #   Column     Non-Null Count  Dtype  \n",
      "---  ------     --------------  -----  \n",
      " 0   geoNameId  3100 non-null   float64\n",
      " 1   toponym    3100 non-null   object \n",
      " 2   longitude  3100 non-null   float64\n",
      " 3   latitude   3100 non-null   float64\n",
      "dtypes: float64(3), object(1)\n",
      "memory usage: 121.1+ KB\n"
     ]
    }
   ],
   "source": [
    "df_geocorpora_poi = df_geocorpora_poi.dropna()\n",
    "df_geocorpora_poi.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 53,
   "id": "a261be50",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "Int64Index: 1012 entries, 0 to 7629\n",
      "Data columns (total 4 columns):\n",
      " #   Column     Non-Null Count  Dtype  \n",
      "---  ------     --------------  -----  \n",
      " 0   geoNameId  1012 non-null   float64\n",
      " 1   toponym    1012 non-null   object \n",
      " 2   longitude  1012 non-null   float64\n",
      " 3   latitude   1012 non-null   float64\n",
      "dtypes: float64(3), object(1)\n",
      "memory usage: 39.5+ KB\n"
     ]
    }
   ],
   "source": [
    "df_geocorpora_poi = df_geocorpora_poi.drop_duplicates()\n",
    "df_geocorpora_poi.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 54,
   "id": "04dd4985",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_geocorpora_poi.to_csv((os.path.dirname(os.getcwd())+'\\\\data\\\\extracted-annotated-locations\\\\GeoCorpora_poi.csv'))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "4ff49e4f",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "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.8.8"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}