notebooks/nyc_2022.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Load in the \"rosetta stone\" file\n",
    "\n",
    "I made this file using mapshaper, as executed in the `makefile` one directory up from this one.\n",
    "\n",
    "I then viewed the confidence-check files in QGIS to visually inspect the boundaries, and deal with shapes that didn't quite fit."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "# this is where the Makefile put my files. Your path will vary\n",
    "mypath = '/Volumes/JK_Smarts_Data/precinct_project/NY'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 83,
   "metadata": {},
   "outputs": [],
   "source": [
    "rosetta_df = pd.read_csv(f'{mypath}/nyc_blocks_precincts_prelim.csv', dtype={'GEOID20':'str', 'precinct':'Int64'})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 84,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['STATEFP20', 'COUNTYFP20', 'TRACTCE20', 'BLOCKCE20', 'GEOID20',\n",
       "       'NAME20', 'MTFCC20', 'UR20', 'UACE20', 'UATYPE20', 'FUNCSTAT20',\n",
       "       'ALAND20', 'AWATER20', 'precinct', 'shape_area', 'shape_leng'],\n",
       "      dtype='object')"
      ]
     },
     "execution_count": 84,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "rosetta_df.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 85,
   "metadata": {},
   "outputs": [],
   "source": [
    "rosetta_df.drop(columns=['STATEFP20', 'COUNTYFP20', 'TRACTCE20', 'BLOCKCE20',\n",
    "       'NAME20', 'MTFCC20', 'UR20', 'UACE20', 'UATYPE20', 'FUNCSTAT20',\n",
    "       'ALAND20', 'AWATER20', 'shape_area', 'shape_leng'], inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 94,
   "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>GEOID20</th>\n",
       "      <th>precinct</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>360050054003000</td>\n",
       "      <td>43</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>360050442003002</td>\n",
       "      <td>47</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>360050462083001</td>\n",
       "      <td>47</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>360050296001007</td>\n",
       "      <td>49</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>360050169001005</td>\n",
       "      <td>42</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>37375</th>\n",
       "      <td>360850208053004</td>\n",
       "      <td>123</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>37376</th>\n",
       "      <td>360850277042003</td>\n",
       "      <td>121</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>37377</th>\n",
       "      <td>360850244013006</td>\n",
       "      <td>123</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>37378</th>\n",
       "      <td>360850020011014</td>\n",
       "      <td>120</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>37379</th>\n",
       "      <td>360850036002002</td>\n",
       "      <td>120</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>37380 rows × 2 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "               GEOID20  precinct\n",
       "0      360050054003000        43\n",
       "1      360050442003002        47\n",
       "2      360050462083001        47\n",
       "3      360050296001007        49\n",
       "4      360050169001005        42\n",
       "...                ...       ...\n",
       "37375  360850208053004       123\n",
       "37376  360850277042003       121\n",
       "37377  360850244013006       123\n",
       "37378  360850020011014       120\n",
       "37379  360850036002002       120\n",
       "\n",
       "[37380 rows x 2 columns]"
      ]
     },
     "execution_count": 94,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "rosetta_df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Adding fixes"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "In the analysis of the data files, the following block centroids a) fell out of any precinct and b) had a population greater than zero:\n",
    "```\n",
    "GEOID20,precinct\n",
    "360471018001000,69\n",
    "360610007008002,1\n",
    "360050274023006,45\n",
    "360050090002018,45\n",
    "360810945001000,109\n",
    "360470628002029,61\n",
    "360470628003014,61\n",
    "360850097011000,120\n",
    "360850223001000,121\n",
    "```\n",
    "\n",
    "Using [Mr. Data Conveter](https://shancarter.github.io/mr-data-converter/)...\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 98,
   "metadata": {},
   "outputs": [],
   "source": [
    "to_change = [{\"GEOID20\":'360471018001000',\"precinct\":69},\n",
    "{\"GEOID20\":'360610007008002',\"precinct\":1},\n",
    "{\"GEOID20\":'360050274023006',\"precinct\":45},\n",
    "{\"GEOID20\":'360050090002018',\"precinct\":45},\n",
    "{\"GEOID20\":'360810945001000',\"precinct\":109},\n",
    "{\"GEOID20\":'360470628002029',\"precinct\":61},\n",
    "{\"GEOID20\":'360470628003014',\"precinct\":61},\n",
    "{\"GEOID20\":'360850097011000',\"precinct\":120},\n",
    "{\"GEOID20\":'360850223001000',\"precinct\":121}]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 99,
   "metadata": {},
   "outputs": [],
   "source": [
    "for item in to_change:\n",
    "    rosetta_df.loc[rosetta_df['GEOID20'] == item['GEOID20'], ['precinct']] = item['precinct']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 101,
   "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>GEOID20</th>\n",
       "      <th>precinct</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>334</th>\n",
       "      <td>360050274023006</td>\n",
       "      <td>45</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "             GEOID20  precinct\n",
       "334  360050274023006        45"
      ]
     },
     "execution_count": 101,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# let's see the current status of 360050274023006\n",
    "rosetta_df.loc[rosetta_df['GEOID20'] == '360050274023006']"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Load in the population data"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "I downloaded the population files from [data.census.gov](https://data.census.gov/cedsci/advanced). \n",
    "\n",
    "I'm using the 2020 Census [P1 Race](https://data.census.gov/cedsci/table?q=United%20States%20Race%20and%20Ethnicity&g=0500000US36005%241000000,36047%241000000,36061%241000000,36081%241000000,36085%241000000&y=2020&tid=DECENNIALPL2020.P1) and [P2 Hispanic or Latino](https://data.census.gov/cedsci/table?q=United%20States%20Race%20and%20Ethnicity&g=0500000US36005%241000000,36047%241000000,36061%241000000,36081%241000000,36085%241000000&y=2020&tid=DECENNIALPL2020.P1) files."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### P1 Race\n",
    "\n",
    "Note that the column numbers are translated into column descriptions [in this metadata file]('../data/nyc/DECENNIALPL2020.P1_metadata_2022-02-06T162722.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 112,
   "metadata": {},
   "outputs": [],
   "source": [
    "# census P1 for NYC\n",
    "p1_df = pd.read_csv(f'{mypath}/DECENNIALPL2020.P1_2022-02-06T162735/DECENNIALPL2020.P1_data_with_overlays_2022-02-06T162722.csv', skiprows=[1])\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 113,
   "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>NAME</th>\n",
       "      <th>P1_001N</th>\n",
       "      <th>P1_002N</th>\n",
       "      <th>P1_003N</th>\n",
       "      <th>P1_004N</th>\n",
       "      <th>P1_005N</th>\n",
       "      <th>P1_006N</th>\n",
       "      <th>P1_007N</th>\n",
       "      <th>P1_008N</th>\n",
       "      <th>...</th>\n",
       "      <th>P1_062N</th>\n",
       "      <th>P1_063N</th>\n",
       "      <th>P1_064N</th>\n",
       "      <th>P1_065N</th>\n",
       "      <th>P1_066N</th>\n",
       "      <th>P1_067N</th>\n",
       "      <th>P1_068N</th>\n",
       "      <th>P1_069N</th>\n",
       "      <th>P1_070N</th>\n",
       "      <th>P1_071N</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1000000US360050001000001</td>\n",
       "      <td>Block 0001, Block Group 0, Census Tract 1, Bro...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1000000US360050001000002</td>\n",
       "      <td>Block 0002, Block Group 0, Census Tract 1, Bro...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1000000US360050001001000</td>\n",
       "      <td>Block 1000, Block Group 1, Census Tract 1, Bro...</td>\n",
       "      <td>171</td>\n",
       "      <td>170</td>\n",
       "      <td>24</td>\n",
       "      <td>100</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>45</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1000000US360050001001001</td>\n",
       "      <td>Block 1001, Block Group 1, Census Tract 1, Bro...</td>\n",
       "      <td>1344</td>\n",
       "      <td>1318</td>\n",
       "      <td>293</td>\n",
       "      <td>734</td>\n",
       "      <td>11</td>\n",
       "      <td>32</td>\n",
       "      <td>0</td>\n",
       "      <td>248</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1000000US360050001001002</td>\n",
       "      <td>Block 1002, Block Group 1, Census Tract 1, Bro...</td>\n",
       "      <td>367</td>\n",
       "      <td>366</td>\n",
       "      <td>44</td>\n",
       "      <td>253</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>68</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>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",
       "      <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>37980</th>\n",
       "      <td>1000000US360859901000007</td>\n",
       "      <td>Block 0007, Block Group 0, Census Tract 9901, ...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>37981</th>\n",
       "      <td>1000000US360859901000008</td>\n",
       "      <td>Block 0008, Block Group 0, Census Tract 9901, ...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>37982</th>\n",
       "      <td>1000000US360859901000009</td>\n",
       "      <td>Block 0009, Block Group 0, Census Tract 9901, ...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>37983</th>\n",
       "      <td>1000000US360859901000010</td>\n",
       "      <td>Block 0010, Block Group 0, Census Tract 9901, ...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>37984</th>\n",
       "      <td>0100000US</td>\n",
       "      <td>United States</td>\n",
       "      <td>331449281</td>\n",
       "      <td>297600338</td>\n",
       "      <td>204277273</td>\n",
       "      <td>41104200</td>\n",
       "      <td>3727135</td>\n",
       "      <td>19886049</td>\n",
       "      <td>689966</td>\n",
       "      <td>27915715</td>\n",
       "      <td>...</td>\n",
       "      <td>553</td>\n",
       "      <td>21685</td>\n",
       "      <td>8632</td>\n",
       "      <td>9141</td>\n",
       "      <td>1137</td>\n",
       "      <td>1021</td>\n",
       "      <td>1309</td>\n",
       "      <td>445</td>\n",
       "      <td>2505</td>\n",
       "      <td>2505</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>37985 rows × 73 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                         GEO_ID  \\\n",
       "0      1000000US360050001000001   \n",
       "1      1000000US360050001000002   \n",
       "2      1000000US360050001001000   \n",
       "3      1000000US360050001001001   \n",
       "4      1000000US360050001001002   \n",
       "...                         ...   \n",
       "37980  1000000US360859901000007   \n",
       "37981  1000000US360859901000008   \n",
       "37982  1000000US360859901000009   \n",
       "37983  1000000US360859901000010   \n",
       "37984                 0100000US   \n",
       "\n",
       "                                                    NAME    P1_001N  \\\n",
       "0      Block 0001, Block Group 0, Census Tract 1, Bro...          0   \n",
       "1      Block 0002, Block Group 0, Census Tract 1, Bro...          0   \n",
       "2      Block 1000, Block Group 1, Census Tract 1, Bro...        171   \n",
       "3      Block 1001, Block Group 1, Census Tract 1, Bro...       1344   \n",
       "4      Block 1002, Block Group 1, Census Tract 1, Bro...        367   \n",
       "...                                                  ...        ...   \n",
       "37980  Block 0007, Block Group 0, Census Tract 9901, ...          0   \n",
       "37981  Block 0008, Block Group 0, Census Tract 9901, ...          0   \n",
       "37982  Block 0009, Block Group 0, Census Tract 9901, ...          0   \n",
       "37983  Block 0010, Block Group 0, Census Tract 9901, ...          0   \n",
       "37984                                      United States  331449281   \n",
       "\n",
       "         P1_002N    P1_003N   P1_004N  P1_005N   P1_006N  P1_007N   P1_008N  \\\n",
       "0              0          0         0        0         0        0         0   \n",
       "1              0          0         0        0         0        0         0   \n",
       "2            170         24       100        1         0        0        45   \n",
       "3           1318        293       734       11        32        0       248   \n",
       "4            366         44       253        0         1        0        68   \n",
       "...          ...        ...       ...      ...       ...      ...       ...   \n",
       "37980          0          0         0        0         0        0         0   \n",
       "37981          0          0         0        0         0        0         0   \n",
       "37982          0          0         0        0         0        0         0   \n",
       "37983          0          0         0        0         0        0         0   \n",
       "37984  297600338  204277273  41104200  3727135  19886049   689966  27915715   \n",
       "\n",
       "       ...  P1_062N  P1_063N  P1_064N  P1_065N  P1_066N  P1_067N  P1_068N  \\\n",
       "0      ...        0        0        0        0        0        0        0   \n",
       "1      ...        0        0        0        0        0        0        0   \n",
       "2      ...        0        0        0        0        0        0        0   \n",
       "3      ...        0        0        0        0        0        0        0   \n",
       "4      ...        0        0        0        0        0        0        0   \n",
       "...    ...      ...      ...      ...      ...      ...      ...      ...   \n",
       "37980  ...        0        0        0        0        0        0        0   \n",
       "37981  ...        0        0        0        0        0        0        0   \n",
       "37982  ...        0        0        0        0        0        0        0   \n",
       "37983  ...        0        0        0        0        0        0        0   \n",
       "37984  ...      553    21685     8632     9141     1137     1021     1309   \n",
       "\n",
       "       P1_069N  P1_070N  P1_071N  \n",
       "0            0        0        0  \n",
       "1            0        0        0  \n",
       "2            0        0        0  \n",
       "3            0        0        0  \n",
       "4            0        0        0  \n",
       "...        ...      ...      ...  \n",
       "37980        0        0        0  \n",
       "37981        0        0        0  \n",
       "37982        0        0        0  \n",
       "37983        0        0        0  \n",
       "37984      445     2505     2505  \n",
       "\n",
       "[37985 rows x 73 columns]"
      ]
     },
     "execution_count": 113,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "p1_df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "^^ Accidentally picked up the entire US. But that will fall away on the merge."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 114,
   "metadata": {},
   "outputs": [],
   "source": [
    "p1_df['GEO_ID'] = p1_df['GEO_ID'].str.replace('1000000US','')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 115,
   "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>NAME</th>\n",
       "      <th>P1_001N</th>\n",
       "      <th>P1_002N</th>\n",
       "      <th>P1_003N</th>\n",
       "      <th>P1_004N</th>\n",
       "      <th>P1_005N</th>\n",
       "      <th>P1_006N</th>\n",
       "      <th>P1_007N</th>\n",
       "      <th>P1_008N</th>\n",
       "      <th>...</th>\n",
       "      <th>P1_062N</th>\n",
       "      <th>P1_063N</th>\n",
       "      <th>P1_064N</th>\n",
       "      <th>P1_065N</th>\n",
       "      <th>P1_066N</th>\n",
       "      <th>P1_067N</th>\n",
       "      <th>P1_068N</th>\n",
       "      <th>P1_069N</th>\n",
       "      <th>P1_070N</th>\n",
       "      <th>P1_071N</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>360050001000001</td>\n",
       "      <td>Block 0001, Block Group 0, Census Tract 1, Bro...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>360050001000002</td>\n",
       "      <td>Block 0002, Block Group 0, Census Tract 1, Bro...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>360050001001000</td>\n",
       "      <td>Block 1000, Block Group 1, Census Tract 1, Bro...</td>\n",
       "      <td>171</td>\n",
       "      <td>170</td>\n",
       "      <td>24</td>\n",
       "      <td>100</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>45</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>360050001001001</td>\n",
       "      <td>Block 1001, Block Group 1, Census Tract 1, Bro...</td>\n",
       "      <td>1344</td>\n",
       "      <td>1318</td>\n",
       "      <td>293</td>\n",
       "      <td>734</td>\n",
       "      <td>11</td>\n",
       "      <td>32</td>\n",
       "      <td>0</td>\n",
       "      <td>248</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>360050001001002</td>\n",
       "      <td>Block 1002, Block Group 1, Census Tract 1, Bro...</td>\n",
       "      <td>367</td>\n",
       "      <td>366</td>\n",
       "      <td>44</td>\n",
       "      <td>253</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>68</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>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",
       "      <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>37980</th>\n",
       "      <td>360859901000007</td>\n",
       "      <td>Block 0007, Block Group 0, Census Tract 9901, ...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>37981</th>\n",
       "      <td>360859901000008</td>\n",
       "      <td>Block 0008, Block Group 0, Census Tract 9901, ...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>37982</th>\n",
       "      <td>360859901000009</td>\n",
       "      <td>Block 0009, Block Group 0, Census Tract 9901, ...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>37983</th>\n",
       "      <td>360859901000010</td>\n",
       "      <td>Block 0010, Block Group 0, Census Tract 9901, ...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>37984</th>\n",
       "      <td>0100000US</td>\n",
       "      <td>United States</td>\n",
       "      <td>331449281</td>\n",
       "      <td>297600338</td>\n",
       "      <td>204277273</td>\n",
       "      <td>41104200</td>\n",
       "      <td>3727135</td>\n",
       "      <td>19886049</td>\n",
       "      <td>689966</td>\n",
       "      <td>27915715</td>\n",
       "      <td>...</td>\n",
       "      <td>553</td>\n",
       "      <td>21685</td>\n",
       "      <td>8632</td>\n",
       "      <td>9141</td>\n",
       "      <td>1137</td>\n",
       "      <td>1021</td>\n",
       "      <td>1309</td>\n",
       "      <td>445</td>\n",
       "      <td>2505</td>\n",
       "      <td>2505</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>37985 rows × 73 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                GEO_ID                                               NAME  \\\n",
       "0      360050001000001  Block 0001, Block Group 0, Census Tract 1, Bro...   \n",
       "1      360050001000002  Block 0002, Block Group 0, Census Tract 1, Bro...   \n",
       "2      360050001001000  Block 1000, Block Group 1, Census Tract 1, Bro...   \n",
       "3      360050001001001  Block 1001, Block Group 1, Census Tract 1, Bro...   \n",
       "4      360050001001002  Block 1002, Block Group 1, Census Tract 1, Bro...   \n",
       "...                ...                                                ...   \n",
       "37980  360859901000007  Block 0007, Block Group 0, Census Tract 9901, ...   \n",
       "37981  360859901000008  Block 0008, Block Group 0, Census Tract 9901, ...   \n",
       "37982  360859901000009  Block 0009, Block Group 0, Census Tract 9901, ...   \n",
       "37983  360859901000010  Block 0010, Block Group 0, Census Tract 9901, ...   \n",
       "37984        0100000US                                      United States   \n",
       "\n",
       "         P1_001N    P1_002N    P1_003N   P1_004N  P1_005N   P1_006N  P1_007N  \\\n",
       "0              0          0          0         0        0         0        0   \n",
       "1              0          0          0         0        0         0        0   \n",
       "2            171        170         24       100        1         0        0   \n",
       "3           1344       1318        293       734       11        32        0   \n",
       "4            367        366         44       253        0         1        0   \n",
       "...          ...        ...        ...       ...      ...       ...      ...   \n",
       "37980          0          0          0         0        0         0        0   \n",
       "37981          0          0          0         0        0         0        0   \n",
       "37982          0          0          0         0        0         0        0   \n",
       "37983          0          0          0         0        0         0        0   \n",
       "37984  331449281  297600338  204277273  41104200  3727135  19886049   689966   \n",
       "\n",
       "        P1_008N  ...  P1_062N  P1_063N  P1_064N  P1_065N  P1_066N  P1_067N  \\\n",
       "0             0  ...        0        0        0        0        0        0   \n",
       "1             0  ...        0        0        0        0        0        0   \n",
       "2            45  ...        0        0        0        0        0        0   \n",
       "3           248  ...        0        0        0        0        0        0   \n",
       "4            68  ...        0        0        0        0        0        0   \n",
       "...         ...  ...      ...      ...      ...      ...      ...      ...   \n",
       "37980         0  ...        0        0        0        0        0        0   \n",
       "37981         0  ...        0        0        0        0        0        0   \n",
       "37982         0  ...        0        0        0        0        0        0   \n",
       "37983         0  ...        0        0        0        0        0        0   \n",
       "37984  27915715  ...      553    21685     8632     9141     1137     1021   \n",
       "\n",
       "       P1_068N  P1_069N  P1_070N  P1_071N  \n",
       "0            0        0        0        0  \n",
       "1            0        0        0        0  \n",
       "2            0        0        0        0  \n",
       "3            0        0        0        0  \n",
       "4            0        0        0        0  \n",
       "...        ...      ...      ...      ...  \n",
       "37980        0        0        0        0  \n",
       "37981        0        0        0        0  \n",
       "37982        0        0        0        0  \n",
       "37983        0        0        0        0  \n",
       "37984     1309      445     2505     2505  \n",
       "\n",
       "[37985 rows x 73 columns]"
      ]
     },
     "execution_count": 115,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "p1_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 116,
   "metadata": {},
   "outputs": [],
   "source": [
    "## merge onto the rosetta file\n",
    "rosetta_p1 = rosetta_df.merge(p1_df, left_on='GEOID20', right_on='GEO_ID', how='left')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 117,
   "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>GEOID20</th>\n",
       "      <th>precinct</th>\n",
       "      <th>GEO_ID</th>\n",
       "      <th>NAME</th>\n",
       "      <th>P1_001N</th>\n",
       "      <th>P1_002N</th>\n",
       "      <th>P1_003N</th>\n",
       "      <th>P1_004N</th>\n",
       "      <th>P1_005N</th>\n",
       "      <th>P1_006N</th>\n",
       "      <th>...</th>\n",
       "      <th>P1_062N</th>\n",
       "      <th>P1_063N</th>\n",
       "      <th>P1_064N</th>\n",
       "      <th>P1_065N</th>\n",
       "      <th>P1_066N</th>\n",
       "      <th>P1_067N</th>\n",
       "      <th>P1_068N</th>\n",
       "      <th>P1_069N</th>\n",
       "      <th>P1_070N</th>\n",
       "      <th>P1_071N</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>360050054003000</td>\n",
       "      <td>43</td>\n",
       "      <td>360050054003000</td>\n",
       "      <td>Block 3000, Block Group 3, Census Tract 54, Br...</td>\n",
       "      <td>538</td>\n",
       "      <td>473</td>\n",
       "      <td>32</td>\n",
       "      <td>92</td>\n",
       "      <td>19</td>\n",
       "      <td>23</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>360050442003002</td>\n",
       "      <td>47</td>\n",
       "      <td>360050442003002</td>\n",
       "      <td>Block 3002, Block Group 3, Census Tract 442, B...</td>\n",
       "      <td>155</td>\n",
       "      <td>129</td>\n",
       "      <td>3</td>\n",
       "      <td>117</td>\n",
       "      <td>2</td>\n",
       "      <td>4</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>360050462083001</td>\n",
       "      <td>47</td>\n",
       "      <td>360050462083001</td>\n",
       "      <td>Block 3001, Block Group 3, Census Tract 462.08...</td>\n",
       "      <td>264</td>\n",
       "      <td>250</td>\n",
       "      <td>10</td>\n",
       "      <td>215</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>360050296001007</td>\n",
       "      <td>49</td>\n",
       "      <td>360050296001007</td>\n",
       "      <td>Block 1007, Block Group 1, Census Tract 296, B...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>360050169001005</td>\n",
       "      <td>42</td>\n",
       "      <td>360050169001005</td>\n",
       "      <td>Block 1005, Block Group 1, Census Tract 169, B...</td>\n",
       "      <td>370</td>\n",
       "      <td>347</td>\n",
       "      <td>19</td>\n",
       "      <td>221</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>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",
       "      <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>37375</th>\n",
       "      <td>360850208053004</td>\n",
       "      <td>123</td>\n",
       "      <td>360850208053004</td>\n",
       "      <td>Block 3004, Block Group 3, Census Tract 208.05...</td>\n",
       "      <td>124</td>\n",
       "      <td>113</td>\n",
       "      <td>111</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>37376</th>\n",
       "      <td>360850277042003</td>\n",
       "      <td>121</td>\n",
       "      <td>360850277042003</td>\n",
       "      <td>Block 2003, Block Group 2, Census Tract 277.04...</td>\n",
       "      <td>246</td>\n",
       "      <td>243</td>\n",
       "      <td>131</td>\n",
       "      <td>7</td>\n",
       "      <td>0</td>\n",
       "      <td>75</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>37377</th>\n",
       "      <td>360850244013006</td>\n",
       "      <td>123</td>\n",
       "      <td>360850244013006</td>\n",
       "      <td>Block 3006, Block Group 3, Census Tract 244.01...</td>\n",
       "      <td>96</td>\n",
       "      <td>90</td>\n",
       "      <td>84</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>37378</th>\n",
       "      <td>360850020011014</td>\n",
       "      <td>120</td>\n",
       "      <td>360850020011014</td>\n",
       "      <td>Block 1014, Block Group 1, Census Tract 20.01,...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>37379</th>\n",
       "      <td>360850036002002</td>\n",
       "      <td>120</td>\n",
       "      <td>360850036002002</td>\n",
       "      <td>Block 2002, Block Group 2, Census Tract 36, Ri...</td>\n",
       "      <td>55</td>\n",
       "      <td>44</td>\n",
       "      <td>0</td>\n",
       "      <td>28</td>\n",
       "      <td>0</td>\n",
       "      <td>7</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>37380 rows × 75 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "               GEOID20  precinct           GEO_ID  \\\n",
       "0      360050054003000        43  360050054003000   \n",
       "1      360050442003002        47  360050442003002   \n",
       "2      360050462083001        47  360050462083001   \n",
       "3      360050296001007        49  360050296001007   \n",
       "4      360050169001005        42  360050169001005   \n",
       "...                ...       ...              ...   \n",
       "37375  360850208053004       123  360850208053004   \n",
       "37376  360850277042003       121  360850277042003   \n",
       "37377  360850244013006       123  360850244013006   \n",
       "37378  360850020011014       120  360850020011014   \n",
       "37379  360850036002002       120  360850036002002   \n",
       "\n",
       "                                                    NAME  P1_001N  P1_002N  \\\n",
       "0      Block 3000, Block Group 3, Census Tract 54, Br...      538      473   \n",
       "1      Block 3002, Block Group 3, Census Tract 442, B...      155      129   \n",
       "2      Block 3001, Block Group 3, Census Tract 462.08...      264      250   \n",
       "3      Block 1007, Block Group 1, Census Tract 296, B...        0        0   \n",
       "4      Block 1005, Block Group 1, Census Tract 169, B...      370      347   \n",
       "...                                                  ...      ...      ...   \n",
       "37375  Block 3004, Block Group 3, Census Tract 208.05...      124      113   \n",
       "37376  Block 2003, Block Group 2, Census Tract 277.04...      246      243   \n",
       "37377  Block 3006, Block Group 3, Census Tract 244.01...       96       90   \n",
       "37378  Block 1014, Block Group 1, Census Tract 20.01,...        0        0   \n",
       "37379  Block 2002, Block Group 2, Census Tract 36, Ri...       55       44   \n",
       "\n",
       "       P1_003N  P1_004N  P1_005N  P1_006N  ...  P1_062N  P1_063N  P1_064N  \\\n",
       "0           32       92       19       23  ...        0        0        0   \n",
       "1            3      117        2        4  ...        0        0        0   \n",
       "2           10      215        0        0  ...        0        0        0   \n",
       "3            0        0        0        0  ...        0        0        0   \n",
       "4           19      221        0        2  ...        0        0        0   \n",
       "...        ...      ...      ...      ...  ...      ...      ...      ...   \n",
       "37375      111        0        0        2  ...        0        0        0   \n",
       "37376      131        7        0       75  ...        0        0        0   \n",
       "37377       84        1        1        1  ...        0        0        0   \n",
       "37378        0        0        0        0  ...        0        0        0   \n",
       "37379        0       28        0        7  ...        0        0        0   \n",
       "\n",
       "       P1_065N  P1_066N  P1_067N  P1_068N  P1_069N  P1_070N  P1_071N  \n",
       "0            0        0        0        0        0        0        0  \n",
       "1            0        0        0        0        0        0        0  \n",
       "2            0        0        0        0        0        0        0  \n",
       "3            0        0        0        0        0        0        0  \n",
       "4            0        0        0        0        0        0        0  \n",
       "...        ...      ...      ...      ...      ...      ...      ...  \n",
       "37375        0        0        0        0        0        0        0  \n",
       "37376        0        0        0        0        0        0        0  \n",
       "37377        0        0        0        0        0        0        0  \n",
       "37378        0        0        0        0        0        0        0  \n",
       "37379        0        0        0        0        0        0        0  \n",
       "\n",
       "[37380 rows x 75 columns]"
      ]
     },
     "execution_count": 117,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "rosetta_p1"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### P2 Hispanic & Latino"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 118,
   "metadata": {},
   "outputs": [],
   "source": [
    "# census P1 for NYC\n",
    "p2_df = pd.read_csv(f'{mypath}/DECENNIALPL2020.P2_2022-02-06T162813/DECENNIALPL2020.P2_data_with_overlays_2022-02-06T162757.csv', skiprows=[1])\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 119,
   "metadata": {},
   "outputs": [],
   "source": [
    "p2_df['GEO_ID'] = p2_df['GEO_ID'].str.replace('1000000US','')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 120,
   "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>NAME</th>\n",
       "      <th>P2_001N</th>\n",
       "      <th>P2_002N</th>\n",
       "      <th>P2_003N</th>\n",
       "      <th>P2_004N</th>\n",
       "      <th>P2_005N</th>\n",
       "      <th>P2_006N</th>\n",
       "      <th>P2_007N</th>\n",
       "      <th>P2_008N</th>\n",
       "      <th>...</th>\n",
       "      <th>P2_064N</th>\n",
       "      <th>P2_065N</th>\n",
       "      <th>P2_066N</th>\n",
       "      <th>P2_067N</th>\n",
       "      <th>P2_068N</th>\n",
       "      <th>P2_069N</th>\n",
       "      <th>P2_070N</th>\n",
       "      <th>P2_071N</th>\n",
       "      <th>P2_072N</th>\n",
       "      <th>P2_073N</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>360050001000001</td>\n",
       "      <td>Block 0001, Block Group 0, Census Tract 1, Bro...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>360050001000002</td>\n",
       "      <td>Block 0002, Block Group 0, Census Tract 1, Bro...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>360050001001000</td>\n",
       "      <td>Block 1000, Block Group 1, Census Tract 1, Bro...</td>\n",
       "      <td>171</td>\n",
       "      <td>75</td>\n",
       "      <td>96</td>\n",
       "      <td>95</td>\n",
       "      <td>8</td>\n",
       "      <td>87</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>360050001001001</td>\n",
       "      <td>Block 1001, Block Group 1, Census Tract 1, Bro...</td>\n",
       "      <td>1344</td>\n",
       "      <td>475</td>\n",
       "      <td>869</td>\n",
       "      <td>861</td>\n",
       "      <td>145</td>\n",
       "      <td>676</td>\n",
       "      <td>6</td>\n",
       "      <td>32</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>360050001001002</td>\n",
       "      <td>Block 1002, Block Group 1, Census Tract 1, Bro...</td>\n",
       "      <td>367</td>\n",
       "      <td>110</td>\n",
       "      <td>257</td>\n",
       "      <td>256</td>\n",
       "      <td>17</td>\n",
       "      <td>234</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>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",
       "      <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>37980</th>\n",
       "      <td>360859901000007</td>\n",
       "      <td>Block 0007, Block Group 0, Census Tract 9901, ...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>37981</th>\n",
       "      <td>360859901000008</td>\n",
       "      <td>Block 0008, Block Group 0, Census Tract 9901, ...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>37982</th>\n",
       "      <td>360859901000009</td>\n",
       "      <td>Block 0009, Block Group 0, Census Tract 9901, ...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>37983</th>\n",
       "      <td>360859901000010</td>\n",
       "      <td>Block 0010, Block Group 0, Census Tract 9901, ...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>37984</th>\n",
       "      <td>0100000US</td>\n",
       "      <td>United States</td>\n",
       "      <td>331449281</td>\n",
       "      <td>62080044</td>\n",
       "      <td>269369237</td>\n",
       "      <td>255820254</td>\n",
       "      <td>191697647</td>\n",
       "      <td>39940338</td>\n",
       "      <td>2251699</td>\n",
       "      <td>19618719</td>\n",
       "      <td>...</td>\n",
       "      <td>150</td>\n",
       "      <td>9103</td>\n",
       "      <td>5247</td>\n",
       "      <td>2604</td>\n",
       "      <td>348</td>\n",
       "      <td>433</td>\n",
       "      <td>244</td>\n",
       "      <td>227</td>\n",
       "      <td>1004</td>\n",
       "      <td>1004</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>37985 rows × 75 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                GEO_ID                                               NAME  \\\n",
       "0      360050001000001  Block 0001, Block Group 0, Census Tract 1, Bro...   \n",
       "1      360050001000002  Block 0002, Block Group 0, Census Tract 1, Bro...   \n",
       "2      360050001001000  Block 1000, Block Group 1, Census Tract 1, Bro...   \n",
       "3      360050001001001  Block 1001, Block Group 1, Census Tract 1, Bro...   \n",
       "4      360050001001002  Block 1002, Block Group 1, Census Tract 1, Bro...   \n",
       "...                ...                                                ...   \n",
       "37980  360859901000007  Block 0007, Block Group 0, Census Tract 9901, ...   \n",
       "37981  360859901000008  Block 0008, Block Group 0, Census Tract 9901, ...   \n",
       "37982  360859901000009  Block 0009, Block Group 0, Census Tract 9901, ...   \n",
       "37983  360859901000010  Block 0010, Block Group 0, Census Tract 9901, ...   \n",
       "37984        0100000US                                      United States   \n",
       "\n",
       "         P2_001N   P2_002N    P2_003N    P2_004N    P2_005N   P2_006N  \\\n",
       "0              0         0          0          0          0         0   \n",
       "1              0         0          0          0          0         0   \n",
       "2            171        75         96         95          8        87   \n",
       "3           1344       475        869        861        145       676   \n",
       "4            367       110        257        256         17       234   \n",
       "...          ...       ...        ...        ...        ...       ...   \n",
       "37980          0         0          0          0          0         0   \n",
       "37981          0         0          0          0          0         0   \n",
       "37982          0         0          0          0          0         0   \n",
       "37983          0         0          0          0          0         0   \n",
       "37984  331449281  62080044  269369237  255820254  191697647  39940338   \n",
       "\n",
       "       P2_007N   P2_008N  ...  P2_064N  P2_065N  P2_066N  P2_067N  P2_068N  \\\n",
       "0            0         0  ...        0        0        0        0        0   \n",
       "1            0         0  ...        0        0        0        0        0   \n",
       "2            0         0  ...        0        0        0        0        0   \n",
       "3            6        32  ...        0        0        0        0        0   \n",
       "4            0         1  ...        0        0        0        0        0   \n",
       "...        ...       ...  ...      ...      ...      ...      ...      ...   \n",
       "37980        0         0  ...        0        0        0        0        0   \n",
       "37981        0         0  ...        0        0        0        0        0   \n",
       "37982        0         0  ...        0        0        0        0        0   \n",
       "37983        0         0  ...        0        0        0        0        0   \n",
       "37984  2251699  19618719  ...      150     9103     5247     2604      348   \n",
       "\n",
       "       P2_069N  P2_070N  P2_071N  P2_072N  P2_073N  \n",
       "0            0        0        0        0        0  \n",
       "1            0        0        0        0        0  \n",
       "2            0        0        0        0        0  \n",
       "3            0        0        0        0        0  \n",
       "4            0        0        0        0        0  \n",
       "...        ...      ...      ...      ...      ...  \n",
       "37980        0        0        0        0        0  \n",
       "37981        0        0        0        0        0  \n",
       "37982        0        0        0        0        0  \n",
       "37983        0        0        0        0        0  \n",
       "37984      433      244      227     1004     1004  \n",
       "\n",
       "[37985 rows x 75 columns]"
      ]
     },
     "execution_count": 120,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "p2_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 121,
   "metadata": {},
   "outputs": [],
   "source": [
    "p2_df.drop(columns=['NAME'], inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 122,
   "metadata": {},
   "outputs": [],
   "source": [
    "## merge onto the rosetta file\n",
    "rosetta_p1_p2 = rosetta_p1.merge(p2_df, left_on='GEOID20', right_on='GEO_ID', how='left')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 123,
   "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>GEOID20</th>\n",
       "      <th>precinct</th>\n",
       "      <th>GEO_ID_x</th>\n",
       "      <th>NAME</th>\n",
       "      <th>P1_001N</th>\n",
       "      <th>P1_002N</th>\n",
       "      <th>P1_003N</th>\n",
       "      <th>P1_004N</th>\n",
       "      <th>P1_005N</th>\n",
       "      <th>P1_006N</th>\n",
       "      <th>...</th>\n",
       "      <th>P2_064N</th>\n",
       "      <th>P2_065N</th>\n",
       "      <th>P2_066N</th>\n",
       "      <th>P2_067N</th>\n",
       "      <th>P2_068N</th>\n",
       "      <th>P2_069N</th>\n",
       "      <th>P2_070N</th>\n",
       "      <th>P2_071N</th>\n",
       "      <th>P2_072N</th>\n",
       "      <th>P2_073N</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>360050054003000</td>\n",
       "      <td>43</td>\n",
       "      <td>360050054003000</td>\n",
       "      <td>Block 3000, Block Group 3, Census Tract 54, Br...</td>\n",
       "      <td>538</td>\n",
       "      <td>473</td>\n",
       "      <td>32</td>\n",
       "      <td>92</td>\n",
       "      <td>19</td>\n",
       "      <td>23</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>360050442003002</td>\n",
       "      <td>47</td>\n",
       "      <td>360050442003002</td>\n",
       "      <td>Block 3002, Block Group 3, Census Tract 442, B...</td>\n",
       "      <td>155</td>\n",
       "      <td>129</td>\n",
       "      <td>3</td>\n",
       "      <td>117</td>\n",
       "      <td>2</td>\n",
       "      <td>4</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>360050462083001</td>\n",
       "      <td>47</td>\n",
       "      <td>360050462083001</td>\n",
       "      <td>Block 3001, Block Group 3, Census Tract 462.08...</td>\n",
       "      <td>264</td>\n",
       "      <td>250</td>\n",
       "      <td>10</td>\n",
       "      <td>215</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>360050296001007</td>\n",
       "      <td>49</td>\n",
       "      <td>360050296001007</td>\n",
       "      <td>Block 1007, Block Group 1, Census Tract 296, B...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>360050169001005</td>\n",
       "      <td>42</td>\n",
       "      <td>360050169001005</td>\n",
       "      <td>Block 1005, Block Group 1, Census Tract 169, B...</td>\n",
       "      <td>370</td>\n",
       "      <td>347</td>\n",
       "      <td>19</td>\n",
       "      <td>221</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>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",
       "      <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>37375</th>\n",
       "      <td>360850208053004</td>\n",
       "      <td>123</td>\n",
       "      <td>360850208053004</td>\n",
       "      <td>Block 3004, Block Group 3, Census Tract 208.05...</td>\n",
       "      <td>124</td>\n",
       "      <td>113</td>\n",
       "      <td>111</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>37376</th>\n",
       "      <td>360850277042003</td>\n",
       "      <td>121</td>\n",
       "      <td>360850277042003</td>\n",
       "      <td>Block 2003, Block Group 2, Census Tract 277.04...</td>\n",
       "      <td>246</td>\n",
       "      <td>243</td>\n",
       "      <td>131</td>\n",
       "      <td>7</td>\n",
       "      <td>0</td>\n",
       "      <td>75</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>37377</th>\n",
       "      <td>360850244013006</td>\n",
       "      <td>123</td>\n",
       "      <td>360850244013006</td>\n",
       "      <td>Block 3006, Block Group 3, Census Tract 244.01...</td>\n",
       "      <td>96</td>\n",
       "      <td>90</td>\n",
       "      <td>84</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>37378</th>\n",
       "      <td>360850020011014</td>\n",
       "      <td>120</td>\n",
       "      <td>360850020011014</td>\n",
       "      <td>Block 1014, Block Group 1, Census Tract 20.01,...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>37379</th>\n",
       "      <td>360850036002002</td>\n",
       "      <td>120</td>\n",
       "      <td>360850036002002</td>\n",
       "      <td>Block 2002, Block Group 2, Census Tract 36, Ri...</td>\n",
       "      <td>55</td>\n",
       "      <td>44</td>\n",
       "      <td>0</td>\n",
       "      <td>28</td>\n",
       "      <td>0</td>\n",
       "      <td>7</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>37380 rows × 149 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "               GEOID20  precinct         GEO_ID_x  \\\n",
       "0      360050054003000        43  360050054003000   \n",
       "1      360050442003002        47  360050442003002   \n",
       "2      360050462083001        47  360050462083001   \n",
       "3      360050296001007        49  360050296001007   \n",
       "4      360050169001005        42  360050169001005   \n",
       "...                ...       ...              ...   \n",
       "37375  360850208053004       123  360850208053004   \n",
       "37376  360850277042003       121  360850277042003   \n",
       "37377  360850244013006       123  360850244013006   \n",
       "37378  360850020011014       120  360850020011014   \n",
       "37379  360850036002002       120  360850036002002   \n",
       "\n",
       "                                                    NAME  P1_001N  P1_002N  \\\n",
       "0      Block 3000, Block Group 3, Census Tract 54, Br...      538      473   \n",
       "1      Block 3002, Block Group 3, Census Tract 442, B...      155      129   \n",
       "2      Block 3001, Block Group 3, Census Tract 462.08...      264      250   \n",
       "3      Block 1007, Block Group 1, Census Tract 296, B...        0        0   \n",
       "4      Block 1005, Block Group 1, Census Tract 169, B...      370      347   \n",
       "...                                                  ...      ...      ...   \n",
       "37375  Block 3004, Block Group 3, Census Tract 208.05...      124      113   \n",
       "37376  Block 2003, Block Group 2, Census Tract 277.04...      246      243   \n",
       "37377  Block 3006, Block Group 3, Census Tract 244.01...       96       90   \n",
       "37378  Block 1014, Block Group 1, Census Tract 20.01,...        0        0   \n",
       "37379  Block 2002, Block Group 2, Census Tract 36, Ri...       55       44   \n",
       "\n",
       "       P1_003N  P1_004N  P1_005N  P1_006N  ...  P2_064N  P2_065N  P2_066N  \\\n",
       "0           32       92       19       23  ...        0        0        0   \n",
       "1            3      117        2        4  ...        0        0        0   \n",
       "2           10      215        0        0  ...        0        0        0   \n",
       "3            0        0        0        0  ...        0        0        0   \n",
       "4           19      221        0        2  ...        0        0        0   \n",
       "...        ...      ...      ...      ...  ...      ...      ...      ...   \n",
       "37375      111        0        0        2  ...        0        0        0   \n",
       "37376      131        7        0       75  ...        0        0        0   \n",
       "37377       84        1        1        1  ...        0        0        0   \n",
       "37378        0        0        0        0  ...        0        0        0   \n",
       "37379        0       28        0        7  ...        0        0        0   \n",
       "\n",
       "       P2_067N  P2_068N  P2_069N  P2_070N  P2_071N  P2_072N  P2_073N  \n",
       "0            0        0        0        0        0        0        0  \n",
       "1            0        0        0        0        0        0        0  \n",
       "2            0        0        0        0        0        0        0  \n",
       "3            0        0        0        0        0        0        0  \n",
       "4            0        0        0        0        0        0        0  \n",
       "...        ...      ...      ...      ...      ...      ...      ...  \n",
       "37375        0        0        0        0        0        0        0  \n",
       "37376        0        0        0        0        0        0        0  \n",
       "37377        0        0        0        0        0        0        0  \n",
       "37378        0        0        0        0        0        0        0  \n",
       "37379        0        0        0        0        0        0        0  \n",
       "\n",
       "[37380 rows x 149 columns]"
      ]
     },
     "execution_count": 123,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "rosetta_p1_p2"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 124,
   "metadata": {},
   "outputs": [],
   "source": [
    "rosetta_p1_p2.drop(columns=['GEO_ID_x','GEO_ID_y'], inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 125,
   "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>GEOID20</th>\n",
       "      <th>precinct</th>\n",
       "      <th>NAME</th>\n",
       "      <th>P1_001N</th>\n",
       "      <th>P1_002N</th>\n",
       "      <th>P1_003N</th>\n",
       "      <th>P1_004N</th>\n",
       "      <th>P1_005N</th>\n",
       "      <th>P1_006N</th>\n",
       "      <th>P1_007N</th>\n",
       "      <th>...</th>\n",
       "      <th>P2_064N</th>\n",
       "      <th>P2_065N</th>\n",
       "      <th>P2_066N</th>\n",
       "      <th>P2_067N</th>\n",
       "      <th>P2_068N</th>\n",
       "      <th>P2_069N</th>\n",
       "      <th>P2_070N</th>\n",
       "      <th>P2_071N</th>\n",
       "      <th>P2_072N</th>\n",
       "      <th>P2_073N</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>360050054003000</td>\n",
       "      <td>43</td>\n",
       "      <td>Block 3000, Block Group 3, Census Tract 54, Br...</td>\n",
       "      <td>538</td>\n",
       "      <td>473</td>\n",
       "      <td>32</td>\n",
       "      <td>92</td>\n",
       "      <td>19</td>\n",
       "      <td>23</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>360050442003002</td>\n",
       "      <td>47</td>\n",
       "      <td>Block 3002, Block Group 3, Census Tract 442, B...</td>\n",
       "      <td>155</td>\n",
       "      <td>129</td>\n",
       "      <td>3</td>\n",
       "      <td>117</td>\n",
       "      <td>2</td>\n",
       "      <td>4</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>360050462083001</td>\n",
       "      <td>47</td>\n",
       "      <td>Block 3001, Block Group 3, Census Tract 462.08...</td>\n",
       "      <td>264</td>\n",
       "      <td>250</td>\n",
       "      <td>10</td>\n",
       "      <td>215</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>360050296001007</td>\n",
       "      <td>49</td>\n",
       "      <td>Block 1007, Block Group 1, Census Tract 296, B...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>360050169001005</td>\n",
       "      <td>42</td>\n",
       "      <td>Block 1005, Block Group 1, Census Tract 169, B...</td>\n",
       "      <td>370</td>\n",
       "      <td>347</td>\n",
       "      <td>19</td>\n",
       "      <td>221</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>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",
       "      <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>37375</th>\n",
       "      <td>360850208053004</td>\n",
       "      <td>123</td>\n",
       "      <td>Block 3004, Block Group 3, Census Tract 208.05...</td>\n",
       "      <td>124</td>\n",
       "      <td>113</td>\n",
       "      <td>111</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>37376</th>\n",
       "      <td>360850277042003</td>\n",
       "      <td>121</td>\n",
       "      <td>Block 2003, Block Group 2, Census Tract 277.04...</td>\n",
       "      <td>246</td>\n",
       "      <td>243</td>\n",
       "      <td>131</td>\n",
       "      <td>7</td>\n",
       "      <td>0</td>\n",
       "      <td>75</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>37377</th>\n",
       "      <td>360850244013006</td>\n",
       "      <td>123</td>\n",
       "      <td>Block 3006, Block Group 3, Census Tract 244.01...</td>\n",
       "      <td>96</td>\n",
       "      <td>90</td>\n",
       "      <td>84</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>37378</th>\n",
       "      <td>360850020011014</td>\n",
       "      <td>120</td>\n",
       "      <td>Block 1014, Block Group 1, Census Tract 20.01,...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>37379</th>\n",
       "      <td>360850036002002</td>\n",
       "      <td>120</td>\n",
       "      <td>Block 2002, Block Group 2, Census Tract 36, Ri...</td>\n",
       "      <td>55</td>\n",
       "      <td>44</td>\n",
       "      <td>0</td>\n",
       "      <td>28</td>\n",
       "      <td>0</td>\n",
       "      <td>7</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>37380 rows × 147 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "               GEOID20  precinct  \\\n",
       "0      360050054003000        43   \n",
       "1      360050442003002        47   \n",
       "2      360050462083001        47   \n",
       "3      360050296001007        49   \n",
       "4      360050169001005        42   \n",
       "...                ...       ...   \n",
       "37375  360850208053004       123   \n",
       "37376  360850277042003       121   \n",
       "37377  360850244013006       123   \n",
       "37378  360850020011014       120   \n",
       "37379  360850036002002       120   \n",
       "\n",
       "                                                    NAME  P1_001N  P1_002N  \\\n",
       "0      Block 3000, Block Group 3, Census Tract 54, Br...      538      473   \n",
       "1      Block 3002, Block Group 3, Census Tract 442, B...      155      129   \n",
       "2      Block 3001, Block Group 3, Census Tract 462.08...      264      250   \n",
       "3      Block 1007, Block Group 1, Census Tract 296, B...        0        0   \n",
       "4      Block 1005, Block Group 1, Census Tract 169, B...      370      347   \n",
       "...                                                  ...      ...      ...   \n",
       "37375  Block 3004, Block Group 3, Census Tract 208.05...      124      113   \n",
       "37376  Block 2003, Block Group 2, Census Tract 277.04...      246      243   \n",
       "37377  Block 3006, Block Group 3, Census Tract 244.01...       96       90   \n",
       "37378  Block 1014, Block Group 1, Census Tract 20.01,...        0        0   \n",
       "37379  Block 2002, Block Group 2, Census Tract 36, Ri...       55       44   \n",
       "\n",
       "       P1_003N  P1_004N  P1_005N  P1_006N  P1_007N  ...  P2_064N  P2_065N  \\\n",
       "0           32       92       19       23        0  ...        0        0   \n",
       "1            3      117        2        4        0  ...        0        0   \n",
       "2           10      215        0        0        0  ...        0        0   \n",
       "3            0        0        0        0        0  ...        0        0   \n",
       "4           19      221        0        2        0  ...        0        0   \n",
       "...        ...      ...      ...      ...      ...  ...      ...      ...   \n",
       "37375      111        0        0        2        0  ...        0        0   \n",
       "37376      131        7        0       75        0  ...        0        0   \n",
       "37377       84        1        1        1        0  ...        0        0   \n",
       "37378        0        0        0        0        0  ...        0        0   \n",
       "37379        0       28        0        7        0  ...        0        0   \n",
       "\n",
       "       P2_066N  P2_067N  P2_068N  P2_069N  P2_070N  P2_071N  P2_072N  P2_073N  \n",
       "0            0        0        0        0        0        0        0        0  \n",
       "1            0        0        0        0        0        0        0        0  \n",
       "2            0        0        0        0        0        0        0        0  \n",
       "3            0        0        0        0        0        0        0        0  \n",
       "4            0        0        0        0        0        0        0        0  \n",
       "...        ...      ...      ...      ...      ...      ...      ...      ...  \n",
       "37375        0        0        0        0        0        0        0        0  \n",
       "37376        0        0        0        0        0        0        0        0  \n",
       "37377        0        0        0        0        0        0        0        0  \n",
       "37378        0        0        0        0        0        0        0        0  \n",
       "37379        0        0        0        0        0        0        0        0  \n",
       "\n",
       "[37380 rows x 147 columns]"
      ]
     },
     "execution_count": 125,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "rosetta_p1_p2"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 126,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0"
      ]
     },
     "execution_count": 126,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "## Check for duplicates\n",
    "rosetta_p1_p2.duplicated(subset='GEOID20', keep='first').sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 133,
   "metadata": {},
   "outputs": [],
   "source": [
    "## Save this out\n",
    "rosetta_p1_p2.to_csv('../data/nyc/nyc_block_precinct_2020pop.csv', index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 128,
   "metadata": {},
   "outputs": [],
   "source": [
    "import numpy as np"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 129,
   "metadata": {},
   "outputs": [],
   "source": [
    "pivot = pd.pivot_table(rosetta_p1_p2, index=\"precinct\", aggfunc=np.sum)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 130,
   "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>P1_001N</th>\n",
       "      <th>P1_002N</th>\n",
       "      <th>P1_003N</th>\n",
       "      <th>P1_004N</th>\n",
       "      <th>P1_005N</th>\n",
       "      <th>P1_006N</th>\n",
       "      <th>P1_007N</th>\n",
       "      <th>P1_008N</th>\n",
       "      <th>P1_009N</th>\n",
       "      <th>P1_010N</th>\n",
       "      <th>...</th>\n",
       "      <th>P2_064N</th>\n",
       "      <th>P2_065N</th>\n",
       "      <th>P2_066N</th>\n",
       "      <th>P2_067N</th>\n",
       "      <th>P2_068N</th>\n",
       "      <th>P2_069N</th>\n",
       "      <th>P2_070N</th>\n",
       "      <th>P2_071N</th>\n",
       "      <th>P2_072N</th>\n",
       "      <th>P2_073N</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>precinct</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></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>1</th>\n",
       "      <td>84799</td>\n",
       "      <td>76937</td>\n",
       "      <td>57501</td>\n",
       "      <td>2655</td>\n",
       "      <td>96</td>\n",
       "      <td>14624</td>\n",
       "      <td>55</td>\n",
       "      <td>2006</td>\n",
       "      <td>7862</td>\n",
       "      <td>7409</td>\n",
       "      <td>...</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>50598</td>\n",
       "      <td>47663</td>\n",
       "      <td>13363</td>\n",
       "      <td>3556</td>\n",
       "      <td>109</td>\n",
       "      <td>27507</td>\n",
       "      <td>69</td>\n",
       "      <td>3059</td>\n",
       "      <td>2935</td>\n",
       "      <td>2749</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>64643</td>\n",
       "      <td>59481</td>\n",
       "      <td>50320</td>\n",
       "      <td>1782</td>\n",
       "      <td>72</td>\n",
       "      <td>5635</td>\n",
       "      <td>34</td>\n",
       "      <td>1638</td>\n",
       "      <td>5162</td>\n",
       "      <td>4867</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>57985</td>\n",
       "      <td>51746</td>\n",
       "      <td>18416</td>\n",
       "      <td>6934</td>\n",
       "      <td>396</td>\n",
       "      <td>17779</td>\n",
       "      <td>46</td>\n",
       "      <td>8175</td>\n",
       "      <td>6239</td>\n",
       "      <td>5672</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>75951</td>\n",
       "      <td>68112</td>\n",
       "      <td>41843</td>\n",
       "      <td>6610</td>\n",
       "      <td>480</td>\n",
       "      <td>11322</td>\n",
       "      <td>84</td>\n",
       "      <td>7773</td>\n",
       "      <td>7839</td>\n",
       "      <td>7193</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>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",
       "      <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>115</th>\n",
       "      <td>179134</td>\n",
       "      <td>154691</td>\n",
       "      <td>26261</td>\n",
       "      <td>10054</td>\n",
       "      <td>4894</td>\n",
       "      <td>34726</td>\n",
       "      <td>119</td>\n",
       "      <td>78637</td>\n",
       "      <td>24443</td>\n",
       "      <td>23033</td>\n",
       "      <td>...</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>120</th>\n",
       "      <td>122308</td>\n",
       "      <td>109715</td>\n",
       "      <td>46574</td>\n",
       "      <td>28782</td>\n",
       "      <td>1410</td>\n",
       "      <td>14002</td>\n",
       "      <td>63</td>\n",
       "      <td>18884</td>\n",
       "      <td>12593</td>\n",
       "      <td>11604</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>121</th>\n",
       "      <td>128149</td>\n",
       "      <td>117516</td>\n",
       "      <td>61832</td>\n",
       "      <td>17664</td>\n",
       "      <td>1517</td>\n",
       "      <td>19670</td>\n",
       "      <td>71</td>\n",
       "      <td>16762</td>\n",
       "      <td>10633</td>\n",
       "      <td>9892</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>122</th>\n",
       "      <td>144552</td>\n",
       "      <td>135413</td>\n",
       "      <td>104075</td>\n",
       "      <td>4494</td>\n",
       "      <td>464</td>\n",
       "      <td>18771</td>\n",
       "      <td>66</td>\n",
       "      <td>7543</td>\n",
       "      <td>9139</td>\n",
       "      <td>8581</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>123</th>\n",
       "      <td>100738</td>\n",
       "      <td>94283</td>\n",
       "      <td>83038</td>\n",
       "      <td>1099</td>\n",
       "      <td>208</td>\n",
       "      <td>6836</td>\n",
       "      <td>34</td>\n",
       "      <td>3068</td>\n",
       "      <td>6455</td>\n",
       "      <td>6221</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>3</td>\n",
       "      <td>0</td>\n",
       "      <td>3</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>77 rows × 144 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "          P1_001N  P1_002N  P1_003N  P1_004N  P1_005N  P1_006N  P1_007N  \\\n",
       "precinct                                                                  \n",
       "1           84799    76937    57501     2655       96    14624       55   \n",
       "5           50598    47663    13363     3556      109    27507       69   \n",
       "6           64643    59481    50320     1782       72     5635       34   \n",
       "7           57985    51746    18416     6934      396    17779       46   \n",
       "9           75951    68112    41843     6610      480    11322       84   \n",
       "...           ...      ...      ...      ...      ...      ...      ...   \n",
       "115        179134   154691    26261    10054     4894    34726      119   \n",
       "120        122308   109715    46574    28782     1410    14002       63   \n",
       "121        128149   117516    61832    17664     1517    19670       71   \n",
       "122        144552   135413   104075     4494      464    18771       66   \n",
       "123        100738    94283    83038     1099      208     6836       34   \n",
       "\n",
       "          P1_008N  P1_009N  P1_010N  ...  P2_064N  P2_065N  P2_066N  P2_067N  \\\n",
       "precinct                             ...                                       \n",
       "1            2006     7862     7409  ...        1        0        0        0   \n",
       "5            3059     2935     2749  ...        0        1        0        1   \n",
       "6            1638     5162     4867  ...        0        0        0        0   \n",
       "7            8175     6239     5672  ...        0        0        0        0   \n",
       "9            7773     7839     7193  ...        0        3        3        0   \n",
       "...           ...      ...      ...  ...      ...      ...      ...      ...   \n",
       "115         78637    24443    23033  ...        1        2        0        2   \n",
       "120         18884    12593    11604  ...        0        1        0        1   \n",
       "121         16762    10633     9892  ...        0        2        0        2   \n",
       "122          7543     9139     8581  ...        0        2        0        2   \n",
       "123          3068     6455     6221  ...        0        3        0        3   \n",
       "\n",
       "          P2_068N  P2_069N  P2_070N  P2_071N  P2_072N  P2_073N  \n",
       "precinct                                                        \n",
       "1               0        0        0        0        0        0  \n",
       "5               0        0        0        0        0        0  \n",
       "6               0        0        0        0        0        0  \n",
       "7               0        0        0        0        0        0  \n",
       "9               0        0        0        0        0        0  \n",
       "...           ...      ...      ...      ...      ...      ...  \n",
       "115             0        0        0        0        2        2  \n",
       "120             0        0        0        0        0        0  \n",
       "121             0        0        0        0        0        0  \n",
       "122             0        0        0        0        0        0  \n",
       "123             0        0        0        0        0        0  \n",
       "\n",
       "[77 rows x 144 columns]"
      ]
     },
     "execution_count": 130,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pivot"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 131,
   "metadata": {},
   "outputs": [],
   "source": [
    "pivot.reset_index(inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 132,
   "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>precinct</th>\n",
       "      <th>P1_001N</th>\n",
       "      <th>P1_002N</th>\n",
       "      <th>P1_003N</th>\n",
       "      <th>P1_004N</th>\n",
       "      <th>P1_005N</th>\n",
       "      <th>P1_006N</th>\n",
       "      <th>P1_007N</th>\n",
       "      <th>P1_008N</th>\n",
       "      <th>P1_009N</th>\n",
       "      <th>...</th>\n",
       "      <th>P2_064N</th>\n",
       "      <th>P2_065N</th>\n",
       "      <th>P2_066N</th>\n",
       "      <th>P2_067N</th>\n",
       "      <th>P2_068N</th>\n",
       "      <th>P2_069N</th>\n",
       "      <th>P2_070N</th>\n",
       "      <th>P2_071N</th>\n",
       "      <th>P2_072N</th>\n",
       "      <th>P2_073N</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>84799</td>\n",
       "      <td>76937</td>\n",
       "      <td>57501</td>\n",
       "      <td>2655</td>\n",
       "      <td>96</td>\n",
       "      <td>14624</td>\n",
       "      <td>55</td>\n",
       "      <td>2006</td>\n",
       "      <td>7862</td>\n",
       "      <td>...</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>5</td>\n",
       "      <td>50598</td>\n",
       "      <td>47663</td>\n",
       "      <td>13363</td>\n",
       "      <td>3556</td>\n",
       "      <td>109</td>\n",
       "      <td>27507</td>\n",
       "      <td>69</td>\n",
       "      <td>3059</td>\n",
       "      <td>2935</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>6</td>\n",
       "      <td>64643</td>\n",
       "      <td>59481</td>\n",
       "      <td>50320</td>\n",
       "      <td>1782</td>\n",
       "      <td>72</td>\n",
       "      <td>5635</td>\n",
       "      <td>34</td>\n",
       "      <td>1638</td>\n",
       "      <td>5162</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>7</td>\n",
       "      <td>57985</td>\n",
       "      <td>51746</td>\n",
       "      <td>18416</td>\n",
       "      <td>6934</td>\n",
       "      <td>396</td>\n",
       "      <td>17779</td>\n",
       "      <td>46</td>\n",
       "      <td>8175</td>\n",
       "      <td>6239</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>9</td>\n",
       "      <td>75951</td>\n",
       "      <td>68112</td>\n",
       "      <td>41843</td>\n",
       "      <td>6610</td>\n",
       "      <td>480</td>\n",
       "      <td>11322</td>\n",
       "      <td>84</td>\n",
       "      <td>7773</td>\n",
       "      <td>7839</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>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",
       "      <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>72</th>\n",
       "      <td>115</td>\n",
       "      <td>179134</td>\n",
       "      <td>154691</td>\n",
       "      <td>26261</td>\n",
       "      <td>10054</td>\n",
       "      <td>4894</td>\n",
       "      <td>34726</td>\n",
       "      <td>119</td>\n",
       "      <td>78637</td>\n",
       "      <td>24443</td>\n",
       "      <td>...</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>73</th>\n",
       "      <td>120</td>\n",
       "      <td>122308</td>\n",
       "      <td>109715</td>\n",
       "      <td>46574</td>\n",
       "      <td>28782</td>\n",
       "      <td>1410</td>\n",
       "      <td>14002</td>\n",
       "      <td>63</td>\n",
       "      <td>18884</td>\n",
       "      <td>12593</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>74</th>\n",
       "      <td>121</td>\n",
       "      <td>128149</td>\n",
       "      <td>117516</td>\n",
       "      <td>61832</td>\n",
       "      <td>17664</td>\n",
       "      <td>1517</td>\n",
       "      <td>19670</td>\n",
       "      <td>71</td>\n",
       "      <td>16762</td>\n",
       "      <td>10633</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>75</th>\n",
       "      <td>122</td>\n",
       "      <td>144552</td>\n",
       "      <td>135413</td>\n",
       "      <td>104075</td>\n",
       "      <td>4494</td>\n",
       "      <td>464</td>\n",
       "      <td>18771</td>\n",
       "      <td>66</td>\n",
       "      <td>7543</td>\n",
       "      <td>9139</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>76</th>\n",
       "      <td>123</td>\n",
       "      <td>100738</td>\n",
       "      <td>94283</td>\n",
       "      <td>83038</td>\n",
       "      <td>1099</td>\n",
       "      <td>208</td>\n",
       "      <td>6836</td>\n",
       "      <td>34</td>\n",
       "      <td>3068</td>\n",
       "      <td>6455</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>3</td>\n",
       "      <td>0</td>\n",
       "      <td>3</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>77 rows × 145 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "    precinct  P1_001N  P1_002N  P1_003N  P1_004N  P1_005N  P1_006N  P1_007N  \\\n",
       "0          1    84799    76937    57501     2655       96    14624       55   \n",
       "1          5    50598    47663    13363     3556      109    27507       69   \n",
       "2          6    64643    59481    50320     1782       72     5635       34   \n",
       "3          7    57985    51746    18416     6934      396    17779       46   \n",
       "4          9    75951    68112    41843     6610      480    11322       84   \n",
       "..       ...      ...      ...      ...      ...      ...      ...      ...   \n",
       "72       115   179134   154691    26261    10054     4894    34726      119   \n",
       "73       120   122308   109715    46574    28782     1410    14002       63   \n",
       "74       121   128149   117516    61832    17664     1517    19670       71   \n",
       "75       122   144552   135413   104075     4494      464    18771       66   \n",
       "76       123   100738    94283    83038     1099      208     6836       34   \n",
       "\n",
       "    P1_008N  P1_009N  ...  P2_064N  P2_065N  P2_066N  P2_067N  P2_068N  \\\n",
       "0      2006     7862  ...        1        0        0        0        0   \n",
       "1      3059     2935  ...        0        1        0        1        0   \n",
       "2      1638     5162  ...        0        0        0        0        0   \n",
       "3      8175     6239  ...        0        0        0        0        0   \n",
       "4      7773     7839  ...        0        3        3        0        0   \n",
       "..      ...      ...  ...      ...      ...      ...      ...      ...   \n",
       "72    78637    24443  ...        1        2        0        2        0   \n",
       "73    18884    12593  ...        0        1        0        1        0   \n",
       "74    16762    10633  ...        0        2        0        2        0   \n",
       "75     7543     9139  ...        0        2        0        2        0   \n",
       "76     3068     6455  ...        0        3        0        3        0   \n",
       "\n",
       "    P2_069N  P2_070N  P2_071N  P2_072N  P2_073N  \n",
       "0         0        0        0        0        0  \n",
       "1         0        0        0        0        0  \n",
       "2         0        0        0        0        0  \n",
       "3         0        0        0        0        0  \n",
       "4         0        0        0        0        0  \n",
       "..      ...      ...      ...      ...      ...  \n",
       "72        0        0        0        2        2  \n",
       "73        0        0        0        0        0  \n",
       "74        0        0        0        0        0  \n",
       "75        0        0        0        0        0  \n",
       "76        0        0        0        0        0  \n",
       "\n",
       "[77 rows x 145 columns]"
      ]
     },
     "execution_count": 132,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pivot"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 134,
   "metadata": {},
   "outputs": [],
   "source": [
    "pivot.to_csv('../data/nyc/nyc_precinct_2020pop.csv', index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Done!"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "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.7.7"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}