00_taa_prep_funcs.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "b2d27f7b",
   "metadata": {},
   "source": [
    "## Functions for TAA Component Preparation\n",
    "\n",
    "Complementary notebook to executable on the same topic.\n",
    "Functions are presented in an order closely following the exe notebook steps:"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "08e8fd75",
   "metadata": {},
   "source": [
    "#### Libraries"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "252c4785",
   "metadata": {},
   "outputs": [],
   "source": [
    "import os\n",
    "import re\n",
    "import numpy as np                                      # basic numeric calculation\n",
    "import pandas as pd                                     # split-apply-combine operations on dataframe\n",
    " \n",
    "import fiona\n",
    "import geopandas as gpd\n",
    "\n",
    "import rasterio\n",
    "import rasterstats\n",
    " \n",
    "import matplotlib as mpl\n",
    "import matplotlib.pyplot as plt                         # plotting tool\n",
    "from matplotlib.patches import RegularPolygon           # drawing hexagons\n",
    " \n",
    "import shapely                                          # to attribute geometric properties for shapes\n",
    "from shapely.geometry import Polygon, Point"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "4472a15d",
   "metadata": {},
   "source": [
    "#### 1. General functions\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "1e4e30f6",
   "metadata": {},
   "outputs": [],
   "source": [
    "def get_agg_cols(data, from_position):\n",
    "  return list(data.columns)[from_position:]\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "c1d06779",
   "metadata": {},
   "outputs": [],
   "source": [
    "def snake2camel(name):\n",
    "    return re.sub(r'(?:^|_)([a-z])', lambda x: x.group(1).upper(), name)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "39be0e19",
   "metadata": {},
   "outputs": [],
   "source": [
    "def camel2spaced(name):\n",
    "    return re.sub('([a-z])([A-Z])', '\\\\g<1> \\\\g<2>', name)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "fb57551d",
   "metadata": {},
   "outputs": [],
   "source": [
    "def read_geojson(path: str, file_name: str):\n",
    "  \"\"\"\n",
    "    A function to read a geojson file \n",
    "    \n",
    "    Arguments: \n",
    "    path - address to directory to read from\n",
    "    file_name - name of file to read\n",
    " \n",
    "    Returns: GeoDataFrame as read from geojson file\n",
    "  \"\"\"\n",
    "    \n",
    "  file = open(os.path.join(path, file_name))\n",
    "  df = gpd.read_file(file)\n",
    "  df = df.set_crs(epsg=4326)\n",
    "  \n",
    "  return df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "4cfa6a36",
   "metadata": {},
   "outputs": [],
   "source": [
    "def read_geojson_(path: str, file_name: str):\n",
    "  \"\"\"\n",
    "    A function to read a geojson file \n",
    "    \n",
    "    Arguments: \n",
    "    path - address to directory to read from\n",
    "    file_name - name of file to read\n",
    " \n",
    "    Returns: GeoDataFrame as read from geojson file\n",
    "  \"\"\"\n",
    "    \n",
    "  file = open(os.path.join(path, file_name),encoding=\"utf-8\")\n",
    "  df = gpd.read_file(file)\n",
    "  df = df.set_crs(epsg=4326)\n",
    "  \n",
    "  return df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "4118d91e",
   "metadata": {},
   "outputs": [],
   "source": [
    "def _to_2d(x, y, z):\n",
    "    return tuple(filter(None, [x, y]))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "6275101d",
   "metadata": {},
   "outputs": [],
   "source": [
    "def construct_wmean_func(carto_data_name, cred_container, w_col):\n",
    "  \"\"\"\n",
    "    A function to create a weighted mean function object when given input specs.\n",
    "    \n",
    "    Arguments: \n",
    "    carto_data_name {string} -- A string containing the name of a carto dataset (for live read)\n",
    "    cred_container {string} -- A string containing the file path and name of a json on carto credentials (for live read)\n",
    "    w_col {string} -- A string containing the name of a column to use for weighing\n",
    " \n",
    "    Returns: function -- \n",
    "  \"\"\"  \n",
    "  \n",
    "  carto_data = ingest_carto_data(carto_data_name, cred_container)\n",
    "  wmean = lambda x: np.average(x, weights=carto_data.loc[x.index, w_col])\n",
    "  wmean.__name__ = \"wmean\"\n",
    "  \n",
    "  return wmean\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "dcdfd730",
   "metadata": {},
   "outputs": [],
   "source": [
    "def construct_wmean_func_from_set(data, w_col):\n",
    "  \"\"\"\n",
    "    A function to create a weighted mean function object when given input specs.\n",
    "    \n",
    "    Arguments: \n",
    "    data {gpd.DataFrame} -- A dataframe to index on for custom wmean function\n",
    "    w_col {string} -- A string containing the name of a column to use for weighing\n",
    " \n",
    "    Returns: function -- \n",
    "  \"\"\"  \n",
    "  \n",
    "  wmean = lambda x: np.average(x, weights=data.loc[x.index, w_col])\n",
    "  wmean.__name__ = \"wmean\"\n",
    "  \n",
    "  return wmean\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "c6877bf3",
   "metadata": {},
   "outputs": [],
   "source": [
    "def derive_outlets_by_chan(cmd, geo_data, id_var=\"CUSTOMER_ID\"):\n",
    "  \"\"\"\n",
    "    A function to engineer features for the number of outlets - by type for geographical grouping (hexagons, or trade areas).\n",
    "    \n",
    "    Arguments: \n",
    "    cmd {SparkDataFrame} -- WP population data - aggregated to hexagon level\n",
    "    geo_data {GeoDataFrame} -- OSM poi data - aggregated to hexagon level\n",
    "    id_var {str} -- Name of customer id variable\n",
    " \n",
    "    Returns: Pandas DataFrame with columns for the number of outlet types within each hexagon/trade area.\n",
    "  \"\"\"     \n",
    "  \n",
    "  # 1) Define channel subcategories\n",
    "  outlets_by_chan = cmd. \\\n",
    "    select(*[id_var,\"CHANNEL_CUSTOM\", \"LATITUDE\", \"LONGITUDE\"]). \\\n",
    "    toPandas()\n",
    " \n",
    " \n",
    "  outlets_by_chan['CHANNEL_COUNTS'] = outlets_by_chan['CHANNEL_CUSTOM']\n",
    "\n",
    "  outlets = outlets_by_chan[\"CHANNEL_COUNTS\"].unique().tolist()\n",
    "  \n",
    "  outlets_by_chan = outlets_by_chan.set_index([id_var, \"LATITUDE\", \"LONGITUDE\"])\n",
    "  outlets_by_chan = pd.get_dummies(outlets_by_chan['CHANNEL_COUNTS']).reset_index()\n",
    "  outlets_by_chan = gpd.GeoDataFrame(outlets_by_chan, geometry=gpd.points_from_xy(outlets_by_chan[\"LONGITUDE\"], outlets_by_chan[\"LATITUDE\"]), crs=\"epsg:4326\")\n",
    "  \n",
    "  # 2) Aggregate to hexagon level \n",
    "#   outlets_by_chan_ready_set = pd.merge(outlets_by_chan, geo_data, on=id_var, how=\"left\")\n",
    "  outlets_by_chan_ready_set = gpd.overlay(outlets_by_chan, geo_data, how=\"intersection\")\n",
    "  \n",
    "  outlets_by_chan_ready_set = outlets_by_chan_ready_set. \\\n",
    "    groupby(\"hex_id\"). \\\n",
    "    agg(dict(zip(outlets, [\"sum\"]*len(outlets)))). \\\n",
    "    reset_index()\n",
    "  \n",
    "  return outlets_by_chan_ready_set\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "05d7fdab",
   "metadata": {},
   "source": [
    "\n",
    "#### 2. Functions on preparing & overlaying a hexagonal grid over a territory"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "f7e2d646",
   "metadata": {},
   "outputs": [],
   "source": [
    "def haversine_custom(coord1, coord2):\n",
    "  \"\"\"\n",
    "      A function to determine the great-circle distance between 2 points on Earth given their longitudes and latitudes\n",
    " \n",
    "      Arguments: \n",
    "      coord1 - territory bounds for first point, lon & lat\n",
    "      coord2 - territory bounds for second point, lon & lat\n",
    " \n",
    "      Returns: Distance in meters\n",
    "  \"\"\" \n",
    "  \n",
    "  # Coordinates in decimal degrees (e.g. 43.60, -79.49)\n",
    "  lon1, lat1 = coord1\n",
    "  lon2, lat2 = coord2\n",
    " \n",
    "  # Radius of Earth in meters\n",
    "  R = 6371000  \n",
    " \n",
    "  phi_1 = np.radians(lat1)\n",
    "  phi_2 = np.radians(lat2)\n",
    "  delta_phi = np.radians(lat2 - lat1)\n",
    "  delta_lambda = np.radians(lon2 - lon1)\n",
    " \n",
    "  a = np.sin(delta_phi / 2.0) ** 2 + np.cos(phi_1) * np.cos(phi_2) * np.sin(delta_lambda / 2.0) ** 2\n",
    "  c = 2 * np.arctan2(np.sqrt(a),np.sqrt(1 - a))\n",
    " \n",
    "  # Output distance in meters\n",
    "  meters = R * c  \n",
    " \n",
    "  # Output distance in kilometers\n",
    "  km = meters / 1000.0  \n",
    "  meters = round(meters)\n",
    "  km = round(km, 3)\n",
    " \n",
    "  #print(f\"Distance: {meters} m\")\n",
    "  #print(f\"Distance: {km} km\")\n",
    " \n",
    "  return meters"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "57302252",
   "metadata": {},
   "outputs": [],
   "source": [
    " def form_hex_grid(territory, hex_diameter: int):\n",
    "  \"\"\"\n",
    "    A function to form a hexagonal grid\n",
    "    \n",
    "    Arguments: \n",
    "    territory - GeoDataFrame for a territory\n",
    "    hex_diameter - integer, diameter size to use in defining hexagon dimensions\n",
    " \n",
    "    Returns: GeoDataFrame with geometry for hexagonal grid formed for a territory provided\n",
    "  \"\"\" \n",
    "  \n",
    "  # 1) Define general hex parameters\n",
    "  xmin, ymin, xmax, ymax = [x * 1.01 for x in territory.total_bounds]\n",
    "  EW = haversine_custom((xmin,ymin),(xmax,ymin))\n",
    "  NS = haversine_custom((xmin,ymin),(xmin,ymax))\n",
    "  \n",
    "  # diamter of each hexagon in the grid\n",
    "  d = hex_diameter\n",
    "  \n",
    "  # horizontal width of hexagon = w = d* sin(60)\n",
    "  w = d*np.sin(np.pi/3)\n",
    "  \n",
    "  # Approximate number of hexagons per row = EW/w \n",
    "  n_cols = int(EW/w) + 1\n",
    "  \n",
    "  # Approximate number of hexagons per column = NS/d\n",
    "  n_rows = int(NS/w) + 1\n",
    "  \n",
    "  # 2) Add hex params to territory\n",
    "  \n",
    "  # ax = territory[[\"geometry\"]].boundary.plot(edgecolor='black', figsize=(30, 60))  #\n",
    "  \n",
    "  # width of hexagon\n",
    "  w = (xmax-xmin)/n_cols \n",
    "  \n",
    "  # diameter of hexagon\n",
    "  d = w/np.sin(np.pi/3)\n",
    "  \n",
    "  array_of_hexes = []\n",
    "  for rows in range(0,n_rows):\n",
    "      hcoord = np.arange(xmin,xmax,w) + (rows%2)*w/2\n",
    "      vcoord = [ymax- rows*d*0.75]*n_cols\n",
    "      for x, y in zip(hcoord, vcoord):  #, colors):\n",
    "          hexes = RegularPolygon((x, y), numVertices=6, radius=d/2, alpha=0.2, edgecolor='k')\n",
    "          verts = hexes.get_path().vertices\n",
    "          trans = hexes.get_patch_transform()\n",
    "          points = trans.transform(verts)\n",
    "          array_of_hexes.append(Polygon(points))\n",
    "          # ax.add_patch(hexes)  #\n",
    " \n",
    "  # ax.set_xlim([xmin, xmax])  #\n",
    "  # ax.set_ylim([ymin, ymax])  #\n",
    "  # plt.show()  #\n",
    "  \n",
    "  # 3) Form hex grid as gpd\n",
    "  hex_grid = gpd.GeoDataFrame({'geometry': array_of_hexes}, crs=\"EPSG:4326\")\n",
    "  hex_grid = hex_grid.to_crs(epsg=4326)\n",
    "  \n",
    "  return hex_grid\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "id": "89b5be1e",
   "metadata": {},
   "outputs": [],
   "source": [
    "def hexalize_territory(territory, hex_grid):\n",
    "  \"\"\"\n",
    "    A function to add hexagonal grid geometry to GeoDataFrame territory \n",
    "    \n",
    "    Arguments: \n",
    "    territory - GeoDataFrame for a territory\n",
    "    hex_grid - GeoDataFrame, hexagonal grid geometry as prepared for specified territory\n",
    " \n",
    "    Returns: GeoDataFrame of a territory overlayed with a hexagonal grid\n",
    "  \"\"\" \n",
    "  \n",
    "  territory_hex = gpd.overlay(hex_grid, territory)\n",
    "  territory_hex = gpd.GeoDataFrame(territory_hex, geometry='geometry', crs=\"EPSG:4326\")\n",
    "  territory_hex = territory_hex.reset_index()\n",
    "  territory_hex.rename(columns={'index': 'hex_id'}, inplace=True)\n",
    "  \n",
    "  return territory_hex\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "id": "ad253b2e",
   "metadata": {},
   "outputs": [],
   "source": [
    "def derive_hex_area(data):\n",
    "  \"\"\"\n",
    "    A function to derive the square kilometer area per hexagon \n",
    "    \n",
    "    Arguments: \n",
    "    data - GeoDataFrame to calculate the area for pre-specified polygons (hexagons)\n",
    " \n",
    "    Returns: GeoDataFrame expanded with a field on square kilometer are per hexagon\n",
    "  \"\"\"\n",
    "     \n",
    "  data['hexagon_area_sq_m'] = data.to_crs({'proj':'cea'})[\"geometry\"].area\n",
    "  data['hexagon_area_sq_km'] = data['hexagon_area_sq_m'] / 1000000  \n",
    "  \n",
    "#   data = data.drop(['hexagon_area_share', 'hexagon_area'], axis=1)\n",
    "  data = data.to_crs(epsg=4326)\n",
    "  \n",
    "  return data\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "id": "93848fa6",
   "metadata": {},
   "outputs": [],
   "source": [
    "def prep_territory_hex(hex_diameter: int = 2500,\n",
    "                       territory_scope: str = '/dbfs/mnt/AA/ba008/assets/shape_files/morocco_l2_adm_areas.shp',\n",
    "                       hex_set: str = None):\n",
    "  \"\"\"\n",
    "    A function to overlay a custom hexagonal grid over a territory\n",
    "    \n",
    "    Arguments:\n",
    "    hex_diameter - integer, diameter size to use in defining hexagon dimensions\n",
    "    territory_scope - string, name of shape file to draw areas to keep for the analysis (i.e. only marrakech_eccbc), default is set to full country\n",
    "    hex_set - string, name of json file to export\n",
    "    \n",
    "    Returns: GeoJSON file saved to fs - of a territory overlayed with a customizable hexagonal grid\n",
    "  \"\"\" \n",
    "    \n",
    "    \n",
    "  # 1) Load full territory data, i.e. from country shape file\n",
    "  territory = gpd.read_file(territory_scope)\n",
    "  territory = territory.to_crs(epsg=4326)\n",
    "    \n",
    "  # 2) Form hex grid for the relevant territory - post area selection\n",
    "  hex_grid = form_hex_grid(territory, hex_diameter)\n",
    "  \n",
    "  # 3) Add hexagonal grid to territory\n",
    "  territory_hex = hexalize_territory(territory, hex_grid)\n",
    "  \n",
    "  # 4) Add a field for the area per hexagon\n",
    "  territory_hex = derive_hex_area(territory_hex)\n",
    "  \n",
    "  # 5) Export\n",
    "  if hex_set is not None:\n",
    "    territory_hex.to_file(hex_set, driver='GeoJSON')\n",
    "  \n",
    "  return territory_hex"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "abbe128b",
   "metadata": {},
   "source": [
    "#### 3. Functions on preparinging external data sources and performing hexagonal-based aggregations"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "0bbc3928",
   "metadata": {},
   "source": [
    "###### 3.1.1. Wrapper function on population data, source: Worldpop "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "id": "ecb620e6",
   "metadata": {},
   "outputs": [],
   "source": [
    "def prep_pop_set(path_hex: str, hex_set: str, path_pop: str, pop_set: str, agg_funcs: list, export_agg_file: str):\n",
    "  \"\"\"\n",
    "    A function to derive aggregate population stats by hexagon\n",
    "    \n",
    "    Arguments: \n",
    "    path_hex - string, name of fs directory to read hex data from\n",
    "    hex_set - string, name of shape file containing hexagon grid (read as gpd)\n",
    "    path_pop - string, name of fs directory to read population data from\n",
    "    pop_set - string, name of tif file with population data (read as raster)\n",
    "    agg_funcs - list of strings with aggregation operations to be performed over the population data\n",
    "    export_agg_file - file path to export aggregated data to\n",
    " \n",
    "    Returns: GeoDataFrame with population stats aggregated by hexagon\n",
    "  \"\"\"\n",
    "  \n",
    "  set_prefix = 'pop_'\n",
    "  \n",
    "  # 1) Derive aggregated stats: over the raster data\n",
    "  output = gpd.GeoDataFrame.from_features(\n",
    "  rasterstats.zonal_stats(\n",
    "    read_geojson(path_hex, hex_set),\n",
    "    os.path.join(path_pop, pop_set),\n",
    "    stats=agg_funcs,\n",
    "    prefix=set_prefix,\n",
    "    geojson_out=True))\n",
    "  \n",
    "  # 2) Fix: a few NaN-s arise from the aggregation step -> replace with 0s\n",
    "  for i in range(0, len(agg_funcs), 1):\n",
    "    output[set_prefix+agg_funcs[i]].fillna(0, inplace=True)\n",
    "  \n",
    "  # 3) Set the coordinate reference system\n",
    "  output.set_crs(epsg=4326)\n",
    "  \n",
    "  # 4) Export to blob\n",
    "  output.to_file(export_agg_file, driver='GeoJSON')\n",
    "  \n",
    "  return output\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "91bfb1b3",
   "metadata": {},
   "source": [
    "###### 3.1.2 Wrapper function on osm poi data, source: OSM"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "id": "24923554",
   "metadata": {},
   "outputs": [],
   "source": [
    "def prep_osm_set(territory_hex: gpd.GeoDataFrame, path_osm: str, osm_set: str, export_agg_file: str = None):\n",
    "  \"\"\"\n",
    "    A function to derive the point-of-interest number per hexagon (count)\n",
    "    \n",
    "    Arguments: \n",
    "    path_hex - string, name of fs directory to read hex data from\n",
    "    hex_set - string, name of shape file containing hexagon grid (read as gdp)\n",
    "    path_osm - string, name of fs directory to read osm data from\n",
    "    osm_set - string, name of parquet file with osm data (read as pd)\n",
    "    export_agg_file - string, file path to export aggregated data to\n",
    " \n",
    "    Returns: GeoDataFrame with the number of poi-s per hexagon\n",
    "  \"\"\"  \n",
    "  \n",
    "  # 1) Read osm set\n",
    "  init =  pd.read_parquet(os.path.join(path_osm, osm_set)).filter([\"osm_id\", \"latitude\", \"longitude\"], axis=1)\n",
    "  output = gpd.GeoDataFrame(init, geometry=gpd.points_from_xy(init.longitude, init.latitude))\n",
    "  output = output.set_crs(epsg=4326)\n",
    "  \n",
    "  # 2) Add hex grid \n",
    "  output = gpd.sjoin(output, territory_hex, how=\"inner\", op=\"intersects\")\n",
    "  \n",
    "  # 3) Derive aggregated stats: over the goepandas data\n",
    "  output = output.groupby(['hex_id'])[['osm_id']].count()\n",
    "  output.reset_index(level=0, inplace=True)\n",
    "  output.rename(columns={'osm_id': 'osm_count'}, inplace=True)\n",
    "  \n",
    "  # 4) Export\n",
    "  if export_agg_file is not None:\n",
    "    output.to_parquet(export_agg_file)\n",
    "    \n",
    "  return output\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "79f50638",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "id": "16c315e1",
   "metadata": {},
   "source": [
    "###### 3.1.3. Wrapper function on customer spend data, source: Carto/Experian"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "id": "d2c75497",
   "metadata": {},
   "outputs": [],
   "source": [
    "def prep_carto_cexpend(territory_hex: gpd.GeoDataFrame, carto_data):\n",
    "  \"\"\"\n",
    "    A function to derive carto aggregations per hexagon\n",
    "    \n",
    "    Arguments: \n",
    "    territory_hex {gpd.GeoDataFrame} -- The hexagonal coverage of the territory\n",
    "    carto_data {pd.DataFrame} -- Carto data read in the notebook\n",
    " \n",
    "    Returns: GeoDataFrame with carto hexagon-level aggregations\n",
    "  \"\"\"  \n",
    "  \n",
    "  # 1) Set helper items\n",
    "  val_cols = [\"wvce_01\"]  ## --- may need changing -> expecting a list of column names \n",
    "  aggs = [[\"sum\"]] * len(val_cols)                           ## --- may need changing -> expecting a list of str-lists on function names (for naming @ step7)\n",
    "  \n",
    "  # 2) Read input sets\n",
    "  # carto_data = ingest_carto_data(carto_data_name, cred_container)\n",
    "#   territory_hex = read_geojson(path_hex, hex_set)\n",
    "  \n",
    "  # 3) Overlay carto and hexagonal grid\n",
    "  output = overlay_carto_hex(carto_data, territory_hex, carto_id_var=\"cartodb_id\", hex_id_var=\"hex_id\")\n",
    "  \n",
    "  # 4) Update columns to reflect the carto value share within each hexagon\n",
    "  output = reflect_carto_share(output, val_cols)\n",
    "   \n",
    "  # 5) Derive aggregated stats\n",
    "  output = output. \\\n",
    "    groupby(\"hex_id\"). \\\n",
    "    agg(dict(zip(val_cols, aggs)))\n",
    "  \n",
    "  # 6) Address naming\n",
    "  output = fix_agg_data_names(output, prefix=\"carto_\", id_var=\"hex_id\")\n",
    " \n",
    "  return output"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "d046170a",
   "metadata": {},
   "source": [
    "###### 3.1.4. Wrapper function on socio-demographic data, source: Carto/Experian"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "id": "393c6d98",
   "metadata": {},
   "outputs": [],
   "source": [
    "def prep_carto_sociodemo(territory_hex: gpd.GeoDataFrame, carto_data, hex_geom_name = \"territory_hex_geom\", carto_geom_name = \"carto_set_geom\"):\n",
    "  \"\"\"\n",
    "    A function to derive carto aggregations per hexagon\n",
    "    \n",
    "    Arguments: \n",
    "    territory_hex {gpd.GeoDataFrame} -- The hexagonal coverage of the territory\n",
    "    carto_data {pd.DataFrame} -- Carto data read in the notebook\n",
    " \n",
    "    Returns: GeoDataFrame with carto hexagon-level aggregations\n",
    "  \"\"\"  \n",
    "  \n",
    "  # 1) Set helper items\n",
    "  val_cols =['pop','hh','male','female','age_t0014', 'age_m0014', 'age_f0014', 'age_t1529', 'age_m1529','age_f1529','age_t3044','age_m3044','age_f3044','age_t4559','age_m4559','age_f4559','age_t60pl','age_m60pl','age_f60pl','di_mio','di_pc','di_ci']\n",
    "  # 2) Read input sets\n",
    "  # carto_data = ingest_carto_data(carto_data_name, cred_container)\n",
    "#   territory_hex = read_geojson(path_hex, hex_set)\n",
    "  \n",
    "  # 3) Overlay carto and hexagonal grid\n",
    "  output = overlay_carto_hex(carto_data, territory_hex, carto_id_var=\"cartodb_id\", hex_id_var=\"hex_id\")\n",
    "  \n",
    "  # Add aggregation functions - data needed for construct_wmean_func_from_set\n",
    "  aggs = [[\"sum\"]]*20 + [[\"mean\", construct_wmean_func_from_set(output, \"pop\")]]*2   ## --- may need changing -> expecting a list of str-lists on function names (for naming @ step7)\n",
    "  \n",
    "  # 4) Update columns to reflect the carto value share within each hexagon\n",
    "  output = reflect_carto_share(output, val_cols, hex_geom_name, carto_geom_name)\n",
    "   \n",
    "  # 5) Derive aggregated stats\n",
    "  output = output. \\\n",
    "    groupby(\"hex_id\"). \\\n",
    "    agg(dict(zip(val_cols, aggs)))\n",
    "  \n",
    "  # 6) Address naming\n",
    "  output = fix_agg_data_names(output, prefix=\"carto_\", id_var=\"hex_id\")\n",
    " \n",
    "  return output\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "43590c6d",
   "metadata": {},
   "source": [
    "###### 3.1.5. Wrapper function on activity/mobility data, source: Unacast"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "id": "f44f8a81",
   "metadata": {},
   "outputs": [],
   "source": [
    "def prep_carto_unacast(territory_hex: gpd.GeoDataFrame, carto_data):\n",
    "  \"\"\"\n",
    "    A function to derive carto aggregations per hexagon\n",
    "    \n",
    "    Arguments: \n",
    "    territory_hex {gpd.GeoDataFrame} -- The hexagonal coverage of the territory\n",
    "    carto_data {pd.DataFrame} -- Carto data read in the notebook\n",
    " \n",
    "    Returns: GeoDataFrame with carto hexagon-level aggregations\n",
    "  \"\"\"    \n",
    "    \n",
    "  # 1) Read input sets\n",
    "  # 1.1) Territory hex set \n",
    "#   territory_hex = read_geojson(path_hex, hex_set)\n",
    "  # 1.2) Carto set\n",
    "  # carto_data = ingest_carto_data(carto_data_name, cred_container)\n",
    "  carto_data = init_prep_carto_unacast(carto_data, id_var=\"seen_in\")\n",
    "  \n",
    "  # 2) Set helper items\n",
    "  # val_cols = get_agg_cols(data=carto_data, from_position=2)  ## --- may need changing -> expecting a list of column names (vestige)\n",
    "  vals_sum = list(carto_data[carto_data.filter(regex='staying').columns]) \n",
    "  vals_mean = list(carto_data[carto_data.filter(regex='proportion_|return_').columns])\n",
    "  val_cols = vals_sum + vals_mean\n",
    "  aggs = [[\"sum\"]]*len(vals_sum) + [[\"mean\"]]*len(vals_mean)   ## --- may need changing -> expecting a list of str-lists on function names (for naming @ step7)\n",
    "  \n",
    "  # 3) Overlay carto and hexagonal grid\n",
    "  output = overlay_carto_hex(carto_data, territory_hex, carto_id_var=\"seen_in\", hex_id_var=\"hex_id\")\n",
    "  \n",
    "  # 4) Update columns to reflect the carto value share within each hexagon\n",
    "  # AR: There's something wrong with this function\n",
    "  output = reflect_carto_share(output, val_cols)\n",
    "   \n",
    "  # 5) Derive aggregated stats\n",
    "  output = output. \\\n",
    "    groupby(\"hex_id\"). \\\n",
    "    agg(dict(zip(val_cols, aggs)))\n",
    "  \n",
    "  # 7) Address naming\n",
    "  output = fix_agg_data_names(output, prefix=\"carto_\", id_var=\"hex_id\")\n",
    "  \n",
    "  return output\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "b3e45dd8",
   "metadata": {},
   "source": [
    "###### 3.1.6. Wrapper function on poi data, source: Pitney Bowes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "id": "aa03c885",
   "metadata": {},
   "outputs": [],
   "source": [
    "def prep_carto_poi(territory_hex: gpd.GeoDataFrame, carto_data, carto_val_cols):\n",
    "  \"\"\"\n",
    "    A function to derive the carto point-of-interest number per hexagon (count), item/s to count may be changed if needed.\n",
    "    \n",
    "    Arguments: \n",
    "    territory_hex {gpd.GeoDataFrame} -- The hexagonal coverage of the territory\n",
    "    carto_data {pd.DataFrame} -- Carto data\n",
    "    carto_val_cols {str list} -- Names of carto columns to aggregate \n",
    " \n",
    "    Returns: GeoDataFrame with the number of poi-s per hexagon\n",
    "  \"\"\"  \n",
    "  \n",
    "  # 0) Helper items\n",
    "  aggs = [[\"sum\"]]*len(carto_val_cols)  # summing dummies -->> resulting in counts\n",
    "  \n",
    "  # 1) Aggregate\n",
    "  output = \\\n",
    "    gpd.sjoin(custom_group_carto_poi(carto_data), territory_hex, how=\"inner\", op=\"intersects\"). \\\n",
    "    groupby(\"hex_id\"). \\\n",
    "    agg(dict(zip(carto_val_cols, aggs)))\n",
    " \n",
    "  # 2) Adjust names\n",
    "  output = fix_agg_data_names(output, prefix=\"carto_\", id_var=\"hex_id\")\n",
    "  output.columns = output.columns.str.replace(\"sum\", \"count\")\n",
    "      \n",
    "  return output\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "1f8bb0ff",
   "metadata": {},
   "source": [
    "###### 3.2.1. Additional general function"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "id": "947a9ea0",
   "metadata": {},
   "outputs": [],
   "source": [
    "def overlay_carto_hex(carto_data, territory_hex, carto_id_var, hex_id_var):\n",
    "  \"\"\"\n",
    "    A function to overlay carto data and hexagonal grid\n",
    "    \n",
    "    Arguments: \n",
    "    carto_data {pd.DataFrame} -- Carto data\n",
    "    territory_hex {pd.DataFrame} -- Hexagonal grid data for the territory\n",
    "    carto_id_var {string} -- Name of carto_data id column\n",
    "    hex_id_var {string} -- Name of territory_hex id column\n",
    " \n",
    "    Returns: GeoDataFrame with carto hexagon-level aggregations\n",
    "  \"\"\"  \n",
    "  \n",
    "  # 1) Overlay hex grid onto carto data -->> NB! the default geometry becomes that of the intersection items\n",
    "  output = gpd.overlay(carto_data, territory_hex, how=\"intersection\")\n",
    "  \n",
    "  # 2) Add extra geometries - from full carto set and from full hex set (gives context for carto value weighing)\n",
    "  output = pd.merge(output, carto_data[[carto_id_var, \"geometry\"]].rename(columns={\"geometry\": \"carto_set_geom\"}), on = [carto_id_var], how=\"left\")\n",
    "  output = pd.merge(output, territory_hex[[hex_id_var, \"geometry\"]].rename(columns={\"geometry\": \"territory_hex_geom\"}), on = [hex_id_var], how=\"left\")\n",
    "  \n",
    "  return output\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "7dd699a8",
   "metadata": {},
   "source": [
    "###### 3.2.2. Additional general function"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "id": "cd04ea3e",
   "metadata": {},
   "outputs": [],
   "source": [
    "def reflect_carto_share(data, val_cols, hex_geom_name = \"territory_hex_geom\", carto_geom_name = \"carto_set_geom\"):\n",
    "  \"\"\"\n",
    "    A function to update carto value columns with their approproiate carto share - falling within each hexagon, prior to follow-up aggregation\n",
    " \n",
    "    Arguments: \n",
    "    data {gpd.DataFrame} -- Data for which to update the carto value columns \n",
    "    val_cols {str list} -- Name of shape file containing hexagon grid (read as gpd)\n",
    " \n",
    "    Returns: GeoDataFrame with carto hexagon-level aggregations\n",
    "  \"\"\"  \n",
    "    \n",
    "#   hex_geom_name = \"territory_hex_geom\"\n",
    "#   carto_geom_name = \"carto_set_geom\"\n",
    "# \"geometry\" is the geom of the intersection from the overlay intersections\n",
    "  \n",
    "  form_hex_share = lambda x: [y.area/z.area for y, z in zip(x[\"geometry\"], x[carto_geom_name])]\n",
    "  \n",
    "  for each in val_cols:\n",
    "    form_val_as_share = lambda x: [y*z for y, z in zip(x[each], x[\"hex_share\"])]\n",
    "    new_col_spex = dict(zip([\"hex_share\"] + [each], [form_hex_share] + [form_val_as_share]))\n",
    "    \n",
    "    data = data.assign(**(new_col_spex))\n",
    "    \n",
    "  return data\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "940046bb",
   "metadata": {},
   "source": [
    "##### 3.2.3. Additional general function"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "id": "2eecb3f1",
   "metadata": {},
   "outputs": [],
   "source": [
    "def fix_agg_data_names(data, prefix=\"carto_\", id_var=\"hex_id\"):\n",
    "  \"\"\"\n",
    "    A function to adjust column names of aggregated data - \n",
    "    concatenates index grouping to column names and adds an indicator prefix (for easier filtering @ later stages)\n",
    "    \n",
    "    Arguments: \n",
    "    data {gpd.DataFrame} -- Post-pivot data to process\n",
    "    prefix {string} -- Suffix to add to column names, short indicator of pivot column setting (i.e. \"dow\" for \"day_of_week\")\n",
    "    id_var {string} -- Name of DataFrame id var\n",
    " \n",
    "    Returns: gpd.DataFrame with adjusted column names.\n",
    "  \"\"\"    \n",
    "    \n",
    "  data.reset_index(level=0, inplace=True)\n",
    "  data.columns = [\"_\".join(x) for x in data.columns.ravel() if x != id_var]  # [id_var] + \n",
    "  data = data.add_prefix(prefix)\n",
    "  data = data.rename(columns={prefix+id_var+\"_\": id_var})\n",
    "  data.head()\n",
    "  \n",
    "  return data\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "0b9f37cd",
   "metadata": {},
   "source": [
    "###### 3.2.4. Additional general function\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "id": "98ba7044",
   "metadata": {},
   "outputs": [],
   "source": [
    "def fix_pivot_data_names(data, suffix, id_var, id_trigger=False):\n",
    "  \"\"\"\n",
    "    A function to adjust column names of pivoted data - \n",
    "    concatenates index grouping to column names and adds an indicator suffix (for easier filtering @ later stages)\n",
    "    \n",
    "    Arguments: \n",
    "    data {gpd.DataFrame} -- Post-pivot data to process\n",
    "    suffix {string} -- Suffix to add to column names, short indicator of pivot column setting (i.e. \"dow\" for \"day_of_week\")\n",
    "    id_var {string} -- Name of DataFrame id var\n",
    "    id_trigger {boolean} -- Flag for id_var treatment in renaming, False drops id_var from renaming functionality (defaults to False)\n",
    " \n",
    "    Returns: performs renaming in place, does not return an object\n",
    "  \"\"\"    \n",
    "  \n",
    "  if id_trigger:\n",
    "    data.columns = [\"_\".join(x) + suffix for x in data.columns.ravel()]\n",
    "  else:\n",
    "    data.columns = [\"_\".join(x) + suffix for x in data.columns.ravel() if x != id_var]\n",
    "    \n",
    "  data.reset_index(level=0, inplace=True)\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a43d77db",
   "metadata": {},
   "source": [
    "###### 3.2.5. Additional set-specific function: carto_poi (callable in wrapper 3.1.6 \"prep_carto_poi\")\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "id": "56f02017",
   "metadata": {},
   "outputs": [],
   "source": [
    "def custom_group_carto_poi(carto_data, id_var=\"cartodb_id\"):\n",
    "  \"\"\"\n",
    "    A function to create a custom grouping based on pb poi trade divisions - to leverage in the estimation of poi count by category. \n",
    "    \n",
    "    Arguments: \n",
    "    carto_data {pd.GeoDataFrame} -- Carto poi data to process\n",
    "    id_var {string} -- Name of id var (key id) to use in indexing and df joining\n",
    " \n",
    "    Returns: GeoDataFrame on carto PB POI - with dummies for the different custom poi groups defined.\n",
    "  \"\"\"   \n",
    "  \n",
    "  # 1) Create subset with trade divisions only\n",
    "  data = carto_data.copy()[[id_var, \"trade_division\"]]\n",
    " \n",
    "  # 2) Define new column parameters - conditions and names, \n",
    "  conditions = [\n",
    "      (data[\"trade_division\"].isin([\"DIVISION C. - CONSTRUCTION\"])),\n",
    "      (data[\"trade_division\"].isin([\"DIVISION D. - MANUFACTURING\"])),\n",
    "      (data[\"trade_division\"].isin([\"DIVISION B. -MINING\"])),\n",
    "\n",
    "      (data[\"trade_division\"].isin([\"DIVISION A. - AGRICULTURE, FORESTRY, AND FISHING\"])),\n",
    "      (data[\"trade_division\"].isin([\"DIVISION K. - NONCLASSIFIABLE ESTABLISHMENTS\"])),\n",
    "      (data[\"trade_division\"].isin([\"DIVISION N. - LABEL FEATURES\"])),\n",
    "      (data[\"trade_division\"].isin([\"DIVISION E. - TRANSPORTATION AND PUBLIC UTILITIES\"])),\n",
    "      (data[\"trade_division\"].isin([\"DIVISION I. - SERVICES\", \"DIVISION F. - WHOLESALE TRADE\", \"DIVISION G. - RETAIL TRADE\", \"DIVISION M. - SPORTS\",\n",
    "                                    \"DIVISION L. - TOURISM\", \"DIVISION H. - FINANCE, INSURANCE, AND REAL ESTATE\", \"DIVISION J. - PUBLIC ADMINISTRATION\"]))\n",
    "       ]\n",
    " \n",
    "  values = [\"poi_\" + x for x in [\"construction\", \"manufacturing\",\"mining\", \"life_sciences\", \"nonclassifiable\", \"terrain_features\", \"utility_transportation\", \"trade_services\"] ]\n",
    " \n",
    "  # 3) Generate new columns\n",
    "  data = data. \\\n",
    "    assign(poi_sic_grouping=np.select(conditions, values)). \\\n",
    "    set_index(id_var)    \n",
    " \n",
    "  business = pd.get_dummies(data[\"poi_sic_grouping\"]). \\\n",
    "    reset_index()\n",
    "  \n",
    "  divisions = pd.get_dummies(data[\"trade_division\"])\n",
    "  divisions.columns = [re.sub(r'^.*?-', \"\", x.lower()) for x in divisions.columns]\n",
    "  divisions.columns = [re.sub(\",\", \"\", x) for x in divisions.columns]\n",
    "  divisions.columns = [\"trade_div\" + re.sub(\" \", \"_\", x) for x in divisions.columns]\n",
    "  divisions = divisions.reset_index()\n",
    "  \n",
    "  # 4) Add to original carto data\n",
    "  carto_data = carto_data.merge(business, on=id_var, how=\"left\").merge(divisions, on=id_var, how=\"left\")\n",
    "  \n",
    "  return carto_data\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f7718f13",
   "metadata": {},
   "source": [
    "###### 3.2.6. Additional set-specific function: carto_unacast (callable in wrapper 3.1.5 \"prep_carto_unacast\")\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "id": "7b8dac49",
   "metadata": {},
   "outputs": [],
   "source": [
    "def init_prep_carto_unacast(carto_data, id_var=\"seen_in\"):\n",
    "  \"\"\"\n",
    "    A function to perform initial prep over the raw carto_unacast_data - spread to wide format.\n",
    "    \n",
    "    Arguments: \n",
    "    carto_unacast {gpd.DataFrame} -- Spread Carto Unacast data to be processed\n",
    "    id_var {string} -- Name of dataset key id var/quadid (indicating unique rows, defaults to \"seen_in\")\n",
    " \n",
    "    Returns: GeoDataFrame with wider data.\n",
    "  \"\"\"  \n",
    "    \n",
    "  # 1) Spread carto set categorical columns \n",
    "  carto_unacast_wip = spread_carto_unacast(carto_data)\n",
    "  \n",
    "  # 2) Transform original proportions to counts\n",
    "  carto_unacast_wip = reflect_unacast_proportions(carto_unacast_wip)\n",
    "  \n",
    "  # 3) Aggregate carto set to geom level\n",
    "  output = agg_carto_unacast_to_geomid(carto_unacast_wip, id_var=id_var)\n",
    "  \n",
    "  return output\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "3f92e096",
   "metadata": {},
   "source": [
    "**3.2.7. Additional set-specific function: carto_unacast (callable in addon 3.2.6 \"init_prep_carto_unacast\")**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "id": "4ddef56b",
   "metadata": {},
   "outputs": [],
   "source": [
    "def spread_carto_unacast(carto_unacast):\n",
    "  \"\"\"\n",
    "    A function to spread categorical carto_unacast columns\n",
    "    \n",
    "    Arguments: \n",
    "    carto_unacast {gpd.DataFrame} -- Carto Unacast data to be processed\n",
    " \n",
    "    Returns: GeoDataFrame with wider data.\n",
    "  \"\"\"  \n",
    "  \n",
    "  # Unfold return_rate column\n",
    "  try:\n",
    "    carto_unacast_wip = carto_unacast. \\\n",
    "      assign(\n",
    "        rr_unfolded = lambda x: [parse_return_rate(string=y) for y in x[\"return_rate\"]],\n",
    "        less_than_5_flag = carto_unacast[\"less_than_5_flag\"].astype(int)\n",
    "      )\n",
    "  except:\n",
    "    return carto_unacast\n",
    " \n",
    "  # Spread into columns\n",
    "  carto_unacast_wip = pd.concat([carto_unacast_wip.drop([\"rr_unfolded\", \"return_rate\"], axis=1), carto_unacast_wip[\"rr_unfolded\"].apply(pd.Series)], axis=1)\n",
    "  carto_unacast_wip.columns = [\"return_rate_\" + col if col in [\"rare\", \"weekly\", \"multi_weekly\", \"monthly\"] else col for col in carto_unacast_wip.columns]\n",
    " \n",
    "   # Adjust type \n",
    "  fix_cols = [col for col in carto_unacast_wip if col.startswith(\"return_rate_\")]\n",
    "  carto_unacast_wip[fix_cols] = carto_unacast_wip[fix_cols].apply(pd.to_numeric, errors='coerce', axis=1)\n",
    "  \n",
    "  return carto_unacast_wip\n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "8c38fffb",
   "metadata": {},
   "source": [
    "**3.2.8. Additional set-specific function: carto_unacast (callable in addon 3.2.7 \"spread_carto_unacast\")**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "id": "b810e16a",
   "metadata": {},
   "outputs": [],
   "source": [
    "def parse_return_rate(string):\n",
    "  \"\"\"\n",
    "    A function to parse return_rate column of the carto unacast data (originally a string).\n",
    "    \n",
    "    Arguments: \n",
    "    string {string} -- String to parse\n",
    " \n",
    "    Returns: String - parsed as a dictionary of key-value pairs to be split into DataFrame columns as the next prep step.\n",
    "  \"\"\"  \n",
    "  \n",
    "  output_string = string.split(\"), \")\n",
    "  output_string = [re.sub(r'[]()[{}]', '', x) for x in output_string]\n",
    "  output_string = [re.sub(r' ', '', x) for x in output_string]\n",
    "  output_string = [re.sub(r'frequency:', '', x) for x in output_string]\n",
    "  output_string = [re.sub(r'ratio:', '', x) for x in output_string]\n",
    "  output_string = [re.sub(r'-', '_', x) for x in output_string]\n",
    "  output_string = [re.sub(r',', '=', x) for x in output_string]\n",
    "  \n",
    "  sep = \";\"\n",
    "  output_string = sep.join(output_string)\n",
    "  \n",
    "  output_string = dict(item.split(\"=\") for item in output_string.split(\";\"))\n",
    "    \n",
    "  return output_string"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "76969476",
   "metadata": {},
   "source": [
    "**3.2.9. Additional set-specific function: carto_unacast (callable in addon 3.2.6 \"init_prep_carto_unacast\")**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "0529c169",
   "metadata": {},
   "outputs": [],
   "source": [
    "def reflect_unacast_proportions(carto_unacast_wip):\n",
    "  \"\"\"\n",
    "    A function to reflect proportions as counts in unacast data (relative to \"staying\" count) - prior to aggregation.\n",
    "    \n",
    "    Arguments: \n",
    "    carto_unacast_wip {gpd.DataFrame} -- Spread Carto Unacast data to be processed\n",
    " \n",
    "    Returns: GeoDataFrame with original proportion columns transformed as counts.\n",
    "  \"\"\"    \n",
    "  \n",
    "  fix_cols =  [col for col in carto_unacast_wip if col.startswith(\"proportion_\")]\n",
    "  \n",
    "  for col in fix_cols:\n",
    "    carto_unacast_wip[col] = carto_unacast_wip[\"staying\"] * carto_unacast_wip[col]\n",
    "    \n",
    "  return carto_unacast_wip\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f016e2c0",
   "metadata": {},
   "source": [
    "**3.2.10. Additional set-specific function: carto_unacast (callable in addon 3.2.10 \"init_prep_carto_unacast\")**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "id": "19fdd63d",
   "metadata": {},
   "outputs": [],
   "source": [
    "def agg_carto_unacast_to_geomid(carto_unacast_wip, id_var=\"seen_in\"):\n",
    "  \"\"\"\n",
    "    A function to aggregate carto_unacast data to quadid level (same as geometry level).\n",
    "    \n",
    "    Arguments: \n",
    "    carto_unacast {gpd.DataFrame} -- Spread Carto Unacast data to be processed\n",
    "    id_var {string} -- Name of dataset key id var/quadid (indicating unique rows, not a geometry)\n",
    " \n",
    "    Returns: GeoDataFrame with wider data.\n",
    "  \"\"\"  \n",
    "  \n",
    "  # 1) Helper items\n",
    "  geom_mapping = carto_unacast_wip[[\"geometry\", id_var]].drop_duplicates()\n",
    "  val_cols = ['staying','proportion_residents','proportion_workers','proportion_others','proportion_same_state','proportion_other_state','proportion_unknown','return_rate_rare','return_rate_multi_weekly','return_rate_weekly','return_rate_monthly']\n",
    "  rr_cols = [col for col in val_cols if col.startswith(\"return_rate_\")]\n",
    "  \n",
    "  # 2) Derive wide set\n",
    "  \n",
    "  # 2.1) aggregate return_rates to seen_in id -> independent of any breaks\n",
    "  # average (if spread by category the same result is observed across different categories)\n",
    "  out_rr_mean = carto_unacast_wip.groupby(id_var).agg(dict(zip(rr_cols, [\"mean\"]*len(rr_cols)))).reset_index()\n",
    "  \n",
    "  # 2.2) aggregate day_of_week\n",
    "  day_of_week = \\\n",
    "    wide_agg_carto_unacast(\n",
    "      carto_unacast_wip, val_cols, id_var, dimension_var=\"day_of_week\", suffix_dim=\"_dow\", \n",
    "      suffix_specs=[\"ALL\", \"Friday\", \"Monday\", \"Saturday\", \"Sunday\", \"Thursday\", \"Tuesday\", \"Wednesday\"])\n",
    "  \n",
    "  # 2.3) aggregate time_of_day\n",
    "  part_of_day = \\\n",
    "    wide_agg_carto_unacast(\n",
    "      carto_unacast_wip, val_cols, id_var, dimension_var=\"part_of_day\", suffix_dim=\"_pod\", \n",
    "      suffix_specs=['ALL', 'Evening', 'Morning', 'Afternoon', 'Night'])  \n",
    "  \n",
    "  # 2.4) aggregate months\n",
    "  month = \\\n",
    "    wide_agg_carto_unacast(\n",
    "      carto_unacast_wip, val_cols, id_var, dimension_var=\"month\", suffix_dim=\"_mo\", \n",
    "      suffix_specs=['September', 'May', 'August', 'June', 'July', 'October'])  \n",
    "  \n",
    "  # 2.4) pivot on combination of day_of_week, time_of_day & month --> NB! results in 240 cols per value var \n",
    "  #   time_combos = carto_unacast_wip. \\\n",
    "  #     assign(part_of_time = lambda x: [y + \"_\" + z + \"_\" + q for y, z, q in zip(x[\"month\"], x[\"day_of_week\"], x[\"part_of_day\"])]). \\\n",
    "  #     pivot(index=id_var, columns=\"part_of_time\", values=val_cols)\n",
    "  #   fix_pivot_data_names(data=time_combos, suffix=\"_combo\", id_var=id_var, id_trigger=True)  \n",
    "  \n",
    "  # 2.5) Combine into 1\n",
    "  output = geom_mapping. \\\n",
    "    merge(month, on=id_var, how=\"inner\"). \\\n",
    "    merge(day_of_week, on=id_var, how=\"inner\"). \\\n",
    "    merge(part_of_day, on=id_var, how=\"inner\"). \\\n",
    "    merge(out_rr_mean, on=id_var, how=\"inner\") #. \\\n",
    "    # merge(time_combos, on=id_var, how=\"inner\")\n",
    "  \n",
    "  return output\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "31b59f0c",
   "metadata": {},
   "source": [
    "**3.2.11. Additional set-specific function: carto_unacast (callable in addon 3.2.10 \"agg_carto_unacast_to_geomid\")**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "id": "6c67613d",
   "metadata": {},
   "outputs": [],
   "source": [
    "def wide_agg_carto_unacast(carto_unacast_wip, val_cols, id_var, dimension_var, suffix_dim, suffix_specs):\n",
    "  \"\"\"\n",
    "    A function to aggregate unacast data to unique id level - prerequisite to hex id aggregation (accounting for proportions and sub categories of temporal columns).\n",
    "    \n",
    "    Arguments: \n",
    "    carto_unacast_wip {gpd.DataFrame} -- Carto Unacast data post initial spread of return rate columns \n",
    "    val_cols {str list} -- Names of value columns to be aggregated\n",
    "    id_var {string} -- Name of dataset key id var/quadid (indicating unique rows, defaults to \"seen_in\")\n",
    "    dimension_var {string} -- Name of temporal column to split the categories of (i.e. \"day_of_week\", \"month\", \"time_of_day\")\n",
    "    suffix_dim {string} -- Name of suffix corresponding to dimension_var (marks source data in new cols)\n",
    "    suffix_specs {str list} -- Names of dimension_var categories (pssible values to be spread into new variables) \n",
    " \n",
    "    Returns: Aggregated GeoDataFrame with additional variables derived from categories of temporal variables.\n",
    "  \"\"\"  \n",
    "  \n",
    "  suffixes = [\"_\" + each + suffix_dim for each in suffix_specs]\n",
    "  rr_cols = [col for col in val_cols if col.startswith(\"return_rate_\")]\n",
    "  val_cols_pure = list(set(val_cols) - set(rr_cols))\n",
    "  \n",
    "  # Aggregations:\n",
    "  \n",
    "  # 1) reflect temporal categories by pivoting with sum of value columns\n",
    "  output = carto_unacast_wip.pivot_table(values=val_cols_pure, columns=dimension_var, index=id_var, aggfunc=dict(zip(val_cols_pure, [\"sum\"]*len(val_cols_pure))))\n",
    "  fix_pivot_data_names(data=output, suffix=suffix_dim, id_var=id_var)\n",
    "  \n",
    "  # 1.1) keep sum of the [avg number of people] per subcategory\n",
    "  out_ppl_sum = output.copy().set_index(id_var)\n",
    "  out_ppl_sum = out_ppl_sum.loc[:, out_ppl_sum.columns.str.startswith(\"proportion_\")].reset_index()\n",
    "  out_ppl_sum.columns = out_ppl_sum.columns.str.replace(r\"proportion_\", \"sum_\")\n",
    "  \n",
    "  # 1.2) add columns reverted back to proportions \n",
    "  for suffix in suffixes:\n",
    "  \n",
    "    columns_with_suffix = [col for col in output.columns if suffix in col]\n",
    "    denominator=[col for col in columns_with_suffix if col.startswith(\"staying_\")]\n",
    "    numerators=list(set(columns_with_suffix) - set(denominator))\n",
    " \n",
    "    for numerator in numerators:\n",
    "      output[numerator] = output[numerator]/output[denominator[0]]\n",
    "      \n",
    "  # 2) build combined output file\n",
    "  output = output. \\\n",
    "    merge(out_ppl_sum, on=id_var, how=\"left\")\n",
    "    \n",
    "  return output\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "2578c016",
   "metadata": {},
   "source": [
    "#### 4. Functions on combining the TAA components"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "id": "1e90c719",
   "metadata": {},
   "outputs": [],
   "source": [
    "def merge_taa_components_from_wppop(pop_stats, poi_stats, carto_cexpend_stats, carto_sociodemo_stats, carto_unacast_stats, carto_poi_stats,\n",
    "                                    id_var=\"hex_id\", export_spec=None):\n",
    "  \"\"\"\n",
    "    A function to combine trade-area-analysis component datasets.\n",
    "    \n",
    "    Arguments: \n",
    "    pop_stats {GeoDataFrame} -- WP population data - aggregated to hexagon level\n",
    "    poi_stats {GeoDataFrame} -- OSM poi data - aggregated to hexagon level\n",
    "    carto_cexpend_stats {GeoDataFrame} -- Carto customer spend data - aggregated to hexagon level\n",
    "    carto_sociodemo_stats {GeoDataFrame} -- Carto socio-demographic data - aggregated to hexagon level\n",
    "    carto_unacast_stats {GeoDataFrame} -- Carto Unacast data - aggregated to hexagon level\n",
    "    carto_poi_stats {GeoDataFrame} -- Carto PB poi data - aggregated to hexagon level\n",
    "    id_var {string} -- Name of id variable to merge on\n",
    "    export_spec {string} -- Name of file to export to blob (path and name)\n",
    " \n",
    "    Returns: GeoDataFrame with wider data.\n",
    "  \"\"\"   \n",
    "  \n",
    "  # need a dataset with geometry and hex_id-> to form a gpd\n",
    "  # currently using pop_stats\n",
    "  \n",
    "  output = \\\n",
    "    pop_stats. \\\n",
    "    merge(poi_stats, on=id_var, how='left'). \\\n",
    "    merge(carto_cexpend_stats, on=id_var, how='left'). \\\n",
    "    merge(carto_sociodemo_stats, on=id_var, how='left'). \\\n",
    "    merge(carto_unacast_stats, on=id_var, how='left'). \\\n",
    "    merge(carto_poi_stats, on=id_var, how='left')\n",
    "  \n",
    "  if export_spec is not None:\n",
    "    output.to_file(export_spec, driver=\"GeoJSON\")\n",
    "  \n",
    "  return(output)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "id": "c3b2363e",
   "metadata": {},
   "outputs": [],
   "source": [
    "def merge_taa_components_from_osm_carto(poi_stats, carto_cexpend_stats, carto_sociodemo_stats, carto_unacast_stats, carto_poi_stats,\n",
    "                                    id_var=\"hex_id\", export_spec=\"/dbfs/mnt/AA/ba008/assets/trade_area_analysis/taa_combo_set.json\"):\n",
    "  \"\"\"\n",
    "    A function to combine trade-area-analysis component datasets.\n",
    "    \n",
    "    Arguments: \n",
    "    poi_stats {GeoDataFrame} -- OSM poi data - aggregated to hexagon level\n",
    "    carto_cexpend_stats {GeoDataFrame} -- Carto customer spend data - aggregated to hexagon level\n",
    "    carto_sociodemo_stats {GeoDataFrame} -- Carto socio-demographic data - aggregated to hexagon level\n",
    "    carto_unacast_stats {GeoDataFrame} -- Carto Unacast data - aggregated to hexagon level\n",
    "    carto_poi_stats {GeoDataFrame} -- Carto PB poi data - aggregated to hexagon level\n",
    "    id_var {string} -- Name of id variable to merge on\n",
    "    export_spec {string} -- Name of file to export to blob (path and name)\n",
    " \n",
    "    Returns: GeoDataFrame with wider data.\n",
    "  \"\"\"   \n",
    "  \n",
    "  # need a dataset with geometry and hex_id-> to form a gpd\n",
    "  # currently using pop_stats\n",
    "  output = \\\n",
    "    poi_stats. \\\n",
    "    merge(carto_cexpend_stats, on=id_var, how='left'). \\\n",
    "    merge(carto_sociodemo_stats, on=id_var, how='left'). \\\n",
    "    merge(carto_unacast_stats, on=id_var, how='left'). \\\n",
    "    merge(carto_poi_stats, on=id_var, how='left')\n",
    "  \n",
    "  output = gpd.GeoDataFrame(output)\n",
    "  output.to_file(export_spec, driver=\"GeoJSON\")\n",
    "  \n",
    "  return(output)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "id": "d958dde5",
   "metadata": {},
   "outputs": [],
   "source": [
    "def merge_taa_components_from_hex(territory_hex, poi_stats, carto_cexpend_stats, carto_sociodemo_stats, carto_unacast_stats, carto_poi_stats,\n",
    "                                  id_var=\"hex_id\", export_spec=\"/dbfs/mnt/AA/ba008/assets/trade_area_analysis/taa_combo_set.json\"):\n",
    "  \"\"\"\n",
    "    A function to combine trade-area-analysis component datasets.\n",
    "    \n",
    "    Arguments: \n",
    "    territory_hex {GeoDataFrame} -- Hexagonal grid for the territory\n",
    "    poi_stats {GeoDataFrame} -- OSM poi data - aggregated to hexagon level\n",
    "    carto_cexpend_stats {GeoDataFrame} -- Carto customer spend data - aggregated to hexagon level\n",
    "    carto_sociodemo_stats {GeoDataFrame} -- Carto socio-demographic data - aggregated to hexagon level\n",
    "    carto_unacast_stats {GeoDataFrame} -- Carto Unacast data - aggregated to hexagon level\n",
    "    carto_poi_stats {GeoDataFrame} -- Carto PB poi data - aggregated to hexagon level\n",
    "    id_var {string} -- Name of id variable to merge on\n",
    "    export_spec {string} -- Name of file to export to blob (path and name)\n",
    " \n",
    "    Returns: GeoDataFrame with wider data.\n",
    "  \"\"\"   \n",
    "  \n",
    "  # need a dataset with geometry and hex_id-> to form a gpd\n",
    "  # currently using territory_hex\n",
    "  \n",
    "  output = \\\n",
    "    territory_hex. \\\n",
    "    merge(poi_stats, on=id_var, how='left'). \\\n",
    "    merge(carto_cexpend_stats, on=id_var, how='left'). \\\n",
    "    merge(carto_sociodemo_stats, on=id_var, how='left'). \\\n",
    "    merge(carto_unacast_stats, on=id_var, how='left'). \\\n",
    "    merge(carto_poi_stats, on=id_var, how='left')\n",
    " \n",
    "  output.to_file(export_spec, driver=\"GeoJSON\")\n",
    "  \n",
    "  return(output)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "9d1214c8",
   "metadata": {},
   "source": [
    "#### Data visualization functions"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "id": "63380705",
   "metadata": {},
   "outputs": [],
   "source": [
    "def plot_simple_hex(combo_set, show_var):\n",
    "  \"\"\"\n",
    "    A function to display data per hexagon - on regional and city level.\n",
    "    \n",
    "    Arguments: \n",
    "    combo_set {gpd.DataFrame} -- Complete data, on regional level\n",
    "    show_var {string} -- Name of variable to display\n",
    " \n",
    "    Returns: GeoDataFrame with wider data.\n",
    "  \"\"\"    \n",
    "  \n",
    "  combo_set.plot(column=show_var, figsize=(10, 10))  \n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "32b9956b",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "PREMIER",
   "language": "python",
   "name": "premier"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.9.4"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}