{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# YFCC100m visualization based on 100x100km grid (Mollweide) <a class=\"tocSkip\">"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "toc": true
   },
   "source": [
    "<h1>Table of Contents<span class=\"tocSkip\"></span></h1>\n",
    "<div class=\"toc\"><ul class=\"toc-item\"><li><span><a href=\"#Introduction\" data-toc-modified-id=\"Introduction-1\"><span class=\"toc-item-num\">1&nbsp;&nbsp;</span>Introduction</a></span></li><li><span><a href=\"#Preparations\" data-toc-modified-id=\"Preparations-2\"><span class=\"toc-item-num\">2&nbsp;&nbsp;</span>Preparations</a></span><ul class=\"toc-item\"><li><span><a href=\"#Parameters\" data-toc-modified-id=\"Parameters-2.1\"><span class=\"toc-item-num\">2.1&nbsp;&nbsp;</span>Parameters</a></span></li><li><span><a href=\"#Load-dependencies\" data-toc-modified-id=\"Load-dependencies-2.2\"><span class=\"toc-item-num\">2.2&nbsp;&nbsp;</span>Load dependencies</a></span><ul class=\"toc-item\"><li><span><a href=\"#Connect-to-database\" data-toc-modified-id=\"Connect-to-database-2.2.1\"><span class=\"toc-item-num\">2.2.1&nbsp;&nbsp;</span>Connect to database</a></span></li></ul></li><li><span><a href=\"#LBSN-structure-data-introduction\" data-toc-modified-id=\"LBSN-structure-data-introduction-2.3\"><span class=\"toc-item-num\">2.3&nbsp;&nbsp;</span>LBSN structure data introduction</a></span><ul class=\"toc-item\"><li><span><a href=\"#Get-data-from-db-and-write-to-CSV\" data-toc-modified-id=\"Get-data-from-db-and-write-to-CSV-2.3.1\"><span class=\"toc-item-num\">2.3.1&nbsp;&nbsp;</span>Get data from db and write to CSV</a></span></li><li><span><a href=\"#RAW-Questions\" data-toc-modified-id=\"RAW-Questions-2.3.2\"><span class=\"toc-item-num\">2.3.2&nbsp;&nbsp;</span>RAW Questions</a></span></li></ul></li><li><span><a href=\"#Create-Grid\" data-toc-modified-id=\"Create-Grid-2.4\"><span class=\"toc-item-num\">2.4&nbsp;&nbsp;</span>Create Grid</a></span><ul class=\"toc-item\"><li><span><a href=\"#Preview-Grid\" data-toc-modified-id=\"Preview-Grid-2.4.1\"><span class=\"toc-item-num\">2.4.1&nbsp;&nbsp;</span>Preview Grid</a></span></li></ul></li></ul></li><li><span><a href=\"#Prepare-binary-search\" data-toc-modified-id=\"Prepare-binary-search-3\"><span class=\"toc-item-num\">3&nbsp;&nbsp;</span>Prepare binary search</a></span><ul class=\"toc-item\"><li><span><a href=\"#Create-test-points\" data-toc-modified-id=\"Create-test-points-3.1\"><span class=\"toc-item-num\">3.1&nbsp;&nbsp;</span>Create test points</a></span><ul class=\"toc-item\"><li><span><a href=\"#Use-np.digitize()-to-assign-coordinates-to-the-grid\" data-toc-modified-id=\"Use-np.digitize()-to-assign-coordinates-to-the-grid-3.1.1\"><span class=\"toc-item-num\">3.1.1&nbsp;&nbsp;</span>Use np.digitize() to assign coordinates to the grid</a></span></li><li><span><a href=\"#Highlight-Testpoint-in-Grid\" data-toc-modified-id=\"Highlight-Testpoint-in-Grid-3.1.2\"><span class=\"toc-item-num\">3.1.2&nbsp;&nbsp;</span>Highlight Testpoint in Grid</a></span></li><li><span><a href=\"#Preview-map-with-testpoint-and-assigned-bin\" data-toc-modified-id=\"Preview-map-with-testpoint-and-assigned-bin-3.1.3\"><span class=\"toc-item-num\">3.1.3&nbsp;&nbsp;</span>Preview map with testpoint and assigned bin</a></span></li></ul></li><li><span><a href=\"#Prepare-functions\" data-toc-modified-id=\"Prepare-functions-3.2\"><span class=\"toc-item-num\">3.2&nbsp;&nbsp;</span>Prepare functions</a></span></li><li><span><a href=\"#Test-with-LBSN-data\" data-toc-modified-id=\"Test-with-LBSN-data-3.3\"><span class=\"toc-item-num\">3.3&nbsp;&nbsp;</span>Test with LBSN data</a></span><ul class=\"toc-item\"><li><span><a href=\"#Load-data\" data-toc-modified-id=\"Load-data-3.3.1\"><span class=\"toc-item-num\">3.3.1&nbsp;&nbsp;</span>Load data</a></span></li><li><span><a href=\"#Project-coordinates-to-Mollweide\" data-toc-modified-id=\"Project-coordinates-to-Mollweide-3.3.2\"><span class=\"toc-item-num\">3.3.2&nbsp;&nbsp;</span>Project coordinates to Mollweide</a></span></li><li><span><a href=\"#Perform-the-bin-assignment\" data-toc-modified-id=\"Perform-the-bin-assignment-3.3.3\"><span class=\"toc-item-num\">3.3.3&nbsp;&nbsp;</span>Perform the bin assignment</a></span></li></ul></li><li><span><a href=\"#A:-Post-Count-per-grid\" data-toc-modified-id=\"A:-Post-Count-per-grid-3.4\"><span class=\"toc-item-num\">3.4&nbsp;&nbsp;</span>A: Post Count per grid</a></span><ul class=\"toc-item\"><li><span><a href=\"#Preview-post-count-map\" data-toc-modified-id=\"Preview-post-count-map-3.4.1\"><span class=\"toc-item-num\">3.4.1&nbsp;&nbsp;</span>Preview post count map</a></span></li></ul></li><li><span><a href=\"#B:-User-Count-per-grid\" data-toc-modified-id=\"B:-User-Count-per-grid-3.5\"><span class=\"toc-item-num\">3.5&nbsp;&nbsp;</span>B: User Count per grid</a></span><ul class=\"toc-item\"><li><span><a href=\"#Perform-the-bin-assignment-and-count-distinct-users\" data-toc-modified-id=\"Perform-the-bin-assignment-and-count-distinct-users-3.5.1\"><span class=\"toc-item-num\">3.5.1&nbsp;&nbsp;</span>Perform the bin assignment and count distinct users</a></span></li><li><span><a href=\"#Preview-user-count-map\" data-toc-modified-id=\"Preview-user-count-map-3.5.2\"><span class=\"toc-item-num\">3.5.2&nbsp;&nbsp;</span>Preview user count map</a></span></li></ul></li><li><span><a href=\"#C:-User-Days\" data-toc-modified-id=\"C:-User-Days-3.6\"><span class=\"toc-item-num\">3.6&nbsp;&nbsp;</span>C: User Days</a></span></li></ul></li><li><span><a href=\"#Prepare-methods\" data-toc-modified-id=\"Prepare-methods-4\"><span class=\"toc-item-num\">4&nbsp;&nbsp;</span>Prepare methods</a></span></li><li><span><a href=\"#Plotting-worldmaps:-Post-Count,-User-Count-and-User-Days\" data-toc-modified-id=\"Plotting-worldmaps:-Post-Count,-User-Count-and-User-Days-5\"><span class=\"toc-item-num\">5&nbsp;&nbsp;</span>Plotting worldmaps: Post Count, User Count and User Days</a></span></li><li><span><a href=\"#Save-&amp;-load-intermediate-data\" data-toc-modified-id=\"Save-&amp;-load-intermediate-data-6\"><span class=\"toc-item-num\">6&nbsp;&nbsp;</span>Save &amp; load intermediate data</a></span><ul class=\"toc-item\"><li><span><a href=\"#Load-&amp;-store-results-from-and-to-CSV\" data-toc-modified-id=\"Load-&amp;-store-results-from-and-to-CSV-6.1\"><span class=\"toc-item-num\">6.1&nbsp;&nbsp;</span>Load &amp; store results from and to CSV</a></span></li><li><span><a href=\"#Load-&amp;-plot-pickled-dataframe\" data-toc-modified-id=\"Load-&amp;-plot-pickled-dataframe-6.2\"><span class=\"toc-item-num\">6.2&nbsp;&nbsp;</span>Load &amp; plot pickled dataframe</a></span></li></ul></li><li><span><a href=\"#Close-DB-connection-&amp;-Create-notebook-HTML\" data-toc-modified-id=\"Close-DB-connection-&amp;-Create-notebook-HTML-7\"><span class=\"toc-item-num\">7&nbsp;&nbsp;</span>Close DB connection &amp; Create notebook HTML</a></span></li></ul></div>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Introduction\n",
    "\n",
    "Based on data from YFCC100m dataset, this Notebook explores a processing example for visualizing frequentation patterns in a 100x100km Grid (worldwide). The data used here was converted from YFCC CSVs to the [raw lbsn structure](https://gitlab.vgiscience.de/lbsn/databases/rawdb) using [lbsntransform](https://lbsn.vgiscience.org/lbsntransform/docs/) package.\n",
    "\n",
    "Our goal was to illustrate a complete typical visualization pipeline, from reading data to processing to visualization. There're additional steps included such as archiving intermediate results or creating an alternative interactive visualization.\n",
    "\n",
    "This is the second notebook in a series of four notebooks:\n",
    "\n",
    "* 1) the [Preparations (01_preparations.ipynb)](01_preparations.html) Preparations for importing and pre-processing YFCC100m.\n",
    "* 2) the [RAW Notebook (02_yfcc_gridagg_raw.ipynb)](02_yfcc_gridagg_raw.html) demonstrates how a typical grid-based visualization looks like when using the **raw** lbsn structure and  \n",
    "* 3) the [HLL Notebook (03_yfcc_gridagg_hll.ipynb)](03_yfcc_gridagg_hll.html) demonstrates the same visualization using the privacy-aware **hll** lbsn structure  \n",
    "* 4) the [Interpretation (04_interpretation.ipynb)](04_interpretation.html) illustrates how to create interactive graphics for comparison of raw and hll results; intersection of published data.\n",
    "\n",
    "In this Notebook, we describe a complete visualization pipeline, exploring worldwide frequentation patterns from YFCC dataset based on a 100x100km grid. The following steps are some of the parts explained:\n",
    "\n",
    "* get data from LBSN raw db (PostgreSQL select)  \n",
    "* store raw data to CSV, load from CSV  \n",
    "* create a parametrized world-wide grid  \n",
    "* implement a binary search for fast mapping of coordinates to grid-bins \n",
    "* perform the bin-assignment with actual coordinates from Flickr YFCC dataset  \n",
    "* chunk processing into smaller parts, to reduce memory load  \n",
    "* summarize different metrics for bins: postcount, usercount, userdays  \n",
    "* create methods to reduce from individual code parts  \n",
    "* measure timing of different steps, to compare processing time with hll-dataset approach  \n",
    "* load and store intermediate results from and to \\*.pickle and \\*.CSV\n",
    "\n",
    "**System requirements**\n",
    "\n",
    "The raw notebook requires about **16 GB of Memory**, the hll notebook about 8 GB.\n",
    "\n",
    "**Additional notes:**\n",
    "\n",
    "Use **Shift+Enter** to walk through the Notebook"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Preparations"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Parameters\n",
    "\n",
    "This is a collection of parameters that affect processing and graphics."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "from pathlib import Path\n",
    "\n",
    "GRID_SIZE_METERS = 100000 # the size of grid cells in meters \n",
    "                          # (spatial accuracy of worldwide measurement)\n",
    "# process x number of hll records per chunk.\n",
    "# Increasing this number will consume more memory,\n",
    "# but reduce processing time because less SQL queries\n",
    "# are needed.\n",
    "CHUNK_SIZE = 5000000                              \n",
    "# target projection: Mollweide (epsg code)\n",
    "EPSG_CODE = 54009\n",
    "# note: Mollweide defined by _esri_\n",
    "# in epsg.io's database\n",
    "CRS_PROJ = f\"esri:{EPSG_CODE}\"\n",
    "# Input projection (Web Mercator)\n",
    "CRS_WGS = \"epsg:4326\"\n",
    "# define path to output directory (figures etc.)\n",
    "OUTPUT = Path.cwd().parents[0] / \"out\""
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Load dependencies\n",
    "\n",
    "Load all dependencies at once, as a means to verify that everything required to run this notebook is available."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "import os\n",
    "import csv\n",
    "import sys\n",
    "import math\n",
    "import colorcet\n",
    "import psycopg2\n",
    "import holoviews as hv\n",
    "import mapclassify as mc\n",
    "import geopandas as gp\n",
    "import pandas as pd\n",
    "import numpy as np\n",
    "import matplotlib.pyplot as plt\n",
    "from pathlib import Path\n",
    "from typing import List, Tuple, Dict, Union, Generator, Optional\n",
    "from pyproj import Transformer, CRS, Proj\n",
    "from geoviews import opts\n",
    "from shapely.geometry import shape, Point, Polygon\n",
    "from shapely.ops import transform\n",
    "from cartopy import crs\n",
    "from matplotlib import colors\n",
    "from IPython.display import clear_output, display, HTML, Markdown\n",
    "# optionally, enable shapely.speedups \n",
    "# which makes some of the spatial \n",
    "# queries running faster\n",
    "import shapely.speedups as speedups\n",
    "speedups.enable()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Load helper module from `../py/module/tools.py`. This also allows to import code from other jupyter notebooks, synced to `*.py` with jupytext."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {
    "tags": [
     "active-ipynb"
    ]
   },
   "outputs": [],
   "source": [
    "module_path = str(Path.cwd().parents[0] / \"py\")\n",
    "if module_path not in sys.path:\n",
    "    sys.path.append(module_path)\n",
    "from modules import tools, preparations"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Set pandas colwidth."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {
    "tags": [
     "active-ipynb"
    ]
   },
   "outputs": [],
   "source": [
    "pd.set_option('display.max_colwidth', 25)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Activate autoreload of changed python files:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [],
   "source": [
    "%load_ext autoreload\n",
    "%autoreload 2"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Load memory profiler extension"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {
    "tags": [
     "active-ipynb"
    ]
   },
   "outputs": [],
   "source": [
    "%load_ext memory_profiler"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Plot used package versions for future use:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {
    "tags": [
     "hide_code",
     "active-ipynb"
    ]
   },
   "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>package</th>\n",
       "      <th>xarray</th>\n",
       "      <th>Shapely</th>\n",
       "      <th>pyproj</th>\n",
       "      <th>pandas</th>\n",
       "      <th>numpy</th>\n",
       "      <th>matplotlib</th>\n",
       "      <th>mapclassify</th>\n",
       "      <th>ipython</th>\n",
       "      <th>holoviews</th>\n",
       "      <th>geoviews</th>\n",
       "      <th>geopandas</th>\n",
       "      <th>Fiona</th>\n",
       "      <th>Cartopy</th>\n",
       "      <th>bokeh</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>version</th>\n",
       "      <td>0.16.1</td>\n",
       "      <td>1.7.1</td>\n",
       "      <td>2.6.1.post1</td>\n",
       "      <td>1.1.2</td>\n",
       "      <td>1.19.1</td>\n",
       "      <td>3.3.2</td>\n",
       "      <td>2.3.0</td>\n",
       "      <td>7.18.1</td>\n",
       "      <td>1.13.4</td>\n",
       "      <td>1.8.1</td>\n",
       "      <td>0.8.1</td>\n",
       "      <td>1.8.17</td>\n",
       "      <td>0.18.0</td>\n",
       "      <td>2.2.1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "package  xarray Shapely       pyproj pandas   numpy matplotlib mapclassify  \\\n",
       "version  0.16.1   1.7.1  2.6.1.post1  1.1.2  1.19.1      3.3.2       2.3.0   \n",
       "\n",
       "package ipython holoviews geoviews geopandas   Fiona Cartopy  bokeh  \n",
       "version  7.18.1    1.13.4    1.8.1     0.8.1  1.8.17  0.18.0  2.2.1  "
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "root_packages = [\n",
    "        'geoviews', 'geopandas', 'pandas', 'numpy', 'cloudpickle',\n",
    "        'matplotlib', 'shapely', 'cartopy', 'holoviews',\n",
    "        'mapclassify', 'fiona', 'bokeh', 'pyproj', 'ipython',\n",
    "        'jupyterlab', 'xarray']\n",
    "preparations.package_report(root_packages)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Connect to database\n",
    "\n",
    "Password is loaded from `.env` file specified in container setup [hlldb](https://gitlab.vgiscience.de/lbsn/databases/hlldb).\n",
    "\n",
    "The docker stack contains a full backup of the YFCC database converted to the privacy-aware datastructure. In this Notebook, we're only working with a small part of the data from the table `spatial.latlng`."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Define credentials as environment variables"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {
    "tags": [
     "active-ipynb"
    ]
   },
   "outputs": [],
   "source": [
    "db_user = \"postgres\"\n",
    "db_pass = os.getenv('POSTGRES_PASSWORD')\n",
    "# set connection variables\n",
    "db_host = \"rawdb\"\n",
    "db_port = \"5432\"\n",
    "db_name = \"rawdb\""
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Connect to empty Postgres database running HLL Extension. Note that only `readonly` privileges are needed.\n",
    "\n",
    "is defined as a global variable, for simplicity, to make it available in all functions."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {
    "tags": [
     "active-ipynb"
    ]
   },
   "outputs": [],
   "source": [
    "db_connection = psycopg2.connect(\n",
    "        host=db_host,\n",
    "        port=db_port,\n",
    "        dbname=db_name,\n",
    "        user=db_user,\n",
    "        password=db_pass\n",
    ")\n",
    "db_connection.set_session(readonly=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Test connection:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {
    "tags": [
     "active-ipynb"
    ]
   },
   "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>?column?</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   ?column?\n",
       "0         1"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "db_query = \"\"\"\n",
    "    SELECT 1;\n",
    "\"\"\"\n",
    "# create pandas DataFrame from database data\n",
    "df = pd.read_sql_query(db_query, db_connection)\n",
    "display(df.head())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "For simplicity, the db connection parameters and query are stored in a class:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {
    "tags": [
     "active-ipynb"
    ]
   },
   "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>?column?</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   ?column?\n",
       "0         1"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "db_conn = tools.DbConn(db_connection)\n",
    "db_conn.query(\"SELECT 1\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## LBSN structure data introduction\n",
    "\n",
    "The [Location Based Social Network (LBSN) structure](https://lbsn.vgiscience.org/) was developed as a standardized conceptual data model for analyzing, comparing and relating information of different LBSN in visual analytics research and beyond. The primary goal is to systematically characterize LBSN data aspects in a common scheme that enables privacy-by-design for connected software, data handling and information visualization.\n",
    "\n",
    "**Modular design**\n",
    "\n",
    "The core lbsn structure is described in a platform independent [Protocol Buffers](https://developers.google.com/protocol-buffers/) file. The [Proto file](https://gitlab.vgiscience.de/lbsn/structure/protobuf/) can be used to compile and implement the proposed structure in any language such as Python, Java or C++.\n",
    "\n",
    "This structure is tightly coupled with a relational datascheme (Postgres SQL) that is maintained [separately](https://gitlab.vgiscience.de/lbsn/structure/rawdb/), inluding a [privacy-aware version](https://gitlab.vgiscience.de/lbsn/structure/hlldb/) that can be used for visualization purposes. The database is ready to use with several provided [Docker containers](https://gitlab.vgiscience.de/lbsn/tools/full-stack-lbsn) that optionally include a PGadmin interface.\n",
    "\n",
    "A documentation of the LBSN structure components is available at [https://lbsn.vgiscience.org/structure/](https://lbsn.vgiscience.org/structure/)."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "First, some statistics for the data we're working with.\n",
    "\n",
    "<div class=\"alert alert-warning\">\n",
    "Note that the following two queries take about 10 Minutes each. They're not necessary to run the notebook.\n",
    "</div>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {
    "tags": [
     "active-ipynb"
    ]
   },
   "outputs": [
    {
     "data": {
      "text/markdown": [
       "There're <strong style='color: red;'>100,000,000</strong> distinct records (Flickr photo posts) in this table."
      ],
      "text/plain": [
       "<IPython.core.display.Markdown object>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "CPU times: user 24.5 ms, sys: 18.2 ms, total: 42.6 ms\n",
      "Wall time: 6min 31s\n"
     ]
    }
   ],
   "source": [
    "%%time\n",
    "db_query = \"\"\"\n",
    "    SELECT count(*) FROM topical.post;\n",
    "\"\"\"\n",
    "\n",
    "display(Markdown(\n",
    "    f\"There're \"\n",
    "    f\"<strong style='color: red;'>\"\n",
    "    f\"{db_conn.query(db_query)['count'][0]:,.0f}</strong> \"\n",
    "    f\"distinct records (Flickr photo posts) in this table.\"))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The Flickt YFCC 100M dataset includes 99,206,564 photos and 793,436 videos from 581,099 \n",
    "different photographers, and 48,469,829 of those are geotagged [[1]](https://code.flickr.net/category/geo/).\n",
    "\n",
    "Photos are available in schema `topical` and table `post`."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "With a query `get_stats_query` defined in `tools` module, we can get a more fine grained output of statistics for this table:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {
    "tags": [
     "active-ipynb"
    ]
   },
   "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>metric</th>\n",
       "      <th>bytes/ct</th>\n",
       "      <th>bytes_pretty</th>\n",
       "      <th>bytes_per_row</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>core_relation_size</td>\n",
       "      <td>48428793856</td>\n",
       "      <td>45 GB</td>\n",
       "      <td>484.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>visibility_map</td>\n",
       "      <td>0</td>\n",
       "      <td>0 bytes</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>free_space_map</td>\n",
       "      <td>11919360</td>\n",
       "      <td>11 MB</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>table_size_incl_toast</td>\n",
       "      <td>49340284928</td>\n",
       "      <td>46 GB</td>\n",
       "      <td>493.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>indexes_size</td>\n",
       "      <td>26507223040</td>\n",
       "      <td>25 GB</td>\n",
       "      <td>265.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>total_size_incl_toast_and_indexes</td>\n",
       "      <td>75847507968</td>\n",
       "      <td>71 GB</td>\n",
       "      <td>758.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>live_rows_in_text_representation</td>\n",
       "      <td>42719824906</td>\n",
       "      <td>40 GB</td>\n",
       "      <td>427.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>------------------------------</td>\n",
       "      <td>0</td>\n",
       "      <td>None</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>row_count</td>\n",
       "      <td>100000000</td>\n",
       "      <td>None</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>live_tuples</td>\n",
       "      <td>0</td>\n",
       "      <td>None</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>dead_tuples</td>\n",
       "      <td>0</td>\n",
       "      <td>None</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                               metric     bytes/ct bytes_pretty  bytes_per_row\n",
       "0                  core_relation_size  48428793856        45 GB          484.0\n",
       "1                      visibility_map            0      0 bytes            0.0\n",
       "2                      free_space_map     11919360        11 MB            0.0\n",
       "3               table_size_incl_toast  49340284928        46 GB          493.0\n",
       "4                        indexes_size  26507223040        25 GB          265.0\n",
       "5   total_size_incl_toast_and_indexes  75847507968        71 GB          758.0\n",
       "6    live_rows_in_text_representation  42719824906        40 GB          427.0\n",
       "7      ------------------------------            0         None            NaN\n",
       "8                           row_count    100000000         None            NaN\n",
       "9                         live_tuples            0         None            NaN\n",
       "10                        dead_tuples            0         None            NaN"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "CPU times: user 60.7 ms, sys: 41.3 ms, total: 102 ms\n",
      "Wall time: 13min 37s\n"
     ]
    }
   ],
   "source": [
    "%%time\n",
    "db_query = tools.get_stats_query(\"topical.post\")\n",
    "stats_df = db_conn.query(db_query)\n",
    "stats_df[\"bytes/ct\"] = stats_df[\"bytes/ct\"].fillna(0).astype('int64')\n",
    "display(stats_df)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Data structure preview (get random 10 records):"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {
    "tags": [
     "active-ipynb"
    ]
   },
   "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>origin_id</th>\n",
       "      <th>post_guid</th>\n",
       "      <th>post_latlng</th>\n",
       "      <th>place_guid</th>\n",
       "      <th>city_guid</th>\n",
       "      <th>country_guid</th>\n",
       "      <th>user_guid</th>\n",
       "      <th>post_publish_date</th>\n",
       "      <th>post_body</th>\n",
       "      <th>post_geoaccuracy</th>\n",
       "      <th>...</th>\n",
       "      <th>post_url</th>\n",
       "      <th>post_type</th>\n",
       "      <th>post_filter</th>\n",
       "      <th>post_quote_count</th>\n",
       "      <th>post_share_count</th>\n",
       "      <th>post_language</th>\n",
       "      <th>input_source</th>\n",
       "      <th>user_mentions</th>\n",
       "      <th>modified</th>\n",
       "      <th>post_content_license</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2</td>\n",
       "      <td>6985418911</td>\n",
       "      <td>0101000020E61000009F02603C837354C0198D7C5EF18C...</td>\n",
       "      <td>56558566</td>\n",
       "      <td>12772085</td>\n",
       "      <td>56043648</td>\n",
       "      <td>39089491@N00</td>\n",
       "      <td>2012-03-15 20:41:23</td>\n",
       "      <td>None</td>\n",
       "      <td>place</td>\n",
       "      <td>...</td>\n",
       "      <td>http://www.flickr.com/photos/39089491@N00/6985...</td>\n",
       "      <td>image</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>[]</td>\n",
       "      <td>2020-02-15 05:09:05.922172</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>10201275523</td>\n",
       "      <td>0101000020E6100000494DBB9866D753C08BF9B9A129D3...</td>\n",
       "      <td>55855853</td>\n",
       "      <td>12697691</td>\n",
       "      <td>56043697</td>\n",
       "      <td>55289779@N00</td>\n",
       "      <td>2013-10-11 06:10:28</td>\n",
       "      <td>None</td>\n",
       "      <td>latlng</td>\n",
       "      <td>...</td>\n",
       "      <td>http://www.flickr.com/photos/55289779@N00/1020...</td>\n",
       "      <td>image</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>[]</td>\n",
       "      <td>2020-02-15 05:09:05.922172</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2</td>\n",
       "      <td>7289030198</td>\n",
       "      <td>0101000020E6100000C7D79E59127F52C0A7ECF483BA5E...</td>\n",
       "      <td>28751381</td>\n",
       "      <td>12761347</td>\n",
       "      <td>56043648</td>\n",
       "      <td>53430201@N03</td>\n",
       "      <td>2012-05-28 21:26:39</td>\n",
       "      <td>None</td>\n",
       "      <td>latlng</td>\n",
       "      <td>...</td>\n",
       "      <td>http://www.flickr.com/photos/53430201@N03/7289...</td>\n",
       "      <td>image</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>[]</td>\n",
       "      <td>2020-02-15 05:09:05.922172</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2</td>\n",
       "      <td>4572998878</td>\n",
       "      <td>0101000020E6100000EC1516DC0FC351C0E544BB0A292B...</td>\n",
       "      <td>2496012</td>\n",
       "      <td>12758726</td>\n",
       "      <td>56043648</td>\n",
       "      <td>78969707@N00</td>\n",
       "      <td>2010-05-03 01:37:20</td>\n",
       "      <td>Greeny's, Christines, Andy, Jeeane and Lou's p...</td>\n",
       "      <td>latlng</td>\n",
       "      <td>...</td>\n",
       "      <td>http://www.flickr.com/photos/78969707@N00/4572...</td>\n",
       "      <td>image</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>[]</td>\n",
       "      <td>2020-02-15 05:09:05.922172</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2</td>\n",
       "      <td>3973434963</td>\n",
       "      <td>0101000020E6100000D20149D8B793D8BFD670917BBABC...</td>\n",
       "      <td>20220221</td>\n",
       "      <td>20080321</td>\n",
       "      <td>56043644</td>\n",
       "      <td>71322403@N00</td>\n",
       "      <td>2009-10-02 11:04:00</td>\n",
       "      <td>None</td>\n",
       "      <td>latlng</td>\n",
       "      <td>...</td>\n",
       "      <td>http://www.flickr.com/photos/71322403@N00/3973...</td>\n",
       "      <td>image</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>[]</td>\n",
       "      <td>2020-02-15 05:09:05.922172</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 28 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "   origin_id    post_guid                                        post_latlng  \\\n",
       "0          2   6985418911  0101000020E61000009F02603C837354C0198D7C5EF18C...   \n",
       "1          2  10201275523  0101000020E6100000494DBB9866D753C08BF9B9A129D3...   \n",
       "2          2   7289030198  0101000020E6100000C7D79E59127F52C0A7ECF483BA5E...   \n",
       "3          2   4572998878  0101000020E6100000EC1516DC0FC351C0E544BB0A292B...   \n",
       "4          2   3973434963  0101000020E6100000D20149D8B793D8BFD670917BBABC...   \n",
       "\n",
       "  place_guid city_guid country_guid     user_guid   post_publish_date  \\\n",
       "0   56558566  12772085     56043648  39089491@N00 2012-03-15 20:41:23   \n",
       "1   55855853  12697691     56043697  55289779@N00 2013-10-11 06:10:28   \n",
       "2   28751381  12761347     56043648  53430201@N03 2012-05-28 21:26:39   \n",
       "3    2496012  12758726     56043648  78969707@N00 2010-05-03 01:37:20   \n",
       "4   20220221  20080321     56043644  71322403@N00 2009-10-02 11:04:00   \n",
       "\n",
       "                                           post_body post_geoaccuracy  ...  \\\n",
       "0                                               None            place  ...   \n",
       "1                                               None           latlng  ...   \n",
       "2                                               None           latlng  ...   \n",
       "3  Greeny's, Christines, Andy, Jeeane and Lou's p...           latlng  ...   \n",
       "4                                               None           latlng  ...   \n",
       "\n",
       "                                            post_url post_type post_filter  \\\n",
       "0  http://www.flickr.com/photos/39089491@N00/6985...     image        None   \n",
       "1  http://www.flickr.com/photos/55289779@N00/1020...     image        None   \n",
       "2  http://www.flickr.com/photos/53430201@N03/7289...     image        None   \n",
       "3  http://www.flickr.com/photos/78969707@N00/4572...     image        None   \n",
       "4  http://www.flickr.com/photos/71322403@N00/3973...     image        None   \n",
       "\n",
       "  post_quote_count post_share_count post_language input_source user_mentions  \\\n",
       "0             None             None          None         None            []   \n",
       "1             None             None          None         None            []   \n",
       "2             None             None          None         None            []   \n",
       "3             None             None          None         None            []   \n",
       "4             None             None          None         None            []   \n",
       "\n",
       "                    modified post_content_license  \n",
       "0 2020-02-15 05:09:05.922172                    3  \n",
       "1 2020-02-15 05:09:05.922172                    5  \n",
       "2 2020-02-15 05:09:05.922172                    3  \n",
       "3 2020-02-15 05:09:05.922172                    3  \n",
       "4 2020-02-15 05:09:05.922172                    1  \n",
       "\n",
       "[5 rows x 28 columns]"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "db_query = \"SELECT * FROM topical.post WHERE post_geoaccuracy != 'unknown' LIMIT 5;\"\n",
    "first_10_df = db_conn.query(db_query)\n",
    "display(first_10_df)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Get data from db and write to CSV\n",
    "\n",
    "To [speed up processing](https://stackoverflow.com/questions/43874559/pandas-is-faster-to-load-csv-than-sql/50876534#50876534) in this notebook, we're going to work on a CSV file instead of live data retrieved from the database. The yfcc raw db contains many attributes, for the visualization and metrics used in this notebook, we only need the following attributes:  \n",
    "* latitude and longitude coordinates of geotagged yfcc photos, to bin coordinates to the grid and counting number of posts  \n",
    "* the user_guid, to count distinct users  \n",
    "* the date of photo creation, to count distinct userdays\n",
    "\n",
    "<div class=\"alert alert-success\">\n",
    "To make processing of raw data comparable to hll data processing, we're also reducing the accuracy of lat/lng coordinates with a GeoHash Precision of 5 to about 4 km spatial accuracy. Similarly, we reduce temporal granularity to dates, because time is not needed for measuring userdays. Such considerations benefit both privacy and ease of processing.\n",
    "</div>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [],
   "source": [
    "def get_yfccposts_fromdb(\n",
    "        chunk_size: int = CHUNK_SIZE) -> List[pd.DataFrame]:\n",
    "    \"\"\"Returns YFCC posts from db\"\"\"\n",
    "    sql = f\"\"\"\n",
    "    SELECT  ST_Y(ST_PointFromGeoHash(ST_GeoHash(t1.post_latlng, 5), 5)) As \"latitude\", \n",
    "            ST_X(ST_PointFromGeoHash(ST_GeoHash(t1.post_latlng, 5), 5)) As \"longitude\",\n",
    "            t1.user_guid,\n",
    "            to_char(t1.post_create_date, 'yyyy-MM-dd') As \"post_create_date\"\n",
    "    FROM topical.post t1\n",
    "    WHERE\n",
    "    NOT ((ST_Y(t1.post_latlng) = 0) AND (ST_X(t1.post_latlng) = 0))\n",
    "    AND\n",
    "    t1.post_geoaccuracy IN ('place', 'latlng', 'city');\n",
    "    \"\"\"\n",
    "    # execute query, enable chunked return\n",
    "    return pd.read_sql(sql, con=db_connection, chunksize=chunk_size)\n",
    "\n",
    "def write_chunkeddf_tocsv(\n",
    "    filename: str, usecols: List[str], chunked_df: List[pd.DataFrame],\n",
    "    chunk_size: int = CHUNK_SIZE, output: Path = OUTPUT):\n",
    "    \"\"\"Write chunked dataframe to CSV\"\"\"\n",
    "    for ix, chunk_df in enumerate(chunked_df):\n",
    "        mode = 'a'\n",
    "        header = False\n",
    "        if ix == 0:\n",
    "            mode = 'w'\n",
    "            header = True\n",
    "        chunk_df.to_csv(\n",
    "            output / \"csv\" / filename,\n",
    "            mode=mode, columns=usecols,\n",
    "            index=False, header=header)\n",
    "        clear_output(wait=True)\n",
    "        display(\n",
    "            f'Stored {(ix*chunk_size)+len(chunk_df)} '\n",
    "            f'posts to CSV..')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The sql explained:\n",
    "\n",
    "```sql\n",
    "SELECT  ST_Y(ST_PointFromGeoHash(ST_GeoHash(t1.post_latlng, 5), 5)) As \"latitude\",  -- lat and long coordinates from\n",
    "        ST_X(ST_PointFromGeoHash(ST_GeoHash(t1.post_latlng, 5), 5)) As \"longitude\", -- PostGis geometry, with GeoHash 5\n",
    "        t1.user_guid,                                                    -- the user_guid from Flickr (yfcc100m)\n",
    "        to_char(t1.post_create_date, 'yyyy-MM-dd') As \"post_create_date\" -- the photo's date of creation, without time, \n",
    "                                                                         -- to count distinct days\n",
    "FROM topical.post t1                                                     -- the table reference from lbsn raw:\n",
    "                                                                         -- scheme (facet) = \"topical\", table = \"post\"\n",
    "WHERE\n",
    "NOT ((ST_Y(t1.post_latlng) = 0) AND (ST_X(t1.post_latlng) = 0))          -- excluding Null Island\n",
    "AND\n",
    "t1.post_geoaccuracy IN ('place', 'latlng', 'city');                      -- lbsn raw geoaccuracy classes,\n",
    "                                                                         -- equals Flickr geoaccuracy levels 8-16*\n",
    "```\n",
    "\n",
    "\\* The maximum resolution for maps will be 50 or 100km raster, therefore 8 (==`city` in lbsn raw structure) appears to be a reasonable choice. Also see [Flickr yfcc to raw lbsn mapping](https://gitlab.vgiscience.de/lbsn/lbsntransform/blob/master/lbsntransform/classes/field_mapping_yfcc100m.py#L343)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Execute query:**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {
    "tags": [
     "active-ipynb"
    ]
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'Stored 47843599 posts to CSV..'"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "CPU times: user 7min 45s, sys: 38 s, total: 8min 23s\n",
      "Wall time: 18min 55s\n"
     ]
    }
   ],
   "source": [
    "%%time\n",
    "filename = \"yfcc_posts.csv\"\n",
    "usecols = [\"latitude\", \"longitude\", \"user_guid\", \"post_create_date\"]\n",
    "if Path(OUTPUT / \"csv\" / filename).exists():\n",
    "        print(f\"CSV already exists, skipping load from db..\")\n",
    "else:\n",
    "    write_chunkeddf_tocsv(\n",
    "        chunked_df=get_yfccposts_fromdb(),\n",
    "        filename=filename,\n",
    "        usecols=usecols)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**RAW file size:**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {
    "tags": [
     "active-ipynb"
    ]
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Size: 2494.16 MB\n"
     ]
    }
   ],
   "source": [
    "raw_size_mb = Path(OUTPUT / \"csv\" / \"yfcc_posts.csv\").stat().st_size / (1024*1024)\n",
    "print(f\"Size: {raw_size_mb:.2f} MB\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### RAW Questions\n",
    "\n",
    "To anticipate some questions or assumptions:\n",
    "\n",
    "**Why do I need a DB connection to get yfcc data, the original yfcc files are available as CSV?**\n",
    "\n",
    "> YFCC original CSVs are formatted in a custom format. LBSN raw structure offers a systematic data scheme for handling of Social Media data such as yfcc. The database also allows us to better illustrate how to limit the query to only the data that is needed."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Create Grid"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "1. Define Mollweide crs string for pyproj/Proj4 and WGS1984 for Social Media imports"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [],
   "source": [
    "# define Transformer ahead of time\n",
    "# with xy-order of coordinates\n",
    "PROJ_TRANSFORMER = Transformer.from_crs(\n",
    "    CRS_WGS, CRS_PROJ, always_xy=True)\n",
    "\n",
    "# also define reverse projection\n",
    "PROJ_TRANSFORMER_BACK = Transformer.from_crs(\n",
    "    CRS_PROJ, CRS_WGS, always_xy=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "2. create bounds from WGS1984 and project to Mollweide"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [],
   "source": [
    "XMIN = PROJ_TRANSFORMER.transform(\n",
    "    -180, 0)[0]\n",
    "XMAX = PROJ_TRANSFORMER.transform(\n",
    "    180, 0)[0]\n",
    "YMAX = PROJ_TRANSFORMER.transform(\n",
    "    0, 90)[1]\n",
    "YMIN = PROJ_TRANSFORMER.transform(\n",
    "    0, -90)[1]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {
    "tags": [
     "active-ipynb"
    ]
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Projected bounds: [-18040095.696147293, -9020047.848073646, 18040095.696147293, 9020047.848073646]\n"
     ]
    }
   ],
   "source": [
    "print(f'Projected bounds: {[XMIN, YMIN, XMAX, YMAX]}')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "3. Create 100x100 km (e.g.) Grid"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [],
   "source": [
    "def create_grid_df(\n",
    "    grid_size: int = GRID_SIZE_METERS,\n",
    "    xmin: float = XMIN,\n",
    "    ymin: float = YMIN, \n",
    "    xmax: float = XMAX, \n",
    "    ymax: float = YMAX,\n",
    "    report: bool = None,\n",
    "    return_rows_cols: bool = None):\n",
    "    \"\"\"Creates dataframe polygon grid based on width and length in Meters\"\"\"\n",
    "    width = grid_size\n",
    "    length = grid_size\n",
    "    cols = list(range(int(np.floor(xmin)), int(np.ceil(xmax)), width))\n",
    "    rows = list(range(int(np.floor(ymin)), int(np.ceil(ymax)), length))\n",
    "    if report:\n",
    "        print(len(cols))\n",
    "        print(len(rows))\n",
    "    rows.reverse()\n",
    "    \n",
    "    polygons = []\n",
    "    for x in cols:\n",
    "        for y in rows:\n",
    "            # combine to tuple: (x,y, poly)\n",
    "            # and append to list\n",
    "            polygons.append(\n",
    "                (x, y,\n",
    "                 Polygon([\n",
    "                     (x, y),\n",
    "                     (x+width, y),\n",
    "                     (x+width, y-length),\n",
    "                     (x, y-length)])))\n",
    "    # create a pandas dataframe\n",
    "    # from list of tuples\n",
    "    grid = pd.DataFrame(polygons)\n",
    "    # name columns\n",
    "    col_labels=['xbin', 'ybin', 'bin_poly']\n",
    "    grid.columns = col_labels\n",
    "    # use x and y as index columns\n",
    "    grid.set_index(['xbin', 'ybin'], inplace=True)\n",
    "    if return_rows_cols:\n",
    "        return grid, rows, cols\n",
    "    return grid"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {
    "tags": [
     "active-ipynb"
    ]
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "361\n",
      "181\n"
     ]
    }
   ],
   "source": [
    "grid, rows, cols = create_grid_df(\n",
    "    report=True, return_rows_cols=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {
    "tags": [
     "active-ipynb"
    ]
   },
   "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></th>\n",
       "      <th>bin_poly</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>xbin</th>\n",
       "      <th>ybin</th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th rowspan=\"5\" valign=\"top\">-18040096</th>\n",
       "      <th>8979952</th>\n",
       "      <td>POLYGON ((-18040096 8...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8879952</th>\n",
       "      <td>POLYGON ((-18040096 8...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8779952</th>\n",
       "      <td>POLYGON ((-18040096 8...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8679952</th>\n",
       "      <td>POLYGON ((-18040096 8...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8579952</th>\n",
       "      <td>POLYGON ((-18040096 8...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                   bin_poly\n",
       "xbin      ybin                             \n",
       "-18040096 8979952  POLYGON ((-18040096 8...\n",
       "          8879952  POLYGON ((-18040096 8...\n",
       "          8779952  POLYGON ((-18040096 8...\n",
       "          8679952  POLYGON ((-18040096 8...\n",
       "          8579952  POLYGON ((-18040096 8..."
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "grid.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Create a geodataframe from dataframe:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [],
   "source": [
    "def grid_to_gdf(\n",
    "    grid: pd.DataFrame, crs_proj: str = CRS_PROJ) -> gp.GeoDataFrame:\n",
    "    \"\"\"Convert grid pandas DataFrame to geopandas Geodataframe\"\"\"\n",
    "    grid = gp.GeoDataFrame(\n",
    "        grid.drop(\n",
    "            columns=[\"bin_poly\"]),\n",
    "            geometry=grid.bin_poly)\n",
    "    grid.crs = crs_proj\n",
    "    return grid"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {
    "tags": [
     "active-ipynb"
    ]
   },
   "outputs": [],
   "source": [
    "grid = grid_to_gdf(grid)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Add columns for aggregation"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [],
   "source": [
    "def reset_metrics(\n",
    "    grid: gp.GeoDataFrame,\n",
    "    metrics: List[str] = [\"postcount\", \"usercount\", \"userdays\"], setzero: bool = None):\n",
    "    \"\"\"Remove columns from GeoDataFrame and optionally fill with 0\"\"\"\n",
    "    for metric in metrics:\n",
    "        try:\n",
    "            grid.drop(metric, axis=1, inplace=True)\n",
    "            grid.drop(f'{metric}_cat', axis=1, inplace=True)\n",
    "        except KeyError:\n",
    "            pass\n",
    "        if setzero:\n",
    "            grid.loc[:, metric] = 0"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {
    "tags": [
     "active-ipynb"
    ]
   },
   "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></th>\n",
       "      <th>geometry</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>xbin</th>\n",
       "      <th>ybin</th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th rowspan=\"5\" valign=\"top\">-18040096</th>\n",
       "      <th>8979952</th>\n",
       "      <td>POLYGON ((-18040096.0...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8879952</th>\n",
       "      <td>POLYGON ((-18040096.0...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8779952</th>\n",
       "      <td>POLYGON ((-18040096.0...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8679952</th>\n",
       "      <td>POLYGON ((-18040096.0...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8579952</th>\n",
       "      <td>POLYGON ((-18040096.0...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"5\" valign=\"top\">17959904</th>\n",
       "      <th>-8620048</th>\n",
       "      <td>POLYGON ((17959904.00...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>-8720048</th>\n",
       "      <td>POLYGON ((17959904.00...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>-8820048</th>\n",
       "      <td>POLYGON ((17959904.00...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>-8920048</th>\n",
       "      <td>POLYGON ((17959904.00...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>-9020048</th>\n",
       "      <td>POLYGON ((17959904.00...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>65341 rows × 1 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                                    geometry\n",
       "xbin      ybin                              \n",
       "-18040096  8979952  POLYGON ((-18040096.0...\n",
       "           8879952  POLYGON ((-18040096.0...\n",
       "           8779952  POLYGON ((-18040096.0...\n",
       "           8679952  POLYGON ((-18040096.0...\n",
       "           8579952  POLYGON ((-18040096.0...\n",
       "...                                      ...\n",
       " 17959904 -8620048  POLYGON ((17959904.00...\n",
       "          -8720048  POLYGON ((17959904.00...\n",
       "          -8820048  POLYGON ((17959904.00...\n",
       "          -8920048  POLYGON ((17959904.00...\n",
       "          -9020048  POLYGON ((17959904.00...\n",
       "\n",
       "[65341 rows x 1 columns]"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "reset_metrics(grid)\n",
    "display(grid)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Read World geometries data**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {
    "tags": [
     "active-ipynb"
    ]
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "CPU times: user 354 ms, sys: 15.9 ms, total: 370 ms\n",
      "Wall time: 368 ms\n"
     ]
    }
   ],
   "source": [
    "%%time\n",
    "world = gp.read_file(gp.datasets.get_path('naturalearth_lowres'), crs=CRS_WGS)\n",
    "world = world.to_crs(CRS_PROJ)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Preview Grid"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {
    "tags": [
     "active-ipynb"
    ]
   },
   "outputs": [
    {
     "data": {
      "image/png": "[encoded data, 194608 characters]\n",
      "text/plain": [
       "<Figure size 1584x2016 with 1 Axes>"
      ]
     },
     "metadata": {
      "needs_background": "light"
     },
     "output_type": "display_data"
    }
   ],
   "source": [
    "base = grid.plot(figsize=(22,28), color='white', edgecolor='black', linewidth=0.1)\n",
    "# combine with world geometry\n",
    "plot = world.plot(ax=base)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Prepare binary search\n",
    "\n",
    "The aggregation speed is important here and we should not use polygon intersection. Since we're working with a regular grid and floating point numbers, a [binary search](https://en.wikipedia.org/wiki/Binary_search_algorithm) is likely one of the fastest ways for our context. [numpy.digitize](https://docs.scipy.org/doc/numpy/reference/generated/numpy.digitize.html) provides a binary search, but it must be adapted to for the spatial context. A lat or lng value is assigned to the nearest bin matching. We get our lat and lng bins from our original Mollweide grid, which are regularly spaced at 100km interval. Note that we need to do two binary searches, for lat and for lng values."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Create test points"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {
    "tags": [
     "active-ipynb"
    ]
   },
   "outputs": [],
   "source": [
    "testpoint = Point(8.546377, 47.392323)\n",
    "testpoint2 = Point(13.726359, 51.028512)\n",
    "gdf_testpoints = gp.GeoSeries([testpoint, testpoint2], crs=CRS_WGS)\n",
    "# project geometries to Mollweide\n",
    "gdf_testpoints_proj = gdf_testpoints.to_crs(CRS_PROJ)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {
    "tags": [
     "active-ipynb"
    ]
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "671646.5840955656"
      ]
     },
     "execution_count": 31,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "gdf_testpoints_proj[0].x"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Preview map for testpoint"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {
    "tags": [
     "active-ipynb"
    ]
   },
   "outputs": [
    {
     "data": {
      "image/png": "[encoded data, 146528 characters]\n",
      "text/plain": [
       "<Figure size 1584x2016 with 1 Axes>"
      ]
     },
     "metadata": {
      "needs_background": "light"
     },
     "output_type": "display_data"
    }
   ],
   "source": [
    "base = world.plot(figsize=(22,28), color='white', edgecolor='black', linewidth=0.1)\n",
    "plot = gdf_testpoints_proj.plot(ax=base)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Use np.digitize() to assign coordinates to the grid\n",
    "\n",
    "> [np.digitize](https://docs.scipy.org/doc/numpy/reference/generated/numpy.digitize.html) is implemented in terms of np.searchsorted. This means that a binary search is used to bin the values, which scales much better for larger number of bins than the previous linear search. It also removes the requirement for the input array to be 1-dimensional."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Create 2 bins for each axis of existing Mollweide rows/cols grid:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {
    "tags": [
     "active-ipynb"
    ]
   },
   "outputs": [],
   "source": [
    "ybins = np.array(rows)\n",
    "xbins = np.array(cols)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Create 2 lists with a single entry (testpoint coordinate)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "metadata": {
    "tags": [
     "active-ipynb"
    ]
   },
   "outputs": [],
   "source": [
    "test_point_list_x = np.array([gdf_testpoints_proj[0].x, gdf_testpoints_proj[1].x])\n",
    "test_point_list_y = np.array([gdf_testpoints_proj[0].y, gdf_testpoints_proj[1].y])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Find the nearest bin for x coordinate (returns the bin-index):"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "metadata": {
    "tags": [
     "active-ipynb"
    ]
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array([187, 190])"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "x_bin = np.digitize(test_point_list_x, xbins) - 1\n",
    "display(x_bin)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Check value of bin (the y coordinate) based on returned index:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "metadata": {
    "tags": [
     "active-ipynb"
    ]
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array([659904, 959904])"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "testpoint_xbin_idx = xbins[[x_bin[0], x_bin[1]]]\n",
    "display(testpoint_xbin_idx)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Repeat the same for y-testpoint:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "metadata": {
    "tags": [
     "active-ipynb"
    ]
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array([33, 29])"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "y_bin = np.digitize(test_point_list_y, ybins) - 1\n",
    "display(y_bin)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "metadata": {
    "tags": [
     "active-ipynb"
    ]
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array([5679952, 6079952])"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "testpoint_ybin_idx = ybins[[y_bin[0], y_bin[1]]]\n",
    "display(testpoint_ybin_idx)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "➡️ **759904** / **5579952** and **1059904** / **5979952** are indexes that we can use in our geodataframe index to return the matching grid-poly for each point"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Highlight Testpoint in Grid"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Get grid-poly by index from testpoint"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "metadata": {
    "tags": [
     "active-ipynb"
    ]
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "geometry    POLYGON ((659904.000 ...\n",
       "Name: (659904, 5679952), dtype: geometry"
      ]
     },
     "execution_count": 39,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "grid.loc[testpoint_xbin_idx[0], testpoint_ybin_idx[0]]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Convert shapely bin poly to Geoseries and plot"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "metadata": {
    "tags": [
     "active-ipynb"
    ]
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<AxesSubplot:>"
      ]
     },
     "execution_count": 40,
     "metadata": {},
     "output_type": "execute_result"
    },
    {
     "data": {
      "image/png": "[encoded data, 5124 characters]\n",
      "text/plain": [
       "<Figure size 432x288 with 1 Axes>"
      ]
     },
     "metadata": {
      "needs_background": "light"
     },
     "output_type": "display_data"
    }
   ],
   "source": [
    "testpoint_grids = gp.GeoSeries(\n",
    "    [grid.loc[testpoint_xbin_idx[0], testpoint_ybin_idx[0]].geometry, grid.loc[testpoint_xbin_idx[1], testpoint_ybin_idx[1]].geometry])\n",
    "testpoint_grids.plot()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Preview map with testpoint and assigned bin"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Set auto zoom with buffer:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "metadata": {
    "tags": [
     "active-ipynb"
    ]
   },
   "outputs": [],
   "source": [
    "minx, miny, maxx, maxy = testpoint_grids.total_bounds\n",
    "buf = 1000000"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "metadata": {
    "tags": [
     "active-ipynb"
    ]
   },
   "outputs": [
    {
     "data": {
      "image/png": "[encoded data, 50684 characters]\n",
      "text/plain": [
       "<Figure size 720x576 with 1 Axes>"
      ]
     },
     "metadata": {
      "needs_background": "light"
     },
     "output_type": "display_data"
    }
   ],
   "source": [
    "# a figure with a 1x1 grid of Axes\n",
    "fig, ax = plt.subplots(1, 1,figsize=(10,8))\n",
    "ax.set_xlim(minx-buf, maxx+buf)\n",
    "ax.set_ylim(miny-buf, maxy+buf)\n",
    "base = world.plot(ax=ax, color='white', edgecolor='black', linewidth=0.1)\n",
    "grid_base = testpoint_grids.plot(ax=base, facecolor='red', linewidth=0.1)\n",
    "plot = gdf_testpoints_proj.plot(ax=grid_base, markersize=8, color='blue')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Prepare functions\n",
    "\n",
    "Now that it has been visually verified that the algorithm works, lets create functions for the main processing job."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "metadata": {},
   "outputs": [],
   "source": [
    "def get_best_bins(\n",
    "    search_values_x: np.array, search_values_y: np.array,\n",
    "    xbins: np.array, ybins: np.array) -> Tuple[np.ndarray, np.ndarray]:\n",
    "    \"\"\"Will return best bin for a lat and lng input\n",
    "    \n",
    "    Note: prepare bins and values in correct matching projection\n",
    "    \n",
    "    Args:\n",
    "        search_values_y: A list of projected latitude values\n",
    "        search_values_x: A list of projected longitude values\n",
    "        xbins: 1-d array of bins to snap lat/lng values\n",
    "        ybins: 1-d array of bins to snap lat/lng values\n",
    "\n",
    "    Returns:\n",
    "        Tuple[int, int]: A list of tuples with 2 index positions for the best \n",
    "            matching bins for each lat/lng\n",
    "    \"\"\"\n",
    "    xbins_idx = np.digitize(search_values_x, xbins, right=False)\n",
    "    ybins_idx = np.digitize(search_values_y, ybins, right=False)\n",
    "    return (xbins[xbins_idx-1], ybins[ybins_idx-1])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Create xbins and ybins directly, as a means to supporting import *"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "metadata": {},
   "outputs": [],
   "source": [
    "_, ROWS, COLS = create_grid_df(return_rows_cols=True)\n",
    "YBINS = np.array(ROWS)\n",
    "XBINS = np.array(COLS)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Test with LBSN data"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We're going to test the binning of coordinates on a part of the YFCC geotagged images."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Prepare lat/lng tuple of lower left corner and upper right corner to crop sample map:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "metadata": {
    "tags": [
     "active-ipynb"
    ]
   },
   "outputs": [],
   "source": [
    "# Part of Italy and Sicily\n",
    "bbox_italy = (\n",
    "    7.8662109375, 36.24427318493909,\n",
    "    19.31396484375, 43.29320031385282)\n",
    "bbox = bbox_italy"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Calculate bounding box with 1000 km buffer. For that, project the bounding Box to Mollweide, apply the buffer, and project back to WGS1984:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 46,
   "metadata": {
    "tags": [
     "active-ipynb"
    ]
   },
   "outputs": [],
   "source": [
    "# convert to Mollweide\n",
    "minx, miny = PROJ_TRANSFORMER.transform(\n",
    "    bbox_italy[0], bbox_italy[1])\n",
    "maxx, maxy = PROJ_TRANSFORMER.transform(\n",
    "    bbox_italy[2], bbox_italy[3])\n",
    "# apply buffer and convetr back to WGS1984\n",
    "min_buf = PROJ_TRANSFORMER_BACK.transform(minx-buf, miny-buf)\n",
    "max_buf = PROJ_TRANSFORMER_BACK.transform(maxx+buf, maxy+buf)\n",
    "bbox_italy_buf = [min_buf[0], min_buf[1], max_buf[0], max_buf[1]]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Select columns and types for improving speed"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "metadata": {
    "tags": [
     "active-ipynb"
    ]
   },
   "outputs": [],
   "source": [
    "usecols = ['latitude', 'longitude']\n",
    "dtypes = {'latitude': float, 'longitude': float}\n",
    "reset_metrics(grid)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Load data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 48,
   "metadata": {
    "tags": [
     "active-ipynb"
    ]
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "47843599\n",
      "CPU times: user 35.7 s, sys: 7.99 s, total: 43.7 s\n",
      "Wall time: 43.7 s\n"
     ]
    }
   ],
   "source": [
    "%%time\n",
    "df = pd.read_csv(\n",
    "    OUTPUT / \"csv\" / \"yfcc_posts.csv\", usecols=usecols, dtype=dtypes, encoding='utf-8')\n",
    "print(len(df))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Filter on bounding box (Italy)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 49,
   "metadata": {},
   "outputs": [],
   "source": [
    "def filter_df_bbox(\n",
    "    df: pd.DataFrame, bbox: Tuple[float, float, float, float],\n",
    "    inplace: bool = True):\n",
    "    \"\"\"Filter dataframe with bbox on latitude and longitude column\"\"\"\n",
    "    df.query(\n",
    "        f'({bbox[0]} < longitude) & '\n",
    "        f'(longitude <  {bbox[2]}) & '\n",
    "        f'({bbox[1]} < latitude) & '\n",
    "        f'(latitude < {bbox[3]})',\n",
    "        inplace=True)\n",
    "    # set index to asc integers\n",
    "    if inplace:\n",
    "        df.reset_index(inplace=True, drop=True)\n",
    "        return\n",
    "    return df.reset_index(inplace=False, drop=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Execute and count number of posts in the bounding box:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 50,
   "metadata": {
    "tags": [
     "active-ipynb"
    ]
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "There're 13,361,348 YFCC geotagged posts located within the bounding box.\n"
     ]
    },
    {
     "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>latitude</th>\n",
       "      <th>longitude</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>39.484863</td>\n",
       "      <td>-0.373535</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>45.417480</td>\n",
       "      <td>12.326660</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>48.142090</td>\n",
       "      <td>11.579590</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>51.130371</td>\n",
       "      <td>4.328613</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>52.448730</td>\n",
       "      <td>-1.560059</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    latitude  longitude\n",
       "0  39.484863  -0.373535\n",
       "1  45.417480  12.326660\n",
       "2  48.142090  11.579590\n",
       "3  51.130371   4.328613\n",
       "4  52.448730  -1.560059"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "CPU times: user 1.49 s, sys: 701 ms, total: 2.19 s\n",
      "Wall time: 2.19 s\n"
     ]
    }
   ],
   "source": [
    "%%time\n",
    "filter_df_bbox(df=df, bbox=bbox_italy_buf)\n",
    "print(f\"There're {len(df):,.0f} YFCC geotagged posts located within the bounding box.\")\n",
    "display(df.head())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Project coordinates to Mollweide\n",
    "\n",
    "Projection speed can be increased by using a predefined `pyproj.Transformer`. We're also splitting our input-dataframe into a list of dataframe, each containing 1 Million records, so we can process the data in _chunks_."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 51,
   "metadata": {},
   "outputs": [],
   "source": [
    "def proj_df(df, proj_transformer: Transformer = PROJ_TRANSFORMER):\n",
    "    \"\"\"Project pandas dataframe latitude and longitude decimal degrees\n",
    "    using predefined proj_transformer\"\"\"\n",
    "    if 'longitude' not in df.columns:\n",
    "        return\n",
    "    xx, yy = proj_transformer.transform(\n",
    "        df['longitude'].values, df['latitude'].values)\n",
    "    # assign projected coordinates to\n",
    "    # new columns x and y\n",
    "    # the ':' means: replace all values in-place\n",
    "    df.loc[:, \"x\"] = xx\n",
    "    df.loc[:, \"y\"] = yy\n",
    "    # Drop WGS coordinates\n",
    "    df.drop(columns=['longitude', 'latitude'], inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 52,
   "metadata": {
    "tags": [
     "active-ipynb"
    ]
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Projected 13,361,348 coordinates\n"
     ]
    },
    {
     "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>x</th>\n",
       "      <th>y</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>-31872.166464</td>\n",
       "      <td>4.731738e+06</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>991046.755221</td>\n",
       "      <td>5.385474e+06</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>901798.894258</td>\n",
       "      <td>5.677544e+06</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>324299.385019</td>\n",
       "      <td>5.991327e+06</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>-114739.576009</td>\n",
       "      <td>6.127444e+06</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "               x             y\n",
       "0  -31872.166464  4.731738e+06\n",
       "1  991046.755221  5.385474e+06\n",
       "2  901798.894258  5.677544e+06\n",
       "3  324299.385019  5.991327e+06\n",
       "4 -114739.576009  6.127444e+06"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "CPU times: user 10.9 s, sys: 477 ms, total: 11.4 s\n",
      "Wall time: 11.4 s\n"
     ]
    }
   ],
   "source": [
    "%%time\n",
    "proj_df(df)\n",
    "print(f'Projected {len(df.values):,.0f} coordinates')\n",
    "display(df.head())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Perform the bin assignment"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 53,
   "metadata": {
    "tags": [
     "active-ipynb"
    ]
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "CPU times: user 2.43 s, sys: 176 ms, total: 2.61 s\n",
      "Wall time: 2.6 s\n"
     ]
    }
   ],
   "source": [
    "%%time\n",
    "xbins_match, ybins_match = get_best_bins(\n",
    "    search_values_x=df['x'].to_numpy(),\n",
    "    search_values_y=df['y'].to_numpy(),\n",
    "    xbins=xbins, ybins=ybins)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 54,
   "metadata": {
    "tags": [
     "active-ipynb"
    ]
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "13361348"
      ]
     },
     "execution_count": 54,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(xbins_match)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 55,
   "metadata": {
    "tags": [
     "active-ipynb"
    ]
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array([ -40096,  959904,  859904,  259904, -140096,  759904,  559904,\n",
       "        -40096,  -40096,  159904])"
      ]
     },
     "execution_count": 55,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "xbins_match[:10]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 56,
   "metadata": {
    "tags": [
     "active-ipynb"
    ]
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array([4779952, 5479952, 5679952, 6079952, 6179952, 5279952, 5979952,\n",
       "       5979952, 6079952, 4979952])"
      ]
     },
     "execution_count": 56,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "ybins_match[:10]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## A: Post Count per grid\n",
    "\n",
    "Attach target bins to original dataframe. The `:` means: modify all values in-place"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 57,
   "metadata": {
    "tags": [
     "active-ipynb"
    ]
   },
   "outputs": [],
   "source": [
    "df.loc[:, 'xbins_match'] = xbins_match\n",
    "df.loc[:, 'ybins_match'] = ybins_match\n",
    "# set new index column\n",
    "df.set_index(['xbins_match', 'ybins_match'], inplace=True)\n",
    "# drop x and y columns not needed anymore\n",
    "df.drop(columns=['x', 'y'], inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 58,
   "metadata": {
    "tags": [
     "active-ipynb"
    ]
   },
   "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></th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>xbins_match</th>\n",
       "      <th>ybins_match</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>-40096</th>\n",
       "      <th>4779952</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>959904</th>\n",
       "      <th>5479952</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>859904</th>\n",
       "      <th>5679952</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>259904</th>\n",
       "      <th>6079952</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>-140096</th>\n",
       "      <th>6179952</th>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "Empty DataFrame\n",
       "Columns: []\n",
       "Index: [(-40096, 4779952), (959904, 5479952), (859904, 5679952), (259904, 6079952), (-140096, 6179952)]"
      ]
     },
     "execution_count": 58,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Count per bin. Since we know that there are no duplicates in the YFCC100M dataset, we can take a shortcut and simply count the size of the index."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 59,
   "metadata": {
    "tags": [
     "active-ipynb"
    ]
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "CPU times: user 21.6 s, sys: 2.3 s, total: 23.9 s\n",
      "Wall time: 23.8 s\n"
     ]
    }
   ],
   "source": [
    "%%time\n",
    "cardinality_series = df.groupby(\n",
    "        df.index).size()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 60,
   "metadata": {
    "tags": [
     "active-ipynb"
    ]
   },
   "outputs": [],
   "source": [
    "cardinality_series.index = pd.MultiIndex.from_tuples(\n",
    "        cardinality_series.index, names=['xbin', 'ybin'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 61,
   "metadata": {
    "tags": [
     "active-ipynb"
    ]
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "xbin     ybin   \n",
       "-340096  3879952       3\n",
       "         4179952       1\n",
       "         4279952     967\n",
       "         4379952       1\n",
       "         4479952    6433\n",
       "dtype: int64"
      ]
     },
     "execution_count": 61,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "cardinality_series.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 62,
   "metadata": {
    "tags": [
     "active-ipynb"
    ]
   },
   "outputs": [],
   "source": [
    "reset_metrics(grid, [\"postcount\"], setzero=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Append Series with calculated counts to grid (as new column) based on index match:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 63,
   "metadata": {
    "tags": [
     "active-ipynb"
    ]
   },
   "outputs": [],
   "source": [
    "grid.loc[cardinality_series.index, 'postcount'] = cardinality_series"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 64,
   "metadata": {
    "tags": [
     "active-ipynb"
    ]
   },
   "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></th>\n",
       "      <th>geometry</th>\n",
       "      <th>postcount</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>xbin</th>\n",
       "      <th>ybin</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th rowspan=\"5\" valign=\"top\">-340096</th>\n",
       "      <th>6179952</th>\n",
       "      <td>POLYGON ((-340096.000...</td>\n",
       "      <td>771</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6079952</th>\n",
       "      <td>POLYGON ((-340096.000...</td>\n",
       "      <td>12990</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5979952</th>\n",
       "      <td>POLYGON ((-340096.000...</td>\n",
       "      <td>5075</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5879952</th>\n",
       "      <td>POLYGON ((-340096.000...</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5779952</th>\n",
       "      <td>POLYGON ((-340096.000...</td>\n",
       "      <td>1521</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                 geometry  postcount\n",
       "xbin    ybin                                        \n",
       "-340096 6179952  POLYGON ((-340096.000...        771\n",
       "        6079952  POLYGON ((-340096.000...      12990\n",
       "        5979952  POLYGON ((-340096.000...       5075\n",
       "        5879952  POLYGON ((-340096.000...          1\n",
       "        5779952  POLYGON ((-340096.000...       1521"
      ]
     },
     "execution_count": 64,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "grid[grid[\"postcount\"] > 0].head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Preview post count map"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Use headtail_breaks classification scheme because it is specifically suited to map long tailed data, see [Jiang 2013](https://arxiv.org/pdf/1209.2801)\n",
    "\n",
    "* Jiang, B. (August 01, 2013). Head/Tail Breaks: A New Classification Scheme for Data with a Heavy-Tailed Distribution. The Professional Geographer, 65, 3, 482-494."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 65,
   "metadata": {
    "tags": [
     "active-ipynb"
    ]
   },
   "outputs": [],
   "source": [
    "# global legend font size setting\n",
    "plt.rc('legend', **{'fontsize': 16})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 66,
   "metadata": {},
   "outputs": [],
   "source": [
    "def leg_format(leg):\n",
    "    \"Format matplotlib legend entries\"\n",
    "    for lbl in leg.get_texts():\n",
    "        label_text = lbl.get_text()\n",
    "        lower = label_text.split(\",\")[0].lstrip(\"[(\")\n",
    "        upper = label_text.split(\",\")[1].rstrip(\")]\")\n",
    "        new_text = f'{float(lower):,.0f} - {float(upper):,.0f}'\n",
    "        lbl.set_text(new_text)\n",
    "\n",
    "def title_savefig_mod(\n",
    "    title, save_fig, grid_size_meters: int = GRID_SIZE_METERS):\n",
    "    \"\"\"Update title/output name if grid size is not 100km\"\"\"\n",
    "    if grid_size_meters == 100000:\n",
    "        return title, save_fig\n",
    "    km_size = grid_size_meters/1000\n",
    "    title = f'{title} ({km_size:.0f}km grid)'\n",
    "    if save_fig:\n",
    "        save_fig = save_fig.replace(\n",
    "            '.png', f'_{km_size:.0f}km.png')\n",
    "    return title, save_fig\n",
    "\n",
    "def save_plot(\n",
    "    grid: gp.GeoDataFrame, title: str, column: str, save_fig: str = None,\n",
    "    output: Path = OUTPUT, bbox: Tuple[float, float, float, float] = None,\n",
    "    proj_transformer: Transformer = PROJ_TRANSFORMER, buf: int = 1000000,\n",
    "    world = None):\n",
    "    \"\"\"Plot GeoDataFrame with matplotlib backend, optionaly export as png\"\"\"\n",
    "    fig, ax = plt.subplots(1, 1,figsize=(10,12))\n",
    "    if bbox is not None:\n",
    "        # create bounds from WGS1984 italy and project to Mollweide\n",
    "        minx, miny = proj_transformer.transform(\n",
    "            bbox[0], bbox[1])\n",
    "        maxx, maxy = proj_transformer.transform(\n",
    "            bbox[2], bbox[3])\n",
    "        ax.set_xlim(minx-buf, maxx+buf)\n",
    "        ax.set_ylim(miny-buf, maxy+buf)\n",
    "    title, save_fig = title_savefig_mod(\n",
    "        title, save_fig)\n",
    "    ax.set_title(title, fontsize=20)\n",
    "    base = grid.plot(\n",
    "        ax=ax, column=column, cmap='OrRd', scheme='headtail_breaks', \n",
    "        legend=True, legend_kwds={'loc': 'lower right'})\n",
    "    if world is None:\n",
    "        world = gp.read_file(\n",
    "            gp.datasets.get_path('naturalearth_lowres'), crs=CRS_WGS)\n",
    "        world = world.to_crs(CRS_PROJ)\n",
    "    # combine with world geometry\n",
    "    plot = world.plot(\n",
    "        ax=base, color='none', edgecolor='black', linewidth=0.1)\n",
    "    leg = ax.get_legend()\n",
    "    leg_format(leg)\n",
    "    if not save_fig:\n",
    "        return\n",
    "    fig.savefig(output / \"figures\" / save_fig, dpi=300, format='PNG',\n",
    "                bbox_inches='tight', pad_inches=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 67,
   "metadata": {
    "tags": [
     "active-ipynb"
    ]
   },
   "outputs": [
    {
     "data": {
      "image/png": "[encoded data, 95328 characters]\n",
      "text/plain": [
       "<Figure size 720x864 with 1 Axes>"
      ]
     },
     "metadata": {
      "needs_background": "light"
     },
     "output_type": "display_data"
    }
   ],
   "source": [
    "save_plot(\n",
    "    grid=grid, title='Post Count',\n",
    "    column='postcount', save_fig='postcount_sample.png',\n",
    "    bbox=bbox_italy, world=world)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## B: User Count per grid\n",
    "\n",
    "When using RAW data, the caveat for calculating usercounts is that all distinct ids per bin must be present first, before calculating the total count. Since the input data (Social Media posts) is spatially unordered, this requires either a two-pass approach (e.g. writing intermediate data to disk and performing the count in a second pass), or storing all user guids per bin in-memory. We're using the second approach here.\n",
    "\n",
    "What can be done to reduce memory load is to process the input data in _chunks_. After each chunk has been processed, Python's garbage collection can do its work and remove everything that is not needed anymore.\n",
    "\n",
    "Furthermore, we can store intermediate data to CSV, which is also more efficient than loading data from DB.\n",
    "\n",
    "These ideas are combined in the methods below. Adjust default chunk_size of `5000000` to your needs.\n",
    "\n",
    "**Specify input data**\n",
    "\n",
    "First, specify the columns that need to be retrieved from the database. In addition to lat and lng, we need the `user_guid` for calculating usercounts."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 68,
   "metadata": {
    "tags": [
     "active-ipynb"
    ]
   },
   "outputs": [],
   "source": [
    "usecols = ['latitude', 'longitude', 'user_guid']"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Adjust method for stream-reading from CSV in chunks:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 69,
   "metadata": {
    "tags": [
     "active-ipynb"
    ]
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "CPU times: user 2.22 ms, sys: 3.82 ms, total: 6.04 ms\n",
      "Wall time: 5.19 ms\n"
     ]
    }
   ],
   "source": [
    "%%time\n",
    "iter_csv = pd.read_csv(\n",
    "    OUTPUT / \"csv\" / \"yfcc_posts.csv\", usecols=usecols, iterator=True,\n",
    "    dtype=dtypes, encoding='utf-8', chunksize=CHUNK_SIZE)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 70,
   "metadata": {},
   "outputs": [],
   "source": [
    "def proj_report(df, cnt, inplace: bool = False):\n",
    "    \"\"\"Project df with progress report\"\"\"\n",
    "    proj_df(df)\n",
    "    clear_output(wait=True)\n",
    "    print(f'Projected {cnt:,.0f} coordinates')\n",
    "    if inplace:\n",
    "        return\n",
    "    return df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 71,
   "metadata": {
    "tags": [
     "active-ipynb"
    ]
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Projected 13,361,348 coordinates\n"
     ]
    },
    {
     "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>user_guid</th>\n",
       "      <th>x</th>\n",
       "      <th>y</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>71322403@N00</td>\n",
       "      <td>-31872.166464</td>\n",
       "      <td>4.731738e+06</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>22841923@N02</td>\n",
       "      <td>991046.755221</td>\n",
       "      <td>5.385474e+06</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>25622716@N02</td>\n",
       "      <td>901798.894258</td>\n",
       "      <td>5.677544e+06</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>84351449@N00</td>\n",
       "      <td>324299.385019</td>\n",
       "      <td>5.991327e+06</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>15181848@N02</td>\n",
       "      <td>-114739.576009</td>\n",
       "      <td>6.127444e+06</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      user_guid              x             y\n",
       "0  71322403@N00  -31872.166464  4.731738e+06\n",
       "1  22841923@N02  991046.755221  5.385474e+06\n",
       "2  25622716@N02  901798.894258  5.677544e+06\n",
       "3  84351449@N00  324299.385019  5.991327e+06\n",
       "4  15181848@N02 -114739.576009  6.127444e+06"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "CPU times: user 1min 18s, sys: 4.46 s, total: 1min 23s\n",
      "Wall time: 1min 23s\n"
     ]
    }
   ],
   "source": [
    "%%time\n",
    "# filter\n",
    "chunked_df = [\n",
    "    filter_df_bbox( \n",
    "        df=chunk_df, bbox=bbox_italy_buf, inplace=False)\n",
    "    for chunk_df in iter_csv]\n",
    "\n",
    "# project\n",
    "projected_cnt = 0\n",
    "for chunk_df in chunked_df:\n",
    "    projected_cnt += len(chunk_df)\n",
    "    proj_report(\n",
    "        chunk_df, projected_cnt, inplace=True)\n",
    "\n",
    "display(chunked_df[0].head())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Perform the bin assignment and count distinct users"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "First assign coordinates to bin using our binary search:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 72,
   "metadata": {},
   "outputs": [],
   "source": [
    "def bin_coordinates(\n",
    "        df: pd.DataFrame, xbins:\n",
    "        np.ndarray, ybins: np.ndarray) -> pd.DataFrame:\n",
    "    \"\"\"Bin coordinates using binary search and append to df as new index\"\"\"\n",
    "    xbins_match, ybins_match = get_best_bins(\n",
    "        search_values_x=df['x'].to_numpy(),\n",
    "        search_values_y=df['y'].to_numpy(),\n",
    "        xbins=xbins, ybins=ybins)\n",
    "    # append target bins to original dataframe\n",
    "    # use .loc to avoid chained indexing\n",
    "    df.loc[:, 'xbins_match'] = xbins_match\n",
    "    df.loc[:, 'ybins_match'] = ybins_match\n",
    "    # drop x and y columns not needed anymore\n",
    "    df.drop(columns=['x', 'y'], inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 73,
   "metadata": {},
   "outputs": [],
   "source": [
    "def bin_chunked_coordinates(\n",
    "    chunked_df: List[pd.DataFrame], xbins:\n",
    "    np.ndarray = XBINS, ybins: np.ndarray = YBINS):\n",
    "    \"\"\"Bin coordinates of chunked dataframe\"\"\"\n",
    "    binned_cnt = 0\n",
    "    for ix, df in enumerate(chunked_df):\n",
    "        bin_coordinates(df, xbins, ybins)\n",
    "        df.set_index(['xbins_match', 'ybins_match'], inplace=True)\n",
    "        clear_output(wait=True)\n",
    "        binned_cnt += len(df)\n",
    "        print(f\"Binned {binned_cnt:,.0f} coordinates..\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 74,
   "metadata": {
    "tags": [
     "active-ipynb"
    ]
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Binned 13,361,348 coordinates..\n"
     ]
    },
    {
     "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></th>\n",
       "      <th>user_guid</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>xbins_match</th>\n",
       "      <th>ybins_match</th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>-40096</th>\n",
       "      <th>4779952</th>\n",
       "      <td>71322403@N00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>959904</th>\n",
       "      <th>5479952</th>\n",
       "      <td>22841923@N02</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>859904</th>\n",
       "      <th>5679952</th>\n",
       "      <td>25622716@N02</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>259904</th>\n",
       "      <th>6079952</th>\n",
       "      <td>84351449@N00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>-140096</th>\n",
       "      <th>6179952</th>\n",
       "      <td>15181848@N02</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                            user_guid\n",
       "xbins_match ybins_match              \n",
       "-40096      4779952      71322403@N00\n",
       " 959904     5479952      22841923@N02\n",
       " 859904     5679952      25622716@N02\n",
       " 259904     6079952      84351449@N00\n",
       "-140096     6179952      15181848@N02"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "CPU times: user 6.98 s, sys: 317 ms, total: 7.29 s\n",
      "Wall time: 7.19 s\n"
     ]
    }
   ],
   "source": [
    "%%time\n",
    "bin_chunked_coordinates(chunked_df)\n",
    "display(chunked_df[0].head())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now group user_guids per bin in distinct sets. The demonstration below is based the first chunk of posts (`[0]`):"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 75,
   "metadata": {
    "tags": [
     "active-ipynb"
    ]
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "CPU times: user 4.72 s, sys: 531 ms, total: 5.25 s\n",
      "Wall time: 5.13 s\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "(-340096, 4279952)    {75315636@N00, 785589...\n",
       "(-340096, 4479952)    {75315636@N00, 140161...\n",
       "(-340096, 4579952)    {75315636@N00, 140161...\n",
       "(-340096, 4679952)    {75315636@N00, 280791...\n",
       "(-340096, 4779952)    {7183730@N06, 1871975...\n",
       "Name: user_guid, dtype: object"
      ]
     },
     "execution_count": 75,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%time\n",
    "df = chunked_df[0]\n",
    "series_grouped = df[\"user_guid\"].groupby(\n",
    "        df.index).apply(set)\n",
    "series_grouped.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now we have sets of user_guids per bin. The next step is to count the number of distinct items in each set:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 76,
   "metadata": {
    "tags": [
     "active-ipynb"
    ]
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "CPU times: user 1.34 ms, sys: 123 µs, total: 1.47 ms\n",
      "Wall time: 1.41 ms\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "(-340096, 4279952)    19\n",
       "(-340096, 4479952)    84\n",
       "(-340096, 4579952)    79\n",
       "(-340096, 4679952)    32\n",
       "(-340096, 4779952)    47\n",
       "Name: user_guid, dtype: int64"
      ]
     },
     "execution_count": 76,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%time\n",
    "cardinality_series = series_grouped.apply(len)\n",
    "cardinality_series.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "To be able to process all user_guids in chunks, we need to union sets incrementally and, finally, attach distinct user count to grid, based on composite index (bin-ids). This last part of the process is the same as in counting posts."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 77,
   "metadata": {},
   "outputs": [],
   "source": [
    "def init_col_emptysets(\n",
    "    grid: Union[pd.DataFrame, gp.GeoDataFrame], col_name: str):\n",
    "    \"\"\"Initialize column of dataframe with empty sets.\"\"\"\n",
    "    grid[col_name] = [set() for x in range(len(grid.index))]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 78,
   "metadata": {},
   "outputs": [],
   "source": [
    "def union_sets_series(\n",
    "    set_series: pd.Series, set_series_other: pd.Series) -> pd.Series:\n",
    "    \"\"\"Union of two pd.Series of sets based on index, with keep set index\"\"\"\n",
    "    return pd.Series(\n",
    "        [set.union(*z) for z in zip(set_series, set_series_other)],\n",
    "        index=set_series.index)\n",
    "    \n",
    "def group_union_chunked(\n",
    "    chunked_df: List[pd.DataFrame], grid: gp.GeoDataFrame,\n",
    "    col: str = \"user_guid\", metric: str = \"usercount\", drop_sets: bool = None,\n",
    "    chunk_size: int = CHUNK_SIZE):\n",
    "    \"\"\"Group dataframe records per bin, create distinct sets,\n",
    "    calculate cardinality and append to grid\"\"\"\n",
    "    if drop_sets is None:\n",
    "        drop_sets = True\n",
    "    # init grid empty sets\n",
    "    init_col_emptysets(grid, f\"{metric}_set\")\n",
    "    for ix, df in enumerate(chunked_df):\n",
    "        series_grouped = df[col].groupby(\n",
    "            df.index).apply(set)\n",
    "        # series of new user_guids per bin\n",
    "        series_grouped.index = pd.MultiIndex.from_tuples(\n",
    "            series_grouped.index, names=['xbin', 'ybin'])\n",
    "        # series of existing user_guids per bin\n",
    "        existing_sets_series = grid.loc[\n",
    "            series_grouped.index, f\"{metric}_set\"]\n",
    "        # union existing & new\n",
    "        series_grouped = union_sets_series(\n",
    "            series_grouped, existing_sets_series)\n",
    "        grid.loc[series_grouped.index, f'{metric}_set'] = series_grouped\n",
    "        clear_output(wait=True)\n",
    "        print(f\"Grouped {(ix*chunk_size)+len(df):,.0f} {col}s..\")\n",
    "    \n",
    "    # after all user_guids have been processed to bins,\n",
    "    # calculate cardinality and drop user_guids to free up memory\n",
    "    grid[metric] = grid[f'{metric}_set'].apply(len)\n",
    "    if drop_sets:\n",
    "        grid.drop(columns=[f'{metric}_set'], inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 79,
   "metadata": {
    "tags": [
     "active-ipynb"
    ]
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Grouped 45,793,148 user_guids..\n",
      "CPU times: user 44.8 s, sys: 1.06 s, total: 45.9 s\n",
      "Wall time: 45.7 s\n"
     ]
    },
    {
     "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></th>\n",
       "      <th>geometry</th>\n",
       "      <th>postcount</th>\n",
       "      <th>usercount</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>xbin</th>\n",
       "      <th>ybin</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th rowspan=\"5\" valign=\"top\">-340096</th>\n",
       "      <th>6179952</th>\n",
       "      <td>POLYGON ((-340096.000...</td>\n",
       "      <td>771</td>\n",
       "      <td>68</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6079952</th>\n",
       "      <td>POLYGON ((-340096.000...</td>\n",
       "      <td>12990</td>\n",
       "      <td>520</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5979952</th>\n",
       "      <td>POLYGON ((-340096.000...</td>\n",
       "      <td>5075</td>\n",
       "      <td>234</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5879952</th>\n",
       "      <td>POLYGON ((-340096.000...</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5779952</th>\n",
       "      <td>POLYGON ((-340096.000...</td>\n",
       "      <td>1521</td>\n",
       "      <td>148</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                 geometry  postcount  usercount\n",
       "xbin    ybin                                                   \n",
       "-340096 6179952  POLYGON ((-340096.000...        771         68\n",
       "        6079952  POLYGON ((-340096.000...      12990        520\n",
       "        5979952  POLYGON ((-340096.000...       5075        234\n",
       "        5879952  POLYGON ((-340096.000...          1          1\n",
       "        5779952  POLYGON ((-340096.000...       1521        148"
      ]
     },
     "execution_count": 79,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%time\n",
    "group_union_chunked(\n",
    "    chunked_df=chunked_df, grid=grid,\n",
    "    col=\"user_guid\", metric=\"usercount\")\n",
    "grid[grid[\"usercount\"]> 0].head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Preview user count map"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 80,
   "metadata": {
    "tags": [
     "active-ipynb"
    ]
   },
   "outputs": [
    {
     "data": {
      "image/png": "[encoded data, 91168 characters]\n",
      "text/plain": [
       "<Figure size 720x864 with 1 Axes>"
      ]
     },
     "metadata": {
      "needs_background": "light"
     },
     "output_type": "display_data"
    }
   ],
   "source": [
    "save_plot(\n",
    "    grid=grid, title='User Count',\n",
    "    column='usercount', save_fig='usercount_sample.png',\n",
    "    bbox=bbox_italy, world=world)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## C: User Days\n",
    "\n",
    "Wood, Guerry, Silver and Lacayo (2013) found that frequency of Flickr users per month correlates with official visitation rates for National Parks in the USA and further coined the term “user days” as a measurement for “the total number of days, across all users, that each person took at least one photograph within each site” (ibid, p. 6). User days has emerged as a suitable intermediate metric, between post count and user count.\n",
    "\n",
    "To calculate user days, we need to query additional YFCC post attribute `post_create_date`. This requires overriding `usecols` list:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 81,
   "metadata": {
    "tags": [
     "active-ipynb"
    ]
   },
   "outputs": [],
   "source": [
    "usecols = ['latitude', 'longitude', 'user_guid', 'post_create_date']"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Get data from CSV (define a method this time):"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 82,
   "metadata": {},
   "outputs": [],
   "source": [
    "def read_project_chunked(filename: str,\n",
    "    usecols: List[str], chunk_size: int = CHUNK_SIZE,\n",
    "    bbox: Tuple[float, float, float, float] = None) -> List[pd.DataFrame]:\n",
    "    \"\"\"Read data from csv, optionally clip to bbox and projet\"\"\"\n",
    "    iter_csv = pd.read_csv(\n",
    "        filename, usecols=usecols, iterator=True,\n",
    "        dtype=dtypes, encoding='utf-8', chunksize=chunk_size)\n",
    "    if bbox:\n",
    "        chunked_df = [filter_df_bbox( \n",
    "            df=chunk_df, bbox=bbox, inplace=False)\n",
    "        for chunk_df in iter_csv]\n",
    "    else:\n",
    "        chunked_df = [chunk_df for chunk_df in iter_csv]\n",
    "    # project\n",
    "    projected_cnt = 0\n",
    "    for chunk_df in chunked_df:\n",
    "        projected_cnt += len(chunk_df)\n",
    "        proj_report(\n",
    "            chunk_df, projected_cnt, inplace=True)\n",
    "    return chunked_df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Run:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 83,
   "metadata": {
    "tags": [
     "active-ipynb"
    ]
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Projected 13,361,348 coordinates\n"
     ]
    },
    {
     "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>user_guid</th>\n",
       "      <th>post_create_date</th>\n",
       "      <th>x</th>\n",
       "      <th>y</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>71322403@N00</td>\n",
       "      <td>2009-06-20</td>\n",
       "      <td>-31872.166464</td>\n",
       "      <td>4.731738e+06</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>22841923@N02</td>\n",
       "      <td>2004-08-15</td>\n",
       "      <td>991046.755221</td>\n",
       "      <td>5.385474e+06</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>25622716@N02</td>\n",
       "      <td>2007-07-09</td>\n",
       "      <td>901798.894258</td>\n",
       "      <td>5.677544e+06</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>84351449@N00</td>\n",
       "      <td>2012-07-07</td>\n",
       "      <td>324299.385019</td>\n",
       "      <td>5.991327e+06</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>15181848@N02</td>\n",
       "      <td>2009-01-24</td>\n",
       "      <td>-114739.576009</td>\n",
       "      <td>6.127444e+06</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      user_guid post_create_date              x             y\n",
       "0  71322403@N00       2009-06-20  -31872.166464  4.731738e+06\n",
       "1  22841923@N02       2004-08-15  991046.755221  5.385474e+06\n",
       "2  25622716@N02       2007-07-09  901798.894258  5.677544e+06\n",
       "3  84351449@N00       2012-07-07  324299.385019  5.991327e+06\n",
       "4  15181848@N02       2009-01-24 -114739.576009  6.127444e+06"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "CPU times: user 1min 34s, sys: 5.53 s, total: 1min 39s\n",
      "Wall time: 1min 39s\n"
     ]
    }
   ],
   "source": [
    "%%time\n",
    "chunked_df = read_project_chunked(\n",
    "    filename=OUTPUT / \"csv\" / \"yfcc_posts.csv\",\n",
    "    usecols=usecols,\n",
    "    bbox=bbox_italy_buf)\n",
    "display(chunked_df[0].head())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 84,
   "metadata": {
    "tags": [
     "active-ipynb"
    ]
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Binned 13,361,348 coordinates..\n",
      "CPU times: user 7.07 s, sys: 54.8 ms, total: 7.12 s\n",
      "Wall time: 7.06 s\n"
     ]
    }
   ],
   "source": [
    "%%time\n",
    "bin_chunked_coordinates(chunked_df)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 85,
   "metadata": {
    "tags": [
     "active-ipynb"
    ]
   },
   "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></th>\n",
       "      <th>user_guid</th>\n",
       "      <th>post_create_date</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>xbins_match</th>\n",
       "      <th>ybins_match</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>-40096</th>\n",
       "      <th>4779952</th>\n",
       "      <td>71322403@N00</td>\n",
       "      <td>2009-06-20</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>959904</th>\n",
       "      <th>5479952</th>\n",
       "      <td>22841923@N02</td>\n",
       "      <td>2004-08-15</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>859904</th>\n",
       "      <th>5679952</th>\n",
       "      <td>25622716@N02</td>\n",
       "      <td>2007-07-09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>259904</th>\n",
       "      <th>6079952</th>\n",
       "      <td>84351449@N00</td>\n",
       "      <td>2012-07-07</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>-140096</th>\n",
       "      <th>6179952</th>\n",
       "      <td>15181848@N02</td>\n",
       "      <td>2009-01-24</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                            user_guid post_create_date\n",
       "xbins_match ybins_match                               \n",
       "-40096      4779952      71322403@N00       2009-06-20\n",
       " 959904     5479952      22841923@N02       2004-08-15\n",
       " 859904     5679952      25622716@N02       2007-07-09\n",
       " 259904     6079952      84351449@N00       2012-07-07\n",
       "-140096     6179952      15181848@N02       2009-01-24"
      ]
     },
     "execution_count": 85,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "chunked_df[0].head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "To count distinct userdays, concat user_guid and post_create_date into single column:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 86,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "CPU times: user 11 µs, sys: 0 ns, total: 11 µs\n",
      "Wall time: 17.4 µs\n"
     ]
    }
   ],
   "source": [
    "%%time\n",
    "def concat_cols_df(\n",
    "    df: pd.DataFrame, col1: str, col2: str, col_out: str):\n",
    "    \"\"\"Concat dataframe values of col1 and col2 into new col\"\"\"\n",
    "    df[col_out] = df[col1] + df[col2]\n",
    "    df.drop(columns=[col1, col2], inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 87,
   "metadata": {
    "tags": [
     "active-ipynb"
    ]
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "CPU times: user 13.2 s, sys: 575 ms, total: 13.8 s\n",
      "Wall time: 13.8 s\n"
     ]
    },
    {
     "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></th>\n",
       "      <th>user_day</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>xbins_match</th>\n",
       "      <th>ybins_match</th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>-40096</th>\n",
       "      <th>4779952</th>\n",
       "      <td>71322403@N002009-06-20</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>959904</th>\n",
       "      <th>5479952</th>\n",
       "      <td>22841923@N022004-08-15</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>859904</th>\n",
       "      <th>5679952</th>\n",
       "      <td>25622716@N022007-07-09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>259904</th>\n",
       "      <th>6079952</th>\n",
       "      <td>84351449@N002012-07-07</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>-140096</th>\n",
       "      <th>6179952</th>\n",
       "      <td>15181848@N022009-01-24</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                       user_day\n",
       "xbins_match ybins_match                        \n",
       "-40096      4779952      71322403@N002009-06-20\n",
       " 959904     5479952      22841923@N022004-08-15\n",
       " 859904     5679952      25622716@N022007-07-09\n",
       " 259904     6079952      84351449@N002012-07-07\n",
       "-140096     6179952      15181848@N022009-01-24"
      ]
     },
     "execution_count": 87,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%time\n",
    "for df in chunked_df:\n",
    "    concat_cols_df(\n",
    "        df, col1=\"user_guid\",\n",
    "        col2=\"post_create_date\",\n",
    "        col_out=\"user_day\")\n",
    "chunked_df[0].head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Count distinct userdays and attach counts to grid. The process is now the same as in counting distinct users:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 88,
   "metadata": {
    "tags": [
     "active-ipynb"
    ]
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Grouped 45,793,148 user_days..\n"
     ]
    },
    {
     "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></th>\n",
       "      <th>geometry</th>\n",
       "      <th>postcount</th>\n",
       "      <th>usercount</th>\n",
       "      <th>userdays</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>xbin</th>\n",
       "      <th>ybin</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th rowspan=\"5\" valign=\"top\">-340096</th>\n",
       "      <th>6179952</th>\n",
       "      <td>POLYGON ((-340096.000...</td>\n",
       "      <td>771</td>\n",
       "      <td>68</td>\n",
       "      <td>163</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6079952</th>\n",
       "      <td>POLYGON ((-340096.000...</td>\n",
       "      <td>12990</td>\n",
       "      <td>520</td>\n",
       "      <td>2550</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5979952</th>\n",
       "      <td>POLYGON ((-340096.000...</td>\n",
       "      <td>5075</td>\n",
       "      <td>234</td>\n",
       "      <td>792</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5879952</th>\n",
       "      <td>POLYGON ((-340096.000...</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5779952</th>\n",
       "      <td>POLYGON ((-340096.000...</td>\n",
       "      <td>1521</td>\n",
       "      <td>148</td>\n",
       "      <td>351</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                 geometry  postcount  usercount  userdays\n",
       "xbin    ybin                                                             \n",
       "-340096 6179952  POLYGON ((-340096.000...        771         68       163\n",
       "        6079952  POLYGON ((-340096.000...      12990        520      2550\n",
       "        5979952  POLYGON ((-340096.000...       5075        234       792\n",
       "        5879952  POLYGON ((-340096.000...          1          1         1\n",
       "        5779952  POLYGON ((-340096.000...       1521        148       351"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "CPU times: user 1min 27s, sys: 1.24 s, total: 1min 28s\n",
      "Wall time: 1min 27s\n"
     ]
    }
   ],
   "source": [
    "%%time\n",
    "group_union_chunked(\n",
    "    chunked_df=chunked_df, grid=grid,\n",
    "    col=\"user_day\", metric=\"userdays\")\n",
    "display(grid[grid[\"userdays\"]> 0].head())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 89,
   "metadata": {
    "tags": [
     "active-ipynb"
    ]
   },
   "outputs": [
    {
     "data": {
      "image/png": "[encoded data, 91124 characters]\n",
      "text/plain": [
       "<Figure size 720x864 with 1 Axes>"
      ]
     },
     "metadata": {
      "needs_background": "light"
     },
     "output_type": "display_data"
    }
   ],
   "source": [
    "save_plot(\n",
    "    grid=grid, title='User Days',\n",
    "    column='userdays', save_fig='userdays_sample.png',\n",
    "    bbox=bbox_italy, world=world)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Prepare methods\n",
    "\n",
    "Lets summarize the above code in a few methods:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 87,
   "metadata": {},
   "outputs": [],
   "source": [
    "def group_count(\n",
    "    df: pd.DataFrame) -> pd.Series:\n",
    "    \"\"\"Group dataframe by composite index and return count of duplicate indexes\n",
    "\n",
    "    Args:\n",
    "       df: Indexed dataframe (with duplicate indexes). \n",
    "    \"\"\"\n",
    "    series_grouped = df.groupby(\n",
    "            df.index).size()\n",
    "    # split tuple index to produce\n",
    "    # the multiindex of the original dataframe\n",
    "    # with xbin and ybin column names\n",
    "    series_grouped.index = pd.MultiIndex.from_tuples(\n",
    "        series_grouped.index, names=['xbin', 'ybin'])\n",
    "    # return column as indexed pd.Series\n",
    "    return series_grouped"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Plotting preparation**\n",
    "\n",
    "The below methods contain combined code from above, plus final plot style improvements."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 90,
   "metadata": {},
   "outputs": [],
   "source": [
    "def format_legend(\n",
    "    leg, bounds: List[str], inverse: bool = None,\n",
    "    metric: str = \"postcount\"):\n",
    "    \"\"\"Formats legend (numbers rounded, colors etc.)\"\"\"\n",
    "    leg.set_bbox_to_anchor((0., 0.2, 0.2, 0.2))\n",
    "    # get all the legend labels\n",
    "    legend_labels = leg.get_texts()\n",
    "    plt.setp(legend_labels, fontsize='12')\n",
    "    lcolor = 'black'\n",
    "    if inverse:\n",
    "        frame = leg.get_frame()\n",
    "        frame.set_facecolor('black')\n",
    "        frame.set_edgecolor('grey')\n",
    "        lcolor = \"white\"\n",
    "    plt.setp(legend_labels, color = lcolor)\n",
    "    if metric == \"postcount\":\n",
    "        leg.set_title(\"Post Count\")\n",
    "    elif metric == \"usercount\":\n",
    "        leg.set_title(\"User Count\")\n",
    "    else:\n",
    "        leg.set_title(\"User Days\")\n",
    "    plt.setp(leg.get_title(), fontsize='12')\n",
    "    leg.get_title().set_color(lcolor)\n",
    "    # replace the numerical legend labels\n",
    "    for bound, legend_label in zip(bounds, legend_labels):\n",
    "        legend_label.set_text(bound)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 91,
   "metadata": {},
   "outputs": [],
   "source": [
    "def format_bound(\n",
    "    upper_bound: float = None, lower_bound: float = None) -> str:\n",
    "    \"\"\"Format legend text for class bounds\"\"\"\n",
    "    if upper_bound is None:\n",
    "        return f'{lower_bound:,.0f}'\n",
    "    if lower_bound is None:\n",
    "        return f'{upper_bound:,.0f}'\n",
    "    return f'{lower_bound:,.0f} - {upper_bound:,.0f}'\n",
    "\n",
    "def get_label_bounds(\n",
    "    scheme_classes, metric_series: pd.Series,\n",
    "    flat: bool = None) -> List[str]:\n",
    "    \"\"\"Get all upper bounds in the scheme_classes category\"\"\"\n",
    "    upper_bounds = scheme_classes.bins\n",
    "    # get and format all bounds\n",
    "    bounds = []\n",
    "    for idx, upper_bound in enumerate(upper_bounds):\n",
    "        if idx == 0:\n",
    "            lower_bound = metric_series.min()\n",
    "        else:\n",
    "            lower_bound = upper_bounds[idx-1]\n",
    "        if flat:\n",
    "            bound = format_bound(\n",
    "                lower_bound=lower_bound)\n",
    "        else:\n",
    "            bound = format_bound(\n",
    "                upper_bound, lower_bound)\n",
    "        bounds.append(bound)\n",
    "    if flat:\n",
    "        upper_bound = format_bound(\n",
    "            upper_bound=upper_bounds[-1])\n",
    "        bounds.append(upper_bound)\n",
    "    return bounds\n",
    "    \n",
    "def label_nodata(\n",
    "    grid: gp.GeoDataFrame, inverse: bool = None,\n",
    "    metric: str = \"postcount\"):\n",
    "    \"\"\"Add white to a colormap to represent missing value\n",
    "    \n",
    "    Adapted from:\n",
    "        https://stackoverflow.com/a/58160985/4556479\n",
    "        \n",
    "    See available colormaps:\n",
    "        http://holoviews.org/user_guide/Colormaps.html\n",
    "    \"\"\"\n",
    "    # set 0 to NaN\n",
    "    grid_nan = grid[metric].replace(0, np.nan)\n",
    "    # get headtail_breaks\n",
    "    # excluding NaN values\n",
    "    headtail_breaks = mc.HeadTailBreaks(\n",
    "        grid_nan.dropna())\n",
    "    grid[f'{metric}_cat'] = headtail_breaks.find_bin(\n",
    "        grid_nan).astype('str')\n",
    "    # set label for NaN values\n",
    "    grid.loc[grid_nan.isnull(), f'{metric}_cat'] = 'No Data'\n",
    "    bounds = get_label_bounds(\n",
    "        headtail_breaks, grid_nan.dropna().values)\n",
    "    cmap_name = 'OrRd'\n",
    "    nodata_color = 'white'\n",
    "    if inverse:\n",
    "        nodata_color = 'black'\n",
    "        cmap_name = 'cet_fire'\n",
    "    cmap = plt.cm.get_cmap(cmap_name, headtail_breaks.k)\n",
    "    # get hex values\n",
    "    cmap_list = [colors.rgb2hex(cmap(i)) for i in range(cmap.N)]\n",
    "    # lighten or darken up first/last color a bit \n",
    "    # to offset from black or white background\n",
    "    if inverse:\n",
    "        firstcolor = '#3E0100'\n",
    "        cmap_list[0] = firstcolor\n",
    "    else:\n",
    "        lastcolor = '#440402'\n",
    "        cmap_list.append(lastcolor)\n",
    "        cmap_list.pop(0)\n",
    "    # append nodata color\n",
    "    cmap_list.append(nodata_color)\n",
    "    cmap_with_nodata = colors.ListedColormap(cmap_list)\n",
    "    return cmap_with_nodata, bounds\n",
    "\n",
    "def plot_figure(\n",
    "    grid: gp.GeoDataFrame, title: str, inverse: bool = None,\n",
    "    metric: str = \"postcount\", store_fig: str = None,\n",
    "    output: Path = OUTPUT):\n",
    "    \"\"\"Combine layers and plot\"\"\"\n",
    "    # for plotting, there're some minor changes applied\n",
    "    # to the dataframe (replace NaN values),\n",
    "    # make a shallow copy here to prevent changes\n",
    "    # to modify the original grid\n",
    "    grid_plot = grid.copy()\n",
    "    # create new plot figure object with one axis\n",
    "    fig, ax = plt.subplots(1, 1, figsize=(22,28))\n",
    "    ax.set_title(title, fontsize=16)\n",
    "    print(\"Classifying bins..\")\n",
    "    cmap_with_nodata, bounds = label_nodata(\n",
    "        grid=grid_plot, inverse=inverse, metric=metric)\n",
    "    base = grid_plot.plot(\n",
    "        ax=ax,\n",
    "        column=f'{metric}_cat', cmap=cmap_with_nodata, legend=True)\n",
    "    leg = ax.get_legend()\n",
    "    print(\"Formatting legend..\")\n",
    "    format_legend(leg, bounds, inverse, metric)\n",
    "    # combine with world geometry\n",
    "    edgecolor = 'black'\n",
    "    if inverse:\n",
    "        edgecolor = 'white'\n",
    "    world = gp.read_file(\n",
    "            gp.datasets.get_path('naturalearth_lowres'), crs=CRS_WGS)\n",
    "    world = world.to_crs(CRS_PROJ)\n",
    "    plot = world.plot(\n",
    "        ax=base, color='none', edgecolor=edgecolor, linewidth=0.1)\n",
    "    if store_fig:\n",
    "        print(\"Storing figure as png..\")\n",
    "        if inverse:\n",
    "            store_fig = store_fig.replace('.png', '_inverse.png')\n",
    "        plot.get_figure().savefig(\n",
    "            output / \"figures\" / store_fig, dpi=300, format='PNG',\n",
    "            bbox_inches='tight', pad_inches=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 92,
   "metadata": {},
   "outputs": [],
   "source": [
    "def filter_nullisland_df(\n",
    "    df: Optional[pd.DataFrame] = None, df_list: Optional[List[pd.DataFrame]] = None,\n",
    "    col_x: str = \"longitude\", col_y: str = \"latitude\"):\n",
    "    \"\"\"Remove records from df inplace where both x and y coordinate are 0\"\"\"\n",
    "    if not df is None:\n",
    "        df_list = [df]\n",
    "    if not df_list:\n",
    "        raise ValueError(\"Please provide either df or df_list\")\n",
    "    for df in df_list:\n",
    "        if col_x in df.columns:\n",
    "            df.query(\n",
    "                f'({col_x} == 0 and {col_y} == 0) == False',\n",
    "                inplace=True)\n",
    "\n",
    "def load_plot(\n",
    "    filename: str, grid: gp.GeoDataFrame, title: str, inverse: bool = None,\n",
    "    metric: str = \"postcount\", store_fig: str = None, store_pickle: str = None,\n",
    "    chunk_size: int = CHUNK_SIZE, drop_sets: bool = None, output: Path = OUTPUT):\n",
    "    \"\"\"Load data, bin coordinates, estimate distinct counts (cardinality) and plot map\n",
    "    \n",
    "        Args:\n",
    "        filename: Filename to read and write intermediate data\n",
    "        grid: A geopandas geodataframe with indexes x and y \n",
    "            (projected coordinates) and grid polys\n",
    "        title: Title of the plot\n",
    "        inverse: If True, inverse colors (black instead of white map)\n",
    "        metric: target column for aggregate. Default: postcount_est.\n",
    "        store_fig: Provide a name to store figure as PNG. Will append \n",
    "            '_inverse.png' if inverse=True.\n",
    "        store_pickle: Provide a name to store pickled dataframe\n",
    "            with aggregate counts to disk\n",
    "        chunk_size: chunk processing into x records per chunk\n",
    "        drop_sets: After count of distinct ids, drop sets per bin, \n",
    "            to free up memory. Default: None/True\n",
    "    \"\"\"\n",
    "    usecols = ['latitude', 'longitude']\n",
    "    if metric != \"postcount\":\n",
    "        usecols.append('user_guid')\n",
    "    if metric == \"userdays\":\n",
    "        usecols.append('post_create_date')\n",
    "    # get data from CSV\n",
    "    chunked_df = read_project_chunked(\n",
    "        filename=output / \"csv\" / filename,\n",
    "        usecols=usecols)\n",
    "    # bin coordinates\n",
    "    bin_chunked_coordinates(chunked_df)\n",
    "    # reset metric column\n",
    "    reset_metrics(grid, [metric], setzero=True)\n",
    "    print(\"Getting cardinality per bin..\")\n",
    "    if metric == \"postcount\":\n",
    "        cardinality_cnt = 0\n",
    "        for df in chunked_df:\n",
    "            cardinality_series = group_count(\n",
    "                df)\n",
    "            # update postcounts per grid-bin based on index,\n",
    "            # use += to allow incremental update\n",
    "            grid.loc[\n",
    "                cardinality_series.index,\n",
    "                'postcount'] += cardinality_series\n",
    "            cardinality_cnt += len(df)\n",
    "            clear_output(wait=True)\n",
    "            print(f\"{cardinality_cnt:,.0f} posts processed.\")\n",
    "    else:\n",
    "        col = \"user_guid\"\n",
    "        if metric == \"userdays\":\n",
    "            # concat user_guid and\n",
    "            # post_create_date\n",
    "            for df in chunked_df:\n",
    "                concat_cols_df(\n",
    "                    df, col1=\"user_guid\",\n",
    "                    col2=\"post_create_date\",\n",
    "                    col_out=\"user_day\")\n",
    "            col = \"user_day\"\n",
    "        group_union_chunked(\n",
    "            chunked_df=chunked_df, grid=grid,\n",
    "            col=col, metric=metric, drop_sets=drop_sets)\n",
    "    print(\"Storing aggregate data as pickle..\")\n",
    "    if store_pickle:\n",
    "        grid.to_pickle(output / \"pickles\" / store_pickle)\n",
    "    print(\"Plotting figure..\")\n",
    "    plot_figure(grid, title, inverse, metric, store_fig)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Plotting worldmaps: Post Count, User Count and User Days\n",
    "\n",
    "Plot worldmap for each datasource"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 93,
   "metadata": {
    "tags": [
     "active-ipynb"
    ]
   },
   "outputs": [],
   "source": [
    "reset_metrics(grid, [\"postcount\", \"usercount\", \"userdays\"])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 92,
   "metadata": {
    "tags": [
     "active-ipynb"
    ]
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "47,843,599 posts processed.\n",
      "Storing aggregate data as pickle..\n",
      "Plotting figure..\n",
      "Classifying bins..\n",
      "Formatting legend..\n",
      "Storing figure as png..\n",
      "peak memory: 11313.71 MiB, increment: 6889.32 MiB\n",
      "CPU times: user 6min 11s, sys: 31.9 s, total: 6min 43s\n",
      "Wall time: 6min 41s\n"
     ]
    },
    {
     "data": {
      "image/png": "[encoded data, 210216 characters]\n",
      "text/plain": [
       "<Figure size 1584x2016 with 1 Axes>"
      ]
     },
     "metadata": {
      "needs_background": "light"
     },
     "output_type": "display_data"
    }
   ],
   "source": [
    "%%time\n",
    "%%memit\n",
    "load_plot(\n",
    "    grid=grid, filename='yfcc_posts.csv', title=f'YFCC Post Count per {int(GRID_SIZE_METERS/1000)}km grid',\n",
    "    inverse=False, store_fig=\"yfcc_postcount.png\", drop_sets=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 93,
   "metadata": {
    "tags": [
     "active-ipynb"
    ]
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Grouped 47,843,599 user_guids..\n",
      "Storing aggregate data as pickle..\n",
      "Plotting figure..\n",
      "Classifying bins..\n",
      "Formatting legend..\n",
      "Storing figure as png..\n",
      "peak memory: 12630.22 MiB, increment: 8194.15 MiB\n",
      "CPU times: user 9min 25s, sys: 59.6 s, total: 10min 24s\n",
      "Wall time: 11min 4s\n"
     ]
    },
    {
     "data": {
      "image/png": "[encoded data, 206852 characters]\n",
      "text/plain": [
       "<Figure size 1584x2016 with 1 Axes>"
      ]
     },
     "metadata": {
      "needs_background": "light"
     },
     "output_type": "display_data"
    }
   ],
   "source": [
    "%%time\n",
    "%%memit\n",
    "load_plot(\n",
    "    grid=grid, filename='yfcc_posts.csv', title=f'YFCC User Count per {int(GRID_SIZE_METERS/1000)}km grid',\n",
    "    inverse=False, store_fig=\"yfcc_usercount.png\",\n",
    "    metric=\"usercount\", drop_sets=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 94,
   "metadata": {
    "tags": [
     "active-ipynb"
    ]
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Grouped 47,843,599 user_days..\n",
      "Storing aggregate data as pickle..\n",
      "Plotting figure..\n",
      "Classifying bins..\n",
      "Formatting legend..\n",
      "Storing figure as png..\n",
      "peak memory: 16218.47 MiB, increment: 10684.51 MiB\n",
      "CPU times: user 14min 36s, sys: 53.2 s, total: 15min 29s\n",
      "Wall time: 15min 28s\n"
     ]
    },
    {
     "data": {
      "image/png": "[encoded data, 207668 characters]\n",
      "text/plain": [
       "<Figure size 1584x2016 with 1 Axes>"
      ]
     },
     "metadata": {
      "needs_background": "light"
     },
     "output_type": "display_data"
    }
   ],
   "source": [
    "%%time\n",
    "%%memit\n",
    "load_plot(\n",
    "    grid=grid, filename='yfcc_posts.csv', title=f'YFCC User Days per {int(GRID_SIZE_METERS/1000)}km grid',\n",
    "    inverse=False, store_fig=\"yfcc_userdays.png\",\n",
    "    metric=\"userdays\", drop_sets=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Have a look at the final `grid` with cardinality (distinct counts) for postcount, usercount and userdays\n",
    "\n",
    "An immediate validation is to verify that `postcount` >= `userdays` >= `usercount`."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 96,
   "metadata": {
    "tags": [
     "active-ipynb"
    ]
   },
   "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></th>\n",
       "      <th>geometry</th>\n",
       "      <th>postcount</th>\n",
       "      <th>usercount</th>\n",
       "      <th>usercount_set</th>\n",
       "      <th>userdays</th>\n",
       "      <th>userdays_set</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>xbin</th>\n",
       "      <th>ybin</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>-18040096</th>\n",
       "      <th>79952</th>\n",
       "      <td>POLYGON ((-18040096.0...</td>\n",
       "      <td>7</td>\n",
       "      <td>2</td>\n",
       "      <td>{26105073@N03, 341979...</td>\n",
       "      <td>7</td>\n",
       "      <td>{26105073@N031986-12-...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>-17640096</th>\n",
       "      <th>-2020048</th>\n",
       "      <td>POLYGON ((-17640096.0...</td>\n",
       "      <td>634</td>\n",
       "      <td>19</td>\n",
       "      <td>{59171457@N00, 212490...</td>\n",
       "      <td>67</td>\n",
       "      <td>{71990437@N032011-10-...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"2\" valign=\"top\">-17540096</th>\n",
       "      <th>-2020048</th>\n",
       "      <td>POLYGON ((-17540096.0...</td>\n",
       "      <td>54</td>\n",
       "      <td>2</td>\n",
       "      <td>{71990437@N03, 101884...</td>\n",
       "      <td>11</td>\n",
       "      <td>{71990437@N032011-10-...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>-2120048</th>\n",
       "      <td>POLYGON ((-17540096.0...</td>\n",
       "      <td>3</td>\n",
       "      <td>1</td>\n",
       "      <td>{67439103@N00}</td>\n",
       "      <td>1</td>\n",
       "      <td>{67439103@N001996-11-01}</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>-17440096</th>\n",
       "      <th>-1620048</th>\n",
       "      <td>POLYGON ((-17440096.0...</td>\n",
       "      <td>4</td>\n",
       "      <td>2</td>\n",
       "      <td>{72898690@N00, 258164...</td>\n",
       "      <td>4</td>\n",
       "      <td>{72898690@N002019-04-...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                    geometry  postcount  usercount  \\\n",
       "xbin      ybin                                                       \n",
       "-18040096  79952    POLYGON ((-18040096.0...          7          2   \n",
       "-17640096 -2020048  POLYGON ((-17640096.0...        634         19   \n",
       "-17540096 -2020048  POLYGON ((-17540096.0...         54          2   \n",
       "          -2120048  POLYGON ((-17540096.0...          3          1   \n",
       "-17440096 -1620048  POLYGON ((-17440096.0...          4          2   \n",
       "\n",
       "                               usercount_set  userdays  \\\n",
       "xbin      ybin                                           \n",
       "-18040096  79952    {26105073@N03, 341979...         7   \n",
       "-17640096 -2020048  {59171457@N00, 212490...        67   \n",
       "-17540096 -2020048  {71990437@N03, 101884...        11   \n",
       "          -2120048            {67439103@N00}         1   \n",
       "-17440096 -1620048  {72898690@N00, 258164...         4   \n",
       "\n",
       "                                userdays_set  \n",
       "xbin      ybin                                \n",
       "-18040096  79952    {26105073@N031986-12-...  \n",
       "-17640096 -2020048  {71990437@N032011-10-...  \n",
       "-17540096 -2020048  {71990437@N032011-10-...  \n",
       "          -2120048  {67439103@N001996-11-01}  \n",
       "-17440096 -1620048  {72898690@N002019-04-...  "
      ]
     },
     "execution_count": 96,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "grid[grid[\"postcount\"]>1].head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Save & load intermediate data\n",
    "## Load & store results from and to CSV\n",
    "\n",
    "To export only aggregate counts (postcount, usercount) to CSV (e.g. for archive purposes):"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Store results to CSV for archive purposes:**"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Define method"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 94,
   "metadata": {},
   "outputs": [],
   "source": [
    "def grid_agg_tocsv(\n",
    "    grid: gp.GeoDataFrame, filename: str,\n",
    "    metrics: List[str] = [\"postcount\", \"usercount\", \"userdays\"],\n",
    "    output: Path = OUTPUT):\n",
    "    \"\"\"Store geodataframe aggregate columns and indexes to CSV\"\"\"\n",
    "    grid.to_csv(output / \"csv\" / filename, mode='w', columns=metrics, index=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Convert/store to CSV (aggregate columns and indexes only):"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 98,
   "metadata": {
    "tags": [
     "active-ipynb"
    ]
   },
   "outputs": [],
   "source": [
    "grid_agg_tocsv(grid, \"yfcc_all_raw.csv\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Store results as benchmark data (with sets):**\n",
    "\n",
    "Internal database scenario"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 99,
   "metadata": {
    "tags": [
     "active-ipynb"
    ]
   },
   "outputs": [],
   "source": [
    "grid_agg_tocsv(\n",
    "    grid, \"yfcc_all_raw_benchmark.csv\", \n",
    "    metrics = [\"usercount_set\", \"userdays_set\",\n",
    "               \"usercount\", \"postcount\", \"userdays\"])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Size of benchmark data:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 100,
   "metadata": {
    "tags": [
     "active-ipynb"
    ]
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Size: 182.46 MB\n"
     ]
    }
   ],
   "source": [
    "benchmark_size_mb = Path(OUTPUT / \"csv\" / \"yfcc_all_raw_benchmark.csv\").stat().st_size / (1024*1024)\n",
    "print(f\"Size: {benchmark_size_mb:.2f} MB\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Load data from CSV:**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 95,
   "metadata": {},
   "outputs": [],
   "source": [
    "def create_new_grid(grid_size: int = GRID_SIZE_METERS,\n",
    "    proj_transformer: Transformer = PROJ_TRANSFORMER) -> gp.GeoDataFrame:\n",
    "    \"\"\"Create new 100x100km grid GeoDataFrame with custom projection\"\"\"\n",
    "    xmin = proj_transformer.transform(\n",
    "        -180, 0)[0]\n",
    "    xmax = proj_transformer.transform(\n",
    "        180, 0)[0]\n",
    "    ymax = proj_transformer.transform(\n",
    "        0, 90)[1]\n",
    "    ymin = proj_transformer.transform(\n",
    "        0, -90)[1]\n",
    "    grid = create_grid_df(\n",
    "        grid_size=grid_size,\n",
    "        xmin=xmin, ymin=ymin,\n",
    "        xmax=xmax, ymax=ymax)\n",
    "    # convert grid DataFrame to grid GeoDataFrame\n",
    "    grid = grid_to_gdf(grid)\n",
    "    return grid\n",
    "    \n",
    "def grid_agg_fromcsv(\n",
    "    filename: str, columns: List[str] = [\"xbin\", \"ybin\"],\n",
    "    metrics: List[str] = None, grid_size: int = GRID_SIZE_METERS):\n",
    "    \"\"\"Create a new Mollweide grid GeoDataFrame and \n",
    "    attach aggregate data columns from CSV based on index\"\"\"\n",
    "    # 1. Create new 100x100km (e.g.) grid\n",
    "    grid = create_new_grid(grid_size=grid_size)\n",
    "    # 2. load aggregate data from CSV and attach to grid\n",
    "    # -----\n",
    "    if metrics is None:\n",
    "        metrics = [\"postcount\", \"usercount\", \"userdays\"]\n",
    "    types_dict = dict()\n",
    "    for metric in metrics:\n",
    "        types_dict[metric] = int\n",
    "    columns = columns + metrics\n",
    "    df = pd.read_csv(\n",
    "        filename, dtype=types_dict, index_col=[\"xbin\", \"ybin\"],\n",
    "        usecols=columns)\n",
    "    # join columns based on index\n",
    "    grid = grid.join(df)\n",
    "    # return grid with aggregate data attached\n",
    "    return grid"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "To create a new grid and load aggregate counts from CSV:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 96,
   "metadata": {
    "tags": [
     "active-ipynb"
    ]
   },
   "outputs": [],
   "source": [
    "grid = grid_agg_fromcsv(\n",
    "    OUTPUT / \"csv\" / \"yfcc_all_raw.csv\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Load & plot pickled dataframe"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Loading (geodataframe) using [pickle](https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.read_pickle.html#pandas.read_pickle). This is the easiest way to store intermediate data, but may be incompatible [if package versions change](https://stackoverflow.com/questions/6687262/how-do-i-know-which-versions-of-pickle-a-particular-version-of-python-supports). If loading pickles does not work, a workaround is to load data from CSV and re-create pickle data, which will be compatible with used versions."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Store results using pickle for later resuse:**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 107,
   "metadata": {
    "tags": [
     "active-ipynb"
    ]
   },
   "outputs": [],
   "source": [
    "grid.to_pickle(OUTPUT / \"pickles\" / \"yfcc_all_raw.pkl\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Load pickled dataframe:**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 108,
   "metadata": {
    "tags": [
     "active-ipynb"
    ]
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "CPU times: user 1.46 s, sys: 0 ns, total: 1.46 s\n",
      "Wall time: 1.47 s\n"
     ]
    }
   ],
   "source": [
    "%%time\n",
    "grid = pd.read_pickle(OUTPUT / \"pickles\" / \"yfcc_all_raw.pkl\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Then use plot_figure on dataframe to plot with new parameters, e.g. plot inverse:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 97,
   "metadata": {
    "tags": [
     "active-ipynb"
    ]
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Classifying bins..\n",
      "Formatting legend..\n"
     ]
    },
    {
     "data": {
      "image/png": "[encoded data, 198824 characters]\n",
      "text/plain": [
       "<Figure size 1584x2016 with 1 Axes>"
      ]
     },
     "metadata": {
      "needs_background": "light"
     },
     "output_type": "display_data"
    }
   ],
   "source": [
    "plot_figure(grid, \"Pickle Test\", inverse=True, metric=\"postcount\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Close DB connection & Create notebook HTML"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 98,
   "metadata": {
    "tags": [
     "active-ipynb"
    ]
   },
   "outputs": [],
   "source": [
    "db_connection.close()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "tags": [
     "active-ipynb"
    ]
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "[NbConvertApp] Converting notebook ./02_yfcc_gridagg_raw.ipynb to html_toc\n",
      "[NbConvertApp] Writing 2069213 bytes to ../out/html/02_yfcc_gridagg_raw.html\n"
     ]
    }
   ],
   "source": [
    "!jupyter nbconvert --to html_toc \\\n",
    "    --output-dir=../out/html ./02_yfcc_gridagg_raw.ipynb \\\n",
    "    --template=../nbconvert.tpl \\\n",
    "    --ExtractOutputPreprocessor.enabled=False # create single output file"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "In the hll notebook, we'll create the same visuals using the privacy-aware hll version of the dataset. Follow in [03_yfcc_gridagg_hll.ipynb](03_yfcc_gridagg_hll.ipynb)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "jupytext": {
   "formats": "notebooks///ipynb,md///md,py///_/py"
  },
  "kernelspec": {
   "display_name": "worker_env",
   "language": "python",
   "name": "worker_env"
  },
  "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.8.5"
  },
  "toc": {
   "base_numbering": 1,
   "nav_menu": {},
   "number_sections": true,
   "sideBar": true,
   "skip_h1_title": false,
   "title_cell": "Table of Contents",
   "title_sidebar": "Contents",
   "toc_cell": true,
   "toc_position": {
    "height": "calc(100% - 180px)",
    "left": "10px",
    "top": "150px",
    "width": "384px"
   },
   "toc_section_display": true,
   "toc_window_display": true
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}
