{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Privacy-aware 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=\"#Load-dependencies\" data-toc-modified-id=\"Load-dependencies-2.1\"><span class=\"toc-item-num\">2.1&nbsp;&nbsp;</span>Load dependencies</a></span></li><li><span><a href=\"#Parameters\" data-toc-modified-id=\"Parameters-2.2\"><span class=\"toc-item-num\">2.2&nbsp;&nbsp;</span>Parameters</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=\"#Get-data-from-db-and-write-to-CSV\" data-toc-modified-id=\"Get-data-from-db-and-write-to-CSV-2.3\"><span class=\"toc-item-num\">2.3&nbsp;&nbsp;</span>Get data from db and write to CSV</a></span></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></li><li><span><a href=\"#Prepare-functions\" data-toc-modified-id=\"Prepare-functions-2.5\"><span class=\"toc-item-num\">2.5&nbsp;&nbsp;</span>Prepare functions</a></span></li><li><span><a href=\"#Test-with-LBSN-data\" data-toc-modified-id=\"Test-with-LBSN-data-2.6\"><span class=\"toc-item-num\">2.6&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-2.6.1\"><span class=\"toc-item-num\">2.6.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-2.6.2\"><span class=\"toc-item-num\">2.6.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-2.6.3\"><span class=\"toc-item-num\">2.6.3&nbsp;&nbsp;</span>Perform the bin assignment</a></span></li></ul></li><li><span><a href=\"#A:-Estimated-Post-Count-per-grid\" data-toc-modified-id=\"A:-Estimated-Post-Count-per-grid-2.7\"><span class=\"toc-item-num\">2.7&nbsp;&nbsp;</span>A: Estimated 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-2.7.1\"><span class=\"toc-item-num\">2.7.1&nbsp;&nbsp;</span>Preview post count map</a></span></li></ul></li><li><span><a href=\"#B:-Estimated-User-Count-per-grid\" data-toc-modified-id=\"B:-Estimated-User-Count-per-grid-2.8\"><span class=\"toc-item-num\">2.8&nbsp;&nbsp;</span>B: Estimated User Count per grid</a></span><ul class=\"toc-item\"><li><span><a href=\"#Perform-the-bin-assignment-and-estimate-distinct-users\" data-toc-modified-id=\"Perform-the-bin-assignment-and-estimate-distinct-users-2.8.1\"><span class=\"toc-item-num\">2.8.1&nbsp;&nbsp;</span>Perform the bin assignment and estimate distinct users</a></span></li><li><span><a href=\"#Preview-user-count-map\" data-toc-modified-id=\"Preview-user-count-map-2.8.2\"><span class=\"toc-item-num\">2.8.2&nbsp;&nbsp;</span>Preview user count map</a></span></li></ul></li><li><span><a href=\"#C:-Estimated-User-Days\" data-toc-modified-id=\"C:-Estimated-User-Days-2.9\"><span class=\"toc-item-num\">2.9&nbsp;&nbsp;</span>C: Estimated User Days</a></span></li></ul></li><li><span><a href=\"#Prepare-methods\" data-toc-modified-id=\"Prepare-methods-3\"><span class=\"toc-item-num\">3&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-4\"><span class=\"toc-item-num\">4&nbsp;&nbsp;</span>Plotting worldmaps: Post Count, User Count and User Days</a></span></li><li><span><a href=\"#Save-&amp;-load-intermediate-and-benchmark-data\" data-toc-modified-id=\"Save-&amp;-load-intermediate-and-benchmark-data-5\"><span class=\"toc-item-num\">5&nbsp;&nbsp;</span>Save &amp; load intermediate and benchmark 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-5.1\"><span class=\"toc-item-num\">5.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-5.2\"><span class=\"toc-item-num\">5.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-6\"><span class=\"toc-item-num\">6&nbsp;&nbsp;</span>Close DB connection &amp; Create notebook HTML</a></span></li><li><span><a href=\"#Interpretation-of-results\" data-toc-modified-id=\"Interpretation-of-results-7\"><span class=\"toc-item-num\">7&nbsp;&nbsp;</span>Interpretation of results</a></span></li></ul></div>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Introduction\n",
    "\n",
    "Based on data from YFCC100m dataset, this Notebook explores a privacy-aware processing example for visualizing frequentation patterns in a 100x100km Grid (worldwide).\n",
    "\n",
    "This is the third notebook in a series of four notebooks:\n",
    "\n",
    "* 1) the [Preparations (01_preparations.ipynb)](01_preparations.html) Basic preparations for processing YFCC100m, explains basic concepts and tools for working with the lbsn data\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_interactive_maps.ipynb)](04_interpretation.html) illustrates how to create interactive graphics for comparison of raw and hll results\n",
    "\n",
    "This notebook includes code parts and examples that have nothing to do with HyperLogLog. 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. At the various parts, we discuss advantages and disadvantages of the privacy-aware data structure compared to working with raw data.\n",
    "\n",
    "In this Notebook, we describe a complete visualization pipeline, exploring worldwide frequentation patterns from YFCC dataset based on a 100x100km grid. In addition to the steps listed in the raw notebook, this notebooks describes, among other aspects:\n",
    "\n",
    "* get data from LBSN hll db (PostgreSQL select)  \n",
    "* store hll data to CSV, load from CSV   \n",
    "* incremental union of hll sets\n",
    "* estimated cardinality for metrics postcount, usercount and userdays \n",
    "* measure timing of different steps, to compare processing time with raw-dataset approach  \n",
    "* load and store intermediate results from and to \\*.pickle and \\*.CSV\n",
    "* create benchmark data to be published\n",
    "\n",
    "**System requirements**\n",
    "\n",
    "The Notebook is configured to run on a computer with **8 GB of Memory** (minimum).\n",
    "\n",
    "If more is available, you may increase the `chunk_size` parameter (Default is 5000000 records per chunk) to improve speed.\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": [
    "## Load dependencies\n",
    "\n",
    "Dependencies are already defined in `02_yfcc_gridagg_raw.ipynb`. We're loading the jupytext python converted version of this notebook to the main namespace, which will make all methods and parameters available here."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "import sys\n",
    "from pathlib import Path\n",
    "module_path = str(Path.cwd().parents[0] / \"py\")\n",
    "if module_path not in sys.path:\n",
    "    sys.path.append(module_path)\n",
    "\n",
    "from _02_yfcc_gridagg_raw import *\n",
    "from modules import tools, preparations"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Parameters\n",
    "\n",
    "Parameters from the first notebook are available through import. They can be overridden here."
   ]
  },
  {
   "cell_type": "raw",
   "metadata": {
    "tags": [
     "active-ipynb"
    ]
   },
   "source": [
    "GRID_SIZE_METERS = 100000 # the size of grid cells in meters \n",
    "                          # (spatial accuracy of worldwide measurement)\n",
    "CHUNK_SIZE = 5000000      # 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."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Activate autoreload of changed python files:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {
    "tags": [
     "active-ipynb"
    ]
   },
   "outputs": [],
   "source": [
    "%load_ext autoreload\n",
    "%autoreload 2"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Load memory profiler extension"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "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": 5,
   "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": {
    "tags": [
     "highlight"
    ]
   },
   "source": [
    "### Connect to database\n",
    "\n",
    "The password is automatically 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": 7,
   "metadata": {
    "tags": [
     "active-ipynb"
    ]
   },
   "outputs": [],
   "source": [
    "db_user = \"postgres\"\n",
    "db_pass = os.getenv('POSTGRES_PASSWORD')\n",
    "# set connection variables\n",
    "db_host = \"hlldb\"\n",
    "db_port = \"5432\"\n",
    "db_name = \"hlldb\""
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Connect to empty Postgres database running HLL Extension. Note that only `read` privileges are needed."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "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": 9,
   "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": 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>?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": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "db_conn = tools.DbConn(db_connection)\n",
    "db_conn.query(\"SELECT 1;\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Get data from db and write to CSV\n",
    "\n",
    "To compare processing speed with the raw notebook, we're also going to save hll data to CSV first. The following records are available from table spatial.latlng:\n",
    "\n",
    "* distinct latitude and longitude coordinates (clear text), this is the \"base\" we're working on\n",
    "* post_hll - approximate post guids stored as hll set\n",
    "* user_hll - approximate user guids stored as hll set\n",
    "* date_hll - approximate user days stored as hll set"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [],
   "source": [
    "def get_yfccposts_fromdb(\n",
    "        chunk_size: int = CHUNK_SIZE) -> List[pd.DataFrame]:\n",
    "    \"\"\"Returns spatial.latlng data from db, excluding Null Island\"\"\"\n",
    "    sql = f\"\"\"\n",
    "    SELECT  latitude,\n",
    "            longitude,\n",
    "            post_hll,\n",
    "            user_hll,\n",
    "            date_hll\n",
    "    FROM spatial.latlng t1\n",
    "    WHERE\n",
    "    NOT ((latitude = 0) AND (longitude = 0));\n",
    "    \"\"\"\n",
    "    # execute query, enable chunked return\n",
    "    return pd.read_sql(sql, con=db_connection, chunksize=chunk_size)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Execute Query:**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {
    "tags": [
     "active-ipynb"
    ]
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "CSV already exists, skipping load from db.. (to reload, delete file)\n",
      "CPU times: user 774 µs, sys: 0 ns, total: 774 µs\n",
      "Wall time: 659 µs\n"
     ]
    }
   ],
   "source": [
    "%%time\n",
    "filename = \"yfcc_latlng.csv\"\n",
    "usecols = [\"latitude\", \"longitude\", \"post_hll\", \"user_hll\", \"date_hll\"]\n",
    "if Path(OUTPUT / \"csv\" / filename).exists():\n",
    "        print(f\"CSV already exists, skipping load from db.. (to reload, delete file)\")\n",
    "else:\n",
    "    write_chunkeddf_tocsv(\n",
    "        chunked_df=get_yfccposts_fromdb(),\n",
    "        filename=filename,\n",
    "        usecols=usecols)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**HLL file size:**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {
    "tags": [
     "active-ipynb"
    ]
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Size: 134.37 MB\n"
     ]
    }
   ],
   "source": [
    "hll_size_mb = Path(OUTPUT / \"csv\" / \"yfcc_latlng.csv\").stat().st_size / (1024*1024)\n",
    "print(f\"Size: {hll_size_mb:.2f} MB\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Create Grid"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "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": 15,
   "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": 16,
   "metadata": {
    "tags": [
     "active-ipynb"
    ]
   },
   "outputs": [],
   "source": [
    "METRICS = [\"postcount_est\", \"usercount_est\", \"userdays_est\"]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "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.000 8979952.000, -17940096...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8879952</th>\n",
       "      <td>POLYGON ((-18040096.000 8879952.000, -17940096...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8779952</th>\n",
       "      <td>POLYGON ((-18040096.000 8779952.000, -17940096...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8679952</th>\n",
       "      <td>POLYGON ((-18040096.000 8679952.000, -17940096...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8579952</th>\n",
       "      <td>POLYGON ((-18040096.000 8579952.000, -17940096...</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.000 -8620048.000, 18059904....</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>-8720048</th>\n",
       "      <td>POLYGON ((17959904.000 -8720048.000, 18059904....</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>-8820048</th>\n",
       "      <td>POLYGON ((17959904.000 -8820048.000, 18059904....</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>-8920048</th>\n",
       "      <td>POLYGON ((17959904.000 -8920048.000, 18059904....</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>-9020048</th>\n",
       "      <td>POLYGON ((17959904.000 -9020048.000, 18059904....</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.000 8979952.000, -17940096...\n",
       "           8879952  POLYGON ((-18040096.000 8879952.000, -17940096...\n",
       "           8779952  POLYGON ((-18040096.000 8779952.000, -17940096...\n",
       "           8679952  POLYGON ((-18040096.000 8679952.000, -17940096...\n",
       "           8579952  POLYGON ((-18040096.000 8579952.000, -17940096...\n",
       "...                                                               ...\n",
       " 17959904 -8620048  POLYGON ((17959904.000 -8620048.000, 18059904....\n",
       "          -8720048  POLYGON ((17959904.000 -8720048.000, 18059904....\n",
       "          -8820048  POLYGON ((17959904.000 -8820048.000, 18059904....\n",
       "          -8920048  POLYGON ((17959904.000 -8920048.000, 18059904....\n",
       "          -9020048  POLYGON ((17959904.000 -9020048.000, 18059904....\n",
       "\n",
       "[65341 rows x 1 columns]"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "reset_metrics(grid, METRICS)\n",
    "display(grid)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Read World geometries data**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {
    "tags": [
     "active-ipynb"
    ]
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "CPU times: user 329 ms, sys: 8.92 ms, total: 338 ms\n",
      "Wall time: 328 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": [
    "## Prepare functions\n",
    "\n",
    "Now that it has been visually verified that the algorithm works. We'll use some of the functions defined in the previous notebook."
   ]
  },
  {
   "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": [
    "Create 2 bins for each axis of existing Mollweide rows/cols grid:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {
    "tags": [
     "active-ipynb"
    ]
   },
   "outputs": [],
   "source": [
    "ybins = np.array(rows)\n",
    "xbins = np.array(cols)"
   ]
  },
  {
   "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": 20,
   "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": 21,
   "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",
    "buf = 1000000\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": 22,
   "metadata": {
    "tags": [
     "highlight",
     "active-ipynb"
    ]
   },
   "outputs": [],
   "source": [
    "usecols = ['latitude', 'longitude', 'post_hll']\n",
    "dtypes = {'latitude': float, 'longitude': float}\n",
    "reset_metrics(grid, METRICS)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Load data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {
    "tags": [
     "active-ipynb"
    ]
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "451949\n",
      "CPU times: user 2.35 s, sys: 393 ms, total: 2.74 s\n",
      "Wall time: 2.74 s\n"
     ]
    }
   ],
   "source": [
    "%%time\n",
    "df = pd.read_csv(\n",
    "    OUTPUT / \"csv\" / \"yfcc_latlng.csv\", usecols=usecols, dtype=dtypes, encoding='utf-8')\n",
    "print(len(df))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Execute and count number of posts in the bounding box:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {
    "tags": [
     "active-ipynb"
    ]
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "There're 92,775 YFCC distinct lat-lng coordinates located within the bounding box.\n",
      "CPU times: user 57.7 ms, sys: 4.21 ms, total: 61.9 ms\n",
      "Wall time: 60.6 ms\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",
       "      <th>post_hll</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>27.663574</td>\n",
       "      <td>28.410645</td>\n",
       "      <td>\\x138b40ba01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>27.663574</td>\n",
       "      <td>30.915527</td>\n",
       "      <td>\\x138b4001e4040204e20a8114231b012bc137e33d6246...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>27.663574</td>\n",
       "      <td>30.959473</td>\n",
       "      <td>\\x138b4025a144c2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>27.663574</td>\n",
       "      <td>33.596191</td>\n",
       "      <td>\\x138b40068535c1452157a6af63fb03</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>27.663574</td>\n",
       "      <td>33.728027</td>\n",
       "      <td>\\x138b40f3e1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    latitude  longitude                                           post_hll\n",
       "0  27.663574  28.410645                                       \\x138b40ba01\n",
       "1  27.663574  30.915527  \\x138b4001e4040204e20a8114231b012bc137e33d6246...\n",
       "2  27.663574  30.959473                                   \\x138b4025a144c2\n",
       "3  27.663574  33.596191                   \\x138b40068535c1452157a6af63fb03\n",
       "4  27.663574  33.728027                                       \\x138b40f3e1"
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%time\n",
    "filter_df_bbox(df=df, bbox=bbox_italy_buf)\n",
    "print(f\"There're {len(df):,.0f} YFCC distinct lat-lng coordinates located within the bounding box.\")\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Project coordinates to Mollweide"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {
    "tags": [
     "active-ipynb"
    ]
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Projected 92775 coordinates\n",
      "CPU times: user 116 ms, sys: 453 µs, total: 116 ms\n",
      "Wall time: 115 ms\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>post_hll</th>\n",
       "      <th>x</th>\n",
       "      <th>y</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>\\x138b40ba01</td>\n",
       "      <td>2.641304e+06</td>\n",
       "      <td>3.369176e+06</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>\\x138b4001e4040204e20a8114231b012bc137e33d6246...</td>\n",
       "      <td>2.874180e+06</td>\n",
       "      <td>3.369176e+06</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>\\x138b4025a144c2</td>\n",
       "      <td>2.878265e+06</td>\n",
       "      <td>3.369176e+06</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>\\x138b40068535c1452157a6af63fb03</td>\n",
       "      <td>3.123398e+06</td>\n",
       "      <td>3.369176e+06</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>\\x138b40f3e1</td>\n",
       "      <td>3.135655e+06</td>\n",
       "      <td>3.369176e+06</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                            post_hll             x  \\\n",
       "0                                       \\x138b40ba01  2.641304e+06   \n",
       "1  \\x138b4001e4040204e20a8114231b012bc137e33d6246...  2.874180e+06   \n",
       "2                                   \\x138b4025a144c2  2.878265e+06   \n",
       "3                   \\x138b40068535c1452157a6af63fb03  3.123398e+06   \n",
       "4                                       \\x138b40f3e1  3.135655e+06   \n",
       "\n",
       "              y  \n",
       "0  3.369176e+06  \n",
       "1  3.369176e+06  \n",
       "2  3.369176e+06  \n",
       "3  3.369176e+06  \n",
       "4  3.369176e+06  "
      ]
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%time\n",
    "proj_df(df)\n",
    "print(f'Projected {len(df.values)} coordinates')\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Perform the bin assignment"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {
    "tags": [
     "active-ipynb"
    ]
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "CPU times: user 9.91 ms, sys: 0 ns, total: 9.91 ms\n",
      "Wall time: 8.84 ms\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": 27,
   "metadata": {
    "tags": [
     "active-ipynb"
    ]
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "92775"
      ]
     },
     "execution_count": 27,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(xbins_match)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {
    "tags": [
     "active-ipynb"
    ]
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array([2559904, 2859904, 2859904, 3059904, 3059904, 3059904, 3159904,\n",
       "       2559904, 2659904, 3159904])"
      ]
     },
     "execution_count": 28,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "xbins_match[:10]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {
    "tags": [
     "active-ipynb"
    ]
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array([3379952, 3379952, 3379952, 3379952, 3379952, 3379952, 3379952,\n",
       "       3379952, 3379952, 3379952])"
      ]
     },
     "execution_count": 29,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "ybins_match[:10]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## A: Estimated Post Count per grid\n",
    "\n",
    "Attach target bins to original dataframe. The `:` means: modify all values in-place"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "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": 31,
   "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>post_hll</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>2559904</th>\n",
       "      <th>3379952</th>\n",
       "      <td>\\x138b40ba01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"2\" valign=\"top\">2859904</th>\n",
       "      <th>3379952</th>\n",
       "      <td>\\x138b4001e4040204e20a8114231b012bc137e33d6246...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3379952</th>\n",
       "      <td>\\x138b4025a144c2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"2\" valign=\"top\">3059904</th>\n",
       "      <th>3379952</th>\n",
       "      <td>\\x138b40068535c1452157a6af63fb03</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3379952</th>\n",
       "      <td>\\x138b40f3e1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                                                  post_hll\n",
       "xbins_match ybins_match                                                   \n",
       "2559904     3379952                                           \\x138b40ba01\n",
       "2859904     3379952      \\x138b4001e4040204e20a8114231b012bc137e33d6246...\n",
       "            3379952                                       \\x138b4025a144c2\n",
       "3059904     3379952                       \\x138b40068535c1452157a6af63fb03\n",
       "            3379952                                           \\x138b40f3e1"
      ]
     },
     "execution_count": 31,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The next step is to union hll sets and (optionally) return the cardinality (the number of distinct elements). This can be done by connecting to a postgres database with HLL extension installed. There's a [python package available](https://github.com/AdRoll/python-hll) for HLL calculations, but it is in a very early stage of development. For simplicity, we're using our `hlldb` here, but it is equally possible to connect to an empty Postgres DB running Citus HLL such as [pg-hll-empty docker container](https://gitlab.vgiscience.de/lbsn/databases/pg-hll-empty)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {
    "tags": [
     "highlight_red"
    ]
   },
   "outputs": [],
   "source": [
    "def union_hll(\n",
    "    hll_series: pd.Series, cardinality: bool = True) -> pd.Series:\n",
    "    \"\"\"HLL Union and (optional) cardinality estimation from series of hll sets\n",
    "    based on group by composite index.\n",
    "\n",
    "        Args:\n",
    "        hll_series: Indexed series (bins) of hll sets. \n",
    "        cardinality: If True, returns cardinality (counts). Otherwise,\n",
    "            the unioned hll set will be returned.\n",
    "            \n",
    "    The method will combine all groups of hll sets first,\n",
    "        in a single SQL command. Union of hll hll-sets belonging \n",
    "        to the same group (bin) and (optionally) returning the cardinality \n",
    "        (the estimated count) per group will be done in postgres.\n",
    "    \n",
    "    By utilizing Postgres´ GROUP BY (instead of, e.g. doing \n",
    "        the group with numpy), it is possible to reduce the number\n",
    "        of SQL calls to a single run, which saves overhead \n",
    "        (establishing the db connection, initializing the SQL query \n",
    "        etc.). Also note that ascending integers are used for groups,\n",
    "        instead of their full original bin-ids, which also reduces\n",
    "        transfer time.\n",
    "    \n",
    "    cardinality = True should be used when calculating counts in\n",
    "        a single pass.\n",
    "        \n",
    "    cardinality = False should be used when incrementally union\n",
    "        of hll sets is required, e.g. due to size of input data.\n",
    "        In the last run, set to cardinality = True.\n",
    "    \"\"\"\n",
    "    # group all hll-sets per index (bin-id)\n",
    "    series_grouped = hll_series.groupby(\n",
    "        hll_series.index).apply(list)\n",
    "    # From grouped hll-sets,\n",
    "    # construct a single SQL Value list;\n",
    "    # if the following nested list comprehension\n",
    "    # doesn't make sense to you, have a look at\n",
    "    # spapas.github.io/2016/04/27/python-nested-list-comprehensions/\n",
    "    # with a decription on how to 'unnest'\n",
    "    # nested list comprehensions to regular for-loops\n",
    "    hll_values_list = \",\".join(\n",
    "        [f\"({ix}::int,'{hll_item}'::hll)\" \n",
    "         for ix, hll_items\n",
    "         in enumerate(series_grouped.values.tolist())\n",
    "         for hll_item in hll_items])\n",
    "    # Compilation of SQL query,\n",
    "    # depending on whether to return the cardinality\n",
    "    # of unioned hll or the unioned hll\n",
    "    return_col = \"hll_union\"\n",
    "    hll_calc_pre = \"\"\n",
    "    hll_calc_tail = \"AS hll_union\"\n",
    "    if cardinality:\n",
    "        # add sql syntax for cardinality \n",
    "        # estimation\n",
    "        # (get count distinct from hll)\n",
    "        return_col = \"hll_cardinality\"\n",
    "        hll_calc_pre = \"hll_cardinality(\"\n",
    "        hll_calc_tail = \")::int\"\n",
    "    db_query = f\"\"\"\n",
    "        SELECT sq.{return_col} FROM (\n",
    "            SELECT s.group_ix,\n",
    "                   {hll_calc_pre}\n",
    "                   hll_union_agg(s.hll_set)\n",
    "                   {hll_calc_tail}\n",
    "            FROM (\n",
    "                VALUES {hll_values_list}\n",
    "                ) s(group_ix, hll_set)\n",
    "            GROUP BY group_ix\n",
    "            ORDER BY group_ix ASC) sq\n",
    "        \"\"\"\n",
    "    df = db_conn.query(db_query)\n",
    "    # to merge values back to grouped dataframe,\n",
    "    # first reset index to ascending integers\n",
    "    # matching those of the returned df;\n",
    "    # this will turn series_grouped into a DataFrame;\n",
    "    # the previous index will still exist in column 'index'\n",
    "    df_grouped = series_grouped.reset_index()\n",
    "    # drop hll sets not needed anymore\n",
    "    df_grouped.drop(columns=[hll_series.name], inplace=True)\n",
    "    # append hll_cardinality counts \n",
    "    # using matching ascending integer indexes\n",
    "    df_grouped.loc[df.index, return_col] = df[return_col]\n",
    "    # set index back to original bin-ids\n",
    "    df_grouped.set_index(\"index\", inplace=True)\n",
    "    # split tuple index to produce\n",
    "    # the multiindex of the original dataframe\n",
    "    # with xbin and ybin column names\n",
    "    df_grouped.index = pd.MultiIndex.from_tuples(\n",
    "        df_grouped.index, names=['xbin', 'ybin'])\n",
    "    # return column as indexed pd.Series\n",
    "    return df_grouped[return_col]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Optionally, split dataframe into chunks, so we're not the exceeding memory limit (e.g. use if memory < 16GB). A chunk size of 1 Million records is suitable for a computer with about 8 GB of memory and optional sparse HLL mode enabled. If sparse mode is disabled, decrease chunk_size accordingly, to compensate for increased space."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {
    "tags": [
     "active-ipynb"
    ]
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "CPU times: user 333 µs, sys: 51 µs, total: 384 µs\n",
      "Wall time: 393 µs\n"
     ]
    }
   ],
   "source": [
    "%%time\n",
    "chunked_df = [\n",
    "    df[i:i+CHUNK_SIZE] for i in range(0, df.shape[0], CHUNK_SIZE)]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "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>post_hll</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>2559904</th>\n",
       "      <th>3379952</th>\n",
       "      <td>\\x138b40ba01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"2\" valign=\"top\">2859904</th>\n",
       "      <th>3379952</th>\n",
       "      <td>\\x138b4001e4040204e20a8114231b012bc137e33d6246...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3379952</th>\n",
       "      <td>\\x138b4025a144c2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"2\" valign=\"top\">3059904</th>\n",
       "      <th>3379952</th>\n",
       "      <td>\\x138b40068535c1452157a6af63fb03</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3379952</th>\n",
       "      <td>\\x138b40f3e1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                                                  post_hll\n",
       "xbins_match ybins_match                                                   \n",
       "2559904     3379952                                           \\x138b40ba01\n",
       "2859904     3379952      \\x138b4001e4040204e20a8114231b012bc137e33d6246...\n",
       "            3379952                                       \\x138b4025a144c2\n",
       "3059904     3379952                       \\x138b40068535c1452157a6af63fb03\n",
       "            3379952                                           \\x138b40f3e1"
      ]
     },
     "execution_count": 34,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "chunked_df[0].head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "To test, process the first chunk:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "metadata": {
    "tags": [
     "highlight_red",
     "active-ipynb"
    ]
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "CPU times: user 664 ms, sys: 105 ms, total: 769 ms\n",
      "Wall time: 2.92 s\n"
     ]
    }
   ],
   "source": [
    "%%time\n",
    "cardinality_series = union_hll(chunked_df[0][\"post_hll\"])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "metadata": {
    "tags": [
     "active-ipynb"
    ]
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "xbin     ybin   \n",
       "-340096  3879952       3\n",
       "         4179952       1\n",
       "         4279952     971\n",
       "         4379952       1\n",
       "         4479952    6449\n",
       "Name: hll_cardinality, dtype: int64"
      ]
     },
     "execution_count": 36,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "cardinality_series.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Remove possibly existing result column in grid from previous run:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "metadata": {
    "tags": [
     "active-ipynb"
    ]
   },
   "outputs": [],
   "source": [
    "reset_metrics(grid, [\"postcount_est\"], 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": 38,
   "metadata": {
    "tags": [
     "active-ipynb"
    ]
   },
   "outputs": [],
   "source": [
    "grid.loc[cardinality_series.index, 'postcount_est'] = cardinality_series"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "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_est</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 6179952.000, -240096.000...</td>\n",
       "      <td>772</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6079952</th>\n",
       "      <td>POLYGON ((-340096.000 6079952.000, -240096.000...</td>\n",
       "      <td>12926</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5979952</th>\n",
       "      <td>POLYGON ((-340096.000 5979952.000, -240096.000...</td>\n",
       "      <td>5181</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5879952</th>\n",
       "      <td>POLYGON ((-340096.000 5879952.000, -240096.000...</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5779952</th>\n",
       "      <td>POLYGON ((-340096.000 5779952.000, -240096.000...</td>\n",
       "      <td>1531</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                                          geometry  \\\n",
       "xbin    ybin                                                         \n",
       "-340096 6179952  POLYGON ((-340096.000 6179952.000, -240096.000...   \n",
       "        6079952  POLYGON ((-340096.000 6079952.000, -240096.000...   \n",
       "        5979952  POLYGON ((-340096.000 5979952.000, -240096.000...   \n",
       "        5879952  POLYGON ((-340096.000 5879952.000, -240096.000...   \n",
       "        5779952  POLYGON ((-340096.000 5779952.000, -240096.000...   \n",
       "\n",
       "                 postcount_est  \n",
       "xbin    ybin                    \n",
       "-340096 6179952            772  \n",
       "        6079952          12926  \n",
       "        5979952           5181  \n",
       "        5879952              1  \n",
       "        5779952           1531  "
      ]
     },
     "execution_count": 39,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "grid[grid[\"postcount_est\"] > 0].head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Process all chunks:**\n",
    "\n",
    "The caveat here is to incrementally union hll sets until all records have been processed. On the last loop, instruct the hll worker to return the cardinality instead of the unioned hll set."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "First, define method to join cardinality to grid"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "metadata": {
    "tags": [
     "highlight"
    ]
   },
   "outputs": [],
   "source": [
    "# reference metric names and column names\n",
    "COLUMN_METRIC_REF = {\n",
    "        \"postcount_est\":\"post_hll\",\n",
    "        \"usercount_est\":\"user_hll\",\n",
    "        \"userdays_est\":\"date_hll\"}\n",
    "\n",
    "def join_df_grid(\n",
    "    df: pd.DataFrame, grid: gp.GeoDataFrame,\n",
    "    metric: str = \"postcount_est\",\n",
    "    cardinality: bool = True,\n",
    "    column_metric_ref: Dict[str,str] = COLUMN_METRIC_REF):\n",
    "    \"\"\"Union HLL Sets and estimate postcount per \n",
    "    grid bin from lat/lng coordinates\n",
    "    \n",
    "        Args:\n",
    "        df: A pandas dataframe with latitude and \n",
    "            longitude columns in WGS1984\n",
    "        grid: A geopandas geodataframe with indexes \n",
    "            x and y (projected coordinates) and grid polys\n",
    "        metric: target column for estimate aggregate.\n",
    "            Default: postcount_est.\n",
    "        cardinality: will compute cardinality of unioned\n",
    "            hll sets. Otherwise, unioned hll sets will be \n",
    "            returned for incremental updates.\n",
    "    \"\"\"\n",
    "    # optionally, bin assigment of projected coordinates,\n",
    "    # make sure to not bin twice:\n",
    "    # x/y columns are removed after binning\n",
    "    if 'x' in df.columns:\n",
    "        bin_coordinates(df, xbins, ybins)\n",
    "        # set index column\n",
    "        df.set_index(\n",
    "            ['xbins_match', 'ybins_match'], inplace=True)\n",
    "    # union hll sets and \n",
    "    # optional estimate count distincts (cardinality)\n",
    "    column = column_metric_ref.get(metric)\n",
    "    # get series with grouped hll sets\n",
    "    hll_series = df[column]\n",
    "    # union of hll sets:\n",
    "    # to allow incremental union of already merged data\n",
    "    # and new data, concatenate series from grid and new df\n",
    "    # only if column with previous hll sets already exists\n",
    "    if metric in grid.columns:\n",
    "        # remove nan values from grid and\n",
    "        # rename series to match names\n",
    "        hll_series = pd.concat(\n",
    "            [hll_series, grid[metric].dropna()]\n",
    "            ).rename(column)\n",
    "    cardinality_series = union_hll(\n",
    "        hll_series, cardinality=cardinality)\n",
    "    # add unioned hll sets/computed cardinality to grid\n",
    "    grid.loc[\n",
    "        cardinality_series.index, metric] = cardinality_series\n",
    "    if cardinality:\n",
    "        # set all remaining grid cells\n",
    "        # with no data to zero and\n",
    "        # downcast column type from float to int\n",
    "        grid[metric] = grid[metric].fillna(0).astype(int)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Define method to process chunks:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 62,
   "metadata": {
    "tags": [
     "highlight"
    ]
   },
   "outputs": [],
   "source": [
    "def join_chunkeddf_grid(\n",
    "    chunked_df: List[pd.DataFrame], grid: gp.GeoDataFrame,\n",
    "    metric: str = \"postcount_est\", chunk_size: int = CHUNK_SIZE,\n",
    "    benchmark_data: Optional[bool] = None,\n",
    "    column_metric_ref: Dict[str,str] = COLUMN_METRIC_REF):\n",
    "    \"\"\"Incremental union of HLL Sets and estimate postcount per \n",
    "    grid bin from chunked list of dataframe records. Results will\n",
    "    be stored in grid.\n",
    "    \n",
    "    Args:\n",
    "    chunked_df: A list of (chunked) dataframes with latitude and \n",
    "        longitude columns in WGS1984\n",
    "    grid: A geopandas geodataframe with indexes \n",
    "        x and y (projected coordinates) and grid polys\n",
    "    metric: target column for estimate aggregate.\n",
    "        Default: postcount_est.\n",
    "    benchmark_data: If True, will not remove HLL sketches after\n",
    "        final cardinality estimation.\n",
    "    column_metric_ref: Dictionary containing references of \n",
    "        metrics to df columns.\n",
    "    \"\"\"\n",
    "    reset_metrics(grid, [metric])\n",
    "    for ix, chunk_df in enumerate(chunked_df):\n",
    "        # compute cardinality only on last iteration\n",
    "        cardinality = False\n",
    "        if ix == len(chunked_df)-1:\n",
    "            cardinality = True\n",
    "        column = column_metric_ref.get(metric)\n",
    "        # get series with grouped hll sets\n",
    "        hll_series = chunk_df[column]\n",
    "        if metric in grid.columns:\n",
    "            # merge existing hll sets with new ones\n",
    "            # into one series (with duplicate indexes);\n",
    "            # remove nan values from grid and\n",
    "            # rename series to match names\n",
    "            hll_series = pd.concat(\n",
    "                [hll_series, grid[metric].dropna()]\n",
    "                ).rename(column)\n",
    "        cardinality_series = union_hll(\n",
    "            hll_series, cardinality=cardinality)\n",
    "        if benchmark_data and (ix == len(chunked_df)-1):\n",
    "            # only if final hll sketches need to\n",
    "            # be kept for benchmarking:\n",
    "            # do another union, without cardinality\n",
    "            # estimation, and store results\n",
    "            # in column \"metric\"_hll\n",
    "            hll_sketch_series = union_hll(\n",
    "                hll_series, cardinality=False)\n",
    "            grid.loc[\n",
    "                hll_sketch_series.index,\n",
    "                f'{metric.replace(\"_est\",\"_hll\")}'] = hll_sketch_series\n",
    "        # add unioned hll sets/computed cardinality to grid\n",
    "        grid.loc[\n",
    "            cardinality_series.index, metric] = cardinality_series\n",
    "        if cardinality:\n",
    "            # set all remaining grid cells\n",
    "            # with no data to zero and\n",
    "            # downcast column type from float to int\n",
    "            grid[metric] = grid[metric].fillna(0).astype(int)\n",
    "        clear_output(wait=True)\n",
    "        print(f'Mapped ~{(ix+1)*chunk_size} coordinates to bins')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "metadata": {
    "tags": [
     "active-ipynb"
    ]
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Mapped ~5000000 coordinates to bins\n"
     ]
    }
   ],
   "source": [
    "join_chunkeddf_grid(chunked_df, grid, chunk_size=CHUNK_SIZE)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "All distinct counts are now attached to the bins of the grid:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "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_est</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 6179952.000, -240096.000...</td>\n",
       "      <td>772</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6079952</th>\n",
       "      <td>POLYGON ((-340096.000 6079952.000, -240096.000...</td>\n",
       "      <td>12926</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5979952</th>\n",
       "      <td>POLYGON ((-340096.000 5979952.000, -240096.000...</td>\n",
       "      <td>5181</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5779952</th>\n",
       "      <td>POLYGON ((-340096.000 5779952.000, -240096.000...</td>\n",
       "      <td>1531</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5679952</th>\n",
       "      <td>POLYGON ((-340096.000 5679952.000, -240096.000...</td>\n",
       "      <td>6965</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                                          geometry  \\\n",
       "xbin    ybin                                                         \n",
       "-340096 6179952  POLYGON ((-340096.000 6179952.000, -240096.000...   \n",
       "        6079952  POLYGON ((-340096.000 6079952.000, -240096.000...   \n",
       "        5979952  POLYGON ((-340096.000 5979952.000, -240096.000...   \n",
       "        5779952  POLYGON ((-340096.000 5779952.000, -240096.000...   \n",
       "        5679952  POLYGON ((-340096.000 5679952.000, -240096.000...   \n",
       "\n",
       "                 postcount_est  \n",
       "xbin    ybin                    \n",
       "-340096 6179952            772  \n",
       "        6079952          12926  \n",
       "        5979952           5181  \n",
       "        5779952           1531  \n",
       "        5679952           6965  "
      ]
     },
     "execution_count": 43,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "grid[grid[\"postcount_est\"]>10].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": 44,
   "metadata": {
    "tags": [
     "active-ipynb"
    ]
   },
   "outputs": [],
   "source": [
    "# global legend font size setting\n",
    "plt.rc('legend', **{'fontsize': 16})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "metadata": {
    "tags": [
     "active-ipynb"
    ]
   },
   "outputs": [
    {
     "data": {
      "image/png": "[encoded data, 97692 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='Estimated Post Count',\n",
    "    column='postcount_est', save_fig='postcount_sample_est.png',\n",
    "    bbox=bbox_italy, world=world)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## B: Estimated User Count per grid\n",
    "\n",
    "When using HLL, aggregation of user_guids or user_days takes the same amount of time (unlike when working with original data, where memory consumption increases significantly). We'll only need to update the columns that are loaded from the database:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 46,
   "metadata": {
    "tags": [
     "active-ipynb"
    ]
   },
   "outputs": [],
   "source": [
    "usecols = ['latitude', 'longitude', 'user_hll']"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Adjust method for stream-reading from CSV in chunks:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "metadata": {
    "tags": [
     "highlight",
     "active-ipynb"
    ]
   },
   "outputs": [],
   "source": [
    "iter_csv = pd.read_csv(\n",
    "    OUTPUT / \"csv\" / \"yfcc_latlng.csv\", usecols=usecols, iterator=True,\n",
    "    dtype=dtypes, encoding='utf-8', chunksize=CHUNK_SIZE)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 48,
   "metadata": {
    "tags": [
     "active-ipynb"
    ]
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Projected 92,775 coordinates\n",
      "CPU times: user 1.63 s, sys: 99.8 ms, total: 1.73 s\n",
      "Wall time: 1.73 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>user_hll</th>\n",
       "      <th>x</th>\n",
       "      <th>y</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>\\x138b4054e2</td>\n",
       "      <td>2.641304e+06</td>\n",
       "      <td>3.369176e+06</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>\\x138b40200439019663c582</td>\n",
       "      <td>2.874180e+06</td>\n",
       "      <td>3.369176e+06</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>\\x138b40c582</td>\n",
       "      <td>2.878265e+06</td>\n",
       "      <td>3.369176e+06</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>\\x138b40cbe1</td>\n",
       "      <td>3.123398e+06</td>\n",
       "      <td>3.369176e+06</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>\\x138b4027c3</td>\n",
       "      <td>3.135655e+06</td>\n",
       "      <td>3.369176e+06</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                   user_hll             x             y\n",
       "0              \\x138b4054e2  2.641304e+06  3.369176e+06\n",
       "1  \\x138b40200439019663c582  2.874180e+06  3.369176e+06\n",
       "2              \\x138b40c582  2.878265e+06  3.369176e+06\n",
       "3              \\x138b40cbe1  3.123398e+06  3.369176e+06\n",
       "4              \\x138b4027c3  3.135655e+06  3.369176e+06"
      ]
     },
     "execution_count": 48,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "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",
    "chunked_df[0].head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Perform the bin assignment and estimate distinct users"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 49,
   "metadata": {
    "tags": [
     "active-ipynb"
    ]
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Binned 92,775 coordinates..\n",
      "CPU times: user 48.3 ms, sys: 4.26 ms, total: 52.6 ms\n",
      "Wall time: 48 ms\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_hll</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>2559904</th>\n",
       "      <th>3379952</th>\n",
       "      <td>\\x138b4054e2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"2\" valign=\"top\">2859904</th>\n",
       "      <th>3379952</th>\n",
       "      <td>\\x138b40200439019663c582</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3379952</th>\n",
       "      <td>\\x138b40c582</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"2\" valign=\"top\">3059904</th>\n",
       "      <th>3379952</th>\n",
       "      <td>\\x138b40cbe1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3379952</th>\n",
       "      <td>\\x138b4027c3</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                         user_hll\n",
       "xbins_match ybins_match                          \n",
       "2559904     3379952                  \\x138b4054e2\n",
       "2859904     3379952      \\x138b40200439019663c582\n",
       "            3379952                  \\x138b40c582\n",
       "3059904     3379952                  \\x138b40cbe1\n",
       "            3379952                  \\x138b4027c3"
      ]
     },
     "execution_count": 49,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%time\n",
    "bin_chunked_coordinates(chunked_df)\n",
    "chunked_df[0].head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Union HLL Sets per grid-id and calculate cardinality (estimated distinct user count):"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 50,
   "metadata": {
    "tags": [
     "active-ipynb"
    ]
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Mapped ~5000000 coordinates to bins\n"
     ]
    }
   ],
   "source": [
    "join_chunkeddf_grid(\n",
    "    chunked_df=chunked_df, grid=grid, metric=\"usercount_est\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 51,
   "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_est</th>\n",
       "      <th>usercount_est</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 6179952.000, -240096.000...</td>\n",
       "      <td>772</td>\n",
       "      <td>65</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6079952</th>\n",
       "      <td>POLYGON ((-340096.000 6079952.000, -240096.000...</td>\n",
       "      <td>12926</td>\n",
       "      <td>521</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5979952</th>\n",
       "      <td>POLYGON ((-340096.000 5979952.000, -240096.000...</td>\n",
       "      <td>5181</td>\n",
       "      <td>232</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5879952</th>\n",
       "      <td>POLYGON ((-340096.000 5879952.000, -240096.000...</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5779952</th>\n",
       "      <td>POLYGON ((-340096.000 5779952.000, -240096.000...</td>\n",
       "      <td>1531</td>\n",
       "      <td>148</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                                          geometry  \\\n",
       "xbin    ybin                                                         \n",
       "-340096 6179952  POLYGON ((-340096.000 6179952.000, -240096.000...   \n",
       "        6079952  POLYGON ((-340096.000 6079952.000, -240096.000...   \n",
       "        5979952  POLYGON ((-340096.000 5979952.000, -240096.000...   \n",
       "        5879952  POLYGON ((-340096.000 5879952.000, -240096.000...   \n",
       "        5779952  POLYGON ((-340096.000 5779952.000, -240096.000...   \n",
       "\n",
       "                 postcount_est  usercount_est  \n",
       "xbin    ybin                                   \n",
       "-340096 6179952            772             65  \n",
       "        6079952          12926            521  \n",
       "        5979952           5181            232  \n",
       "        5879952              1              1  \n",
       "        5779952           1531            148  "
      ]
     },
     "execution_count": 51,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "grid[grid[\"usercount_est\"]> 0].head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Look at this. There're many polygons were thounsands of photos have been created by only few users. Lets see how this affects our test map.."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Preview user count map"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 52,
   "metadata": {
    "tags": [
     "active-ipynb"
    ]
   },
   "outputs": [
    {
     "data": {
      "image/png": "[encoded data, 93236 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='Estimated User Count',\n",
    "    column='usercount_est', save_fig='usercount_sample_est.png',\n",
    "    bbox=bbox_italy, world=world)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## C: Estimated User Days\n",
    "\n",
    "Usually, due to the [Count Distinct Problem](https://en.wikipedia.org/wiki/Count-distinct_problem) increasing computation times will apply for more complex distinct queries. This is not the case when using HLL. Any count distinct (postcount, usercount etc.) requires the same amount of time. A useful metric introduced by Wood et al. (2013) is User Days, which lies inbetween Post Count and User Count because Users may be counted more than once if they visited the location on consecutive days. User Days particularly allows capturing the difference between local and tourist behaviour patterns. The rationale here is that locals visit few places very often. In contrast, tourists visit many places only once.\n",
    "\n",
    "The sequence of commands for userdays is exactly the same as for postcount and usercount above."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 53,
   "metadata": {
    "tags": [
     "active-ipynb"
    ]
   },
   "outputs": [],
   "source": [
    "usecols = ['latitude', 'longitude', 'date_hll']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 54,
   "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,\n",
    "    output: Path = OUTPUT,\n",
    "    dtypes = None) -> List[pd.DataFrame]:\n",
    "    \"\"\"Read data from csv, optionally clip to bbox and projet\"\"\"\n",
    "    if dtypes is None:\n",
    "        dtypes = {'latitude': float, 'longitude': float}\n",
    "    iter_csv = pd.read_csv(\n",
    "        output / \"csv\" / 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": 55,
   "metadata": {
    "tags": [
     "active-ipynb"
    ]
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Projected 92,775 coordinates\n",
      "CPU times: user 1.69 s, sys: 92.3 ms, total: 1.79 s\n",
      "Wall time: 1.78 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>date_hll</th>\n",
       "      <th>x</th>\n",
       "      <th>y</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>\\x138b40fbe2</td>\n",
       "      <td>2.641304e+06</td>\n",
       "      <td>3.369176e+06</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>\\x138b4021613a629864ee41f2a1</td>\n",
       "      <td>2.874180e+06</td>\n",
       "      <td>3.369176e+06</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>\\x138b40f2a1</td>\n",
       "      <td>2.878265e+06</td>\n",
       "      <td>3.369176e+06</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>\\x138b4067847141b903c5e3</td>\n",
       "      <td>3.123398e+06</td>\n",
       "      <td>3.369176e+06</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>\\x138b40a2e1</td>\n",
       "      <td>3.135655e+06</td>\n",
       "      <td>3.369176e+06</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                       date_hll             x             y\n",
       "0                  \\x138b40fbe2  2.641304e+06  3.369176e+06\n",
       "1  \\x138b4021613a629864ee41f2a1  2.874180e+06  3.369176e+06\n",
       "2                  \\x138b40f2a1  2.878265e+06  3.369176e+06\n",
       "3      \\x138b4067847141b903c5e3  3.123398e+06  3.369176e+06\n",
       "4                  \\x138b40a2e1  3.135655e+06  3.369176e+06"
      ]
     },
     "execution_count": 55,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%time\n",
    "chunked_df = read_project_chunked(\n",
    "    filename=\"yfcc_latlng.csv\",\n",
    "    usecols=usecols,\n",
    "    bbox=bbox_italy_buf)\n",
    "chunked_df[0].head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 56,
   "metadata": {
    "tags": [
     "active-ipynb"
    ]
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Binned 92,775 coordinates..\n",
      "CPU times: user 37.9 ms, sys: 3.92 ms, total: 41.8 ms\n",
      "Wall time: 38 ms\n"
     ]
    }
   ],
   "source": [
    "%%time\n",
    "bin_chunked_coordinates(chunked_df)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 57,
   "metadata": {
    "tags": [
     "active-ipynb"
    ]
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Mapped ~5000000 coordinates to bins\n"
     ]
    }
   ],
   "source": [
    "join_chunkeddf_grid(\n",
    "    chunked_df=chunked_df, grid=grid, metric=\"userdays_est\")"
   ]
  },
  {
   "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",
       "      <th>date_hll</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>2559904</th>\n",
       "      <th>3379952</th>\n",
       "      <td>\\x138b40fbe2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"2\" valign=\"top\">2859904</th>\n",
       "      <th>3379952</th>\n",
       "      <td>\\x138b4021613a629864ee41f2a1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3379952</th>\n",
       "      <td>\\x138b40f2a1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"2\" valign=\"top\">3059904</th>\n",
       "      <th>3379952</th>\n",
       "      <td>\\x138b4067847141b903c5e3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3379952</th>\n",
       "      <td>\\x138b40a2e1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                             date_hll\n",
       "xbins_match ybins_match                              \n",
       "2559904     3379952                      \\x138b40fbe2\n",
       "2859904     3379952      \\x138b4021613a629864ee41f2a1\n",
       "            3379952                      \\x138b40f2a1\n",
       "3059904     3379952          \\x138b4067847141b903c5e3\n",
       "            3379952                      \\x138b40a2e1"
      ]
     },
     "execution_count": 58,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "chunked_df[0].head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 59,
   "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_est</th>\n",
       "      <th>usercount_est</th>\n",
       "      <th>userdays_est</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 6179952.000, -240096.000...</td>\n",
       "      <td>772</td>\n",
       "      <td>65</td>\n",
       "      <td>164</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6079952</th>\n",
       "      <td>POLYGON ((-340096.000 6079952.000, -240096.000...</td>\n",
       "      <td>12926</td>\n",
       "      <td>521</td>\n",
       "      <td>2545</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5979952</th>\n",
       "      <td>POLYGON ((-340096.000 5979952.000, -240096.000...</td>\n",
       "      <td>5181</td>\n",
       "      <td>232</td>\n",
       "      <td>778</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5879952</th>\n",
       "      <td>POLYGON ((-340096.000 5879952.000, -240096.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 5779952.000, -240096.000...</td>\n",
       "      <td>1531</td>\n",
       "      <td>148</td>\n",
       "      <td>341</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                                          geometry  \\\n",
       "xbin    ybin                                                         \n",
       "-340096 6179952  POLYGON ((-340096.000 6179952.000, -240096.000...   \n",
       "        6079952  POLYGON ((-340096.000 6079952.000, -240096.000...   \n",
       "        5979952  POLYGON ((-340096.000 5979952.000, -240096.000...   \n",
       "        5879952  POLYGON ((-340096.000 5879952.000, -240096.000...   \n",
       "        5779952  POLYGON ((-340096.000 5779952.000, -240096.000...   \n",
       "\n",
       "                 postcount_est  usercount_est  userdays_est  \n",
       "xbin    ybin                                                 \n",
       "-340096 6179952            772             65           164  \n",
       "        6079952          12926            521          2545  \n",
       "        5979952           5181            232           778  \n",
       "        5879952              1              1             1  \n",
       "        5779952           1531            148           341  "
      ]
     },
     "execution_count": 59,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "grid[grid[\"userdays_est\"]> 0].head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 60,
   "metadata": {
    "tags": [
     "active-ipynb"
    ]
   },
   "outputs": [
    {
     "data": {
      "image/png": "[encoded data, 93596 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='Estimated User Days',\n",
    "    column='userdays_est', save_fig='userdays_sample_est.png',\n",
    "    bbox=bbox_italy, world=world)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "There're other approaches for further reducing noise. For example, to reduce the impact of automatic capturing devices (such as webcams uploading x pictures per day), a possibility is to count distinct **userlocations**. For userlocations metric, a user would be counted multiple times per grid bin only for pictures with different lat/lng. Or the number of distinct **userlocationdays** (etc.). These metrics easy to implement using hll, but would be quite difficult to compute using raw data."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Prepare methods\n",
    "\n",
    "Lets summarize the above code in a few methods:"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Plotting preparation**\n",
    "\n",
    "The method below utilizes many of the methods defined for raw data processing."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 63,
   "metadata": {
    "tags": [
     "highlight"
    ]
   },
   "outputs": [],
   "source": [
    "def load_plot(\n",
    "    grid: gp.GeoDataFrame, title: str, inverse: bool = None,\n",
    "    metric: str = \"postcount_est\", store_fig: str = None, store_pickle: str = None,\n",
    "    chunk_size: int = CHUNK_SIZE, benchmark_data: Optional[bool] = None,\n",
    "    column_metric_ref: Dict[str,str] = COLUMN_METRIC_REF):\n",
    "    \"\"\"Load data, bin coordinates, estimate distinct counts (cardinality) and plot map\n",
    "    \n",
    "        Args:\n",
    "        data: Path to read input CSV\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",
    "        benchmark_data: If True, hll_sketches will not be removed \n",
    "            after final estimation of cardinality\n",
    "    \"\"\"\n",
    "    usecols = ['latitude', 'longitude']\n",
    "    column = column_metric_ref.get(metric)\n",
    "    usecols.append(column)\n",
    "    # get data from csv\n",
    "    chunked_df = read_project_chunked(\n",
    "        filename=\"yfcc_latlng.csv\",\n",
    "        usecols=usecols)\n",
    "    # bin coordinates\n",
    "    bin_chunked_coordinates(chunked_df)\n",
    "    # reset metric column\n",
    "    reset_metrics(grid, [metric], setzero=False)\n",
    "    print(\"Getting cardinality per bin..\")\n",
    "    # union hll sets per chunk and \n",
    "    # calculate distinct counts on last iteration\n",
    "    join_chunkeddf_grid(\n",
    "        chunked_df=chunked_df, grid=grid,\n",
    "        metric=metric, chunk_size=chunk_size,\n",
    "        benchmark_data=benchmark_data)\n",
    "    # store intermediate data\n",
    "    if store_pickle:\n",
    "        print(\"Storing aggregate data as pickle..\")\n",
    "        grid.to_pickle(output / \"pickles\" / store_pickle)\n",
    "    print(\"Plotting figure..\")\n",
    "    plot_figure(grid=grid, title=title, inverse=inverse, metric=metric, store_fig=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": 64,
   "metadata": {
    "tags": [
     "active-ipynb"
    ]
   },
   "outputs": [],
   "source": [
    "reset_metrics(grid, [\"postcount_est\", \"usercount_est\", \"userdays_est\"])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 65,
   "metadata": {
    "tags": [
     "active-ipynb"
    ]
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Mapped ~5000000 coordinates to bins\n",
      "Plotting figure..\n",
      "Classifying bins..\n",
      "Formatting legend..\n",
      "Storing figure as png..\n",
      "peak memory: 1277.17 MiB, increment: 633.00 MiB\n",
      "CPU times: user 51.5 s, sys: 2.64 s, total: 54.1 s\n",
      "Wall time: 1min 16s\n"
     ]
    },
    {
     "data": {
      "image/png": "[encoded data, 210852 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, title=f'Estimated YFCC Post Count per {int(GRID_SIZE_METERS/1000)}km grid',\n",
    "    inverse=False, store_fig=\"yfcc_postcount_est.png\", benchmark_data=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 66,
   "metadata": {
    "tags": [
     "active-ipynb"
    ]
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Mapped ~5000000 coordinates to bins\n",
      "Plotting figure..\n",
      "Classifying bins..\n",
      "Formatting legend..\n",
      "Storing figure as png..\n",
      "peak memory: 1177.50 MiB, increment: 288.57 MiB\n",
      "CPU times: user 50.3 s, sys: 1.73 s, total: 52 s\n",
      "Wall time: 1min 7s\n"
     ]
    },
    {
     "data": {
      "image/png": "[encoded data, 207240 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, title=f'Estimated YFCC User Count per {int(GRID_SIZE_METERS/1000)}km grid',\n",
    "    inverse=False, store_fig=\"yfcc_usercount_est.png\",\n",
    "    metric=\"usercount_est\", benchmark_data=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 67,
   "metadata": {
    "tags": [
     "active-ipynb"
    ]
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Mapped ~5000000 coordinates to bins\n",
      "Plotting figure..\n",
      "Classifying bins..\n",
      "Formatting legend..\n",
      "Storing figure as png..\n",
      "peak memory: 1193.97 MiB, increment: 344.04 MiB\n",
      "CPU times: user 49.2 s, sys: 1.82 s, total: 51 s\n",
      "Wall time: 1min 8s\n"
     ]
    },
    {
     "data": {
      "image/png": "[encoded data, 207964 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, title=f'Estimated YFCC User Days per {int(GRID_SIZE_METERS/1000)}km grid',\n",
    "    inverse=False, store_fig=\"yfcc_userdays_est.png\",\n",
    "    metric=\"userdays_est\", benchmark_data=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Have a look at the final `grid` with the estimated cardinality for postcount, usercount and userdays\n",
    "\n",
    "it is possible to make an immediate validation of the numbers by verifying that `postcount` >= `userdays` >= `usercount`. On very rare occasions and edge cases, this may invalidate due to the estimation error of 3 to 5% of HyperLogLog derived cardinality."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 68,
   "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>postcount_est</th>\n",
       "      <th>usercount_est</th>\n",
       "      <th>userdays_est</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>-18040096</th>\n",
       "      <th>79952</th>\n",
       "      <td>7</td>\n",
       "      <td>2</td>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>-17640096</th>\n",
       "      <th>-2020048</th>\n",
       "      <td>630</td>\n",
       "      <td>19</td>\n",
       "      <td>68</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"2\" valign=\"top\">-17540096</th>\n",
       "      <th>-2020048</th>\n",
       "      <td>55</td>\n",
       "      <td>2</td>\n",
       "      <td>11</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>-2120048</th>\n",
       "      <td>3</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>-17440096</th>\n",
       "      <th>-1620048</th>\n",
       "      <td>4</td>\n",
       "      <td>2</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                    postcount_est  usercount_est  userdays_est\n",
       "xbin      ybin                                                \n",
       "-18040096  79952                7              2             7\n",
       "-17640096 -2020048            630             19            68\n",
       "-17540096 -2020048             55              2            11\n",
       "          -2120048              3              1             1\n",
       "-17440096 -1620048              4              2             4"
      ]
     },
     "execution_count": 68,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "grid[grid[\"postcount_est\"]>1].drop(\n",
    "    ['geometry', 'usercount_hll', 'postcount_hll', 'userdays_hll'], axis=1, errors=\"ignore\").head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Final HLL Sets are also available, as benchmark data, in columns `usercount_hll`, `postcount_hll`, `userdays_hll` columns:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 69,
   "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>postcount_hll</th>\n",
       "      <th>usercount_hll</th>\n",
       "      <th>userdays_hll</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>-18040096</th>\n",
       "      <th>79952</th>\n",
       "      <td>\\x138b4022a12d41476195819b21e0a1f881</td>\n",
       "      <td>\\x138b401ac2d3a2</td>\n",
       "      <td>\\x138b400ae459a171e19bc2a242a841b322</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>-17640096</th>\n",
       "      <th>-2020048</th>\n",
       "      <td>\\x138b400022006300e501420244026102c40343038103...</td>\n",
       "      <td>\\x138b4000a208c3100211c314e1176118012be4450357...</td>\n",
       "      <td>\\x138b4008220f4115a12a212ac131a432a1370141e247...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"2\" valign=\"top\">-17540096</th>\n",
       "      <th>-2020048</th>\n",
       "      <td>\\x138b400024074109c10be413811c41246425452e4132...</td>\n",
       "      <td>\\x138b401002b843</td>\n",
       "      <td>\\x138b400661170230e138634b624c216d8174217fe38a...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>-2120048</th>\n",
       "      <td>\\x138b407ac2c0e2dce1</td>\n",
       "      <td>\\x138b4054e1</td>\n",
       "      <td>\\x138b40c301</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>-17440096</th>\n",
       "      <th>-1620048</th>\n",
       "      <td>\\x138b4045a18b22d821fc82</td>\n",
       "      <td>\\x138b40182272e1</td>\n",
       "      <td>\\x138b4063e172218ce2d9e2</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                                        postcount_hll  \\\n",
       "xbin      ybin                                                          \n",
       "-18040096  79952                 \\x138b4022a12d41476195819b21e0a1f881   \n",
       "-17640096 -2020048  \\x138b400022006300e501420244026102c40343038103...   \n",
       "-17540096 -2020048  \\x138b400024074109c10be413811c41246425452e4132...   \n",
       "          -2120048                               \\x138b407ac2c0e2dce1   \n",
       "-17440096 -1620048                           \\x138b4045a18b22d821fc82   \n",
       "\n",
       "                                                        usercount_hll  \\\n",
       "xbin      ybin                                                          \n",
       "-18040096  79952                                     \\x138b401ac2d3a2   \n",
       "-17640096 -2020048  \\x138b4000a208c3100211c314e1176118012be4450357...   \n",
       "-17540096 -2020048                                   \\x138b401002b843   \n",
       "          -2120048                                       \\x138b4054e1   \n",
       "-17440096 -1620048                                   \\x138b40182272e1   \n",
       "\n",
       "                                                         userdays_hll  \n",
       "xbin      ybin                                                         \n",
       "-18040096  79952                 \\x138b400ae459a171e19bc2a242a841b322  \n",
       "-17640096 -2020048  \\x138b4008220f4115a12a212ac131a432a1370141e247...  \n",
       "-17540096 -2020048  \\x138b400661170230e138634b624c216d8174217fe38a...  \n",
       "          -2120048                                       \\x138b40c301  \n",
       "-17440096 -1620048                           \\x138b4063e172218ce2d9e2  "
      ]
     },
     "execution_count": 69,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "grid[grid[\"postcount_est\"]>1].drop(\n",
    "    ['geometry', 'usercount_est', 'postcount_est', 'userdays_est'], axis=1, errors=\"ignore\").head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Save & load intermediate and benchmark 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": [
    "Convert/store to CSV (aggregate columns and indexes only):"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 70,
   "metadata": {
    "tags": [
     "active-ipynb"
    ]
   },
   "outputs": [],
   "source": [
    "grid_agg_tocsv(grid, \"yfcc_all_est.csv\", metrics=[\"postcount_est\", \"usercount_est\", \"userdays_est\"])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Store results as benchmark data (with hll sketches):**\n",
    "\n",
    "**a)** Published data scenario\n",
    "\n",
    "As a minimal protection against intersection attacks on published data, only export\n",
    "hll sets with cardinality > 100."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 71,
   "metadata": {
    "tags": [
     "active-ipynb"
    ]
   },
   "outputs": [],
   "source": [
    "grid_agg_tocsv(\n",
    "    grid[grid[\"usercount_est\"]>100], \"yfcc_all_est_benchmark.csv\", \n",
    "    metrics = [\"postcount_est\", \"usercount_est\", \"userdays_est\",\n",
    "               \"usercount_hll\", \"postcount_hll\", \"userdays_hll\"])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Size of benchmark data:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 72,
   "metadata": {
    "tags": [
     "active-ipynb"
    ]
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Size: 10.61 MB\n"
     ]
    }
   ],
   "source": [
    "benchmark_size_mb = Path(OUTPUT / \"csv\" / \"yfcc_all_est_benchmark.csv\").stat().st_size / (1024*1024)\n",
    "print(f\"Size: {benchmark_size_mb:.2f} MB\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**b)** Internal database scenario\n",
    "\n",
    "Export all hll sets, as a means to demonstrate internal data compromisation."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 73,
   "metadata": {
    "tags": [
     "active-ipynb"
    ]
   },
   "outputs": [],
   "source": [
    "grid_agg_tocsv(\n",
    "    grid, \"yfcc_all_est_benchmark_internal.csv\", \n",
    "    metrics = [\"postcount_est\", \"usercount_est\", \"userdays_est\",\n",
    "               \"usercount_hll\", \"postcount_hll\", \"userdays_hll\"])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Size of benchmark data:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 74,
   "metadata": {
    "tags": [
     "active-ipynb"
    ]
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Size: 19.80 MB\n"
     ]
    }
   ],
   "source": [
    "benchmark_size_mb = Path(OUTPUT / \"csv\" / \"yfcc_all_est_benchmark_internal.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": "markdown",
   "metadata": {},
   "source": [
    "To create a new grid and load aggregate counts from CSV:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 75,
   "metadata": {
    "tags": [
     "active-ipynb"
    ]
   },
   "outputs": [],
   "source": [
    "grid = grid_agg_fromcsv(\n",
    "    OUTPUT / \"csv\" / \"yfcc_all_est.csv\",\n",
    "    columns=[\"xbin\", \"ybin\", \"postcount_est\", \"usercount_est\", \"userdays_est\"])"
   ]
  },
  {
   "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": 76,
   "metadata": {
    "tags": [
     "active-ipynb"
    ]
   },
   "outputs": [],
   "source": [
    "grid.to_pickle(OUTPUT / \"pickles\" / \"yfcc_all_est.pkl\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Load pickled dataframe:**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 77,
   "metadata": {
    "tags": [
     "active-ipynb"
    ]
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "CPU times: user 1.27 s, sys: 0 ns, total: 1.27 s\n",
      "Wall time: 1.27 s\n"
     ]
    }
   ],
   "source": [
    "%%time\n",
    "grid = pd.read_pickle(OUTPUT / \"pickles\" / \"yfcc_all_est.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": 78,
   "metadata": {
    "tags": [
     "active-ipynb"
    ]
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Classifying bins..\n",
      "Formatting legend..\n"
     ]
    },
    {
     "data": {
      "image/png": "[encoded data, 198292 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_est\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**To merge results of raw and hll dataset:**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 79,
   "metadata": {
    "tags": [
     "active-ipynb"
    ]
   },
   "outputs": [],
   "source": [
    "grid_est = pd.read_pickle(OUTPUT / \"pickles\" / \"yfcc_all_est.pkl\")\n",
    "grid_raw = pd.read_pickle(OUTPUT / \"pickles\" / \"yfcc_all_raw.pkl\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 80,
   "metadata": {
    "tags": [
     "active-ipynb"
    ]
   },
   "outputs": [],
   "source": [
    "grid = grid_est.merge(\n",
    "    grid_raw[['postcount', 'usercount', 'userdays']],\n",
    "    left_index=True, right_index=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Have a look at the numbers for exact and estimated values. Smaller values are exact in both hll and raw because Sparse Mode is used."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 81,
   "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_est</th>\n",
       "      <th>usercount_est</th>\n",
       "      <th>userdays_est</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",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>-17640096</th>\n",
       "      <th>-2020048</th>\n",
       "      <td>POLYGON ((-17640096.000 -2020048.000, -1754009...</td>\n",
       "      <td>630</td>\n",
       "      <td>19</td>\n",
       "      <td>68</td>\n",
       "      <td>634</td>\n",
       "      <td>19</td>\n",
       "      <td>67</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>-17040096</th>\n",
       "      <th>-1620048</th>\n",
       "      <td>POLYGON ((-17040096.000 -1620048.000, -1694009...</td>\n",
       "      <td>456</td>\n",
       "      <td>17</td>\n",
       "      <td>82</td>\n",
       "      <td>449</td>\n",
       "      <td>17</td>\n",
       "      <td>81</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"3\" valign=\"top\">-16940096</th>\n",
       "      <th>-1620048</th>\n",
       "      <td>POLYGON ((-16940096.000 -1620048.000, -1684009...</td>\n",
       "      <td>990</td>\n",
       "      <td>39</td>\n",
       "      <td>125</td>\n",
       "      <td>960</td>\n",
       "      <td>39</td>\n",
       "      <td>125</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>-1720048</th>\n",
       "      <td>POLYGON ((-16940096.000 -1720048.000, -1684009...</td>\n",
       "      <td>120</td>\n",
       "      <td>11</td>\n",
       "      <td>36</td>\n",
       "      <td>124</td>\n",
       "      <td>11</td>\n",
       "      <td>36</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>-2220048</th>\n",
       "      <td>POLYGON ((-16940096.000 -2220048.000, -1684009...</td>\n",
       "      <td>171</td>\n",
       "      <td>13</td>\n",
       "      <td>37</td>\n",
       "      <td>176</td>\n",
       "      <td>13</td>\n",
       "      <td>37</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                                             geometry  \\\n",
       "xbin      ybin                                                          \n",
       "-17640096 -2020048  POLYGON ((-17640096.000 -2020048.000, -1754009...   \n",
       "-17040096 -1620048  POLYGON ((-17040096.000 -1620048.000, -1694009...   \n",
       "-16940096 -1620048  POLYGON ((-16940096.000 -1620048.000, -1684009...   \n",
       "          -1720048  POLYGON ((-16940096.000 -1720048.000, -1684009...   \n",
       "          -2220048  POLYGON ((-16940096.000 -2220048.000, -1684009...   \n",
       "\n",
       "                    postcount_est  usercount_est  userdays_est  postcount  \\\n",
       "xbin      ybin                                                              \n",
       "-17640096 -2020048            630             19            68        634   \n",
       "-17040096 -1620048            456             17            82        449   \n",
       "-16940096 -1620048            990             39           125        960   \n",
       "          -1720048            120             11            36        124   \n",
       "          -2220048            171             13            37        176   \n",
       "\n",
       "                    usercount  userdays  \n",
       "xbin      ybin                           \n",
       "-17640096 -2020048         19        67  \n",
       "-17040096 -1620048         17        81  \n",
       "-16940096 -1620048         39       125  \n",
       "          -1720048         11        36  \n",
       "          -2220048         13        37  "
      ]
     },
     "execution_count": 81,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "grid[grid[\"usercount_est\"]>5].head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Close DB connection & Create notebook HTML"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 82,
   "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 ./03_yfcc_gridagg_hll.ipynb to html_toc\n",
      "[NbConvertApp] Writing 1563704 bytes to ../out/html/03_yfcc_gridagg_hll.html\n"
     ]
    }
   ],
   "source": [
    "!jupyter nbconvert --to html_toc \\\n",
    "    --output-dir=../out/html ./03_yfcc_gridagg_hll.ipynb \\\n",
    "    --template=../nbconvert.tpl \\\n",
    "    --ExtractOutputPreprocessor.enabled=False # create single output file"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Interpretation of results\n",
    "\n",
    "The last part of the tutorial will look at ways to improve interpretation of results. Interactive bokeh maps and widget tab display are used to make comparison of raw and hll results easier. Follow in in [04_interpretation.ipynb](04_interpretation.ipynb)\n"
   ]
  }
 ],
 "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
}
