notebooks/ESIDA DB Data Quality.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "08710c3d",
   "metadata": {},
   "source": [
    "# ESIDA DB Data Access Demo for Data Quality"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "ab643bd6",
   "metadata": {},
   "outputs": [],
   "source": [
    "import requests\n",
    "from requests.auth import HTTPBasicAuth\n",
    "from urllib.parse import urlencode\n",
    "import datetime as dt\n",
    "\n",
    "import pandas as pd\n",
    "import geopandas\n",
    "\n",
    "import matplotlib.pyplot as plt\n",
    "import matplotlib.ticker as mtick\n",
    "import numpy as np"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "78a56ac9",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Run for local database\n",
    "HOST = 'http://localhost'\n",
    "auth = None"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "8f72e5c5",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Run for hosted database\n",
    "HOST = 'https://esida.informatik.haw-hamburg.de'\n",
    "auth = HTTPBasicAuth('esida', 'opendata22')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "9e600103",
   "metadata": {},
   "source": [
    "# Get region shapes from database"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "c24d2228",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Parameters:\n",
    "# - type=region|district (optional)\n",
    "# - wkt (optional, returns Geometry)\n",
    "q = {\n",
    "    'type': 'region',\n",
    "    'wkt': True\n",
    "}\n",
    "d = requests.get(f\"{HOST}/api/v1/shapes?{urlencode(q)}\", auth=auth).json()\n",
    "\n",
    "# DataFrame\n",
    "regions_gdf = geopandas.GeoDataFrame(d['data'])\n",
    "if q['wkt']:\n",
    "    regions_gdf['geometry'] = geopandas.GeoSeries.from_wkt(regions_gdf['wkt'])\n",
    "    regions_gdf = regions_gdf.drop(columns=['wkt'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "4750a7de",
   "metadata": {},
   "outputs": [],
   "source": [
    "regions = dict(zip(regions_gdf.id, regions_gdf.name))\n",
    "regions_Name2Id = dict(zip(regions_gdf.name, regions_gdf.id))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "630395d8",
   "metadata": {},
   "outputs": [],
   "source": [
    "regions_gdf.plot()\n",
    "plt.title('Regions')\n",
    "plt.axis('off')\n",
    "plt.show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "8a2b0606",
   "metadata": {},
   "outputs": [],
   "source": [
    "len(regions_gdf)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "109b63c0",
   "metadata": {},
   "source": [
    "# Get district shapes from database"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "320a1346",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Parameters:\n",
    "# - type=region|district (optional)\n",
    "# - wkt (optional, returns Geometry)\n",
    "q = {\n",
    "    'type': 'district',\n",
    "    'wkt': True\n",
    "}\n",
    "d = requests.get(f\"{HOST}/api/v1/shapes?{urlencode(q)}\", auth=auth).json()\n",
    "\n",
    "# DataFrame\n",
    "districts_gdf = geopandas.GeoDataFrame(d['data'])\n",
    "if q['wkt']:\n",
    "    districts_gdf['geometry'] = geopandas.GeoSeries.from_wkt(districts_gdf['wkt'])\n",
    "    districts_gdf = districts_gdf.drop(columns=['wkt'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "66bde169",
   "metadata": {},
   "outputs": [],
   "source": [
    "districts = dict(zip(districts_gdf.id, districts_gdf.name))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "001be481",
   "metadata": {},
   "outputs": [],
   "source": [
    "districts_gdf.plot()\n",
    "plt.title('Districts')\n",
    "plt.axis('off')\n",
    "plt.show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "0cdf7d31",
   "metadata": {},
   "outputs": [],
   "source": [
    "len(districts_gdf)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "78ee2ffb",
   "metadata": {},
   "source": [
    "# Get data layer information"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "3f68a256",
   "metadata": {},
   "outputs": [],
   "source": [
    "q = {}\n",
    "d = requests.get(f\"{HOST}/api/v1/parameters?{urlencode(q)}\", auth=auth).json()\n",
    "\n",
    "# DataFrame\n",
    "datalayers_df = pd.DataFrame(d['data'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "d12bce89",
   "metadata": {},
   "outputs": [],
   "source": [
    "datalayers_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "f4f20a55",
   "metadata": {},
   "outputs": [],
   "source": [
    "# list all available loaded parameters\n",
    "loaded_parameters = list(datalayers_df['parameter_id'].values)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "30716117",
   "metadata": {},
   "source": [
    "# Get map of temporal coverage"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "67f9460f",
   "metadata": {},
   "outputs": [],
   "source": [
    "# for which parameters do want to show temporal coverage?\n",
    "parameters = ['chirts_maxt', 'meteo_maxt']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "28a4a940",
   "metadata": {
    "scrolled": false
   },
   "outputs": [],
   "source": [
    "cache = {}\n",
    "for parameter_id in parameters:\n",
    "    rows = []\n",
    "\n",
    "    for name, shape_id in regions_Name2Id.items():\n",
    "        if shape_id not in cache:\n",
    "            q = {\n",
    "                'shape_id': shape_id,\n",
    "            }\n",
    "            d = requests.get(f\"{HOST}/api/v1/parameters?{urlencode(q)}\", auth=auth).json()\n",
    "\n",
    "            cache[shape_id] = pd.DataFrame(d['data'])\n",
    "        dfx = cache[shape_id]\n",
    "        row = {\n",
    "            'id': shape_id,\n",
    "            'value': dfx[dfx['parameter_id'] == parameter_id].reset_index(drop=True).at[0, 'temporal_coverage']\n",
    "        }\n",
    "        rows.append(row)\n",
    "\n",
    "    dfxv = pd.DataFrame(rows)\n",
    "    gdfxv = regions_gdf.merge(dfxv, on='id')\n",
    "\n",
    "    gdfxv.plot(column='value',  vmin=0, vmax=1, legend=True)\n",
    "    plt.title(f\"{parameter_id} between 2010 and 2020\")\n",
    "    plt.show()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "d035a731",
   "metadata": {},
   "source": [
    "# Spatial coverage\n",
    "\n",
    "**ATTENTION**: the queries regarding the spatial coverage are quite cpu time heavy (minutes up to houres for the large spatial data sets). This can lead to timeout errors due to the Kubernetes Ingress Proxy for the provided hosting of the database on the HAW.\n",
    "\n",
    "This queries will probably fail in combination with the HAW hosted database. You need to run the ESIDA database locally and import the data of interest and then use the cells below."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "97f289b2",
   "metadata": {
    "scrolled": false
   },
   "outputs": [],
   "source": [
    "cache = {}\n",
    "for parameter_id in loaded_parameters[:2]:\n",
    "    rows = []\n",
    "\n",
    "    for name, shape_id in regions_Name2Id.items():\n",
    "        if shape_id not in cache:\n",
    "            q = {\n",
    "                'shape_id': shape_id,\n",
    "                'da_spatial': True\n",
    "            }\n",
    "            d = requests.get(f\"{HOST}/api/v1/parameters?{urlencode(q)}\", auth=auth).json()\n",
    "\n",
    "            cache[shape_id] = pd.DataFrame(d['data'])\n",
    "        dfx = cache[shape_id]\n",
    "        row = {\n",
    "            'id': shape_id,\n",
    "            'value': dfx[dfx['parameter_id'] == parameter_id].reset_index(drop=True).at[0, 'spatial_coverage']\n",
    "        }\n",
    "        rows.append(row)\n",
    "\n",
    "    dfxv = pd.DataFrame(rows)\n",
    "    gdfxv = regions_gdf.merge(dfxv, on='id')\n",
    "\n",
    "    gdfxv.plot(column='value',  vmin=0, vmax=1, legend=True)\n",
    "    plt.title(f\"{parameter_id} between 2010 and 2020\")\n",
    "    plt.show()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "4fea1497",
   "metadata": {},
   "source": [
    "# Spatial coverage for a single parameter"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "1186612f",
   "metadata": {},
   "outputs": [],
   "source": [
    "p = 'worldpop_popd'\n",
    "q = {}\n",
    "d = requests.get(f\"{HOST}/api/v1/da_spatial/{p}?{urlencode(q)}\", auth=auth).json()\n",
    "\n",
    "df = pd.DataFrame(d['data'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "3c15b3ee",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Total spatial completness over all files (mean)\n",
    "d['data_quality']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "1bd7fabc",
   "metadata": {},
   "outputs": [],
   "source": [
    "# per file / per shape coverage\n",
    "df.head(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "eb4b361f",
   "metadata": {},
   "outputs": [],
   "source": [
    "df.to_csv('da_spatial_worldpop_popd.csv', index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "2a192d05",
   "metadata": {},
   "outputs": [],
   "source": [
    "spatial_coverage = []\n",
    "for shape_id, name in regions.items():\n",
    "    dfx = df[df['shape_id'] == shape_id]\n",
    "    spatial_coverage.append({\n",
    "        'id': shape_id,\n",
    "        'coverage': dfx['coverage'].mean()\n",
    "    })"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "581afd53",
   "metadata": {},
   "outputs": [],
   "source": [
    "spatial_coverage_df = pd.DataFrame(spatial_coverage)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "4ce1b114",
   "metadata": {},
   "outputs": [],
   "source": [
    "\n",
    "#fig, ax = plt.subplots(1, 1, figsize=(10,10)) # rows, cols\n",
    "\n",
    "ax = regions_gdf.merge(spatial_coverage_df, on='id').plot(column='coverage', legend=True, legend_kwds={\"fmt\": \"{:.0f}aa\"})\n",
    "#ax.yaxis.set_major_formatter(mtick.PercentFormatter())\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "5be5f27e",
   "metadata": {},
   "outputs": [],
   "source": [
    "spatial_coverage = []\n",
    "for shape_id, name in districts.items():\n",
    "    dfx = df[df['shape_id'] == shape_id]\n",
    "    spatial_coverage.append({\n",
    "        'id': shape_id,\n",
    "        'coverage': dfx['coverage'].mean()\n",
    "    })\n",
    "spatial_coverage_df = pd.DataFrame(spatial_coverage)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "78c78f4e",
   "metadata": {},
   "outputs": [],
   "source": [
    "ax = districts_gdf.merge(spatial_coverage_df, on='id').plot(column='coverage', legend=True)\n",
    "#districts_gdf.plot(ax=ax, facecolor='none', ec='black')"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "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.9.14"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}