02_2 visualize home and work race diversity.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "6ad093d6-cc32-4e5e-8b5f-cbe4c7da6dd4",
   "metadata": {},
   "source": [
    "# Generate home and work based racial diversity (CBG level)\n",
    "output: CA_CBG_dominantRace.pkl or LA_CBG_dominantRace.pkl\n",
    "\n",
    "These are census based racial diversity data. No need to run this notebook again."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "3a086177-1e7c-4065-8a5f-0bff6c9cd728",
   "metadata": {},
   "outputs": [],
   "source": [
    "%reload_ext sql\n",
    "%config SqlMagic.autocommit=False\n",
    "%config SqlMagic.autolimit=0\n",
    "%config SqlMagic.autopandas=True\n",
    "%config SqlMagic.displaylimit=200\n",
    "%sql trino://localhost:9090/cuebiq/\n",
    "\n",
    "import pandas as pd\n",
    "import yaml\n",
    "import numpy as np\n",
    "import matplotlib.pyplot as plt\n",
    "import matplotlib.cm as cm\n",
    "import os\n",
    "from pyhive import trino\n",
    "import pydeck as pdk\n",
    "from typing import List\n",
    "import json\n",
    "import copy\n",
    "import itertools\n",
    "# import geohash\n",
    "from shapely.geometry import shape\n",
    "from shapely.geometry import Polygon\n",
    "from shapely.geometry import box\n",
    "import geopandas as geopd\n",
    "from pyquadkey2 import quadkey\n",
    "from pyquadkey2.quadkey import TileAnchor, QuadKey\n",
    "from h3 import h3\n",
    "import seaborn as sns\n",
    "import folium\n",
    "from keplergl import KeplerGl\n",
    "from datetime import datetime, timedelta\n",
    "import math\n",
    "\n",
    "os.environ['MAPBOX_API_KEY'] = \"INSERT YOUR MAPBOX TOKEN HERE\"\n",
    "pd.set_option('display.max_colwidth', 0)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "f4138193-66fc-4fe0-8955-fcf6c243eddd",
   "metadata": {},
   "outputs": [],
   "source": [
    "# SQL engine\n",
    "from trino.dbapi import connect \n",
    "from sqlalchemy import create_engine\n",
    "\n",
    "class TrinoEngine():\n",
    "    def __init__(self):\n",
    "        conn = connect(\n",
    "            host=\"localhost\",\n",
    "            port=9090,\n",
    "            catalog=\"cuebiq\"\n",
    "        )\n",
    "        self.cur = conn.cursor()\n",
    "        self.engine = create_engine(\"trino://localhost:9090/cuebiq/\")\n",
    "    \n",
    "    def execute_statement(self, query:str) -> list:\n",
    "        \"\"\"\n",
    "        Create and drop statements.\n",
    "        \"\"\"\n",
    "        self.cur.execute(query)\n",
    "        return self.cur.fetchall()\n",
    "    \n",
    "    def read_sql(self, query:str) -> pd.DataFrame: \n",
    "        \"\"\"\n",
    "        Select and insert into operations.\n",
    "        \"\"\"\n",
    "        return pd.read_sql(query, self.engine)\n",
    "\n",
    "sql_engine = TrinoEngine()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "e6330eb3-9cef-433b-b51e-cbbd485a1a2c",
   "metadata": {},
   "outputs": [],
   "source": [
    "geography_table = \"cuebiq.paas_cda_pe_v3.geography_registry\"\n",
    "# Census Block Groups typically have a population between 600 to 3000 people, this makes the data privacy safe.\n",
    "hw_table = \"cuebiq.paas_cda_pe_v3.device_recurring_area\"\n",
    "date = 20220301\n",
    "date_formated = datetime.strptime(str(date), \"%Y%m%d\").strftime(\"%Y-%m-%d\")\n",
    "\n",
    "census_data_table = \"cuebiq.paas_public_data.census_data\"\n",
    "census_taxonomy_table = \"cuebiq.paas_public_data.census_taxonomy\"\n",
    "\n",
    "geog_clause = 'US.CA.037%'  #'US.CA.%'   'US.CA.037%'\n",
    "# sql_engine.read_sql(f\"desc {census_data_table}\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "b7e1fb46-bc31-419d-8e18-1a05c14cea66",
   "metadata": {},
   "outputs": [],
   "source": [
    "%%time\n",
    "#select HOME locations\n",
    "df_home_loc = sql_engine.read_sql(\n",
    "    f\"\"\"\n",
    "    select *\n",
    "    from {hw_table}\n",
    "    where\n",
    "        provider_id = '190199'\n",
    "        and country_code = 'US'\n",
    "        and snapshot_event_date = {date} \n",
    "        and tag_type_code = 'HOME'\n",
    "        and block_group_id like '{geog_clause}'\n",
    "    \"\"\"\n",
    ")\n",
    "# df_home_loc = df_home_loc[~df_home_loc['block_group_id'].isin(['US.CA.037.599100.2','US.CA.037.599000.2','US.CA.037.599000.1','US.CA.037.599000.4','US.CA.037.599000.3','US.CA.037.599100.1'])]\n",
    "print(df_home_loc.shape[0])\n",
    "df_home_loc.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "1bd06382-c701-4fb9-ad04-85a827e54e73",
   "metadata": {},
   "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>segment_group_id</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>YEARLY_MEDIAN_HOUSEHOLD_INCOME</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>YEARLY_MEDIAN_FAMILY_INCOME_BY_FAMILY_SIZE</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>AGGREGATE_HOUSEHOLD_INCOME_BY_QUINTILE</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>MEDIAN_AGE_BY_SEX_ETHNICITY</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>HOUSEHOLD_INCOME_BY_VALUE</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>ETHNICITY</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>TOTAL_POPULATION</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>HISPANIC_OR_LATINO_ETHNICITY</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>SEX_BY_AGE</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>MEDIAN_AGE_BY_SEX</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>YEARLY_MEDIAN_HOUSEHOLD_INCOME_BY_ETHNICITY</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                               segment_group_id\n",
       "0   YEARLY_MEDIAN_HOUSEHOLD_INCOME             \n",
       "1   YEARLY_MEDIAN_FAMILY_INCOME_BY_FAMILY_SIZE \n",
       "2   AGGREGATE_HOUSEHOLD_INCOME_BY_QUINTILE     \n",
       "3   MEDIAN_AGE_BY_SEX_ETHNICITY                \n",
       "4   HOUSEHOLD_INCOME_BY_VALUE                  \n",
       "5   ETHNICITY                                  \n",
       "6   TOTAL_POPULATION                           \n",
       "7   HISPANIC_OR_LATINO_ETHNICITY               \n",
       "8   SEX_BY_AGE                                 \n",
       "9   MEDIAN_AGE_BY_SEX                          \n",
       "10  YEARLY_MEDIAN_HOUSEHOLD_INCOME_BY_ETHNICITY"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# sql_engine.read_sql(\n",
    "#     f\"\"\"\n",
    "#     select distinct \n",
    "#         segment_group_id\n",
    "#     from {census_data_table}\n",
    "#     \"\"\"\n",
    "# )"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "6d5d681a-fb3f-41db-8d87-ff6cba620d01",
   "metadata": {},
   "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>census_variable_code</th>\n",
       "      <th>segment_group_id</th>\n",
       "      <th>census_variable_description</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>B03002_001E</td>\n",
       "      <td>HISPANIC_OR_LATINO_ETHNICITY</td>\n",
       "      <td>Estimate!!Total</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>B03002_002E</td>\n",
       "      <td>HISPANIC_OR_LATINO_ETHNICITY</td>\n",
       "      <td>Estimate!!Total!!Not Hispanic or Latino</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>B03002_003E</td>\n",
       "      <td>HISPANIC_OR_LATINO_ETHNICITY</td>\n",
       "      <td>Estimate!!Total!!Not Hispanic or Latino!!White alone</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>B03002_004E</td>\n",
       "      <td>HISPANIC_OR_LATINO_ETHNICITY</td>\n",
       "      <td>Estimate!!Total!!Not Hispanic or Latino!!Black or African American alone</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>B03002_005E</td>\n",
       "      <td>HISPANIC_OR_LATINO_ETHNICITY</td>\n",
       "      <td>Estimate!!Total!!Not Hispanic or Latino!!American Indian and Alaska Native alone</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>B03002_006E</td>\n",
       "      <td>HISPANIC_OR_LATINO_ETHNICITY</td>\n",
       "      <td>Estimate!!Total!!Not Hispanic or Latino!!Asian alone</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>B03002_007E</td>\n",
       "      <td>HISPANIC_OR_LATINO_ETHNICITY</td>\n",
       "      <td>Estimate!!Total!!Not Hispanic or Latino!!Native Hawaiian and Other Pacific Islander alone</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>B03002_008E</td>\n",
       "      <td>HISPANIC_OR_LATINO_ETHNICITY</td>\n",
       "      <td>Estimate!!Total!!Not Hispanic or Latino!!Some other race alone</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>B03002_009E</td>\n",
       "      <td>HISPANIC_OR_LATINO_ETHNICITY</td>\n",
       "      <td>Estimate!!Total!!Not Hispanic or Latino!!Two or more races</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>B03002_010E</td>\n",
       "      <td>HISPANIC_OR_LATINO_ETHNICITY</td>\n",
       "      <td>Estimate!!Total!!Not Hispanic or Latino!!Two or more races!!Two races including Some other race</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>B03002_011E</td>\n",
       "      <td>HISPANIC_OR_LATINO_ETHNICITY</td>\n",
       "      <td>Estimate!!Total!!Not Hispanic or Latino!!Two or more races!!Two races excluding Some other race, and three or more races</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>B03002_012E</td>\n",
       "      <td>HISPANIC_OR_LATINO_ETHNICITY</td>\n",
       "      <td>Estimate!!Total!!Hispanic or Latino</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>B03002_013E</td>\n",
       "      <td>HISPANIC_OR_LATINO_ETHNICITY</td>\n",
       "      <td>Estimate!!Total!!Hispanic or Latino!!White alone</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>B03002_014E</td>\n",
       "      <td>HISPANIC_OR_LATINO_ETHNICITY</td>\n",
       "      <td>Estimate!!Total!!Hispanic or Latino!!Black or African American alone</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>B03002_015E</td>\n",
       "      <td>HISPANIC_OR_LATINO_ETHNICITY</td>\n",
       "      <td>Estimate!!Total!!Hispanic or Latino!!American Indian and Alaska Native alone</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>B03002_016E</td>\n",
       "      <td>HISPANIC_OR_LATINO_ETHNICITY</td>\n",
       "      <td>Estimate!!Total!!Hispanic or Latino!!Asian alone</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>B03002_017E</td>\n",
       "      <td>HISPANIC_OR_LATINO_ETHNICITY</td>\n",
       "      <td>Estimate!!Total!!Hispanic or Latino!!Native Hawaiian and Other Pacific Islander alone</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>B03002_018E</td>\n",
       "      <td>HISPANIC_OR_LATINO_ETHNICITY</td>\n",
       "      <td>Estimate!!Total!!Hispanic or Latino!!Some other race alone</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>B03002_019E</td>\n",
       "      <td>HISPANIC_OR_LATINO_ETHNICITY</td>\n",
       "      <td>Estimate!!Total!!Hispanic or Latino!!Two or more races</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>B03002_020E</td>\n",
       "      <td>HISPANIC_OR_LATINO_ETHNICITY</td>\n",
       "      <td>Estimate!!Total!!Hispanic or Latino!!Two or more races!!Two races including Some other race</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>B03002_021E</td>\n",
       "      <td>HISPANIC_OR_LATINO_ETHNICITY</td>\n",
       "      <td>Estimate!!Total!!Hispanic or Latino!!Two or more races!!Two races excluding Some other race, and three or more races</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   census_variable_code              segment_group_id  \\\n",
       "0   B03002_001E          HISPANIC_OR_LATINO_ETHNICITY   \n",
       "1   B03002_002E          HISPANIC_OR_LATINO_ETHNICITY   \n",
       "2   B03002_003E          HISPANIC_OR_LATINO_ETHNICITY   \n",
       "3   B03002_004E          HISPANIC_OR_LATINO_ETHNICITY   \n",
       "4   B03002_005E          HISPANIC_OR_LATINO_ETHNICITY   \n",
       "5   B03002_006E          HISPANIC_OR_LATINO_ETHNICITY   \n",
       "6   B03002_007E          HISPANIC_OR_LATINO_ETHNICITY   \n",
       "7   B03002_008E          HISPANIC_OR_LATINO_ETHNICITY   \n",
       "8   B03002_009E          HISPANIC_OR_LATINO_ETHNICITY   \n",
       "9   B03002_010E          HISPANIC_OR_LATINO_ETHNICITY   \n",
       "10  B03002_011E          HISPANIC_OR_LATINO_ETHNICITY   \n",
       "11  B03002_012E          HISPANIC_OR_LATINO_ETHNICITY   \n",
       "12  B03002_013E          HISPANIC_OR_LATINO_ETHNICITY   \n",
       "13  B03002_014E          HISPANIC_OR_LATINO_ETHNICITY   \n",
       "14  B03002_015E          HISPANIC_OR_LATINO_ETHNICITY   \n",
       "15  B03002_016E          HISPANIC_OR_LATINO_ETHNICITY   \n",
       "16  B03002_017E          HISPANIC_OR_LATINO_ETHNICITY   \n",
       "17  B03002_018E          HISPANIC_OR_LATINO_ETHNICITY   \n",
       "18  B03002_019E          HISPANIC_OR_LATINO_ETHNICITY   \n",
       "19  B03002_020E          HISPANIC_OR_LATINO_ETHNICITY   \n",
       "20  B03002_021E          HISPANIC_OR_LATINO_ETHNICITY   \n",
       "\n",
       "                                                                                                 census_variable_description  \n",
       "0   Estimate!!Total                                                                                                           \n",
       "1   Estimate!!Total!!Not Hispanic or Latino                                                                                   \n",
       "2   Estimate!!Total!!Not Hispanic or Latino!!White alone                                                                      \n",
       "3   Estimate!!Total!!Not Hispanic or Latino!!Black or African American alone                                                  \n",
       "4   Estimate!!Total!!Not Hispanic or Latino!!American Indian and Alaska Native alone                                          \n",
       "5   Estimate!!Total!!Not Hispanic or Latino!!Asian alone                                                                      \n",
       "6   Estimate!!Total!!Not Hispanic or Latino!!Native Hawaiian and Other Pacific Islander alone                                 \n",
       "7   Estimate!!Total!!Not Hispanic or Latino!!Some other race alone                                                            \n",
       "8   Estimate!!Total!!Not Hispanic or Latino!!Two or more races                                                                \n",
       "9   Estimate!!Total!!Not Hispanic or Latino!!Two or more races!!Two races including Some other race                           \n",
       "10  Estimate!!Total!!Not Hispanic or Latino!!Two or more races!!Two races excluding Some other race, and three or more races  \n",
       "11  Estimate!!Total!!Hispanic or Latino                                                                                       \n",
       "12  Estimate!!Total!!Hispanic or Latino!!White alone                                                                          \n",
       "13  Estimate!!Total!!Hispanic or Latino!!Black or African American alone                                                      \n",
       "14  Estimate!!Total!!Hispanic or Latino!!American Indian and Alaska Native alone                                              \n",
       "15  Estimate!!Total!!Hispanic or Latino!!Asian alone                                                                          \n",
       "16  Estimate!!Total!!Hispanic or Latino!!Native Hawaiian and Other Pacific Islander alone                                     \n",
       "17  Estimate!!Total!!Hispanic or Latino!!Some other race alone                                                                \n",
       "18  Estimate!!Total!!Hispanic or Latino!!Two or more races                                                                    \n",
       "19  Estimate!!Total!!Hispanic or Latino!!Two or more races!!Two races including Some other race                               \n",
       "20  Estimate!!Total!!Hispanic or Latino!!Two or more races!!Two races excluding Some other race, and three or more races      "
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# sql_engine.read_sql(\n",
    "#     f\"\"\"\n",
    "#     select\n",
    "#         census_variable_code,\n",
    "#         segment_group_id,\n",
    "#         census_variable_description\n",
    "#     from {census_taxonomy_table}\n",
    "#     where \n",
    "#         segment_group_id = 'HISPANIC_OR_LATINO_ETHNICITY'\n",
    "#     order by 1\n",
    "#     \"\"\"\n",
    "# )"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "id": "531c4cdc-2b61-4184-b8c4-0affe34a6a4b",
   "metadata": {},
   "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>census_variable_code</th>\n",
       "      <th>segment_group_id</th>\n",
       "      <th>census_variable_description</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>B02001_001E</td>\n",
       "      <td>ETHNICITY</td>\n",
       "      <td>Estimate!!Total</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>B02001_002E</td>\n",
       "      <td>ETHNICITY</td>\n",
       "      <td>Estimate!!Total!!White alone</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>B02001_003E</td>\n",
       "      <td>ETHNICITY</td>\n",
       "      <td>Estimate!!Total!!Black or African American alone</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>B02001_004E</td>\n",
       "      <td>ETHNICITY</td>\n",
       "      <td>Estimate!!Total!!American Indian and Alaska Native alone</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>B02001_005E</td>\n",
       "      <td>ETHNICITY</td>\n",
       "      <td>Estimate!!Total!!Asian alone</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>B02001_006E</td>\n",
       "      <td>ETHNICITY</td>\n",
       "      <td>Estimate!!Total!!Native Hawaiian and Other Pacific Islander alone</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>B02001_007E</td>\n",
       "      <td>ETHNICITY</td>\n",
       "      <td>Estimate!!Total!!Some other race alone</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>B02001_008E</td>\n",
       "      <td>ETHNICITY</td>\n",
       "      <td>Estimate!!Total!!Two or more races</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>B02001_009E</td>\n",
       "      <td>ETHNICITY</td>\n",
       "      <td>Estimate!!Total!!Two or more races!!Two races including Some other race</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>B02001_010E</td>\n",
       "      <td>ETHNICITY</td>\n",
       "      <td>Estimate!!Total!!Two or more races!!Two races excluding Some other race, and three or more races</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  census_variable_code segment_group_id  \\\n",
       "0  B02001_001E          ETHNICITY         \n",
       "1  B02001_002E          ETHNICITY         \n",
       "2  B02001_003E          ETHNICITY         \n",
       "3  B02001_004E          ETHNICITY         \n",
       "4  B02001_005E          ETHNICITY         \n",
       "5  B02001_006E          ETHNICITY         \n",
       "6  B02001_007E          ETHNICITY         \n",
       "7  B02001_008E          ETHNICITY         \n",
       "8  B02001_009E          ETHNICITY         \n",
       "9  B02001_010E          ETHNICITY         \n",
       "\n",
       "                                                                        census_variable_description  \n",
       "0  Estimate!!Total                                                                                   \n",
       "1  Estimate!!Total!!White alone                                                                      \n",
       "2  Estimate!!Total!!Black or African American alone                                                  \n",
       "3  Estimate!!Total!!American Indian and Alaska Native alone                                          \n",
       "4  Estimate!!Total!!Asian alone                                                                      \n",
       "5  Estimate!!Total!!Native Hawaiian and Other Pacific Islander alone                                 \n",
       "6  Estimate!!Total!!Some other race alone                                                            \n",
       "7  Estimate!!Total!!Two or more races                                                                \n",
       "8  Estimate!!Total!!Two or more races!!Two races including Some other race                           \n",
       "9  Estimate!!Total!!Two or more races!!Two races excluding Some other race, and three or more races  "
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# sql_engine.read_sql(\n",
    "#     f\"\"\"\n",
    "#     select\n",
    "#         census_variable_code,\n",
    "#         segment_group_id,\n",
    "#         census_variable_description\n",
    "#     from {census_taxonomy_table}\n",
    "#     where \n",
    "#         segment_group_id = 'ETHNICITY'\n",
    "#     order by 1\n",
    "#     \"\"\"\n",
    "# )"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "id": "84e9de6b-4a98-466a-9efb-5e5e9021f256",
   "metadata": {},
   "outputs": [],
   "source": [
    "# test = sql_engine.read_sql(f\"\"\"\n",
    "#         select\n",
    "#             geography_id_2 as block_group_id,\n",
    "#             census_variable_code as ethnicity,\n",
    "#             value,\n",
    "#             sum(value) over (partition by geography_id) as total_value\n",
    "#         from {census_data_table}\n",
    "#         where \n",
    "#             segment_group_id = 'HISPANIC_OR_LATINO_ETHNICITY'\n",
    "#             and admin_level = 'block_group'\n",
    "#             and geography_id_2 like 'US.CA.037.113303.2%'\n",
    "#             and census_variable_code != 'B03002_001E'\n",
    "#             and census_variable_code != 'B03002_002E'\n",
    "#             and census_variable_code != 'B03002_013E'\n",
    "#             and census_variable_code != 'B03002_014E'\n",
    "#             and census_variable_code != 'B03002_015E'\n",
    "#             and census_variable_code != 'B03002_016E'\n",
    "#             and census_variable_code != 'B03002_017E'\n",
    "#             and census_variable_code != 'B03002_018E'\n",
    "#             and census_variable_code != 'B03002_019E'\n",
    "#             and census_variable_code != 'B03002_020E'\n",
    "#             and census_variable_code != 'B03002_021E'\n",
    "#         \"\"\"\n",
    "# )\n",
    "\n",
    "# test"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "852e61f3-01cf-4924-a910-fd0386277749",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "6425\n",
      "CPU times: user 25.4 s, sys: 290 µs, total: 25.4 s\n",
      "Wall time: 47.4 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>block_group_id</th>\n",
       "      <th>population</th>\n",
       "      <th>R_White</th>\n",
       "      <th>R_HispanicLatinx</th>\n",
       "      <th>R_AfricanAmerican</th>\n",
       "      <th>R_AmericanIndianAlaska</th>\n",
       "      <th>R_Asian</th>\n",
       "      <th>R_Other</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>US.CA.037.120400.1</td>\n",
       "      <td>3412.0</td>\n",
       "      <td>0.203986</td>\n",
       "      <td>0.436108</td>\n",
       "      <td>0.084115</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.250293</td>\n",
       "      <td>0.025498</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>US.CA.037.199201.1</td>\n",
       "      <td>1373.0</td>\n",
       "      <td>0.007283</td>\n",
       "      <td>0.872542</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.120175</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>US.CA.037.218120.2</td>\n",
       "      <td>794.0</td>\n",
       "      <td>0.020151</td>\n",
       "      <td>0.700252</td>\n",
       "      <td>0.028967</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.217884</td>\n",
       "      <td>0.032746</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>US.CA.037.232400.4</td>\n",
       "      <td>2214.0</td>\n",
       "      <td>0.017164</td>\n",
       "      <td>0.628726</td>\n",
       "      <td>0.320235</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.033875</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>US.CA.037.265202.1</td>\n",
       "      <td>447.0</td>\n",
       "      <td>0.550336</td>\n",
       "      <td>0.098434</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.351230</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       block_group_id  population   R_White  R_HispanicLatinx  \\\n",
       "0  US.CA.037.120400.1  3412.0      0.203986  0.436108           \n",
       "1  US.CA.037.199201.1  1373.0      0.007283  0.872542           \n",
       "2  US.CA.037.218120.2  794.0       0.020151  0.700252           \n",
       "3  US.CA.037.232400.4  2214.0      0.017164  0.628726           \n",
       "4  US.CA.037.265202.1  447.0       0.550336  0.098434           \n",
       "\n",
       "   R_AfricanAmerican  R_AmericanIndianAlaska   R_Asian   R_Other  \n",
       "0  0.084115           0.0                     0.250293  0.025498  \n",
       "1  0.000000           0.0                     0.120175  0.000000  \n",
       "2  0.028967           0.0                     0.217884  0.032746  \n",
       "3  0.320235           0.0                     0.000000  0.033875  \n",
       "4  0.000000           0.0                     0.351230  0.000000  "
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%time \n",
    "\n",
    "### block_group level ethnicity data\n",
    "ethnicity_subquery = f\"\"\"\n",
    "    ethnicity_tmp as ( \n",
    "        select\n",
    "            geography_id_2 as block_group_id,\n",
    "            census_variable_code as ethnicity,\n",
    "            value,\n",
    "            sum(value) over (partition by geography_id) as total_value\n",
    "        from {census_data_table}\n",
    "        where \n",
    "            segment_group_id = 'HISPANIC_OR_LATINO_ETHNICITY'\n",
    "            and admin_level = 'block_group'\n",
    "            and geography_id_2 like '{geog_clause}'\n",
    "            and census_variable_code != 'B03002_001E'\n",
    "            and census_variable_code != 'B03002_002E'\n",
    "            and census_variable_code != 'B03002_013E'\n",
    "            and census_variable_code != 'B03002_014E'\n",
    "            and census_variable_code != 'B03002_015E'\n",
    "            and census_variable_code != 'B03002_016E'\n",
    "            and census_variable_code != 'B03002_017E'\n",
    "            and census_variable_code != 'B03002_018E'\n",
    "            and census_variable_code != 'B03002_019E'\n",
    "            and census_variable_code != 'B03002_020E'\n",
    "            and census_variable_code != 'B03002_021E'            \n",
    "    ),\n",
    "    \n",
    "    ethnicity as (\n",
    "        select\n",
    "            block_group_id,\n",
    "            total_value as population,\n",
    "            map_agg(ethnicity, value/total_value) as ethnicity\n",
    "        from ethnicity_tmp\n",
    "        group by 1, 2\n",
    "    )\n",
    "    \"\"\"\n",
    "\n",
    "df_ethnicity = sql_engine.read_sql(\n",
    "    f\"\"\"\n",
    "    with \n",
    "    {ethnicity_subquery}\n",
    "    select * from ethnicity\n",
    "    \"\"\"\n",
    ")\n",
    "\n",
    "def split_dict_to_columns(row, col):\n",
    "    for key, value in row[col].items():\n",
    "        row[key] = value\n",
    "    return pd.Series(row)\n",
    "\n",
    "# Apply the custom function to each row in the DataFrame\n",
    "df_ethnicity_new = df_ethnicity.apply(lambda x: split_dict_to_columns(x,'ethnicity'), axis=1)\n",
    "df_ethnicity_new['R_White'] = df_ethnicity_new['B03002_003E']\n",
    "df_ethnicity_new['R_AfricanAmerican'] = df_ethnicity_new['B03002_004E']\n",
    "df_ethnicity_new['R_AmericanIndianAlaska'] = df_ethnicity_new['B03002_005E']\n",
    "df_ethnicity_new['R_Asian'] = df_ethnicity_new['B03002_006E'] +  df_ethnicity_new['B03002_007E'] #Asian alone + Native Hawaiian and Other Pacific Islander alone\n",
    "df_ethnicity_new['R_Other'] = df_ethnicity_new['B03002_008E'] + df_ethnicity_new['B03002_009E']+df_ethnicity_new['B03002_010E']+df_ethnicity_new['B03002_011E']\n",
    "df_ethnicity_new['R_HispanicLatinx'] = df_ethnicity_new['B03002_012E']\n",
    "df_ethnicity_new = df_ethnicity_new[['block_group_id','population','R_White','R_HispanicLatinx','R_AfricanAmerican','R_AmericanIndianAlaska','R_Asian','R_Other']]\n",
    "print(df_ethnicity_new.shape[0])\n",
    "df_ethnicity_new.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "4b6ce87b-b8eb-4358-8309-9d592960b731",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2659052.0 4893603.0 795505.0 20307.0 1476381.0 476484.0\n",
      "10321332\n",
      "25.76268256849019\n",
      "47.41251419874877\n",
      "7.707386992299056\n",
      "0.1967478616132104\n",
      "14.304171205809482\n",
      "4.616497173039294\n"
     ]
    }
   ],
   "source": [
    "df_ethnicity_new['white'] = df_ethnicity_new['population']*df_ethnicity_new['R_White']\n",
    "df_ethnicity_new['latino'] = df_ethnicity_new['population']*df_ethnicity_new['R_HispanicLatinx']\n",
    "df_ethnicity_new['black'] = df_ethnicity_new['population']*df_ethnicity_new['R_AfricanAmerican']\n",
    "df_ethnicity_new['indian'] = df_ethnicity_new['population']*df_ethnicity_new['R_AmericanIndianAlaska']\n",
    "df_ethnicity_new['asian'] = df_ethnicity_new['population']*df_ethnicity_new['R_Asian']\n",
    "df_ethnicity_new['other'] = df_ethnicity_new['population']*df_ethnicity_new['R_Other']\n",
    "print(df_ethnicity_new['white'].sum(), df_ethnicity_new['latino'].sum(), df_ethnicity_new['black'].sum(), df_ethnicity_new['indian'].sum(), df_ethnicity_new['asian'].sum(), df_ethnicity_new['other'].sum())\n",
    "print(2659052 +  4893603 +  795505 +  20307 +  1476381 +  476484)\n",
    "for i  in [2659052,4893603,795505,20307,1476381,476484]:\n",
    "    print(i/10321332*100)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "id": "c0adac5b-2912-4f8c-a449-33ae83c71248",
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "CPU times: user 3.78 s, sys: 0 ns, total: 3.78 s\n",
      "Wall time: 3.78 s\n"
     ]
    }
   ],
   "source": [
    "%%time\n",
    "def compute_scaled_entropy(row,col1,col2,col3,col4,col5,col6):\n",
    "    sum_nominator = 0\n",
    "    for item in [row[col1],row[col2],row[col3],row[col4],row[col5],row[col6]]:\n",
    "        if item != 0:\n",
    "            sum_nominator += item*(np.log(1/item))\n",
    "    row['scaled_entropy'] = sum_nominator/np.log(6)\n",
    "    return row\n",
    "\n",
    "df_ethnicity_entropy = df_ethnicity_new.apply(lambda x:compute_scaled_entropy(x,'R_White','R_HispanicLatinx','R_AfricanAmerican','R_AmericanIndianAlaska','R_Asian','R_Other'), axis=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "e1d5a77c-cb69-495e-ad38-c53ada6bdc59",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "6419\n",
      "CPU times: user 221 ms, sys: 3.95 ms, total: 225 ms\n",
      "Wall time: 8.41 s\n"
     ]
    }
   ],
   "source": [
    "%%time\n",
    "# read LA CBG data\n",
    "cbg_geom = sql_engine.read_sql(\n",
    "    f\"\"\"\n",
    "    select\n",
    "        geography_id, geometry_wkt\n",
    "    from {geography_table}\n",
    "    where\n",
    "        country_code = 'US'\n",
    "        and geography_type_code = 'admin4'\n",
    "        and geography_id like '{geog_clause}'\n",
    "    \"\"\"\n",
    ")\n",
    "\n",
    "cbg_geom.rename(columns={'geography_id': 'block_group_id', 'geometry_wkt': 'geom'}, inplace=True)\n",
    "# eliminate Catalina island and another island in the south of Catalina island\n",
    "cbg_geom = cbg_geom[~cbg_geom['block_group_id'].isin(['US.CA.037.599100.2','US.CA.037.599000.2','US.CA.037.599000.1','US.CA.037.599000.4','US.CA.037.599000.3','US.CA.037.599100.1'])]\n",
    "print(cbg_geom.shape[0])"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "0a4c9fc2-deca-4037-8255-8de68b14473c",
   "metadata": {},
   "source": [
    "# census based segregation"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "id": "2283ec7b-34c9-445c-8907-2411cb4d0f63",
   "metadata": {},
   "outputs": [],
   "source": [
    "def identify_max_col(row,col1,col2,col3,col4,col5,col6):\n",
    "    dominant = max([row[col1],row[col2],row[col3],row[col4],row[col5],row[col6]])\n",
    "    if dominant == row[col1]: row['dominant_race'] = col1\n",
    "    if dominant == row[col2]: row['dominant_race'] = col2\n",
    "    if dominant == row[col3]: row['dominant_race'] = col3\n",
    "    if dominant == row[col4]: row['dominant_race'] = col4\n",
    "    if dominant == row[col5]: row['dominant_race'] = col5\n",
    "    if dominant == row[col6]: row['dominant_race'] = col6\n",
    "    return row\n",
    "\n",
    "def extract_dominant_group(df):\n",
    "    df.loc[df['scaled_entropy']<=0.3707,'class'] = 'low diversity'\n",
    "    df.loc[df['R_White']>=0.8,'class'] = 'low diversity'\n",
    "    df.loc[df['R_HispanicLatinx']>=0.8,'class'] = 'low diversity'\n",
    "    df.loc[df['R_AfricanAmerican']>=0.8,'class'] = 'low diversity'\n",
    "    df.loc[df['R_AmericanIndianAlaska']>=0.8,'class'] = 'low diversity'\n",
    "    df.loc[df['R_Asian']>=0.8,'class'] = 'low diversity'\n",
    "    df.loc[df['R_Other']>=0.8,'class'] = 'low diversity'\n",
    "\n",
    "    df.loc[(df['scaled_entropy']>=0.7414)&(df['R_White']<0.45)\n",
    "                             &(df['R_HispanicLatinx']<0.45)&(df['R_AfricanAmerican']<0.45)\n",
    "                             &(df['R_AmericanIndianAlaska']<0.45)&(df['R_Asian']<0.45)\n",
    "                             &(df['R_Other']<0.45)\n",
    "                             ,'class'] = 'high diversity'\n",
    "\n",
    "    df['class'] = df['class'].fillna('moderate diversity')\n",
    "    print(df['class'].value_counts())\n",
    "    \n",
    "    df = df.apply(lambda x:identify_max_col(x,'R_White','R_HispanicLatinx','R_AfricanAmerican','R_AmericanIndianAlaska','R_Asian','R_Other'), axis=1)\n",
    "    \n",
    "    df['class_dominant'] = df['dominant_race'] + '_' + df['class']\n",
    "    df.loc[df['class']=='high diversity','class_dominant'] = 'high diversity'\n",
    "    print(df.class_dominant.value_counts())\n",
    "    return df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "id": "617eb5b9-88f2-4047-9e27-3be910f1cac9",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "moderate diversity    4232\n",
      "low diversity         1676\n",
      "high diversity        517 \n",
      "Name: class, dtype: int64\n",
      "R_HispanicLatinx_moderate diversity     1907\n",
      "R_White_moderate diversity              1664\n",
      "R_HispanicLatinx_low diversity          1342\n",
      "high diversity                          517 \n",
      "R_Asian_moderate diversity              449 \n",
      "R_White_low diversity                   235 \n",
      "R_AfricanAmerican_moderate diversity    180 \n",
      "R_AfricanAmerican_low diversity         49  \n",
      "R_Asian_low diversity                   48  \n",
      "R_Other_moderate diversity              3   \n",
      "R_AmericanIndianAlaska_low diversity    2   \n",
      "Name: class_dominant, dtype: int64\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>R_AfricanAmerican</th>\n",
       "      <th>R_AmericanIndianAlaska</th>\n",
       "      <th>R_Asian</th>\n",
       "      <th>R_HispanicLatinx</th>\n",
       "      <th>R_Other</th>\n",
       "      <th>R_White</th>\n",
       "      <th>block_group_id</th>\n",
       "      <th>class</th>\n",
       "      <th>dominant_race</th>\n",
       "      <th>population</th>\n",
       "      <th>scaled_entropy</th>\n",
       "      <th>class_dominant</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0.024465</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.016820</td>\n",
       "      <td>0.928135</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.030581</td>\n",
       "      <td>US.CA.037.104320.4</td>\n",
       "      <td>low diversity</td>\n",
       "      <td>R_HispanicLatinx</td>\n",
       "      <td>654.0</td>\n",
       "      <td>0.187165</td>\n",
       "      <td>R_HispanicLatinx_low diversity</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>0.007865</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.065169</td>\n",
       "      <td>0.206742</td>\n",
       "      <td>0.144944</td>\n",
       "      <td>0.575281</td>\n",
       "      <td>US.CA.037.187300.2</td>\n",
       "      <td>moderate diversity</td>\n",
       "      <td>R_White</td>\n",
       "      <td>1780.0</td>\n",
       "      <td>0.636230</td>\n",
       "      <td>R_White_moderate diversity</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>0.592987</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.001252</td>\n",
       "      <td>0.324358</td>\n",
       "      <td>0.046337</td>\n",
       "      <td>0.035066</td>\n",
       "      <td>US.CA.037.234502.3</td>\n",
       "      <td>moderate diversity</td>\n",
       "      <td>R_AfricanAmerican</td>\n",
       "      <td>1597.0</td>\n",
       "      <td>0.526454</td>\n",
       "      <td>R_AfricanAmerican_moderate diversity</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>0.021684</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.153571</td>\n",
       "      <td>0.526020</td>\n",
       "      <td>0.046173</td>\n",
       "      <td>0.252551</td>\n",
       "      <td>US.CA.037.400603.1</td>\n",
       "      <td>moderate diversity</td>\n",
       "      <td>R_HispanicLatinx</td>\n",
       "      <td>3920.0</td>\n",
       "      <td>0.668769</td>\n",
       "      <td>R_HispanicLatinx_moderate diversity</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>0.107566</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.238037</td>\n",
       "      <td>0.116973</td>\n",
       "      <td>0.104703</td>\n",
       "      <td>0.432720</td>\n",
       "      <td>US.CA.037.463602.2</td>\n",
       "      <td>high diversity</td>\n",
       "      <td>R_White</td>\n",
       "      <td>2445.0</td>\n",
       "      <td>0.798796</td>\n",
       "      <td>high diversity</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   R_AfricanAmerican  R_AmericanIndianAlaska   R_Asian  R_HispanicLatinx  \\\n",
       "0  0.024465           0.0                     0.016820  0.928135           \n",
       "1  0.007865           0.0                     0.065169  0.206742           \n",
       "2  0.592987           0.0                     0.001252  0.324358           \n",
       "3  0.021684           0.0                     0.153571  0.526020           \n",
       "4  0.107566           0.0                     0.238037  0.116973           \n",
       "\n",
       "    R_Other   R_White      block_group_id               class  \\\n",
       "0  0.000000  0.030581  US.CA.037.104320.4  low diversity        \n",
       "1  0.144944  0.575281  US.CA.037.187300.2  moderate diversity   \n",
       "2  0.046337  0.035066  US.CA.037.234502.3  moderate diversity   \n",
       "3  0.046173  0.252551  US.CA.037.400603.1  moderate diversity   \n",
       "4  0.104703  0.432720  US.CA.037.463602.2  high diversity       \n",
       "\n",
       "       dominant_race  population  scaled_entropy  \\\n",
       "0  R_HispanicLatinx   654.0       0.187165         \n",
       "1  R_White            1780.0      0.636230         \n",
       "2  R_AfricanAmerican  1597.0      0.526454         \n",
       "3  R_HispanicLatinx   3920.0      0.668769         \n",
       "4  R_White            2445.0      0.798796         \n",
       "\n",
       "                         class_dominant  \n",
       "0  R_HispanicLatinx_low diversity        \n",
       "1  R_White_moderate diversity            \n",
       "2  R_AfricanAmerican_moderate diversity  \n",
       "3  R_HispanicLatinx_moderate diversity   \n",
       "4  high diversity                        "
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_ethnicity_entropy = extract_dominant_group(df_ethnicity_entropy)\n",
    "df_ethnicity_entropy.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "898b93fa-8953-4b47-a1fd-6e9cce3132ea",
   "metadata": {},
   "outputs": [],
   "source": [
    "if geog_clause == 'US.CA.%':\n",
    "    df_ethnicity_entropy.to_pickle(\"../output/CA_CBG_dominantRace.pkl\")\n",
    "elif geog_clause == 'US.CA.037%':\n",
    "    df_ethnicity_entropy.to_pickle(\"../output/LA_CBG_dominantRace.pkl\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "15452312-e09f-4a73-b771-85dfa7972e57",
   "metadata": {},
   "outputs": [],
   "source": [
    "# sum_nominator = 0\n",
    "# for item in [0.45,0.45,0.1,0,0,0]:\n",
    "#     if item != 0:\n",
    "#         sum_nominator += item*(np.log(1/item))\n",
    "# sum_nominator/np.log(6) #= 0.36\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "4eb12bf4-e4ff-4cec-9c29-4a4a3ac62d1f",
   "metadata": {},
   "source": [
    "## Mapping"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "c39465d0-d3c0-4496-997e-1f8c3223b5e7",
   "metadata": {},
   "outputs": [],
   "source": [
    "if geog_clause == 'US.CA.%':\n",
    "    df_ethnicity_entropy = pd.read_pickle(\"../output/CA_CBG_dominantRace.pkl\")\n",
    "elif geog_clause == 'US.CA.037%':\n",
    "    df_ethnicity_entropy = pd.read_pickle(\"../output/LA_CBG_dominantRace.pkl\")\n",
    "print(df_ethnicity_entropy.shape[0]) #6425 CBGs    "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "a2f7ab4e-0331-4a15-b500-6c6389405643",
   "metadata": {},
   "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>block_group_id</th>\n",
       "      <th>population</th>\n",
       "      <th>class_dominant</th>\n",
       "      <th>class</th>\n",
       "      <th>dominant_race</th>\n",
       "      <th>scaled_entropy</th>\n",
       "      <th>geom</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>US.CA.037.119340.1</td>\n",
       "      <td>4032.0</td>\n",
       "      <td>R_HispanicLatinx_low diversity</td>\n",
       "      <td>low diversity</td>\n",
       "      <td>R_HispanicLatinx</td>\n",
       "      <td>0.124208</td>\n",
       "      <td>POLYGON ((-118.453549 34.235433, -118.453549 34.231806999999996, -118.450254 34.231784000000005, -118.450254 34.233134, -118.45025000000001 34.23541, -118.453549 34.235433))</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       block_group_id  population                  class_dominant  \\\n",
       "0  US.CA.037.119340.1  4032.0      R_HispanicLatinx_low diversity   \n",
       "\n",
       "           class     dominant_race  scaled_entropy  \\\n",
       "0  low diversity  R_HispanicLatinx  0.124208         \n",
       "\n",
       "                                                                                                                                                                            geom  \n",
       "0  POLYGON ((-118.453549 34.235433, -118.453549 34.231806999999996, -118.450254 34.231784000000005, -118.450254 34.233134, -118.45025000000001 34.23541, -118.453549 34.235433))  "
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_ethnicity_shp = pd.merge(df_ethnicity_entropy[['block_group_id','population','class_dominant','class','dominant_race','scaled_entropy']],cbg_geom,on='block_group_id')\n",
    "df_ethnicity_shp = df_ethnicity_shp[df_ethnicity_shp.scaled_entropy>=0]\n",
    "print(df_ethnicity_shp.shape[0]) #6390 CBGs\n",
    "df_ethnicity_shp.head(1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "aab3bfcb-03e6-4224-8d3d-c4b495326583",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "6390\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "R_HispanicLatinx_moderate diversity     1906\n",
       "R_White_moderate diversity              1660\n",
       "R_HispanicLatinx_low diversity          1341\n",
       "high diversity                          517 \n",
       "R_Asian_moderate diversity              449 \n",
       "R_White_low diversity                   235 \n",
       "R_AfricanAmerican_moderate diversity    180 \n",
       "R_AfricanAmerican_low diversity         49  \n",
       "R_Asian_low diversity                   48  \n",
       "R_Other_moderate diversity              3   \n",
       "R_AmericanIndianAlaska_low diversity    2   \n",
       "Name: class_dominant, dtype: int64"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "print(df_ethnicity_shp['block_group_id'].unique().shape[0])\n",
    "df_ethnicity_shp['class_dominant'].value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "3cdb6ca2-5871-466e-9b3e-4054de5dd3ab",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "7fecc9f6-b1e3-493d-82d5-41869d0e41b9",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "R_HispanicLatinx_moderate diversity     29.827856\n",
       "R_White_moderate diversity              25.978091\n",
       "R_HispanicLatinx_low diversity          20.985915\n",
       "high diversity                          8.090767 \n",
       "R_Asian_moderate diversity              7.026604 \n",
       "R_White_low diversity                   3.677621 \n",
       "R_AfricanAmerican_moderate diversity    2.816901 \n",
       "R_AfricanAmerican_low diversity         0.766823 \n",
       "R_Asian_low diversity                   0.751174 \n",
       "R_Other_moderate diversity              0.046948 \n",
       "R_AmericanIndianAlaska_low diversity    0.031299 \n",
       "Name: class_dominant, dtype: float64"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_ethnicity_shp['class_dominant'].value_counts() / len(df_ethnicity_shp) * 100"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "6d54e899-e78e-4469-b6c9-801c37ade3bf",
   "metadata": {},
   "outputs": [],
   "source": [
    "gdf_ethnicity_shp = geopd.GeoDataFrame(df_ethnicity_shp, crs=\"EPSG:4326\", geometry='geom')\n",
    "gdf_ethnicity_shp.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "4977e9c5-5647-4655-b7b8-565d024c2c7b",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_ethnicity_shp.to_file('../output/LA_CBG_dominantRace.shp', driver='ESRI Shapefile')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "id": "4fb8144a-294b-4f6e-a952-9ea260d30820",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "User Guide: https://docs.kepler.gl/docs/keplergl-jupyter\n",
      "CPU times: user 79 ms, sys: 15.6 ms, total: 94.6 ms\n",
      "Wall time: 92.6 ms\n"
     ]
    },
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "52887176d9b34aa8a7e5bb3caed60bc3",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "KeplerGl(config={'version': 'v1', 'config': {'visState': {'filters': [], 'layers': [{'id': '4gb7yr', 'type': '…"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "%%time\n",
    "df_ethnicity_shp['category'] = df_ethnicity_shp['class_dominant']\n",
    "\n",
    "k2 = KeplerGl(data={'h':df_ethnicity_shp[df_ethnicity_shp['category']!='R_Other_moderate diversity']}, height=700) \n",
    "with open('config/home_entropy.py', 'r') as config_file:\n",
    "    k2.config = json.loads(config_file.read())\n",
    "k2 # k.save_to_html(file_name='test.html')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "873b41c2-d4aa-46f6-b196-944dcd2fd281",
   "metadata": {},
   "source": [
    "# cubic work location based segregation"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 77,
   "id": "7319da89-18f0-4fca-8ccb-958cd8eac2a4",
   "metadata": {},
   "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>block_group_id</th>\n",
       "      <th>population</th>\n",
       "      <th>R_White</th>\n",
       "      <th>R_HispanicLatinx</th>\n",
       "      <th>R_AfricanAmerican</th>\n",
       "      <th>R_AmericanIndianAlaska</th>\n",
       "      <th>R_Asian</th>\n",
       "      <th>R_Other</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>US.CA.037.113303.2</td>\n",
       "      <td>1901.0</td>\n",
       "      <td>0.375066</td>\n",
       "      <td>0.471331</td>\n",
       "      <td>0.027880</td>\n",
       "      <td>0.005786</td>\n",
       "      <td>0.092057</td>\n",
       "      <td>0.027880</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>US.CA.037.134201.2</td>\n",
       "      <td>914.0</td>\n",
       "      <td>0.358862</td>\n",
       "      <td>0.420131</td>\n",
       "      <td>0.047046</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.163020</td>\n",
       "      <td>0.010941</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>US.CA.037.188100.1</td>\n",
       "      <td>1880.0</td>\n",
       "      <td>0.332979</td>\n",
       "      <td>0.413298</td>\n",
       "      <td>0.023404</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.031915</td>\n",
       "      <td>0.198404</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>US.CA.037.194500.2</td>\n",
       "      <td>963.0</td>\n",
       "      <td>0.839045</td>\n",
       "      <td>0.017653</td>\n",
       "      <td>0.006231</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.058152</td>\n",
       "      <td>0.078920</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>US.CA.037.209810.2</td>\n",
       "      <td>1316.0</td>\n",
       "      <td>0.155775</td>\n",
       "      <td>0.622340</td>\n",
       "      <td>0.054711</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.127660</td>\n",
       "      <td>0.039514</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       block_group_id  population   R_White  R_HispanicLatinx  \\\n",
       "0  US.CA.037.113303.2  1901.0      0.375066  0.471331           \n",
       "1  US.CA.037.134201.2  914.0       0.358862  0.420131           \n",
       "2  US.CA.037.188100.1  1880.0      0.332979  0.413298           \n",
       "3  US.CA.037.194500.2  963.0       0.839045  0.017653           \n",
       "4  US.CA.037.209810.2  1316.0      0.155775  0.622340           \n",
       "\n",
       "   R_AfricanAmerican  R_AmericanIndianAlaska   R_Asian   R_Other  \n",
       "0  0.027880           0.005786                0.092057  0.027880  \n",
       "1  0.047046           0.000000                0.163020  0.010941  \n",
       "2  0.023404           0.000000                0.031915  0.198404  \n",
       "3  0.006231           0.000000                0.058152  0.078920  \n",
       "4  0.054711           0.000000                0.127660  0.039514  "
      ]
     },
     "execution_count": 77,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_ethnicity_new.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 78,
   "id": "1de20bb9-4515-4e0b-ba61-ed1565726802",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "CPU times: user 3.72 s, sys: 183 ms, total: 3.9 s\n",
      "Wall time: 22.4 s\n"
     ]
    }
   ],
   "source": [
    "%%time\n",
    "#select work locations\n",
    "\n",
    "dfg_work = sql_engine.read_sql(\n",
    "    f\"\"\"\n",
    "    select *\n",
    "    from {hw_table}\n",
    "    where\n",
    "        provider_id = '190199'\n",
    "        and country_code = 'US'\n",
    "        and snapshot_event_date = {date} \n",
    "        and tag_type_code = 'WORK'\n",
    "        and block_group_id like 'US.CA.037%'   --- <<< filter recurring areas in LA\n",
    "    \"\"\"\n",
    ")\n",
    "\n",
    "dfg_work = dfg_work[~dfg_work['block_group_id'].isin(['US.CA.037.599100.2','US.CA.037.599000.2','US.CA.037.599000.1','US.CA.037.599000.4','US.CA.037.599000.3','US.CA.037.599100.1'])]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "ce61c8ce-0094-4d8e-b55e-3fd098c87c26",
   "metadata": {},
   "outputs": [],
   "source": [
    "# dfg_work\n",
    "df_home_loc_race = pd.merge(df_home_loc[['cuebiq_id','block_group_id']],df_ethnicity_new,on='block_group_id')  \n",
    "df_home_loc_race.rename(columns={'block_group_id': 'home_block'}, inplace=True)\n",
    "df_home_loc_race = df_home_loc_race[df_home_loc_race['R_White'].notna()]\n",
    "df_home_loc_race"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 73,
   "id": "2d21a073-ff59-4446-87bf-e95c18348bab",
   "metadata": {},
   "outputs": [],
   "source": [
    "# dfg_work['confidence_level'].unique()\n",
    "# test = dfg_work.groupby('cuebiq_id').size().reset_index(name='counts')\n",
    "# test[test.counts>1]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 83,
   "id": "2321da73-f8b5-4bb1-884f-373b21c66bf7",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "271594\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/tmp/ipykernel_76/655762048.py:5: FutureWarning: The default value of numeric_only in DataFrameGroupBy.sum is deprecated. In a future version, numeric_only will default to False. Either specify numeric_only or select only columns which should be valid for the function.\n",
      "  df_work_loc_race = df_work_loc_race.groupby('block_group_id').sum().reset_index()\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>block_group_id</th>\n",
       "      <th>population</th>\n",
       "      <th>R_White</th>\n",
       "      <th>R_HispanicLatinx</th>\n",
       "      <th>R_AfricanAmerican</th>\n",
       "      <th>R_AmericanIndianAlaska</th>\n",
       "      <th>R_Asian</th>\n",
       "      <th>R_Other</th>\n",
       "      <th>devices</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>US.CA.037.101110.1</td>\n",
       "      <td>28698.0</td>\n",
       "      <td>7.145338</td>\n",
       "      <td>3.369432</td>\n",
       "      <td>0.302998</td>\n",
       "      <td>0.002159</td>\n",
       "      <td>2.059023</td>\n",
       "      <td>1.121050</td>\n",
       "      <td>14</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>US.CA.037.101110.2</td>\n",
       "      <td>50583.0</td>\n",
       "      <td>12.286846</td>\n",
       "      <td>8.211290</td>\n",
       "      <td>0.641664</td>\n",
       "      <td>0.130364</td>\n",
       "      <td>2.766493</td>\n",
       "      <td>0.963342</td>\n",
       "      <td>25</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>US.CA.037.101110.3</td>\n",
       "      <td>282420.0</td>\n",
       "      <td>67.406997</td>\n",
       "      <td>53.846179</td>\n",
       "      <td>4.102572</td>\n",
       "      <td>0.272035</td>\n",
       "      <td>13.759681</td>\n",
       "      <td>5.612535</td>\n",
       "      <td>145</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>US.CA.037.101122.1</td>\n",
       "      <td>33281.0</td>\n",
       "      <td>9.562052</td>\n",
       "      <td>3.792788</td>\n",
       "      <td>0.447056</td>\n",
       "      <td>0.090872</td>\n",
       "      <td>2.638460</td>\n",
       "      <td>1.468773</td>\n",
       "      <td>18</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>US.CA.037.101122.2</td>\n",
       "      <td>27863.0</td>\n",
       "      <td>6.967776</td>\n",
       "      <td>4.655852</td>\n",
       "      <td>0.152566</td>\n",
       "      <td>0.014357</td>\n",
       "      <td>2.355524</td>\n",
       "      <td>0.853924</td>\n",
       "      <td>15</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       block_group_id  population    R_White  R_HispanicLatinx  \\\n",
       "0  US.CA.037.101110.1  28698.0     7.145338   3.369432           \n",
       "1  US.CA.037.101110.2  50583.0     12.286846  8.211290           \n",
       "2  US.CA.037.101110.3  282420.0    67.406997  53.846179          \n",
       "3  US.CA.037.101122.1  33281.0     9.562052   3.792788           \n",
       "4  US.CA.037.101122.2  27863.0     6.967776   4.655852           \n",
       "\n",
       "   R_AfricanAmerican  R_AmericanIndianAlaska    R_Asian   R_Other  devices  \n",
       "0  0.302998           0.002159                2.059023   1.121050  14       \n",
       "1  0.641664           0.130364                2.766493   0.963342  25       \n",
       "2  4.102572           0.272035                13.759681  5.612535  145      \n",
       "3  0.447056           0.090872                2.638460   1.468773  18       \n",
       "4  0.152566           0.014357                2.355524   0.853924  15       "
      ]
     },
     "execution_count": 83,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_work_loc_race = pd.merge(dfg_work[['cuebiq_id','block_group_id']],df_home_loc_race,on='cuebiq_id')  \n",
    "print(df_work_loc_race.shape[0]) #27k\n",
    "df_work_loc_race = df_work_loc_race.drop(['cuebiq_id'], inplace=False, axis=1)\n",
    "df_work_loc_race['devices'] = 1\n",
    "df_work_loc_race = df_work_loc_race.groupby('block_group_id').sum().reset_index()\n",
    "df_work_loc_race = df_work_loc_race[df_work_loc_race['devices']>5]\n",
    "df_work_loc_race.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 84,
   "id": "396bec99-5e7b-4e2d-a3ef-a8114eefedd8",
   "metadata": {},
   "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>block_group_id</th>\n",
       "      <th>population</th>\n",
       "      <th>R_White</th>\n",
       "      <th>R_HispanicLatinx</th>\n",
       "      <th>R_AfricanAmerican</th>\n",
       "      <th>R_AmericanIndianAlaska</th>\n",
       "      <th>R_Asian</th>\n",
       "      <th>R_Other</th>\n",
       "      <th>devices</th>\n",
       "      <th>scaled_entropy</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>US.CA.037.101110.1</td>\n",
       "      <td>28698.0</td>\n",
       "      <td>0.510381</td>\n",
       "      <td>0.240674</td>\n",
       "      <td>0.021643</td>\n",
       "      <td>0.000154</td>\n",
       "      <td>0.147073</td>\n",
       "      <td>0.080075</td>\n",
       "      <td>14</td>\n",
       "      <td>0.700135</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>US.CA.037.101110.2</td>\n",
       "      <td>50583.0</td>\n",
       "      <td>0.491474</td>\n",
       "      <td>0.328452</td>\n",
       "      <td>0.025667</td>\n",
       "      <td>0.005215</td>\n",
       "      <td>0.110660</td>\n",
       "      <td>0.038534</td>\n",
       "      <td>25</td>\n",
       "      <td>0.672684</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>US.CA.037.101110.3</td>\n",
       "      <td>282420.0</td>\n",
       "      <td>0.464876</td>\n",
       "      <td>0.371353</td>\n",
       "      <td>0.028294</td>\n",
       "      <td>0.001876</td>\n",
       "      <td>0.094894</td>\n",
       "      <td>0.038707</td>\n",
       "      <td>145</td>\n",
       "      <td>0.661886</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>US.CA.037.101122.1</td>\n",
       "      <td>33281.0</td>\n",
       "      <td>0.531225</td>\n",
       "      <td>0.210710</td>\n",
       "      <td>0.024836</td>\n",
       "      <td>0.005048</td>\n",
       "      <td>0.146581</td>\n",
       "      <td>0.081598</td>\n",
       "      <td>18</td>\n",
       "      <td>0.708016</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>US.CA.037.101122.2</td>\n",
       "      <td>27863.0</td>\n",
       "      <td>0.464518</td>\n",
       "      <td>0.310390</td>\n",
       "      <td>0.010171</td>\n",
       "      <td>0.000957</td>\n",
       "      <td>0.157035</td>\n",
       "      <td>0.056928</td>\n",
       "      <td>15</td>\n",
       "      <td>0.684521</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6386</th>\n",
       "      <td>US.CA.037.980025.1</td>\n",
       "      <td>108203.0</td>\n",
       "      <td>0.229204</td>\n",
       "      <td>0.469824</td>\n",
       "      <td>0.120232</td>\n",
       "      <td>0.002444</td>\n",
       "      <td>0.130173</td>\n",
       "      <td>0.048123</td>\n",
       "      <td>65</td>\n",
       "      <td>0.766484</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6388</th>\n",
       "      <td>US.CA.037.980028.1</td>\n",
       "      <td>2401336.0</td>\n",
       "      <td>0.212360</td>\n",
       "      <td>0.462763</td>\n",
       "      <td>0.129516</td>\n",
       "      <td>0.002487</td>\n",
       "      <td>0.145143</td>\n",
       "      <td>0.047732</td>\n",
       "      <td>1233</td>\n",
       "      <td>0.776107</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6389</th>\n",
       "      <td>US.CA.037.980030.1</td>\n",
       "      <td>94515.0</td>\n",
       "      <td>0.253027</td>\n",
       "      <td>0.424919</td>\n",
       "      <td>0.075433</td>\n",
       "      <td>0.003415</td>\n",
       "      <td>0.180066</td>\n",
       "      <td>0.063139</td>\n",
       "      <td>59</td>\n",
       "      <td>0.786310</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6390</th>\n",
       "      <td>US.CA.037.980031.1</td>\n",
       "      <td>417304.0</td>\n",
       "      <td>0.311659</td>\n",
       "      <td>0.409415</td>\n",
       "      <td>0.078140</td>\n",
       "      <td>0.001901</td>\n",
       "      <td>0.132839</td>\n",
       "      <td>0.066045</td>\n",
       "      <td>228</td>\n",
       "      <td>0.774489</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6391</th>\n",
       "      <td>US.CA.037.980033.1</td>\n",
       "      <td>313879.0</td>\n",
       "      <td>0.252124</td>\n",
       "      <td>0.455568</td>\n",
       "      <td>0.085714</td>\n",
       "      <td>0.007525</td>\n",
       "      <td>0.156091</td>\n",
       "      <td>0.042979</td>\n",
       "      <td>184</td>\n",
       "      <td>0.769129</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5785 rows × 10 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "          block_group_id  population   R_White  R_HispanicLatinx  \\\n",
       "0     US.CA.037.101110.1  28698.0     0.510381  0.240674           \n",
       "1     US.CA.037.101110.2  50583.0     0.491474  0.328452           \n",
       "2     US.CA.037.101110.3  282420.0    0.464876  0.371353           \n",
       "3     US.CA.037.101122.1  33281.0     0.531225  0.210710           \n",
       "4     US.CA.037.101122.2  27863.0     0.464518  0.310390           \n",
       "...                  ...      ...          ...       ...           \n",
       "6386  US.CA.037.980025.1  108203.0    0.229204  0.469824           \n",
       "6388  US.CA.037.980028.1  2401336.0   0.212360  0.462763           \n",
       "6389  US.CA.037.980030.1  94515.0     0.253027  0.424919           \n",
       "6390  US.CA.037.980031.1  417304.0    0.311659  0.409415           \n",
       "6391  US.CA.037.980033.1  313879.0    0.252124  0.455568           \n",
       "\n",
       "      R_AfricanAmerican  R_AmericanIndianAlaska   R_Asian   R_Other  devices  \\\n",
       "0     0.021643           0.000154                0.147073  0.080075  14        \n",
       "1     0.025667           0.005215                0.110660  0.038534  25        \n",
       "2     0.028294           0.001876                0.094894  0.038707  145       \n",
       "3     0.024836           0.005048                0.146581  0.081598  18        \n",
       "4     0.010171           0.000957                0.157035  0.056928  15        \n",
       "...        ...                ...                     ...       ...  ..        \n",
       "6386  0.120232           0.002444                0.130173  0.048123  65        \n",
       "6388  0.129516           0.002487                0.145143  0.047732  1233      \n",
       "6389  0.075433           0.003415                0.180066  0.063139  59        \n",
       "6390  0.078140           0.001901                0.132839  0.066045  228       \n",
       "6391  0.085714           0.007525                0.156091  0.042979  184       \n",
       "\n",
       "      scaled_entropy  \n",
       "0     0.700135        \n",
       "1     0.672684        \n",
       "2     0.661886        \n",
       "3     0.708016        \n",
       "4     0.684521        \n",
       "...        ...        \n",
       "6386  0.766484        \n",
       "6388  0.776107        \n",
       "6389  0.786310        \n",
       "6390  0.774489        \n",
       "6391  0.769129        \n",
       "\n",
       "[5785 rows x 10 columns]"
      ]
     },
     "execution_count": 84,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# df_work_loc_race['total'] = df_work_loc_race['B02001_002E'] + df_work_loc_race['B02001_003E']+ df_work_loc_race['B02001_004E']+ df_work_loc_race['B02001_005E']+ df_work_loc_race['B02001_006E']\n",
    "# print(df_work_loc_race.shape[0])\n",
    "df_work_loc_race['R_White'] = df_work_loc_race['R_White']/df_work_loc_race['devices']\n",
    "df_work_loc_race['R_AfricanAmerican'] = df_work_loc_race['R_AfricanAmerican']/df_work_loc_race['devices']\n",
    "df_work_loc_race['R_AmericanIndianAlaska'] = df_work_loc_race['R_AmericanIndianAlaska']/df_work_loc_race['devices']\n",
    "df_work_loc_race['R_Asian'] = df_work_loc_race['R_Asian']/df_work_loc_race['devices']\n",
    "df_work_loc_race['R_HispanicLatinx'] = df_work_loc_race['R_HispanicLatinx']/df_work_loc_race['devices']\n",
    "df_work_loc_race['R_Other'] = df_work_loc_race['R_Other']/df_work_loc_race['devices']\n",
    "\n",
    "df_work_loc_entropy = df_work_loc_race.apply(lambda x:compute_scaled_entropy(x,'R_White','R_AfricanAmerican','R_HispanicLatinx','R_AmericanIndianAlaska','R_Asian','R_Other'), axis=1)\n",
    "df_work_loc_entropy = df_work_loc_entropy[df_work_loc_entropy['scaled_entropy'].notna()]\n",
    "df_work_loc_entropy"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 86,
   "id": "b3c80b9a-d808-44f5-a68d-a90dcdde35d5",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "moderate diversity    4378\n",
      "high diversity        1109\n",
      "low diversity         298 \n",
      "Name: class, dtype: int64\n",
      "R_HispanicLatinx_moderate diversity     3109\n",
      "high diversity                          1109\n",
      "R_White_moderate diversity              1059\n",
      "R_HispanicLatinx_low diversity          298 \n",
      "R_Asian_moderate diversity              189 \n",
      "R_AfricanAmerican_moderate diversity    21  \n",
      "Name: class_dominantclass_dominant, dtype: int64\n"
     ]
    }
   ],
   "source": [
    "df_work_loc_entropy = extract_dominant_group(df_work_loc_entropy)\n",
    "df_work_loc_entropy.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 109,
   "id": "80b8d6fd-e6a9-4415-8968-6f43bd34a383",
   "metadata": {},
   "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>block_group_id</th>\n",
       "      <th>population</th>\n",
       "      <th>devices</th>\n",
       "      <th>class_dominantclass_dominant</th>\n",
       "      <th>class</th>\n",
       "      <th>dominant_race</th>\n",
       "      <th>scaled_entropy</th>\n",
       "      <th>geom</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>US.CA.037.101110.1</td>\n",
       "      <td>28698.0</td>\n",
       "      <td>14</td>\n",
       "      <td>R_White_moderate diversity</td>\n",
       "      <td>moderate diversity</td>\n",
       "      <td>R_White</td>\n",
       "      <td>0.700135</td>\n",
       "      <td>POLYGON ((-118.28592399999998 34.255894999999995, -118.28498000000002 34.255888999999996, -118.284913 34.262463, -118.28846699999998 34.26282, -118.29130900000001 34.262854, -118.29132400000002 34.259555000000006, -118.291044 34.255927, -118.287333 34.255904, -118.28592399999998 34.255894999999995))</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       block_group_id  population  devices class_dominantclass_dominant  \\\n",
       "0  US.CA.037.101110.1  28698.0     14       R_White_moderate diversity    \n",
       "\n",
       "                class dominant_race  scaled_entropy  \\\n",
       "0  moderate diversity  R_White       0.700135         \n",
       "\n",
       "                                                                                                                                                                                                                                                                                                           geom  \n",
       "0  POLYGON ((-118.28592399999998 34.255894999999995, -118.28498000000002 34.255888999999996, -118.284913 34.262463, -118.28846699999998 34.26282, -118.29130900000001 34.262854, -118.29132400000002 34.259555000000006, -118.291044 34.255927, -118.287333 34.255904, -118.28592399999998 34.255894999999995))  "
      ]
     },
     "execution_count": 109,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_work_ethnicity_shp = pd.merge(df_work_loc_entropy[['block_group_id','population','devices','class_dominant','class','dominant_race','scaled_entropy']],cbg_geom,on='block_group_id')\n",
    "df_work_ethnicity_shp = df_work_ethnicity_shp[df_work_ethnicity_shp.scaled_entropy>=0]\n",
    "df_work_ethnicity_shp.head(1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 113,
   "id": "aee46a90-afed-42c8-97e4-f77b463ce9d5",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "User Guide: https://docs.kepler.gl/docs/keplergl-jupyter\n",
      "CPU times: user 101 ms, sys: 104 µs, total: 101 ms\n",
      "Wall time: 99 ms\n"
     ]
    },
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "689cffaa47a44fe192e57af04bfe531d",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "KeplerGl(config={'version': 'v1', 'config': {'visState': {'filters': [], 'layers': [{'id': '4gb7yr', 'type': '…"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "%%time\n",
    "df_work_ethnicity_shp['category'] = df_work_ethnicity_shp['class_dominant']\n",
    "k3 = KeplerGl(data={'h':df_work_ethnicity_shp}, height=700) \n",
    "with open('config/work_entropy.py', 'r') as config_file:\n",
    "    k3.config = json.loads(config_file.read())\n",
    "k3 "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "3d6eafa2-19bf-4f12-b7a9-4c173fb55243",
   "metadata": {},
   "source": [
    "# Stop location based segregation (don't run below)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "d5fdb97f-642d-4fd7-ba6b-9973c30fad5c",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_home_loc_race "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "id": "e3cece9b-f2f5-48f4-8026-164d1e9b8f10",
   "metadata": {},
   "outputs": [],
   "source": [
    "# stop_table = f\"dedicated.rsu.la_stop_uplevelled\"\n",
    "# visit_table = f\"cuebiq.paas_cda_pe_v3.visit\"\n",
    "# date_plus = int((datetime.strptime(str(date), \"%Y%m%d\") + timedelta(days=3)).strftime(\"%Y%m%d\"))# You see that we can limit to the first three processing dates starting from the local date of interest.\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 46,
   "id": "ccf33f91-5497-4987-be04-1698d12d05e6",
   "metadata": {},
   "outputs": [],
   "source": [
    "# %%time\n",
    "# df_stop_LA = sql_engine.read_sql(\n",
    "#     f\"\"\"\n",
    "#     select *, cast(hour(from_iso8601_timestamp(stop_zoned_datetime)) as int) as hour\n",
    "#     from {stop_table}\n",
    "#     where\n",
    "#         substr(stop_zoned_datetime,1,10) = '{date_formated}'\n",
    "#     \"\"\"\n",
    "# )\n",
    "# print(df_stop_LA.shape[0])\n",
    "# df_stop_LA.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "id": "4626a738-2a87-4b12-958c-a411cd8b7a04",
   "metadata": {
    "collapsed": true,
    "jupyter": {
     "outputs_hidden": true
    },
    "tags": []
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "264083\n",
      "CPU times: user 6.55 s, sys: 285 ms, total: 6.83 s\n",
      "Wall time: 43.6 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>admin1_id</th>\n",
       "      <th>admin2_id</th>\n",
       "      <th>brand_id</th>\n",
       "      <th>country_code</th>\n",
       "      <th>cuebiq_id</th>\n",
       "      <th>dwell_time_minutes</th>\n",
       "      <th>event_date</th>\n",
       "      <th>geohash</th>\n",
       "      <th>geoset_id</th>\n",
       "      <th>lat</th>\n",
       "      <th>...</th>\n",
       "      <th>os_name</th>\n",
       "      <th>place_id</th>\n",
       "      <th>place_version</th>\n",
       "      <th>zipcode_id</th>\n",
       "      <th>zoned_datetime</th>\n",
       "      <th>processing_date</th>\n",
       "      <th>provider_id</th>\n",
       "      <th>hour</th>\n",
       "      <th>geography_id</th>\n",
       "      <th>geometry_wkt</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>US.CA</td>\n",
       "      <td>US.CA.037</td>\n",
       "      <td>1704</td>\n",
       "      <td>US</td>\n",
       "      <td>4079472660</td>\n",
       "      <td>5.250000</td>\n",
       "      <td>20220310</td>\n",
       "      <td>9qh09n2rc</td>\n",
       "      <td>9478</td>\n",
       "      <td>33.873562</td>\n",
       "      <td>...</td>\n",
       "      <td>IOS</td>\n",
       "      <td>27822215</td>\n",
       "      <td>1646828334131</td>\n",
       "      <td>US.90701</td>\n",
       "      <td>2022-03-10T14:04:59-08:00</td>\n",
       "      <td>20220311</td>\n",
       "      <td>190199</td>\n",
       "      <td>14</td>\n",
       "      <td>US.CA.037.554801.2</td>\n",
       "      <td>POLYGON ((-118.080043 33.873053999999996, -118.08006499999999 33.875319, -118.079633 33.875325, -118.07964899999999 33.876462, -118.082302 33.876456, -118.08234999999999 33.880365999999995, -118.08238399999999 33.880365999999995, -118.08780600000001 33.880338, -118.087777 33.87675, -118.091036 33.876512, -118.09099200000001 33.872980999999996, -118.082284 33.873013, -118.080043 33.873053999999996))</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>US.CA</td>\n",
       "      <td>US.CA.037</td>\n",
       "      <td>315</td>\n",
       "      <td>US</td>\n",
       "      <td>4433486738</td>\n",
       "      <td>7.400000</td>\n",
       "      <td>20220310</td>\n",
       "      <td>9q5dy0nj3</td>\n",
       "      <td>8015</td>\n",
       "      <td>34.234320</td>\n",
       "      <td>...</td>\n",
       "      <td>IOS</td>\n",
       "      <td>25759121</td>\n",
       "      <td>1646482823500</td>\n",
       "      <td>US.91324</td>\n",
       "      <td>2022-03-10T17:01:20-08:00</td>\n",
       "      <td>20220311</td>\n",
       "      <td>190199</td>\n",
       "      <td>17</td>\n",
       "      <td>US.CA.037.113301.2</td>\n",
       "      <td>POLYGON ((-118.553566 34.232338999999996, -118.55357599999999 34.250001, -118.56180300000001 34.249997, -118.56281400000002 34.248839, -118.56354799999998 34.247324, -118.564497 34.24274, -118.56445699999999 34.235533, -118.56399100000002 34.234860999999995, -118.56230700000002 34.234317, -118.56230700000002 34.234049999999996, -118.553566 34.232338999999996))</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>US.CA</td>\n",
       "      <td>US.CA.037</td>\n",
       "      <td>1580</td>\n",
       "      <td>US</td>\n",
       "      <td>5188616004</td>\n",
       "      <td>42.116667</td>\n",
       "      <td>20220310</td>\n",
       "      <td>9q5f2r8pc</td>\n",
       "      <td>9546</td>\n",
       "      <td>34.188079</td>\n",
       "      <td>...</td>\n",
       "      <td>ANDROID</td>\n",
       "      <td>27861995</td>\n",
       "      <td>1646828343574</td>\n",
       "      <td>US.91411</td>\n",
       "      <td>2022-03-10T15:34:15-08:00</td>\n",
       "      <td>20220311</td>\n",
       "      <td>190199</td>\n",
       "      <td>15</td>\n",
       "      <td>US.CA.037.127806.3</td>\n",
       "      <td>POLYGON ((-118.466183 34.190289, -118.466182 34.188475, -118.46618 34.18665, -118.46399700000002 34.186651, -118.46399800000002 34.188476, -118.457449 34.188662, -118.45745000000001 34.190294, -118.464 34.19029, -118.466183 34.190289))</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>US.CA</td>\n",
       "      <td>US.CA.037</td>\n",
       "      <td>629</td>\n",
       "      <td>US</td>\n",
       "      <td>5145469708</td>\n",
       "      <td>8.616667</td>\n",
       "      <td>20220310</td>\n",
       "      <td>9q5g0c36q</td>\n",
       "      <td>11132</td>\n",
       "      <td>34.284601</td>\n",
       "      <td>...</td>\n",
       "      <td>IOS</td>\n",
       "      <td>31106344</td>\n",
       "      <td>1646569145525</td>\n",
       "      <td>US.91340</td>\n",
       "      <td>2022-03-10T22:16:29-08:00</td>\n",
       "      <td>20220311</td>\n",
       "      <td>190199</td>\n",
       "      <td>22</td>\n",
       "      <td>US.CA.037.320300.2</td>\n",
       "      <td>POLYGON ((-118.43894399999998 34.282337, -118.440536 34.283833, -118.44344300000002 34.286578, -118.444265 34.287347, -118.448036 34.284586, -118.445562 34.282266, -118.44271600000002 34.279581, -118.44187300000002 34.278780999999995, -118.43924699999998 34.280677, -118.439495 34.280916999999995, -118.438839 34.281344, -118.43939600000002 34.282002, -118.43894399999998 34.282337))</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>US.CA</td>\n",
       "      <td>US.CA.037</td>\n",
       "      <td>84</td>\n",
       "      <td>US</td>\n",
       "      <td>4839293717</td>\n",
       "      <td>8.233333</td>\n",
       "      <td>20220310</td>\n",
       "      <td>9q5c4w7zj</td>\n",
       "      <td>7699</td>\n",
       "      <td>33.961351</td>\n",
       "      <td>...</td>\n",
       "      <td>IOS</td>\n",
       "      <td>25233326</td>\n",
       "      <td>1646400555575</td>\n",
       "      <td>US.90301</td>\n",
       "      <td>2022-03-10T17:23:18-08:00</td>\n",
       "      <td>20220311</td>\n",
       "      <td>190199</td>\n",
       "      <td>17</td>\n",
       "      <td>US.CA.037.601211.2</td>\n",
       "      <td>POLYGON ((-118.35328299999999 33.959477, -118.353303 33.961774, -118.35730900000001 33.961681999999996, -118.35733900000001 33.962931999999995, -118.361724 33.962866999999996, -118.361712 33.961669, -118.36165999999999 33.958777, -118.35724900000001 33.958833999999996, -118.35459800000001 33.959455999999996, -118.35328299999999 33.959477))</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 22 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "  admin1_id  admin2_id  brand_id country_code   cuebiq_id  dwell_time_minutes  \\\n",
       "0  US.CA     US.CA.037  1704      US           4079472660  5.250000             \n",
       "1  US.CA     US.CA.037  315       US           4433486738  7.400000             \n",
       "2  US.CA     US.CA.037  1580      US           5188616004  42.116667            \n",
       "3  US.CA     US.CA.037  629       US           5145469708  8.616667             \n",
       "4  US.CA     US.CA.037  84        US           4839293717  8.233333             \n",
       "\n",
       "   event_date    geohash  geoset_id        lat  ...  os_name  place_id  \\\n",
       "0  20220310    9qh09n2rc  9478       33.873562  ...  IOS      27822215   \n",
       "1  20220310    9q5dy0nj3  8015       34.234320  ...  IOS      25759121   \n",
       "2  20220310    9q5f2r8pc  9546       34.188079  ...  ANDROID  27861995   \n",
       "3  20220310    9q5g0c36q  11132      34.284601  ...  IOS      31106344   \n",
       "4  20220310    9q5c4w7zj  7699       33.961351  ...  IOS      25233326   \n",
       "\n",
       "   place_version  zipcode_id             zoned_datetime processing_date  \\\n",
       "0  1646828334131  US.90701    2022-03-10T14:04:59-08:00  20220311         \n",
       "1  1646482823500  US.91324    2022-03-10T17:01:20-08:00  20220311         \n",
       "2  1646828343574  US.91411    2022-03-10T15:34:15-08:00  20220311         \n",
       "3  1646569145525  US.91340    2022-03-10T22:16:29-08:00  20220311         \n",
       "4  1646400555575  US.90301    2022-03-10T17:23:18-08:00  20220311         \n",
       "\n",
       "  provider_id  hour        geography_id  \\\n",
       "0  190199      14    US.CA.037.554801.2   \n",
       "1  190199      17    US.CA.037.113301.2   \n",
       "2  190199      15    US.CA.037.127806.3   \n",
       "3  190199      22    US.CA.037.320300.2   \n",
       "4  190199      17    US.CA.037.601211.2   \n",
       "\n",
       "                                                                                                                                                                                                                                                                                                                                                                                                        geometry_wkt  \n",
       "0  POLYGON ((-118.080043 33.873053999999996, -118.08006499999999 33.875319, -118.079633 33.875325, -118.07964899999999 33.876462, -118.082302 33.876456, -118.08234999999999 33.880365999999995, -118.08238399999999 33.880365999999995, -118.08780600000001 33.880338, -118.087777 33.87675, -118.091036 33.876512, -118.09099200000001 33.872980999999996, -118.082284 33.873013, -118.080043 33.873053999999996))  \n",
       "1  POLYGON ((-118.553566 34.232338999999996, -118.55357599999999 34.250001, -118.56180300000001 34.249997, -118.56281400000002 34.248839, -118.56354799999998 34.247324, -118.564497 34.24274, -118.56445699999999 34.235533, -118.56399100000002 34.234860999999995, -118.56230700000002 34.234317, -118.56230700000002 34.234049999999996, -118.553566 34.232338999999996))                                         \n",
       "2  POLYGON ((-118.466183 34.190289, -118.466182 34.188475, -118.46618 34.18665, -118.46399700000002 34.186651, -118.46399800000002 34.188476, -118.457449 34.188662, -118.45745000000001 34.190294, -118.464 34.19029, -118.466183 34.190289))                                                                                                                                                                        \n",
       "3  POLYGON ((-118.43894399999998 34.282337, -118.440536 34.283833, -118.44344300000002 34.286578, -118.444265 34.287347, -118.448036 34.284586, -118.445562 34.282266, -118.44271600000002 34.279581, -118.44187300000002 34.278780999999995, -118.43924699999998 34.280677, -118.439495 34.280916999999995, -118.438839 34.281344, -118.43939600000002 34.282002, -118.43894399999998 34.282337))                    \n",
       "4  POLYGON ((-118.35328299999999 33.959477, -118.353303 33.961774, -118.35730900000001 33.961681999999996, -118.35733900000001 33.962931999999995, -118.361724 33.962866999999996, -118.361712 33.961669, -118.36165999999999 33.958777, -118.35724900000001 33.958833999999996, -118.35459800000001 33.959455999999996, -118.35328299999999 33.959477))                                                              \n",
       "\n",
       "[5 rows x 22 columns]"
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# %%time\n",
    "# df_visit_LA = sql_engine.read_sql(f\"\"\"\n",
    "#     with la_visit as (\n",
    "#         select\n",
    "#             *, \n",
    "#             cast(hour(from_iso8601_timestamp(zoned_datetime)) as int) as hour\n",
    "#         from {visit_table}\n",
    "#         where \n",
    "#             country_code = 'US'\n",
    "#             and provider_id = '190199'\n",
    "#             and processing_date between {date} and {date_plus}\n",
    "#             and event_date = {date}\n",
    "#             and admin2_id = 'US.CA.037'\n",
    "#         ),\n",
    "        \n",
    "#         la_cbg as (\n",
    "#         select\n",
    "#             geography_id, geometry_wkt\n",
    "#         from {geography_table}\n",
    "#         where\n",
    "#             country_code = 'US'\n",
    "#             and geography_type_code = 'admin4'\n",
    "#             and geography_id like 'US.CA.037%'     --- <<< filter geometries in LA\n",
    "#         )\n",
    "        \n",
    "#         select\n",
    "#         *\n",
    "#         from la_visit s\n",
    "#         inner join la_cbg c\n",
    "#         on st_contains(st_geometryfromtext(c.geometry_wkt), st_point(s.lng, s.lat))\n",
    "#         \"\"\"\n",
    "# )\n",
    "# print(df_visit_LA.shape[0])\n",
    "# df_visit_LA.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 46,
   "id": "b76606ab-1c60-45ee-836b-dc1d7d354a71",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "22982\n",
      "20211\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/tmp/ipykernel_107/315645920.py:3: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  df_visit_LA_hourly.rename(columns={'geography_id': 'block_group_id'}, inplace=True)\n",
      "/tmp/ipykernel_107/315645920.py:9: FutureWarning: The default value of numeric_only in DataFrameGroupBy.sum is deprecated. In a future version, numeric_only will default to False. Either specify numeric_only or select only columns which should be valid for the function.\n",
      "  df_visit_LA_hourly_race = df_visit_LA_hourly_race.groupby('block_group_id').sum().reset_index()\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>block_group_id</th>\n",
       "      <th>R_White</th>\n",
       "      <th>R_AfricanAmerican</th>\n",
       "      <th>R_AmericanIndianAlaska</th>\n",
       "      <th>R_Asian</th>\n",
       "      <th>R_Other</th>\n",
       "      <th>devices</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>US.CA.037.101300.2</td>\n",
       "      <td>8.780610</td>\n",
       "      <td>0.072423</td>\n",
       "      <td>0.011041</td>\n",
       "      <td>1.261158</td>\n",
       "      <td>1.874768</td>\n",
       "      <td>12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>US.CA.037.101300.3</td>\n",
       "      <td>7.932072</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.680997</td>\n",
       "      <td>0.386930</td>\n",
       "      <td>9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>US.CA.037.103102.5</td>\n",
       "      <td>5.599671</td>\n",
       "      <td>0.180411</td>\n",
       "      <td>0.002970</td>\n",
       "      <td>1.138109</td>\n",
       "      <td>2.078839</td>\n",
       "      <td>9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>US.CA.037.103200.2</td>\n",
       "      <td>7.672020</td>\n",
       "      <td>0.441198</td>\n",
       "      <td>0.081616</td>\n",
       "      <td>1.805510</td>\n",
       "      <td>1.999656</td>\n",
       "      <td>12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>US.CA.037.104203.1</td>\n",
       "      <td>8.164102</td>\n",
       "      <td>1.249074</td>\n",
       "      <td>0.033158</td>\n",
       "      <td>0.524419</td>\n",
       "      <td>6.029247</td>\n",
       "      <td>16</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        block_group_id   R_White  R_AfricanAmerican  R_AmericanIndianAlaska  \\\n",
       "2   US.CA.037.101300.2  8.780610  0.072423           0.011041                 \n",
       "3   US.CA.037.101300.3  7.932072  0.000000           0.000000                 \n",
       "9   US.CA.037.103102.5  5.599671  0.180411           0.002970                 \n",
       "10  US.CA.037.103200.2  7.672020  0.441198           0.081616                 \n",
       "17  US.CA.037.104203.1  8.164102  1.249074           0.033158                 \n",
       "\n",
       "     R_Asian   R_Other  devices  \n",
       "2   1.261158  1.874768  12       \n",
       "3   0.680997  0.386930  9        \n",
       "9   1.138109  2.078839  9        \n",
       "10  1.805510  1.999656  12       \n",
       "17  0.524419  6.029247  16       "
      ]
     },
     "execution_count": 46,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# df_visit_LA_hourly = df_visit_LA[df_visit_LA['hour']==15]\n",
    "# print(df_visit_LA_hourly.shape[0])\n",
    "# df_visit_LA_hourly.rename(columns={'geography_id': 'block_group_id'}, inplace=True)\n",
    "\n",
    "# df_visit_LA_hourly_race = pd.merge(df_visit_LA_hourly[['cuebiq_id','block_group_id']],df_home_loc_race,on='cuebiq_id')  \n",
    "# print(df_visit_LA_hourly_race.shape[0])\n",
    "# df_visit_LA_hourly_race = df_visit_LA_hourly_race.drop(['cuebiq_id'], inplace=False, axis=1)\n",
    "# df_visit_LA_hourly_race['devices'] = 1\n",
    "# df_visit_LA_hourly_race = df_visit_LA_hourly_race.groupby('block_group_id').sum().reset_index()\n",
    "# df_visit_LA_hourly_race = df_visit_LA_hourly_race[df_visit_LA_hourly_race['devices']>5]\n",
    "# df_visit_LA_hourly_race.head()\n",
    "# # df_visit_LA_hourly.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "id": "69c9f81d-bb55-4b23-b1ab-f4b98b570f3b",
   "metadata": {},
   "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>block_group_id</th>\n",
       "      <th>scaled_entropy</th>\n",
       "      <th>devices</th>\n",
       "      <th>geom</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>US.CA.037.101300.2</td>\n",
       "      <td>0.002965</td>\n",
       "      <td>12</td>\n",
       "      <td>POLYGON ((-118.272473 34.232527, -118.266803 34.231235, -118.266733 34.240795999999996, -118.269008 34.242208999999995, -118.274034 34.245334, -118.276103 34.246483999999995, -118.276295 34.245152, -118.27395599999998 34.242992, -118.273341 34.240743, -118.27047400000001 34.236937, -118.270361 34.233942, -118.27068500000001 34.232994999999995, -118.27120200000002 34.233243, -118.271936 34.233067, -118.272473 34.232527))</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       block_group_id  scaled_entropy  devices  \\\n",
       "0  US.CA.037.101300.2  0.002965        12        \n",
       "\n",
       "                                                                                                                                                                                                                                                                                                                                                                                                                                      geom  \n",
       "0  POLYGON ((-118.272473 34.232527, -118.266803 34.231235, -118.266733 34.240795999999996, -118.269008 34.242208999999995, -118.274034 34.245334, -118.276103 34.246483999999995, -118.276295 34.245152, -118.27395599999998 34.242992, -118.273341 34.240743, -118.27047400000001 34.236937, -118.270361 34.233942, -118.27068500000001 34.232994999999995, -118.27120200000002 34.233243, -118.271936 34.233067, -118.272473 34.232527))  "
      ]
     },
     "execution_count": 44,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# df_visit_LA_hourly_race['R_White'] = df_visit_LA_hourly_race['R_White']/df_visit_LA_hourly_race['devices']\n",
    "# df_visit_LA_hourly_race['R_AfricanAmerican'] = df_visit_LA_hourly_race['R_AfricanAmerican']/df_visit_LA_hourly_race['devices']\n",
    "# df_visit_LA_hourly_race['R_AmericanIndianAlaska'] = df_visit_LA_hourly_race['R_AmericanIndianAlaska']/df_visit_LA_hourly_race['devices']\n",
    "# df_visit_LA_hourly_race['R_Asian'] = df_visit_LA_hourly_race['R_Asian']/df_visit_LA_hourly_race['devices']\n",
    "# df_visit_LA_hourly_race['R_Other'] = df_visit_LA_hourly_race['R_Other']/df_visit_LA_hourly_race['devices']\n",
    "# # df_visit_LA_hourly_race.head()\n",
    "\n",
    "# df_la_visit_loc_entropy = df_visit_LA_hourly_race.apply(lambda x:compute_scaled_entropy(x,'R_White','R_AfricanAmerican','R_AmericanIndianAlaska','R_Asian','R_Other'), axis=1)\n",
    "# df_la_visit_loc_entropy = df_la_visit_loc_entropy[df_la_visit_loc_entropy['scaled_entropy'].notna()]\n",
    "# # df_la_visit_loc_entropy\n",
    "\n",
    "# df_la_visit_loc_entropy_shp = pd.merge(df_la_visit_loc_entropy[['block_group_id','scaled_entropy','devices']],cbg_geom[['block_group_id','geom']],on='block_group_id')  \n",
    "# df_la_visit_loc_entropy_shp = df_la_visit_loc_entropy_shp[df_la_visit_loc_entropy_shp.scaled_entropy>=0]\n",
    "# df_la_visit_loc_entropy_shp.head(1)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "id": "e906e2f1-86d1-4338-bb36-3a2c81c31b5f",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "User Guide: https://docs.kepler.gl/docs/keplergl-jupyter\n",
      "CPU times: user 28.4 ms, sys: 94 µs, total: 28.5 ms\n",
      "Wall time: 26.6 ms\n"
     ]
    },
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "8cdf72859a484ba99b3faaf7ddc43476",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "KeplerGl(config={'config': {'mapState': {'bearing': 0, 'dragRotate': False, 'isSplit': False, 'latitude': 33.9…"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "# %%time\n",
    "# df_la_visit_loc_entropy_shp['colorscale'] = df_la_visit_loc_entropy_shp['scaled_entropy']\n",
    "# k4 = KeplerGl(data={'h':df_la_visit_loc_entropy_shp}, height=700)\n",
    "# with open('config/home_count_map.yml', 'r', encoding='utf-8') as config_file:\n",
    "#     k4.config = yaml.load(config_file, yaml.FullLoader)\n",
    "# k4 "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "ac9e308d-b760-4b53-90ea-2e7681e6ec31",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "67933822-f0d8-4957-9b05-26683c8763b0",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "5ec73f5a-1340-4b0f-86f5-53c4ff9948c1",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "582b4ec6-032e-42e5-9a6b-787314e26e44",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.11.8"
  },
  "widgets": {
   "application/vnd.jupyter.widget-state+json": {
    "state": {},
    "version_major": 2,
    "version_minor": 0
   }
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}