country.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "035d757d",
   "metadata": {},
   "source": [
    "![Natural Earth logo](https://www.naturalearthdata.com/wp-content/themes/NEV/images/nev_logo.png \"Natural Earth logo\")\n",
    "<div align=\"center\">\n",
    "\n",
    "## Scraping countries data and boundaries\n",
    "</div>\n",
    "\n",
    "Document explaining how countries data is processed and saved in PostgreSQL database.\n",
    "\n",
    "Link to data from Natural Earth: [counties data](https://www.naturalearthdata.com/http//www.naturalearthdata.com/download/50m/cultural/ne_50m_admin_0_countries.zip)\n",
    "\n",
    "<hr>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "0409a416",
   "metadata": {},
   "source": [
    "# 1. Download and load data\n",
    "## Data is in shapefile format compressed into zip file. There is need to import geopandas to read spatial data and requests to get zip file from web."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "c51850eb",
   "metadata": {},
   "outputs": [],
   "source": [
    "import geopandas as gpd\n",
    "import requests\n",
    "import os"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "7e7887b2",
   "metadata": {},
   "outputs": [],
   "source": [
    "countries_link = 'https://www.naturalearthdata.com/http//www.naturalearthdata.com/download/50m/cultural/ne_50m_admin_0_countries.zip'"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "5f4a43e4",
   "metadata": {},
   "source": [
    "## Get zip file and check status code. 200 is OK."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "af206636",
   "metadata": {},
   "outputs": [],
   "source": [
    "r = requests.get(countries_link ,stream=True, headers={\"User-Agent\": \"XY\"})\n",
    "r.status_code"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "70c642f7",
   "metadata": {},
   "source": [
    "## Save zip file inside temp folder."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "77929ab6",
   "metadata": {},
   "outputs": [],
   "source": [
    "if not os.path.exists('../temp/country/'):\n",
    "    os.makedirs('../temp/country/')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "f8dbbc27",
   "metadata": {},
   "outputs": [],
   "source": [
    "with open('../temp/country/ne_50m_admin_0_countries.zip', 'wb') as fd:\n",
    "    for chunk in r.iter_content(chunk_size=128):\n",
    "        fd.write(chunk)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "a9163df7",
   "metadata": {},
   "outputs": [],
   "source": [
    "zip_file = \"zip://../temp/country/ne_50m_admin_0_countries.zip!ne_50m_admin_0_countries.shp\""
   ]
  },
  {
   "cell_type": "markdown",
   "id": "bd96f687",
   "metadata": {},
   "source": [
    "## Load shapefile into geopandas dataframe."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "44487784",
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "countries_shp = gpd.read_file(\n",
    "    zip_file, layer='ne_50m_admin_0_countries'\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "67416c9f",
   "metadata": {},
   "source": [
    "## Check number of row, columns and crs shapefile data."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "5bb80f61",
   "metadata": {},
   "outputs": [],
   "source": [
    "countries_shp.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "8d4463e4",
   "metadata": {},
   "outputs": [],
   "source": [
    "countries_shp.crs"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "01a13b43",
   "metadata": {},
   "source": [
    "## Modify dataframe to display only columns that are valuable for our dataset."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "c4e640b4",
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "countries_shp.columns.values.tolist()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "c94a5127",
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "filter_countries = countries_shp[\n",
    "    [\n",
    "        \"NAME\", \"NAME_LONG\",\n",
    "        \"FIPS_10\", \"ISO_A2\",\n",
    "        \"ISO_A3\", \"POSTAL\",\n",
    "        \"TYPE\", \"CONTINENT\",\n",
    "        \"SUBREGION\", \"WIKIDATAID\",\n",
    "        \"geometry\",\n",
    "    ]\n",
    "]"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c7e5f4a7",
   "metadata": {},
   "source": [
    "## Change column names to equal database model names."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "a7e9249a",
   "metadata": {},
   "outputs": [],
   "source": [
    "filter_countries.set_axis(\n",
    "[\n",
    "    'name', 'name_long',\n",
    "    'fips_10', 'iso2',\n",
    "    'iso3', 'postal',\n",
    "    'country_type', 'continent',\n",
    "    'subregion', 'wikidata_id',\n",
    "    \"geometry\"\n",
    "], axis=1, inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "17c88145",
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "filter_countries"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "40fb09e7",
   "metadata": {},
   "source": [
    "# 2. Create foreign keys if not exist and save in db"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "8566ff0c",
   "metadata": {},
   "outputs": [],
   "source": [
    "from apps.landform.models import Continent\n",
    "from apps.administrative_area.models import CountryType, Subregion"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "6374eb6f",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "id": "8a7e53c7",
   "metadata": {},
   "source": [
    "## Get unique values from \"country_type\" column and save if not yet in database."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "676fc8d7",
   "metadata": {},
   "outputs": [],
   "source": [
    "types = set(filter_countries[\"country_type\"])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "4736cb38",
   "metadata": {},
   "outputs": [],
   "source": [
    "types"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "67e9db9f",
   "metadata": {},
   "outputs": [],
   "source": [
    "[CountryType.objects.get_or_create(name=item) for item in types]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "d9535d58",
   "metadata": {},
   "outputs": [],
   "source": [
    "CountryType.objects.values_list('name', flat=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "191770d5",
   "metadata": {},
   "source": [
    "## Get unique values from \"continent\" column and save if not yet in database."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "dc136599",
   "metadata": {},
   "outputs": [],
   "source": [
    "continents = set(filter_countries[\"continent\"])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "de3aef76",
   "metadata": {},
   "outputs": [],
   "source": [
    "continents"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "ed08c9dd",
   "metadata": {},
   "outputs": [],
   "source": [
    "[Continent.objects.get_or_create(name=item) for item in continents]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "c4993cfc",
   "metadata": {},
   "outputs": [],
   "source": [
    "Continent.objects.values_list('name', flat=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "bbedcff3",
   "metadata": {},
   "source": [
    "## Get unique values from \"subregion\" column and save if not yet in database."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "21a1c557",
   "metadata": {},
   "outputs": [],
   "source": [
    "subregions = set(filter_countries[\"subregion\"])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "a78fcdb8",
   "metadata": {},
   "outputs": [],
   "source": [
    "subregions"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "f12da609",
   "metadata": {},
   "outputs": [],
   "source": [
    "[Subregion.objects.get_or_create(name=item) for item in subregions]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "5640fde2",
   "metadata": {},
   "outputs": [],
   "source": [
    "Subregion.objects.values_list('name', flat=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f09edc97",
   "metadata": {},
   "source": [
    "# 3. Fix -99 value for country codes (error creating fk)\n",
    "## Some country codes values are '-99'. It is important to change especially iso2 values because it will be conected to other data by foreign key eg. regions and state data."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "f7a486ab",
   "metadata": {},
   "outputs": [],
   "source": [
    "filter_countries[filter_countries.eq('-99').any(1)]"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ea0e9805",
   "metadata": {},
   "source": [
    "## Replace data that is avaliable manually"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "9fb132fe",
   "metadata": {},
   "outputs": [],
   "source": [
    "for index, row in filter_countries[filter_countries.eq('-99').any(1)].iterrows():\n",
    "    if row['name'] == 'S. Sudan':\n",
    "        filter_countries.at[index, 'fips_10'] = 'OD'\n",
    "    elif row['name'] == 'Norway':\n",
    "        filter_countries.at[index, 'fips_10'] = 'NO'\n",
    "        filter_countries.at[index, 'iso2'] = 'NO'\n",
    "        filter_countries.at[index, 'iso3'] = 'NOR'\n",
    "    elif row['name'] == 'Israel':\n",
    "        filter_countries.at[index, 'fips_10'] = 'IS'\n",
    "    elif row['name'] == 'France':\n",
    "        filter_countries.at[index, 'iso2'] = 'FR'\n",
    "        filter_countries.at[index, 'iso3'] = 'FRA'"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "5cf6e32e",
   "metadata": {},
   "source": [
    "## Change not avaliable data from '-99' to None"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "4520dd1b",
   "metadata": {},
   "outputs": [],
   "source": [
    "finall_countries = filter_countries.replace('-99', None)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "a1f2d3a2",
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "finall_countries"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "630ef113",
   "metadata": {},
   "source": [
    "# 4. Save in database"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "b4af25cc",
   "metadata": {},
   "source": [
    "## Preview avaliable data from dataframe on map."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "bd3e7dd5",
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "finall_countries.explore(column=\"continent\", popup=True, style_kwds=dict(color=\"black\"))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "81df65fa",
   "metadata": {},
   "outputs": [],
   "source": [
    "from django.contrib.gis.geos import GEOSGeometry, Polygon, MultiPolygon\n",
    "from apps.administrative_area.models import Country"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "38ae5c39",
   "metadata": {},
   "source": [
    "## Convert geometry field from geopandas dataframe to GIS Multipolygon."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "1f958284",
   "metadata": {},
   "outputs": [],
   "source": [
    "def convert_geometry(geometry):\n",
    "    geometry = GEOSGeometry(str(geometry))\n",
    "    if geometry.geom_type == 'Polygon':\n",
    "        geometry = MultiPolygon(geometry)\n",
    "    return geometry"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "dda5a818",
   "metadata": {},
   "source": [
    "## Iterate through dataframe, convert data, get FK and update and create new entry in database."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "ff4c701f",
   "metadata": {},
   "outputs": [],
   "source": [
    "for row in finall_countries.itertuples(index=False, name='Pandas'):\n",
    "    geometry = convert_geometry(row.geometry)\n",
    "    country_type = CountryType.objects.filter(name=row.country_type)[0]\n",
    "    continent = Continent.objects.filter(name=row.continent)[0]\n",
    "    subregion = Subregion.objects.filter(name=row.subregion)[0]\n",
    "    \n",
    "    updated_values = {\n",
    "        'name': row.name,\n",
    "        'name_long': row.name_long,\n",
    "        'fips_10': row.fips_10,\n",
    "        'iso2': row.iso2,\n",
    "        'iso3': row.iso3,\n",
    "        'continent': continent,\n",
    "        'subregion': subregion,\n",
    "        'country_type': country_type,\n",
    "        'geometry': geometry,\n",
    "    }\n",
    "    \n",
    "    # update base on postal code and wikidata_id\n",
    "    Country.objects.update_or_create(\n",
    "        wikidata_id=row.wikidata_id,\n",
    "        postal=row.postal,\n",
    "        defaults=updated_values,\n",
    "    )"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "cc6e46f4",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Django Shell-Plus",
   "language": "python",
   "name": "django_extensions"
  },
  "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.9.7"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}