3. Get ACS5 data from API.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Listing the variables we need...\n",
      "Looking up state names and codes...\n",
      "\n",
      "\n",
      "Will get 105 variables!\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "['metadata', 'data']"
      ]
     },
     "execution_count": 1,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "theyear = 2012\n",
    "debug = 2\n",
    "# 2022: Got 105/28331 variables for 84,415 geographies in 7 minutes 50 seconds!\n",
    "# 2021: Got 105/28038 variables for 84,414 geographies in 8 minutes 15 seconds!\n",
    "# 2020: Got 105/36538 variables for 84,414 geographies in 8 minutes 28 seconds!\n",
    "# 2019: Got 105/35704 variables for 73,056 geographies in 7 minutes 57 seconds!\n",
    "# 2018: Got 105/35676 variables for 73,056 geographies in 8 minutes 10 seconds!\n",
    "# 2017: Got 105/33923 variables for 73,056 geographies in 7 minutes 43 seconds!\n",
    "# 2016: Got 105/31800 variables for 73,056 geographies in 8 minutes 17 seconds!\n",
    "# 2015: Got 105/31717 variables for 73,056 geographies in 8 minutes 29 seconds!\n",
    "# 2014: Got 105/31908 variables for 73,056 geographies in 9 minutes 2 seconds!\n",
    "# 2013: Got 105/16893 variables for 73,056 geographies in 9 minutes 16 seconds!\n",
    "# 2012: Got 105/22661 variables for 73,056 geographies in 8 minutes 30 seconds!\n",
    "\n",
    "import numpy as np\n",
    "import pandas\n",
    "import time\n",
    "from pprint import pprint\n",
    "import geopandas\n",
    "import os\n",
    "import urllib\n",
    "import time\n",
    "import shutil\n",
    "import requests\n",
    "import io\n",
    "\n",
    "pandas.set_option('display.max_colwidth', None)\n",
    "\n",
    "\n",
    "apikey = '92801b058cccd057649dfe29d55d185387d1363a'\n",
    "bad_data_value = -666666666\n",
    "\n",
    "basedir = '/home/idies/workspace/21cc/Data/Census/'\n",
    "\n",
    "\n",
    "outdir = basedir + 'ACS5/'\n",
    "if not(os.path.exists(outdir)):\n",
    "    os.makedirs(outdir)\n",
    "\n",
    "\n",
    "yeardir = outdir + '{0:.0f}/'.format(theyear)\n",
    "if not(os.path.exists(yeardir)):\n",
    "    os.makedirs(yeardir)\n",
    "\n",
    "metadir = yeardir + 'metadata/'\n",
    "if not(os.path.exists(metadir)):\n",
    "    os.makedirs(metadir)\n",
    "    \n",
    "datadir = yeardir + 'data/'    \n",
    "if not(os.path.exists(datadir)):\n",
    "    os.makedirs(datadir)\n",
    "\n",
    "# geodir = yeardir + 'geo/'\n",
    "# if not(os.path.exists(datadir)):\n",
    "#     os.makedirs(geodir)\n",
    "    \n",
    "shapefiledir = basedir+'Census/Data/Shapefiles/TIGER/{0:.0f}'.format(theyear)\n",
    "\n",
    "extras_dir = '/home/idies/workspace/Storage/raddick/census/extras/'\n",
    "\n",
    "tempdir = '/home/idies/workspace/Temporary/raddick/cra_scratch/acs5/temp/'\n",
    "if not(os.path.exists(tempdir)):\n",
    "    os.makedirs(tempdir)    \n",
    "\n",
    "\n",
    "g = 0\n",
    "\n",
    "print('Listing the variables we need...')\n",
    "\n",
    "varnames = 'for_cra_analysis_mac'\n",
    "\n",
    "thevars = [\n",
    "#     'FILEID',\n",
    "#  'FILETYPE',\n",
    "#  'STUSAB',\n",
    "#  'CHARITER',\n",
    "#  'SEQUENCE',\n",
    "#  'LOGRECNO',\n",
    "#  'B00001_001',\n",
    "#  'B00002_001',\n",
    " 'B01001_001',\n",
    " 'B02001_001',\n",
    " 'B02001_002',\n",
    " 'B02001_003',\n",
    " 'B08013_001',\n",
    " 'B11001_001',\n",
    " 'B11001_002',\n",
    " 'B11001_006',\n",
    " 'B11001_007',\n",
    " 'B11001A_001', \n",
    " 'B11001B_001',\n",
    "'B01001_011',\n",
    "'B01001_012',\n",
    "'B01001_013',\n",
    "'B01001_014',\n",
    "'B01001_015',\n",
    "'B01001_016',\n",
    "'B01001_017',\n",
    "'B01001_018',\n",
    "'B01001_019',\n",
    "'B01001_020',\n",
    "'B01001_021',\n",
    "'B01001_022',\n",
    "'B01001_023',\n",
    "'B01001_024',\n",
    "'B01001_025',\n",
    "'B01001_026',\n",
    "'B01001_027',\n",
    "'B01001_028',\n",
    "'B01001_029',\n",
    "'B01001_030',\n",
    "'B01001_031',\n",
    "'B01001_032',\n",
    "'B01001_033',\n",
    "'B01001_034',\n",
    "'B01001_035',\n",
    "'B01001_036',\n",
    "'B01001_037',\n",
    "'B01001_038',\n",
    "'B01001_039',\n",
    "'B01001_040',\n",
    "'B01001_041',\n",
    "'B01001_042',\n",
    "'B01001_043',\n",
    "'B01001_044',\n",
    "'B01001_045',\n",
    "'B01001_046',\n",
    "'B01001_047',\n",
    "'B01001_048',\n",
    "'B01001_049',                                                                \n",
    " 'B15003_001',\n",
    " 'B15003_002',\n",
    " 'B15003_003',\n",
    " 'B15003_004',\n",
    " 'B15003_005',\n",
    " 'B15003_006',\n",
    " 'B15003_007',\n",
    " 'B15003_008',\n",
    " 'B15003_009',\n",
    " 'B15003_010',\n",
    " 'B15003_011',\n",
    " 'B15003_012',\n",
    " 'B15003_013',\n",
    " 'B15003_014',\n",
    " 'B15003_015',\n",
    " 'B15003_016',\n",
    " 'B15003_017',\n",
    " 'B15003_018',\n",
    " 'B15003_019',\n",
    " 'B15003_020',\n",
    " 'B15003_021',\n",
    " 'B15003_022',\n",
    " 'B15003_023',\n",
    " 'B15003_024',\n",
    " 'B15003_025',\n",
    " 'B17001_001',\n",
    " 'B17001_002',\n",
    " 'B17001A_001',\n",
    " 'B17001A_002',\n",
    " 'B17001B_001',\n",
    " 'B17001B_002',\n",
    " 'B19013_001',\n",
    " 'B19013A_001',\n",
    " 'B19013B_001',\n",
    " 'B19113_001',\n",
    " 'B23025_002',\n",
    " 'B23025_005',\n",
    " 'B25002_001',\n",
    " 'B25002_002',\n",
    " 'B25002_003',\n",
    " 'B25003_001',\n",
    " 'B25003_002',\n",
    " 'B25003_003',\n",
    " 'B25003A_001',\n",
    " 'B25003A_002',\n",
    " 'B25003A_003',\n",
    " 'B25003B_001',\n",
    " 'B25003B_002',\n",
    " 'B25003B_003',\n",
    " 'B25077_001',\n",
    " 'B25034_001',\n",
    " 'B25035_001']\n",
    "\n",
    "\n",
    "print('Looking up state names and codes...')        \n",
    "print('\\n')\n",
    "#sorted([x for x in os.listdir(shapefiledir+'TRACT') if x[-4:] == '.shp'])[0]\n",
    "state_codes_df = pandas.read_csv(extras_dir+'statecodes.csv')\n",
    "state_codes_df = state_codes_df.set_index('STUSAB')\n",
    "state_codes_df = state_codes_df.drop(['AS', 'GU', 'MP', 'PR', 'UM', 'VI'], axis=0)\n",
    "statecodes = ['{0:02d}'.format(x) for x in state_codes_df['STATE'].tolist()]\n",
    "\n",
    "print('Will get {0:,.0f} variables!'.format(len(thevars)+3))\n",
    "\n",
    "os.listdir(yeardir)\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Get files for variable descriptions"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "scrolled": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "['ACS2012_5-Year_TableShells.xls', 'Sequence_Number_and_Table_Number_Lookup.xls', 'variables.csv']\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Table\\nID</th>\n",
       "      <th>Line</th>\n",
       "      <th>Unique ID</th>\n",
       "      <th>Stub</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>B00001</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>UNWEIGHTED SAMPLE COUNT OF THE POPULATION</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>B00001</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Universe:  Total population</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>B00001</td>\n",
       "      <td>1.0</td>\n",
       "      <td>B00001001</td>\n",
       "      <td>Total</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25741</th>\n",
       "      <td>C27016</td>\n",
       "      <td>37.0</td>\n",
       "      <td>C27016037</td>\n",
       "      <td>With health insurance coverage</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25742</th>\n",
       "      <td>C27016</td>\n",
       "      <td>38.0</td>\n",
       "      <td>C27016038</td>\n",
       "      <td>No health insurance coverage</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25743</th>\n",
       "      <td>C27016</td>\n",
       "      <td>39.0</td>\n",
       "      <td>C27016039</td>\n",
       "      <td>65 years and over:</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25744</th>\n",
       "      <td>C27016</td>\n",
       "      <td>40.0</td>\n",
       "      <td>C27016040</td>\n",
       "      <td>With health insurance coverage</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25745</th>\n",
       "      <td>C27016</td>\n",
       "      <td>41.0</td>\n",
       "      <td>C27016041</td>\n",
       "      <td>No health insurance coverage</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>25746 rows × 4 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "      Table\\nID  Line  Unique ID                                       Stub\n",
       "0           NaN   NaN        NaN                                        NaN\n",
       "1        B00001   NaN        NaN  UNWEIGHTED SAMPLE COUNT OF THE POPULATION\n",
       "2        B00001   NaN        NaN                Universe:  Total population\n",
       "3        B00001   1.0  B00001001                                      Total\n",
       "4           NaN   NaN        NaN                                        NaN\n",
       "...         ...   ...        ...                                        ...\n",
       "25741    C27016  37.0  C27016037             With health insurance coverage\n",
       "25742    C27016  38.0  C27016038               No health insurance coverage\n",
       "25743    C27016  39.0  C27016039                         65 years and over:\n",
       "25744    C27016  40.0  C27016040             With health insurance coverage\n",
       "25745    C27016  41.0  C27016041               No health insurance coverage\n",
       "\n",
       "[25746 rows x 4 columns]"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "if (theyear >= 2021):\n",
    "    r = requests.get('https://www2.census.gov/programs-surveys/acs/summary_file/{0:.0f}/table-based-SF/documentation/ACS{0:.0f}5YR_Table_Shells.txt'.format(theyear))\n",
    "    r.encoding = 'utf-8'\n",
    "    with io.open(metadir+'ACS{0:.0f}5YR_Table_Shells.txt'.format(theyear), 'w') as f:\n",
    "        f.write(r.text)\n",
    "    df = pandas.read_csv(metadir+'ACS{0:.0f}5YR_Table_Shells.txt'.format(theyear), sep='|', encoding='utf-8', low_memory=False)\n",
    "elif (theyear >= 2014):\n",
    "    r = requests.get('https://www2.census.gov/programs-surveys/acs/summary_file/{0:.0f}/documentation/user_tools/ACS{0:.0f}_Table_Shells.xlsx'.format(theyear))\n",
    "    with io.open(metadir+'ACS{0:.0f}5YR_Table_Shells.xlsx'.format(theyear), 'wb') as f:\n",
    "        f.write(r.content)\n",
    "    df = pandas.read_excel(metadir+'ACS{0:.0f}5YR_Table_Shells.xlsx'.format(theyear))\n",
    "elif (theyear == 2013):\n",
    "    r = requests.get('https://www2.census.gov/programs-surveys/acs/summary_file/{0:.0f}/documentation/user_tools/ACS{0:.0f}_TableShells.xls'.format(theyear))\n",
    "    with io.open(metadir+'ACS{0:.0f}_TableShells.xls'.format(theyear), 'wb') as f:\n",
    "        f.write(r.content)\n",
    "    df = pandas.read_excel(metadir+'ACS{0:.0f}_TableShells.xls'.format(theyear))\n",
    "    \n",
    "    r = requests.get('https://www2.census.gov/programs-surveys/acs/summary_file/{0:.0f}/documentation/user_tools/ACS_5yr_Seq_Table_Number_Lookup.xls'.format(theyear))\n",
    "    with io.open(metadir+'ACS_5yr_Seq_Table_Number_Lookup.xls', 'wb') as f:\n",
    "        f.write(r.content)\n",
    "    gdf = pandas.read_excel(metadir+'ACS_5yr_Seq_Table_Number_Lookup.xls')    \n",
    "else:\n",
    "    r = requests.get('https://www2.census.gov/programs-surveys/acs/summary_file/{0:.0f}/documentation/5_year/user_tools/ACS{0:.0f}_5-Year_TableShells.xls'.format(theyear))\n",
    "    with io.open(metadir+'ACS{0:.0f}_5-Year_TableShells.xls'.format(theyear), 'wb') as f:\n",
    "        f.write(r.content)\n",
    "    df = pandas.read_excel(metadir+'ACS{0:.0f}_5-Year_TableShells.xls'.format(theyear))\n",
    "    \n",
    "    r = requests.get('https://www2.census.gov/programs-surveys/acs/summary_file/{0:.0f}/documentation/5_year/user_tools/Sequence_Number_and_Table_Number_Lookup.xls'.format(theyear))\n",
    "    with io.open(metadir+'Sequence_Number_and_Table_Number_Lookup.xls', 'wb') as f:\n",
    "        f.write(r.content)\n",
    "    gdf = pandas.read_excel(metadir+'Sequence_Number_and_Table_Number_Lookup.xls')\n",
    "    \n",
    "    \n",
    "print(os.listdir(metadir))\n",
    "df\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {
    "scrolled": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Processing metadata for 2012...\n",
      "\tFinding table information...\n",
      "\tGenerating variable names...\n",
      "\tFinding variable information from tables...\n",
      "Writing out variables...\n",
      "Got 22,661 variables in 72.9 seconds!\n"
     ]
    }
   ],
   "source": [
    "# # Get variable descriptoins\n",
    "#### 2013: Need to save Excel file as .csv\n",
    "\n",
    "s = time.time()\n",
    "# print(metadir)\n",
    "# print(os.listdir(metadir))\n",
    "\n",
    "print('Processing metadata for {0:.0f}...'.format(theyear))\n",
    "\n",
    "if (theyear >= 2021):\n",
    "    metadata_df = pandas.read_csv(metadir+'ACS{0:.0f}5YR_Table_Shells.txt'.format(theyear), sep='|')\n",
    "    metadata_df = metadata_df.set_index('Unique ID')\n",
    "    metadata_df = metadata_df[['Table ID', 'Label', 'Title', 'Universe']]\n",
    "    print('Writing out variables file...')\n",
    "    metadata_df.to_csv(metadir+'variables.csv')\n",
    "elif (theyear >= 2015):\n",
    "    if (debug >= 1):\n",
    "        print('\\tReading file...')\n",
    "    metadata_df = pandas.read_excel(metadir+'ACS{0:.0f}5YR_Table_Shells.xlsx'.format(theyear))\n",
    "\n",
    "    metadata_df = metadata_df.rename(columns = {'UniqueID': 'Unique ID'})\n",
    "    metadata_df = metadata_df.rename(columns = {'Table\\nID': 'Table ID'})\n",
    "    metadata_df.index.name = 'rownumber'\n",
    "\n",
    "    metadata_df = metadata_df.rename(columns={'UniqueID': 'Unique ID', 'Table\\nID': 'Table ID'})\n",
    "\n",
    "    duplicated_vars_list = metadata_df.groupby(\"Unique ID\").filter(lambda x: len(x) > 1)['Unique ID'].drop_duplicates().tolist()\n",
    "\n",
    "    if (len(duplicated_vars_list) > 0):\n",
    "        if (debug >= 1):\n",
    "            print('Finding and fixing duplicated variable names...')\n",
    "\n",
    "        metadata_df = metadata_df.assign(unique_id_original = metadata_df['Unique ID'])\n",
    "        previous_var = ''\n",
    "        cnt = 0\n",
    "        for ix, thisrow in metadata_df[metadata_df['Unique ID'].isin(duplicated_vars_list)].iterrows():\n",
    "            if (thisrow['Unique ID'] != previous_var):\n",
    "                cnt = 0\n",
    "                previous_var = thisrow['Unique ID']\n",
    "            else:\n",
    "                cnt += 1\n",
    "            metadata_df.loc[ix, 'Unique ID'] = '{0:}_{1:.0f}'.format(thisrow['Unique ID'], cnt)\n",
    "\n",
    "    if (debug >= 1):\n",
    "        print('\\tFinding table information...')\n",
    "    tables_df = pandas.DataFrame(\n",
    "        data=None, \n",
    "        columns=['Title', 'Universe'], \n",
    "        index=metadata_df['Table ID'][\n",
    "            (metadata_df['Table ID'].notnull())\n",
    "            & (metadata_df['Table ID'] != ' ')\n",
    "        ].drop_duplicates().sort_values().tolist()\n",
    "    )\n",
    "\n",
    "    tables_df.loc[:, 'Title'] = metadata_df[\n",
    "        (metadata_df['Table ID'].isin(tables_df.index))\n",
    "        & (metadata_df['Unique ID'].isnull())\n",
    "        & (metadata_df['Stub'].notnull())\n",
    "        & (metadata_df['Stub'].apply(lambda x: 'universe' not in str(x).lower()))\n",
    "        & (metadata_df['Stub'].apply(lambda x: str(x).upper() == str(x)))\n",
    "    ][['Table ID', 'Stub']].set_index('Table ID')\n",
    "\n",
    "    tables_df.loc[:, 'Universe'] = metadata_df[\n",
    "        (metadata_df['Table ID'].isin(tables_df.index))\n",
    "        & (metadata_df['Unique ID'].isnull())\n",
    "        & (metadata_df['Stub'].notnull())\n",
    "        & (metadata_df['Stub'].apply(lambda x: 'universe' in str(x).lower()))\n",
    "    ][['Table ID', 'Stub']].set_index('Table ID')\n",
    "    \n",
    "    tables_df.index.name = 'Table ID'\n",
    "\n",
    "    if (debug >= 1):\n",
    "        print('\\tFinding variable information...')\n",
    "    \n",
    "    variables_df = metadata_df[metadata_df['Unique ID'].notnull()].merge(tables_df.reset_index(), how='left', on='Table ID').set_index('Unique ID')\n",
    "    variables_df = variables_df.rename(columns={'Stub': 'Label'})\n",
    "    \n",
    "    print('Writing out variables...')\n",
    "    variables_df.to_csv(metadir+'variables.csv')\n",
    "else:  # 2014 or earlier\n",
    "    if (theyear == 2014):\n",
    "        metadata_df = pandas.read_excel(metadir+'ACS{0:.0f}5YR_Table_Shells.xlsx'.format(theyear))    \n",
    "        metadata_df = metadata_df.rename(columns={'Table\\nID': 'Table ID', 'UniqueID': 'Unique ID'})\n",
    "    elif (theyear == 2013):\n",
    "        metadata_df = pandas.read_excel(metadir+'ACS{0:.0f}_TableShells.xls'.format(theyear))\n",
    "        metadata_df = metadata_df.assign(UniqueID = np.nan)\n",
    "        metadata_df = metadata_df.rename(columns={'Table\\nID': 'Table ID', 'UniqueID': 'Unique ID', 'Line Number': 'Line'})\n",
    "    else:  # 2012\n",
    "        metadata_df = pandas.read_excel(metadir+'ACS{0:.0f}_5-Year_TableShells.xls'.format(theyear))\n",
    "        metadata_df = metadata_df.rename(columns={'Table\\nID': 'Table ID'})\n",
    "        \n",
    "    if (debug >= 1):\n",
    "        print('\\tFinding table information...')\n",
    "    tables_df = pandas.DataFrame(\n",
    "        data=None, \n",
    "        columns=['Title', 'Universe'], \n",
    "        index=metadata_df['Table ID'][\n",
    "            (metadata_df['Table ID'].notnull())\n",
    "            & (metadata_df['Table ID'] != ' ')\n",
    "        ].drop_duplicates().sort_values().tolist()\n",
    "    )        \n",
    "    tables_df.loc[:, 'Title'] = metadata_df[\n",
    "            (metadata_df['Table ID'].isin(tables_df.index))\n",
    "            & (metadata_df['Table ID'].notnull())\n",
    "            & (metadata_df['Unique ID'].isnull())\n",
    "            & (metadata_df['Stub'].notnull())\n",
    "            & (metadata_df['Stub'].apply(lambda x: 'universe' not in str(x).lower()))\n",
    "            & (metadata_df['Stub'].apply(lambda x: str(x).upper() == str(x)))\n",
    "            & (metadata_df['Stub'].apply(lambda x: str(x) != \"2\"))\n",
    "            & (metadata_df['Stub'].apply(lambda x: \"DSL\" not in str(x)))\n",
    "    ][['Table ID', 'Stub']].set_index('Table ID')\n",
    "\n",
    "\n",
    "    tables_df.loc[:, 'Universe'] = metadata_df[\n",
    "        (metadata_df['Table ID'].isin(tables_df.index))\n",
    "        & (metadata_df['Unique ID'].isnull())\n",
    "        & (metadata_df['Stub'].notnull())\n",
    "        & (metadata_df['Stub'].apply(lambda x: 'universe' in str(x).lower()))\n",
    "    ][['Table ID', 'Stub']].set_index('Table ID')\n",
    "    \n",
    "    tables_df.index.name = 'Table ID'\n",
    "\n",
    "    if (debug >= 1):\n",
    "        print('\\tGenerating variable names...')\n",
    "\n",
    "    unique_table_list = tables_df.index.tolist()\n",
    "    for thistablename in unique_table_list:\n",
    "        cnt = 0\n",
    "        for ix, thisrow in metadata_df[(metadata_df['Table ID'] == thistablename) & ((metadata_df['Line'].notnull()) & (metadata_df['Line'].apply(lambda x: str(x).strip() != '')))].iterrows():\n",
    "            cnt += 1\n",
    "            metadata_df.loc[ix, 'Unique ID'] = '{0:}_{1:03d}'.format(thisrow['Table ID'], cnt)\n",
    "    \n",
    "    if (debug >= 1):\n",
    "        print('\\tFinding variable information from tables...')\n",
    "    \n",
    "    variables_df = metadata_df[metadata_df['Unique ID'].notnull()].merge(tables_df.reset_index(), how='left', on='Table ID').set_index('Unique ID')\n",
    "    variables_df = variables_df.rename(columns={'Stub': 'Label'})\n",
    "\n",
    "    print('Writing out variables...')\n",
    "    variables_df.to_csv(metadir+'variables.csv')\n",
    "    \n",
    "e = time.time()\n",
    "g += e-s\n",
    "if (theyear >= 2021):\n",
    "    print('Got {0:,.0f} variables in {1:,.1f} seconds!'.format(len(metadata_df), e-s))\n",
    "else:\n",
    "    print('Got {0:,.0f} variables in {1:,.1f} seconds!'.format(len(variables_df), e-s))\n",
    "\n",
    "#metadata_df\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "# ['B01001_001', 'B02001_001', 'B02001_002', 'B02001_003', 'B08013_001'] # ...\n",
    "#metadata_df[metadata_df['Unique ID'].notnull()]\n",
    "#variables_df\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Get data from census API"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {
    "scrolled": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Calling census API...\n",
      "\tstate 01...\n",
      "\t\tE section 0...\n",
      "\t\tE section 1...\n",
      "\t\tE section 2...\n",
      "\n",
      "\n",
      "\t\tM section 0...\n",
      "\t\tM section 1...\n",
      "\t\tM section 2...\n",
      "\n",
      "\n",
      "\tstate 02...\n",
      "\t\tE section 0...\n",
      "\t\tE section 1...\n",
      "\t\tE section 2...\n",
      "\n",
      "\n",
      "\t\tM section 0...\n",
      "\t\tM section 1...\n",
      "\t\tM section 2...\n",
      "\n",
      "\n",
      "\tstate 04...\n",
      "\t\tE section 0...\n",
      "\t\tE section 1...\n",
      "\t\tE section 2...\n",
      "\n",
      "\n",
      "\t\tM section 0...\n",
      "\t\tM section 1...\n",
      "\t\tM section 2...\n",
      "\n",
      "\n",
      "\tstate 05...\n",
      "\t\tE section 0...\n",
      "\t\tE section 1...\n",
      "\t\tE section 2...\n",
      "\n",
      "\n",
      "\t\tM section 0...\n",
      "\t\tM section 1...\n",
      "\t\tM section 2...\n",
      "\n",
      "\n",
      "\tstate 06...\n",
      "\t\tE section 0...\n",
      "\t\tE section 1...\n",
      "\t\tE section 2...\n",
      "\n",
      "\n",
      "\t\tM section 0...\n",
      "\t\tM section 1...\n",
      "\t\tM section 2...\n",
      "\n",
      "\n",
      "\tstate 08...\n",
      "\t\tE section 0...\n",
      "\t\tE section 1...\n",
      "\t\tE section 2...\n",
      "\n",
      "\n",
      "\t\tM section 0...\n",
      "\t\tM section 1...\n",
      "\t\tM section 2...\n",
      "\n",
      "\n",
      "\tstate 09...\n",
      "\t\tE section 0...\n",
      "\t\tE section 1...\n",
      "\t\tE section 2...\n",
      "\n",
      "\n",
      "\t\tM section 0...\n",
      "\t\tM section 1...\n",
      "\t\tM section 2...\n",
      "\n",
      "\n",
      "\tstate 10...\n",
      "\t\tE section 0...\n",
      "\t\tE section 1...\n",
      "\t\tE section 2...\n",
      "\n",
      "\n",
      "\t\tM section 0...\n",
      "\t\tM section 1...\n",
      "\t\tM section 2...\n",
      "\n",
      "\n",
      "\tstate 11...\n",
      "\t\tE section 0...\n",
      "\t\tE section 1...\n",
      "\t\tE section 2...\n",
      "\n",
      "\n",
      "\t\tM section 0...\n",
      "\t\tM section 1...\n",
      "\t\tM section 2...\n",
      "\n",
      "\n",
      "\tstate 12...\n",
      "\t\tE section 0...\n",
      "\t\tE section 1...\n",
      "\t\tE section 2...\n",
      "\n",
      "\n",
      "\t\tM section 0...\n",
      "\t\tM section 1...\n",
      "\t\tM section 2...\n",
      "\n",
      "\n",
      "\tstate 13...\n",
      "\t\tE section 0...\n",
      "\t\tE section 1...\n",
      "\t\tE section 2...\n",
      "\n",
      "\n",
      "\t\tM section 0...\n",
      "\t\tM section 1...\n",
      "\t\tM section 2...\n",
      "\n",
      "\n",
      "\tstate 15...\n",
      "\t\tE section 0...\n",
      "\t\tE section 1...\n",
      "\t\tE section 2...\n",
      "\n",
      "\n",
      "\t\tM section 0...\n",
      "\t\tM section 1...\n",
      "\t\tM section 2...\n",
      "\n",
      "\n",
      "\tstate 16...\n",
      "\t\tE section 0...\n",
      "\t\tE section 1...\n",
      "\t\tE section 2...\n",
      "\n",
      "\n",
      "\t\tM section 0...\n",
      "\t\tM section 1...\n",
      "\t\tM section 2...\n",
      "\n",
      "\n",
      "\tstate 17...\n",
      "\t\tE section 0...\n",
      "\t\tE section 1...\n",
      "\t\tE section 2...\n",
      "\n",
      "\n",
      "\t\tM section 0...\n",
      "\t\tM section 1...\n",
      "\t\tM section 2...\n",
      "\n",
      "\n",
      "\tstate 18...\n",
      "\t\tE section 0...\n",
      "\t\tE section 1...\n",
      "\t\tE section 2...\n",
      "\n",
      "\n",
      "\t\tM section 0...\n",
      "\t\tM section 1...\n",
      "\t\tM section 2...\n",
      "\n",
      "\n",
      "\tstate 19...\n",
      "\t\tE section 0...\n",
      "\t\tE section 1...\n",
      "\t\tE section 2...\n",
      "\n",
      "\n",
      "\t\tM section 0...\n",
      "\t\tM section 1...\n",
      "\t\tM section 2...\n",
      "\n",
      "\n",
      "\tstate 20...\n",
      "\t\tE section 0...\n",
      "\t\tE section 1...\n",
      "\t\tE section 2...\n",
      "\n",
      "\n",
      "\t\tM section 0...\n",
      "\t\tM section 1...\n",
      "\t\tM section 2...\n",
      "\n",
      "\n",
      "\tstate 21...\n",
      "\t\tE section 0...\n",
      "\t\tE section 1...\n",
      "\t\tE section 2...\n",
      "\n",
      "\n",
      "\t\tM section 0...\n",
      "\t\tM section 1...\n",
      "\t\tM section 2...\n",
      "\n",
      "\n",
      "\tstate 22...\n",
      "\t\tE section 0...\n",
      "\t\tE section 1...\n",
      "\t\tE section 2...\n",
      "\n",
      "\n",
      "\t\tM section 0...\n",
      "\t\tM section 1...\n",
      "\t\tM section 2...\n",
      "\n",
      "\n",
      "\tstate 23...\n",
      "\t\tE section 0...\n",
      "\t\tE section 1...\n",
      "\t\tE section 2...\n",
      "\n",
      "\n",
      "\t\tM section 0...\n",
      "\t\tM section 1...\n",
      "\t\tM section 2...\n",
      "\n",
      "\n",
      "\tstate 24...\n",
      "\t\tE section 0...\n",
      "\t\tE section 1...\n",
      "\t\tE section 2...\n",
      "\n",
      "\n",
      "\t\tM section 0...\n",
      "\t\tM section 1...\n",
      "\t\tM section 2...\n",
      "\n",
      "\n",
      "\tstate 25...\n",
      "\t\tE section 0...\n",
      "\t\tE section 1...\n",
      "\t\tE section 2...\n",
      "\n",
      "\n",
      "\t\tM section 0...\n",
      "\t\tM section 1...\n",
      "\t\tM section 2...\n",
      "\n",
      "\n",
      "\tstate 26...\n",
      "\t\tE section 0...\n",
      "\t\tE section 1...\n",
      "\t\tE section 2...\n",
      "\n",
      "\n",
      "\t\tM section 0...\n",
      "\t\tM section 1...\n",
      "\t\tM section 2...\n",
      "\n",
      "\n",
      "\tstate 27...\n",
      "\t\tE section 0...\n",
      "\t\tE section 1...\n",
      "\t\tE section 2...\n",
      "\n",
      "\n",
      "\t\tM section 0...\n",
      "\t\tM section 1...\n",
      "\t\tM section 2...\n",
      "\n",
      "\n",
      "\tstate 28...\n",
      "\t\tE section 0...\n",
      "\t\tE section 1...\n",
      "\t\tE section 2...\n",
      "\n",
      "\n",
      "\t\tM section 0...\n",
      "\t\tM section 1...\n",
      "\t\tM section 2...\n",
      "\n",
      "\n",
      "\tstate 29...\n",
      "\t\tE section 0...\n",
      "\t\tE section 1...\n",
      "\t\tE section 2...\n",
      "\n",
      "\n",
      "\t\tM section 0...\n",
      "\t\tM section 1...\n",
      "\t\tM section 2...\n",
      "\n",
      "\n",
      "\tstate 30...\n",
      "\t\tE section 0...\n",
      "\t\tE section 1...\n",
      "\t\tE section 2...\n",
      "\n",
      "\n",
      "\t\tM section 0...\n",
      "\t\tM section 1...\n",
      "\t\tM section 2...\n",
      "\n",
      "\n",
      "\tstate 31...\n",
      "\t\tE section 0...\n",
      "\t\tE section 1...\n",
      "\t\tE section 2...\n",
      "\n",
      "\n",
      "\t\tM section 0...\n",
      "\t\tM section 1...\n",
      "\t\tM section 2...\n",
      "\n",
      "\n",
      "\tstate 32...\n",
      "\t\tE section 0...\n",
      "\t\tE section 1...\n",
      "\t\tE section 2...\n",
      "\n",
      "\n",
      "\t\tM section 0...\n",
      "\t\tM section 1...\n",
      "\t\tM section 2...\n",
      "\n",
      "\n",
      "\tstate 33...\n",
      "\t\tE section 0...\n",
      "\t\tE section 1...\n",
      "\t\tE section 2...\n",
      "\n",
      "\n",
      "\t\tM section 0...\n",
      "\t\tM section 1...\n",
      "\t\tM section 2...\n",
      "\n",
      "\n",
      "\tstate 34...\n",
      "\t\tE section 0...\n",
      "\t\tE section 1...\n",
      "\t\tE section 2...\n",
      "\n",
      "\n",
      "\t\tM section 0...\n",
      "\t\tM section 1...\n",
      "\t\tM section 2...\n",
      "\n",
      "\n",
      "\tstate 35...\n",
      "\t\tE section 0...\n",
      "\t\tE section 1...\n",
      "\t\tE section 2...\n",
      "\n",
      "\n",
      "\t\tM section 0...\n",
      "\t\tM section 1...\n",
      "\t\tM section 2...\n",
      "\n",
      "\n",
      "\tstate 36...\n",
      "\t\tE section 0...\n",
      "\t\tE section 1...\n",
      "\t\tE section 2...\n",
      "\n",
      "\n",
      "\t\tM section 0...\n",
      "\t\tM section 1...\n",
      "\t\tM section 2...\n",
      "\n",
      "\n",
      "\tstate 37...\n",
      "\t\tE section 0...\n",
      "\t\tE section 1...\n",
      "\t\tE section 2...\n",
      "\n",
      "\n",
      "\t\tM section 0...\n",
      "\t\tM section 1...\n",
      "\t\tM section 2...\n",
      "\n",
      "\n",
      "\tstate 38...\n",
      "\t\tE section 0...\n",
      "\t\tE section 1...\n",
      "\t\tE section 2...\n",
      "\n",
      "\n",
      "\t\tM section 0...\n",
      "\t\tM section 1...\n",
      "\t\tM section 2...\n",
      "\n",
      "\n",
      "\tstate 39...\n",
      "\t\tE section 0...\n",
      "\t\tE section 1...\n",
      "\t\tE section 2...\n",
      "\n",
      "\n",
      "\t\tM section 0...\n",
      "\t\tM section 1...\n",
      "\t\tM section 2...\n",
      "\n",
      "\n",
      "\tstate 40...\n",
      "\t\tE section 0...\n",
      "\t\tE section 1...\n",
      "\t\tE section 2...\n",
      "\n",
      "\n",
      "\t\tM section 0...\n",
      "\t\tM section 1...\n",
      "\t\tM section 2...\n",
      "\n",
      "\n",
      "\tstate 41...\n",
      "\t\tE section 0...\n",
      "\t\tE section 1...\n",
      "\t\tE section 2...\n",
      "\n",
      "\n",
      "\t\tM section 0...\n",
      "\t\tM section 1...\n",
      "\t\tM section 2...\n",
      "\n",
      "\n",
      "\tstate 42...\n",
      "\t\tE section 0...\n",
      "\t\tE section 1...\n",
      "\t\tE section 2...\n",
      "\n",
      "\n",
      "\t\tM section 0...\n",
      "\t\tM section 1...\n",
      "\t\tM section 2...\n",
      "\n",
      "\n",
      "\tstate 44...\n",
      "\t\tE section 0...\n",
      "\t\tE section 1...\n",
      "\t\tE section 2...\n",
      "\n",
      "\n",
      "\t\tM section 0...\n",
      "\t\tM section 1...\n",
      "\t\tM section 2...\n",
      "\n",
      "\n",
      "\tstate 45...\n",
      "\t\tE section 0...\n",
      "\t\tE section 1...\n",
      "\t\tE section 2...\n",
      "\n",
      "\n",
      "\t\tM section 0...\n",
      "\t\tM section 1...\n",
      "\t\tM section 2...\n",
      "\n",
      "\n",
      "\tstate 46...\n",
      "\t\tE section 0...\n",
      "\t\tE section 1...\n",
      "\t\tE section 2...\n",
      "\n",
      "\n",
      "\t\tM section 0...\n",
      "\t\tM section 1...\n",
      "\t\tM section 2...\n",
      "\n",
      "\n",
      "\tstate 47...\n",
      "\t\tE section 0...\n",
      "\t\tE section 1...\n",
      "\t\tE section 2...\n",
      "\n",
      "\n",
      "\t\tM section 0...\n",
      "\t\tM section 1...\n",
      "\t\tM section 2...\n",
      "\n",
      "\n",
      "\tstate 48...\n",
      "\t\tE section 0...\n",
      "\t\tE section 1...\n",
      "\t\tE section 2...\n",
      "\n",
      "\n",
      "\t\tM section 0...\n",
      "\t\tM section 1...\n",
      "\t\tM section 2...\n",
      "\n",
      "\n",
      "\tstate 49...\n",
      "\t\tE section 0...\n",
      "\t\tE section 1...\n",
      "\t\tE section 2...\n",
      "\n",
      "\n",
      "\t\tM section 0...\n",
      "\t\tM section 1...\n",
      "\t\tM section 2...\n",
      "\n",
      "\n",
      "\tstate 50...\n",
      "\t\tE section 0...\n",
      "\t\tE section 1...\n",
      "\t\tE section 2...\n",
      "\n",
      "\n",
      "\t\tM section 0...\n",
      "\t\tM section 1...\n",
      "\t\tM section 2...\n",
      "\n",
      "\n",
      "\tstate 51...\n",
      "\t\tE section 0...\n",
      "\t\tE section 1...\n",
      "\t\tE section 2...\n",
      "\n",
      "\n",
      "\t\tM section 0...\n",
      "\t\tM section 1...\n",
      "\t\tM section 2...\n",
      "\n",
      "\n",
      "\tstate 53...\n",
      "\t\tE section 0...\n",
      "\t\tE section 1...\n",
      "\t\tE section 2...\n",
      "\n",
      "\n",
      "\t\tM section 0...\n",
      "\t\tM section 1...\n",
      "\t\tM section 2...\n",
      "\n",
      "\n",
      "\tstate 54...\n",
      "\t\tE section 0...\n",
      "\t\tE section 1...\n",
      "\t\tE section 2...\n",
      "\n",
      "\n",
      "\t\tM section 0...\n",
      "\t\tM section 1...\n",
      "\t\tM section 2...\n",
      "\n",
      "\n",
      "\tstate 55...\n",
      "\t\tE section 0...\n",
      "\t\tE section 1...\n",
      "\t\tE section 2...\n",
      "\n",
      "\n",
      "\t\tM section 0...\n",
      "\t\tM section 1...\n",
      "\t\tM section 2...\n",
      "\n",
      "\n",
      "\tstate 56...\n",
      "\t\tE section 0...\n",
      "\t\tE section 1...\n",
      "\t\tE section 2...\n",
      "\n",
      "\n",
      "\t\tM section 0...\n",
      "\t\tM section 1...\n",
      "\t\tM section 2...\n",
      "\n",
      "\n",
      "Got 306 files in 6 minutes 14 seconds!\n"
     ]
    }
   ],
   "source": [
    "s = time.time()\n",
    "splitat = 40\n",
    "\n",
    "print('Calling census API...')\n",
    "\n",
    "vartypelist = ['E', 'M']\n",
    "\n",
    "\n",
    "for onestate in statecodes:\n",
    "    print('\\tstate {0:}...'.format(onestate))\n",
    "    nparts = np.divmod(len(thevars), splitat)[0]\n",
    "    for vartype in vartypelist:\n",
    "        for section in range(0,nparts+1):\n",
    "            if (debug >= 1):\n",
    "                print('\\t\\t{0:} section {1:.0f}...'.format(vartype, section))\n",
    "            call = 'https://api.census.gov/data/{0:.0f}/acs/acs5?get='.format(theyear)\n",
    "            lo = section*splitat\n",
    "            hi = section*splitat + splitat\n",
    "            if (hi > len(thevars)):\n",
    "                hi = len(thevars)\n",
    "            for i in range(lo, hi-1):\n",
    "                call += '{0:}{1:},'.format(thevars[i], vartype)\n",
    "            call += '{0:}{1:}'.format(thevars[hi-1], vartype)\n",
    "            call += '&for=tract:*&in=state:{0:}&in=county:*&key={1:}'.format(onestate, apikey)    \n",
    "            datafilename = '{0:}{1:}_{2:}temp{3:.0f}.csv'.format(tempdir,onestate,vartype.lower(),section)\n",
    "            with urllib.request.urlopen(call) as response:    \n",
    "                it = response.read()\n",
    "                with open(datafilename, 'wb') as f:\n",
    "                    f.write(it)\n",
    "        print('\\n')\n",
    "\n",
    "e = time.time()\n",
    "g += e-s\n",
    "print('Got {0:} files in {1:,.0f} minutes {2:,.0f} seconds!'.format(len(os.listdir(tempdir)), np.floor((e-s)/60), (e-s)%60))\n",
    "#os.listdir(tempdir)\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Load estimates"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {
    "scrolled": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Loading estimates into dataframe...\n",
      "Fixing columns...\n",
      "Fixing data values...\n",
      "Creating GEOIDs...\n",
      "writing out estimates file...\n",
      "\n",
      "\n",
      "Got estimates for 105 variables for 73,056 geographies in 0 minutes 32 seconds!\n"
     ]
    }
   ],
   "source": [
    "s = time.time()\n",
    "debug = 0\n",
    "print('Loading estimates into dataframe...')\n",
    "if (debug >= 1):\n",
    "    print('\\n')\n",
    "edf = pandas.DataFrame()\n",
    "\n",
    "for onestate in statecodes:\n",
    "    if (debug >= 1):\n",
    "        print('\\tReading estiamtes for {0:}...'.format(state_codes_df[state_codes_df['STATE'].apply(lambda x: '{0:02d}'.format(x)) == onestate]['STATE_NAME'].tolist()[0]))\n",
    "    edfs = pandas.DataFrame()\n",
    "    sefiles = sorted([tempdir+x for x in os.listdir(tempdir) if '{0:}_e'.format(onestate) in x])\n",
    "    \n",
    "    for thisfile in sefiles:\n",
    "        edfsi = pandas.read_csv(thisfile)\n",
    "        edfsi = edfsi.rename(columns={'tract]': 'tract'})\n",
    "        unnamed_columns = [x for x in edfsi.columns if 'unnamed' in x.lower()]\n",
    "        for col in unnamed_columns:\n",
    "            edfsi = edfsi.drop(col, axis=1)        \n",
    "        if (len(edfs) == 0):\n",
    "            edfs = edfsi\n",
    "        else:\n",
    "            edfs = edfs.merge(edfsi, how='left', on=['state', 'county', 'tract'])\n",
    "    if (debug >= 2):\n",
    "        print('\\t\\tGot {0:.0f} variables for {1:,.0f} tracts...'.format(edfs.shape[1], edfs.shape[0]))    \n",
    "    edf = pandas.concat((edf, edfs), axis=0)\n",
    "\n",
    "print('Fixing columns...')\n",
    "edf = edf[['state', 'county', 'tract']+[x for x in edf.columns.tolist() if x not in ['state', 'county', 'tract']]]\n",
    "newcols = [x.replace('\"','').replace(\"'\", \"\").replace('[','').replace(']','') for x in edf.columns.tolist()]\n",
    "for i in range(0, len(newcols)):\n",
    "    if (edf.columns.tolist()[i] != newcols[i]):\n",
    "        edf = edf.rename(columns = {edf.columns.tolist()[i]: newcols[i]})\n",
    "# edf.columns = newcols\n",
    "# edf = edf[newcols]\n",
    "print('Fixing data values...')\n",
    "edf.loc[:, 'tract'] = edf['tract'].apply(lambda x: x.replace(']',''))\n",
    "for col in edf.columns[3:]:\n",
    "    if (edf[col].dtype == 'object'):\n",
    "        if (theyear <= 2014):\n",
    "            edf.loc[:, col] = pandas.to_numeric(edf[col].apply(lambda x: str(x).replace('[', '').replace(\"'\", \"\").replace('\"', '')), errors='coerce')\n",
    "        else:\n",
    "            edf.loc[:, col] = pandas.to_numeric(edf[col].apply(lambda x: x.replace('[', '').replace(\"'\", \"\").replace('\"', '')), errors='coerce')\n",
    "    \n",
    "    if (edf[col].dtype == 'float'):\n",
    "        for ix, thisval in edf[col].iteritems():\n",
    "            if (np.isnan(thisval)):\n",
    "                edf.loc[ix, col] = bad_data_value\n",
    "        edf.loc[:, col] = pandas.to_numeric(edf[col], errors='coerce')\n",
    "    edf.loc[:, col] = edf[col]\n",
    "\n",
    "\n",
    "edf = edf.replace(bad_data_value, np.nan)\n",
    "\n",
    "print('Creating GEOIDs...')\n",
    "edf = edf.assign(GEOID = edf.apply(lambda row: '14000US{0:02d}{1:03d}{2:}'.format(row['state'],row['county'],row['tract']), axis=1))\n",
    "edf = edf.set_index('GEOID')\n",
    "\n",
    "print('writing out estimates file...')\n",
    "edf.to_csv(datadir+'estimates.csv')\n",
    "\n",
    "e = time.time()\n",
    "g += e-s\n",
    "print('\\n')\n",
    "print('Got estimates for {0:,.0f} variables for {1:,.0f} geographies in {2:,.0f} minutes {3:,.0f} seconds!'.format(edf.shape[1], edf.shape[0], np.floor((e-s)/60), (e-s)%60))\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Load margins of error"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Loading margins of error into dataframe...\n",
      "Fixing columns...\n",
      "Fixing data values...\n",
      "Creating GEOIDs...\n",
      "writing out margins of error file...\n",
      "\n",
      "\n",
      "Margins of error: got 105 variables for 73,056 geographies in 0 minutes 26 seconds!\n"
     ]
    }
   ],
   "source": [
    "s = time.time()\n",
    "print('Loading margins of error into dataframe...')\n",
    "if (debug >= 1):\n",
    "    print('\\n')\n",
    "mdf = pandas.DataFrame()\n",
    "\n",
    "for onestate in statecodes:\n",
    "    if (debug >= 1):\n",
    "        print('\\tReading margins of error for {0:}...'.format(state_codes_df[state_codes_df['STATE'].apply(lambda x: '{0:02d}'.format(x)) == onestate]['STATE_NAME'].tolist()[0]))\n",
    "    mdfs = pandas.DataFrame()\n",
    "    smfiles = sorted([tempdir+x for x in os.listdir(tempdir) if '{0:}_m'.format(onestate) in x])\n",
    "    \n",
    "    for thisfile in smfiles:\n",
    "        mdfsi = pandas.read_csv(thisfile)\n",
    "        mdfsi = mdfsi.rename(columns={'tract]': 'tract'})\n",
    "        unnamed_columns = [x for x in mdfsi.columns if 'unnamed' in x.lower()]\n",
    "        for col in unnamed_columns:\n",
    "            mdfsi = mdfsi.drop(col, axis=1)        \n",
    "        if (len(mdfs) == 0):\n",
    "            mdfs = mdfsi\n",
    "        else:\n",
    "            mdfs = mdfs.merge(mdfsi, how='left', on=['state', 'county', 'tract'])\n",
    "    if (debug >= 2):\n",
    "        print('\\t\\tGot {0:.0f} variables for {1:,.0f} tracts...'.format(mdfs.shape[1], mdfs.shape[0]))    \n",
    "    mdf = pandas.concat((mdf, mdfs), axis=0)\n",
    "\n",
    "print('Fixing columns...')\n",
    "mdf = mdf[['state', 'county', 'tract']+[x for x in mdf.columns.tolist() if x not in ['state', 'county', 'tract']]]\n",
    "newcols = [x.replace('\"','').replace(\"'\", \"\").replace('[','').replace(']','') for x in mdf.columns.tolist()]\n",
    "for i in range(0, len(newcols)):\n",
    "    if (mdf.columns.tolist()[i] != newcols[i]):\n",
    "        mdf = mdf.rename(columns = {mdf.columns.tolist()[i]: newcols[i]})\n",
    "\n",
    "print('Fixing data values...')\n",
    "mdf.loc[:, 'tract'] = mdf['tract'].apply(lambda x: x.replace(']',''))\n",
    "for col in mdf.columns[3:]:\n",
    "    if (mdf[col].dtype == 'object'):\n",
    "        if (theyear <= 2014):\n",
    "            mdf.loc[:, col] = pandas.to_numeric(mdf[col].apply(lambda x: str(x).replace('[', '').replace(\"'\", \"\").replace('\"', '')), errors='coerce')\n",
    "        else:\n",
    "            mdf.loc[:, col] = pandas.to_numeric(mdf[col].apply(lambda x: x.replace('[', '').replace(\"'\", \"\").replace('\"', '')), errors='coerce')\n",
    "    \n",
    "    if (mdf[col].dtype == 'float'):\n",
    "        for ix, thisval in mdf[col].iteritems():\n",
    "            if (np.isnan(thisval)):\n",
    "                mdf.loc[ix, col] = bad_data_value\n",
    "        mdf.loc[:, col] = pandas.to_numeric(mdf[col], downcast='integer', errors='coerce')\n",
    "        \n",
    "print('Creating GEOIDs...')\n",
    "mdf = mdf.assign(GEOID = mdf.apply(lambda row: '14000US{0:02d}{1:03d}{2:}'.format(row['state'],row['county'],row['tract']), axis=1))\n",
    "mdf = mdf.set_index('GEOID')\n",
    "\n",
    "print('writing out margins of error file...')\n",
    "mdf.to_csv(datadir+'margins_of_error.csv')\n",
    "\n",
    "\n",
    "e = time.time()\n",
    "g += e-s\n",
    "print('\\n')\n",
    "print('Margins of error: got {0:,.0f} variables for {1:,.0f} geographies in {2:,.0f} minutes {3:,.0f} seconds!'.format(mdf.shape[1], mdf.shape[0], np.floor((e-s)/60), (e-s)%60))\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "deleting temp files...\n",
      "Got 105/22661 variables for 73,056 geographies in 8 minutes 30 seconds!\n"
     ]
    }
   ],
   "source": [
    "s = time.time()\n",
    "print('deleting temp files...')\n",
    "for root, dirs, files in os.walk(tempdir, True):\n",
    "    for file in files:\n",
    "        os.remove(root+file)\n",
    "e = time.time()\n",
    "g += e-s\n",
    "\n",
    "\n",
    "if (theyear >= 2021):\n",
    "    print('Got {0:,.0f}/{1:.0f} variables for {2:,.0f} geographies in {3:,.0f} minutes {4:,.0f} seconds!'.format(mdf.shape[1], len(metadata_df), mdf.shape[0], np.floor(g/60), g%60))\n",
    "else:\n",
    "    print('Got {0:,.0f}/{1:.0f} variables for {2:,.0f} geographies in {3:,.0f} minutes {4:,.0f} seconds!'.format(mdf.shape[1], len(variables_df), mdf.shape[0], np.floor(g/60), g%60))\n",
    "\n",
    "\n",
    "#print('Total time: {0:,.0f} minutes {1:,.0f} seconds!'.format(np.floor(g/60), g%60))\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3.8 (py38)",
   "language": "python",
   "name": "py38"
  },
  "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.5"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}