popsim_data_processing_2.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "from functools import reduce\n",
    "import pandas as pd\n",
    "import numpy as np"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Geographic Relationships"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Take intersected data from QGIS, recreate geoid\n",
    "geo = pd.read_csv('./populationsim-master/example_wa/data/qgis_geo.csv')\n",
    "geo = geo.drop('Unnamed: 5', axis=1)\n",
    "geo['REGION'] = '1'\n",
    "geo['STATE'] = geo['STATE'].astype(str).str.zfill(2)\n",
    "geo['COUNTY'] = geo['COUNTY'].astype(str).str.zfill(3)\n",
    "geo['TRACT'] = geo['TRACT'].astype(str).str.zfill(6)\n",
    "geo['TRACT'] = geo['STATE'] + geo['COUNTY'] + geo['TRACT']\n",
    "geo.to_csv('./populationsim-master/example_wa/data/geo_cross_walk.csv',index=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Seed Samples from PUMS"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Persons"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "p_df = pd.read_csv('./data/PUMS_2017_5YR_WA/psam_p53.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "pums_data_p = p_df[['SERIALNO','SPORDER','PUMA','PWGTP','AGEP']].copy()\n",
    "pums_data_p.dropna(inplace=True)\n",
    "\n",
    "pums_data_p.to_csv('./populationsim-master/example_wa/data/seed_persons.csv', index=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Households"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "h_df = pd.read_csv('./data/PUMS_2017_5YR_WA/psam_h53.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [],
   "source": [
    "pums_data_h = h_df[['SERIALNO','PUMA','WGTP','NP']].copy()\n",
    "pums_data_h.dropna(inplace=True)\n",
    "\n",
    "# Some PUMS households have no people in them; need to be filtered out of dataset\n",
    "hh_persons = pd.merge(pums_data_h, pums_data_p, how='left', on='SERIALNO')\n",
    "index = hh_persons['SPORDER'].index[hh_persons['SPORDER'].apply(np.isnan)]\n",
    "empty_households = hh_persons.iloc[index,:]['SERIALNO'].values\n",
    "pums_data_h = pums_data_h[~(pums_data_h['SERIALNO'].isin(empty_households))]\n",
    "\n",
    "pums_data_h.to_csv('./populationsim-master/example_wa/data/seed_households.csv', index=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Control Totals from ACS"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Tract"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [],
   "source": [
    "t2_df = pd.read_csv('./data/ACS_2019_WA/dp02.csv', low_memory=False)\n",
    "t2_data = t2_df[['GEO_ID',\n",
    "                'DP02_0060E',\n",
    "                'DP02_0061E',\n",
    "                'DP02_0062E',\n",
    "                'DP02_0063E',\n",
    "                'DP02_0064E',\n",
    "                'DP02_0065E',\n",
    "                'DP02_0066E',\n",
    "                'DP02_0153E']].copy()\n",
    "t2_data.columns = ['GEO_ID',\n",
    "                  'SCHL1',\n",
    "                  'SCHL2',\n",
    "                  'SCHL3',\n",
    "                  'SCHL4',\n",
    "                  'SCHL5',\n",
    "                  'SCHL6',\n",
    "                  'SCHL7',\n",
    "                  'ACC1']\n",
    "\n",
    "t3_df = pd.read_csv('./data/ACS_2019_WA/dp03.csv', low_memory=False)\n",
    "t3_data = t3_df[['GEO_ID',\n",
    "                'DP03_0051E',\n",
    "                'DP03_0052E',\n",
    "                'DP03_0053E',\n",
    "                'DP03_0054E',\n",
    "                'DP03_0055E',\n",
    "                'DP03_0056E',\n",
    "                'DP03_0057E',\n",
    "                'DP03_0058E',\n",
    "                'DP03_0059E',\n",
    "                'DP03_0060E',\n",
    "                'DP03_0061E',\n",
    "                'DP03_0047E',\n",
    "                'DP03_0048E',\n",
    "                'DP03_0049E',\n",
    "                'DP03_0050E',\n",
    "                'DP03_0003E',\n",
    "                'DP03_0006E']].copy()\n",
    "t3_data.columns = ['GEO_ID',\n",
    "                  'NUM_HH',\n",
    "                  'HINCP1',\n",
    "                  'HINCP2',\n",
    "                  'HINCP3',\n",
    "                  'HINCP4',\n",
    "                  'HINCP5',\n",
    "                  'HINCP6',\n",
    "                  'HINCP7',\n",
    "                  'HINCP8',\n",
    "                  'HINCP9',\n",
    "                  'HINCP10',\n",
    "                  'COW1',\n",
    "                  'COW2',\n",
    "                  'COW3',\n",
    "                  'COW4',\n",
    "                  'ESR1',\n",
    "                  'ESR2']\n",
    "\n",
    "t4_df = pd.read_csv('./data/ACS_2019_WA/dp04.csv', low_memory=False)\n",
    "t4_data = t4_df[['GEO_ID',\n",
    "                'DP04_0058E',\n",
    "                'DP04_0059E',\n",
    "                'DP04_0060E',\n",
    "                'DP04_0061E',\n",
    "                'DP04_0007E',\n",
    "                'DP04_0008E',\n",
    "                'DP04_0009E',\n",
    "                'DP04_0010E',\n",
    "                'DP04_0011E',\n",
    "                'DP04_0012E',\n",
    "                'DP04_0013E',\n",
    "                'DP04_0014E',\n",
    "                'DP04_0015E']].copy()\n",
    "t4_data.columns = ['GEO_ID',\n",
    "                  'VEH1',\n",
    "                  'VEH2',\n",
    "                  'VEH3',\n",
    "                  'VEH4',\n",
    "                  'BLD1',\n",
    "                  'BLD2',\n",
    "                  'BLD3',\n",
    "                  'BLD4',\n",
    "                  'BLD5',\n",
    "                  'BLD6',\n",
    "                  'BLD7',\n",
    "                  'BLD8',\n",
    "                  'BLD9']\n",
    "\n",
    "t5_df = pd.read_csv('./data/ACS_2019_WA/dp05.csv', low_memory=False)\n",
    "t5_data = t5_df[['GEO_ID',\n",
    "                'DP05_0002E',\n",
    "                'DP05_0003E',\n",
    "                'DP05_0008E',\n",
    "                'DP05_0009E',\n",
    "                'DP05_0010E',\n",
    "                'DP05_0011E',\n",
    "                'DP05_0012E',\n",
    "                'DP05_0013E',\n",
    "                'DP05_0014E',\n",
    "                'DP05_0015E',\n",
    "                'DP05_0016E',\n",
    "                'DP05_0017E',\n",
    "                'DP05_0037E',\n",
    "                'DP05_0038E',\n",
    "                'DP05_0039E',\n",
    "                'DP05_0044E',\n",
    "                'DP05_0052E',\n",
    "                'DP05_0057E',\n",
    "                'DP05_0058E']].copy()\n",
    "t5_data.columns = ['GEO_ID',\n",
    "                  'SEX1',\n",
    "                  'SEX2',\n",
    "                  'AGEP1',  # Start at 20yrs old; there must be people in the seed to meet the controls\n",
    "                  'AGEP2',\n",
    "                  'AGEP3',\n",
    "                  'AGEP4',\n",
    "                  'AGEP5',\n",
    "                  'AGEP6',\n",
    "                  'AGEP7',\n",
    "                  'AGEP8',\n",
    "                  'AGEP9',\n",
    "                  'AGEP10',\n",
    "                  'RAC1',\n",
    "                  'RAC2',\n",
    "                  'RAC3',\n",
    "                  'RAC4',\n",
    "                  'RAC5',\n",
    "                  'RAC6',\n",
    "                  'RAC7']\n",
    "\n",
    "t7_df = pd.read_csv('./data/ACS_2019_WA/s2501.csv', low_memory=False)\n",
    "t7_data = t7_df[['GEO_ID',\n",
    "                'S2501_C01_002E',\n",
    "                'S2501_C01_003E',\n",
    "                'S2501_C01_004E',\n",
    "                'S2501_C01_005E']].copy()\n",
    "t7_data.columns = ['GEO_ID',\n",
    "                  'NP1',\n",
    "                  'NP2',\n",
    "                  'NP3',\n",
    "                  'NP4']\n",
    "\n",
    "t8_df = pd.read_csv('./data/ACS_2019_WA/s0801.csv', low_memory=False)\n",
    "t8_data = t8_df[['GEO_ID',\n",
    "                'S0801_C02_037E',\n",
    "                'S0801_C02_038E',\n",
    "                'S0801_C02_039E',\n",
    "                'S0801_C02_040E',\n",
    "                'S0801_C02_041E',\n",
    "                'S0801_C02_042E',\n",
    "                'S0801_C02_043E',\n",
    "                'S0801_C02_044E',\n",
    "                'S0801_C02_045E']].copy()\n",
    "t8_data.columns = ['GEO_ID',\n",
    "                  'JWM1',\n",
    "                  'JWM2',\n",
    "                  'JWM3',\n",
    "                  'JWM4',\n",
    "                  'JWM5',\n",
    "                  'JWM6',\n",
    "                  'JWM7',\n",
    "                  'JWM8',\n",
    "                  'JWM9']\n",
    "\n",
    "t9_df = pd.read_csv('./data/ACS_2019_WA/s1810.csv', low_memory=False)\n",
    "t9_data = t9_df[['GEO_ID',\n",
    "                'S1810_C02_047E',\n",
    "                'S1810_C02_029E',\n",
    "                'S1810_C02_039E',\n",
    "                'S1810_C02_063E']].copy()\n",
    "t9_data.columns = ['GEO_ID',\n",
    "                  'PHY1',\n",
    "                  'EYE1',\n",
    "                  'REM1',\n",
    "                  'OUT1']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "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>GEO_ID</th>\n",
       "      <th>SCHL1</th>\n",
       "      <th>SCHL2</th>\n",
       "      <th>SCHL3</th>\n",
       "      <th>SCHL4</th>\n",
       "      <th>SCHL5</th>\n",
       "      <th>SCHL6</th>\n",
       "      <th>SCHL7</th>\n",
       "      <th>ACC1</th>\n",
       "      <th>NUM_HH</th>\n",
       "      <th>...</th>\n",
       "      <th>JWM5</th>\n",
       "      <th>JWM6</th>\n",
       "      <th>JWM7</th>\n",
       "      <th>JWM8</th>\n",
       "      <th>JWM9</th>\n",
       "      <th>PHY1</th>\n",
       "      <th>EYE1</th>\n",
       "      <th>REM1</th>\n",
       "      <th>OUT1</th>\n",
       "      <th>TRACT</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1400000US53001950100</td>\n",
       "      <td>53</td>\n",
       "      <td>121</td>\n",
       "      <td>592</td>\n",
       "      <td>409</td>\n",
       "      <td>190</td>\n",
       "      <td>255</td>\n",
       "      <td>155</td>\n",
       "      <td>839</td>\n",
       "      <td>1050</td>\n",
       "      <td>...</td>\n",
       "      <td>3.2</td>\n",
       "      <td>6.9</td>\n",
       "      <td>1.8</td>\n",
       "      <td>7.3</td>\n",
       "      <td>5.3</td>\n",
       "      <td>190</td>\n",
       "      <td>52</td>\n",
       "      <td>114</td>\n",
       "      <td>122</td>\n",
       "      <td>53001950100</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1400000US53001950200</td>\n",
       "      <td>20</td>\n",
       "      <td>102</td>\n",
       "      <td>343</td>\n",
       "      <td>303</td>\n",
       "      <td>116</td>\n",
       "      <td>171</td>\n",
       "      <td>53</td>\n",
       "      <td>475</td>\n",
       "      <td>615</td>\n",
       "      <td>...</td>\n",
       "      <td>8.3</td>\n",
       "      <td>8.0</td>\n",
       "      <td>10.8</td>\n",
       "      <td>5.0</td>\n",
       "      <td>11.0</td>\n",
       "      <td>144</td>\n",
       "      <td>31</td>\n",
       "      <td>134</td>\n",
       "      <td>74</td>\n",
       "      <td>53001950200</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1400000US53001950300</td>\n",
       "      <td>916</td>\n",
       "      <td>381</td>\n",
       "      <td>972</td>\n",
       "      <td>416</td>\n",
       "      <td>148</td>\n",
       "      <td>214</td>\n",
       "      <td>183</td>\n",
       "      <td>1218</td>\n",
       "      <td>1655</td>\n",
       "      <td>...</td>\n",
       "      <td>2.5</td>\n",
       "      <td>8.5</td>\n",
       "      <td>4.2</td>\n",
       "      <td>6.6</td>\n",
       "      <td>9.7</td>\n",
       "      <td>200</td>\n",
       "      <td>123</td>\n",
       "      <td>379</td>\n",
       "      <td>167</td>\n",
       "      <td>53001950300</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1400000US53001950400</td>\n",
       "      <td>475</td>\n",
       "      <td>94</td>\n",
       "      <td>417</td>\n",
       "      <td>318</td>\n",
       "      <td>57</td>\n",
       "      <td>186</td>\n",
       "      <td>116</td>\n",
       "      <td>704</td>\n",
       "      <td>974</td>\n",
       "      <td>...</td>\n",
       "      <td>2.3</td>\n",
       "      <td>20.7</td>\n",
       "      <td>10.3</td>\n",
       "      <td>1.0</td>\n",
       "      <td>2.3</td>\n",
       "      <td>153</td>\n",
       "      <td>69</td>\n",
       "      <td>129</td>\n",
       "      <td>94</td>\n",
       "      <td>53001950400</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>1400000US53001950500</td>\n",
       "      <td>1035</td>\n",
       "      <td>344</td>\n",
       "      <td>750</td>\n",
       "      <td>452</td>\n",
       "      <td>157</td>\n",
       "      <td>97</td>\n",
       "      <td>102</td>\n",
       "      <td>1232</td>\n",
       "      <td>1679</td>\n",
       "      <td>...</td>\n",
       "      <td>2.9</td>\n",
       "      <td>16.2</td>\n",
       "      <td>6.0</td>\n",
       "      <td>2.2</td>\n",
       "      <td>5.4</td>\n",
       "      <td>356</td>\n",
       "      <td>193</td>\n",
       "      <td>172</td>\n",
       "      <td>140</td>\n",
       "      <td>53001950500</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",
       "      <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>1454</th>\n",
       "      <td>1400000US53077940002</td>\n",
       "      <td>485</td>\n",
       "      <td>373</td>\n",
       "      <td>757</td>\n",
       "      <td>664</td>\n",
       "      <td>163</td>\n",
       "      <td>165</td>\n",
       "      <td>128</td>\n",
       "      <td>1034</td>\n",
       "      <td>1316</td>\n",
       "      <td>...</td>\n",
       "      <td>4.5</td>\n",
       "      <td>8.0</td>\n",
       "      <td>6.7</td>\n",
       "      <td>1.5</td>\n",
       "      <td>2.9</td>\n",
       "      <td>393</td>\n",
       "      <td>80</td>\n",
       "      <td>203</td>\n",
       "      <td>197</td>\n",
       "      <td>53077940002</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1455</th>\n",
       "      <td>1400000US53077940003</td>\n",
       "      <td>235</td>\n",
       "      <td>219</td>\n",
       "      <td>760</td>\n",
       "      <td>474</td>\n",
       "      <td>195</td>\n",
       "      <td>117</td>\n",
       "      <td>106</td>\n",
       "      <td>745</td>\n",
       "      <td>1017</td>\n",
       "      <td>...</td>\n",
       "      <td>2.5</td>\n",
       "      <td>17.0</td>\n",
       "      <td>3.7</td>\n",
       "      <td>3.5</td>\n",
       "      <td>4.0</td>\n",
       "      <td>171</td>\n",
       "      <td>65</td>\n",
       "      <td>141</td>\n",
       "      <td>136</td>\n",
       "      <td>53077940003</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1456</th>\n",
       "      <td>1400000US53077940004</td>\n",
       "      <td>925</td>\n",
       "      <td>579</td>\n",
       "      <td>1074</td>\n",
       "      <td>431</td>\n",
       "      <td>79</td>\n",
       "      <td>102</td>\n",
       "      <td>54</td>\n",
       "      <td>1277</td>\n",
       "      <td>1615</td>\n",
       "      <td>...</td>\n",
       "      <td>5.7</td>\n",
       "      <td>13.2</td>\n",
       "      <td>1.4</td>\n",
       "      <td>2.2</td>\n",
       "      <td>3.4</td>\n",
       "      <td>260</td>\n",
       "      <td>100</td>\n",
       "      <td>213</td>\n",
       "      <td>174</td>\n",
       "      <td>53077940004</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1457</th>\n",
       "      <td>1400000US53077940005</td>\n",
       "      <td>721</td>\n",
       "      <td>420</td>\n",
       "      <td>664</td>\n",
       "      <td>555</td>\n",
       "      <td>120</td>\n",
       "      <td>152</td>\n",
       "      <td>75</td>\n",
       "      <td>1084</td>\n",
       "      <td>1408</td>\n",
       "      <td>...</td>\n",
       "      <td>10.3</td>\n",
       "      <td>13.2</td>\n",
       "      <td>2.9</td>\n",
       "      <td>4.4</td>\n",
       "      <td>4.1</td>\n",
       "      <td>265</td>\n",
       "      <td>120</td>\n",
       "      <td>164</td>\n",
       "      <td>160</td>\n",
       "      <td>53077940005</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1458</th>\n",
       "      <td>1400000US53077940006</td>\n",
       "      <td>839</td>\n",
       "      <td>478</td>\n",
       "      <td>626</td>\n",
       "      <td>426</td>\n",
       "      <td>109</td>\n",
       "      <td>105</td>\n",
       "      <td>38</td>\n",
       "      <td>952</td>\n",
       "      <td>1252</td>\n",
       "      <td>...</td>\n",
       "      <td>4.7</td>\n",
       "      <td>19.0</td>\n",
       "      <td>2.9</td>\n",
       "      <td>4.6</td>\n",
       "      <td>5.3</td>\n",
       "      <td>276</td>\n",
       "      <td>84</td>\n",
       "      <td>231</td>\n",
       "      <td>105</td>\n",
       "      <td>53077940006</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>1458 rows × 76 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                    GEO_ID SCHL1 SCHL2 SCHL3 SCHL4 SCHL5 SCHL6 SCHL7  ACC1  \\\n",
       "1     1400000US53001950100    53   121   592   409   190   255   155   839   \n",
       "2     1400000US53001950200    20   102   343   303   116   171    53   475   \n",
       "3     1400000US53001950300   916   381   972   416   148   214   183  1218   \n",
       "4     1400000US53001950400   475    94   417   318    57   186   116   704   \n",
       "5     1400000US53001950500  1035   344   750   452   157    97   102  1232   \n",
       "...                    ...   ...   ...   ...   ...   ...   ...   ...   ...   \n",
       "1454  1400000US53077940002   485   373   757   664   163   165   128  1034   \n",
       "1455  1400000US53077940003   235   219   760   474   195   117   106   745   \n",
       "1456  1400000US53077940004   925   579  1074   431    79   102    54  1277   \n",
       "1457  1400000US53077940005   721   420   664   555   120   152    75  1084   \n",
       "1458  1400000US53077940006   839   478   626   426   109   105    38   952   \n",
       "\n",
       "     NUM_HH  ...  JWM5  JWM6  JWM7 JWM8  JWM9 PHY1 EYE1 REM1 OUT1        TRACT  \n",
       "1      1050  ...   3.2   6.9   1.8  7.3   5.3  190   52  114  122  53001950100  \n",
       "2       615  ...   8.3   8.0  10.8  5.0  11.0  144   31  134   74  53001950200  \n",
       "3      1655  ...   2.5   8.5   4.2  6.6   9.7  200  123  379  167  53001950300  \n",
       "4       974  ...   2.3  20.7  10.3  1.0   2.3  153   69  129   94  53001950400  \n",
       "5      1679  ...   2.9  16.2   6.0  2.2   5.4  356  193  172  140  53001950500  \n",
       "...     ...  ...   ...   ...   ...  ...   ...  ...  ...  ...  ...          ...  \n",
       "1454   1316  ...   4.5   8.0   6.7  1.5   2.9  393   80  203  197  53077940002  \n",
       "1455   1017  ...   2.5  17.0   3.7  3.5   4.0  171   65  141  136  53077940003  \n",
       "1456   1615  ...   5.7  13.2   1.4  2.2   3.4  260  100  213  174  53077940004  \n",
       "1457   1408  ...  10.3  13.2   2.9  4.4   4.1  265  120  164  160  53077940005  \n",
       "1458   1252  ...   4.7  19.0   2.9  4.6   5.3  276   84  231  105  53077940006  \n",
       "\n",
       "[1458 rows x 76 columns]"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Join data from different census tables\n",
    "tract_dataframes = [t2_data, t3_data, t4_data, t5_data, t7_data, t8_data, t9_data]\n",
    "tract_data = reduce(lambda left,right: pd.merge(left,right,on='GEO_ID'), tract_dataframes)\n",
    "tract_data.dropna(inplace=True)\n",
    "\n",
    "# Get tract id from the extended geo id, assign region 1\n",
    "tract_data['TRACT'] = tract_data['GEO_ID'].str.slice(-11,)\n",
    "tract_data = tract_data.iloc[1:,:]\n",
    "tract_data.to_csv('./populationsim-master/example_wa/data/control_totals_tract.csv', index=False)\n",
    "tract_data"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Region"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "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>SCHL1</th>\n",
       "      <th>SCHL2</th>\n",
       "      <th>SCHL3</th>\n",
       "      <th>SCHL4</th>\n",
       "      <th>SCHL5</th>\n",
       "      <th>SCHL6</th>\n",
       "      <th>SCHL7</th>\n",
       "      <th>ACC1</th>\n",
       "      <th>NUM_HH</th>\n",
       "      <th>HINCP1</th>\n",
       "      <th>...</th>\n",
       "      <th>JWM6</th>\n",
       "      <th>JWM7</th>\n",
       "      <th>JWM8</th>\n",
       "      <th>JWM9</th>\n",
       "      <th>PHY1</th>\n",
       "      <th>EYE1</th>\n",
       "      <th>REM1</th>\n",
       "      <th>OUT1</th>\n",
       "      <th>STATE</th>\n",
       "      <th>num_hh</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>186275.0</td>\n",
       "      <td>256174.0</td>\n",
       "      <td>1122330.0</td>\n",
       "      <td>1189880.0</td>\n",
       "      <td>509353.0</td>\n",
       "      <td>1144545.0</td>\n",
       "      <td>693067.0</td>\n",
       "      <td>2516185.0</td>\n",
       "      <td>2848396.0</td>\n",
       "      <td>136214.0</td>\n",
       "      <td>...</td>\n",
       "      <td>19708.4</td>\n",
       "      <td>11284.1</td>\n",
       "      <td>13538.9</td>\n",
       "      <td>17351.3</td>\n",
       "      <td>445243.0</td>\n",
       "      <td>155474.0</td>\n",
       "      <td>361975.0</td>\n",
       "      <td>310612.0</td>\n",
       "      <td>53</td>\n",
       "      <td>2848396</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>1 rows × 76 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "      SCHL1     SCHL2      SCHL3      SCHL4     SCHL5      SCHL6     SCHL7  \\\n",
       "0  186275.0  256174.0  1122330.0  1189880.0  509353.0  1144545.0  693067.0   \n",
       "\n",
       "        ACC1     NUM_HH    HINCP1  ...     JWM6     JWM7     JWM8     JWM9  \\\n",
       "0  2516185.0  2848396.0  136214.0  ...  19708.4  11284.1  13538.9  17351.3   \n",
       "\n",
       "       PHY1      EYE1      REM1      OUT1  STATE   num_hh  \n",
       "0  445243.0  155474.0  361975.0  310612.0     53  2848396  \n",
       "\n",
       "[1 rows x 76 columns]"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Sum the values across all census tracts to get region marginals\n",
    "region_data = tract_data.iloc[:,1:-1].apply(pd.to_numeric, errors='coerce')\n",
    "region_data = pd.DataFrame(region_data.sum()).transpose()\n",
    "region_data['STATE'] = '53'\n",
    "region_data['num_hh'] = 2848396\n",
    "region_data.dropna(inplace=True)\n",
    "region_data.to_csv('./populationsim-master/example_wa/data/control_totals_state.csv', index=False)\n",
    "region_data"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.8.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}