{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now that we have a SQLite database with indices, page titles, and coordinate strings, let's make a database where we extract all the metadata out of those coordinate strings so it's queryable.\n",
    "\n",
    "This should be run after the other notebook that extracts the coordinate strings."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {},
   "outputs": [],
   "source": [
    "import csv\n",
    "import json\n",
    "from wikiparse import indexer, syntax_parser as sp\n",
    "import time\n",
    "import os\n",
    "import sqlite3\n",
    "import random"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "opening E:/enwiki-20190101-pages-articles-multistream.xml/scratch\\py3\\index.db\n",
      "current mapping 19.1 m pages\n",
      "\n",
      "__init__ complete\n"
     ]
    }
   ],
   "source": [
    "dumps = indexer.load_dumps(build_index=False, scratch_folder='py3')\n",
    "english = dumps['en']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "metadata": {},
   "outputs": [],
   "source": [
    "# english.db.close()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "metadata": {},
   "outputs": [],
   "source": [
    "c = english.cursor"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Before we create the database let's get a complete list of the entries we're going to want. That is, let's look at all the coordinate strings we've extracted from each page and extract the list of keywords from there. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[(66,\n",
       "  'Coord|32.7|-86.7|type:adm1st_region:US_dim:1000000_source:USGS|display=title'),\n",
       " (86, 'Coord|36|42|N|3|13|E|type:city'),\n",
       " (105, 'coord|42|30|N|1|31|E|display=inline,title'),\n",
       " (114,\n",
       "  'Coord|64|N|150|W|region:US-AK_type:adm1st_scale:10000000|display=title|notes=<ref>{{Cite gnis|1785533|State of Alaska'),\n",
       " (139,\n",
       "  'Coord|13|19|N|169|9|W|type:event|name=Apollo 11 splashdown||Coord|10|36|N|172|24|E|display=inline||Coord|13|19|N|169|9|W|display=inline'),\n",
       " (140,\n",
       "  'Coord|8|8|N|165|1|W|type:event|name=Apollo 8 landing||Coord|30|12|N|74|7|W|name=Apollo 8 S-IC impact||Coord|31|50|N|37|17|W|name=Apollo 8 S-II impact||Coord|8|8|N|165|1|W|name=Apollo 8 estimated splashdown'),\n",
       " (161,\n",
       "  'Coord|12|30|40|N|69|58|27|W|type:isle|display=title||Coord|12|31|07|N|70|02|09|W||Coord|12|31|01|N|70|02|04|W|'),\n",
       " (166, 'coord|0|N|25|W|region:ZZ_type:waterbody|display=inline,title'),\n",
       " (168, 'Coord|12|30|S|18|30|E|display=title||Coord|8|50|S|13|20|E|type:city'),\n",
       " (177,\n",
       "  'Coord|55|N|115|W|type:adm1st_scale:10000000_region:CA-AB|display=title')]"
      ]
     },
     "execution_count": 38,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "result = c.execute('''SELECT page_num,coords,title FROM indices WHERE coords != \"\"\n",
    "''').fetchall()\n",
    "coordStrings = {item[0]:item[1] for item in result}\n",
    "idx_to_title = {item[0]:item[2] for item in result}\n",
    "list(coordStrings.items())[:10]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(1152376, dict)"
      ]
     },
     "execution_count": 39,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(coordStrings), type(coordStrings)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "If a page has more than one coordinate string, choose the one that's displayed at the top (`display=title`) or the first."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "metadata": {},
   "outputs": [],
   "source": [
    "for page_num in coordStrings:\n",
    "    if '||' in coordStrings[page_num]:\n",
    "        pageCoordStrings = coordStrings[page_num].split('||')\n",
    "        coordStrings[page_num] = pageCoordStrings[0]\n",
    "        for s in pageCoordStrings:\n",
    "            if \"display=title\" in s:\n",
    "                coordStrings[page_num] = s"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[(66,\n",
       "  'Coord|32.7|-86.7|type:adm1st_region:US_dim:1000000_source:USGS|display=title'),\n",
       " (86, 'Coord|36|42|N|3|13|E|type:city'),\n",
       " (105, 'coord|42|30|N|1|31|E|display=inline,title'),\n",
       " (114,\n",
       "  'Coord|64|N|150|W|region:US-AK_type:adm1st_scale:10000000|display=title|notes=<ref>{{Cite gnis|1785533|State of Alaska'),\n",
       " (139, 'Coord|13|19|N|169|9|W|type:event|name=Apollo 11 splashdown'),\n",
       " (140, 'Coord|8|8|N|165|1|W|type:event|name=Apollo 8 landing'),\n",
       " (161, 'Coord|12|30|40|N|69|58|27|W|type:isle|display=title'),\n",
       " (166, 'coord|0|N|25|W|region:ZZ_type:waterbody|display=inline,title'),\n",
       " (168, 'Coord|12|30|S|18|30|E|display=title'),\n",
       " (177,\n",
       "  'Coord|55|N|115|W|type:adm1st_scale:10000000_region:CA-AB|display=title')]"
      ]
     },
     "execution_count": 41,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "list(coordStrings.items())[:10]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "For some Coord templates, there's a `note` (https://en.wikipedia.org/wiki/Template:Coord#Examples) which contains more pipes that will cut off the rest of the template. Since that tag seems to come after the other important tags, let's\n",
    "ignore this problem."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "{'type': 'event', 'name': 'Apollo 8 landing'}\n",
      "{'region': 'US-AK_type', 'display': 'title', 'notes': '<ref>{{Cite gnis'}\n",
      "{'type': 'city'}\n"
     ]
    }
   ],
   "source": [
    "def getKeywords(coordString, verbose=False):\n",
    "    if verbose:\n",
    "        print(coordString)\n",
    "    keywords = {}\n",
    "    rest = []\n",
    "    items = coordString.split('|')\n",
    "    for item in items:\n",
    "        if '=' in item:\n",
    "            keywords[item.split('=')[0]] = item.split('=')[1]\n",
    "        elif ':' in item:\n",
    "            keywords[item.split(':')[0]] = item.split(':')[1]\n",
    "        else:\n",
    "            rest.append(item)\n",
    "#     return keywords, '|'.join(rest)\n",
    "    return keywords\n",
    "print(getKeywords('Coord|8|8|N|165|1|W|type:event|name=Apollo 8 landing'))\n",
    "print(getKeywords('Coord|64|N|150|W|region:US-AK_type:adm1st_scale:10000000|display=title|notes=<ref>{{Cite gnis|1785533|State of Alaska'))\n",
    "print(getKeywords('Coord|36|42|N|3|13|E|type:city'))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We only care about a subset of keywords, so let's make a whitelist."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "defaultdict(int,\n",
       "            {'type': 287434,\n",
       "             'display': 1099820,\n",
       "             'region': 432293,\n",
       "             'notes': 1748,\n",
       "             'name': 20998,\n",
       "             'dim': 4491,\n",
       "             'format': 133330,\n",
       "             'source': 67662,\n",
       "             'globe': 2834,\n",
       "             'scale': 8045,\n",
       "             'id': 217,\n",
       "             'accessdate': 305,\n",
       "             'entrydate': 42,\n",
       "             'title': 270,\n",
       "             'journal': 9,\n",
       "             'volume': 9,\n",
       "             'number': 1,\n",
       "             'pages': 12,\n",
       "             'author1': 1,\n",
       "             'date': 62,\n",
       "             'doi': 8,\n",
       "             'bibcode': 2,\n",
       "             'display-authors': 1,\n",
       "             'label': 1,\n",
       "             '3': 143,\n",
       "             'url': 151,\n",
       "             'DK_type': 1,\n",
       "             'trans-title': 2,\n",
       "             'language': 7,\n",
       "             'publisher': 99,\n",
       "             'type_landmark_region': 2,\n",
       "             'region:US': 1,\n",
       "             '2': 5,\n",
       "             \"The ''globe''&nbsp;[[File\": 25,\n",
       "             'upright': 25,\n",
       "             '': 5,\n",
       "             'first': 13,\n",
       "             'last': 19,\n",
       "             'work': 70,\n",
       "             'deadurl': 6,\n",
       "             'archiveurl': 5,\n",
       "             'archivedate': 5,\n",
       "             'df': 4,\n",
       "             '234503_622630_region': 1,\n",
       "             'הערה': 1,\n",
       "             'landmark_region': 4,\n",
       "             'USGS': 3,\n",
       "             '4': 8,\n",
       "             '6': 3,\n",
       "             'Register of Historic Parks and Gardens]].<ref name': 1,\n",
       "             'num': 1,\n",
       "             'desc': 1,\n",
       "             'access-date': 29,\n",
       "             'mode': 1,\n",
       "             'year': 14,\n",
       "             'page': 9,\n",
       "             '44region': 1,\n",
       "             '7': 3,\n",
       "             'length_km': 2,\n",
       "             '<br>Crow Lane Roundabout': 1,\n",
       "             'nosave': 8,\n",
       "             'website': 12,\n",
       "             'source:https://archnet.org/print/preview/sites': 1,\n",
       "             'For example': 1,\n",
       "             '5': 7,\n",
       "             'elevation': 4,\n",
       "             'GNS]] coordinates adjusted using [[Google Maps]] and [http': 42,\n",
       "             'type;landmark_region': 2,\n",
       "             'author': 16,\n",
       "             'location': 9,\n",
       "             'GNS]] coordinates adjusted using [[Google Maps]], and [http': 23,\n",
       "             '600700_212618_region': 1,\n",
       "             '{{#expr': 457,\n",
       "             '<!-- 10 Guittard Rd.<ref name': 1,\n",
       "             'reason': 3,\n",
       "             'adm2nd_region': 3,\n",
       "             'area_km2': 2,\n",
       "             'GE_scale': 1,\n",
       "             '526917_188241_region': 1,\n",
       "             '405800_439900_region': 1,\n",
       "             'Type': 6,\n",
       "             'authorlink': 3,\n",
       "             'isbn': 3,\n",
       "             '{{#invoke': 2,\n",
       "             '[[MediaWiki': 1,\n",
       "             'egion': 1,\n",
       "             'last1': 9,\n",
       "             'first1': 9,\n",
       "             'archive-url': 1,\n",
       "             'dead-url': 1,\n",
       "             'archive-date': 1,\n",
       "             '_region': 8,\n",
       "             '9': 4,\n",
       "             'http': 5,\n",
       "             'GNS]] coordinates adjusted using an Instituto Geográfico Militar [http': 7,\n",
       "             'typetype': 1,\n",
       "             'city': 6,\n",
       "             'ref': 1,\n",
       "             'FR_type': 2,\n",
       "             '_source': 13,\n",
       "             'region_BD_type': 1,\n",
       "             'p': 55,\n",
       "             'isle_region': 1,\n",
       "             'GBtype': 1,\n",
       "             'first2': 7,\n",
       "             'last2': 7,\n",
       "             'first3': 7,\n",
       "             'last3': 7,\n",
       "             'issue': 6,\n",
       "             'NZ244651_region': 2,\n",
       "             '{{#statements': 1,\n",
       "             'tpye': 1,\n",
       "             'coauthors': 1,\n",
       "             \"coordinates map template]] and one of the options for either degree, minutes and seconds or decimal latitude and longitude.\\n\\n*There has been a '''[[Module\": 1,\n",
       "             'Simpson]] and [[Template': 1,\n",
       "             'Location map options and instructions]] for an example of its use.\\n\\n\\n6. Update census demographic information to the [http://www12.statcan.ca/english/census06/data/profiles/community/Index.cfm?Lang': 1,\n",
       "             'community]] pages and remove <nowiki>{{</nowiki>Template': 1,\n",
       "             '_type': 5,\n",
       "             'type_city_region': 1,\n",
       "             'regionCA-ON_type': 1,\n",
       "             'adm3rd_region': 1,\n",
       "             'issn': 5,\n",
       "             'display{{': 2,\n",
       "             \"Coord]] - a cool template that displays the coordinates of the county. Notes about this one:\\n\\n''View [[Template:Infobox U.S. county/Sample]] to see how this infobox is rendered.''\": 1,\n",
       "             'Region': 4,\n",
       "             'editprotected request]] is fulfilled. —[[User': 1,\n",
       "             'Para]] 15': 1,\n",
       "             'Michael de la Pole]], seated at Wingfield Castle, who in 1385 was created [[Earl of Suffolk]]. His descendant [[Edmund de la Pole, 3rd Duke of Suffolk]] (1472–1513) was forced to surrender his dukedom in 1493. It was resurrected by King Henry VIII in 1514 for his [[favourite]] [[Charles Brandon, 1st Duke of Suffolk]] (1484–1545), who although he had no close connection with Wingfield Castle and the county of Suffolk, was a great-grandson of Sir [[Robert Wingfield]] (died 1454), of [[Letheringham]] in [[Suffolk]], about 12 miles south of Wingfield.': 1,\n",
       "             'heading': 5,\n",
       "             'region_US-CA_type': 1,\n",
       "             'Coord]], [[Template': 1,\n",
       "             'Coor at dms]], etc. To simplify things, I switch the URL to the one using [[Template': 1,\n",
       "             'Erik Baas]] 15': 1,\n",
       "             'Bryan]] (<small>[[User talk': 1,\n",
       "             '[[': 1,\n",
       "             'commons]]</small>) 18': 1,\n",
       "             'Dispenser]] 19': 1,\n",
       "             'Cush]] 10:22, 28 October 2007 (UTC)': 1,\n",
       "             'Rtphokie]] 22': 1,\n",
       "             '<font color': 2,\n",
       "             'Gene Nygaard]] 23': 1,\n",
       "             'Anomie]] 01': 1,\n",
       "             'Rtphokie]] 12:32, 28 October 2007 (UTC)': 1,\n",
       "             'NOTE_TEXT': 2,\n",
       "             'NOTE_CAT': 2,\n",
       "             'contribs]]; 1/1) [[': 1,\n",
       "             'Monitored link]] - www.fourinfo.com/newsletter/feb05.htm#greetings%20from%20rural%20ramble - rule': 1,\n",
       "             '.com - reason: Automonitor: reported to [[:fr:MediaWiki:Spam-blacklist]] ([http://fr.wikipedia.org/w/index.php?title': 1,\n",
       "             'Rural Ramble]] - [http://en.wikipedia.org/w/index.php?title': 1,\n",
       "             'zoom': 4,\n",
       "             'minority]] when trying to oppose the removal of another more organised linkspamming. I cannot fathom how people can even think that promoting some specific external service over all its alternatives would be following [[WP': 1,\n",
       "             'Para]] ([[User talk': 1,\n",
       "             'talk]]) 12': 2,\n",
       "             'Orderinchaos]] 18': 1,\n",
       "             '<b><font color': 1,\n",
       "             'resolution]]; please use them. I am marking this resolved since there is nothing that any admin can or will do. [[User': 1,\n",
       "             'LessHeard vanU]] ([[User talk': 1,\n",
       "             'talk]]) 13': 2,\n",
       "             'Wikidemo]] ([[User talk': 1,\n",
       "             'talk]]) 16': 1,\n",
       "             'talk]]) 17': 2,\n",
       "             'consensus editing]].  Instead, if Brownhaired Girl felt this was highly inappropriate, that is why we have [[WP': 1,\n",
       "             'dispute resolution]].  An RfC, Mediation, etc would have handled it quite properly.[[User': 1,\n",
       "             'Wjhonson]] ([[User talk': 1,\n",
       "             \"talk]]) 00:05, 3 February 2008 (UTC)\\n\\n*(resolved label removed - an important point has yet to be made). I've reviewed the [http://en.wikipedia.org/w/index.php?title\": 1,\n",
       "             'Carcharoth]] ([[User talk': 1,\n",
       "             'talk]]) 01': 1,\n",
       "             'here]] for a discussion on how best to phrase all this in the documentation. I\\'ve left various other notes around, so hopefully people will realise the sense in using custom edit summaries for non-vandalism reverts (regardless of what tool is used to do the reverts, be it rollback, undo, or some combination using scripts). Ideally, people on a dedicated run of rollbacking vandalism (eg. RC patrolling) would also have a custom edit summary enabled (eg. \"rollback of vandalism reviewed during recent changes patrolling\"). People spotting the ocassional instances of vandalism during normal editing will still find it quicker to hit rollback, and I am aware (before someone says this) that there is no requirement to use edit summaries at all, but it makes a great deal of sense to use informative edit summaries where possible. [[User': 1,\n",
       "             'talk]]) 01:43, 3 February 2008 (UTC)': 1,\n",
       "             '{{subst': 1,\n",
       "             'lead coordinator resigned]], the project [[Wikipedia talk': 1,\n",
       "             'modified its A-class review process]] to include a source review, [[Wikipedia talk': 1,\n",
       "             \"altered its internal guidelines]] for drafting biographies, and questions about the German war effort case are being asked by a couple of our members to the candidates for this year's ArbCom election. In the past 24 hours, it [[Wikipedia talk\": 1,\n",
       "             'has resurfaced]] [[Wikipedia talk': 1,\n",
       "             'in a dispute]] about an award the project bestows annually. Might be worth a special report. I for one know it will effect how I will be voting for Arbcom this year. -[[User': 1,\n",
       "             'Indy beetle]] ([[User talk': 1,\n",
       "             'talk]]) 02:46, 3 December 2018 (UTC)': 1,\n",
       "             'unsigned]] comment added by [[User': 1,\n",
       "             '108.24.2.34]] ([[User talk': 1,\n",
       "             'talk]] • [[Special': 1,\n",
       "             'contribs]]) 23': 1,\n",
       "             'last4': 4,\n",
       "             'first4': 4,\n",
       "             'coordinate template]], as well as related templates listed in documentation section. This template needs to be placed in the article before it can appear on google maps. [[User': 1,\n",
       "             'Someguy1221]] ([[User talk': 1,\n",
       "             'talk]]) 22:22, 2 June 2008 (UTC)': 1,\n",
       "             'Deproduction]] ([[User talk': 1,\n",
       "             '<font STYLE': 1,\n",
       "             'Talk]] or [[Special': 1,\n",
       "             'C]]</sup></b> 19': 1,\n",
       "             '70.59.16.211]] ([[User talk': 1,\n",
       "             'talk]]) 16:36, 3 June 2008 (UTC)': 1,\n",
       "             'Mark J Richards]] ([[User talk': 1,\n",
       "             'talk]]) 09': 3,\n",
       "             \"'''<font color\": 1,\n",
       "             '<small><sub><font color': 1,\n",
       "             '<small><sup><font color': 1,\n",
       "             'Aozukum]] ([[User talk': 1,\n",
       "             'this guide]]. It has a lot of tips for getting started. You may also want to look at this [[WP': 1,\n",
       "             'notability guideline]] for biographies. I have also left a message on [[User talk': 1,\n",
       "             'your talk page]] with some useful links. Good luck and feel free to ask questions! <b><font color': 1,\n",
       "             'TN]]</font>‑<font color': 1,\n",
       "             'X]]</font>-<font color': 1,\n",
       "             'Man]]</font></b> 13:22, 3 June 2008 (UTC)': 1,\n",
       "             'Gaurdians1]] ([[User talk': 1,\n",
       "             'talk]]) 20': 1,\n",
       "             \"internal links]] created by putting double brackets - [[ ]] -  around the name of the article you would like to link to.  For example, if I want to link to an article about mice, I would type <nowiki>[[Mice]]</nowiki>, and it would show up as [[Mice]].  If you try to link to an article that doesn't exist, it will show up as a [[Wikipedia\": 1,\n",
       "             'red link]], [[like this one]]. -- [[User': 1,\n",
       "             'Nataly<font color': 1,\n",
       "             'reference desks]]. [[User': 1,\n",
       "             'ukexpat]] ([[User talk': 1,\n",
       "             'talk]]) 13:08, 4 June 2008 (UTC)': 1,\n",
       "             'Sibyllenoras]] ([[User talk': 1,\n",
       "             \"to this article]]? It looks like it's available. If you have any other questions, let me know. Cheers! <b><font color\": 1,\n",
       "             'Man]]</font></b> 13': 1,\n",
       "             'Confusing Manifestation]]<small>([[User talk': 1,\n",
       "             'Say hi!]])</small> 23:24, 4 June 2008 (UTC)': 1,\n",
       "             '202.67.4.3]] ([[User talk': 1,\n",
       "             'talk]]) 06:44, 4 June 2008 (UTC)': 1,\n",
       "             'Coordinator]]\\n* Advertisements:  <span id': 1,\n",
       "             'Questions]] <span id': 1,\n",
       "             'Style (articles)]] <span id': 1,\n",
       "             'Quality of articles]]\\n**Assessment department': 1,\n",
       "             'Questions]]) <span id': 1,\n",
       "             'Templates]] (in general) and specific topics as well (articles, talk pages, maintenance, etc.)\\n* Barnstars': 1,\n",
       "             'Awards]]\\n* Biographies: <span id': 1,\n",
       "             'Series boxes]]) \\n***[[Wikipedia': 1,\n",
       "             'Resources]]) <span id': 1,\n",
       "             'User scripts]]) <span id': 1,\n",
       "             'numbers': 1,\n",
       "             'sections': 1,\n",
       "             'quote': 4,\n",
       "             '\\u200bdisplay': 2,\n",
       "             'landmark_scale': 1,\n",
       "             'line': 3,\n",
       "             'other': 1,\n",
       "             'platform': 1,\n",
       "             'tracks': 1,\n",
       "             'parking': 1,\n",
       "             'bicycle': 1,\n",
       "             'passengers': 1,\n",
       "             'pass_year': 1,\n",
       "             'pass_percent': 1,\n",
       "             'opened': 1,\n",
       "             'closed': 1,\n",
       "             'rebuilt': 1,\n",
       "             'ADA': 1,\n",
       "             'code': 1,\n",
       "             'owned': 1,\n",
       "             'zone': 1,\n",
       "             'other_services_header': 1,\n",
       "             'other_services': 1,\n",
       "             'locale': 2,\n",
       "             'Cross-reference 1909 Ordnance Survey map<ref name': 1,\n",
       "             'type:event_region': 1,\n",
       "             \"'' See here for further help'']]\\n* For administrative districts, use the location of the head office, or if that cannot be found, the approximate centre of the district.\": 1,\n",
       "             'overly precise]], but do ensure that the single point that the chosen pair of coordinates represents does fall on or within the feature.': 1,\n",
       "             'x,type': 1,\n",
       "             'improvedby': 2,\n",
       "             'landmaark_region': 1,\n",
       "             'mountain_region': 2,\n",
       "             'NS': 4,\n",
       "             'EW': 4,\n",
       "             'deg': 1,\n",
       "             'min': 1,\n",
       "             'sec': 1,\n",
       "             'hem': 1,\n",
       "             'rnd': 1,\n",
       "             \"the few pages that don't already have it]] and on which it belongs. --[[User talk\": 1,\n",
       "             'NE2]] 18': 1,\n",
       "             'Thomas Paine1776]] ([[User talk': 1,\n",
       "             'talk]]) 00': 1,\n",
       "             'nogoogle': 1,\n",
       "             'noosm': 1,\n",
       "             'Geographic coordinates]] and [[Template': 1,\n",
       "             \"biographical data]]. The framework is already there, but the problem with those is that there's no way to get the data efficiently. <span style\": 1,\n",
       "             'Mr.]][[User talk': 2,\n",
       "             \"'''''Z-'''man'']]</span> 18\": 1,\n",
       "             'type;Landmark_region': 1,\n",
       "             'GNS]] coordinates adjusted using a Ministério dos Transportes [http': 2,\n",
       "             'width': 1,\n",
       "             'Year\\n! style': 1,\n",
       "             'Winner\\n! style': 1,\n",
       "             '}': 1,\n",
       "             'GNS]] coordinates adjusted using [[Google Maps]], [http': 3,\n",
       "             'bot': 1,\n",
       "             'fix-attempted': 1,\n",
       "             'services': 1,\n",
       "             'membership': 1,\n",
       "             'type:mountain_elevation:437_region': 1,\n",
       "             'region:US},}<ref>Topographical Map of Mahaska County, Iowa, [http://digital.lib.uiowa.edu/u?/atlases,48 Huebinger\\'s Atlas of the State of Iowa], Iowa Publishing Co., 1904</ref> and it quickly developed as one of the most prosperous and largest coal camps in Iowa.<ref>\"Iowa\\'s Pioneer Coal Operators,\" [https://books.google.com/books?id': 1,\n",
       "             \"passenger car]].  It left [[Staunton, Virginia]] on May 12 and traveled via Chicago and [[Marshalltown, Iowa]], arriving in Muchakinock on May 15.  Rail fare from Virginia to Iowa was $12, which the company paid and took as an advance against each miner's monthly wages.<ref name\": 1,\n",
       "             'adj': 1,\n",
       "             'pmc': 1,\n",
       "             'pmid': 1,\n",
       "             'E\\n[[Template': 1,\n",
       "             'lat_deg': 1,\n",
       "             'lat_min': 1,\n",
       "             'lon_deg': 1,\n",
       "             'lon_min': 1,\n",
       "             'type:landmark_region': 1,\n",
       "             'coord]] [[Wikipedia': 1,\n",
       "             'geotagging links]] already provide this kind of information is a more systematic and useful way, which also [[Template': 1,\n",
       "             'does not privilege]] one company over others.': 1,\n",
       "             'HaeB]] ([[User talk': 1,\n",
       "             'talk]]) 21': 1,\n",
       "             'WhatamIdoing]] ([[User talk': 1,\n",
       "             'talk]]) 22': 1,\n",
       "             'Richard McCoy]] ([[User talk': 1,\n",
       "             'talk]]) 12:41, 25 February 2010 (UTC)': 1,\n",
       "             'External links]]\" section with a handful of links (four? five?) to the official websites of the most prominent nontrinitarian groups, or directly to the groups\\' own explanation of the matter.<br />--[[User': 1,\n",
       "             'AuthorityTam]] ([[User talk': 1,\n",
       "             'talk]]) 23': 3,\n",
       "             'talk]]) 00:26, 27 February 2010 (UTC)': 1,\n",
       "             '{{safesubst': 3,\n",
       "             '{{<includeonly>safesubst': 1,\n",
       "             'diff': 1,\n",
       "             'box': 1,\n",
       "             'added': 1,\n",
       "             'changed': 1,\n",
       "             'deleted': 1,\n",
       "             'urls': 1,\n",
       "             'isIP': 1,\n",
       "             'function': 1,\n",
       "             'whitelisted': 1,\n",
       "             'timestamp': 1,\n",
       "             'region_IN_type': 1,\n",
       "             \"locations]], etc, and we currently don't expect perfection from new editors. Article development is iterative\": 1,\n",
       "             '<b style': 1,\n",
       "             'Rd232]] <sup>[[user talk': 1,\n",
       "             'talk]]</sup> 19': 1,\n",
       "             'Hans]] [[User talk': 1,\n",
       "             'Adler]] 14': 1,\n",
       "             '<joke>(also; we were first': 1,\n",
       "             'NOTE_4_IMAGE': 1,\n",
       "             'NOTE_4_FORMAT': 1,\n",
       "             'NOTE_4_CAT': 1,\n",
       "             'alt': 2,\n",
       "             'here]]. <span style': 1,\n",
       "             \"'''''Z-'''man'']]</span> 05:55, 26 April 2011 (UTC)\": 1,\n",
       "             'Mahitgar]] ([[User talk': 1,\n",
       "             'talk]]) 15': 3,\n",
       "             'Genicoord]]<br/>\\n[[User': 1,\n",
       "             'Genidealingwithalbumcovers]]<br/>\\n[[User': 1,\n",
       "             'Genidealingwithfairuse]]<br/>\\n[[User': 1,\n",
       "             'Geniice]]<br/>\\n[[User': 1,\n",
       "             'Genisock]]<br/>\\n[[User': 1,\n",
       "             'Genisock2]]<br/>\\n[[User': 1,\n",
       "             'Genisockrating]]<br/>\\n[[User': 1,\n",
       "             'Genisocky]]<br/>\\n[[User': 1,\n",
       "             \"It's Character Forming]]<br/>\\n[[User\": 1,\n",
       "             'Liveware problem]]<br/>\\n[[User': 1,\n",
       "             'UK voteing account]]<br/>\\n[[User': 1,\n",
       "             '[[Wikipedia': 3,\n",
       "             'December 2004]]<br/>\\n<s>[[Wikipedia': 1,\n",
       "             'March 2008]]</s><br/>\\n<s>[[Wikipedia': 1,\n",
       "             'August 2008]]</s><br/>\\n[[Wikipedia': 1,\n",
       "             'sourtype': 1,\n",
       "             'country': 2,\n",
       "             'operator': 2,\n",
       "             'cause': 2,\n",
       "             'trains': 2,\n",
       "             'pax': 2,\n",
       "             'deaths': 2,\n",
       "             'region_': 1,\n",
       "             'oclc': 1,\n",
       "             '1': 2,\n",
       "             '8': 2,\n",
       "             'Coordinates Template]]).': 1,\n",
       "             'Wikipedia': 1,\n",
       "             'label 1': 1,\n",
       "             '.': 1,\n",
       "             'Template': 1,\n",
       "             'RU_type': 1,\n",
       "             'It is proposed]] that they will each serve as the primary lead coordinator for a four month period, with AustralianRupert taking the first four months followed by Nick and Dank. The other coordinators for the year commencing 1 October are': 1,\n",
       "             'Anotherclown]], [[User': 1,\n",
       "             'Cplakidas]], [[User': 1,\n",
       "             'Grandiose]], [[User': 1,\n",
       "             'Hawkeye7]], [[User': 1,\n",
       "             'HJ Mitchell]], [[User': 1,\n",
       "             'Ian Rose]], [[User': 1,\n",
       "             'MarcusBritish]], [[User': 1,\n",
       "             'Nikkimaria]], [[User': 1,\n",
       "             'The ed17]] and [[User': 1,\n",
       "             'TomStar81]]. In addition, [[User': 1,\n",
       "             'Arius1998]], [[User': 1,\n",
       "             'Johnsc12]], [[User': 1,\n",
       "             'Knight of Gloucestershire]] and [[User': 1,\n",
       "             'RoslynSKP]] also stood for election, but were not successful. The next coordinator election will be held in September 2013. \\n\\nComments on the election are very welcome, and should be made [[Wikipedia talk': 1,\n",
       "             'here]].\\n\\nYour editors, [[User': 1,\n",
       "             'Nick-D]] ([[User talk': 1,\n",
       "             'talk]]) and [[User': 1,\n",
       "             'Ed]]&nbsp;<sup>[[User talk': 1,\n",
       "             '[talk]]] [[WP': 1,\n",
       "             \"N''? I haven't added that but I can add it this weekend. Or Friday, hopefully. I'll update the function overview at that time. --[[User\": 1,\n",
       "             'Alan]]<sup>[[User talk': 1,\n",
       "             '(T)]][[Special': 1,\n",
       "             '(E)]]</sup> 03:34, 4 April 2013 (UTC)\\n:::Yes; thank you. <span class': 1,\n",
       "             'Andy Mabbett]]</span> (<span class': 2,\n",
       "             'Talk to Andy]]; [[Special': 2,\n",
       "             \"Andy's edits]]</span> 09\": 1,\n",
       "             'type{landmark_region': 1,\n",
       "             'typerailway_station_region': 7,\n",
       "             '\" does not show up in the included templates? --[[User': 1,\n",
       "             'ThurnerRupert]] ([[User talk': 1,\n",
       "             \"talk]]) 10:31, 25 August 2013 (UTC)\\n:::Sorry, I'm not sure what you mean. <span class\": 1,\n",
       "             \"Andy's edits]]</span> 20\": 1,\n",
       "             'talk]]) 01:54, 26 August 2013 (UTC)': 1,\n",
       "             'talk]]) 07': 1,\n",
       "             '<span style': 2,\n",
       "             '♪ talk ♪]]</sup> 09': 1,\n",
       "             'talk]]) 10': 1,\n",
       "             'table]] to the article instead. If you look at [[Special': 1,\n",
       "             'region:CA-ON_source:<ref name': 1,\n",
       "             '<!-- location of Bangui -->display': 1,\n",
       "             '{{#property': 5,\n",
       "             'Wtype': 1,\n",
       "             'ype': 3,\n",
       "             'region_US-WA_type': 1,\n",
       "             'island': 1,\n",
       "             'regio': 2,\n",
       "             'Genicoord]], [[User': 1,\n",
       "             'YetanotherGenisock]], [[User': 1,\n",
       "             'Genidealingwithfairuse]], [[User': 1,\n",
       "             'Liveware problem]], [[User': 1,\n",
       "             \"It's Character Forming]], [[User\": 1,\n",
       "             'UK voteing account]], [[User': 1,\n",
       "             'Geniice]]<br/><small>several punctuation accounts to push abusive names off the first page of [[Special': 1,\n",
       "             'December 2004 (A)]]<br/>\\n<s>[[Wikipedia': 1,\n",
       "             'September&nbsp;2006&nbsp;(B)]]</s><br/>\\n<s>[[Wikipedia': 1,\n",
       "             'March 2008 (A)]]</s><br/>\\n<s>[[Wikipedia': 1,\n",
       "             'August 2008 (A)]]</s><br/>\\n[[Wikipedia': 1,\n",
       "             'region:US-WV_scale:10000_source:placeopedia:display': 1,\n",
       "             'region-iso': 1,\n",
       "             'conference': 1,\n",
       "             'conference-url': 1,\n",
       "             'reg': 4,\n",
       "             'tye': 1,\n",
       "             'range_coordinates': 1,\n",
       "             'nopp': 1,\n",
       "             'last5': 3,\n",
       "             'first5': 3,\n",
       "             'last6': 3,\n",
       "             'first6': 3,\n",
       "             'last7': 3,\n",
       "             'first7': 3,\n",
       "             'scales': 1,\n",
       "             'soutype': 1,\n",
       "             'Avadi': 1,\n",
       "             'E<!--- FIXME': 1,\n",
       "             'region-ISO': 2,\n",
       "             'coordinates link]] (look for a globe icon at the top right corner of an article). Here is the link produced for the article [[United States]]: [https://tools.wmflabs.org/geohack/geohack.php?pagename': 1,\n",
       "             'Finnusertop]]</span> ([[User talk': 1,\n",
       "             'talk]] ⋅ [[Special': 1,\n",
       "             'contribs]]) 06:51, 10 March 2016 (UTC)': 1,\n",
       "             'Famous and Other Notable Maltipoos]]\" got added. Now if the dogs listed there had been in something like [[Old Yeller]] or [[Where the Red Fern Grows]] I\\'d think they deserved to be included, because they\\'d be notable. But they\\'re just dogs that had Facebook and YouTube pages made for them by their owners, and the info is sourced off those social media pages. I removed this stuff once already and an IP added it back with the comment, \"this editor’s [me] work reflects their bias\". Should I remove it again? [[User': 1,\n",
       "             'from': 4,\n",
       "             \"'''''template parameters'''''}&#125;'''\\n\\n*'''<code>wikidata page name</code>''' – unnamed positional parameter is the Wikidata page name (starting with Q). Default is the current page.\\n*'''<code>coordinate parameters</code>''' – passed through to Template\": 1,\n",
       "             'Usage]] at Template': 1,\n",
       "             'W<!--- FIXME': 1,\n",
       "             'fetch': 2,\n",
       "             'disply': 1,\n",
       "             'city_region': 1,\n",
       "             'vn': 2,\n",
       "             'on 1,000,000+ other pages]].)\\n* Editors wanting to \\'\\'remove\\'\\' the coordinates appear not to have cited any Wikipedia policies at all that would exclude the coordinates.\\nEven the closing statement says that there is a policy that would have the coordinates included in the article (even when an external organization wants them removed) but does not mention any policy that would exclude them.\\n\\nThe result of \"no consensus\" is not appropriate because it applies equal weighting to opinions that have no basis in policy, whereas those opinions should have been discarded; only those opinions based on policy should have been considered. I submit that result of the RFC should have been to \\'\\'include\\'\\' the coordinates because there are several policies and guidelines that say we should include them and explicitly say that we will not remove them at the request of an external organization. There are no policies that would exclude the coordinates from the article. [[User': 2,\n",
       "             'Mitch Ames]] ([[User talk': 2,\n",
       "             'talk]]) 08': 2,\n",
       "             'Only in death does duty end]] ([[User talk': 2,\n",
       "             'Mitch Ames]] that policy reasons were presented, and that they are aware of it. It is their choice to come here and state otherwise. Diffs: [//en.wikipedia.org/w/index.php?title': 2,\n",
       "             'Nabla]] ([[User talk': 2,\n",
       "             'talk]]) 09:52, 6 December 2016 (UTC)\\n::I\\'m not asserting that the \"removers\" did not present any policy, I\\'m asserting that the removers did not present any policy \\'\\'that would exclude the coordinates from the article\\'\\'. [https://en.wikipedia.org/w/index.php?title': 2,\n",
       "             'Hobit]] ([[User talk': 2,\n",
       "             'talk]]) 14': 2,\n",
       "             'Nyttend]] ([[User talk': 2,\n",
       "             'Mr]][[user talk': 2,\n",
       "             'X]] 16': 2,\n",
       "             'type_event_region': 1,\n",
       "             'NRHPcoord]]\" or \"[[template': 1,\n",
       "             'Nrhpcoord]]\" or \"[[template': 1,\n",
       "             'nrhpcoord]]\" or \"[[template': 1,\n",
       "             'NRHPCOORD]]\" or \"[[template': 1,\n",
       "             'source1': 1,\n",
       "             'source2': 1,\n",
       "             'userdate': 1,\n",
       "             'user': 1,\n",
       "             'user-visited-site': 1,\n",
       "             'usernote': 1,\n",
       "             'type:landmark_region:FR_pop: _elevation:295_dim:1000_mapsize': 1,\n",
       "             'vtype': 1,\n",
       "             'pp': 1,\n",
       "             'region_IN': 5,\n",
       "             'qid': 2,\n",
       "             'type_river_region': 1,\n",
       "             '{{#titleparts': 1,\n",
       "             'verb': 1,\n",
       "             'float': 1,\n",
       "             'nocat': 1,\n",
       "             'E are invalid. [[User': 1,\n",
       "             'Graeme Bartlett]] ([[User talk': 1,\n",
       "             'talk]]) 11:58, 26 January 2018 (UTC)': 1,\n",
       "             'soure': 1,\n",
       "             'regior': 2,\n",
       "             'soucre': 1,\n",
       "             'version': 2,\n",
       "             'Display': 1,\n",
       "             'diplay': 1,\n",
       "             'link': 1,\n",
       "             'dms': 1,\n",
       "             'disp': 1})"
      ]
     },
     "execution_count": 43,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "from collections import defaultdict\n",
    "keywords = defaultdict(int)\n",
    "for coordString in coordStrings.values():\n",
    "    for kw in getKeywords(coordString).keys():\n",
    "        keywords[kw.strip()] += 1\n",
    "keywords"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{'3',\n",
       " 'GNS]] coordinates adjusted using [[Google Maps]] and [http',\n",
       " 'GNS]] coordinates adjusted using [[Google Maps]], and [http',\n",
       " \"The ''globe''&nbsp;[[File\",\n",
       " '_source',\n",
       " 'access-date',\n",
       " 'accessdate',\n",
       " 'author',\n",
       " 'date',\n",
       " 'dim',\n",
       " 'display',\n",
       " 'entrydate',\n",
       " 'first',\n",
       " 'format',\n",
       " 'globe',\n",
       " 'id',\n",
       " 'last',\n",
       " 'name',\n",
       " 'notes',\n",
       " 'p',\n",
       " 'pages',\n",
       " 'publisher',\n",
       " 'region',\n",
       " 'scale',\n",
       " 'source',\n",
       " 'title',\n",
       " 'type',\n",
       " 'upright',\n",
       " 'url',\n",
       " 'website',\n",
       " 'work',\n",
       " 'year',\n",
       " '{{#expr'}"
      ]
     },
     "execution_count": 44,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "{kw for kw,count in keywords.items() if count > 10}"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Most of these look good except for a few that are duplicates or a weird artifact from the imperfect string processing. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "accessdate TEXT,\n",
      "date TEXT,\n",
      "dim TEXT,\n",
      "display TEXT,\n",
      "elevation TEXT,\n",
      "format TEXT,\n",
      "globe TEXT,\n",
      "id TEXT,\n",
      "name TEXT,\n",
      "nosave TEXT,\n",
      "notes TEXT,\n",
      "publisher TEXT,\n",
      "reason TEXT,\n",
      "region TEXT,\n",
      "scale TEXT,\n",
      "source TEXT,\n",
      "title TEXT,\n",
      "type TEXT,\n",
      "upright TEXT,\n",
      "url TEXT,\n",
      "work TEXT,\n"
     ]
    }
   ],
   "source": [
    "entriesList = [\n",
    " 'accessdate',\n",
    " 'date',\n",
    " 'dim',\n",
    " 'display',\n",
    " 'elevation',\n",
    " 'format',\n",
    " 'globe',\n",
    " 'id',\n",
    " 'name',\n",
    " 'nosave',\n",
    " 'notes',\n",
    " 'publisher',\n",
    " 'reason',\n",
    " 'region',\n",
    " 'scale',\n",
    " 'source',\n",
    " 'title',\n",
    " 'type',\n",
    " 'upright',\n",
    " 'url',\n",
    " 'work']\n",
    "for e in entriesList:\n",
    "    print(e, 'TEXT,')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "It would be nice to see examples of values for the keywords so we can see how they're used."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 46,
   "metadata": {},
   "outputs": [],
   "source": [
    "from numpy.random import shuffle\n",
    "def findKeywordExample(kw, count=1, debug=False):\n",
    "    kws = []\n",
    "    coordStringsList = list(coordStrings.values())\n",
    "    shuffle(coordStringsList)\n",
    "    for cs in coordStringsList:\n",
    "        if f'|{kw}=' in cs:\n",
    "            kws.append(cs.split(f'|{kw}=')[1].split('|')[0])\n",
    "            if debug:\n",
    "                print(cs)\n",
    "        elif f'|{kw}:' in cs:\n",
    "            kws.append(cs.split(f'|{kw}:')[1].split('|')[0])\n",
    "            if debug:\n",
    "                print(cs)\n",
    "        if len(kws) >= count:\n",
    "            break\n",
    "    return kws"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "coord|42.440556|-98.148083|type:landmark|name=Ashfall Fossil Beds\n",
      "coord|42|37|37|N|85|1|28|W|name=Vermontville Opera House|region:US_type:landmark\n",
      "coord|76|5|30|N|109|41|59|W|region:CA-NU_type:waterbody_scale:500000|display=inline, title|name=Eldridge Bay\n",
      "coord|-34.42928|172.681582|format=dms|name=Start of SH 1N|type:landmark_region:NZ|display=inline\n",
      "coord|60.872|-69.323|display=inline,title|name=Eider Islands\n",
      "coord|55.3057|N|117.8961|W|name=Amerada Crown GF23-11|display=inline,title\n",
      "coord|75|15|N|105|00|W|region:CA-NU_type:waterbody |display=inline,title|name=Byam Channel\n",
      "coord|53.59526|-2.50612|type:landmark|name=Blundell Arms Public House\n",
      "coord|48|07|49.22|N|07|21|22.32|E|type:city|name=Bennwihr Gare\n",
      "Coord|32.1276|N|110.4317|W|type:mountain_region:US|name=Forest Hill (peak)\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "['Ashfall Fossil Beds',\n",
       " 'Vermontville Opera House',\n",
       " 'Eldridge Bay',\n",
       " 'Start of SH 1N',\n",
       " 'Eider Islands',\n",
       " 'Amerada Crown GF23-11',\n",
       " 'Byam Channel',\n",
       " 'Blundell Arms Public House',\n",
       " 'Bennwihr Gare',\n",
       " 'Forest Hill (peak)']"
      ]
     },
     "execution_count": 47,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "findKeywordExample('name', 10, True)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now we can create all the columns en masse."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 50,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<sqlite3.Cursor at 0x296b03e0810>"
      ]
     },
     "execution_count": 50,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "c.execute('''CREATE TABLE coords\n",
    "    (coords TEXT,\n",
    "    lat REAL DEFAULT 0,\n",
    "    lon REAL DEFAULT 0,\n",
    "    page_num INTEGER PRIMARY KEY,\n",
    "    accessdate TEXT DEFAULT '',\n",
    "    date TEXT DEFAULT '',\n",
    "    dim TEXT DEFAULT '',\n",
    "    display TEXT DEFAULT '',\n",
    "    elevation TEXT DEFAULT '',\n",
    "    format TEXT DEFAULT '',\n",
    "    globe TEXT DEFAULT '',\n",
    "    id TEXT DEFAULT '',\n",
    "    name TEXT DEFAULT '',\n",
    "    nosave TEXT DEFAULT '',\n",
    "    notes TEXT DEFAULT '',\n",
    "    publisher TEXT DEFAULT '',\n",
    "    reason TEXT DEFAULT '',\n",
    "    region TEXT DEFAULT '',\n",
    "    scale TEXT DEFAULT '',\n",
    "    source TEXT DEFAULT '',\n",
    "    title TEXT DEFAULT '',\n",
    "    type TEXT DEFAULT '',\n",
    "    upright TEXT DEFAULT '',\n",
    "    url TEXT DEFAULT '',\n",
    "    work TEXT DEFAULT '')\n",
    "''')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 51,
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "# c.execute('''DROP TABLE coords''')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 59,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "['41.4347', 'N', '25.1328', 'E']\n",
      "['48.860', '2.327']\n",
      "['50.82', '-1.45']\n",
      "['49.49', '0.10']\n",
      "['49', '26', '02', 'N', '0', '12', '24', 'E']\n",
      "['47', '31', 'N', '11', '09', 'E']\n",
      "['51', '30', '44', 'N', '00', '09', '48', 'W']\n",
      "['51.4988', '-0.0901']\n",
      "['39', '10', 'N', '26', '20', 'E']\n",
      "[]\n"
     ]
    }
   ],
   "source": [
    "def extract_lat_lon(coord_string):\n",
    "    split = coord_string.split('|')\n",
    "    coord_list = []\n",
    "    for s in split:\n",
    "        if ':' in s or '=' in s:\n",
    "            break\n",
    "        if 'Coord' not in s and 'LAT' not in s and 'LONG' not in s and 'coord' not in s:\n",
    "            coord_list.append(s)  \n",
    "    return coord_list\n",
    "\n",
    "begin = random.randint(0, len(coordStrings)-10)\n",
    "for i in range(begin, begin+10):\n",
    "    print(extract_lat_lon(list(coordStrings.values())[i]))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 60,
   "metadata": {},
   "outputs": [],
   "source": [
    "def convert_to_decimal(coord_list):\n",
    "#     print(' '.join(coord_list), end='\\t')\n",
    "    coord_list = [s.strip().lower() for s in coord_list if s.strip() != '']\n",
    "    if len(coord_list) < 2:\n",
    "        return [0, 0]\n",
    "    if len(coord_list) == 2:\n",
    "        return [float(coord_list[0]), float(coord_list[1])]\n",
    "    directions = 0\n",
    "    for s in coord_list:\n",
    "        s = s.strip().lower()\n",
    "        if s and s.strip() in 'nesw':\n",
    "            directions += 1\n",
    "    if directions != 2:\n",
    "        raise Exception(directions, \"wrong number of directions for:\", coord_list)\n",
    "        \n",
    "    lat = []\n",
    "    lon = []\n",
    "    creating_lat = True\n",
    "    for s in coord_list:\n",
    "        s = s.strip().lower()\n",
    "        if s == '':\n",
    "            continue\n",
    "        if creating_lat:\n",
    "            if s in 'ns':\n",
    "                creating_lat = False\n",
    "                while len(lat) < 3:\n",
    "                    lat.append(0)\n",
    "                if s == 'n':\n",
    "                    lat.append(1)\n",
    "                else:\n",
    "                    lat.append(-1)\n",
    "            else:\n",
    "                if ',' in s:\n",
    "                    s = s.replace(',', '.')\n",
    "                lat.append(float(s))\n",
    "        else:\n",
    "            if s in 'ew':\n",
    "                while len(lon) < 3:\n",
    "                    lon.append(0)\n",
    "                if s == 'e':\n",
    "                    lon.append(1)\n",
    "                else:\n",
    "                    lon.append(-1)\n",
    "            else:\n",
    "                if ',' in s:\n",
    "                    s = s.replace(',', '.')\n",
    "                lon.append(float(s))\n",
    "    return [\n",
    "        (lat[0] + lat[1]/60 + lat[2]/3600) * lat[3],\n",
    "        (lon[0] + lon[1]/60 + lon[2]/3600) * lon[3]\n",
    "    ]\n",
    "\n",
    "count = 1000\n",
    "begin = random.randint(0, len(coordStrings)-count)\n",
    "for i in range(begin, begin+count):\n",
    "    coord_list = extract_lat_lon(list(coordStrings.values())[i])\n",
    "    try:\n",
    "        result = convert_to_decimal(coord_list)\n",
    "    except Exception as e:\n",
    "        print(coord_list, e)\n",
    "#     print(result)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 61,
   "metadata": {},
   "outputs": [],
   "source": [
    "def insertKeywordDict(page_num, coords, kws, debug=False):\n",
    "    allowed_keys = [kw for kw in kws.keys() if kw in entriesList]\n",
    "    allowed_vals = [kws[kw] for kw in allowed_keys]\n",
    "    \n",
    "    try:\n",
    "        lat,lon = convert_to_decimal(extract_lat_lon(coords))\n",
    "    except:\n",
    "        lat = lon = 0\n",
    "    keys = ['coords', 'lat', 'lon', 'page_num'] + allowed_keys\n",
    "    vals = [coords, lat, lon, page_num] + allowed_vals\n",
    "    \n",
    "    qstring = f'INSERT INTO coords (\\\n",
    "        {\",\".join(keys)}) VALUES (\\\n",
    "        {\",\".join([\"?\" for item in vals])})'\n",
    "    \n",
    "    if debug:\n",
    "        print(qstring, vals)\n",
    "    c.execute(qstring, vals)\n",
    "# insertKeywordDict({'region': 'US-AK_type', 'display': 'title', 'notes': '<ref>{{Cite gnis'})"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Grab a drink before running the next cell, it'll take a while."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 71,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "99.99 (1152300) succeeded: 212499\r"
     ]
    }
   ],
   "source": [
    "start = 100_000\n",
    "succeeded = 0\n",
    "coordStringsList = list(coordStrings.items())\n",
    "for i in range(start, len(coordStringsList)):\n",
    "    page_idx,coordString = coordStringsList[i]\n",
    "    try:\n",
    "        keywords = getKeywords(coordString)\n",
    "        keywords['title'] = idx_to_title[page_idx]\n",
    "        insertKeywordDict(page_idx, coordString, keywords)\n",
    "        succeeded += 1\n",
    "    except sqlite3.IntegrityError:\n",
    "        pass\n",
    "    if i % 100 ==  0:\n",
    "        english.db.commit()\n",
    "        print(f'{round(100*i/len(coordStrings), 2)} ({i}) succeeded: {succeeded}', end='\\r')\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 72,
   "metadata": {},
   "outputs": [],
   "source": [
    "# c.execute('''SELECT * FROM coords WHERE page_num > 19000000''').fetchall()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 73,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[('Museum of Pop Culture',),\n",
       " ('Bill & Melinda Gates Foundation',),\n",
       " ('The Art Institute of Seattle',),\n",
       " ('Space Needle',),\n",
       " ('School of Visual Concepts',),\n",
       " ('Fremont, Seattle',),\n",
       " ('City University of Seattle',),\n",
       " ('Woodland Park Zoo',),\n",
       " ('Belltown, Seattle',),\n",
       " ('Fremont Troll',),\n",
       " ('Cranium, Inc.',),\n",
       " ('Seattle Aquarium',),\n",
       " ('The Real World: Seattle',),\n",
       " ('Fremont Bridge (Seattle)',),\n",
       " ('Aurora Bridge',),\n",
       " ('Attachmate',),\n",
       " ('Woodland Park (Seattle)',),\n",
       " ('Seattle Opera',),\n",
       " ('Denny Park (Seattle)',),\n",
       " ('Victor Steinbrueck Park',),\n",
       " ('Unexpected Productions',),\n",
       " ('Memorial Stadium (Seattle)',),\n",
       " ('Bad Animals Studio',),\n",
       " ('Westlake, Seattle',),\n",
       " ('B. F. Day Elementary School',),\n",
       " ('Cutter & Buck',),\n",
       " ('Mercer Arena',),\n",
       " ('Seattle Cinerama',),\n",
       " ('Area code 206',),\n",
       " ('Waiting for the Interurban',),\n",
       " ('2006 Seattle Jewish Federation shooting',),\n",
       " (\"Beth's Cafe\",),\n",
       " ('Pike Place Fish Market',),\n",
       " ('SkyCity',),\n",
       " ('Antioch University Seattle',),\n",
       " ('Moore Theatre',),\n",
       " ('McLeod Residence',),\n",
       " ('Flight to Mars (ride)',),\n",
       " ('Fourth and Blanchard Building',),\n",
       " ('Seattle Parks and Recreation',),\n",
       " ('The Crocodile',),\n",
       " ('American Seafoods',),\n",
       " ('InterConnection.org',),\n",
       " ('Green Lake Aqua Theater',),\n",
       " ('Pike Place Market',),\n",
       " ('A Place for Mom',),\n",
       " ('Waterfront Park (Seattle)',),\n",
       " ('Alaskan Way Viaduct replacement tunnel',),\n",
       " ('History House of Greater Seattle',),\n",
       " ('Gum Wall',),\n",
       " ('Alaska Trade Building',),\n",
       " ('Butterworth Building',),\n",
       " ('Swedish Cultural Center',),\n",
       " ('TheFilmSchool',),\n",
       " ('Eastern Congo Initiative',),\n",
       " ('The 5 Point Cafe',),\n",
       " ('Institute for Health Metrics and Evaluation',),\n",
       " ('Impinj',),\n",
       " ('Insignia Towers',),\n",
       " ('Bell Apartments',),\n",
       " ('Griffin College',),\n",
       " ('Lundeberg Derby Monument',),\n",
       " ('Wagner Houseboat',),\n",
       " ('Tilikum Place',),\n",
       " ('Seattle Great Wheel',),\n",
       " ('Via6 Towers',),\n",
       " ('Canlis',),\n",
       " ('Northwest Woodworkers Gallery',),\n",
       " ('Original Starbucks',),\n",
       " ('Pier 57 (Seattle)',),\n",
       " ('Fremont Brewing',),\n",
       " ('Bell Street Park',),\n",
       " ('Chief Seattle (sculpture)',),\n",
       " ('Arrivé',),\n",
       " ('Tower 12',),\n",
       " ('Barnes Building',),\n",
       " ('Calhoun Hotel',),\n",
       " ('Left Bank Books',),\n",
       " ('McGuire Apartments',),\n",
       " ('The Emerald (building)',),\n",
       " ('Pike Street',),\n",
       " ('Chris Cornell (Marra)',)]"
      ]
     },
     "execution_count": 73,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "l = c.execute('''SELECT title FROM coords WHERE \n",
    "         lat BETWEEN 47.6 AND 47.7\n",
    "        AND lon BETWEEN -122.35 AND -122.34\n",
    "    ''').fetchall()\n",
    "l"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "How many cities do we have?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 92,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "7924"
      ]
     },
     "execution_count": 92,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "l = c.execute('''SELECT title,coords FROM coords WHERE \n",
    "         type == \"city\"\n",
    "    ''').fetchall()\n",
    "len(l)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "What's the coverage for this tag?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 75,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[]"
      ]
     },
     "execution_count": 75,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "[el[0] for el in l if 'Seattle' in el[0]]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['Portland, Indiana', 'South Portland, Maine', 'Portland, Pennsylvania']"
      ]
     },
     "execution_count": 27,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "[el[0] for el in l if 'Portland' in el[0]]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['Sea Gate, Brooklyn']"
      ]
     },
     "execution_count": 29,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "[el[0] for el in l if 'Brooklyn' in el[0]]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Not great."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 98,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[('Seattle', '', 47.609722222222224, -122.33305555555555)]"
      ]
     },
     "execution_count": 98,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "l = c.execute('''SELECT title,type,lat,lon FROM coords''').fetchall()\n",
    "[el for el in l if 'Seattle' == el[0]]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "That's because Seattle isn't tagged as a city."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 99,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[('Portland, Oregon',\n",
       "  'city(568380)_region',\n",
       "  45.519999999999996,\n",
       "  -122.68194444444445)]"
      ]
     },
     "execution_count": 99,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "[el for el in l if \"Portland, Oregon\" == el[0]]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 101,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[('San Francisco', '', 37.78333333333333, -122.41666666666667)]"
      ]
     },
     "execution_count": 101,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "[el for el in l if \"San Francisco\" == el[0]]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 102,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "1152376"
      ]
     },
     "execution_count": 102,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(l)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Hmm, probably too many to do TF-IDF on all of them. Can we narrow it down?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 132,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "602882"
      ]
     },
     "execution_count": 132,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "l = c.execute('''SELECT title,coords FROM coords WHERE \n",
    "         display == \"inline,title\"\n",
    "    ''').fetchall()\n",
    "len(l)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 133,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[('Seattle', 'coord|47|36|35|N|122|19|59|W|region:US-WA|display=inline,title')]"
      ]
     },
     "execution_count": 133,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "[el for el in l if 'Seattle' == el[0]]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 135,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[('Portland, Oregon',\n",
       "  'coord|45|31|12|N|122|40|55|W|type:city(568380)_region:US-OR_source:gnis-1136645|display=inline,title')]"
      ]
     },
     "execution_count": 135,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "[el for el in l if 'Portland, Oregon' == el[0]]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "This narrows it down by ~50%, at least."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 136,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[('Andorra', 'coord|42|30|N|1|31|E|display=inline,title'),\n",
       " ('Atlantic Ocean',\n",
       "  'coord|0|N|25|W|region:ZZ_type:waterbody|display=inline,title'),\n",
       " ('Aegean Sea',\n",
       "  'coord|39|N|25|E|type:waterbody_dim:500000|display=inline,title'),\n",
       " ('Amsterdam', 'coord|52|22|N|4|54|E|region:NL|display=inline,title'),\n",
       " ('Anatolia', 'Coord|39|N|35|E|type:country|display=inline,title'),\n",
       " ('Aberdeenshire',\n",
       "  'coord|57|9|3.6|N|2|7|22.8|W|type:adm2nd_region:GB|format=dms|display=inline,title'),\n",
       " ('Azincourt', 'coord|50.46|2.13|format=dms|display=inline,title'),\n",
       " ('Geography of Azerbaijan',\n",
       "  'Coord|40|30|N|47|30|E|type:country_region:AZ|display=inline,title'),\n",
       " ('Adelaide',\n",
       "  'coord|34|55|44|S|138|36|4|E|type:city_region:AU-SA|display=inline,title'),\n",
       " ('Athens', 'coord|37|59|02.3|N|23|43|40.1|E|format=dms|display=inline,title'),\n",
       " ('Ames, Iowa',\n",
       "  'coord|42|02|05|N|93|37|12|W|region:US-IA|display=inline,title'),\n",
       " ('Abensberg', 'coord|48|48|N|11|51|E|format=dms|display=inline,title'),\n",
       " ('Park Güell',\n",
       "  'Coord|41|24|49|N|2|09|10|E|region:ES-CT_scale:5000|display=inline,title'),\n",
       " ('Andes', 'coord|32|S|70|W|type:mountain|display=inline,title'),\n",
       " ('Anazarbus', 'coord|37|15|50|N|35|54|20|E|display=inline,title'),\n",
       " ('Abbotsford House',\n",
       "  'coord|55|35|59|N|2|46|55|W|region:GB|display=inline,title'),\n",
       " ('Abydos, Egypt', 'coord|26|11|06|N|31|55|08|E|display=inline,title'),\n",
       " ('Acapulco', 'coord|16|51|49|N|99|52|57|W|region:MX|display=inline,title'),\n",
       " ('Aachen', 'coord|50|46|32|N|06|05|01|E|format=dms|display=inline,title'),\n",
       " ('Aga Khan I', 'coord|latitude|longitude|type:landmark|display=inline,title'),\n",
       " ('Aga Khan III',\n",
       "  'coord|latitude|longitude|type:landmark|display=inline,title'),\n",
       " ('Ahmad Shah Durrani', 'coord|31|37|10|N|65|42|25|E|display=inline,title'),\n",
       " ('Aberdeen',\n",
       "  'coord|57.15|-2.11|type:city_region:GB-ABE|display=inline,title'),\n",
       " ('Algiers',\n",
       "  'coord|36|45|14|N|3|3|32|E|region:DZ_type:city|display=inline,title'),\n",
       " ('Amathus',\n",
       "  'coord|34|42|45|N|33|08|30|E|type:city_region:CY|display=inline,title'),\n",
       " ('Amphipolis', 'coord|40|49|6|N|23|50|24|E|format=dms|display=inline,title'),\n",
       " ('Anah', 'coord|34|22|20|N|41|59|15|E|region:IQ|display=inline,title'),\n",
       " ('Andaman Islands',\n",
       "  'Coord|12|30|N|92|45|E|region:IN_type:isle|display=inline,title'),\n",
       " ('Astoria, Oregon',\n",
       "  'coord|46|11|20|N|123|49|16|W|type:city(10045)_region:US-OR_source:gnis-1117076|display=inline,title'),\n",
       " ('Abadan, Iran',\n",
       "  'coord|30|20|21|N|48|18|15|E|region:IR|display=inline,title'),\n",
       " ('Australian Capital Territory',\n",
       "  'coord|35|18|29|S|149|7|28|E|display=inline,title'),\n",
       " ('Austin, Texas', 'coord|30|16|N|97|44|W|region:US-TX|display=inline,title'),\n",
       " ('Acropolis of Athens',\n",
       "  'coord|37|58|15|N|23|43|34|E|type:landmark_region:GR_scale:2000|display=inline,title'),\n",
       " ('Acadia University',\n",
       "  'Coord|45|05|16|N|64|21|58|W|region:CA-NS_type:edu|display=inline,title'),\n",
       " ('Albion, Michigan',\n",
       "  'coord|42|14|48|N|84|45|12|W|region:US-MI|display=inline,title'),\n",
       " ('Abdul Rashid Dostum', 'Coord|LAT|LONG|display=inline,title'),\n",
       " ('Andhra Pradesh',\n",
       "  'coord|16.50|80.64|region:IN-AP_type:adm1st_dim:500000|display=inline,title'),\n",
       " ('Alicante',\n",
       "  'coord|38|20|43|N|0|28|59|W|region:ES_type:city|display=inline,title'),\n",
       " ('Aarau', 'coord|47|24|N|8|03|E|display=inline,title'),\n",
       " ('Canton of Aargau', 'coord|47|5|N|8|0|E|format=dms|display=inline,title'),\n",
       " ('Abadeh',\n",
       "  'coord|31|09|39|N|52|39|02|E|region:IR_type:city|display=inline,title'),\n",
       " ('Abakan', 'coord|53|43|N|91|28|E|display=inline,title'),\n",
       " ('Arc de Triomphe', 'coord|48.8738|2.2950|display=inline,title'),\n",
       " ('Aswan', 'coord|24|05|20|N|32|53|59|E|region:EG|display=inline,title'),\n",
       " ('Angus, Scotland', 'coord|56|40|N|2|55|W|display=inline,title|format=dms'),\n",
       " ('Abano Terme',\n",
       "  'coord|45|21|37|N|11|47|24|E|region:IT_type:city(19062)|display=inline,title'),\n",
       " ('Aeclanum', 'coord|41|3|14|N|15|0|40|E|display=inline,title'),\n",
       " ('Aegina', 'coord|37|45|N|23|26|E|format=dms|display=inline,title'),\n",
       " ('Ajaccio', 'coord|41.9267|8.7369|format=dms|display=inline,title'),\n",
       " ('Ajmer', 'coord|26.4499|N|74.6399|E|display=inline,title')]"
      ]
     },
     "execution_count": 136,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "l[:50]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "I could also open this dataset of cities that would narrow it down a lot further, but I would have to do a bunch of fuzzy name matching probably :("
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 112,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 116,
   "metadata": {},
   "outputs": [],
   "source": [
    "index_col = ['nn', 'name', 'name_ascii', \n",
    "        'other_names', 'lat', 'lon', 'a', 'b', 'c', 'd', 'e', 'f', 'g', 'h', 'i', 'j', 'k', 'region', 'date']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 117,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "19"
      ]
     },
     "execution_count": 117,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(index_col)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 120,
   "metadata": {},
   "outputs": [],
   "source": [
    "cities_df = pd.read_csv('cities15000.tsv', sep='\\t', header=None, names=index_col)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 126,
   "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>nn</th>\n",
       "      <th>name</th>\n",
       "      <th>alt_name</th>\n",
       "      <th>other_names</th>\n",
       "      <th>lat</th>\n",
       "      <th>lon</th>\n",
       "      <th>a</th>\n",
       "      <th>b</th>\n",
       "      <th>c</th>\n",
       "      <th>d</th>\n",
       "      <th>e</th>\n",
       "      <th>f</th>\n",
       "      <th>g</th>\n",
       "      <th>h</th>\n",
       "      <th>i</th>\n",
       "      <th>j</th>\n",
       "      <th>k</th>\n",
       "      <th>region</th>\n",
       "      <th>date</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>15964</th>\n",
       "      <td>1697175</td>\n",
       "      <td>Olongapo</td>\n",
       "      <td>Olongapo</td>\n",
       "      <td>Bandaraya Olongapo,Ciudad ti Olongapo,Dakbayan...</td>\n",
       "      <td>14.82917</td>\n",
       "      <td>120.28278</td>\n",
       "      <td>P</td>\n",
       "      <td>PPLA3</td>\n",
       "      <td>PH</td>\n",
       "      <td>NaN</td>\n",
       "      <td>03</td>\n",
       "      <td>64</td>\n",
       "      <td>037107000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>221178</td>\n",
       "      <td>NaN</td>\n",
       "      <td>11</td>\n",
       "      <td>Asia/Manila</td>\n",
       "      <td>2017-12-27</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4879</th>\n",
       "      <td>2881485</td>\n",
       "      <td>Landshut</td>\n",
       "      <td>Landshut</td>\n",
       "      <td>Gorad Landsgut,Landishuta,Landsgut,Landshuad,L...</td>\n",
       "      <td>48.52961</td>\n",
       "      <td>12.16179</td>\n",
       "      <td>P</td>\n",
       "      <td>PPLA2</td>\n",
       "      <td>DE</td>\n",
       "      <td>NaN</td>\n",
       "      <td>02</td>\n",
       "      <td>092</td>\n",
       "      <td>09261</td>\n",
       "      <td>09261000</td>\n",
       "      <td>60488</td>\n",
       "      <td>NaN</td>\n",
       "      <td>488</td>\n",
       "      <td>Europe/Berlin</td>\n",
       "      <td>2014-02-08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>298</th>\n",
       "      <td>3855974</td>\n",
       "      <td>Esquel</td>\n",
       "      <td>Esquel</td>\n",
       "      <td>EQS,Ehskel',Eskelis,Esquel,ai si ke er,ayskywl...</td>\n",
       "      <td>-42.91147</td>\n",
       "      <td>-71.31947</td>\n",
       "      <td>P</td>\n",
       "      <td>PPL</td>\n",
       "      <td>AR</td>\n",
       "      <td>NaN</td>\n",
       "      <td>04</td>\n",
       "      <td>26035</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>28486</td>\n",
       "      <td>NaN</td>\n",
       "      <td>577</td>\n",
       "      <td>America/Argentina/Catamarca</td>\n",
       "      <td>2015-04-22</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14950</th>\n",
       "      <td>1028434</td>\n",
       "      <td>Quelimane</td>\n",
       "      <td>Quelimane</td>\n",
       "      <td>Gorad Kelimaneh,Kelimane,Kelimanė,Quelimane,UE...</td>\n",
       "      <td>-17.87861</td>\n",
       "      <td>36.88833</td>\n",
       "      <td>P</td>\n",
       "      <td>PPLA</td>\n",
       "      <td>MZ</td>\n",
       "      <td>NaN</td>\n",
       "      <td>09</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>188964</td>\n",
       "      <td>NaN</td>\n",
       "      <td>9</td>\n",
       "      <td>Africa/Maputo</td>\n",
       "      <td>2012-01-19</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4007</th>\n",
       "      <td>3682108</td>\n",
       "      <td>Garzón</td>\n",
       "      <td>Garzon</td>\n",
       "      <td>GLJ,Garzon,Garzón</td>\n",
       "      <td>2.19593</td>\n",
       "      <td>-75.62777</td>\n",
       "      <td>P</td>\n",
       "      <td>PPL</td>\n",
       "      <td>CO</td>\n",
       "      <td>NaN</td>\n",
       "      <td>16</td>\n",
       "      <td>41298</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>29451</td>\n",
       "      <td>NaN</td>\n",
       "      <td>834</td>\n",
       "      <td>America/Bogota</td>\n",
       "      <td>2017-06-21</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3084</th>\n",
       "      <td>1529452</td>\n",
       "      <td>Hoxtolgay</td>\n",
       "      <td>Hoxtolgay</td>\n",
       "      <td>Chia-shih-t'o-lo-kai,Chia-shih-t’o-lo-kai,Hesh...</td>\n",
       "      <td>46.51872</td>\n",
       "      <td>86.00214</td>\n",
       "      <td>P</td>\n",
       "      <td>PPLA4</td>\n",
       "      <td>CN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>13</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>22000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>804</td>\n",
       "      <td>Asia/Urumqi</td>\n",
       "      <td>2013-06-04</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16582</th>\n",
       "      <td>756868</td>\n",
       "      <td>Tomaszów Lubelski</td>\n",
       "      <td>Tomaszow Lubelski</td>\n",
       "      <td>Liublino Tomasuvas,Liublino Tomašuvas,Tomashev...</td>\n",
       "      <td>50.44767</td>\n",
       "      <td>23.41616</td>\n",
       "      <td>P</td>\n",
       "      <td>PPLA2</td>\n",
       "      <td>PL</td>\n",
       "      <td>NaN</td>\n",
       "      <td>75</td>\n",
       "      <td>0618</td>\n",
       "      <td>061801</td>\n",
       "      <td>NaN</td>\n",
       "      <td>20261</td>\n",
       "      <td>275.0</td>\n",
       "      <td>274</td>\n",
       "      <td>Europe/Warsaw</td>\n",
       "      <td>2010-10-14</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10703</th>\n",
       "      <td>1269834</td>\n",
       "      <td>Ichalkaranji</td>\n",
       "      <td>Ichalkaranji</td>\n",
       "      <td>Icalkarandzi,Ichalkaranji,aychalkaranjy,aychl ...</td>\n",
       "      <td>16.69117</td>\n",
       "      <td>74.46054</td>\n",
       "      <td>P</td>\n",
       "      <td>PPL</td>\n",
       "      <td>IN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>16</td>\n",
       "      <td>530</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>274383</td>\n",
       "      <td>NaN</td>\n",
       "      <td>561</td>\n",
       "      <td>Asia/Kolkata</td>\n",
       "      <td>2014-10-13</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12517</th>\n",
       "      <td>248414</td>\n",
       "      <td>Kurayyimah</td>\n",
       "      <td>Kurayyimah</td>\n",
       "      <td>Kareime,Kereime,Kuraiyima,Kuraymah,Kurayyimah,...</td>\n",
       "      <td>32.27639</td>\n",
       "      <td>35.59938</td>\n",
       "      <td>P</td>\n",
       "      <td>PPL</td>\n",
       "      <td>JO</td>\n",
       "      <td>NaN</td>\n",
       "      <td>18</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>17837</td>\n",
       "      <td>NaN</td>\n",
       "      <td>-183</td>\n",
       "      <td>Asia/Amman</td>\n",
       "      <td>2016-05-07</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18765</th>\n",
       "      <td>65170</td>\n",
       "      <td>Baardheere</td>\n",
       "      <td>Baardheere</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2.34464</td>\n",
       "      <td>42.27644</td>\n",
       "      <td>P</td>\n",
       "      <td>PPLL</td>\n",
       "      <td>SO</td>\n",
       "      <td>NaN</td>\n",
       "      <td>06</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>42240</td>\n",
       "      <td>NaN</td>\n",
       "      <td>97</td>\n",
       "      <td>Africa/Mogadishu</td>\n",
       "      <td>2006-01-27</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "            nn               name           alt_name  \\\n",
       "15964  1697175           Olongapo           Olongapo   \n",
       "4879   2881485           Landshut           Landshut   \n",
       "298    3855974             Esquel             Esquel   \n",
       "14950  1028434          Quelimane          Quelimane   \n",
       "4007   3682108             Garzón             Garzon   \n",
       "3084   1529452          Hoxtolgay          Hoxtolgay   \n",
       "16582   756868  Tomaszów Lubelski  Tomaszow Lubelski   \n",
       "10703  1269834       Ichalkaranji       Ichalkaranji   \n",
       "12517   248414         Kurayyimah         Kurayyimah   \n",
       "18765    65170         Baardheere         Baardheere   \n",
       "\n",
       "                                             other_names       lat        lon  \\\n",
       "15964  Bandaraya Olongapo,Ciudad ti Olongapo,Dakbayan...  14.82917  120.28278   \n",
       "4879   Gorad Landsgut,Landishuta,Landsgut,Landshuad,L...  48.52961   12.16179   \n",
       "298    EQS,Ehskel',Eskelis,Esquel,ai si ke er,ayskywl... -42.91147  -71.31947   \n",
       "14950  Gorad Kelimaneh,Kelimane,Kelimanė,Quelimane,UE... -17.87861   36.88833   \n",
       "4007                                   GLJ,Garzon,Garzón   2.19593  -75.62777   \n",
       "3084   Chia-shih-t'o-lo-kai,Chia-shih-t’o-lo-kai,Hesh...  46.51872   86.00214   \n",
       "16582  Liublino Tomasuvas,Liublino Tomašuvas,Tomashev...  50.44767   23.41616   \n",
       "10703  Icalkarandzi,Ichalkaranji,aychalkaranjy,aychl ...  16.69117   74.46054   \n",
       "12517  Kareime,Kereime,Kuraiyima,Kuraymah,Kurayyimah,...  32.27639   35.59938   \n",
       "18765                                                NaN   2.34464   42.27644   \n",
       "\n",
       "       a      b   c    d   e      f          g         h       i      j    k  \\\n",
       "15964  P  PPLA3  PH  NaN  03     64  037107000       NaN  221178    NaN   11   \n",
       "4879   P  PPLA2  DE  NaN  02    092      09261  09261000   60488    NaN  488   \n",
       "298    P    PPL  AR  NaN  04  26035        NaN       NaN   28486    NaN  577   \n",
       "14950  P   PPLA  MZ  NaN  09    NaN        NaN       NaN  188964    NaN    9   \n",
       "4007   P    PPL  CO  NaN  16  41298        NaN       NaN   29451    NaN  834   \n",
       "3084   P  PPLA4  CN  NaN  13    NaN        NaN       NaN   22000    NaN  804   \n",
       "16582  P  PPLA2  PL  NaN  75   0618     061801       NaN   20261  275.0  274   \n",
       "10703  P    PPL  IN  NaN  16    530        NaN       NaN  274383    NaN  561   \n",
       "12517  P    PPL  JO  NaN  18    NaN        NaN       NaN   17837    NaN -183   \n",
       "18765  P   PPLL  SO  NaN  06    NaN        NaN       NaN   42240    NaN   97   \n",
       "\n",
       "                            region        date  \n",
       "15964                  Asia/Manila  2017-12-27  \n",
       "4879                 Europe/Berlin  2014-02-08  \n",
       "298    America/Argentina/Catamarca  2015-04-22  \n",
       "14950                Africa/Maputo  2012-01-19  \n",
       "4007                America/Bogota  2017-06-21  \n",
       "3084                   Asia/Urumqi  2013-06-04  \n",
       "16582                Europe/Warsaw  2010-10-14  \n",
       "10703                 Asia/Kolkata  2014-10-13  \n",
       "12517                   Asia/Amman  2016-05-07  \n",
       "18765             Africa/Mogadishu  2006-01-27  "
      ]
     },
     "execution_count": 126,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "cities_df[cities_df.f != float('nan')].sample(10)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Insert fuzzy name matching (if necessary)"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.7.4"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}