02_simulate_scenario_4.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "694dfd52",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Wed May 10 13:45:12 PDT 2023\r\n"
     ]
    }
   ],
   "source": [
    "import numpy as np, matplotlib.pyplot as plt, pandas as pd\n",
    "pd.set_option('display.max_rows', 8)\n",
    "!date\n",
    "\n",
    "%load_ext autoreload\n",
    "%autoreload 2"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "4c5c937e",
   "metadata": {},
   "source": [
    "# Scenario 4: Swapping DAS for 2010 data, TDA DAS for 2020 data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "5852ec8a",
   "metadata": {},
   "outputs": [],
   "source": [
    "np.random.seed(12345)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "fc70badb",
   "metadata": {},
   "source": [
    "# Load synthetic data for TX and use it to simulate 2010 and 2020 populations, and also get k_anon_2020 value from existing demo product\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "feaa2385",
   "metadata": {},
   "outputs": [],
   "source": [
    "import linked_census_disclosure.data as lcd_data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "8073b908",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "CPU times: user 16.6 s, sys: 2.85 s, total: 19.5 s\n",
      "Wall time: 29.5 s\n"
     ]
    }
   ],
   "source": [
    "%%time\n",
    "\n",
    "sf1 = lcd_data.read_sf1_remf('tx')\n",
    "dhc = lcd_data.read_dhc_remf('tx')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "27c5b115",
   "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>state</th>\n",
       "      <th>county</th>\n",
       "      <th>tract</th>\n",
       "      <th>block</th>\n",
       "      <th>row_num</th>\n",
       "      <th>age</th>\n",
       "      <th>sex</th>\n",
       "      <th>race</th>\n",
       "      <th>eth</th>\n",
       "      <th>n</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>48</td>\n",
       "      <td>201.0</td>\n",
       "      <td>431802.0</td>\n",
       "      <td>2009.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>8.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>48</td>\n",
       "      <td>201.0</td>\n",
       "      <td>431802.0</td>\n",
       "      <td>2009.0</td>\n",
       "      <td>1</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>3.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>48</td>\n",
       "      <td>201.0</td>\n",
       "      <td>431802.0</td>\n",
       "      <td>2009.0</td>\n",
       "      <td>2</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>6.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>48</td>\n",
       "      <td>201.0</td>\n",
       "      <td>431802.0</td>\n",
       "      <td>2009.0</td>\n",
       "      <td>3</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>12.0</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>5941</th>\n",
       "      <td>48</td>\n",
       "      <td>451.0</td>\n",
       "      <td>1600.0</td>\n",
       "      <td>4313.0</td>\n",
       "      <td>0</td>\n",
       "      <td>18.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5942</th>\n",
       "      <td>48</td>\n",
       "      <td>337.0</td>\n",
       "      <td>950200.0</td>\n",
       "      <td>1051.0</td>\n",
       "      <td>0</td>\n",
       "      <td>18.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5943</th>\n",
       "      <td>48</td>\n",
       "      <td>333.0</td>\n",
       "      <td>950100.0</td>\n",
       "      <td>2208.0</td>\n",
       "      <td>0</td>\n",
       "      <td>18.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5944</th>\n",
       "      <td>48</td>\n",
       "      <td>279.0</td>\n",
       "      <td>950600.0</td>\n",
       "      <td>1182.0</td>\n",
       "      <td>0</td>\n",
       "      <td>18.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>6005936 rows × 10 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "      state  county     tract   block  row_num   age  sex  race  eth     n\n",
       "0        48   201.0  431802.0  2009.0        0   0.0  0.0   0.0  0.0   8.0\n",
       "1        48   201.0  431802.0  2009.0        1   0.0  0.0   0.0  1.0   3.0\n",
       "2        48   201.0  431802.0  2009.0        2   0.0  0.0   6.0  0.0   2.0\n",
       "3        48   201.0  431802.0  2009.0        3   0.0  1.0   0.0  0.0  12.0\n",
       "...     ...     ...       ...     ...      ...   ...  ...   ...  ...   ...\n",
       "5941     48   451.0    1600.0  4313.0        0  18.0  1.0   0.0  0.0   2.0\n",
       "5942     48   337.0  950200.0  1051.0        0  18.0  0.0   0.0  0.0   2.0\n",
       "5943     48   333.0  950100.0  2208.0        0  18.0  0.0   0.0  0.0   1.0\n",
       "5944     48   279.0  950600.0  1182.0        0  18.0  0.0   0.0  0.0   1.0\n",
       "\n",
       "[6005936 rows x 10 columns]"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sf1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "57debc00",
   "metadata": {},
   "outputs": [],
   "source": [
    "old_dhc = dhc"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "aebcd619",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "CPU times: user 3min 17s, sys: 53.3 s, total: 4min 10s\n",
      "Wall time: 4min 32s\n"
     ]
    }
   ],
   "source": [
    "%%time\n",
    "\n",
    "ppmf = pd.read_csv('/share/scratch/users/abie/projects/2023/ppmf_production_settings/2023-04-03-ppmf_p.csv')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "06467d87",
   "metadata": {},
   "source": [
    "# details on these ppmfs\n",
    "\n",
    "https://www2.census.gov/programs-surveys/decennial/2020/program-management/data-product-planning/2010-demonstration-data-products/04-Demonstration_Data_Products_Suite/2023-04-03/2023-04-03_Privacy-Protected_Microdata_File/2023-04-03-ppmf-record-layout.pdf"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "c8f2bee5",
   "metadata": {},
   "outputs": [],
   "source": [
    "# restructure ppmf data to match reconstructed dhc data that we used previously\n",
    "t = ppmf[ppmf.TABBLKST == 48].copy()\n",
    "\n",
    "t['state'] = t.TABBLKST.astype(int)  # ANNOYING: these types need to be just right for my hacky geoids to match up\n",
    "assert set(t.state.unique()) == set(dhc.state.unique())\n",
    "\n",
    "t['county'] = t.TABBLKCOU.astype(float)\n",
    "assert set(t.county.unique()) == set(dhc.county.unique())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "9d4985c6",
   "metadata": {},
   "outputs": [],
   "source": [
    "# def my_combined_set(df, col_list):\n",
    "#     return set(\n",
    "#         sum([df[col].astype(str) for col in col_list]).unique()\n",
    "#     )\n",
    "\n",
    "\n",
    "t['tract'] = t.TABTRACTCE.astype(float)\n",
    "# assert my_combined_set(t, ['county', 'tract']) == my_combined_set(dhc, ['county', 'tract'])\n",
    "assert set(t.tract.unique()) == set(dhc.tract.unique())\n",
    "\n",
    "t['block'] = t.TABBLK.astype(float)\n",
    "# assert my_combined_set(t, ['county', 'tract', 'block']) == my_combined_set(dhc, ['county', 'tract', 'block'])\n",
    "# assert set(t.block.unique()) == set(dhc.block.unique())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "3eb6c584",
   "metadata": {},
   "outputs": [],
   "source": [
    "t['age'] = t.QAGE.astype(float)\n",
    "t['sex'] = (t.QSEX == 2).astype(float) # 2 -> female -> 1.0; 1 -> male -> 0.0\n",
    "t['race'] = (t.CENRACE - 1) # TODO: confirm that this mapping is correct\n",
    "t['eth'] = (t.CENHISP == 2).astype(float) # 2 -> Hispanic -> 1.0"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "71de2cd4",
   "metadata": {},
   "outputs": [],
   "source": [
    "t['n'] = 1.0\n",
    "g = t.groupby(['state', 'county', 'tract', 'block', 'age', 'sex', 'race', 'eth'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "id": "1daf4415",
   "metadata": {},
   "outputs": [],
   "source": [
    "dhc = g.n.sum().reset_index()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "id": "dab33b59",
   "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>state</th>\n",
       "      <th>county</th>\n",
       "      <th>tract</th>\n",
       "      <th>block</th>\n",
       "      <th>age</th>\n",
       "      <th>sex</th>\n",
       "      <th>race</th>\n",
       "      <th>eth</th>\n",
       "      <th>n</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>48</td>\n",
       "      <td>1.0</td>\n",
       "      <td>950100.0</td>\n",
       "      <td>1000.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>5</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>48</td>\n",
       "      <td>1.0</td>\n",
       "      <td>950100.0</td>\n",
       "      <td>1000.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>5</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>48</td>\n",
       "      <td>1.0</td>\n",
       "      <td>950100.0</td>\n",
       "      <td>1000.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>5</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>48</td>\n",
       "      <td>1.0</td>\n",
       "      <td>950100.0</td>\n",
       "      <td>1000.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</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",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11717815</th>\n",
       "      <td>48</td>\n",
       "      <td>507.0</td>\n",
       "      <td>950302.0</td>\n",
       "      <td>5026.0</td>\n",
       "      <td>59.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11717816</th>\n",
       "      <td>48</td>\n",
       "      <td>507.0</td>\n",
       "      <td>950302.0</td>\n",
       "      <td>5026.0</td>\n",
       "      <td>59.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11717817</th>\n",
       "      <td>48</td>\n",
       "      <td>507.0</td>\n",
       "      <td>950302.0</td>\n",
       "      <td>5026.0</td>\n",
       "      <td>64.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11717818</th>\n",
       "      <td>48</td>\n",
       "      <td>507.0</td>\n",
       "      <td>950302.0</td>\n",
       "      <td>5026.0</td>\n",
       "      <td>71.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>11717819 rows × 9 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "          state  county     tract   block   age  sex  race  eth    n\n",
       "0            48     1.0  950100.0  1000.0   2.0  1.0     5  1.0  1.0\n",
       "1            48     1.0  950100.0  1000.0   3.0  0.0     5  1.0  1.0\n",
       "2            48     1.0  950100.0  1000.0   4.0  1.0     5  1.0  1.0\n",
       "3            48     1.0  950100.0  1000.0   5.0  1.0     0  0.0  1.0\n",
       "...         ...     ...       ...     ...   ...  ...   ...  ...  ...\n",
       "11717815     48   507.0  950302.0  5026.0  59.0  0.0     0  0.0  1.0\n",
       "11717816     48   507.0  950302.0  5026.0  59.0  1.0     0  1.0  1.0\n",
       "11717817     48   507.0  950302.0  5026.0  64.0  1.0     0  1.0  1.0\n",
       "11717818     48   507.0  950302.0  5026.0  71.0  0.0     0  1.0  1.0\n",
       "\n",
       "[11717819 rows x 9 columns]"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "dhc"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "id": "2279236c",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'25,145,561'"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# does this have the expected number of people? yes, now it does\n",
    "f'{sf1.n.sum():,.0f}' # expect population of texas in 2010 to be 25,145,561"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "45c7f8b9",
   "metadata": {},
   "source": [
    "# Focus in on the 10-17 year olds\n",
    "\n",
    "We will use this group as our 2020 population and then simulate what their attributes were in 2010 for our 2010 population."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "id": "a3c05670",
   "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>state</th>\n",
       "      <th>county</th>\n",
       "      <th>tract</th>\n",
       "      <th>block</th>\n",
       "      <th>row_num</th>\n",
       "      <th>age</th>\n",
       "      <th>sex</th>\n",
       "      <th>race</th>\n",
       "      <th>eth</th>\n",
       "      <th>n</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>42</th>\n",
       "      <td>48</td>\n",
       "      <td>201.0</td>\n",
       "      <td>431802.0</td>\n",
       "      <td>2009.0</td>\n",
       "      <td>42</td>\n",
       "      <td>10.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>43</th>\n",
       "      <td>48</td>\n",
       "      <td>201.0</td>\n",
       "      <td>431802.0</td>\n",
       "      <td>2009.0</td>\n",
       "      <td>43</td>\n",
       "      <td>10.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>44</th>\n",
       "      <td>48</td>\n",
       "      <td>201.0</td>\n",
       "      <td>431802.0</td>\n",
       "      <td>2009.0</td>\n",
       "      <td>44</td>\n",
       "      <td>10.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>3.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>45</th>\n",
       "      <td>48</td>\n",
       "      <td>201.0</td>\n",
       "      <td>431802.0</td>\n",
       "      <td>2009.0</td>\n",
       "      <td>45</td>\n",
       "      <td>10.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>4.0</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>5925</th>\n",
       "      <td>48</td>\n",
       "      <td>111.0</td>\n",
       "      <td>950100.0</td>\n",
       "      <td>1136.0</td>\n",
       "      <td>3</td>\n",
       "      <td>14.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>17.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5934</th>\n",
       "      <td>48</td>\n",
       "      <td>387.0</td>\n",
       "      <td>950100.0</td>\n",
       "      <td>2057.0</td>\n",
       "      <td>0</td>\n",
       "      <td>17.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5936</th>\n",
       "      <td>48</td>\n",
       "      <td>171.0</td>\n",
       "      <td>950500.0</td>\n",
       "      <td>1125.0</td>\n",
       "      <td>0</td>\n",
       "      <td>10.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5937</th>\n",
       "      <td>48</td>\n",
       "      <td>171.0</td>\n",
       "      <td>950500.0</td>\n",
       "      <td>1125.0</td>\n",
       "      <td>1</td>\n",
       "      <td>17.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>2058208 rows × 10 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "      state  county     tract   block  row_num   age  sex  race  eth    n\n",
       "42       48   201.0  431802.0  2009.0       42  10.0  0.0   0.0  1.0  1.0\n",
       "43       48   201.0  431802.0  2009.0       43  10.0  0.0   1.0  0.0  2.0\n",
       "44       48   201.0  431802.0  2009.0       44  10.0  1.0   0.0  0.0  3.0\n",
       "45       48   201.0  431802.0  2009.0       45  10.0  1.0   0.0  1.0  4.0\n",
       "...     ...     ...       ...     ...      ...   ...  ...   ...  ...  ...\n",
       "5925     48   111.0  950100.0  1136.0        3  14.0  0.0  17.0  1.0  1.0\n",
       "5934     48   387.0  950100.0  2057.0        0  17.0  0.0   1.0  1.0  1.0\n",
       "5936     48   171.0  950500.0  1125.0        0  10.0  0.0   0.0  1.0  1.0\n",
       "5937     48   171.0  950500.0  1125.0        1  17.0  1.0   0.0  1.0  1.0\n",
       "\n",
       "[2058208 rows x 10 columns]"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sf1[(sf1.age >= 10) & (sf1.age < 18)]"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "68622c31",
   "metadata": {},
   "source": [
    "## expand this portion of the SF1 into microdata\n",
    "\n",
    "For example, turn the first row of the table above into 9 rows for non-Hispanic White males who are age 8 in 2020."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "id": "136f6897",
   "metadata": {},
   "outputs": [],
   "source": [
    "# it takes a while to do this, around 15-30 minutes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "id": "3e638409",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "CPU times: user 25min, sys: 23.5 s, total: 25min 24s\n",
      "Wall time: 25min 21s\n"
     ]
    }
   ],
   "source": [
    "%%time\n",
    "\n",
    "def my_expand(df):\n",
    "    assert len(df) == 1\n",
    "    return pd.Series(1, index=range(df.iloc[0].n.astype(int)), name='pweight')\n",
    "\n",
    "df = sf1[(sf1.age >= 10) & (sf1.age < 18)].groupby(\n",
    "    ['state', 'county', 'tract', 'block', 'age', 'sex', 'race', 'eth']).apply(my_expand).reset_index()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "id": "8140ff76",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_all = df.copy()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "id": "64b8b1e2",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'3,009,117'"
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "n_kids = len(df)  # number of children\n",
    "f'{n_kids:,.0f}'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "id": "517dba76",
   "metadata": {},
   "outputs": [],
   "source": [
    "#### simulate 10 years of demographic change\n",
    "\n",
    "df['age_2020'] = df.age\n",
    "df['age_2010'] = df.age - 10\n",
    "del df['age']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "id": "63018976",
   "metadata": {},
   "outputs": [],
   "source": [
    "assert np.all(df.age_2010 >= 0), 'ensure that all ages are still non-negative'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "id": "e4b235e4",
   "metadata": {},
   "outputs": [],
   "source": [
    "# ignore births, because we are focused\n",
    "# only on kids who can be linked between 2010 and 2020 census"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "82d4291b",
   "metadata": {},
   "source": [
    "# Add in migration, make the change to the 2010 geography\n",
    "\n",
    "p_stay from ACS, see [2022_04_19a_das_dhc_attack_mig_data.ipynb](2022_04_19a_das_dhc_attack_mig_data.ipynb)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "id": "dc7050e4",
   "metadata": {},
   "outputs": [],
   "source": [
    "# simple model migration, based on probability\n",
    "# of being in same house for 10+ years among household with 8-17 year olds\n",
    "# in ACS\n",
    "\n",
    "p_stay = 0.23\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "id": "bfbe765d",
   "metadata": {},
   "outputs": [],
   "source": [
    "def add_geoid(df):\n",
    "    \"\"\"form geoid from 'state', 'county', 'tract', 'block'\n",
    "    \"\"\"\n",
    "\n",
    "    df['geoid'] = (df.state.astype(str) + df.county.astype(str).str.zfill(3)\n",
    "                   + df.tract.astype(str).str.zfill(6) + df.block.astype(str).str.zfill(4))\n",
    "    \n",
    "add_geoid(sf1)\n",
    "add_geoid(dhc)\n",
    "add_geoid(df)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "id": "1453cfcb",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "4829.0181819.02002.0     707\n",
       "48201.0542102.02012.0    702\n",
       "4829.0181404.01002.0     631\n",
       "4829.0180701.02001.0     629\n",
       "                        ... \n",
       "48285.00400.02075.0        1\n",
       "4813.0960600.01170.0       1\n",
       "48177.00600.01081.0        1\n",
       "48441.013600.02067.0       1\n",
       "Name: geoid, Length: 458171, dtype: int64"
      ]
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "dhc.geoid.value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "id": "d6c160f9",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "48201.0542102.02012.0    133\n",
       "48201.0542000.01008.0    127\n",
       "48141.010600.03025.0     126\n",
       "4829.0181819.02002.0     125\n",
       "                        ... \n",
       "4879.0950100.01361.0       1\n",
       "4883.0950700.01428.0       1\n",
       "48503.0950400.03107.0      1\n",
       "48279.0950600.01182.0      1\n",
       "Name: geoid, Length: 454658, dtype: int64"
      ]
     },
     "execution_count": 26,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sf1.geoid.value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "id": "072d9451",
   "metadata": {},
   "outputs": [],
   "source": [
    "all_locations = sf1.geoid\n",
    "p_all_locations = sf1.n / sf1.n.sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "id": "ba2cdc3a",
   "metadata": {},
   "outputs": [],
   "source": [
    "locations_2020 = df.geoid"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "id": "39c6a32e",
   "metadata": {},
   "outputs": [],
   "source": [
    "random_location = np.random.choice(all_locations, size=len(df),\n",
    "                                   p=p_all_locations, replace=True)\n",
    "\n",
    "locations_2010 = np.where(np.random.uniform(size=len(df)) < p_stay,\n",
    "                         locations_2020,\n",
    "                         random_location)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "id": "06777971",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0.23014824614662707"
      ]
     },
     "execution_count": 30,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "np.mean(locations_2010 == locations_2020)  # should be around 0.23"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "id": "da986b37",
   "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>state</th>\n",
       "      <th>county</th>\n",
       "      <th>tract</th>\n",
       "      <th>block</th>\n",
       "      <th>sex</th>\n",
       "      <th>race</th>\n",
       "      <th>eth</th>\n",
       "      <th>level_8</th>\n",
       "      <th>pweight</th>\n",
       "      <th>age_2020</th>\n",
       "      <th>age_2010</th>\n",
       "      <th>geoid_2020</th>\n",
       "      <th>geoid_2010</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>48</td>\n",
       "      <td>1.0</td>\n",
       "      <td>950100.0</td>\n",
       "      <td>1000.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>10.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>481.0950100.01000.0</td>\n",
       "      <td>48185.0180101.03003.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>48</td>\n",
       "      <td>1.0</td>\n",
       "      <td>950100.0</td>\n",
       "      <td>1000.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>11.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>481.0950100.01000.0</td>\n",
       "      <td>48201.0411800.03023.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>48</td>\n",
       "      <td>1.0</td>\n",
       "      <td>950100.0</td>\n",
       "      <td>1000.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>13.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>481.0950100.01000.0</td>\n",
       "      <td>481.0950100.01000.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>48</td>\n",
       "      <td>1.0</td>\n",
       "      <td>950100.0</td>\n",
       "      <td>1000.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>15.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>481.0950100.01000.0</td>\n",
       "      <td>481.0950100.01000.0</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",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3009113</th>\n",
       "      <td>48</td>\n",
       "      <td>507.0</td>\n",
       "      <td>950302.0</td>\n",
       "      <td>5025.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>13.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>48507.0950302.05025.0</td>\n",
       "      <td>48135.02900.03007.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3009114</th>\n",
       "      <td>48</td>\n",
       "      <td>507.0</td>\n",
       "      <td>950302.0</td>\n",
       "      <td>5025.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>17.0</td>\n",
       "      <td>7.0</td>\n",
       "      <td>48507.0950302.05025.0</td>\n",
       "      <td>48409.010302.02003.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3009115</th>\n",
       "      <td>48</td>\n",
       "      <td>507.0</td>\n",
       "      <td>950302.0</td>\n",
       "      <td>5026.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>15.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>48507.0950302.05026.0</td>\n",
       "      <td>48507.0950302.05026.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3009116</th>\n",
       "      <td>48</td>\n",
       "      <td>507.0</td>\n",
       "      <td>950302.0</td>\n",
       "      <td>5026.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>17.0</td>\n",
       "      <td>7.0</td>\n",
       "      <td>48507.0950302.05026.0</td>\n",
       "      <td>4829.0182101.01003.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>3009117 rows × 13 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "         state  county     tract   block  sex  race  eth  level_8  pweight  \\\n",
       "0           48     1.0  950100.0  1000.0  0.0   5.0  1.0        0        1   \n",
       "1           48     1.0  950100.0  1000.0  1.0   5.0  1.0        0        1   \n",
       "2           48     1.0  950100.0  1000.0  0.0   5.0  1.0        0        1   \n",
       "3           48     1.0  950100.0  1000.0  0.0   1.0  0.0        0        1   \n",
       "...        ...     ...       ...     ...  ...   ...  ...      ...      ...   \n",
       "3009113     48   507.0  950302.0  5025.0  0.0   0.0  1.0        0        1   \n",
       "3009114     48   507.0  950302.0  5025.0  1.0   0.0  1.0        0        1   \n",
       "3009115     48   507.0  950302.0  5026.0  0.0   0.0  1.0        0        1   \n",
       "3009116     48   507.0  950302.0  5026.0  0.0   0.0  1.0        0        1   \n",
       "\n",
       "         age_2020  age_2010             geoid_2020             geoid_2010  \n",
       "0            10.0       0.0    481.0950100.01000.0  48185.0180101.03003.0  \n",
       "1            11.0       1.0    481.0950100.01000.0  48201.0411800.03023.0  \n",
       "2            13.0       3.0    481.0950100.01000.0    481.0950100.01000.0  \n",
       "3            15.0       5.0    481.0950100.01000.0    481.0950100.01000.0  \n",
       "...           ...       ...                    ...                    ...  \n",
       "3009113      13.0       3.0  48507.0950302.05025.0    48135.02900.03007.0  \n",
       "3009114      17.0       7.0  48507.0950302.05025.0   48409.010302.02003.0  \n",
       "3009115      15.0       5.0  48507.0950302.05026.0  48507.0950302.05026.0  \n",
       "3009116      17.0       7.0  48507.0950302.05026.0   4829.0182101.01003.0  \n",
       "\n",
       "[3009117 rows x 13 columns]"
      ]
     },
     "execution_count": 31,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df['geoid_2020'] = locations_2020\n",
    "df['geoid_2010'] = locations_2010\n",
    "del df['geoid']\n",
    "\n",
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "id": "5f0ec06d",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0.23014824614662707"
      ]
     },
     "execution_count": 32,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "np.mean(df.geoid_2010 == df.geoid_2020)  # should be around 23%"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "d0faa0b7",
   "metadata": {},
   "source": [
    "# Make gender column, based on BRFSS 2019 SOGI results\n",
    "\n",
    "In future work, could try to incorporate observation that there is substantial age dependence in these values.  But for now, keep it simple, and work with the crude prevalence rates.  It is already not-that-simple.\n",
    "\n",
    "Since we want to use the demonstration DHC without re-running it, we first take the reported_sex_2020 column from the `sex` column in the SF1 ReMF (note: this conflates swapping and TDA)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "id": "e84d4367",
   "metadata": {},
   "outputs": [],
   "source": [
    "df['reported_sex_2020'] = df.sex"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "id": "c0c5f3de",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array([0.00182556, 0.00223124, 0.00121704, 0.99472617])"
      ]
     },
     "execution_count": 34,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# then initalize a gender for each simulant, calibrated to have unconditional probability from BRFSS\n",
    "# and also to match the data generation procedure for the reported_sex_2020 column\n",
    "\n",
    "p_trans_boy = 0.18 / 100\n",
    "p_trans_girl = 0.22 / 100\n",
    "p_trans_other = 0.12 / 100\n",
    "p_cis = 98.08 / 100\n",
    "\n",
    "# rescale to sum to 100%\n",
    "p_gender = np.array([p_trans_boy, p_trans_girl, p_trans_other, p_cis])\n",
    "p_gender /= p_gender.sum()\n",
    "p_gender"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "id": "6425a054",
   "metadata": {},
   "outputs": [],
   "source": [
    "# first initialize gender without distinguishing cis boy and cis girl\n",
    "# since that matches BRFSS SOGI question\n",
    "df['gender'] = np.random.choice(['trans_boy', 'trans_girl', 'trans_other', 'cis'], p=p_gender, size=len(df))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "id": "3376a0f9",
   "metadata": {},
   "outputs": [],
   "source": [
    "# now distinguish cis based on reconstructed sex_id\n",
    "df.gender = np.where(df.gender == 'cis',\n",
    "                     df.sex.map({0:'cis_boy', 1:'cis_girl'}),\n",
    "                     df.gender  # ~50% of trans_boys have reported_sex_2020 female, etc\n",
    "                    )"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "id": "506ff253",
   "metadata": {},
   "outputs": [],
   "source": [
    "df['trans'] = df.gender.isin(['trans_boy', 'trans_girl', 'trans_other'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "id": "edd2635f",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "cis_boy        50.99\n",
       "cis_girl       48.48\n",
       "trans_girl      0.22\n",
       "trans_boy       0.18\n",
       "trans_other     0.12\n",
       "Name: gender, dtype: float64"
      ]
     },
     "execution_count": 38,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "np.round(100 * df.gender.value_counts(normalize=True), 2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "id": "4c164e06",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0.52"
      ]
     },
     "execution_count": 39,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "np.round(100 * df.gender.value_counts(normalize=True).filter(like='trans').sum(), 2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "id": "b30b0540",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0.52"
      ]
     },
     "execution_count": 40,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "np.round(100 * df.trans.mean(), 2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "id": "4c04fdc6",
   "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>reported_sex_2020</th>\n",
       "      <th>0.0</th>\n",
       "      <th>1.0</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>gender</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>trans_boy</th>\n",
       "      <td>0.497812</td>\n",
       "      <td>0.502188</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>trans_girl</th>\n",
       "      <td>0.500673</td>\n",
       "      <td>0.499327</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>trans_other</th>\n",
       "      <td>0.507739</td>\n",
       "      <td>0.492261</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "reported_sex_2020       0.0       1.0\n",
       "gender                               \n",
       "trans_boy          0.497812  0.502188\n",
       "trans_girl         0.500673  0.499327\n",
       "trans_other        0.507739  0.492261"
      ]
     },
     "execution_count": 41,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df[df.trans].groupby('gender').reported_sex_2020.value_counts(normalize=True).unstack()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "id": "c5eeb453",
   "metadata": {},
   "outputs": [],
   "source": [
    "def gender_to_sex_2010(gender, reported_sex_2020):\n",
    "    # start with values reported in 2020\n",
    "    sex = reported_sex_2020.copy()\n",
    "    \n",
    "    # update the trans_boy and trans_girl entries to be sex assigned at birth\n",
    "    sex = np.where((gender == 'trans_boy'),\n",
    "                   1,\n",
    "                   sex\n",
    "                  )\n",
    "    sex = np.where((gender == 'trans_girl'),\n",
    "                   0,\n",
    "                   sex\n",
    "                  )\n",
    "    return sex\n",
    "\n",
    "\n",
    "df['reported_sex_2010'] = gender_to_sex_2010(df.gender, df.reported_sex_2020)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "id": "aedcd5f9",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0.005247054202279273"
      ]
     },
     "execution_count": 43,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df['trans'].mean()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "id": "56f264ab",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0.0020168707298519797"
      ]
     },
     "execution_count": 44,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "np.mean(df.reported_sex_2010 != df.reported_sex_2020)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "e8370bc7",
   "metadata": {},
   "source": [
    "# Values for results section"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "id": "6c13cbcb",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'3,009,117'"
      ]
     },
     "execution_count": 45,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Our synthetic population matched the age, sex, race/ethnicity, and geography of Texas\n",
    "# on census day April 1, 2010, with\n",
    "# X children ages 0-7 in Y household on census day 2010\n",
    "\n",
    "n_kids = len(df)  # number of children\n",
    "f'{n_kids:,.0f}'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 46,
   "id": "cf6e6846",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'692,543'"
      ]
     },
     "execution_count": 46,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# number of kids that were in same census block in 2010 and 2020 census\n",
    "n_stayed = (df.geoid_2010 == df.geoid_2020).sum()\n",
    "f'{n_stayed:,.0f}'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "id": "5ca1ed86",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'3,629'"
      ]
     },
     "execution_count": 47,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# number of trans kids that were in same place in 2010 and 2020 census\n",
    "\n",
    "n_trans_stayed = (df.trans & (df.geoid_2010 == df.geoid_2020)).sum()\n",
    "f'{n_trans_stayed:,.0f}'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 48,
   "id": "5efb6e20",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'6,069'"
      ]
     },
     "execution_count": 48,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# number of trans kids identified if full census data with names and dob was released\n",
    "\n",
    "n_trans = df[(df.reported_sex_2010 != df.reported_sex_2020)].pweight.sum()\n",
    "f'{n_trans:,.0f}'"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "d4af7528",
   "metadata": {},
   "source": [
    "# number of trans families identified by age, geoid from reconstruction based on TDA-protected DHC\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 49,
   "id": "2cfb7327",
   "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></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th>n_simulants</th>\n",
       "      <th>pct_male</th>\n",
       "      <th>pct_trans</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>geoid</th>\n",
       "      <th>age</th>\n",
       "      <th>race</th>\n",
       "      <th>eth</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th rowspan=\"4\" valign=\"top\">481.0950100.01000.0</th>\n",
       "      <th>11.0</th>\n",
       "      <th>0.0</th>\n",
       "      <th>1.0</th>\n",
       "      <td>1</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"2\" valign=\"top\">13.0</th>\n",
       "      <th>0.0</th>\n",
       "      <th>0.0</th>\n",
       "      <td>1</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5.0</th>\n",
       "      <th>1.0</th>\n",
       "      <td>2</td>\n",
       "      <td>50.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15.0</th>\n",
       "      <th>1.0</th>\n",
       "      <th>0.0</th>\n",
       "      <td>2</td>\n",
       "      <td>50.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <th>...</th>\n",
       "      <th>...</th>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4899.010804.03032.0</th>\n",
       "      <th>13.0</th>\n",
       "      <th>1.0</th>\n",
       "      <th>0.0</th>\n",
       "      <td>1</td>\n",
       "      <td>100.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"2\" valign=\"top\">4899.010804.03034.0</th>\n",
       "      <th>12.0</th>\n",
       "      <th>7.0</th>\n",
       "      <th>0.0</th>\n",
       "      <td>1</td>\n",
       "      <td>100.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13.0</th>\n",
       "      <th>0.0</th>\n",
       "      <th>0.0</th>\n",
       "      <td>2</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4899.0980000.01211.0</th>\n",
       "      <th>13.0</th>\n",
       "      <th>0.0</th>\n",
       "      <th>1.0</th>\n",
       "      <td>1</td>\n",
       "      <td>100.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>2204887 rows × 3 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                                    n_simulants  pct_male  pct_trans\n",
       "geoid                age  race eth                                  \n",
       "481.0950100.01000.0  11.0 0.0  1.0            1       0.0        0.0\n",
       "                     13.0 0.0  0.0            1       0.0        0.0\n",
       "                          5.0  1.0            2      50.0        0.0\n",
       "                     15.0 1.0  0.0            2      50.0        0.0\n",
       "...                                         ...       ...        ...\n",
       "4899.010804.03032.0  13.0 1.0  0.0            1     100.0        0.0\n",
       "4899.010804.03034.0  12.0 7.0  0.0            1     100.0        0.0\n",
       "                     13.0 0.0  0.0            2       0.0        0.0\n",
       "4899.0980000.01211.0 13.0 0.0  1.0            1     100.0        0.0\n",
       "\n",
       "[2204887 rows x 3 columns]"
      ]
     },
     "execution_count": 49,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# without reidentification to link on, there is still a risk of identifying a block with a trans kid\n",
    "# by finding blocks where there was a single kids of a given age in 2010 and that age+10 in 2020 and\n",
    "# different reported sex\n",
    "df['reported_male_2010'] = (df.reported_sex_2010 == 0)\n",
    "df['geoid'] = df.geoid_2010\n",
    "df['age'] = df.age_2010 + 10 # add ten to make merge easier\n",
    "g = df.groupby(['geoid', 'age',\n",
    "                'race', 'eth'])\n",
    "\n",
    "df_a = pd.DataFrame({'n_simulants': g.pweight.sum()})\n",
    "df_a['pct_male'] = 100*g.reported_male_2010.mean()\n",
    "df_a['pct_trans'] = 100*g.trans.mean()\n",
    "df_a"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 50,
   "id": "c3f0c7e9",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "1239637"
      ]
     },
     "execution_count": 50,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df['reported_male_2020'] = (df.reported_sex_2020 == 0)\n",
    "df['geoid'] = df.geoid_2020\n",
    "df['age'] = df.age_2020\n",
    "df['discordant_sex'] = (df.reported_sex_2010 != df.reported_sex_2020)\n",
    "\n",
    "g = df.groupby(['geoid', 'age',\n",
    "                'race', 'eth'])\n",
    "\n",
    "df_b = pd.DataFrame({'n_simulants': g.pweight.sum()})\n",
    "df_b['pct_male'] = 100*g.reported_male_2020.mean()\n",
    "df_b['pct_trans'] = 100*g.trans.mean()\n",
    "df_b['pct_discordant_sex'] = 100*g.discordant_sex.mean()\n",
    "n_unique_2020 = sum(df_b.n_simulants == 1)\n",
    "n_unique_2020"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 51,
   "id": "5f4d69ff",
   "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>state</th>\n",
       "      <th>county</th>\n",
       "      <th>tract</th>\n",
       "      <th>block</th>\n",
       "      <th>age</th>\n",
       "      <th>sex</th>\n",
       "      <th>race</th>\n",
       "      <th>eth</th>\n",
       "      <th>n</th>\n",
       "      <th>geoid</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>48</td>\n",
       "      <td>1.0</td>\n",
       "      <td>950100.0</td>\n",
       "      <td>1000.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>5</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>481.0950100.01000.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>48</td>\n",
       "      <td>1.0</td>\n",
       "      <td>950100.0</td>\n",
       "      <td>1000.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>5</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>481.0950100.01000.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>48</td>\n",
       "      <td>1.0</td>\n",
       "      <td>950100.0</td>\n",
       "      <td>1000.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>5</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>481.0950100.01000.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>48</td>\n",
       "      <td>1.0</td>\n",
       "      <td>950100.0</td>\n",
       "      <td>1000.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>481.0950100.01000.0</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>11717815</th>\n",
       "      <td>48</td>\n",
       "      <td>507.0</td>\n",
       "      <td>950302.0</td>\n",
       "      <td>5026.0</td>\n",
       "      <td>59.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>48507.0950302.05026.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11717816</th>\n",
       "      <td>48</td>\n",
       "      <td>507.0</td>\n",
       "      <td>950302.0</td>\n",
       "      <td>5026.0</td>\n",
       "      <td>59.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>48507.0950302.05026.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11717817</th>\n",
       "      <td>48</td>\n",
       "      <td>507.0</td>\n",
       "      <td>950302.0</td>\n",
       "      <td>5026.0</td>\n",
       "      <td>64.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>48507.0950302.05026.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11717818</th>\n",
       "      <td>48</td>\n",
       "      <td>507.0</td>\n",
       "      <td>950302.0</td>\n",
       "      <td>5026.0</td>\n",
       "      <td>71.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>48507.0950302.05026.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>11717819 rows × 10 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "          state  county     tract   block   age  sex  race  eth    n  \\\n",
       "0            48     1.0  950100.0  1000.0   2.0  1.0     5  1.0  1.0   \n",
       "1            48     1.0  950100.0  1000.0   3.0  0.0     5  1.0  1.0   \n",
       "2            48     1.0  950100.0  1000.0   4.0  1.0     5  1.0  1.0   \n",
       "3            48     1.0  950100.0  1000.0   5.0  1.0     0  0.0  1.0   \n",
       "...         ...     ...       ...     ...   ...  ...   ...  ...  ...   \n",
       "11717815     48   507.0  950302.0  5026.0  59.0  0.0     0  0.0  1.0   \n",
       "11717816     48   507.0  950302.0  5026.0  59.0  1.0     0  1.0  1.0   \n",
       "11717817     48   507.0  950302.0  5026.0  64.0  1.0     0  1.0  1.0   \n",
       "11717818     48   507.0  950302.0  5026.0  71.0  0.0     0  1.0  1.0   \n",
       "\n",
       "                          geoid  \n",
       "0           481.0950100.01000.0  \n",
       "1           481.0950100.01000.0  \n",
       "2           481.0950100.01000.0  \n",
       "3           481.0950100.01000.0  \n",
       "...                         ...  \n",
       "11717815  48507.0950302.05026.0  \n",
       "11717816  48507.0950302.05026.0  \n",
       "11717817  48507.0950302.05026.0  \n",
       "11717818  48507.0950302.05026.0  \n",
       "\n",
       "[11717819 rows x 10 columns]"
      ]
     },
     "execution_count": 51,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "dhc"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 52,
   "id": "7a190b3a",
   "metadata": {},
   "outputs": [],
   "source": [
    "dhc['male'] = (dhc.sex == 0)\n",
    "# dhc['male'] = (np.random.uniform(size=len(dhc)) < 0.5) # a) Take the actual DHC, ignore the sex attributes, generate sex randomly, and use that as the \"DHC\" in the simulation.\n",
    "g = dhc.groupby(['geoid', 'age', 'race', 'eth'])\n",
    "df_c = pd.DataFrame({'n_simulants':g.n.sum()})\n",
    "df_c['pct_male'] = 100 * g.male.mean()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 53,
   "id": "a6fde235",
   "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></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th>n_simulants</th>\n",
       "      <th>pct_male</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>geoid</th>\n",
       "      <th>age</th>\n",
       "      <th>race</th>\n",
       "      <th>eth</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th rowspan=\"4\" valign=\"top\">481.0950100.01000.0</th>\n",
       "      <th>2.0</th>\n",
       "      <th>5</th>\n",
       "      <th>1.0</th>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3.0</th>\n",
       "      <th>5</th>\n",
       "      <th>1.0</th>\n",
       "      <td>1.0</td>\n",
       "      <td>100.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4.0</th>\n",
       "      <th>5</th>\n",
       "      <th>1.0</th>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5.0</th>\n",
       "      <th>0</th>\n",
       "      <th>0.0</th>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <th>...</th>\n",
       "      <th>...</th>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"3\" valign=\"top\">4899.0980000.01428.0</th>\n",
       "      <th>58.0</th>\n",
       "      <th>0</th>\n",
       "      <th>0.0</th>\n",
       "      <td>1.0</td>\n",
       "      <td>100.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>63.0</th>\n",
       "      <th>0</th>\n",
       "      <th>0.0</th>\n",
       "      <td>1.0</td>\n",
       "      <td>100.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>73.0</th>\n",
       "      <th>0</th>\n",
       "      <th>0.0</th>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4899.0980000.01538.0</th>\n",
       "      <th>14.0</th>\n",
       "      <th>9</th>\n",
       "      <th>0.0</th>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>10811822 rows × 2 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                                    n_simulants  pct_male\n",
       "geoid                age  race eth                       \n",
       "481.0950100.01000.0  2.0  5    1.0          1.0       0.0\n",
       "                     3.0  5    1.0          1.0     100.0\n",
       "                     4.0  5    1.0          1.0       0.0\n",
       "                     5.0  0    0.0          1.0       0.0\n",
       "...                                         ...       ...\n",
       "4899.0980000.01428.0 58.0 0    0.0          1.0     100.0\n",
       "                     63.0 0    0.0          1.0     100.0\n",
       "                     73.0 0    0.0          1.0       0.0\n",
       "4899.0980000.01538.0 14.0 9    0.0          1.0       0.0\n",
       "\n",
       "[10811822 rows x 2 columns]"
      ]
     },
     "execution_count": 53,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_c"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 54,
   "id": "e59d0f8b",
   "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></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th>n_simulants_2010</th>\n",
       "      <th>pct_male_2010</th>\n",
       "      <th>pct_trans_2010</th>\n",
       "      <th>n_simulants_2020</th>\n",
       "      <th>pct_male_2020</th>\n",
       "      <th>pct_trans_2020</th>\n",
       "      <th>pct_discordant_sex</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>geoid</th>\n",
       "      <th>age</th>\n",
       "      <th>race</th>\n",
       "      <th>eth</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>481.0950100.01022.0</th>\n",
       "      <th>11.0</th>\n",
       "      <th>0.0</th>\n",
       "      <th>0.0</th>\n",
       "      <td>1</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"3\" valign=\"top\">481.0950100.01073.0</th>\n",
       "      <th>11.0</th>\n",
       "      <th>0.0</th>\n",
       "      <th>0.0</th>\n",
       "      <td>1</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>100.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13.0</th>\n",
       "      <th>0.0</th>\n",
       "      <th>0.0</th>\n",
       "      <td>1</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17.0</th>\n",
       "      <th>0.0</th>\n",
       "      <th>0.0</th>\n",
       "      <td>1</td>\n",
       "      <td>100.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <th>...</th>\n",
       "      <th>...</th>\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",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"2\" valign=\"top\">4899.010804.03018.0</th>\n",
       "      <th rowspan=\"2\" valign=\"top\">16.0</th>\n",
       "      <th>0.0</th>\n",
       "      <th>0.0</th>\n",
       "      <td>1</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>100.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5.0</th>\n",
       "      <th>1.0</th>\n",
       "      <td>1</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>100.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4899.010804.03019.0</th>\n",
       "      <th>16.0</th>\n",
       "      <th>0.0</th>\n",
       "      <th>0.0</th>\n",
       "      <td>1</td>\n",
       "      <td>100.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>100.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4899.010804.03030.0</th>\n",
       "      <th>11.0</th>\n",
       "      <th>0.0</th>\n",
       "      <th>0.0</th>\n",
       "      <td>1</td>\n",
       "      <td>100.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>100.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>94429 rows × 7 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                                   n_simulants_2010  pct_male_2010  \\\n",
       "geoid               age  race eth                                    \n",
       "481.0950100.01022.0 11.0 0.0  0.0                 1            0.0   \n",
       "481.0950100.01073.0 11.0 0.0  0.0                 1            0.0   \n",
       "                    13.0 0.0  0.0                 1            0.0   \n",
       "                    17.0 0.0  0.0                 1          100.0   \n",
       "...                                             ...            ...   \n",
       "4899.010804.03018.0 16.0 0.0  0.0                 1            0.0   \n",
       "                         5.0  1.0                 1            0.0   \n",
       "4899.010804.03019.0 16.0 0.0  0.0                 1          100.0   \n",
       "4899.010804.03030.0 11.0 0.0  0.0                 1          100.0   \n",
       "\n",
       "                                   pct_trans_2010  n_simulants_2020  \\\n",
       "geoid               age  race eth                                     \n",
       "481.0950100.01022.0 11.0 0.0  0.0             0.0               1.0   \n",
       "481.0950100.01073.0 11.0 0.0  0.0             0.0               1.0   \n",
       "                    13.0 0.0  0.0             0.0               1.0   \n",
       "                    17.0 0.0  0.0             0.0               1.0   \n",
       "...                                           ...               ...   \n",
       "4899.010804.03018.0 16.0 0.0  0.0             0.0               1.0   \n",
       "                         5.0  1.0             0.0               1.0   \n",
       "4899.010804.03019.0 16.0 0.0  0.0             0.0               1.0   \n",
       "4899.010804.03030.0 11.0 0.0  0.0             0.0               1.0   \n",
       "\n",
       "                                   pct_male_2020  pct_trans_2020  \\\n",
       "geoid               age  race eth                                  \n",
       "481.0950100.01022.0 11.0 0.0  0.0            0.0             0.0   \n",
       "481.0950100.01073.0 11.0 0.0  0.0          100.0             0.0   \n",
       "                    13.0 0.0  0.0            0.0             NaN   \n",
       "                    17.0 0.0  0.0            0.0             0.0   \n",
       "...                                          ...             ...   \n",
       "4899.010804.03018.0 16.0 0.0  0.0          100.0             NaN   \n",
       "                         5.0  1.0          100.0             NaN   \n",
       "4899.010804.03019.0 16.0 0.0  0.0          100.0             NaN   \n",
       "4899.010804.03030.0 11.0 0.0  0.0          100.0             0.0   \n",
       "\n",
       "                                   pct_discordant_sex  \n",
       "geoid               age  race eth                      \n",
       "481.0950100.01022.0 11.0 0.0  0.0                 0.0  \n",
       "481.0950100.01073.0 11.0 0.0  0.0                 0.0  \n",
       "                    13.0 0.0  0.0                 NaN  \n",
       "                    17.0 0.0  0.0                 0.0  \n",
       "...                                               ...  \n",
       "4899.010804.03018.0 16.0 0.0  0.0                 NaN  \n",
       "                         5.0  1.0                 NaN  \n",
       "4899.010804.03019.0 16.0 0.0  0.0                 NaN  \n",
       "4899.010804.03030.0 11.0 0.0  0.0                 0.0  \n",
       "\n",
       "[94429 rows x 7 columns]"
      ]
     },
     "execution_count": 54,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_abc = df_a[df_a.n_simulants==1].copy()\n",
    "df_abc.columns = ['n_simulants_2010', 'pct_male_2010', 'pct_trans_2010']\n",
    "\n",
    "df_abc['n_simulants_2020'] = df_c[df_c.n_simulants==1].n_simulants\n",
    "df_abc['pct_male_2020'] = df_c[df_c.n_simulants==1].pct_male\n",
    "df_abc = df_abc.dropna()\n",
    "\n",
    "df_abc['pct_trans_2020'] = df_b.pct_trans\n",
    "df_abc['pct_discordant_sex'] = df_b.pct_discordant_sex\n",
    "df_abc"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 55,
   "id": "9b051afc",
   "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></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th>n_simulants_2010</th>\n",
       "      <th>pct_male_2010</th>\n",
       "      <th>pct_trans_2010</th>\n",
       "      <th>n_simulants_2020</th>\n",
       "      <th>pct_male_2020</th>\n",
       "      <th>pct_trans_2020</th>\n",
       "      <th>pct_discordant_sex</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>geoid</th>\n",
       "      <th>age</th>\n",
       "      <th>race</th>\n",
       "      <th>eth</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>481.0950100.01022.0</th>\n",
       "      <th>11.0</th>\n",
       "      <th>0.0</th>\n",
       "      <th>0.0</th>\n",
       "      <td>1</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"3\" valign=\"top\">481.0950100.01073.0</th>\n",
       "      <th>11.0</th>\n",
       "      <th>0.0</th>\n",
       "      <th>0.0</th>\n",
       "      <td>1</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>100.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13.0</th>\n",
       "      <th>0.0</th>\n",
       "      <th>0.0</th>\n",
       "      <td>1</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17.0</th>\n",
       "      <th>0.0</th>\n",
       "      <th>0.0</th>\n",
       "      <td>1</td>\n",
       "      <td>100.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <th>...</th>\n",
       "      <th>...</th>\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",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"2\" valign=\"top\">4899.010804.03018.0</th>\n",
       "      <th rowspan=\"2\" valign=\"top\">16.0</th>\n",
       "      <th>0.0</th>\n",
       "      <th>0.0</th>\n",
       "      <td>1</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>100.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5.0</th>\n",
       "      <th>1.0</th>\n",
       "      <td>1</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>100.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4899.010804.03019.0</th>\n",
       "      <th>16.0</th>\n",
       "      <th>0.0</th>\n",
       "      <th>0.0</th>\n",
       "      <td>1</td>\n",
       "      <td>100.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>100.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4899.010804.03030.0</th>\n",
       "      <th>11.0</th>\n",
       "      <th>0.0</th>\n",
       "      <th>0.0</th>\n",
       "      <td>1</td>\n",
       "      <td>100.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>100.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>94429 rows × 7 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                                   n_simulants_2010  pct_male_2010  \\\n",
       "geoid               age  race eth                                    \n",
       "481.0950100.01022.0 11.0 0.0  0.0                 1            0.0   \n",
       "481.0950100.01073.0 11.0 0.0  0.0                 1            0.0   \n",
       "                    13.0 0.0  0.0                 1            0.0   \n",
       "                    17.0 0.0  0.0                 1          100.0   \n",
       "...                                             ...            ...   \n",
       "4899.010804.03018.0 16.0 0.0  0.0                 1            0.0   \n",
       "                         5.0  1.0                 1            0.0   \n",
       "4899.010804.03019.0 16.0 0.0  0.0                 1          100.0   \n",
       "4899.010804.03030.0 11.0 0.0  0.0                 1          100.0   \n",
       "\n",
       "                                   pct_trans_2010  n_simulants_2020  \\\n",
       "geoid               age  race eth                                     \n",
       "481.0950100.01022.0 11.0 0.0  0.0             0.0               1.0   \n",
       "481.0950100.01073.0 11.0 0.0  0.0             0.0               1.0   \n",
       "                    13.0 0.0  0.0             0.0               1.0   \n",
       "                    17.0 0.0  0.0             0.0               1.0   \n",
       "...                                           ...               ...   \n",
       "4899.010804.03018.0 16.0 0.0  0.0             0.0               1.0   \n",
       "                         5.0  1.0             0.0               1.0   \n",
       "4899.010804.03019.0 16.0 0.0  0.0             0.0               1.0   \n",
       "4899.010804.03030.0 11.0 0.0  0.0             0.0               1.0   \n",
       "\n",
       "                                   pct_male_2020  pct_trans_2020  \\\n",
       "geoid               age  race eth                                  \n",
       "481.0950100.01022.0 11.0 0.0  0.0            0.0             0.0   \n",
       "481.0950100.01073.0 11.0 0.0  0.0          100.0             0.0   \n",
       "                    13.0 0.0  0.0            0.0             NaN   \n",
       "                    17.0 0.0  0.0            0.0             0.0   \n",
       "...                                          ...             ...   \n",
       "4899.010804.03018.0 16.0 0.0  0.0          100.0             NaN   \n",
       "                         5.0  1.0          100.0             NaN   \n",
       "4899.010804.03019.0 16.0 0.0  0.0          100.0             NaN   \n",
       "4899.010804.03030.0 11.0 0.0  0.0          100.0             0.0   \n",
       "\n",
       "                                   pct_discordant_sex  \n",
       "geoid               age  race eth                      \n",
       "481.0950100.01022.0 11.0 0.0  0.0                 0.0  \n",
       "481.0950100.01073.0 11.0 0.0  0.0                 0.0  \n",
       "                    13.0 0.0  0.0                 NaN  \n",
       "                    17.0 0.0  0.0                 0.0  \n",
       "...                                               ...  \n",
       "4899.010804.03018.0 16.0 0.0  0.0                 NaN  \n",
       "                         5.0  1.0                 NaN  \n",
       "4899.010804.03019.0 16.0 0.0  0.0                 NaN  \n",
       "4899.010804.03030.0 11.0 0.0  0.0                 0.0  \n",
       "\n",
       "[94429 rows x 7 columns]"
      ]
     },
     "execution_count": 55,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_abc.dropna(subset=['n_simulants_2010'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 56,
   "id": "d68a5442",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0.000000      22982\n",
       "NaN           22616\n",
       "100.000000       69\n",
       "50.000000        68\n",
       "              ...  \n",
       "20.000000        11\n",
       "12.500000         3\n",
       "16.666667         2\n",
       "14.285714         2\n",
       "Name: pct_trans_2020, Length: 10, dtype: int64"
      ]
     },
     "execution_count": 56,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_abc[df_abc.pct_male_2010 != df_abc.pct_male_2020].pct_trans_2020.value_counts(dropna=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 57,
   "id": "31d90e5a",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0.000000      23096\n",
       "NaN           22616\n",
       "50.000000        29\n",
       "100.000000       25\n",
       "              ...  \n",
       "20.000000         5\n",
       "14.285714         2\n",
       "12.500000         2\n",
       "16.666667         1\n",
       "Name: pct_discordant_sex, Length: 10, dtype: int64"
      ]
     },
     "execution_count": 57,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_abc[df_abc.pct_male_2010 != df_abc.pct_male_2020].pct_discordant_sex.value_counts(dropna=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 58,
   "id": "efc170a5",
   "metadata": {},
   "outputs": [],
   "source": [
    "n_scenario_4 = sum(\n",
    "    df_abc[df_abc.pct_male_2010 != df_abc.pct_male_2020].pct_trans_2020 > 0\n",
    "    )\n",
    "N_scenario_4 = sum(df_abc.pct_male_2010 != df_abc.pct_male_2020)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 59,
   "id": "45725e87",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(194, 45598)"
      ]
     },
     "execution_count": 59,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "n_scenario_4, N_scenario_4-n_scenario_4"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 60,
   "id": "0b4d9d03",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0.00423654786862334"
      ]
     },
     "execution_count": 60,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "n_scenario_4 / N_scenario_4"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 61,
   "id": "d3218a51",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0.0033665635091421433"
      ]
     },
     "execution_count": 61,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# what is the rate of identifying trans kids if you just pick strata at random\n",
    "t = df.groupby(['geoid', 'age',\n",
    "                'race', 'eth']).discordant_sex.mean()\n",
    "(t>0).mean()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "2a2c59b5",
   "metadata": {},
   "outputs": [],
   "source": [
    "# HACK to avoid re-running all\n",
    "n_scenario_4 = 194"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "b664e35b",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "70.47184170471841"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "n_scenario_3 = 657\n",
    "100 * (n_scenario_3 - n_scenario_4) / n_scenario_3  # pct decrease from scenario 3 to 4"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "44f76512",
   "metadata": {},
   "source": [
    "# Double check that I mapped race correctly\n",
    "\n",
    "And might as well check the coding of sex and ethnicity while I'm at it."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 63,
   "id": "57654fe8",
   "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>dhc</th>\n",
       "      <th>sf1</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>race</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0.0</th>\n",
       "      <td>17701558.0</td>\n",
       "      <td>1994695</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1.0</th>\n",
       "      <td>2979629.0</td>\n",
       "      <td>390564</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2.0</th>\n",
       "      <td>170962.0</td>\n",
       "      <td>23603</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3.0</th>\n",
       "      <td>964608.0</td>\n",
       "      <td>99794</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>59.0</th>\n",
       "      <td>8.0</td>\n",
       "      <td>43</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>60.0</th>\n",
       "      <td>16.0</td>\n",
       "      <td>34</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>61.0</th>\n",
       "      <td>2.0</td>\n",
       "      <td>25</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>62.0</th>\n",
       "      <td>92.0</td>\n",
       "      <td>19</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>63 rows × 2 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "             dhc      sf1\n",
       "race                     \n",
       "0.0   17701558.0  1994695\n",
       "1.0    2979629.0   390564\n",
       "2.0     170962.0    23603\n",
       "3.0     964608.0    99794\n",
       "...          ...      ...\n",
       "59.0         8.0       43\n",
       "60.0        16.0       34\n",
       "61.0         2.0       25\n",
       "62.0        92.0       19\n",
       "\n",
       "[63 rows x 2 columns]"
      ]
     },
     "execution_count": 63,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "s_dhc_race = dhc.groupby('race').n.sum()\n",
    "s_sf1_race = df_all.groupby('race').pweight.sum()\n",
    "t = pd.DataFrame({'dhc': s_dhc_race,\n",
    "                 'sf1': s_sf1_race,})\n",
    "t"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 64,
   "id": "8cc8fb3e",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "image/png": "iVBORw0KGgoAAAANSUhEUgAAAXcAAAD8CAYAAACMwORRAAAAOXRFWHRTb2Z0d2FyZQBNYXRwbG90bGliIHZlcnNpb24zLjUuMiwgaHR0cHM6Ly9tYXRwbG90bGliLm9yZy8qNh9FAAAACXBIWXMAAAsTAAALEwEAmpwYAAAbQklEQVR4nO3dcWyc933f8fdXNGnRoisudkBAtBwptcFUlbCw4qIkRodj0YZyE8WcGsRWumIaVGsO4ALtAi5ikyAulkAahGBbUmOGurgKsEy0p2iEZKsROsiHBLXT2RoTSJqjTQvQWKcAbmxRGCV6ounv/uAddTw+x3vu7rnnuefh5wUI9vN77p7nywP1vUff5/v8fubuiIhItqxJOgAREYmekruISAYpuYuIZJCSu4hIBim5i4hkkJK7iEgG3ZF0AAD33nuvb9q0iRs3brBu3bqkwwlN8bZWmuJNU6ygeFstrnjPnTv3S3d/f+BOd0/8z/bt293d/aWXXvI0UbytlaZ40xSru+JttbjiBV7zKnlVZRkRkQxKNLmb2S4zO3L9+vUkwxARyZxEk7u7n3L3/evXr08yDBGRzFFZRkQkg9qiW0ZEZLWZnCpw+Mwlrk7PsqG3m7GRAUYH+yM7vpK7iEjMJqcKjJ84z+zcPACF6VnGT5wHiCzBqywjIhKzw2cuLSb2ktm5eQ6fuRTZOdQtIyISs6vTs3WNN0LdMiIiMdvQ213XeCNUlhERidnYyADdnR1Lxro7OxgbGYjsHLqhKiISs9JNU3XLiIhkzOhgf6TJvJLKMiIiGaTkLiKSQUruIiIZFHnN3czWAP8a+BUW5hr+TtTnEBGRlYW6cjezZ83sTTO7UDG+08wumdllMztQHH4EuA+YA65EG66IiIQRtixzFNhZPmBmHcDTwMPAFmCPmW0BBoCX3f1fAp+PLlQREQkrVHJ39x8Ab1cMfwS47O4/c/dbwAQLV+1XgGvF18wjIiKxs4Vl+EK80GwT8IK7by1ufwbY6e5/WNz+A2AH8K+AbwE3gZ+6+9NVjrcf2A/Q19e3fWJigpmZGXp6epr7iWKkeFsrTfGmKVZQvK0WV7zDw8Pn3H0oaF/kN1Td/SawL8TrjpjZL4Bdd9999/ZcLkc+nyeXy0UdUsso3tZKU7xpihUUb6u1Q7zNtEIWgI1l2/cVx0LTxGEiIq3RTHJ/FXjQzDabWRfwGHCyngNoyl8RkdYI2wp5DHgFGDCzK2a2z93fBZ4EzgCvA8+7+8V6Tq4rdxGR1ghVc3f3PVXGTwOnGz25me0Cdj3wwAONHkJERAJosQ4RkQzS3DIiIhmkNVRFRDJIZRkRkQxSWUZEJINUlhERyaBE11B191PAqaGhoceTjENEsm1yqrC4GHXvXZ24w/XZuZYsTN0utEC2iGTa5FSB8RPnmZ1bmKT22s25xX2F6VnGT5wHyFyCV1lGRDLt8JlLi4k9yOzcPIfPXIoxonioW0ZEMu3q9Gwkr0kbdcuISKZt6O2O5DVpo+QuIpk2NjJAd2dH1f3dnR2MjQzEGFE8dENVRDKtdKNU3TIx0qyQIhKH0cH+TCbwleiGqohIBqnmLiKSQUruIiIZpOQuIpJBSu4iIhmk6QdERDJI3TIiIhmksoyISAYpuYuIZJCSu4hIBim5i4hkkJK7iEgGKbmLiGRQ5MndzHJm9kMze8bMclEfX0REaguV3M3sWTN708wuVIzvNLNLZnbZzA4Uhx2YAdYCV6INV0RWi8mpAg8dOsvmAy/yhfxNJqcKSYeUKmGv3I8CO8sHzKwDeBp4GNgC7DGzLcAP3f1h4IvAn0UXqoisFpNTBcZPnKcwPYsDb73jjJ84rwRfh1DJ3d1/ALxdMfwR4LK7/8zdbwETwCPu/l5x/zXgzsgiFZFV4/CZS8zOzS8Zm52b5/CZSwlFlD7m7uFeaLYJeMHdtxa3PwPsdPc/LG7/AbADOAuMAL3Af3D3fJXj7Qf2A/T19W2fmJhgZmaGnp6eZn6eWCne1kpTvGmKFdo/3r3fv1F139Gd62KMpDFxfb7Dw8Pn3H0oaF/ky+y5+wngRIjXHTGzXwC77r777u25XI58Pk8ul4s6pJZRvK2VpnjTFCu0f7z9PzpLYXp2+Xhvd1vHXdIOn28z3TIFYGPZ9n3FsdA0cZiIBBkbGaC7s2PJWHdnB2MjAwlFlD7NJPdXgQfNbLOZdQGPASfrOYCm/BWRIKOD/RzcvY3+3m4MuGetcXD3tlW3yHUzQpVlzOwYkAPuNbMrwFfd/dtm9iRwBugAnnX3i/Wc3N1PAaeGhoYery9sEcm60cH+xWSez+fJKbHXJVRyd/c9VcZPA6cbPbmZ7QJ2PfDAA40eQkREAmixDhGRDNLcMiIiGaQ1VEVEMkhlGRGRDFJZRkQkg1SWERHJIJVlREQyKPK5ZUQk2yanChw+c4mr07Ns6O1mbGRAT462oUSTux5iEkmX0jzrpel4C9OzjJ84D6AE32ZUlhGR0DTPenqoW0ZEQrsaMA3vSuOSHCV3EQltQ293XeOSHCV3kVXo5atzi4tPP3TobOi1STXPenrohqrIKjM5VeDohVvcKq52XM9N0dJ+dcu0v0STu+ZzF4nf4TOXFhN7SemmaClJr9TuWD7PurQv9bmLrDK1boqq3TEbVHMXWWVq3RRVu2M2KLmLrDJjIwN0VfzNL78pqnbHbFByF1llRgf72bu1a3Hx6f7e7iWLT6vdMRvULSOySpTfJH3fWuMrj2wJrKGPjQwsqbmD2h3TSNMPiKwCpZukhelZHHjrHWf8xPnA/vbRwX4O7t5W9cpe0kHdMiKrwEo3SYOSttod0081d5FVQDdJVx8ld5FVQDdJVx8ld5FVQHPCrD6quYusApVzwix0y+gmaZbpyl1EJINaktzNbJ2ZvWZmn2rF8UWkPvW0Qko2hEruZvasmb1pZhcqxnea2SUzu2xmB8p2fRF4PspARaRxmi9m9Ql75X4U2Fk+YGYdwNPAw8AWYI+ZbTGz3wH+J/BmhHGKSBPUCrn6mLuHe6HZJuAFd99a3P4Y8JS7jxS3x4sv7QHWsZDwZ4F/4u7vBRxvP7AfoK+vb/vExAQzMzP09PQ09xPFSPG2VpribfdYv5C/yVvvLP+7fs9a4xu5uxKIqD7t/vlWiive4eHhc+4+FLSvmW6ZfuCNsu0rwA53fxLAzPYCvwxK7ADufgQ4AjA0NOS5XI58Pk8ul2sipHgp3tZKU7ztHutX1hcC54v5yiPbyKWgY6bdP99K7RBvy1oh3f1orddo4jCReKgVcvVpJrkXgI1l2/cVx0LTMnsi8SmfLyafz6fiil0a10wr5KvAg2a22cy6gMeAk/UcwMx2mdmR69evNxGGiIhUCtsKeQx4BRgwsytmts/d3wWeBM4ArwPPu/vFek6uKX9FRFojVFnG3fdUGT8NnG705Kq5i4i0hhbrEGkjk1MFHjp0ls0HXuShQ2f1BKk0TBOHibSJ0hQBpXbFwvQs4yfOA6irReqW6JW7bqiK3KYpAiRKKsuItAlNESBR0pS/Im1CqyVJlFSWEWkTWi1JoqSyjEibGB3s5+DubfT3dmNAf283B3drigBpjLplRNpI+RQBIs1QWUZEJINUlhERySB1y4iIZJBq7iIJmZwqLM6vvqG3m7GRAdXbJTJK7iIxKiX0wvQsBpQWvtNUAxI13VAViUlp7phC8YnTyhVNNdWARCnRK3etxCRZFVRyCZo7ppKmGpCoqCwjEoGXr87xpUNnuTo9y/ruTm7cepe5+YVr81LJpVZiB001INFRchdp0uRUgaMXbnHrvYXt6dm5Za+ZnZunw4x5ryzG3KapBiRKSu4iTTp85tJiYl/JvDvdnR1LruBLN1X71S0jEVNyF2lSIWSdvL+s9q72R2m1RJO71lCVLKhVboGFK/ThD71fc8dIbDT9gEiTaiV2WCi9fO9cQWuiSmw0/YBIk/pDdrioj13ipOQu0qSxkQG6Qv5NUh+7xEXJXaRJo4P97N3atWSRjd7uzsDXqo9d4qJuGZEIfHxDJ3/6udzidmmqgfK2R/WxS5yU3EVqaGT2xtJ+tT1KUpTcRVZQeQVez+yNanuUJEVeczezXzOzZ8zsuJl9Purji8QpaLIvdb1IGoRK7mb2rJm9aWYXKsZ3mtklM7tsZgcA3P11d38C+CzwUPQhi8SnWneLul6k3YW9cj8K7CwfMLMO4GngYWALsMfMthT3fRp4ETgdWaQiCajW3aKuF2l35iGergMws03AC+6+tbj9MeApdx8pbo8DuPvBsve86O6frHK8/cB+gL6+vu0TExPMzMzQ09PTxI8TL8XbWu0Q78tX55bM+AjQtQb2bu3i4xtutzu2Q6z1ULytFVe8w8PD59x9KGhfMzdU+4E3yravADvMLAfsBu5khSt3dz8CHAEYGhryXC5HPp8nl8s1EVK8FG9rtSLeoM4XqN7VkgO2hOiW0WfbWoq3fpF3y7h7HsiHea0mDpM4BXW+jB3/CTjMvbd0YQ243Q2jrhdJo2a6ZQrAxrLt+4pjoWniMIlTUOfL3LwvJvYSdcNIFjST3F8FHjSzzWbWBTwGnKznAFogW+JUT4eLumEk7cK2Qh4DXgEGzOyKme1z93eBJ4EzwOvA8+5+sZ6T68pd4lRPh4u6YSTtQtXc3X1PlfHTNNHuqJq7xGVyqsDNW+8G7lsDlK+SpzlgJAsSnX7A3U8Bp4aGhh5PMg7JrsmpAk+dvBi4aHVJR4fxK113cH12TnPASGZobhnJrMmpAmP/5SfLbphWmpt31t15Bz/+6idiikyk9bSGqmTWUycv1kzsJVenZxua/VGkXWkNVcmslUoxle7q6mD8xHkK07M4t/vdteappJXKMpIZlVfe9bh5a57Ka/xSv7uu3iWNVJaRTAh6+rQe1Yo36neXtFJZRjIh6OnTenSYBY6r313SSgtkSyasdIVdStv9vd3804/eT3dnx5L93Z0d7NmxMXBc/e6SVirLSCZs6O2uWopxFhL73xz4LQCGPvC+wK6YauMiaaSHmCQTxkYGltTcK5Vf2Veb5VGzP0qWqCwjmTA62M/B3dtUOxcpUnKXzBgd7Ocbn/2Hqp2LoD53yZhSWUW1c1ntdENVMke1cxH1uYuIZJJq7iIiGaTkLiKSQUruIiIZpOQuIpJBiSZ3M9tlZkeuX7+eZBgiIpmjbhkRkQxSWUZEJIOU3EVEMkjTD0gitBi1SGspuUvsgpbEGz9xHkAJXiQiKstI7IKWxCstRi0i0VByl9hVWxJPi1GLRKclyd3MRs3sL8zsOTP7RCvOIelVbeEMLaghEp3Qyd3MnjWzN83sQsX4TjO7ZGaXzewAgLtPuvvjwBPAo9GGLO1ucqrAQ4fOsvnAizx06CyTU4Ul+8dGBrSghkiL1XPlfhTYWT5gZh3A08DDwBZgj5ltKXvJl4v7ZZUo3SwtTM/i3L5ZWp7gS0vi9fd2YywsXn1w9zbdTBWJkLl7+BebbQJecPetxe2PAU+5+0hxe7z40kPFP3/t7v+tyrH2A/sB+vr6tk9MTDAzM0NPT0+jP0vsFO9yX8jf5K13lv9O3bPW+EburrqOlabPN02xguJttbjiHR4ePufuQ0H7mm2F7AfeKNu+AuwA/gj4bWC9mT3g7s9UvtHdjwBHAIaGhjyXy5HP58nlck2GFB/Fu9zb338xePwdr/vcafp80xQrKN5Wa4d4W9Ln7u7fBL5Z63VaZi97NvR2UwjoetHNUpF4NdstUwA2lm3fVxwLRROHZU/QzVKAm7feXXZjVURap9nk/irwoJltNrMu4DHgZNg3a8rfbFrbufzX6trNuWU3VkWkdepphTwGvAIMmNkVM9vn7u8CTwJngNeB5939Ythj6so9W0qdMtduzgXu11OoIvEJXXN39z1Vxk8Dpxs5uWru6VY5+de1G/+P2bn3VnyPnkIViYcW65CGBPWz36yR2EE3VkXiorllpCFBk3+FoadQReKhNVSlIY2UVwxN6SsSF5VlElZrHpZ21Uh5Jfyz0CLSrMwu1hHlSj+tWjUoLYtWBP38YyMD/PFzP67rOP2qt4vEJpNlmTCTVyVxrErVFq34s1Ohu0lbrtrPD9Db3Vn1fVaxrVkfReKVybJMlCv9tHLVoGp162s35xIrz3x58jy/On6aTQde5FfHT/Ol/3q+6s//1Kd/PXDq3n/36If5t49+WLM+iiQok2WZKFf6WflY6+o+Xrlq87DAwpdK3Mnwy5Pn+U8/+vni9rw7N24Fd8RcnZ5djK9ayUrJXCQ5iSb3Vj3EFOXkVY0eK0ydfqW6dRIP+xz72zdqv6io9POPDvYriYu0oUyWZaJc6aeRY4Wt048O9letWyfxsM98yLn9m6mfp7U7SCRtMvkQU5Qr/TRyrHrq9EF1awOGP/T+umOtppRQ937/xooJtcMqb4PejieKzzLMl1558v9C/qaSv0iDMllzh2jLBfUeq56a/+hgP6/93dt890c/X+wDd+B75woMfeB9Tf8M9bRb7tmxcUnNveT3P3o/Xxvd1lQcsPKX3uhg/7JY33rH27I1VCQNMpvck1Rvnf6ln/79sgd8ypNeM546ebFqu2XlPYFSAj/2t28w706HGXt2bGw4sVfed6h287j0pVcr+YtIeKm9odqqB4vqOecn758nF/C64Q+9f8mVOKxcp46yu6cy3unZ4Ol3r92cW5yat/xq/muj2yK5Sg/6F4MR/JRq6UuvVZ+DyGqUyhuqrXywqJ5zHr1wa9k5J6cKfO9cYUkSM+D3tlcv7VS7oi8fb+RGZD29+FHPtR50Fe6s/HBTmM9BRMJJ5Q3VVj5YVM85b723PIFWS2ov/fTvqx67VkdOo19m9V7xVr6+mc6Waud2qt+cjbLLSWS1S2XNPYl/voc9ZyOx1XoYKMyNyKD39t7VGbgqkhkEdT2uL2vLDCqr/MlzP+aPn/sx/SHKYNVq7P293fzNgd8K9Tm8b63xlUf0ZKtII1KZ3KN8SCnqczYa20odOSt9YVTrhnnt797mekBi7+wwHv1HG3nuv7/B3HtLM/yN4iLWo4P9Vf8FUn6OUtxBxkYGlsQF4a7Cyz+HfD5PToldpCGpLMsk8c/3oHN2rVm++EQrYlupFl3tqv67P/o5Qesi3bHG+NroNnrWLv9en5v3xTJTrX8F1SqDRfmsgYjUL5XdMrXKGK0QdM5P3j+/7JytiG2lq+A/qTJ9QbVnTUtrnE5XWcS6lNRXal2sfG01mppAJDmJJnd3PwWcGhoaerze9yaROCrPmc/nQ70uivNC8BfG4TOXaibhILXKR0FfKNVeKyLtJ5U199Wo2hdGtav6NUbgjI7/4K7OFd9XKh+Vf6EE9airi0WkvSm5p1y1q3qAseM/YW7+dkq+w+Cru359xfeVf4GUf6Ek8dCYiDROyT0DKq/qS4l4bn5hCoF5d/oD7hHUUz5S/VwkXZTcM6ayNXLefbGE0nv9fyccnYjEJZWtkFJdEk/vikj7UXLPGE2+JSLQguRuZh80s2+b2fGojy21afItEYGQyd3MnjWzN83sQsX4TjO7ZGaXzewAgLv/zN33tSJYqU2Tb4kIhL9yPwrsLB8wsw7gaeBhYAuwx8y2RBqdLFNrpkY99i8iAOYhF0U2s03AC+6+tbj9MeApdx8pbo8DuPvB4vZxd//MCsfbD+wH6Ovr2z4xMcHMzAw9PT1N/Djxijvel6/OcfTCLW6VTRrTtQb2bu3i4xuCF9oup8+3ddIUKyjeVosr3uHh4XPuPhS0r5lWyH7gjbLtK8AOM7sH+DowaGbjpWRfyd2PAEcAhoaGPJfLLcwCmMs1EVK84o73S4fOLknssDCn/Is/7+BPP1c7Dn2+rZOmWEHxtlo7xBt5n7u7vwU8Eea1zSyztxqpE0ZEwmqmW6YAbCzbvq84Flqjy+ytVuqEEZGwmknurwIPmtlmM+sCHgNO1nMAM9tlZkeuX7/eRBirhzphRCSssK2Qx4BXgAEzu2Jm+9z9XeBJ4AzwOvC8u1+s5+S6cq+POmFEJKxQNXd331Nl/DRwutGTq+ZeP03gJSJhJDr9gK7cRURaQ3PLiIhkUKLJXTdURURaQ2UZEZEMUllGRCSDEl2JqdQtA9w0s9eBe4Ff1nmY9cBKdZ2g/bXGav1/6b/tHG/QWFLxrrQdZ7zV9jUTbzOxtiLedvvdTVu8acsNH6j6andP/A9wpPjf1xp9bz37a43V+v80xFtlLJF4V9qOM95q+5qJt5lYWxFvu/3upi3etOWGlf60S1nmVAvfG7S/1lit/09DvNX21yuKeFfajjPeavuaibeZWGu9v5F42+13N2i8neNNW26oKvSUv3Ews9e8yvSV7Ujxtlaa4k1TrKB4W60d4m2XK/eSI0kHUCfF21ppijdNsYLibbXE422rK3cREYlGu125i4hIBJTcRUQySMldRCSD2jq5m9k6M/uOmf2Fmf1+0vHUYmYfNLNvm9nxpGOpxcxGi5/rc2b2iaTjqcXMfs3MnjGz42b2+aTjCaP4+/uamX0q6VhqMbOcmf2w+Bnnko6nFjNbY2ZfN7Nvmdk/SzqelZjZbxY/1/9oZi/Hdd7Yk7uZPWtmb5rZhYrxnWZ2ycwum9mB4vBu4Li7Pw58Ou5Yi3GFjtfdf+bu+5KIsxhTPbFOFj/XJ4BHUxDv6+7+BPBZ4KF2j7foi8Dz8Ua5JK564nVgBljLwmL3sasz3kdYWNpzjgTirfN394fF390XgO/EFmS9T1E1+wf4x8BvABfKxjqA/wN8EOgCfgJsAcaBDxdf85/jjrXeeMv2H09RrN8AfiMN8bLwBf9XwOfaPV7gd1hYenIv8KkUxLumuL8P+G4K4j0A/Ivia2L/+9bg37XngbvjijH2K3d3/wHwdsXwR4DLvnDlewuYYOGb+QoL386QUAmpzngTVU+stuDfAH/l7v8j7lih/s/W3U+6+8NAIiW6OuPNAR8FPgc8bmZt/XfN3d8r7r8G3BljmIsayA3Xiq+Zjy/KBfX+7prZ/cB1d/+/ccWY6MRhZfqBN8q2rwA7gG8Cf25mn6T5x7yjFBivmd0DfB0YNLNxdz+YSHRLVfts/wj4bWC9mT3g7s8kEVyAap9tjoUy3Z00sbRjCwTG6+5PApjZXuCXZckzadU+393ACNAL/HkCcVVT7ff33wPfMrPfBH6QRGABqsUKsA/4yziDaZfkHsjdbwD/POk4wnL3t1ioYbc9d/8mC1+eqeDueSCfcBh1c/ejSccQhrufAE4kHUdY7n6ThYSZCu7+1bjP2S7dMgVgY9n2fcWxdpWmeNMUKyjeVlO8rdNWsbZLcn8VeNDMNptZFws3ok4mHNNK0hRvmmIFxdtqird12ivWBO4yHwN+we0Wpn3F8d8F/hcLd5u/FHdcWYg3TbEqXsWb5njTEKsmDhMRyaB2KcuIiEiElNxFRDJIyV1EJIOU3EVEMkjJXUQkg5TcRUQySMldRCSDlNxFRDJIyV1EJIP+P3Gldt6RASeZAAAAAElFTkSuQmCC\n",
      "text/plain": [
       "<Figure size 432x288 with 1 Axes>"
      ]
     },
     "metadata": {
      "needs_background": "light"
     },
     "output_type": "display_data"
    }
   ],
   "source": [
    "plt.loglog(t.dhc, t.sf1, 'o')\n",
    "plt.grid();"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 65,
   "id": "acd2e2f5",
   "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>dhc</th>\n",
       "      <th>sf1</th>\n",
       "      <th>ratio</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>eth</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0.0</th>\n",
       "      <td>15684656.0</td>\n",
       "      <td>1615133</td>\n",
       "      <td>0.102975</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1.0</th>\n",
       "      <td>9460905.0</td>\n",
       "      <td>1393984</td>\n",
       "      <td>0.147342</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "            dhc      sf1     ratio\n",
       "eth                               \n",
       "0.0  15684656.0  1615133  0.102975\n",
       "1.0   9460905.0  1393984  0.147342"
      ]
     },
     "execution_count": 65,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "def my_compare(col):\n",
    "    s_dhc = dhc.groupby(col).n.sum()\n",
    "    s_sf1 = df_all.groupby(col).pweight.sum()\n",
    "    t = pd.DataFrame({'dhc': s_dhc,\n",
    "                     'sf1': s_sf1,})\n",
    "    t['ratio'] = t.sf1 / t.dhc\n",
    "    return t\n",
    "my_compare('eth')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 66,
   "id": "c0260c80",
   "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>dhc</th>\n",
       "      <th>sf1</th>\n",
       "      <th>ratio</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>sex</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0.0</th>\n",
       "      <td>12471657.0</td>\n",
       "      <td>1542286</td>\n",
       "      <td>0.123663</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1.0</th>\n",
       "      <td>12673904.0</td>\n",
       "      <td>1466831</td>\n",
       "      <td>0.115736</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "            dhc      sf1     ratio\n",
       "sex                               \n",
       "0.0  12471657.0  1542286  0.123663\n",
       "1.0  12673904.0  1466831  0.115736"
      ]
     },
     "execution_count": 66,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "my_compare('sex')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "e5f017f0",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "pyomo_env",
   "language": "python",
   "name": "pyomo_env"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.10.4"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}