US Census Bureau API & General Economic Info.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 95,
   "id": "50ce56c4",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2023-06-23T22:55:20.541083Z",
     "start_time": "2023-06-23T22:55:20.522022Z"
    }
   },
   "outputs": [],
   "source": [
    "import requests\n",
    "import pandas as pd\n",
    "import dill\n",
    "import matplotlib.pyplot as plt\n",
    "\n",
    "from requests.exceptions import RequestException\n",
    "\n",
    "econ_df = pd.read_pickle('econ_df.pkl')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "45120e74",
   "metadata": {},
   "source": [
    "## Summary / Notes"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "01bca6d1",
   "metadata": {},
   "source": [
    "This notebook ingests and processes data from the Colorado Dept. of Revenue and the US Census Bureau, with the results saved in the dataframe <b>'econ_df.pkl'</b>.\n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "5b6c5f14",
   "metadata": {},
   "source": [
    "### Links:\n",
    "Info on dispensaries by county found here: https://cdor.colorado.gov/data-and-reports/marijuana-data/marijuana-sales-reports <br><br>\n",
    "Small Area Income and Poverty Estimates\n",
    "https://api.census.gov/data/timeseries/poverty/saipe.html\n",
    "\n",
    "US Census American Community Survey has good data, but only begins at 2005.  May revisit later.\n",
    "https://www.census.gov/data/developers/data-sets/acs-1year.html <br>\n",
    "Titled \"American Community Survey: 1-Year Estimates: Detailed Tables 1-Year\" in list of APIs (https://api.census.gov/data.html)\n",
    "\n",
    "Unemployment from USDA ERS: https://www.ers.usda.gov/data-products/county-level-data-sets/county-level-data-sets-download-data/"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "3c7dbf1a",
   "metadata": {},
   "source": [
    "## Marijuana Sales by County"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 86,
   "id": "5a9912f9",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2023-06-08T18:13:11.921003Z",
     "start_time": "2023-06-08T18:13:11.885759Z"
    }
   },
   "outputs": [],
   "source": [
    "# Initial import\n",
    "weed_df = pd.read_csv('Marijuana_Sales_By_County_2014_To_Date_Report.xlsx - County Report - edit.csv')\n",
    "\n",
    "# Convert relevant columns to numeric values\n",
    "weed_df['Medical Marijuana Sales'] = weed_df['Medical Marijuana Sales'].str.replace(r'[^0-9.]', '', regex = True)\n",
    "weed_df['Medical Marijuana Sales'] = pd.to_numeric(weed_df['Medical Marijuana Sales'], errors='coerce')\n",
    "\n",
    "weed_df['Retail Marijuana Sales'] = weed_df['Retail Marijuana Sales'].str.replace(r'[^0-9.]', '', regex = True)\n",
    "weed_df['Retail Marijuana Sales'] = pd.to_numeric(weed_df['Retail Marijuana Sales'], errors='coerce')\n",
    "\n",
    "# Group by year and county\n",
    "weedyear_df = weed_df.groupby(['Year', 'County']).sum().reset_index().drop(columns = 'Month')\n",
    "weedyear_df = weedyear_df.rename(columns = {'Year': 'year', 'County': 'county'})\n",
    "weedyear_df['county'] = weedyear_df['county'].str.upper().str.replace('TOTAL', 'COLORADO')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 110,
   "id": "36df98f9",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2023-06-08T18:20:36.392086Z",
     "start_time": "2023-06-08T18:20:36.374986Z"
    },
    "scrolled": false
   },
   "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>year</th>\n",
       "      <th>county</th>\n",
       "      <th>Medical Marijuana Sales</th>\n",
       "      <th>Retail Marijuana Sales</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2014</td>\n",
       "      <td>COLORADO</td>\n",
       "      <td>380284040.0</td>\n",
       "      <td>303239699.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2014</td>\n",
       "      <td>ADAMS</td>\n",
       "      <td>5781889.0</td>\n",
       "      <td>2749908.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2014</td>\n",
       "      <td>ALAMOSA</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2014</td>\n",
       "      <td>ARAPAHOE</td>\n",
       "      <td>16861284.0</td>\n",
       "      <td>2221928.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2014</td>\n",
       "      <td>ARCHULETA</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</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>422</th>\n",
       "      <td>2023</td>\n",
       "      <td>SAN JUAN</td>\n",
       "      <td>0.0</td>\n",
       "      <td>24753.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>423</th>\n",
       "      <td>2023</td>\n",
       "      <td>SAN MIGUEL</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1618613.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>424</th>\n",
       "      <td>2023</td>\n",
       "      <td>SEDGWICK</td>\n",
       "      <td>0.0</td>\n",
       "      <td>3567541.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>425</th>\n",
       "      <td>2023</td>\n",
       "      <td>SUMMIT</td>\n",
       "      <td>0.0</td>\n",
       "      <td>8169430.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>426</th>\n",
       "      <td>2023</td>\n",
       "      <td>WELD</td>\n",
       "      <td>547775.0</td>\n",
       "      <td>8670223.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>427 rows × 4 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "     year      county  Medical Marijuana Sales  Retail Marijuana Sales\n",
       "0    2014    COLORADO              380284040.0             303239699.0\n",
       "1    2014       ADAMS                5781889.0               2749908.0\n",
       "2    2014     ALAMOSA                      0.0                     0.0\n",
       "3    2014    ARAPAHOE               16861284.0               2221928.0\n",
       "4    2014   ARCHULETA                      0.0                     0.0\n",
       "..    ...         ...                      ...                     ...\n",
       "422  2023    SAN JUAN                      0.0                 24753.0\n",
       "423  2023  SAN MIGUEL                      0.0               1618613.0\n",
       "424  2023    SEDGWICK                      0.0               3567541.0\n",
       "425  2023      SUMMIT                      0.0               8169430.0\n",
       "426  2023        WELD                 547775.0               8670223.0\n",
       "\n",
       "[427 rows x 4 columns]"
      ]
     },
     "execution_count": 110,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "weedyear_df"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "1a63a267",
   "metadata": {},
   "source": [
    "## American Community Survey"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 118,
   "id": "be7d1ff5",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2023-06-23T23:42:14.934960Z",
     "start_time": "2023-06-23T23:42:14.120099Z"
    }
   },
   "outputs": [],
   "source": [
    "# https://api.census.gov/data/2005/acs/acs1?get=NAME,B01001_001E ,B25105_001M,B25105_001MA'\n",
    "url = 'https://api.census.gov/data/2016/acs/acs1'\n",
    "params = {\n",
    "    'get': 'NAME,B25105_001EA,B25105_001M,B25105_001MA',\n",
    "    'for': 'county:*',\n",
    "    'in': 'state:08',\n",
    "}\n",
    "\n",
    "response = requests.get(url, params=params)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 119,
   "id": "83516d0f",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2023-06-23T23:42:17.614795Z",
     "start_time": "2023-06-23T23:42:17.604340Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[['NAME', 'B25105_001EA', 'B25105_001M', 'B25105_001MA', 'state', 'county'],\n",
       " ['Denver County, Colorado', None, '28', None, '08', '031'],\n",
       " ['Jefferson County, Colorado', None, '25', None, '08', '059'],\n",
       " ['Weld County, Colorado', None, '42', None, '08', '123'],\n",
       " ['Douglas County, Colorado', None, '37', None, '08', '035'],\n",
       " ['Boulder County, Colorado', None, '35', None, '08', '013'],\n",
       " ['Pueblo County, Colorado', None, '36', None, '08', '101'],\n",
       " ['Mesa County, Colorado', None, '43', None, '08', '077'],\n",
       " ['El Paso County, Colorado', None, '19', None, '08', '041'],\n",
       " ['Arapahoe County, Colorado', None, '24', None, '08', '005'],\n",
       " ['Broomfield County, Colorado', None, '60', None, '08', '014'],\n",
       " ['Adams County, Colorado', None, '21', None, '08', '001'],\n",
       " ['Larimer County, Colorado', None, '36', None, '08', '069']]"
      ]
     },
     "execution_count": 119,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "response.json()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "2720ff6c",
   "metadata": {},
   "source": [
    "## Small Area Income and Poverty Estimates (SAIPE)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "6cee2d1a",
   "metadata": {},
   "source": [
    "Colorado state code: 08 <br>\n",
    "County codes: ['0', '1', '3', '5', '7', '9', '11', '13', '14', '15', '17', \n",
    "                '19', '21', '23', '25', '27', '29', '31', '33', '35', '37', \n",
    "                '39', '41', '43', '45', '47', '49', '51', '53', '55', '57', \n",
    "                '59', '61', '63', '65', '67', '69', '71', '73', '75', '77', \n",
    "                '79', '81', '83', '85', '87', '89', '91', '93', '95', '97', \n",
    "                '99', '101', '103', '105', '107', '109', '111', '113', '115',\n",
    "                '117', '119', '121', '123', '125']\n",
    "                "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f0da0317",
   "metadata": {},
   "source": [
    "API documentation says Small Area Income and Poverty Estimates only goes to 2016, but requests below work until 2021"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "5fb78998",
   "metadata": {},
   "source": [
    "Complete list of variables available at: https://api.census.gov/data/timeseries/poverty/saipe/variables.html\n",
    "\n",
    "Variables obtained: <br>\n",
    "Median Household Income Estimate: SAEMHI_PT <br>\n",
    "All ages in Poverty, Count Estimate: SAEPOVALL_PT <br>\n",
    "All ages in Poverty, Rate Estimate: SAEPOVRTALL_PT <br>\n",
    "Ages 5-17 in Families in Poverty, Count Estimate: SAEPOV5_17R_PT <br>\n",
    "Ages 5-17 in Families in Poverty, Rate Estimate: SAEPOVRT5_17R_PT"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "b994222a",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2023-06-08T16:47:17.557872Z",
     "start_time": "2023-06-08T16:47:16.468087Z"
    }
   },
   "outputs": [],
   "source": [
    "# List of variables available at: https://api.census.gov/data/timeseries/poverty/saipe/variables.html\n",
    "\n",
    "url = 'https://api.census.gov/data/timeseries/poverty/saipe'\n",
    "params = {\n",
    "    'get': 'NAME,SAEMHI_PT,SAEPOVALL_PT,SAEPOVRTALL_PT,SAEPOV5_17R_PT,SAEPOVRT5_17R_PT',\n",
    "    'for': 'county:*',\n",
    "    'in': 'state:08',\n",
    "    'time': 'from 2000 to 2021'\n",
    "}\n",
    "\n",
    "response = requests.get(url, params=params).json()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 64,
   "id": "4d25ef14",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2023-06-08T17:53:39.014053Z",
     "start_time": "2023-06-08T17:53:39.008053Z"
    },
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "# Missing one year for Broomfield County\n",
    "saipe_df = pd.DataFrame(response[1:], columns = response[0])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 65,
   "id": "df14210a",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2023-06-08T17:53:39.405936Z",
     "start_time": "2023-06-08T17:53:39.390641Z"
    },
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "saipe_df = saipe_df.drop(['state', 'county'], axis = 1) \\\n",
    "                   .rename(columns = {'NAME': 'county', 'time': 'year', 'SAEMHI_PT': 'median household income',\n",
    "                                      'SAEPOVALL_PT': 'all poverty count', 'SAEPOVRTALL_PT': 'all poverty rate',\n",
    "                                      'SAEPOV5_17R_PT': 'age5-17 poverty count', 'SAEPOVRT5_17R_PT': 'age5-17 poverty rate'}) \\\n",
    "\n",
    "saipe_df['county'] = saipe_df['county'].str.replace(' County', '', regex=False).str.upper()\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 70,
   "id": "66a7f6a8",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2023-06-08T17:57:39.750748Z",
     "start_time": "2023-06-08T17:57:39.726738Z"
    }
   },
   "outputs": [],
   "source": [
    "cols = ['median household income', 'all poverty count', 'all poverty rate', \n",
    "       'age5-17 poverty count', 'age5-17 poverty rate', 'year']\n",
    "\n",
    "saipe_df[cols] = saipe_df[cols].apply(pd.to_numeric, errors='coerce')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 71,
   "id": "afc2a6cb",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2023-06-08T17:57:44.045917Z",
     "start_time": "2023-06-08T17:57:44.023638Z"
    }
   },
   "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>county</th>\n",
       "      <th>median household income</th>\n",
       "      <th>all poverty count</th>\n",
       "      <th>all poverty rate</th>\n",
       "      <th>age5-17 poverty count</th>\n",
       "      <th>age5-17 poverty rate</th>\n",
       "      <th>year</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>ADAMS</td>\n",
       "      <td>48307</td>\n",
       "      <td>33377</td>\n",
       "      <td>8.9</td>\n",
       "      <td>8806</td>\n",
       "      <td>12.0</td>\n",
       "      <td>2000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>ALAMOSA</td>\n",
       "      <td>30538</td>\n",
       "      <td>2851</td>\n",
       "      <td>20.3</td>\n",
       "      <td>706</td>\n",
       "      <td>24.1</td>\n",
       "      <td>2000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>ARAPAHOE</td>\n",
       "      <td>55359</td>\n",
       "      <td>29023</td>\n",
       "      <td>5.8</td>\n",
       "      <td>6514</td>\n",
       "      <td>6.8</td>\n",
       "      <td>2000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>ARCHULETA</td>\n",
       "      <td>38241</td>\n",
       "      <td>1379</td>\n",
       "      <td>13.2</td>\n",
       "      <td>377</td>\n",
       "      <td>18.9</td>\n",
       "      <td>2000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>BACA</td>\n",
       "      <td>29274</td>\n",
       "      <td>773</td>\n",
       "      <td>17.5</td>\n",
       "      <td>188</td>\n",
       "      <td>23.5</td>\n",
       "      <td>2000</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",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1402</th>\n",
       "      <td>SUMMIT</td>\n",
       "      <td>90297</td>\n",
       "      <td>2053</td>\n",
       "      <td>6.7</td>\n",
       "      <td>225</td>\n",
       "      <td>6.4</td>\n",
       "      <td>2021</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1403</th>\n",
       "      <td>TELLER</td>\n",
       "      <td>76715</td>\n",
       "      <td>1968</td>\n",
       "      <td>7.9</td>\n",
       "      <td>388</td>\n",
       "      <td>12.2</td>\n",
       "      <td>2021</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1404</th>\n",
       "      <td>WASHINGTON</td>\n",
       "      <td>57522</td>\n",
       "      <td>544</td>\n",
       "      <td>11.6</td>\n",
       "      <td>128</td>\n",
       "      <td>15.2</td>\n",
       "      <td>2021</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1405</th>\n",
       "      <td>WELD</td>\n",
       "      <td>84826</td>\n",
       "      <td>32543</td>\n",
       "      <td>9.8</td>\n",
       "      <td>6942</td>\n",
       "      <td>11.0</td>\n",
       "      <td>2021</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1406</th>\n",
       "      <td>YUMA</td>\n",
       "      <td>56161</td>\n",
       "      <td>1315</td>\n",
       "      <td>13.5</td>\n",
       "      <td>340</td>\n",
       "      <td>16.7</td>\n",
       "      <td>2021</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>1407 rows × 7 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "          county  median household income  all poverty count  \\\n",
       "0          ADAMS                    48307              33377   \n",
       "1        ALAMOSA                    30538               2851   \n",
       "2       ARAPAHOE                    55359              29023   \n",
       "3      ARCHULETA                    38241               1379   \n",
       "4           BACA                    29274                773   \n",
       "...          ...                      ...                ...   \n",
       "1402      SUMMIT                    90297               2053   \n",
       "1403      TELLER                    76715               1968   \n",
       "1404  WASHINGTON                    57522                544   \n",
       "1405        WELD                    84826              32543   \n",
       "1406        YUMA                    56161               1315   \n",
       "\n",
       "      all poverty rate  age5-17 poverty count  age5-17 poverty rate  year  \n",
       "0                  8.9                   8806                  12.0  2000  \n",
       "1                 20.3                    706                  24.1  2000  \n",
       "2                  5.8                   6514                   6.8  2000  \n",
       "3                 13.2                    377                  18.9  2000  \n",
       "4                 17.5                    188                  23.5  2000  \n",
       "...                ...                    ...                   ...   ...  \n",
       "1402               6.7                    225                   6.4  2021  \n",
       "1403               7.9                    388                  12.2  2021  \n",
       "1404              11.6                    128                  15.2  2021  \n",
       "1405               9.8                   6942                  11.0  2021  \n",
       "1406              13.5                    340                  16.7  2021  \n",
       "\n",
       "[1407 rows x 7 columns]"
      ]
     },
     "execution_count": 71,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "saipe_df"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f75b13e9",
   "metadata": {},
   "source": [
    "## Population Estimates"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "7556f28d",
   "metadata": {},
   "outputs": [],
   "source": [
    "# US Census API for population estimates changed year to year and was hard to \n",
    "# navigate.  Found Excel spreadsheets at these links:\n",
    "# https://www.census.gov/data/tables/time-series/demo/popest/intercensal-2000-2010-counties.html\n",
    "# https://www.census.gov/data/datasets/time-series/demo/popest/2010s-counties-total.html\n",
    "# https://www.census.gov/data/datasets/time-series/demo/popest/2020s-counties-total.html"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 58,
   "id": "d15eab12",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2023-06-08T17:52:46.977243Z",
     "start_time": "2023-06-08T17:52:46.963244Z"
    }
   },
   "outputs": [],
   "source": [
    "pop2000s = pd.read_csv('population data/co-est00int-01-08-edit.csv', header=1)\n",
    "pop2000s = pop2000s.rename(columns = {'Unnamed: 0': 'region'})\n",
    "pop2000s['region'] = pop2000s['region'].str.replace('.', '', regex=False).str.replace(' County', '').str.upper()\n",
    "\n",
    "pop2010s = pd.read_csv('population data/co-est2019-annres-08-edit.csv', header=1)\n",
    "pop2010s = pop2010s.rename(columns = {'Unnamed: 0': 'region'})\n",
    "pop2010s['region'] = pop2010s['region'].str.replace('.', '', regex=False).str.replace(' County, Colorado', '').str.upper()\n",
    "\n",
    "pop2020s = pd.read_csv('population data/co-est2022-pop-08-edit.csv', header=1)\n",
    "pop2020s = pop2020s.rename(columns = {'Unnamed: 0': 'region'})\n",
    "pop2020s['region'] = pop2020s['region'].str.replace('.', '', regex=False).str.replace(' County, Colorado', '').str.upper()\n",
    "\n",
    "countypop_df = pop2000s.merge(pop2010s, on = 'region')\n",
    "countypop_df = countypop_df.merge(pop2020s, on = 'region')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 59,
   "id": "9f8dc369",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2023-06-08T17:52:47.228059Z",
     "start_time": "2023-06-08T17:52:47.209038Z"
    }
   },
   "outputs": [],
   "source": [
    "# Create an empty list to store the reformatted rows\n",
    "new_rows = []\n",
    "\n",
    "# Iterate over each row in the original dataframe\n",
    "for index, row in countypop_df.iterrows():\n",
    "    region = row['region']\n",
    "    # Iterate over each column except the 'region' column\n",
    "    for year, population in list(row.items())[1:]:\n",
    "        new_rows.append([region, year, population])\n",
    "\n",
    "# Create a new dataframe using the reformatted rows\n",
    "countypop_df = pd.DataFrame(new_rows, columns=['county', 'year', 'population'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 60,
   "id": "b5c774df",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2023-06-08T17:52:47.605522Z",
     "start_time": "2023-06-08T17:52:47.592510Z"
    }
   },
   "outputs": [],
   "source": [
    "countypop_df['population'] = countypop_df['population'].str.replace(',', '')\n",
    "countypop_df['population'] = pd.to_numeric(countypop_df['population'], errors = 'coerce')\n",
    "\n",
    "countypop_df['year'] = pd.to_numeric(countypop_df['year'], errors = 'coerce')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ad9f0632",
   "metadata": {},
   "source": [
    "## Unemployment Statistics"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "id": "ff731ba6",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2023-06-23T22:04:20.729300Z",
     "start_time": "2023-06-23T22:04:17.882394Z"
    }
   },
   "outputs": [],
   "source": [
    "unemployment = pd.read_excel('population data/unemployment.xlsx', header=4)\n",
    "unemployment = unemployment[unemployment['State'] == 'CO']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "id": "473fdec1",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2023-06-23T22:04:24.428904Z",
     "start_time": "2023-06-23T22:04:24.408869Z"
    }
   },
   "outputs": [],
   "source": [
    "columns_to_remove = unemployment.filter(regex=r'^Employed_\\d{4}$').columns\n",
    "unemployment = unemployment.drop(columns=columns_to_remove)\n",
    "\n",
    "unemployment = unemployment.drop(columns = ['FIPS_code', 'State', 'Urban_influence_code_2013', \n",
    "                                           'Rural_urban_continuum_code_2013', 'Metro_2013',\n",
    "                                           'Median_Household_Income_2020', 'Med_HH_Income_Percent_of_State_Total_2020'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 93,
   "id": "17ea3236",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2023-06-23T22:55:10.779618Z",
     "start_time": "2023-06-23T22:55:10.755930Z"
    }
   },
   "outputs": [],
   "source": [
    "new_columns = ['county', 'year', 'civilian_labor_force', 'unemployed', 'unemployment_rate']\n",
    "\n",
    "# Create an empty dictionary to store the data\n",
    "data_dict = {col: [] for col in new_columns}\n",
    "\n",
    "for _, row in unemployment.iterrows():\n",
    "    area_name = row['Area_name']\n",
    "    \n",
    "    # Iterate over the columns and extract the year and values\n",
    "    for col_name, value in row.items():\n",
    "        if col_name.startswith('Civilian_labor_force_'):\n",
    "            year = col_name.split('_')[3]\n",
    "            civilian_labor = value\n",
    "            unemployed = row['Unemployed_' + year]\n",
    "            unemployment_rate = row['Unemployment_rate_' + year]\n",
    "            \n",
    "            # Add the extracted values to the dictionary\n",
    "            data_dict['county'].append(area_name)\n",
    "            data_dict['year'].append(year)\n",
    "            data_dict['civilian_labor_force'].append(civilian_labor)\n",
    "            data_dict['unemployed'].append(unemployed)\n",
    "            data_dict['unemployment_rate'].append(unemployment_rate)\n",
    "\n",
    "# Create the DataFrame from the dictionary\n",
    "unemployment_df = pd.DataFrame(data_dict)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 94,
   "id": "f9780f2c",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2023-06-23T22:55:11.611591Z",
     "start_time": "2023-06-23T22:55:11.589537Z"
    }
   },
   "outputs": [],
   "source": [
    "unemployment_df['county'] = unemployment_df['county'].str.replace('Denver County/city, CO', 'DENVER')\n",
    "unemployment_df['county'] = unemployment_df['county'].str.replace(' County, CO', '').str.upper()\n",
    "unemployment_df['year'] = pd.to_numeric(unemployment_df['year'], errors = 'coerce')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "32a77b6a",
   "metadata": {},
   "source": [
    "## CPI Info"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 120,
   "id": "85797d08",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2023-06-27T03:21:20.224574Z",
     "start_time": "2023-06-27T03:21:20.186460Z"
    }
   },
   "outputs": [],
   "source": [
    "# from https://www.ssa.gov/oact/STATS/avgcpi.html\n",
    "CPI = pd.read_csv('population data/CPIdata.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 124,
   "id": "7c6f6bb7",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2023-06-27T03:23:29.516279Z",
     "start_time": "2023-06-27T03:23:29.500748Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{2000: 168.892,\n",
       " 2001: 173.517,\n",
       " 2002: 175.875,\n",
       " 2003: 179.808,\n",
       " 2004: 184.483,\n",
       " 2005: 190.975,\n",
       " 2006: 197.142,\n",
       " 2007: 202.767,\n",
       " 2008: 211.053,\n",
       " 2009: 209.63,\n",
       " 2010: 213.967,\n",
       " 2011: 221.575,\n",
       " 2012: 226.229,\n",
       " 2013: 229.324,\n",
       " 2014: 232.771,\n",
       " 2015: 231.81,\n",
       " 2016: 234.076,\n",
       " 2017: 239.051,\n",
       " 2018: 245.146,\n",
       " 2019: 249.222,\n",
       " 2020: 252.248,\n",
       " 2021: 265.51}"
      ]
     },
     "execution_count": 124,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "CPIdict = dict(zip(CPI['Year'], CPI['Annual']))\n",
    "CPIdict"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "bcd49182",
   "metadata": {},
   "source": [
    "## Merge Dataframes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 125,
   "id": "a43eaa3d",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2023-06-08T18:24:38.353017Z",
     "start_time": "2023-06-08T18:24:38.331692Z"
    }
   },
   "outputs": [],
   "source": [
    "econ_df = countypop_df.merge(saipe_df, on = ['year', 'county'], how = 'left')\n",
    "econ_df = econ_df.merge(weedyear_df, on = ['year', 'county'], how = 'left')\n",
    "econ_df['Medical Marijuana Sales'].fillna(0, inplace=True)\n",
    "econ_df['Retail Marijuana Sales'].fillna(0, inplace=True)\n",
    "\n",
    "econ_df = econ_df.merge(unemployment_df, on = ['year', 'county'], how = 'left')\n",
    "econ_df['labor participation rate'] = econ_df['civilian_labor_force'] / econ_df['population']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 125,
   "id": "981ad218",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2023-06-27T03:24:21.750419Z",
     "start_time": "2023-06-27T03:24:21.726061Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['county', 'year', 'population', 'median household income',\n",
       "       'all poverty count', 'all poverty rate', 'age5-17 poverty count',\n",
       "       'age5-17 poverty rate', 'Medical Marijuana Sales',\n",
       "       'Retail Marijuana Sales', 'civilian_labor_force', 'unemployed',\n",
       "       'unemployment_rate', 'labor participation rate'],\n",
       "      dtype='object')"
      ]
     },
     "execution_count": 125,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "econ_df.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 132,
   "id": "ff5e75c6",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2023-06-27T03:32:33.321671Z",
     "start_time": "2023-06-27T03:32:33.309309Z"
    },
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "def adjust_income(row):\n",
    "    try:\n",
    "        cpi2021 = CPIdict.get(2021)\n",
    "        year = row['year']\n",
    "        cpi = CPIdict.get(year)  # Get the CPI value for the corresponding year\n",
    "        income = row['median household income']\n",
    "        adjusted_income = income / cpi * cpi2021\n",
    "        return adjusted_income\n",
    "    except:\n",
    "        return None"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 141,
   "id": "9afe039e",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2023-06-27T03:37:17.889480Z",
     "start_time": "2023-06-27T03:37:17.859071Z"
    }
   },
   "outputs": [],
   "source": [
    "econ_df['median_income_2021CPI'] = econ_df.apply(lambda row: adjust_income(row), axis=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 146,
   "id": "e60116ac",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2023-06-27T03:38:05.440907Z",
     "start_time": "2023-06-27T03:38:05.426645Z"
    }
   },
   "outputs": [],
   "source": [
    "econ_df.to_pickle('econ_df.pkl')"
   ]
  }
 ],
 "metadata": {
  "hide_input": false,
  "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.9.16"
  },
  "nbTranslate": {
   "displayLangs": [
    "*"
   ],
   "hotkey": "alt-t",
   "langInMainMenu": true,
   "sourceLang": "en",
   "targetLang": "fr",
   "useGoogleTranslate": true
  },
  "toc": {
   "base_numbering": 1,
   "nav_menu": {
    "height": "217px",
    "width": "162px"
   },
   "number_sections": true,
   "sideBar": true,
   "skip_h1_title": false,
   "title_cell": "Table of Contents",
   "title_sidebar": "Contents",
   "toc_cell": false,
   "toc_position": {},
   "toc_section_display": true,
   "toc_window_display": false
  },
  "varInspector": {
   "cols": {
    "lenName": 16,
    "lenType": 16,
    "lenVar": 40
   },
   "kernels_config": {
    "python": {
     "delete_cmd_postfix": "",
     "delete_cmd_prefix": "del ",
     "library": "var_list.py",
     "varRefreshCmd": "print(var_dic_list())"
    },
    "r": {
     "delete_cmd_postfix": ") ",
     "delete_cmd_prefix": "rm(",
     "library": "var_list.r",
     "varRefreshCmd": "cat(var_dic_list()) "
    }
   },
   "types_to_exclude": [
    "module",
    "function",
    "builtin_function_or_method",
    "instance",
    "_Feature"
   ],
   "window_display": false
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}