02 2021census_home based racial composition.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "a8ca8a75-8b02-40e9-81d9-4fac4eb4c1e6",
   "metadata": {},
   "source": [
    "# This notebook is used to infer individual's race information based on their home locations\n",
    "I tried to update the analysis using the 2021 census (most recent data as of Oct 2023). However, according to the code block under \"individual home location based race composition\", only 18717 records can match with the home CBG data provided by Spectus. This is because different years of census use different CBG divisions. So there should be some disparities between the 2018 (Spectus) and 2021 (most recent) census. The correlation between the number of devices and the population is only 0.535. I decided not to proceed as all datasets provided by Spectus (home CBGs, ) are based on the 2018 CBG division."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "70ccc8bd",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "\n",
    "\n",
    "pd.set_option('display.max_colwidth', 0)\n",
    "pd.set_option('display.max_rows', 500)\n",
    "pd.set_option('display.max_columns', 500)\n",
    "pd.set_option('display.width', 1000)\n",
    "import warnings\n",
    "warnings.filterwarnings('ignore')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 62,
   "id": "097031f6-0a46-44ec-92b1-2167f133a778",
   "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 yaml\n",
    "import numpy as np\n",
    "import os\n",
    "from pyhive import trino\n",
    "import pydeck as pdk\n",
    "from typing import List\n",
    "import copy\n",
    "import itertools\n",
    "from pyquadkey2 import quadkey\n",
    "from pyquadkey2.quadkey import TileAnchor, QuadKey\n",
    "from datetime import datetime, timedelta\n",
    "import math\n",
    "import pickle\n",
    "\n",
    "os.environ['MAPBOX_API_KEY'] = \"INSERT YOUR MAPBOX TOKEN HERE\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 63,
   "id": "344bc5df-1b9c-4d55-8fbb-48b6e978f03a",
   "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": 64,
   "id": "3b3251e8-720f-4ee3-b6f6-9f89c6bd054c",
   "metadata": {},
   "outputs": [],
   "source": [
    "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",
    "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": 91,
   "id": "efe9b0ef-cb2b-43b5-a929-e430d872ea2c",
   "metadata": {},
   "outputs": [],
   "source": [
    "# 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 # Thursday\n",
    "date_formated = datetime.strptime(str(date), \"%Y%m%d\").strftime(\"%Y-%m-%d\")\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": 2,
   "id": "79b9c1f2",
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/var/folders/ws/4kvjv8pj0xs3vgrbl186twtc0000gn/T/ipykernel_99346/4141035720.py:1: DtypeWarning: Columns (2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32,33,34,35,36,37,38,39,40,41,42,43,44,45,46,47,48,49,50,51,52,53,54,55,56,57,58,59,60,61,62,63,64,65,66,67,68,69,70,71,72,73,74,75,76,77,78,79,80,81,82,83,84,85) have mixed types. Specify dtype option on import or set low_memory=False.\n",
      "  census_data = pd.read_csv(\"data/ACSDT5Y2021.B03002-Data.csv\",header=0).tail(-1)\n"
     ]
    }
   ],
   "source": [
    "census_data = pd.read_csv(\"data/ACSDT5Y2021.B03002-Data.csv\",header=0).tail(-1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "4c2c5c9c",
   "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>GEO_ID</th>\n",
       "      <th>NAME</th>\n",
       "      <th>B03002_001E</th>\n",
       "      <th>B03002_001M</th>\n",
       "      <th>B03002_001MA</th>\n",
       "      <th>B03002_001EA</th>\n",
       "      <th>B03002_002E</th>\n",
       "      <th>B03002_002EA</th>\n",
       "      <th>B03002_002M</th>\n",
       "      <th>B03002_002MA</th>\n",
       "      <th>B03002_003E</th>\n",
       "      <th>B03002_003M</th>\n",
       "      <th>B03002_003MA</th>\n",
       "      <th>B03002_003EA</th>\n",
       "      <th>B03002_004E</th>\n",
       "      <th>B03002_004M</th>\n",
       "      <th>B03002_004MA</th>\n",
       "      <th>B03002_004EA</th>\n",
       "      <th>B03002_005E</th>\n",
       "      <th>B03002_005M</th>\n",
       "      <th>B03002_005MA</th>\n",
       "      <th>B03002_005EA</th>\n",
       "      <th>B03002_006E</th>\n",
       "      <th>B03002_006M</th>\n",
       "      <th>B03002_006MA</th>\n",
       "      <th>B03002_006EA</th>\n",
       "      <th>B03002_007E</th>\n",
       "      <th>B03002_007M</th>\n",
       "      <th>B03002_007MA</th>\n",
       "      <th>B03002_007EA</th>\n",
       "      <th>B03002_008E</th>\n",
       "      <th>B03002_008M</th>\n",
       "      <th>B03002_008MA</th>\n",
       "      <th>B03002_008EA</th>\n",
       "      <th>B03002_009E</th>\n",
       "      <th>B03002_009EA</th>\n",
       "      <th>B03002_009M</th>\n",
       "      <th>B03002_009MA</th>\n",
       "      <th>B03002_010E</th>\n",
       "      <th>B03002_010M</th>\n",
       "      <th>B03002_010MA</th>\n",
       "      <th>B03002_010EA</th>\n",
       "      <th>B03002_011E</th>\n",
       "      <th>B03002_011M</th>\n",
       "      <th>B03002_011MA</th>\n",
       "      <th>B03002_011EA</th>\n",
       "      <th>B03002_012E</th>\n",
       "      <th>B03002_012M</th>\n",
       "      <th>B03002_012MA</th>\n",
       "      <th>B03002_012EA</th>\n",
       "      <th>B03002_013E</th>\n",
       "      <th>B03002_013M</th>\n",
       "      <th>B03002_013MA</th>\n",
       "      <th>B03002_013EA</th>\n",
       "      <th>B03002_014E</th>\n",
       "      <th>B03002_014M</th>\n",
       "      <th>B03002_014MA</th>\n",
       "      <th>B03002_014EA</th>\n",
       "      <th>B03002_015E</th>\n",
       "      <th>B03002_015EA</th>\n",
       "      <th>B03002_015M</th>\n",
       "      <th>B03002_015MA</th>\n",
       "      <th>B03002_016E</th>\n",
       "      <th>B03002_016M</th>\n",
       "      <th>B03002_016MA</th>\n",
       "      <th>B03002_016EA</th>\n",
       "      <th>B03002_017E</th>\n",
       "      <th>B03002_017EA</th>\n",
       "      <th>B03002_017M</th>\n",
       "      <th>B03002_017MA</th>\n",
       "      <th>B03002_018E</th>\n",
       "      <th>B03002_018EA</th>\n",
       "      <th>B03002_018M</th>\n",
       "      <th>B03002_018MA</th>\n",
       "      <th>B03002_019E</th>\n",
       "      <th>B03002_019M</th>\n",
       "      <th>B03002_019MA</th>\n",
       "      <th>B03002_019EA</th>\n",
       "      <th>B03002_020E</th>\n",
       "      <th>B03002_020M</th>\n",
       "      <th>B03002_020MA</th>\n",
       "      <th>B03002_020EA</th>\n",
       "      <th>B03002_021E</th>\n",
       "      <th>B03002_021M</th>\n",
       "      <th>B03002_021MA</th>\n",
       "      <th>B03002_021EA</th>\n",
       "      <th>Unnamed: 86</th>\n",
       "      <th>population</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>R_HispanicLatinx</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>US.CA.001.400100.1</td>\n",
       "      <td>1500000US060014001001</td>\n",
       "      <td>Block Group 1, Census Tract 4001, Alameda County, California</td>\n",
       "      <td>1963</td>\n",
       "      <td>457</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1847</td>\n",
       "      <td>NaN</td>\n",
       "      <td>441</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1429</td>\n",
       "      <td>434</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>44</td>\n",
       "      <td>41</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>13</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>257</td>\n",
       "      <td>100</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>13</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>17</td>\n",
       "      <td>29</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>100</td>\n",
       "      <td>NaN</td>\n",
       "      <td>92</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>13</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>100</td>\n",
       "      <td>92</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>116</td>\n",
       "      <td>83</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>95</td>\n",
       "      <td>81</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>13</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>13</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>13</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>13</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>13</td>\n",
       "      <td>NaN</td>\n",
       "      <td>21</td>\n",
       "      <td>29</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>21</td>\n",
       "      <td>29</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>13</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1963</td>\n",
       "      <td>0.727967</td>\n",
       "      <td>0.022415</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.130922</td>\n",
       "      <td>0.110545</td>\n",
       "      <td>0.059093</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>US.CA.001.400100.2</td>\n",
       "      <td>1500000US060014001002</td>\n",
       "      <td>Block Group 2, Census Tract 4001, Alameda County, California</td>\n",
       "      <td>1361</td>\n",
       "      <td>273</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1227</td>\n",
       "      <td>NaN</td>\n",
       "      <td>249</td>\n",
       "      <td>NaN</td>\n",
       "      <td>904</td>\n",
       "      <td>260</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>60</td>\n",
       "      <td>68</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>13</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>221</td>\n",
       "      <td>85</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>13</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>13</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>42</td>\n",
       "      <td>NaN</td>\n",
       "      <td>43</td>\n",
       "      <td>NaN</td>\n",
       "      <td>16</td>\n",
       "      <td>26</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>26</td>\n",
       "      <td>33</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>134</td>\n",
       "      <td>99</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>84</td>\n",
       "      <td>79</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>13</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>13</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>13</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>13</td>\n",
       "      <td>NaN</td>\n",
       "      <td>38</td>\n",
       "      <td>NaN</td>\n",
       "      <td>48</td>\n",
       "      <td>NaN</td>\n",
       "      <td>12</td>\n",
       "      <td>19</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>12</td>\n",
       "      <td>19</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>13</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1361</td>\n",
       "      <td>0.664217</td>\n",
       "      <td>0.044085</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.162381</td>\n",
       "      <td>0.061719</td>\n",
       "      <td>0.098457</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       block_group_id                 GEO_ID                                                          NAME B03002_001E B03002_001M B03002_001MA B03002_001EA B03002_002E B03002_002EA B03002_002M B03002_002MA B03002_003E B03002_003M B03002_003MA B03002_003EA B03002_004E B03002_004M B03002_004MA B03002_004EA B03002_005E B03002_005M B03002_005MA B03002_005EA B03002_006E B03002_006M B03002_006MA B03002_006EA B03002_007E B03002_007M B03002_007MA B03002_007EA B03002_008E B03002_008M B03002_008MA B03002_008EA B03002_009E B03002_009EA B03002_009M B03002_009MA B03002_010E B03002_010M B03002_010MA B03002_010EA B03002_011E B03002_011M B03002_011MA B03002_011EA B03002_012E B03002_012M B03002_012MA B03002_012EA B03002_013E B03002_013M B03002_013MA B03002_013EA B03002_014E B03002_014M B03002_014MA B03002_014EA B03002_015E B03002_015EA B03002_015M B03002_015MA B03002_016E B03002_016M B03002_016MA B03002_016EA B03002_017E B03002_017EA B03002_017M B03002_017MA B03002_018E B03002_018EA B03002_018M  \\\n",
       "1  US.CA.001.400100.1  1500000US060014001001  Block Group 1, Census Tract 4001, Alameda County, California  1963        457         NaN          NaN          1847        NaN          441         NaN          1429        434         NaN          NaN          44          41          NaN          NaN          0           13          NaN          NaN          257         100         NaN          NaN          0           13          NaN          NaN          17          29          NaN          NaN          100         NaN          92          NaN          0           13          NaN          NaN          100         92          NaN          NaN          116         83          NaN          NaN          95          81          NaN          NaN          0           13          NaN          NaN          0           NaN          13          NaN          0           13          NaN          NaN          0           NaN          13          NaN          0           NaN          13           \n",
       "2  US.CA.001.400100.2  1500000US060014001002  Block Group 2, Census Tract 4001, Alameda County, California  1361        273         NaN          NaN          1227        NaN          249         NaN          904         260         NaN          NaN          60          68          NaN          NaN          0           13          NaN          NaN          221         85          NaN          NaN          0           13          NaN          NaN          0           13          NaN          NaN          42          NaN          43          NaN          16          26          NaN          NaN          26          33          NaN          NaN          134         99          NaN          NaN          84          79          NaN          NaN          0           13          NaN          NaN          0           NaN          13          NaN          0           13          NaN          NaN          0           NaN          13          NaN          38          NaN          48           \n",
       "\n",
       "  B03002_018MA B03002_019E B03002_019M B03002_019MA B03002_019EA B03002_020E B03002_020M B03002_020MA B03002_020EA B03002_021E B03002_021M B03002_021MA B03002_021EA  Unnamed: 86  population   R_White  R_AfricanAmerican  R_AmericanIndianAlaska   R_Asian   R_Other  R_HispanicLatinx  \n",
       "1  NaN          21          29          NaN          NaN          21          29          NaN          NaN          0           13          NaN          NaN         NaN           1963        0.727967  0.022415           0.0                     0.130922  0.110545  0.059093          \n",
       "2  NaN          12          19          NaN          NaN          12          19          NaN          NaN          0           13          NaN          NaN         NaN           1361        0.664217  0.044085           0.0                     0.162381  0.061719  0.098457          "
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "census_data.head(2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "id": "7933103f",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "white 0.41\n",
      "black 0.01\n",
      "asian 0.01\n",
      "native 0.02\n",
      "other alone 0.38\n",
      "two or more 0.18\n"
     ]
    }
   ],
   "source": [
    "# no need to run this block, just for fun\n",
    "test = census_data\n",
    "test['Hispanic_total'] = test['B03002_012E'].astype(int)\n",
    "test['Hispanic_white'] = test['B03002_013E'].astype(int)\n",
    "test['Hispanic_black'] = test['B03002_014E'].astype(int)\n",
    "test['Hispanic_native'] = test['B03002_015E'].astype(int)\n",
    "test['Hispanic_asian'] = test['B03002_016E'].astype(int) + test['B03002_017E'].astype(int)\n",
    "test['Hispanic_otherAlone'] = test['B03002_018E'].astype(int)\n",
    "test['Hispanic_twoOrMoreRaces'] = test['B03002_019E'].astype(int)\n",
    "test = test[['block_group_id','GEO_ID','Hispanic_total','Hispanic_white','Hispanic_black','Hispanic_native',\n",
    "            'Hispanic_asian','Hispanic_otherAlone','Hispanic_twoOrMoreRaces']]\n",
    "test['total'] = test['Hispanic_white']+test['Hispanic_black']+test['Hispanic_native']+test['Hispanic_asian']+test['Hispanic_otherAlone']+test['Hispanic_twoOrMoreRaces']\n",
    "# test['diff'] = test['total'] - test['Hispanic_total']\n",
    "# test[test['diff']!=0]\n",
    "total_hisp =test['Hispanic_total'].sum()\n",
    "hisp_white =test['Hispanic_white'].sum()\n",
    "hisp_black =test['Hispanic_black'].sum()\n",
    "hisp_native =test['Hispanic_native'].sum()\n",
    "hisp_asian =test['Hispanic_asian'].sum()\n",
    "hisp_otheralone =test['Hispanic_otherAlone'].sum()\n",
    "hisp_twoormore =test['Hispanic_twoOrMoreRaces'].sum()\n",
    "# test\n",
    "print('white', round(hisp_white/total_hisp,2))\n",
    "print('black', round(hisp_black/total_hisp,2))\n",
    "print('asian', round(hisp_asian/total_hisp,2))\n",
    "print('native', round(hisp_native/total_hisp,2))\n",
    "print('other alone', round(hisp_otheralone/total_hisp,2))\n",
    "print('two or more', round(hisp_twoormore/total_hisp,2))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 92,
   "id": "0b94bc04-4563-485c-9ddc-9b39e989e706",
   "metadata": {},
   "outputs": [],
   "source": [
    "# %%time\n",
    "# # census_taxonomy_table = \"cuebiq.paas_public_data.census_taxonomy\"#\n",
    "# # sql_engine.read_sql(f\"desc {census_taxonomy_table}\")\n",
    "# # show code book for race\n",
    "# df_census_taxonomy = sql_engine.read_sql(f\"select * from {census_taxonomy_table}\")\n",
    "# # df_census_taxonomy"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "id": "46685d81-e7fb-4002-af2b-a6fada2d81a2",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "total number of CBG: 25607\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>GEO_ID</th>\n",
       "      <th>NAME</th>\n",
       "      <th>B03002_001E</th>\n",
       "      <th>B03002_001M</th>\n",
       "      <th>B03002_001MA</th>\n",
       "      <th>B03002_001EA</th>\n",
       "      <th>B03002_002E</th>\n",
       "      <th>B03002_002EA</th>\n",
       "      <th>B03002_002M</th>\n",
       "      <th>B03002_002MA</th>\n",
       "      <th>B03002_003E</th>\n",
       "      <th>B03002_003M</th>\n",
       "      <th>B03002_003MA</th>\n",
       "      <th>B03002_003EA</th>\n",
       "      <th>B03002_004E</th>\n",
       "      <th>B03002_004M</th>\n",
       "      <th>B03002_004MA</th>\n",
       "      <th>B03002_004EA</th>\n",
       "      <th>B03002_005E</th>\n",
       "      <th>B03002_005M</th>\n",
       "      <th>B03002_005MA</th>\n",
       "      <th>B03002_005EA</th>\n",
       "      <th>B03002_006E</th>\n",
       "      <th>B03002_006M</th>\n",
       "      <th>B03002_006MA</th>\n",
       "      <th>B03002_006EA</th>\n",
       "      <th>B03002_007E</th>\n",
       "      <th>B03002_007M</th>\n",
       "      <th>B03002_007MA</th>\n",
       "      <th>B03002_007EA</th>\n",
       "      <th>B03002_008E</th>\n",
       "      <th>B03002_008M</th>\n",
       "      <th>B03002_008MA</th>\n",
       "      <th>B03002_008EA</th>\n",
       "      <th>B03002_009E</th>\n",
       "      <th>B03002_009EA</th>\n",
       "      <th>B03002_009M</th>\n",
       "      <th>B03002_009MA</th>\n",
       "      <th>B03002_010E</th>\n",
       "      <th>B03002_010M</th>\n",
       "      <th>B03002_010MA</th>\n",
       "      <th>B03002_010EA</th>\n",
       "      <th>B03002_011E</th>\n",
       "      <th>B03002_011M</th>\n",
       "      <th>B03002_011MA</th>\n",
       "      <th>B03002_011EA</th>\n",
       "      <th>B03002_012E</th>\n",
       "      <th>B03002_012M</th>\n",
       "      <th>B03002_012MA</th>\n",
       "      <th>B03002_012EA</th>\n",
       "      <th>B03002_013E</th>\n",
       "      <th>B03002_013M</th>\n",
       "      <th>B03002_013MA</th>\n",
       "      <th>B03002_013EA</th>\n",
       "      <th>B03002_014E</th>\n",
       "      <th>B03002_014M</th>\n",
       "      <th>B03002_014MA</th>\n",
       "      <th>B03002_014EA</th>\n",
       "      <th>B03002_015E</th>\n",
       "      <th>B03002_015EA</th>\n",
       "      <th>B03002_015M</th>\n",
       "      <th>B03002_015MA</th>\n",
       "      <th>B03002_016E</th>\n",
       "      <th>B03002_016M</th>\n",
       "      <th>B03002_016MA</th>\n",
       "      <th>B03002_016EA</th>\n",
       "      <th>B03002_017E</th>\n",
       "      <th>B03002_017EA</th>\n",
       "      <th>B03002_017M</th>\n",
       "      <th>B03002_017MA</th>\n",
       "      <th>B03002_018E</th>\n",
       "      <th>B03002_018EA</th>\n",
       "      <th>B03002_018M</th>\n",
       "      <th>B03002_018MA</th>\n",
       "      <th>B03002_019E</th>\n",
       "      <th>B03002_019M</th>\n",
       "      <th>B03002_019MA</th>\n",
       "      <th>B03002_019EA</th>\n",
       "      <th>B03002_020E</th>\n",
       "      <th>B03002_020M</th>\n",
       "      <th>B03002_020MA</th>\n",
       "      <th>B03002_020EA</th>\n",
       "      <th>B03002_021E</th>\n",
       "      <th>B03002_021M</th>\n",
       "      <th>B03002_021MA</th>\n",
       "      <th>B03002_021EA</th>\n",
       "      <th>Unnamed: 86</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Geography</td>\n",
       "      <td>Geographic Area Name</td>\n",
       "      <td>Estimate!!Total:</td>\n",
       "      <td>Margin of Error!!Total:</td>\n",
       "      <td>Annotation of Margin of Error!!Total:</td>\n",
       "      <td>Annotation of Estimate!!Total:</td>\n",
       "      <td>Estimate!!Total:!!Not Hispanic or Latino:</td>\n",
       "      <td>Annotation of Estimate!!Total:!!Not Hispanic or Latino:</td>\n",
       "      <td>Margin of Error!!Total:!!Not Hispanic or Latino:</td>\n",
       "      <td>Annotation of Margin of Error!!Total:!!Not Hispanic or Latino:</td>\n",
       "      <td>Estimate!!Total:!!Not Hispanic or Latino:!!White alone</td>\n",
       "      <td>Margin of Error!!Total:!!Not Hispanic or Latino:!!White alone</td>\n",
       "      <td>Annotation of Margin of Error!!Total:!!Not Hispanic or Latino:!!White alone</td>\n",
       "      <td>Annotation of Estimate!!Total:!!Not Hispanic or Latino:!!White alone</td>\n",
       "      <td>Estimate!!Total:!!Not Hispanic or Latino:!!Black or African American alone</td>\n",
       "      <td>Margin of Error!!Total:!!Not Hispanic or Latino:!!Black or African American alone</td>\n",
       "      <td>Annotation of Margin of Error!!Total:!!Not Hispanic or Latino:!!Black or African American alone</td>\n",
       "      <td>Annotation of Estimate!!Total:!!Not Hispanic or Latino:!!Black or African American alone</td>\n",
       "      <td>Estimate!!Total:!!Not Hispanic or Latino:!!American Indian and Alaska Native alone</td>\n",
       "      <td>Margin of Error!!Total:!!Not Hispanic or Latino:!!American Indian and Alaska Native alone</td>\n",
       "      <td>Annotation of Margin of Error!!Total:!!Not Hispanic or Latino:!!American Indian and Alaska Native alone</td>\n",
       "      <td>Annotation of Estimate!!Total:!!Not Hispanic or Latino:!!American Indian and Alaska Native alone</td>\n",
       "      <td>Estimate!!Total:!!Not Hispanic or Latino:!!Asian alone</td>\n",
       "      <td>Margin of Error!!Total:!!Not Hispanic or Latino:!!Asian alone</td>\n",
       "      <td>Annotation of Margin of Error!!Total:!!Not Hispanic or Latino:!!Asian alone</td>\n",
       "      <td>Annotation of Estimate!!Total:!!Not Hispanic or Latino:!!Asian alone</td>\n",
       "      <td>Estimate!!Total:!!Not Hispanic or Latino:!!Native Hawaiian and Other Pacific Islander alone</td>\n",
       "      <td>Margin of Error!!Total:!!Not Hispanic or Latino:!!Native Hawaiian and Other Pacific Islander alone</td>\n",
       "      <td>Annotation of Margin of Error!!Total:!!Not Hispanic or Latino:!!Native Hawaiian and Other Pacific Islander alone</td>\n",
       "      <td>Annotation of Estimate!!Total:!!Not Hispanic or Latino:!!Native Hawaiian and Other Pacific Islander alone</td>\n",
       "      <td>Estimate!!Total:!!Not Hispanic or Latino:!!Some other race alone</td>\n",
       "      <td>Margin of Error!!Total:!!Not Hispanic or Latino:!!Some other race alone</td>\n",
       "      <td>Annotation of Margin of Error!!Total:!!Not Hispanic or Latino:!!Some other race alone</td>\n",
       "      <td>Annotation of Estimate!!Total:!!Not Hispanic or Latino:!!Some other race alone</td>\n",
       "      <td>Estimate!!Total:!!Not Hispanic or Latino:!!Two or more races:</td>\n",
       "      <td>Annotation of Estimate!!Total:!!Not Hispanic or Latino:!!Two or more races:</td>\n",
       "      <td>Margin of Error!!Total:!!Not Hispanic or Latino:!!Two or more races:</td>\n",
       "      <td>Annotation of Margin of Error!!Total:!!Not Hispanic or Latino:!!Two or more races:</td>\n",
       "      <td>Estimate!!Total:!!Not Hispanic or Latino:!!Two or more races:!!Two races including Some other race</td>\n",
       "      <td>Margin of Error!!Total:!!Not Hispanic or Latino:!!Two or more races:!!Two races including Some other race</td>\n",
       "      <td>Annotation of Margin of Error!!Total:!!Not Hispanic or Latino:!!Two or more races:!!Two races including Some other race</td>\n",
       "      <td>Annotation of Estimate!!Total:!!Not Hispanic or Latino:!!Two or more races:!!Two races including Some other race</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",
       "      <td>Margin of Error!!Total:!!Not Hispanic or Latino:!!Two or more races:!!Two races excluding Some other race, and three or more races</td>\n",
       "      <td>Annotation of Margin of Error!!Total:!!Not Hispanic or Latino:!!Two or more races:!!Two races excluding Some other race, and three or more races</td>\n",
       "      <td>Annotation of Estimate!!Total:!!Not Hispanic or Latino:!!Two or more races:!!Two races excluding Some other race, and three or more races</td>\n",
       "      <td>Estimate!!Total:!!Hispanic or Latino:</td>\n",
       "      <td>Margin of Error!!Total:!!Hispanic or Latino:</td>\n",
       "      <td>Annotation of Margin of Error!!Total:!!Hispanic or Latino:</td>\n",
       "      <td>Annotation of Estimate!!Total:!!Hispanic or Latino:</td>\n",
       "      <td>Estimate!!Total:!!Hispanic or Latino:!!White alone</td>\n",
       "      <td>Margin of Error!!Total:!!Hispanic or Latino:!!White alone</td>\n",
       "      <td>Annotation of Margin of Error!!Total:!!Hispanic or Latino:!!White alone</td>\n",
       "      <td>Annotation of Estimate!!Total:!!Hispanic or Latino:!!White alone</td>\n",
       "      <td>Estimate!!Total:!!Hispanic or Latino:!!Black or African American alone</td>\n",
       "      <td>Margin of Error!!Total:!!Hispanic or Latino:!!Black or African American alone</td>\n",
       "      <td>Annotation of Margin of Error!!Total:!!Hispanic or Latino:!!Black or African American alone</td>\n",
       "      <td>Annotation of Estimate!!Total:!!Hispanic or Latino:!!Black or African American alone</td>\n",
       "      <td>Estimate!!Total:!!Hispanic or Latino:!!American Indian and Alaska Native alone</td>\n",
       "      <td>Annotation of Estimate!!Total:!!Hispanic or Latino:!!American Indian and Alaska Native alone</td>\n",
       "      <td>Margin of Error!!Total:!!Hispanic or Latino:!!American Indian and Alaska Native alone</td>\n",
       "      <td>Annotation of Margin of Error!!Total:!!Hispanic or Latino:!!American Indian and Alaska Native alone</td>\n",
       "      <td>Estimate!!Total:!!Hispanic or Latino:!!Asian alone</td>\n",
       "      <td>Margin of Error!!Total:!!Hispanic or Latino:!!Asian alone</td>\n",
       "      <td>Annotation of Margin of Error!!Total:!!Hispanic or Latino:!!Asian alone</td>\n",
       "      <td>Annotation of Estimate!!Total:!!Hispanic or Latino:!!Asian alone</td>\n",
       "      <td>Estimate!!Total:!!Hispanic or Latino:!!Native Hawaiian and Other Pacific Islander alone</td>\n",
       "      <td>Annotation of Estimate!!Total:!!Hispanic or Latino:!!Native Hawaiian and Other Pacific Islander alone</td>\n",
       "      <td>Margin of Error!!Total:!!Hispanic or Latino:!!Native Hawaiian and Other Pacific Islander alone</td>\n",
       "      <td>Annotation of Margin of Error!!Total:!!Hispanic or Latino:!!Native Hawaiian and Other Pacific Islander alone</td>\n",
       "      <td>Estimate!!Total:!!Hispanic or Latino:!!Some other race alone</td>\n",
       "      <td>Annotation of Estimate!!Total:!!Hispanic or Latino:!!Some other race alone</td>\n",
       "      <td>Margin of Error!!Total:!!Hispanic or Latino:!!Some other race alone</td>\n",
       "      <td>Annotation of Margin of Error!!Total:!!Hispanic or Latino:!!Some other race alone</td>\n",
       "      <td>Estimate!!Total:!!Hispanic or Latino:!!Two or more races:</td>\n",
       "      <td>Margin of Error!!Total:!!Hispanic or Latino:!!Two or more races:</td>\n",
       "      <td>Annotation of Margin of Error!!Total:!!Hispanic or Latino:!!Two or more races:</td>\n",
       "      <td>Annotation of Estimate!!Total:!!Hispanic or Latino:!!Two or more races:</td>\n",
       "      <td>Estimate!!Total:!!Hispanic or Latino:!!Two or more races:!!Two races including Some other race</td>\n",
       "      <td>Margin of Error!!Total:!!Hispanic or Latino:!!Two or more races:!!Two races including Some other race</td>\n",
       "      <td>Annotation of Margin of Error!!Total:!!Hispanic or Latino:!!Two or more races:!!Two races including Some other race</td>\n",
       "      <td>Annotation of Estimate!!Total:!!Hispanic or Latino:!!Two or more races:!!Two races including Some other race</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",
       "      <td>Margin of Error!!Total:!!Hispanic or Latino:!!Two or more races:!!Two races excluding Some other race, and three or more races</td>\n",
       "      <td>Annotation of Margin of Error!!Total:!!Hispanic or Latino:!!Two or more races:!!Two races excluding Some other race, and three or more races</td>\n",
       "      <td>Annotation of Estimate!!Total:!!Hispanic or Latino:!!Two or more races:!!Two races excluding Some other race, and three or more races</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1500000US060014001001</td>\n",
       "      <td>Block Group 1, Census Tract 4001, Alameda County, California</td>\n",
       "      <td>1963</td>\n",
       "      <td>457</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1847</td>\n",
       "      <td>NaN</td>\n",
       "      <td>441</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1429</td>\n",
       "      <td>434</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>44</td>\n",
       "      <td>41</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>13</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>257</td>\n",
       "      <td>100</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>13</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>17</td>\n",
       "      <td>29</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>100</td>\n",
       "      <td>NaN</td>\n",
       "      <td>92</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>13</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>100</td>\n",
       "      <td>92</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>116</td>\n",
       "      <td>83</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>95</td>\n",
       "      <td>81</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>13</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>13</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>13</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>13</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>13</td>\n",
       "      <td>NaN</td>\n",
       "      <td>21</td>\n",
       "      <td>29</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>21</td>\n",
       "      <td>29</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>13</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                  GEO_ID                                                          NAME       B03002_001E              B03002_001M                           B03002_001MA                    B03002_001EA                                B03002_002E                                             B03002_002EA                                       B03002_002M                                                    B03002_002MA                                             B03002_003E                                                    B03002_003M                                                                 B03002_003MA                                                          B03002_003EA                                                                 B03002_004E                                                                        B03002_004M                                                                                     B03002_004MA  \\\n",
       "0  Geography              Geographic Area Name                                          Estimate!!Total:  Margin of Error!!Total:  Annotation of Margin of Error!!Total:  Annotation of Estimate!!Total:  Estimate!!Total:!!Not Hispanic or Latino:  Annotation of Estimate!!Total:!!Not Hispanic or Latino:  Margin of Error!!Total:!!Not Hispanic or Latino:  Annotation of Margin of Error!!Total:!!Not Hispanic or Latino:  Estimate!!Total:!!Not Hispanic or Latino:!!White alone  Margin of Error!!Total:!!Not Hispanic or Latino:!!White alone  Annotation of Margin of Error!!Total:!!Not Hispanic or Latino:!!White alone  Annotation of Estimate!!Total:!!Not Hispanic or Latino:!!White alone  Estimate!!Total:!!Not Hispanic or Latino:!!Black or African American alone  Margin of Error!!Total:!!Not Hispanic or Latino:!!Black or African American alone  Annotation of Margin of Error!!Total:!!Not Hispanic or Latino:!!Black or African American alone   \n",
       "1  1500000US060014001001  Block Group 1, Census Tract 4001, Alameda County, California  1963              457                      NaN                                    NaN                             1847                                       NaN                                                      441                                               NaN                                                             1429                                                    434                                                            NaN                                                                          NaN                                                                   44                                                                          41                                                                                 NaN                                                                                               \n",
       "\n",
       "                                                                               B03002_004EA                                                                         B03002_005E                                                                                B03002_005M                                                                                             B03002_005MA                                                                                      B03002_005EA                                             B03002_006E                                                    B03002_006M                                                                 B03002_006MA                                                          B03002_006EA                                                                                  B03002_007E                                                                                         B03002_007M  \\\n",
       "0  Annotation of Estimate!!Total:!!Not Hispanic or Latino:!!Black or African American alone  Estimate!!Total:!!Not Hispanic or Latino:!!American Indian and Alaska Native alone  Margin of Error!!Total:!!Not Hispanic or Latino:!!American Indian and Alaska Native alone  Annotation of Margin of Error!!Total:!!Not Hispanic or Latino:!!American Indian and Alaska Native alone  Annotation of Estimate!!Total:!!Not Hispanic or Latino:!!American Indian and Alaska Native alone  Estimate!!Total:!!Not Hispanic or Latino:!!Asian alone  Margin of Error!!Total:!!Not Hispanic or Latino:!!Asian alone  Annotation of Margin of Error!!Total:!!Not Hispanic or Latino:!!Asian alone  Annotation of Estimate!!Total:!!Not Hispanic or Latino:!!Asian alone  Estimate!!Total:!!Not Hispanic or Latino:!!Native Hawaiian and Other Pacific Islander alone  Margin of Error!!Total:!!Not Hispanic or Latino:!!Native Hawaiian and Other Pacific Islander alone   \n",
       "1  NaN                                                                                       0                                                                                   13                                                                                         NaN                                                                                                      NaN                                                                                               257                                                     100                                                            NaN                                                                          NaN                                                                   0                                                                                            13                                                                                                   \n",
       "\n",
       "                                                                                                       B03002_007MA                                                                                               B03002_007EA                                                       B03002_008E                                                              B03002_008M                                                                           B03002_008MA                                                                    B03002_008EA                                                    B03002_009E                                                                 B03002_009EA                                                           B03002_009M                                                                        B03002_009MA                                                                                         B03002_010E  \\\n",
       "0  Annotation of Margin of Error!!Total:!!Not Hispanic or Latino:!!Native Hawaiian and Other Pacific Islander alone  Annotation of Estimate!!Total:!!Not Hispanic or Latino:!!Native Hawaiian and Other Pacific Islander alone  Estimate!!Total:!!Not Hispanic or Latino:!!Some other race alone  Margin of Error!!Total:!!Not Hispanic or Latino:!!Some other race alone  Annotation of Margin of Error!!Total:!!Not Hispanic or Latino:!!Some other race alone  Annotation of Estimate!!Total:!!Not Hispanic or Latino:!!Some other race alone  Estimate!!Total:!!Not Hispanic or Latino:!!Two or more races:  Annotation of Estimate!!Total:!!Not Hispanic or Latino:!!Two or more races:  Margin of Error!!Total:!!Not Hispanic or Latino:!!Two or more races:  Annotation of Margin of Error!!Total:!!Not Hispanic or Latino:!!Two or more races:  Estimate!!Total:!!Not Hispanic or Latino:!!Two or more races:!!Two races including Some other race   \n",
       "1  NaN                                                                                                               NaN                                                                                                        17                                                                29                                                                       NaN                                                                                    NaN                                                                             100                                                            NaN                                                                          92                                                                    NaN                                                                                 0                                                                                                    \n",
       "\n",
       "                                                                                                 B03002_010M                                                                                                             B03002_010MA                                                                                                      B03002_010EA                                                                                                                  B03002_011E                                                                                                                         B03002_011M                                                                                                                                      B03002_011MA                                                                                                                               B03002_011EA                            B03002_012E                                   B03002_012M  \\\n",
       "0  Margin of Error!!Total:!!Not Hispanic or Latino:!!Two or more races:!!Two races including Some other race  Annotation of Margin of Error!!Total:!!Not Hispanic or Latino:!!Two or more races:!!Two races including Some other race  Annotation of Estimate!!Total:!!Not Hispanic or Latino:!!Two or more races:!!Two races including Some other race  Estimate!!Total:!!Not Hispanic or Latino:!!Two or more races:!!Two races excluding Some other race, and three or more races  Margin of Error!!Total:!!Not Hispanic or Latino:!!Two or more races:!!Two races excluding Some other race, and three or more races  Annotation of Margin of Error!!Total:!!Not Hispanic or Latino:!!Two or more races:!!Two races excluding Some other race, and three or more races  Annotation of Estimate!!Total:!!Not Hispanic or Latino:!!Two or more races:!!Two races excluding Some other race, and three or more races  Estimate!!Total:!!Hispanic or Latino:  Margin of Error!!Total:!!Hispanic or Latino:   \n",
       "1  13                                                                                                         NaN                                                                                                                      NaN                                                                                                               100                                                                                                                          92                                                                                                                                  NaN                                                                                                                                               NaN                                                                                                                                        116                                    83                                             \n",
       "\n",
       "                                                 B03002_012MA                                         B03002_012EA                                         B03002_013E                                                B03002_013M                                                             B03002_013MA                                                      B03002_013EA                                                             B03002_014E                                                                    B03002_014M                                                                                 B03002_014MA                                                                          B03002_014EA                                                                     B03002_015E                                                                                  B03002_015EA                                                                            B03002_015M  \\\n",
       "0  Annotation of Margin of Error!!Total:!!Hispanic or Latino:  Annotation of Estimate!!Total:!!Hispanic or Latino:  Estimate!!Total:!!Hispanic or Latino:!!White alone  Margin of Error!!Total:!!Hispanic or Latino:!!White alone  Annotation of Margin of Error!!Total:!!Hispanic or Latino:!!White alone  Annotation of Estimate!!Total:!!Hispanic or Latino:!!White alone  Estimate!!Total:!!Hispanic or Latino:!!Black or African American alone  Margin of Error!!Total:!!Hispanic or Latino:!!Black or African American alone  Annotation of Margin of Error!!Total:!!Hispanic or Latino:!!Black or African American alone  Annotation of Estimate!!Total:!!Hispanic or Latino:!!Black or African American alone  Estimate!!Total:!!Hispanic or Latino:!!American Indian and Alaska Native alone  Annotation of Estimate!!Total:!!Hispanic or Latino:!!American Indian and Alaska Native alone  Margin of Error!!Total:!!Hispanic or Latino:!!American Indian and Alaska Native alone   \n",
       "1  NaN                                                         NaN                                                  95                                                  81                                                         NaN                                                                      NaN                                                               0                                                                       13                                                                             NaN                                                                                          NaN                                                                                   0                                                                               NaN                                                                                           13                                                                                      \n",
       "\n",
       "                                                                                          B03002_015MA                                         B03002_016E                                                B03002_016M                                                             B03002_016MA                                                      B03002_016EA                                                                              B03002_017E                                                                                           B03002_017EA                                                                                     B03002_017M                                                                                                  B03002_017MA                                                   B03002_018E                                                                B03002_018EA                                                          B03002_018M  \\\n",
       "0  Annotation of Margin of Error!!Total:!!Hispanic or Latino:!!American Indian and Alaska Native alone  Estimate!!Total:!!Hispanic or Latino:!!Asian alone  Margin of Error!!Total:!!Hispanic or Latino:!!Asian alone  Annotation of Margin of Error!!Total:!!Hispanic or Latino:!!Asian alone  Annotation of Estimate!!Total:!!Hispanic or Latino:!!Asian alone  Estimate!!Total:!!Hispanic or Latino:!!Native Hawaiian and Other Pacific Islander alone  Annotation of Estimate!!Total:!!Hispanic or Latino:!!Native Hawaiian and Other Pacific Islander alone  Margin of Error!!Total:!!Hispanic or Latino:!!Native Hawaiian and Other Pacific Islander alone  Annotation of Margin of Error!!Total:!!Hispanic or Latino:!!Native Hawaiian and Other Pacific Islander alone  Estimate!!Total:!!Hispanic or Latino:!!Some other race alone  Annotation of Estimate!!Total:!!Hispanic or Latino:!!Some other race alone  Margin of Error!!Total:!!Hispanic or Latino:!!Some other race alone   \n",
       "1  NaN                                                                                                  0                                                   13                                                         NaN                                                                      NaN                                                               0                                                                                        NaN                                                                                                    13                                                                                              NaN                                                                                                           0                                                             NaN                                                                         13                                                                    \n",
       "\n",
       "                                                                        B03002_018MA                                                B03002_019E                                                       B03002_019M                                                                    B03002_019MA                                                             B03002_019EA                                                                                     B03002_020E                                                                                            B03002_020M                                                                                                         B03002_020MA                                                                                                  B03002_020EA                                                                                                              B03002_021E  \\\n",
       "0  Annotation of Margin of Error!!Total:!!Hispanic or Latino:!!Some other race alone  Estimate!!Total:!!Hispanic or Latino:!!Two or more races:  Margin of Error!!Total:!!Hispanic or Latino:!!Two or more races:  Annotation of Margin of Error!!Total:!!Hispanic or Latino:!!Two or more races:  Annotation of Estimate!!Total:!!Hispanic or Latino:!!Two or more races:  Estimate!!Total:!!Hispanic or Latino:!!Two or more races:!!Two races including Some other race  Margin of Error!!Total:!!Hispanic or Latino:!!Two or more races:!!Two races including Some other race  Annotation of Margin of Error!!Total:!!Hispanic or Latino:!!Two or more races:!!Two races including Some other race  Annotation of Estimate!!Total:!!Hispanic or Latino:!!Two or more races:!!Two races including Some other race  Estimate!!Total:!!Hispanic or Latino:!!Two or more races:!!Two races excluding Some other race, and three or more races   \n",
       "1  NaN                                                                                21                                                         29                                                                NaN                                                                             NaN                                                                      21                                                                                              29                                                                                                     NaN                                                                                                                  NaN                                                                                                           0                                                                                                                         \n",
       "\n",
       "                                                                                                                      B03002_021M                                                                                                                                  B03002_021MA                                                                                                                           B03002_021EA  Unnamed: 86  \n",
       "0  Margin of Error!!Total:!!Hispanic or Latino:!!Two or more races:!!Two races excluding Some other race, and three or more races  Annotation of Margin of Error!!Total:!!Hispanic or Latino:!!Two or more races:!!Two races excluding Some other race, and three or more races  Annotation of Estimate!!Total:!!Hispanic or Latino:!!Two or more races:!!Two races excluding Some other race, and three or more races NaN           \n",
       "1  13                                                                                                                              NaN                                                                                                                                           NaN                                                                                                                                   NaN           "
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "census_data['block_group_id'] = 'US.CA.' + census_data['GEO_ID'].str[11:14] + '.' + census_data['GEO_ID'].str[14:20] + '.' + census_data['GEO_ID'].str[-1:]\n",
    "first_column = census_data.pop('block_group_id')\n",
    "census_data.insert(0, 'block_group_id', first_column)\n",
    "print('total number of CBG:',census_data.shape[0])\n",
    "# census_data.head(1)\n",
    "pd.read_csv(\"data/ACSDT5Y2021.B03002-Data.csv\",header=0).head(2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "a91927d3-73bb-48ca-a8f3-52e584328328",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "25607\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>1</th>\n",
       "      <td>US.CA.001.400100.1</td>\n",
       "      <td>1963</td>\n",
       "      <td>0.727967</td>\n",
       "      <td>0.059093</td>\n",
       "      <td>0.022415</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.130922</td>\n",
       "      <td>0.110545</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>US.CA.001.400100.2</td>\n",
       "      <td>1361</td>\n",
       "      <td>0.664217</td>\n",
       "      <td>0.098457</td>\n",
       "      <td>0.044085</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.162381</td>\n",
       "      <td>0.061719</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>US.CA.001.400200.1</td>\n",
       "      <td>1029</td>\n",
       "      <td>0.793975</td>\n",
       "      <td>0.068027</td>\n",
       "      <td>0.005831</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.045675</td>\n",
       "      <td>0.172983</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>US.CA.001.400200.2</td>\n",
       "      <td>1009</td>\n",
       "      <td>0.623389</td>\n",
       "      <td>0.081269</td>\n",
       "      <td>0.041625</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.148662</td>\n",
       "      <td>0.203171</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>US.CA.001.400300.1</td>\n",
       "      <td>1122</td>\n",
       "      <td>0.655080</td>\n",
       "      <td>0.062389</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.006239</td>\n",
       "      <td>0.254011</td>\n",
       "      <td>0.044563</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       block_group_id  population   R_White  R_HispanicLatinx  \\\n",
       "1  US.CA.001.400100.1        1963  0.727967          0.059093   \n",
       "2  US.CA.001.400100.2        1361  0.664217          0.098457   \n",
       "3  US.CA.001.400200.1        1029  0.793975          0.068027   \n",
       "4  US.CA.001.400200.2        1009  0.623389          0.081269   \n",
       "5  US.CA.001.400300.1        1122  0.655080          0.062389   \n",
       "\n",
       "   R_AfricanAmerican  R_AmericanIndianAlaska   R_Asian   R_Other  \n",
       "1           0.022415                0.000000  0.130922  0.110545  \n",
       "2           0.044085                0.000000  0.162381  0.061719  \n",
       "3           0.005831                0.000000  0.045675  0.172983  \n",
       "4           0.041625                0.000000  0.148662  0.203171  \n",
       "5           0.000000                0.006239  0.254011  0.044563  "
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_ethnicity_new = census_data\n",
    "df_ethnicity_new['population'] = df_ethnicity_new['B03002_001E'].astype(int)\n",
    "df_ethnicity_new['R_White'] = df_ethnicity_new['B03002_003E'].astype(int)/df_ethnicity_new['population']\n",
    "df_ethnicity_new['R_AfricanAmerican'] = df_ethnicity_new['B03002_004E'].astype(int)/df_ethnicity_new['population']\n",
    "df_ethnicity_new['R_AmericanIndianAlaska'] = df_ethnicity_new['B03002_005E'].astype(int)/df_ethnicity_new['population']\n",
    "df_ethnicity_new['R_Asian'] = (df_ethnicity_new['B03002_006E'].astype(int) +  df_ethnicity_new['B03002_007E'].astype(int))/df_ethnicity_new['population'] #Asian alone + Native Hawaiian and Other Pacific Islander alone\n",
    "df_ethnicity_new['R_Other'] = (df_ethnicity_new['B03002_008E'].astype(int) + df_ethnicity_new['B03002_009E'].astype(int)+df_ethnicity_new['B03002_010E'].astype(int)+df_ethnicity_new['B03002_011E'].astype(int))/df_ethnicity_new['population']\n",
    "df_ethnicity_new['R_HispanicLatinx'] = df_ethnicity_new['B03002_012E'].astype(int)/df_ethnicity_new['population']\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])#23212 for the whole CA\n",
    "df_ethnicity_new.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 111,
   "id": "695d4b69-4277-4d05-84ec-9219c942bc72",
   "metadata": {},
   "outputs": [],
   "source": [
    "# %%time\n",
    "# #Option 2: select HOME locations in CA\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 'US.CA.%' \n",
    "#     \"\"\"\n",
    "# )\n",
    "# df_home_loc.to_pickle(\"../output/df_hoemloc_wholeCA.pkl\")\n",
    "df_home_loc = pd.read_pickle(\"../output/df_hoemloc_wholeCA.pkl\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "46924bb1-7738-4537-b77a-3bd39db20d4b",
   "metadata": {},
   "source": [
    "## individual home location based race composition"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 114,
   "id": "706b1adc-03fa-4e1f-9294-6eaad02b29d0",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "23162 25607\n",
      "18717\n",
      "Correlation Coefficient: 0.535201876401364\n",
      "P-value: 0.0\n"
     ]
    }
   ],
   "source": [
    "# pearson correlation between census block group population and the observed number of devices \n",
    "df_home_loc['devices'] = 1\n",
    "df_weight = df_home_loc.groupby('block_group_id').sum().reset_index()\n",
    "print(df_weight.shape[0],df_ethnicity_new.shape[0])\n",
    "df_ethnicity_new = pd.merge(df_ethnicity_new,df_weight[['block_group_id','devices']],on='block_group_id')  \n",
    "print(df_ethnicity_new.shape[0])\n",
    "\n",
    "from scipy.stats import pearsonr\n",
    "corr, pval = pearsonr(df_ethnicity_new['population'], df_ethnicity_new['devices'])\n",
    "print(\"Correlation Coefficient:\", corr)\n",
    "print(\"P-value:\", pval)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "id": "9118098b-1ab9-4470-a28c-c212a154aa6e",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "10781\n",
      "10733\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",
       "      <th>weight</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>US.CA.037.104701.1</td>\n",
       "      <td>1758.0</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.995449</td>\n",
       "      <td>0.004551</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>35</td>\n",
       "      <td>50.228571</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>US.CA.037.111400.1</td>\n",
       "      <td>2689.0</td>\n",
       "      <td>0.383414</td>\n",
       "      <td>0.361101</td>\n",
       "      <td>0.027148</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.228338</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>87</td>\n",
       "      <td>30.908046</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>US.CA.037.113321.1</td>\n",
       "      <td>4143.0</td>\n",
       "      <td>0.283611</td>\n",
       "      <td>0.381849</td>\n",
       "      <td>0.089549</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.161960</td>\n",
       "      <td>0.083032</td>\n",
       "      <td>145</td>\n",
       "      <td>28.572414</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>US.CA.037.115202.1</td>\n",
       "      <td>2066.0</td>\n",
       "      <td>0.386738</td>\n",
       "      <td>0.217812</td>\n",
       "      <td>0.101162</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.188771</td>\n",
       "      <td>0.105518</td>\n",
       "      <td>84</td>\n",
       "      <td>24.595238</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>US.CA.037.117301.2</td>\n",
       "      <td>1429.0</td>\n",
       "      <td>0.395381</td>\n",
       "      <td>0.425472</td>\n",
       "      <td>0.012596</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.075577</td>\n",
       "      <td>0.090973</td>\n",
       "      <td>59</td>\n",
       "      <td>24.220339</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       block_group_id  population   R_White  R_HispanicLatinx  R_AfricanAmerican  R_AmericanIndianAlaska   R_Asian   R_Other  devices     weight\n",
       "0  US.CA.037.104701.1  1758.0      0.000000  0.995449          0.004551           0.0                     0.000000  0.000000  35       50.228571\n",
       "1  US.CA.037.111400.1  2689.0      0.383414  0.361101          0.027148           0.0                     0.228338  0.000000  87       30.908046\n",
       "2  US.CA.037.113321.1  4143.0      0.283611  0.381849          0.089549           0.0                     0.161960  0.083032  145      28.572414\n",
       "3  US.CA.037.115202.1  2066.0      0.386738  0.217812          0.101162           0.0                     0.188771  0.105518  84       24.595238\n",
       "4  US.CA.037.117301.2  1429.0      0.395381  0.425472          0.012596           0.0                     0.075577  0.090973  59       24.220339"
      ]
     },
     "execution_count": 32,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "print(df_ethnicity_new.shape[0])\n",
    "df_ethnicity_new['weight'] = df_ethnicity_new.population/df_ethnicity_new.devices\n",
    "df_ethnicity_new = df_ethnicity_new[df_ethnicity_new['devices']>5]\n",
    "print(df_ethnicity_new.shape[0])\n",
    "df_ethnicity_new.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "id": "41ff3fa1-6e41-4545-ae58-4bc1e10116ea",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_indi_home_loc_race = pd.merge(df_home_loc[['cuebiq_id','block_group_id']],df_ethnicity_new,on='block_group_id')  \n",
    "df_indi_home_loc_race.rename(columns={'block_group_id': 'home_block'}, inplace=True)\n",
    "df_indi_home_loc_race = df_indi_home_loc_race[df_indi_home_loc_race['R_White'].notna()]"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ec3dec28-36c0-4595-9407-d0aae3b9a803",
   "metadata": {},
   "source": [
    "considering sample weight"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "6edbff1d-7bfa-450b-9946-d5a2caffd027",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_indi_home_loc_race['R_White'] = df_indi_home_loc_race['R_White']*df_indi_home_loc_race['weight']\n",
    "df_indi_home_loc_race['R_HispanicLatinx'] = df_indi_home_loc_race['R_HispanicLatinx']*df_indi_home_loc_race['weight']\n",
    "df_indi_home_loc_race['R_AfricanAmerican'] = df_indi_home_loc_race['R_AfricanAmerican']*df_indi_home_loc_race['weight']\n",
    "df_indi_home_loc_race['R_AmericanIndianAlaska'] = df_indi_home_loc_race['R_AmericanIndianAlaska']*df_indi_home_loc_race['weight']\n",
    "df_indi_home_loc_race['R_Asian'] = df_indi_home_loc_race['R_Asian']*df_indi_home_loc_race['weight']\n",
    "df_indi_home_loc_race['R_Other'] = df_indi_home_loc_race['R_Other']*df_indi_home_loc_race['weight']\n",
    "df_indi_home_loc_race"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "id": "9acb8372-f60c-4169-b393-8d41b2a21b65",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_indi_home_loc_race.to_pickle(\"./output/GreaterLA_Individual_race_composition.pkl\")  "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "541fd621-12fb-4864-89e1-552db46009f0",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "6e93df56-4ef1-4da2-9cde-a14af3f04cfb",
   "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>cuebiq_id</th>\n",
       "      <th>home_block</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>weight</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>4374082992</td>\n",
       "      <td>US.CA.037.553802.3</td>\n",
       "      <td>2674.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>24.795455</td>\n",
       "      <td>1.772727</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.522727</td>\n",
       "      <td>2.295455</td>\n",
       "      <td>88</td>\n",
       "      <td>30.386364</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    cuebiq_id          home_block  population  R_White  R_HispanicLatinx  R_AfricanAmerican  R_AmericanIndianAlaska   R_Asian   R_Other  devices     weight\n",
       "0  4374082992  US.CA.037.553802.3  2674.0      1.0      24.795455         1.772727           0.0                     0.522727  2.295455  88       30.386364"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_indi_home_loc_race = pd.read_pickle(\"../output/GreaterLA_Individual_race_composition.pkl\")\n",
    "df_indi_home_loc_race.head(1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "a1e5d238-d4de-4042-9563-801f1664d70b",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "56a8e248-3742-42be-8779-71ab5db4d4b3",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "cc366804-6e91-40d6-b807-301dfdc271ea",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "85e9f303-a45d-46c2-b161-7ee451ecb0b4",
   "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.3"
  },
  "widgets": {
   "application/vnd.jupyter.widget-state+json": {
    "state": {},
    "version_major": 2,
    "version_minor": 0
   }
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}