notebooks/dataprep/00f-GeoNamesAdmin1.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# First Administrative Divisions of Countries"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**[Work in progress]**\n",
    "\n",
    "This notebook creates a .csv file with first administrative divisions (State, Province, Municipality) 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 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-b9d10363-6d59-4deb-9595-2cb904a99d1d/installation-4.1.0/import\n"
     ]
    }
   ],
   "source": [
    "NEO4J_IMPORT = Path(os.getenv('NEO4J_IMPORT'))\n",
    "print(NEO4J_IMPORT)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Create admin1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "admin1_url = 'https://download.geonames.org/export/dump/admin1CodesASCII.txt'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "names = ['code', 'name', 'name_ascii', 'geonameid']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "admin1 = pd.read_csv(admin1_url, sep='\\t', dtype='str', names=names)\n",
    "admin1 = admin1[['code', 'name_ascii', 'geonameid']]"
   ]
  },
  {
   "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": 7,
   "metadata": {},
   "outputs": [],
   "source": [
    "admin1.rename(columns={'code': 'id'}, inplace=True) # standard id column to link nodes\n",
    "admin1.rename(columns={'name_ascii': 'name'}, inplace=True)\n",
    "admin1.rename(columns={'geonameid': 'geonameId'}, inplace=True)\n",
    "admin1['code'] = admin1['id'].str.split('.', expand=True)[1]\n",
    "admin1['parentId'] = admin1['id'].str.split('.', expand=True)[0]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Use \"District of Columbia\" to be consistent with US Census"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [],
   "source": [
    "admin1['name'] = admin1['name'].str.replace('Washington, D.C.', 'District of Columbia')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Example"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "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>geonameId</th>\n",
       "      <th>code</th>\n",
       "      <th>parentId</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>3658</th>\n",
       "      <td>US.DC</td>\n",
       "      <td>District of Columbia</td>\n",
       "      <td>4138106</td>\n",
       "      <td>DC</td>\n",
       "      <td>US</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         id                  name geonameId code parentId\n",
       "3658  US.DC  District of Columbia   4138106   DC       US"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "admin1.query(\"id == 'US.DC'\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "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>geonameId</th>\n",
       "      <th>code</th>\n",
       "      <th>parentId</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>3665</th>\n",
       "      <td>US.MO</td>\n",
       "      <td>Missouri</td>\n",
       "      <td>4398678</td>\n",
       "      <td>MO</td>\n",
       "      <td>US</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         id      name geonameId code parentId\n",
       "3665  US.MO  Missouri   4398678   MO       US"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "admin1.query(\"name == 'Missouri'\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Export a minimum subset for now"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [],
   "source": [
    "admin1 = admin1[['id','name','code','parentId', 'geonameId']]\n",
    "admin1.fillna('', inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>id</th>\n",
       "      <th>name</th>\n",
       "      <th>code</th>\n",
       "      <th>parentId</th>\n",
       "      <th>geonameId</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>AD.06</td>\n",
       "      <td>Sant Julia de Loria</td>\n",
       "      <td>06</td>\n",
       "      <td>AD</td>\n",
       "      <td>3039162</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>AD.05</td>\n",
       "      <td>Ordino</td>\n",
       "      <td>05</td>\n",
       "      <td>AD</td>\n",
       "      <td>3039676</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>AD.04</td>\n",
       "      <td>La Massana</td>\n",
       "      <td>04</td>\n",
       "      <td>AD</td>\n",
       "      <td>3040131</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>AD.03</td>\n",
       "      <td>Encamp</td>\n",
       "      <td>03</td>\n",
       "      <td>AD</td>\n",
       "      <td>3040684</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>AD.02</td>\n",
       "      <td>Canillo</td>\n",
       "      <td>02</td>\n",
       "      <td>AD</td>\n",
       "      <td>3041203</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      id                 name code parentId geonameId\n",
       "0  AD.06  Sant Julia de Loria   06       AD   3039162\n",
       "1  AD.05               Ordino   05       AD   3039676\n",
       "2  AD.04           La Massana   04       AD   3040131\n",
       "3  AD.03               Encamp   03       AD   3040684\n",
       "4  AD.02              Canillo   02       AD   3041203"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "admin1.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [],
   "source": [
    "admin1.to_csv(NEO4J_IMPORT / \"00f-GeoNamesAdmin1.csv\", index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.7.8"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}