merge_hic_dataset_2014_2023.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "####  Objective\n",
    "This file aims to present the shelter and permanent housing capacity data, specifically focusing on the \"Total Year Round\" beds for various housing types: Emergency Shelter (ES), Transitional Housing (TH), Safe Haven (SH), Rapid Re-Housing (RRH), and Permanent Supportive Housing (PSH). The data includes separate indicators for each bed type, distinguishing between Households with Children and Households without Children, at the Continuum of Care (CoC) level. The analysis combines datasets from the 2007-2023 Housing Inventory Count (HIC) provided by the U.S. Department of Housing and Urban Development (HUD) to create a new, comprehensive dataset featuring the required indicators. The source data can be accessed from the \"2007-2023-HIC Counts by CoC\" Excel file available online.\n",
    "https://www.hudexchange.info/resource/3031/pit-and-hic-data-since-2007/"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "c:\\ProgramData\\anaconda3\\Lib\\site-packages\\openpyxl\\worksheet\\header_footer.py:48: UserWarning: Cannot parse header or footer so it will be ignored\n",
      "  warn(\"\"\"Cannot parse header or footer so it will be ignored\"\"\")\n",
      "c:\\ProgramData\\anaconda3\\Lib\\site-packages\\openpyxl\\worksheet\\header_footer.py:48: UserWarning: Cannot parse header or footer so it will be ignored\n",
      "  warn(\"\"\"Cannot parse header or footer so it will be ignored\"\"\")\n",
      "c:\\ProgramData\\anaconda3\\Lib\\site-packages\\openpyxl\\worksheet\\header_footer.py:48: UserWarning: Cannot parse header or footer so it will be ignored\n",
      "  warn(\"\"\"Cannot parse header or footer so it will be ignored\"\"\")\n",
      "c:\\ProgramData\\anaconda3\\Lib\\site-packages\\openpyxl\\worksheet\\header_footer.py:48: UserWarning: Cannot parse header or footer so it will be ignored\n",
      "  warn(\"\"\"Cannot parse header or footer so it will be ignored\"\"\")\n",
      "c:\\ProgramData\\anaconda3\\Lib\\site-packages\\openpyxl\\worksheet\\header_footer.py:48: UserWarning: Cannot parse header or footer so it will be ignored\n",
      "  warn(\"\"\"Cannot parse header or footer so it will be ignored\"\"\")\n",
      "c:\\ProgramData\\anaconda3\\Lib\\site-packages\\openpyxl\\worksheet\\header_footer.py:48: UserWarning: Cannot parse header or footer so it will be ignored\n",
      "  warn(\"\"\"Cannot parse header or footer so it will be ignored\"\"\")\n",
      "c:\\ProgramData\\anaconda3\\Lib\\site-packages\\openpyxl\\worksheet\\header_footer.py:48: UserWarning: Cannot parse header or footer so it will be ignored\n",
      "  warn(\"\"\"Cannot parse header or footer so it will be ignored\"\"\")\n"
     ]
    }
   ],
   "source": [
    "import pandas as pd\n",
    "dfs = pd.read_excel('2007-2023-HIC-Counts-by-CoC.xlsx', skiprows=1, sheet_name=None)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "columns_to_extract = [\n",
    "    'CoC Number',\n",
    "    'Total Year-Round Beds (ES, TH, SH)',\n",
    "    'Total Beds for Households with Children (ES, TH, SH)',\n",
    "    'Total Beds for Households without Children (ES, TH, SH)',\n",
    "    'Total Year-Round Beds (ES)',\n",
    "    'Total Beds for Households with Children (ES)',\n",
    "    'Total Beds for Households without Children (ES)',\n",
    "    'Total Year-Round Beds (TH)',\n",
    "    'Total Beds for Households with Children (TH)',\n",
    "    'Total Beds for Households without Children (TH)',\n",
    "    'Total Year-Round Beds (SH)',\n",
    "    'Total Beds for Households with Children (SH)',\n",
    "    'Total Beds for Households without Children (SH)',\n",
    "    'Total Year-Round Beds (RRH)',\n",
    "    'Total Beds for Households with Children (RRH)',\n",
    "    'Total Beds for Households without Children (RRH)',\n",
    "    'Total Year-Round Beds (PSH)',\n",
    "    'Total Beds for Households with Children (PSH)',\n",
    "    'Total Beds for Households without Children (PSH)'\n",
    "]\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_2023 = dfs['2023']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_subset = df_2023.loc[:, columns_to_extract]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>CoC Number</th>\n",
       "      <th>Total Year-Round Beds (ES, TH, SH)</th>\n",
       "      <th>Total Beds for Households with Children (ES, TH, SH)</th>\n",
       "      <th>Total Beds for Households without Children (ES, TH, SH)</th>\n",
       "      <th>Total Year-Round Beds (ES)</th>\n",
       "      <th>Total Beds for Households with Children (ES)</th>\n",
       "      <th>Total Beds for Households without Children (ES)</th>\n",
       "      <th>Total Year-Round Beds (TH)</th>\n",
       "      <th>Total Beds for Households with Children (TH)</th>\n",
       "      <th>Total Beds for Households without Children (TH)</th>\n",
       "      <th>Total Year-Round Beds (SH)</th>\n",
       "      <th>Total Beds for Households with Children (SH)</th>\n",
       "      <th>Total Beds for Households without Children (SH)</th>\n",
       "      <th>Total Year-Round Beds (RRH)</th>\n",
       "      <th>Total Beds for Households with Children (RRH)</th>\n",
       "      <th>Total Beds for Households without Children (RRH)</th>\n",
       "      <th>Total Year-Round Beds (PSH)</th>\n",
       "      <th>Total Beds for Households with Children (PSH)</th>\n",
       "      <th>Total Beds for Households without Children (PSH)</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>AK-500</td>\n",
       "      <td>1188</td>\n",
       "      <td>346</td>\n",
       "      <td>833</td>\n",
       "      <td>950</td>\n",
       "      <td>194</td>\n",
       "      <td>748</td>\n",
       "      <td>238</td>\n",
       "      <td>152</td>\n",
       "      <td>85</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>256</td>\n",
       "      <td>168</td>\n",
       "      <td>88</td>\n",
       "      <td>720</td>\n",
       "      <td>172</td>\n",
       "      <td>548</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>AK-501</td>\n",
       "      <td>1120</td>\n",
       "      <td>372</td>\n",
       "      <td>718</td>\n",
       "      <td>847</td>\n",
       "      <td>267</td>\n",
       "      <td>551</td>\n",
       "      <td>273</td>\n",
       "      <td>105</td>\n",
       "      <td>167</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>105</td>\n",
       "      <td>52</td>\n",
       "      <td>53</td>\n",
       "      <td>385</td>\n",
       "      <td>50</td>\n",
       "      <td>335</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>AL-500</td>\n",
       "      <td>544</td>\n",
       "      <td>89</td>\n",
       "      <td>448</td>\n",
       "      <td>424</td>\n",
       "      <td>67</td>\n",
       "      <td>350</td>\n",
       "      <td>110</td>\n",
       "      <td>22</td>\n",
       "      <td>88</td>\n",
       "      <td>10</td>\n",
       "      <td>0</td>\n",
       "      <td>10</td>\n",
       "      <td>179</td>\n",
       "      <td>56</td>\n",
       "      <td>123</td>\n",
       "      <td>1399</td>\n",
       "      <td>239</td>\n",
       "      <td>1160</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>AL-501</td>\n",
       "      <td>394</td>\n",
       "      <td>218</td>\n",
       "      <td>176</td>\n",
       "      <td>252</td>\n",
       "      <td>137</td>\n",
       "      <td>115</td>\n",
       "      <td>142</td>\n",
       "      <td>81</td>\n",
       "      <td>61</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>173</td>\n",
       "      <td>132</td>\n",
       "      <td>41</td>\n",
       "      <td>241</td>\n",
       "      <td>26</td>\n",
       "      <td>215</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>AL-502</td>\n",
       "      <td>302</td>\n",
       "      <td>121</td>\n",
       "      <td>165</td>\n",
       "      <td>86</td>\n",
       "      <td>51</td>\n",
       "      <td>35</td>\n",
       "      <td>216</td>\n",
       "      <td>70</td>\n",
       "      <td>130</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  CoC Number  Total Year-Round Beds (ES, TH, SH)  \\\n",
       "0     AK-500                                1188   \n",
       "1     AK-501                                1120   \n",
       "2     AL-500                                 544   \n",
       "3     AL-501                                 394   \n",
       "4     AL-502                                 302   \n",
       "\n",
       "   Total Beds for Households with Children (ES, TH, SH)  \\\n",
       "0                                                346      \n",
       "1                                                372      \n",
       "2                                                 89      \n",
       "3                                                218      \n",
       "4                                                121      \n",
       "\n",
       "   Total Beds for Households without Children (ES, TH, SH)  \\\n",
       "0                                                833         \n",
       "1                                                718         \n",
       "2                                                448         \n",
       "3                                                176         \n",
       "4                                                165         \n",
       "\n",
       "   Total Year-Round Beds (ES)  Total Beds for Households with Children (ES)  \\\n",
       "0                         950                                           194   \n",
       "1                         847                                           267   \n",
       "2                         424                                            67   \n",
       "3                         252                                           137   \n",
       "4                          86                                            51   \n",
       "\n",
       "   Total Beds for Households without Children (ES)  \\\n",
       "0                                              748   \n",
       "1                                              551   \n",
       "2                                              350   \n",
       "3                                              115   \n",
       "4                                               35   \n",
       "\n",
       "   Total Year-Round Beds (TH)  Total Beds for Households with Children (TH)  \\\n",
       "0                         238                                           152   \n",
       "1                         273                                           105   \n",
       "2                         110                                            22   \n",
       "3                         142                                            81   \n",
       "4                         216                                            70   \n",
       "\n",
       "   Total Beds for Households without Children (TH)  \\\n",
       "0                                               85   \n",
       "1                                              167   \n",
       "2                                               88   \n",
       "3                                               61   \n",
       "4                                              130   \n",
       "\n",
       "   Total Year-Round Beds (SH)  Total Beds for Households with Children (SH)  \\\n",
       "0                           0                                             0   \n",
       "1                           0                                             0   \n",
       "2                          10                                             0   \n",
       "3                           0                                             0   \n",
       "4                           0                                             0   \n",
       "\n",
       "   Total Beds for Households without Children (SH)  \\\n",
       "0                                                0   \n",
       "1                                                0   \n",
       "2                                               10   \n",
       "3                                                0   \n",
       "4                                                0   \n",
       "\n",
       "   Total Year-Round Beds (RRH)  Total Beds for Households with Children (RRH)  \\\n",
       "0                          256                                            168   \n",
       "1                          105                                             52   \n",
       "2                          179                                             56   \n",
       "3                          173                                            132   \n",
       "4                            0                                              0   \n",
       "\n",
       "   Total Beds for Households without Children (RRH)  \\\n",
       "0                                                88   \n",
       "1                                                53   \n",
       "2                                               123   \n",
       "3                                                41   \n",
       "4                                                 0   \n",
       "\n",
       "   Total Year-Round Beds (PSH)  Total Beds for Households with Children (PSH)  \\\n",
       "0                          720                                            172   \n",
       "1                          385                                             50   \n",
       "2                         1399                                            239   \n",
       "3                          241                                             26   \n",
       "4                            0                                              0   \n",
       "\n",
       "   Total Beds for Households without Children (PSH)  \n",
       "0                                               548  \n",
       "1                                               335  \n",
       "2                                              1160  \n",
       "3                                               215  \n",
       "4                                                 0  "
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_subset.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {},
   "outputs": [],
   "source": [
    "sheet_data = []\n",
    "for sheet, df in dfs.items():\n",
    "    selected_columns = df.columns.intersection(columns_to_extract)\n",
    "    if not selected_columns.empty:\n",
    "        df_subset = df.loc[:, selected_columns]\n",
    "        df_subset['Year'] = sheet\n",
    "        sheet_data.append(df_subset)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {},
   "outputs": [],
   "source": [
    "merged_data = pd.concat(sheet_data, ignore_index=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>CoC Number</th>\n",
       "      <th>Total Year-Round Beds (ES, TH, SH)</th>\n",
       "      <th>Total Year-Round Beds (ES)</th>\n",
       "      <th>Total Year-Round Beds (TH)</th>\n",
       "      <th>Total Year-Round Beds (SH)</th>\n",
       "      <th>Total Beds for Households with Children (ES, TH, SH)</th>\n",
       "      <th>Total Beds for Households without Children (ES, TH, SH)</th>\n",
       "      <th>Total Beds for Households with Children (ES)</th>\n",
       "      <th>Total Beds for Households without Children (ES)</th>\n",
       "      <th>Total Beds for Households with Children (TH)</th>\n",
       "      <th>Total Beds for Households without Children (TH)</th>\n",
       "      <th>Total Beds for Households with Children (SH)</th>\n",
       "      <th>Total Beds for Households without Children (SH)</th>\n",
       "      <th>Total Year-Round Beds (RRH)</th>\n",
       "      <th>Total Beds for Households with Children (RRH)</th>\n",
       "      <th>Total Beds for Households without Children (RRH)</th>\n",
       "      <th>Total Year-Round Beds (PSH)</th>\n",
       "      <th>Total Beds for Households with Children (PSH)</th>\n",
       "      <th>Total Beds for Households without Children (PSH)</th>\n",
       "      <th>Year</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>AK-500</td>\n",
       "      <td>1188.0</td>\n",
       "      <td>950.0</td>\n",
       "      <td>238.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>346.0</td>\n",
       "      <td>833.0</td>\n",
       "      <td>194.0</td>\n",
       "      <td>748.0</td>\n",
       "      <td>152.0</td>\n",
       "      <td>85.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>256.0</td>\n",
       "      <td>168.0</td>\n",
       "      <td>88.0</td>\n",
       "      <td>720.0</td>\n",
       "      <td>172.0</td>\n",
       "      <td>548.0</td>\n",
       "      <td>2023</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>AK-501</td>\n",
       "      <td>1120.0</td>\n",
       "      <td>847.0</td>\n",
       "      <td>273.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>372.0</td>\n",
       "      <td>718.0</td>\n",
       "      <td>267.0</td>\n",
       "      <td>551.0</td>\n",
       "      <td>105.0</td>\n",
       "      <td>167.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>105.0</td>\n",
       "      <td>52.0</td>\n",
       "      <td>53.0</td>\n",
       "      <td>385.0</td>\n",
       "      <td>50.0</td>\n",
       "      <td>335.0</td>\n",
       "      <td>2023</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>AL-500</td>\n",
       "      <td>544.0</td>\n",
       "      <td>424.0</td>\n",
       "      <td>110.0</td>\n",
       "      <td>10.0</td>\n",
       "      <td>89.0</td>\n",
       "      <td>448.0</td>\n",
       "      <td>67.0</td>\n",
       "      <td>350.0</td>\n",
       "      <td>22.0</td>\n",
       "      <td>88.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>10.0</td>\n",
       "      <td>179.0</td>\n",
       "      <td>56.0</td>\n",
       "      <td>123.0</td>\n",
       "      <td>1399.0</td>\n",
       "      <td>239.0</td>\n",
       "      <td>1160.0</td>\n",
       "      <td>2023</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>AL-501</td>\n",
       "      <td>394.0</td>\n",
       "      <td>252.0</td>\n",
       "      <td>142.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>218.0</td>\n",
       "      <td>176.0</td>\n",
       "      <td>137.0</td>\n",
       "      <td>115.0</td>\n",
       "      <td>81.0</td>\n",
       "      <td>61.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>173.0</td>\n",
       "      <td>132.0</td>\n",
       "      <td>41.0</td>\n",
       "      <td>241.0</td>\n",
       "      <td>26.0</td>\n",
       "      <td>215.0</td>\n",
       "      <td>2023</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>AL-502</td>\n",
       "      <td>302.0</td>\n",
       "      <td>86.0</td>\n",
       "      <td>216.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>121.0</td>\n",
       "      <td>165.0</td>\n",
       "      <td>51.0</td>\n",
       "      <td>35.0</td>\n",
       "      <td>70.0</td>\n",
       "      <td>130.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2023</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  CoC Number  Total Year-Round Beds (ES, TH, SH)  Total Year-Round Beds (ES)  \\\n",
       "0     AK-500                              1188.0                       950.0   \n",
       "1     AK-501                              1120.0                       847.0   \n",
       "2     AL-500                               544.0                       424.0   \n",
       "3     AL-501                               394.0                       252.0   \n",
       "4     AL-502                               302.0                        86.0   \n",
       "\n",
       "   Total Year-Round Beds (TH)  Total Year-Round Beds (SH)  \\\n",
       "0                       238.0                         0.0   \n",
       "1                       273.0                         0.0   \n",
       "2                       110.0                        10.0   \n",
       "3                       142.0                         0.0   \n",
       "4                       216.0                         0.0   \n",
       "\n",
       "   Total Beds for Households with Children (ES, TH, SH)  \\\n",
       "0                                              346.0      \n",
       "1                                              372.0      \n",
       "2                                               89.0      \n",
       "3                                              218.0      \n",
       "4                                              121.0      \n",
       "\n",
       "   Total Beds for Households without Children (ES, TH, SH)  \\\n",
       "0                                              833.0         \n",
       "1                                              718.0         \n",
       "2                                              448.0         \n",
       "3                                              176.0         \n",
       "4                                              165.0         \n",
       "\n",
       "   Total Beds for Households with Children (ES)  \\\n",
       "0                                         194.0   \n",
       "1                                         267.0   \n",
       "2                                          67.0   \n",
       "3                                         137.0   \n",
       "4                                          51.0   \n",
       "\n",
       "   Total Beds for Households without Children (ES)  \\\n",
       "0                                            748.0   \n",
       "1                                            551.0   \n",
       "2                                            350.0   \n",
       "3                                            115.0   \n",
       "4                                             35.0   \n",
       "\n",
       "   Total Beds for Households with Children (TH)  \\\n",
       "0                                         152.0   \n",
       "1                                         105.0   \n",
       "2                                          22.0   \n",
       "3                                          81.0   \n",
       "4                                          70.0   \n",
       "\n",
       "   Total Beds for Households without Children (TH)  \\\n",
       "0                                             85.0   \n",
       "1                                            167.0   \n",
       "2                                             88.0   \n",
       "3                                             61.0   \n",
       "4                                            130.0   \n",
       "\n",
       "   Total Beds for Households with Children (SH)  \\\n",
       "0                                           0.0   \n",
       "1                                           0.0   \n",
       "2                                           0.0   \n",
       "3                                           0.0   \n",
       "4                                           0.0   \n",
       "\n",
       "   Total Beds for Households without Children (SH)  \\\n",
       "0                                              0.0   \n",
       "1                                              0.0   \n",
       "2                                             10.0   \n",
       "3                                              0.0   \n",
       "4                                              0.0   \n",
       "\n",
       "   Total Year-Round Beds (RRH)  Total Beds for Households with Children (RRH)  \\\n",
       "0                        256.0                                          168.0   \n",
       "1                        105.0                                           52.0   \n",
       "2                        179.0                                           56.0   \n",
       "3                        173.0                                          132.0   \n",
       "4                          0.0                                            0.0   \n",
       "\n",
       "   Total Beds for Households without Children (RRH)  \\\n",
       "0                                              88.0   \n",
       "1                                              53.0   \n",
       "2                                             123.0   \n",
       "3                                              41.0   \n",
       "4                                               0.0   \n",
       "\n",
       "   Total Year-Round Beds (PSH)  Total Beds for Households with Children (PSH)  \\\n",
       "0                        720.0                                          172.0   \n",
       "1                        385.0                                           50.0   \n",
       "2                       1399.0                                          239.0   \n",
       "3                        241.0                                           26.0   \n",
       "4                          0.0                                            0.0   \n",
       "\n",
       "   Total Beds for Households without Children (PSH)  Year  \n",
       "0                                             548.0  2023  \n",
       "1                                             335.0  2023  \n",
       "2                                            1160.0  2023  \n",
       "3                                             215.0  2023  \n",
       "4                                               0.0  2023  "
      ]
     },
     "execution_count": 33,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "merged_data.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>CoC Number</th>\n",
       "      <th>Total Year-Round Beds (ES, TH, SH)</th>\n",
       "      <th>Total Year-Round Beds (ES)</th>\n",
       "      <th>Total Year-Round Beds (TH)</th>\n",
       "      <th>Total Year-Round Beds (SH)</th>\n",
       "      <th>Total Beds for Households with Children (ES, TH, SH)</th>\n",
       "      <th>Total Beds for Households without Children (ES, TH, SH)</th>\n",
       "      <th>Total Beds for Households with Children (ES)</th>\n",
       "      <th>Total Beds for Households without Children (ES)</th>\n",
       "      <th>Total Beds for Households with Children (TH)</th>\n",
       "      <th>Total Beds for Households without Children (TH)</th>\n",
       "      <th>Total Beds for Households with Children (SH)</th>\n",
       "      <th>Total Beds for Households without Children (SH)</th>\n",
       "      <th>Total Year-Round Beds (RRH)</th>\n",
       "      <th>Total Beds for Households with Children (RRH)</th>\n",
       "      <th>Total Beds for Households without Children (RRH)</th>\n",
       "      <th>Total Year-Round Beds (PSH)</th>\n",
       "      <th>Total Beds for Households with Children (PSH)</th>\n",
       "      <th>Total Beds for Households without Children (PSH)</th>\n",
       "      <th>Year</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>3563</th>\n",
       "      <td>AK-500</td>\n",
       "      <td>988.0</td>\n",
       "      <td>606.0</td>\n",
       "      <td>382.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>330.0</td>\n",
       "      <td>658.0</td>\n",
       "      <td>156.0</td>\n",
       "      <td>450.0</td>\n",
       "      <td>174.0</td>\n",
       "      <td>208.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>139.0</td>\n",
       "      <td>105.0</td>\n",
       "      <td>34.0</td>\n",
       "      <td>505.0</td>\n",
       "      <td>126.0</td>\n",
       "      <td>379.0</td>\n",
       "      <td>2014</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3564</th>\n",
       "      <td>AK-501</td>\n",
       "      <td>826.0</td>\n",
       "      <td>583.0</td>\n",
       "      <td>243.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>316.0</td>\n",
       "      <td>459.0</td>\n",
       "      <td>245.0</td>\n",
       "      <td>316.0</td>\n",
       "      <td>71.0</td>\n",
       "      <td>143.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>19.0</td>\n",
       "      <td>18.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>210.0</td>\n",
       "      <td>41.0</td>\n",
       "      <td>169.0</td>\n",
       "      <td>2014</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3565</th>\n",
       "      <td>AL-500</td>\n",
       "      <td>1065.0</td>\n",
       "      <td>400.0</td>\n",
       "      <td>631.0</td>\n",
       "      <td>34.0</td>\n",
       "      <td>409.0</td>\n",
       "      <td>638.0</td>\n",
       "      <td>82.0</td>\n",
       "      <td>300.0</td>\n",
       "      <td>327.0</td>\n",
       "      <td>304.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>34.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1729.0</td>\n",
       "      <td>965.0</td>\n",
       "      <td>764.0</td>\n",
       "      <td>2014</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3566</th>\n",
       "      <td>AL-501</td>\n",
       "      <td>537.0</td>\n",
       "      <td>344.0</td>\n",
       "      <td>193.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>213.0</td>\n",
       "      <td>324.0</td>\n",
       "      <td>122.0</td>\n",
       "      <td>222.0</td>\n",
       "      <td>91.0</td>\n",
       "      <td>102.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>10.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>10.0</td>\n",
       "      <td>199.0</td>\n",
       "      <td>39.0</td>\n",
       "      <td>160.0</td>\n",
       "      <td>2014</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3567</th>\n",
       "      <td>AL-502</td>\n",
       "      <td>266.0</td>\n",
       "      <td>82.0</td>\n",
       "      <td>184.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>143.0</td>\n",
       "      <td>123.0</td>\n",
       "      <td>48.0</td>\n",
       "      <td>34.0</td>\n",
       "      <td>95.0</td>\n",
       "      <td>89.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>50.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>50.0</td>\n",
       "      <td>2014</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3973</th>\n",
       "      <td>WV-501</td>\n",
       "      <td>261.0</td>\n",
       "      <td>180.0</td>\n",
       "      <td>68.0</td>\n",
       "      <td>13.0</td>\n",
       "      <td>128.0</td>\n",
       "      <td>133.0</td>\n",
       "      <td>60.0</td>\n",
       "      <td>120.0</td>\n",
       "      <td>68.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>13.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>403.0</td>\n",
       "      <td>167.0</td>\n",
       "      <td>236.0</td>\n",
       "      <td>2014</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3974</th>\n",
       "      <td>WV-503</td>\n",
       "      <td>430.0</td>\n",
       "      <td>302.0</td>\n",
       "      <td>128.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>115.0</td>\n",
       "      <td>305.0</td>\n",
       "      <td>75.0</td>\n",
       "      <td>222.0</td>\n",
       "      <td>40.0</td>\n",
       "      <td>83.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>178.0</td>\n",
       "      <td>32.0</td>\n",
       "      <td>146.0</td>\n",
       "      <td>2014</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3975</th>\n",
       "      <td>WV-508</td>\n",
       "      <td>1157.0</td>\n",
       "      <td>804.0</td>\n",
       "      <td>353.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>511.0</td>\n",
       "      <td>646.0</td>\n",
       "      <td>336.0</td>\n",
       "      <td>468.0</td>\n",
       "      <td>175.0</td>\n",
       "      <td>178.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>66.0</td>\n",
       "      <td>41.0</td>\n",
       "      <td>23.0</td>\n",
       "      <td>620.0</td>\n",
       "      <td>308.0</td>\n",
       "      <td>312.0</td>\n",
       "      <td>2014</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3976</th>\n",
       "      <td>WY-500</td>\n",
       "      <td>815.0</td>\n",
       "      <td>445.0</td>\n",
       "      <td>370.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>375.0</td>\n",
       "      <td>426.0</td>\n",
       "      <td>175.0</td>\n",
       "      <td>256.0</td>\n",
       "      <td>200.0</td>\n",
       "      <td>170.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>67.0</td>\n",
       "      <td>52.0</td>\n",
       "      <td>15.0</td>\n",
       "      <td>236.0</td>\n",
       "      <td>127.0</td>\n",
       "      <td>54.0</td>\n",
       "      <td>2014</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3977</th>\n",
       "      <td>Total</td>\n",
       "      <td>424880.0</td>\n",
       "      <td>249497.0</td>\n",
       "      <td>173224.0</td>\n",
       "      <td>2159.0</td>\n",
       "      <td>217401.0</td>\n",
       "      <td>202938.0</td>\n",
       "      <td>123252.0</td>\n",
       "      <td>123173.0</td>\n",
       "      <td>94149.0</td>\n",
       "      <td>77606.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2159.0</td>\n",
       "      <td>36176.0</td>\n",
       "      <td>27911.0</td>\n",
       "      <td>8241.0</td>\n",
       "      <td>300282.0</td>\n",
       "      <td>113487.0</td>\n",
       "      <td>186623.0</td>\n",
       "      <td>2014</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>415 rows × 20 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "     CoC Number  Total Year-Round Beds (ES, TH, SH)  \\\n",
       "3563     AK-500                               988.0   \n",
       "3564     AK-501                               826.0   \n",
       "3565     AL-500                              1065.0   \n",
       "3566     AL-501                               537.0   \n",
       "3567     AL-502                               266.0   \n",
       "...         ...                                 ...   \n",
       "3973     WV-501                               261.0   \n",
       "3974     WV-503                               430.0   \n",
       "3975     WV-508                              1157.0   \n",
       "3976     WY-500                               815.0   \n",
       "3977      Total                            424880.0   \n",
       "\n",
       "      Total Year-Round Beds (ES)  Total Year-Round Beds (TH)  \\\n",
       "3563                       606.0                       382.0   \n",
       "3564                       583.0                       243.0   \n",
       "3565                       400.0                       631.0   \n",
       "3566                       344.0                       193.0   \n",
       "3567                        82.0                       184.0   \n",
       "...                          ...                         ...   \n",
       "3973                       180.0                        68.0   \n",
       "3974                       302.0                       128.0   \n",
       "3975                       804.0                       353.0   \n",
       "3976                       445.0                       370.0   \n",
       "3977                    249497.0                    173224.0   \n",
       "\n",
       "      Total Year-Round Beds (SH)  \\\n",
       "3563                         0.0   \n",
       "3564                         0.0   \n",
       "3565                        34.0   \n",
       "3566                         0.0   \n",
       "3567                         0.0   \n",
       "...                          ...   \n",
       "3973                        13.0   \n",
       "3974                         0.0   \n",
       "3975                         0.0   \n",
       "3976                         0.0   \n",
       "3977                      2159.0   \n",
       "\n",
       "      Total Beds for Households with Children (ES, TH, SH)  \\\n",
       "3563                                              330.0      \n",
       "3564                                              316.0      \n",
       "3565                                              409.0      \n",
       "3566                                              213.0      \n",
       "3567                                              143.0      \n",
       "...                                                 ...      \n",
       "3973                                              128.0      \n",
       "3974                                              115.0      \n",
       "3975                                              511.0      \n",
       "3976                                              375.0      \n",
       "3977                                           217401.0      \n",
       "\n",
       "      Total Beds for Households without Children (ES, TH, SH)  \\\n",
       "3563                                              658.0         \n",
       "3564                                              459.0         \n",
       "3565                                              638.0         \n",
       "3566                                              324.0         \n",
       "3567                                              123.0         \n",
       "...                                                 ...         \n",
       "3973                                              133.0         \n",
       "3974                                              305.0         \n",
       "3975                                              646.0         \n",
       "3976                                              426.0         \n",
       "3977                                           202938.0         \n",
       "\n",
       "      Total Beds for Households with Children (ES)  \\\n",
       "3563                                         156.0   \n",
       "3564                                         245.0   \n",
       "3565                                          82.0   \n",
       "3566                                         122.0   \n",
       "3567                                          48.0   \n",
       "...                                            ...   \n",
       "3973                                          60.0   \n",
       "3974                                          75.0   \n",
       "3975                                         336.0   \n",
       "3976                                         175.0   \n",
       "3977                                      123252.0   \n",
       "\n",
       "      Total Beds for Households without Children (ES)  \\\n",
       "3563                                            450.0   \n",
       "3564                                            316.0   \n",
       "3565                                            300.0   \n",
       "3566                                            222.0   \n",
       "3567                                             34.0   \n",
       "...                                               ...   \n",
       "3973                                            120.0   \n",
       "3974                                            222.0   \n",
       "3975                                            468.0   \n",
       "3976                                            256.0   \n",
       "3977                                         123173.0   \n",
       "\n",
       "      Total Beds for Households with Children (TH)  \\\n",
       "3563                                         174.0   \n",
       "3564                                          71.0   \n",
       "3565                                         327.0   \n",
       "3566                                          91.0   \n",
       "3567                                          95.0   \n",
       "...                                            ...   \n",
       "3973                                          68.0   \n",
       "3974                                          40.0   \n",
       "3975                                         175.0   \n",
       "3976                                         200.0   \n",
       "3977                                       94149.0   \n",
       "\n",
       "      Total Beds for Households without Children (TH)  \\\n",
       "3563                                            208.0   \n",
       "3564                                            143.0   \n",
       "3565                                            304.0   \n",
       "3566                                            102.0   \n",
       "3567                                             89.0   \n",
       "...                                               ...   \n",
       "3973                                              0.0   \n",
       "3974                                             83.0   \n",
       "3975                                            178.0   \n",
       "3976                                            170.0   \n",
       "3977                                          77606.0   \n",
       "\n",
       "      Total Beds for Households with Children (SH)  \\\n",
       "3563                                           0.0   \n",
       "3564                                           0.0   \n",
       "3565                                           0.0   \n",
       "3566                                           0.0   \n",
       "3567                                           0.0   \n",
       "...                                            ...   \n",
       "3973                                           0.0   \n",
       "3974                                           0.0   \n",
       "3975                                           0.0   \n",
       "3976                                           0.0   \n",
       "3977                                           0.0   \n",
       "\n",
       "      Total Beds for Households without Children (SH)  \\\n",
       "3563                                              0.0   \n",
       "3564                                              0.0   \n",
       "3565                                             34.0   \n",
       "3566                                              0.0   \n",
       "3567                                              0.0   \n",
       "...                                               ...   \n",
       "3973                                             13.0   \n",
       "3974                                              0.0   \n",
       "3975                                              0.0   \n",
       "3976                                              0.0   \n",
       "3977                                           2159.0   \n",
       "\n",
       "      Total Year-Round Beds (RRH)  \\\n",
       "3563                        139.0   \n",
       "3564                         19.0   \n",
       "3565                          0.0   \n",
       "3566                         10.0   \n",
       "3567                          0.0   \n",
       "...                           ...   \n",
       "3973                          4.0   \n",
       "3974                          2.0   \n",
       "3975                         66.0   \n",
       "3976                         67.0   \n",
       "3977                      36176.0   \n",
       "\n",
       "      Total Beds for Households with Children (RRH)  \\\n",
       "3563                                          105.0   \n",
       "3564                                           18.0   \n",
       "3565                                            0.0   \n",
       "3566                                            0.0   \n",
       "3567                                            0.0   \n",
       "...                                             ...   \n",
       "3973                                            0.0   \n",
       "3974                                            0.0   \n",
       "3975                                           41.0   \n",
       "3976                                           52.0   \n",
       "3977                                        27911.0   \n",
       "\n",
       "      Total Beds for Households without Children (RRH)  \\\n",
       "3563                                              34.0   \n",
       "3564                                               1.0   \n",
       "3565                                               0.0   \n",
       "3566                                              10.0   \n",
       "3567                                               0.0   \n",
       "...                                                ...   \n",
       "3973                                               4.0   \n",
       "3974                                               2.0   \n",
       "3975                                              23.0   \n",
       "3976                                              15.0   \n",
       "3977                                            8241.0   \n",
       "\n",
       "      Total Year-Round Beds (PSH)  \\\n",
       "3563                        505.0   \n",
       "3564                        210.0   \n",
       "3565                       1729.0   \n",
       "3566                        199.0   \n",
       "3567                         50.0   \n",
       "...                           ...   \n",
       "3973                        403.0   \n",
       "3974                        178.0   \n",
       "3975                        620.0   \n",
       "3976                        236.0   \n",
       "3977                     300282.0   \n",
       "\n",
       "      Total Beds for Households with Children (PSH)  \\\n",
       "3563                                          126.0   \n",
       "3564                                           41.0   \n",
       "3565                                          965.0   \n",
       "3566                                           39.0   \n",
       "3567                                            0.0   \n",
       "...                                             ...   \n",
       "3973                                          167.0   \n",
       "3974                                           32.0   \n",
       "3975                                          308.0   \n",
       "3976                                          127.0   \n",
       "3977                                       113487.0   \n",
       "\n",
       "      Total Beds for Households without Children (PSH)  Year  \n",
       "3563                                             379.0  2014  \n",
       "3564                                             169.0  2014  \n",
       "3565                                             764.0  2014  \n",
       "3566                                             160.0  2014  \n",
       "3567                                              50.0  2014  \n",
       "...                                                ...   ...  \n",
       "3973                                             236.0  2014  \n",
       "3974                                             146.0  2014  \n",
       "3975                                             312.0  2014  \n",
       "3976                                              54.0  2014  \n",
       "3977                                          186623.0  2014  \n",
       "\n",
       "[415 rows x 20 columns]"
      ]
     },
     "execution_count": 39,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "merged_data[merged_data['Year'] == '2014']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(7066, 20)"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "merged_data.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [],
   "source": [
    "merged_data.to_csv('2014-2023-HIC-Counts-by-CoC_merged.csv')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#Conclusion \n",
    "The code above achieves a merged dataset for indicators under consideration from 2014 to 2023 for HIC data set. "
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "base",
   "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.11.9"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}