notebooks/dataprep/00k-UNRegion.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# UN Regions"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**[Work in progress]**\n",
    "\n",
    "This notebook creates a .csv file with UN geographic regions, subregions, and intermediate region information for ingestion into the Knowledge Graph.\n",
    "\n",
    "Data source: [Statistics Division of the United Nations Secretariat](https://unstats.un.org/unsd/methodology/m49/)\n",
    "\n",
    "Data set: [M49](https://unstats.un.org/unsd/methodology/m49/overview)\n",
    "\n",
    "Data preparation: The Excel file downloaded from UN has features that are not compatible with Pandas. To fix this issue, download the the Excel file, load into Excel, than save again in the .xlsl format and put into this location ../../reference_data/UNSDMethodology.xlsx\n",
    "\n",
    "Authors: Braden Riggs (bdriggs@ucsd.edu), 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/com.Neo4j.Relate/data/dbmss/dbms-8bf637fc-0d20-4d9f-9c6f-f7e72e92a4da/import\n"
     ]
    }
   ],
   "source": [
    "NEO4J_IMPORT = Path(os.getenv('NEO4J_IMPORT'))\n",
    "print(NEO4J_IMPORT)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### UN regions, subregions, and intermediate regions"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "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>Region Name</th>\n",
       "      <th>Region Code</th>\n",
       "      <th>Sub-region Name</th>\n",
       "      <th>Sub-region Code</th>\n",
       "      <th>Intermediate Region Name</th>\n",
       "      <th>Intermediate Region Code</th>\n",
       "      <th>ISO-alpha3 Code</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Africa</td>\n",
       "      <td>2</td>\n",
       "      <td>Northern Africa</td>\n",
       "      <td>15</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>DZA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Africa</td>\n",
       "      <td>2</td>\n",
       "      <td>Northern Africa</td>\n",
       "      <td>15</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>EGY</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Africa</td>\n",
       "      <td>2</td>\n",
       "      <td>Northern Africa</td>\n",
       "      <td>15</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>LBY</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Africa</td>\n",
       "      <td>2</td>\n",
       "      <td>Northern Africa</td>\n",
       "      <td>15</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>MAR</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Africa</td>\n",
       "      <td>2</td>\n",
       "      <td>Northern Africa</td>\n",
       "      <td>15</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>SDN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  Region Name Region Code  Sub-region Name Sub-region Code  \\\n",
       "0      Africa           2  Northern Africa              15   \n",
       "1      Africa           2  Northern Africa              15   \n",
       "2      Africa           2  Northern Africa              15   \n",
       "3      Africa           2  Northern Africa              15   \n",
       "4      Africa           2  Northern Africa              15   \n",
       "\n",
       "  Intermediate Region Name Intermediate Region Code ISO-alpha3 Code  \n",
       "0                                                               DZA  \n",
       "1                                                               EGY  \n",
       "2                                                               LBY  \n",
       "3                                                               MAR  \n",
       "4                                                               SDN  "
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = pd.read_excel(\"../../reference_data/UNSDMethodology.xlsx\", dtype='str')\n",
    "df = df[['Region Name', 'Region Code', 'Sub-region Name', 'Sub-region Code', \n",
    "         'Intermediate Region Name', 'Intermediate Region Code', 'ISO-alpha3 Code']]\n",
    "df.fillna('', inplace=True)\n",
    "# for now exclude region without an iso code (Channel Islands)\n",
    "df = df.query(\"`ISO-alpha3 Code` != ''\")\n",
    "# Antarctica has no region code\n",
    "df = df.query(\"`Region Name` != ''\")\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Assign names without spaces"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.rename(columns={'Region Name': 'UNRegion'}, inplace=True)\n",
    "df.rename(columns={'Region Code': 'UNRegionCode'}, inplace=True)\n",
    "df.rename(columns={'Sub-region Name': 'UNSubRegion'}, inplace=True)\n",
    "df.rename(columns={'Sub-region Code': 'UNSubRegionCode'}, inplace=True)\n",
    "df.rename(columns={'Intermediate Region Name': 'UNIntermediateRegion'}, inplace=True)\n",
    "df.rename(columns={'Intermediate Region Code': 'UNIntermediateRegionCode'}, inplace=True)\n",
    "df.rename(columns={'ISO-alpha3 Code': 'iso3'}, inplace=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Assign unique identifiers\n",
    "Use m49 as a prefix for the M49 standard  by the Statistics Division of the United Nations Secretariat "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "df['UNRegionCode'] = 'm49:' + df['UNRegionCode']\n",
    "df['UNSubRegionCode'] = 'm49:' + df['UNSubRegionCode'] \n",
    "df['UNIntermediateRegionCode'] = 'm49:' + df['UNIntermediateRegionCode'] "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>UNRegion</th>\n",
       "      <th>UNRegionCode</th>\n",
       "      <th>UNSubRegion</th>\n",
       "      <th>UNSubRegionCode</th>\n",
       "      <th>UNIntermediateRegion</th>\n",
       "      <th>UNIntermediateRegionCode</th>\n",
       "      <th>iso3</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Africa</td>\n",
       "      <td>m49:2</td>\n",
       "      <td>Northern Africa</td>\n",
       "      <td>m49:15</td>\n",
       "      <td></td>\n",
       "      <td>m49:</td>\n",
       "      <td>DZA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Africa</td>\n",
       "      <td>m49:2</td>\n",
       "      <td>Northern Africa</td>\n",
       "      <td>m49:15</td>\n",
       "      <td></td>\n",
       "      <td>m49:</td>\n",
       "      <td>EGY</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Africa</td>\n",
       "      <td>m49:2</td>\n",
       "      <td>Northern Africa</td>\n",
       "      <td>m49:15</td>\n",
       "      <td></td>\n",
       "      <td>m49:</td>\n",
       "      <td>LBY</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Africa</td>\n",
       "      <td>m49:2</td>\n",
       "      <td>Northern Africa</td>\n",
       "      <td>m49:15</td>\n",
       "      <td></td>\n",
       "      <td>m49:</td>\n",
       "      <td>MAR</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Africa</td>\n",
       "      <td>m49:2</td>\n",
       "      <td>Northern Africa</td>\n",
       "      <td>m49:15</td>\n",
       "      <td></td>\n",
       "      <td>m49:</td>\n",
       "      <td>SDN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  UNRegion UNRegionCode      UNSubRegion UNSubRegionCode UNIntermediateRegion  \\\n",
       "0   Africa        m49:2  Northern Africa          m49:15                        \n",
       "1   Africa        m49:2  Northern Africa          m49:15                        \n",
       "2   Africa        m49:2  Northern Africa          m49:15                        \n",
       "3   Africa        m49:2  Northern Africa          m49:15                        \n",
       "4   Africa        m49:2  Northern Africa          m49:15                        \n",
       "\n",
       "  UNIntermediateRegionCode iso3  \n",
       "0                     m49:  DZA  \n",
       "1                     m49:  EGY  \n",
       "2                     m49:  LBY  \n",
       "3                     m49:  MAR  \n",
       "4                     m49:  SDN  "
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Add missing region information (from hand-curated list)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [],
   "source": [
    "additions = pd.read_csv(\"../../reference_data/UNRegionAdditions.csv\")\n",
    "additions.fillna('', inplace=True)"
   ]
  },
  {
   "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>UNRegion</th>\n",
       "      <th>UNRegionCode</th>\n",
       "      <th>UNSubRegion</th>\n",
       "      <th>UNSubRegionCode</th>\n",
       "      <th>UNIntermediateRegion</th>\n",
       "      <th>UNIntermediateRegionCode</th>\n",
       "      <th>iso3</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Antarctica</td>\n",
       "      <td>m49:Antarctica</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>ATA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Americas</td>\n",
       "      <td>m49:19</td>\n",
       "      <td>Latin America and the Caribbean</td>\n",
       "      <td>m49:419</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>BES</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Americas</td>\n",
       "      <td>m49:19</td>\n",
       "      <td>Latin America and the Caribbean</td>\n",
       "      <td>m49:419</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>ANT</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Asia</td>\n",
       "      <td>m49:142</td>\n",
       "      <td>Eastern Asia</td>\n",
       "      <td>m49:30</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>HKG</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Asia</td>\n",
       "      <td>m49:142</td>\n",
       "      <td>Eastern Asia</td>\n",
       "      <td>m49:30</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>MAC</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>Asia</td>\n",
       "      <td>m49:142</td>\n",
       "      <td>Eastern Asia</td>\n",
       "      <td>m49:30</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>TWN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>Europe</td>\n",
       "      <td>m49:150</td>\n",
       "      <td>Southern Europe</td>\n",
       "      <td>m49:39</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>XKX</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>Europe</td>\n",
       "      <td>m49:150</td>\n",
       "      <td>Southern Europe</td>\n",
       "      <td>m49:39</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>CSG</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     UNRegion    UNRegionCode                      UNSubRegion  \\\n",
       "0  Antarctica  m49:Antarctica                                    \n",
       "1    Americas          m49:19  Latin America and the Caribbean   \n",
       "2    Americas          m49:19  Latin America and the Caribbean   \n",
       "3        Asia         m49:142                     Eastern Asia   \n",
       "4        Asia         m49:142                     Eastern Asia   \n",
       "5        Asia         m49:142                     Eastern Asia   \n",
       "6      Europe         m49:150                  Southern Europe   \n",
       "7      Europe         m49:150                  Southern Europe   \n",
       "\n",
       "  UNSubRegionCode UNIntermediateRegion UNIntermediateRegionCode iso3  \n",
       "0                                                                ATA  \n",
       "1         m49:419                                                BES  \n",
       "2         m49:419                                                ANT  \n",
       "3          m49:30                                                HKG  \n",
       "4          m49:30                                                MAC  \n",
       "5          m49:30                                                TWN  \n",
       "6          m49:39                                                XKX  \n",
       "7          m49:39                                                CSG  "
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "additions.tail(10)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [],
   "source": [
    "df = df.append(additions)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.to_csv(NEO4J_IMPORT / \"00k-UNAll.csv\", index=False)  "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Save region assignments in separate files\n",
    "This is done so iso3 country codes can be linked to the lowest level in the UN region hierarchy."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [],
   "source": [
    "intermediateRegion = df[df['UNIntermediateRegion'] != '']\n",
    "intermediateRegion.to_csv(NEO4J_IMPORT / \"00k-UNIntermediateRegion.csv\", index=False)                            "
   ]
  },
  {
   "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>UNRegion</th>\n",
       "      <th>UNRegionCode</th>\n",
       "      <th>UNSubRegion</th>\n",
       "      <th>UNSubRegionCode</th>\n",
       "      <th>UNIntermediateRegion</th>\n",
       "      <th>UNIntermediateRegionCode</th>\n",
       "      <th>iso3</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>Africa</td>\n",
       "      <td>m49:2</td>\n",
       "      <td>Sub-Saharan Africa</td>\n",
       "      <td>m49:202</td>\n",
       "      <td>Eastern Africa</td>\n",
       "      <td>m49:14</td>\n",
       "      <td>IOT</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>Africa</td>\n",
       "      <td>m49:2</td>\n",
       "      <td>Sub-Saharan Africa</td>\n",
       "      <td>m49:202</td>\n",
       "      <td>Eastern Africa</td>\n",
       "      <td>m49:14</td>\n",
       "      <td>BDI</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>Africa</td>\n",
       "      <td>m49:2</td>\n",
       "      <td>Sub-Saharan Africa</td>\n",
       "      <td>m49:202</td>\n",
       "      <td>Eastern Africa</td>\n",
       "      <td>m49:14</td>\n",
       "      <td>COM</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>Africa</td>\n",
       "      <td>m49:2</td>\n",
       "      <td>Sub-Saharan Africa</td>\n",
       "      <td>m49:202</td>\n",
       "      <td>Eastern Africa</td>\n",
       "      <td>m49:14</td>\n",
       "      <td>DJI</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>Africa</td>\n",
       "      <td>m49:2</td>\n",
       "      <td>Sub-Saharan Africa</td>\n",
       "      <td>m49:202</td>\n",
       "      <td>Eastern Africa</td>\n",
       "      <td>m49:14</td>\n",
       "      <td>ERI</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   UNRegion UNRegionCode         UNSubRegion UNSubRegionCode  \\\n",
       "7    Africa        m49:2  Sub-Saharan Africa         m49:202   \n",
       "8    Africa        m49:2  Sub-Saharan Africa         m49:202   \n",
       "9    Africa        m49:2  Sub-Saharan Africa         m49:202   \n",
       "10   Africa        m49:2  Sub-Saharan Africa         m49:202   \n",
       "11   Africa        m49:2  Sub-Saharan Africa         m49:202   \n",
       "\n",
       "   UNIntermediateRegion UNIntermediateRegionCode iso3  \n",
       "7        Eastern Africa                   m49:14  IOT  \n",
       "8        Eastern Africa                   m49:14  BDI  \n",
       "9        Eastern Africa                   m49:14  COM  \n",
       "10       Eastern Africa                   m49:14  DJI  \n",
       "11       Eastern Africa                   m49:14  ERI  "
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "intermediateRegion.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [],
   "source": [
    "subRegion = df[(df['UNSubRegion'] != '') & (df['UNIntermediateRegion'] == '')]\n",
    "subRegion.to_csv(NEO4J_IMPORT / \"00k-UNSubRegion.csv\", index=False)  "
   ]
  },
  {
   "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>UNRegion</th>\n",
       "      <th>UNRegionCode</th>\n",
       "      <th>UNSubRegion</th>\n",
       "      <th>UNSubRegionCode</th>\n",
       "      <th>UNIntermediateRegion</th>\n",
       "      <th>UNIntermediateRegionCode</th>\n",
       "      <th>iso3</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Africa</td>\n",
       "      <td>m49:2</td>\n",
       "      <td>Northern Africa</td>\n",
       "      <td>m49:15</td>\n",
       "      <td></td>\n",
       "      <td>m49:</td>\n",
       "      <td>DZA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Africa</td>\n",
       "      <td>m49:2</td>\n",
       "      <td>Northern Africa</td>\n",
       "      <td>m49:15</td>\n",
       "      <td></td>\n",
       "      <td>m49:</td>\n",
       "      <td>EGY</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Africa</td>\n",
       "      <td>m49:2</td>\n",
       "      <td>Northern Africa</td>\n",
       "      <td>m49:15</td>\n",
       "      <td></td>\n",
       "      <td>m49:</td>\n",
       "      <td>LBY</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Africa</td>\n",
       "      <td>m49:2</td>\n",
       "      <td>Northern Africa</td>\n",
       "      <td>m49:15</td>\n",
       "      <td></td>\n",
       "      <td>m49:</td>\n",
       "      <td>MAR</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Africa</td>\n",
       "      <td>m49:2</td>\n",
       "      <td>Northern Africa</td>\n",
       "      <td>m49:15</td>\n",
       "      <td></td>\n",
       "      <td>m49:</td>\n",
       "      <td>SDN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  UNRegion UNRegionCode      UNSubRegion UNSubRegionCode UNIntermediateRegion  \\\n",
       "0   Africa        m49:2  Northern Africa          m49:15                        \n",
       "1   Africa        m49:2  Northern Africa          m49:15                        \n",
       "2   Africa        m49:2  Northern Africa          m49:15                        \n",
       "3   Africa        m49:2  Northern Africa          m49:15                        \n",
       "4   Africa        m49:2  Northern Africa          m49:15                        \n",
       "\n",
       "  UNIntermediateRegionCode iso3  \n",
       "0                     m49:  DZA  \n",
       "1                     m49:  EGY  \n",
       "2                     m49:  LBY  \n",
       "3                     m49:  MAR  \n",
       "4                     m49:  SDN  "
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "subRegion.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [],
   "source": [
    "region = df[(df['UNSubRegion'] == '') & (df['UNIntermediateRegion'] == '')]\n",
    "region.to_csv(NEO4J_IMPORT / \"00k-UNRegion.csv\", index=False)  "
   ]
  },
  {
   "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>UNRegion</th>\n",
       "      <th>UNRegionCode</th>\n",
       "      <th>UNSubRegion</th>\n",
       "      <th>UNSubRegionCode</th>\n",
       "      <th>UNIntermediateRegion</th>\n",
       "      <th>UNIntermediateRegionCode</th>\n",
       "      <th>iso3</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Antarctica</td>\n",
       "      <td>m49:Antarctica</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>ATA</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     UNRegion    UNRegionCode UNSubRegion UNSubRegionCode  \\\n",
       "0  Antarctica  m49:Antarctica                               \n",
       "\n",
       "  UNIntermediateRegion UNIntermediateRegionCode iso3  \n",
       "0                                                ATA  "
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "region.head()"
   ]
  }
 ],
 "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.10"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}