disagg2021.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "eaa95de1",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd # standard python data library\n",
    "import geopandas as gp # the geo-version of pandas\n",
    "import numpy as np\n",
    "import time\n",
    "import os"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "397a6752",
   "metadata": {},
   "source": [
    "# CVAP Disaggregation\n",
    "\n",
    "Function that takes in an RDH processed CVAP file (with appropriately summed columns) and disaggregates to the block-level"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "1ba62323",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Variable to set the year for the CVAP data\n",
    "y = \"21\"\n",
    "\n",
    "# Define the column names\n",
    "update_dict = {'P0040001': 'CVAP_TOT' + y,\n",
    " 'P0040003': 'CVAP_NHS' + y,\n",
    " 'P0040007': 'CVAP_AIA' + y,\n",
    " 'P0040008': 'CVAP_ASN' + y,\n",
    " 'P0040006': 'CVAP_BLK' + y,\n",
    " 'P0040009': 'CVAP_NHP' + y,\n",
    " 'P0040005': 'CVAP_WHT' + y,\n",
    " 'P0040014': 'CVAP_AIW' + y,\n",
    " 'P0040015': 'CVAP_ASW' + y,\n",
    " 'P0040013': 'CVAP_BLW' + y,\n",
    " 'P0040018': 'CVAP_AIB' + y,\n",
    " 'P0040011': 'CVAP_2OM' + y,\n",
    " 'P0040002': 'CVAP_HSP' + y,\n",
    " 'P0020001': 'C_TOT' + y,\n",
    " 'P0020003': 'C_NHS' + y,\n",
    " 'P0020007': 'C_AIA' + y,\n",
    " 'P0020008': 'C_ASN' + y,\n",
    " 'P0020006': 'C_BLK' + y,\n",
    " 'P0020009': 'C_NHP' + y,\n",
    " 'P0020005': 'C_WHT' + y,\n",
    " 'P0020014': 'C_AIW' + y,\n",
    " 'P0020015': 'C_ASW' + y,\n",
    " 'P0020013': 'C_BLW' + y,\n",
    " 'P0020018': 'C_AIB' + y,\n",
    " 'P0020011': 'C_2OM' + y,\n",
    " 'P0020002': 'C_HSP' + y}"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "ad27c426",
   "metadata": {},
   "outputs": [],
   "source": [
    "def define_multi_cols(df):\n",
    "    '''\n",
    "    Function to sum the combined race columns\n",
    "    '''\n",
    "    df['C_AIA'+y] = df['C_AIA'+y]+df['C_AIB'+y]+df['C_AIW'+y]\n",
    "    df['CVAP_AIA'+y] = df['CVAP_AIA'+y]+df['CVAP_AIB'+y]+df['CVAP_AIW'+y]\n",
    "    df['C_BLK'+y] = df['C_BLK'+y]+df['C_BLW'+y]+df['C_AIB'+y]    \n",
    "    df['CVAP_BLK'+y] = df['CVAP_BLK'+y]+df['CVAP_BLW'+y]+df['CVAP_AIB'+y]\n",
    "    df['C_ASN'+y] = df['C_ASN'+y] + df['C_ASW'+y]\n",
    "    df['CVAP_ASN'+y] = df['CVAP_ASN'+y] + df['CVAP_ASW'+y]\n",
    "    df['CVAP_2OM'+y] = df['CVAP_2OM'+y] - df['CVAP_AIB'+y] - df['CVAP_AIW'+y] - df['CVAP_BLW'+y] - df['CVAP_ASW'+y]\n",
    "    df['C_2OM'+y] = df['C_2OM'+y] - df['C_AIB'+y] - df['C_AIW'+y] - df['C_BLW'+y] - df['C_ASW'+y] \n",
    "    \n",
    "    return df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "358fbcc4",
   "metadata": {},
   "outputs": [],
   "source": [
    "# List of total population columns\n",
    "tot_cols = ['C_TOT21',\n",
    " 'CVAP_TOT21']\n",
    "\n",
    "# List of non-total population columns\n",
    "non_tot_cols = [\n",
    " 'C_HSP21',\n",
    " 'C_NHS21',\n",
    " 'C_WHT21',\n",
    " 'C_BLK21',\n",
    " 'C_AIA21',\n",
    " 'C_ASN21',\n",
    " 'C_NHP21',\n",
    " 'C_2OM21',\n",
    " 'C_BLW21',\n",
    " 'C_AIW21',\n",
    " 'C_ASW21',\n",
    " 'C_AIB21',\n",
    " 'CVAP_HSP21',\n",
    " 'CVAP_NHS21',\n",
    " 'CVAP_WHT21',\n",
    " 'CVAP_BLK21',\n",
    " 'CVAP_AIA21',\n",
    " 'CVAP_ASN21',\n",
    " 'CVAP_NHP21',\n",
    " 'CVAP_2OM21',\n",
    " 'CVAP_BLW21',\n",
    " 'CVAP_AIW21',\n",
    " 'CVAP_ASW21',\n",
    " 'CVAP_AIB21']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "2dc54907",
   "metadata": {},
   "outputs": [],
   "source": [
    "def get_state_bg_cvap(state):\n",
    "    '''\n",
    "    Add code to retrieve the bg data\n",
    "    ''' \n",
    "    raise ValueError('Add In Code Here')\n",
    "    return True\n",
    "    \n",
    "def get_state_block_pl(state):\n",
    "    '''\n",
    "    Add code to retrieve block-level PL data here\n",
    "    '''\n",
    "    raise ValueError('Add In Code Here')\n",
    "    return True"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "3f2bbae7",
   "metadata": {},
   "outputs": [],
   "source": [
    "def run_disagg_changed(state):\n",
    "    '''Runs a disaggregation to the block-level from CVAP data'''\n",
    "    \n",
    "    # Set the state abbreviation to lower case\n",
    "    state = state.lower()\n",
    "    \n",
    "    # Get the block-group CVAP data and block-level PL data\n",
    "    state_bg_cvap = get_state_bg_cvap(state)\n",
    "    state_block_pl = get_state_block_pl(state)\n",
    "    \n",
    "    # PL-Data: Rename the columns to their proxies using the above\n",
    "    state_block_pl.rename(columns = update_dict, inplace = True)\n",
    "    state_block_pl = define_multi_cols(state_block_pl)\n",
    "    \n",
    "    # PL-Data: Create a GEOID for block groups\n",
    "    state_block_pl[\"BLKGRP\"] = state_block_pl[\"GEOID20\"].astype(str).str.zfill(15).str[0:12]\n",
    "    \n",
    "    # PL-Data: Create a count variable for number of blocks in block group\n",
    "    state_block_pl[\"COUNT\"] = 1\n",
    "    \n",
    "    # PL-Data: Aggregate the block groups together, clean indices\n",
    "    state_bg_pl = state_block_pl.groupby([\"BLKGRP\"]).sum()\n",
    "    state_bg_pl.reset_index(inplace = True, drop = False)\n",
    "    \n",
    "    # CVAP-Data: Clean the GEOID20, call it BLKGRP\n",
    "    state_bg_cvap[\"GEOID20\"] = state_bg_cvap[\"GEOID20\"].astype(str).str.zfill(12)\n",
    "    state_bg_cvap.rename(columns = {\"GEOID20\":\"BLKGRP\"}, inplace = True)\n",
    "    \n",
    "    # Merge the two files together\n",
    "    merged_data = pd.merge(state_block_pl, state_bg_pl, on = \"BLKGRP\", how = \"left\", indicator = \"ind_1\", suffixes = [\"_block\",\"_bg\"] )\n",
    "    merged_data_final = pd.merge(merged_data, state_bg_cvap, on = \"BLKGRP\", how = \"left\", indicator = \"ind_2\")\n",
    "\n",
    "    # Mapping for total columns\n",
    "    col_mapping = {\"C_TOT21\":\"P0010001\", 'CVAP_TOT21':\"P0030001\"}\n",
    "    \n",
    "    # Iterate over the total columns first\n",
    "    for val in tot_cols:\n",
    "        merged_data_final[val+\"_DISAGG\"] = np.where(merged_data_final[val]==0,0,\n",
    "                                                    np.where(merged_data_final[val+\"_bg\"]!=0, (merged_data_final[val+\"_block\"]/merged_data_final[val+\"_bg\"]) * merged_data_final[val], \n",
    "                                                        np.where(merged_data_final[col_mapping[val]+\"_bg\"]==0, (1/merged_data_final[\"COUNT_bg\"])*merged_data_final[val],(merged_data_final[col_mapping[val]+\"_block\"]/merged_data_final[col_mapping[val]+\"_bg\"]) * merged_data_final[val]) \n",
    "                                                            )\n",
    "                                                    )\n",
    "                                                                 \n",
    "    # Iterate over the remaining columns after\n",
    "    for val in non_tot_cols:\n",
    "        merged_data_final[val+\"_DISAGG\"] = np.where(merged_data_final[val]==0,0,\n",
    "                                                    np.where(merged_data_final[val+\"_bg\"]!=0, (merged_data_final[val+\"_block\"]/merged_data_final[val+\"_bg\"]) * merged_data_final[val], \n",
    "                                                        np.where(\"VAP\" in val, (merged_data_final[\"CVAP_TOT21_DISAGG\"]/merged_data_final[\"CVAP_TOT21\"]) * merged_data_final[val],\n",
    "                                                                    (merged_data_final[\"C_TOT21_DISAGG\"]/merged_data_final[\"C_TOT21\"]) * merged_data_final[val]\n",
    "                                                                )\n",
    "                                                            )\n",
    "                                                        )\n",
    "    \n",
    "    # Filter down to the relevant columns\n",
    "    merged_data_final_export = merged_data_final[[\"GEOID20_block\"]+[i for i in merged_data_final.columns if \"_DISAGG\" in i]]\n",
    "    \n",
    "    # Export the files to a particular path\n",
    "    if not os.path.exists(\"./2021_cvap_disagg/\"+state+\"/\"):\n",
    "        os.mkdir(\"./2021_cvap_disagg/\"+state+\"/\")\n",
    "    \n",
    "    # Export to CSV\n",
    "    merged_data_final_export.to_csv(\"./2021_cvap_disagg/\"+state+\"/\"+state+\"_2021_cvap_block.csv\", index = False)    "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "7d1ce424",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Iterate over the abbreviations\n",
    "for abbrev in ['al','ak','az','ar','ca','co','ct','de','fl','ga','hi','id','il','in','ia','ks','ky','la','me','md','ma','mi','mn','ms','mo','mt','ne','nv','nh','nj','nm','ny','nc','nd','oh','ok','or','pa','ri','sc','sd','tn','tx','ut','vt','va','wa','wv','wi','wy']:\n",
    "    # Start timer\n",
    "    start_process_time = time.time()\n",
    "    \n",
    "    # Run disagg\n",
    "    run_disagg_changed(abbrev)\n",
    "    \n",
    "    # End timer\n",
    "    end_process_time = time.time()\n",
    "    \n",
    "    # Write to a .txt file\n",
    "    print(abbrev + \" took \" +  str(round(end_process_time - start_process_time,3)) + \" seconds \")\n",
    "    with open('log.txt', 'a') as t:\n",
    "        t.write(abbrev + \" took \" +  str(round(end_process_time - start_process_time,3)) + \" seconds \" + \"\\n\")\n",
    "    t.close()"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.8.13"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}