notebooks/dataprep/00h-GeoNamesCity.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Cities"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**[Work in progress]**\n",
    "\n",
    "This notebook creates a .csv file with city information (population > 1000) for ingestion into the Knowledge Graph.\n",
    "\n",
    "Data source: [GeoNames.org](https://download.geonames.org/export/dump/)\n",
    "\n",
    "Author: Peter Rose (pwrose@ucsd.edu)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import os\n",
    "from io import BytesIO\n",
    "from zipfile import ZipFile\n",
    "from urllib.request import urlopen\n",
    "from pathlib import Path\n",
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "pd.options.display.max_rows = None  # display all rows\n",
    "pd.options.display.max_columns = None  # display all columsns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "/Users/peter/Library/Application Support/Neo4j Desktop/Application/neo4jDatabases/database-9f7418e6-ef5d-4a2d-ae16-29a5a6814849/installation-4.1.0/import\n"
     ]
    }
   ],
   "source": [
    "NEO4J_IMPORT = Path(os.getenv('NEO4J_IMPORT'))\n",
    "print(NEO4J_IMPORT)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Read City data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "names = [\n",
    "        'geonameid','name','asciiname','alternatenames','latitude','longitude','feature class',\n",
    "        'feature code','country code','cc2','admin1 code','admin2 code','admin3 code','admin4 code',\n",
    "        'population','elevation','dem','timezone','modification date'\n",
    "]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Read city data (population > 15,000)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "url = 'https://download.geonames.org/export/dump/cities15000.zip'\n",
    "file_name = \"cities15000.txt\"\n",
    "resp = urlopen(url)\n",
    "zipfile = ZipFile(BytesIO(resp.read()))\n",
    "city_15k = pd.read_csv(zipfile.open(file_name), sep=\"\\t\", low_memory=False, names=names)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Read city data (population > 5,000)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "url = 'https://download.geonames.org/export/dump/cities5000.zip'\n",
    "file_name = \"cities5000.txt\"\n",
    "resp = urlopen(url)\n",
    "zipfile = ZipFile(BytesIO(resp.read()))\n",
    "city_5k = pd.read_csv(zipfile.open(file_name), sep=\"\\t\", low_memory=False, names=names)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Read city data (population > 1000)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [],
   "source": [
    "url = 'https://download.geonames.org/export/dump/cities1000.zip'\n",
    "file_name = \"cities1000.txt\"\n",
    "resp = urlopen(url)\n",
    "zipfile = ZipFile(BytesIO(resp.read()))\n",
    "city_1k = pd.read_csv(zipfile.open(file_name), sep=\"\\t\", low_memory=False, names=names)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [],
   "source": [
    "# TODO read city data (population > 500)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [],
   "source": [
    "city = pd.concat([city_15k, city_5k, city_1k])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [],
   "source": [
    "city = city[['geonameid', 'asciiname', 'country code', 'admin1 code', 'admin2 code']]\n",
    "city.fillna('', inplace=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Remove duplicates"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [],
   "source": [
    "city.drop_duplicates('geonameid', inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Number of cities 137497\n"
     ]
    }
   ],
   "source": [
    "print('Number of cities', city.shape[0])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [],
   "source": [
    "def get_location_id(country, admin1, admin2):\n",
    "    location = country\n",
    "    if admin1 != '':\n",
    "        location = location + '.' + admin1\n",
    "    if admin2 != '':\n",
    "        location = location + '.' + admin2\n",
    "        \n",
    "    return location"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Standardize column names for Knowlege Graph\n",
    "* id: unique identifier for country\n",
    "* name: name of node\n",
    "* parentId: unique identifier for continent\n",
    "* properties: camelCase"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [],
   "source": [
    "city.rename(columns={'geonameid': 'geonameId'}, inplace=True)\n",
    "city['id'] = city['geonameId']\n",
    "city.rename(columns={'asciiname': 'name'}, inplace=True)\n",
    "city['parentId'] = city.apply(lambda row: get_location_id(row['country code'], \n",
    "                                                         row['admin1 code'], \n",
    "                                                         row['admin2 code']), axis=1)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Example"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>geonameId</th>\n",
       "      <th>name</th>\n",
       "      <th>country code</th>\n",
       "      <th>admin1 code</th>\n",
       "      <th>admin2 code</th>\n",
       "      <th>id</th>\n",
       "      <th>parentId</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>4337</th>\n",
       "      <td>3621926</td>\n",
       "      <td>San Diego</td>\n",
       "      <td>CR</td>\n",
       "      <td>02</td>\n",
       "      <td>303</td>\n",
       "      <td>3621926</td>\n",
       "      <td>CR.02.303</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23384</th>\n",
       "      <td>5391811</td>\n",
       "      <td>San Diego</td>\n",
       "      <td>US</td>\n",
       "      <td>CA</td>\n",
       "      <td>073</td>\n",
       "      <td>5391811</td>\n",
       "      <td>US.CA.073</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8212</th>\n",
       "      <td>3669947</td>\n",
       "      <td>San Diego</td>\n",
       "      <td>CO</td>\n",
       "      <td>10</td>\n",
       "      <td>20750</td>\n",
       "      <td>3669947</td>\n",
       "      <td>CO.10.20750</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>53075</th>\n",
       "      <td>3590312</td>\n",
       "      <td>San Diego</td>\n",
       "      <td>GT</td>\n",
       "      <td>22</td>\n",
       "      <td></td>\n",
       "      <td>3590312</td>\n",
       "      <td>GT.22</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>53461</th>\n",
       "      <td>3602368</td>\n",
       "      <td>San Diego</td>\n",
       "      <td>HN</td>\n",
       "      <td>07</td>\n",
       "      <td></td>\n",
       "      <td>3602368</td>\n",
       "      <td>HN.07</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>81073</th>\n",
       "      <td>3827294</td>\n",
       "      <td>San Diego</td>\n",
       "      <td>MX</td>\n",
       "      <td>17</td>\n",
       "      <td></td>\n",
       "      <td>3827294</td>\n",
       "      <td>MX.17</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>81232</th>\n",
       "      <td>3973609</td>\n",
       "      <td>San Diego</td>\n",
       "      <td>MX</td>\n",
       "      <td>24</td>\n",
       "      <td>024</td>\n",
       "      <td>3973609</td>\n",
       "      <td>MX.24.024</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>81922</th>\n",
       "      <td>3987339</td>\n",
       "      <td>San Diego</td>\n",
       "      <td>MX</td>\n",
       "      <td>25</td>\n",
       "      <td>006</td>\n",
       "      <td>3987339</td>\n",
       "      <td>MX.25.006</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>83547</th>\n",
       "      <td>4024589</td>\n",
       "      <td>San Diego</td>\n",
       "      <td>MX</td>\n",
       "      <td>11</td>\n",
       "      <td>037</td>\n",
       "      <td>4024589</td>\n",
       "      <td>MX.11.037</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>84474</th>\n",
       "      <td>8858713</td>\n",
       "      <td>San Diego</td>\n",
       "      <td>MX</td>\n",
       "      <td>21</td>\n",
       "      <td>174</td>\n",
       "      <td>8858713</td>\n",
       "      <td>MX.21.174</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>85746</th>\n",
       "      <td>11085550</td>\n",
       "      <td>San Diego</td>\n",
       "      <td>MX</td>\n",
       "      <td>15</td>\n",
       "      <td>088</td>\n",
       "      <td>11085550</td>\n",
       "      <td>MX.15.088</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>92448</th>\n",
       "      <td>1690158</td>\n",
       "      <td>San Diego</td>\n",
       "      <td>PH</td>\n",
       "      <td>40</td>\n",
       "      <td>33</td>\n",
       "      <td>1690158</td>\n",
       "      <td>PH.40.33</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>92449</th>\n",
       "      <td>1690160</td>\n",
       "      <td>San Diego</td>\n",
       "      <td>PH</td>\n",
       "      <td>40</td>\n",
       "      <td>09</td>\n",
       "      <td>1690160</td>\n",
       "      <td>PH.40.09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>124675</th>\n",
       "      <td>4726311</td>\n",
       "      <td>San Diego</td>\n",
       "      <td>US</td>\n",
       "      <td>TX</td>\n",
       "      <td>131</td>\n",
       "      <td>4726311</td>\n",
       "      <td>US.TX.131</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>135693</th>\n",
       "      <td>3628462</td>\n",
       "      <td>San Diego</td>\n",
       "      <td>VE</td>\n",
       "      <td>07</td>\n",
       "      <td>0812</td>\n",
       "      <td>3628462</td>\n",
       "      <td>VE.07.0812</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        geonameId       name country code admin1 code admin2 code        id  \\\n",
       "4337      3621926  San Diego           CR          02         303   3621926   \n",
       "23384     5391811  San Diego           US          CA         073   5391811   \n",
       "8212      3669947  San Diego           CO          10       20750   3669947   \n",
       "53075     3590312  San Diego           GT          22               3590312   \n",
       "53461     3602368  San Diego           HN          07               3602368   \n",
       "81073     3827294  San Diego           MX          17               3827294   \n",
       "81232     3973609  San Diego           MX          24         024   3973609   \n",
       "81922     3987339  San Diego           MX          25         006   3987339   \n",
       "83547     4024589  San Diego           MX          11         037   4024589   \n",
       "84474     8858713  San Diego           MX          21         174   8858713   \n",
       "85746    11085550  San Diego           MX          15         088  11085550   \n",
       "92448     1690158  San Diego           PH          40          33   1690158   \n",
       "92449     1690160  San Diego           PH          40          09   1690160   \n",
       "124675    4726311  San Diego           US          TX         131   4726311   \n",
       "135693    3628462  San Diego           VE          07        0812   3628462   \n",
       "\n",
       "           parentId  \n",
       "4337      CR.02.303  \n",
       "23384     US.CA.073  \n",
       "8212    CO.10.20750  \n",
       "53075         GT.22  \n",
       "53461         HN.07  \n",
       "81073         MX.17  \n",
       "81232     MX.24.024  \n",
       "81922     MX.25.006  \n",
       "83547     MX.11.037  \n",
       "84474     MX.21.174  \n",
       "85746     MX.15.088  \n",
       "92448      PH.40.33  \n",
       "92449      PH.40.09  \n",
       "124675    US.TX.131  \n",
       "135693   VE.07.0812  "
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "city.query(\"name == 'San Diego'\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Export a minimum subset for now"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [],
   "source": [
    "city = city[['id', 'name', 'parentId', 'geonameId']]\n",
    "city.fillna('', inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>id</th>\n",
       "      <th>name</th>\n",
       "      <th>parentId</th>\n",
       "      <th>geonameId</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>3040051</td>\n",
       "      <td>les Escaldes</td>\n",
       "      <td>AD.08</td>\n",
       "      <td>3040051</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>3041563</td>\n",
       "      <td>Andorra la Vella</td>\n",
       "      <td>AD.07</td>\n",
       "      <td>3041563</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>290594</td>\n",
       "      <td>Umm Al Quwain City</td>\n",
       "      <td>AE.07</td>\n",
       "      <td>290594</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>291074</td>\n",
       "      <td>Ras Al Khaimah City</td>\n",
       "      <td>AE.05</td>\n",
       "      <td>291074</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>291580</td>\n",
       "      <td>Zayed City</td>\n",
       "      <td>AE.01.103</td>\n",
       "      <td>291580</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        id                 name   parentId  geonameId\n",
       "0  3040051         les Escaldes      AD.08    3040051\n",
       "1  3041563     Andorra la Vella      AD.07    3041563\n",
       "2   290594   Umm Al Quwain City      AE.07     290594\n",
       "3   291074  Ras Al Khaimah City      AE.05     291074\n",
       "4   291580           Zayed City  AE.01.103     291580"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "city.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [],
   "source": [
    "city.to_csv(NEO4J_IMPORT / \"00h-GeoNamesCity.csv\", index=False)"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.7.8"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}