Clean and Concat DFs.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "21fe7a03",
   "metadata": {},
   "source": [
    "## Our notes\n",
    "\n",
    "In this file we created data frames for each year of data sets and we took some time to understand the data in each set. We corrected the column names, dropped irrelevant columns and merged all of the data frames together so we would have one complete data frame with all rows of data from 2013-2023."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "07c21100",
   "metadata": {},
   "source": [
    "naics_dict = {\n",
    "   - \"11\": \"Agriculture, Forestry, Fishing and Hunting\",\n",
    "   - \"21\": \"Mining, Quarrying, and Oil and Gas Extraction\",\n",
    "   - \"22\": \"Utilities\",\n",
    "   - \"23\": \"Construction\",\n",
    "   - \"31\": \"Manufacturing\",\n",
    "   - \"32\": \"Manufacturing\",\n",
    "   - \"33\": \"Manufacturing\",\n",
    "   - \"42\": \"Wholesale Trade\",\n",
    "   - \"44\": \"Retail Trade\",\n",
    "   - \"45\": \"Retail Trade\",\n",
    "   - \"48\": \"Transportation and Warehousing\",\n",
    "   - \"49\": \"Transportation and Warehousing\",\n",
    "   - \"51\": \"Information\",\n",
    "   - \"52\": \"Finance and Insurance\",\n",
    "   - \"53\": \"Real Estate and Rental and Leasing\",\n",
    "   - \"54\": \"Professional, Scientific, and Technical Services\",\n",
    "   - \"55\": \"Management of Companies and Enterprises\",\n",
    "   - \"56\": \"Administrative and Support and Waste Management and Remediation Services\",\n",
    "   - \"61\": \"Educational Services\",\n",
    "   - \"62\": \"Health Care and Social Assistance\",\n",
    "   - \"71\": \"Arts, Entertainment, and Recreation\",\n",
    "   - \"72\": \"Accommodation and Food Services\",\n",
    "   - \"81\": \"Other Services (except Public Administration)\",\n",
    "   - \"92\": \"Public Administration\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "id": "be7e85e5",
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "\n",
    "from Biz_Set_Functions import *"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "id": "b6e1c284",
   "metadata": {},
   "outputs": [],
   "source": [
    "#loading dfs, adding year column and standardizing column titles\n",
    "df13 = made_df('Data Sets/cbp13pr_mun.csv', 2013)\n",
    "df14 = made_df('Data Sets/cbp14pr_mun.csv', 2014)\n",
    "df15 = made_df('Data Sets/cbp15pr_mun.csv', 2015)\n",
    "df16 = made_df('Data Sets/cbp16pr_mun.csv', 2016)\n",
    "df17 = made_df('Data Sets/cbp17pr_co.csv', 2017)\n",
    "df18 = made_df('Data Sets/cbp_pr_mun_2018.csv', 2018)\n",
    "df19 = made_df('Data Sets/cbp_pr_mun_2019.csv', 2019)\n",
    "\n",
    "files = ['Data Sets/2020-1.csv','Data Sets/2020-2.csv','Data Sets/2020-3.csv','Data Sets/2020-4.csv']\n",
    "df20 = combined_df(files, 2020)\n",
    "\n",
    "files21 = ['Data Sets/_lmi_excel_QCEW_2021_Composición Industrial Municipio 1.csv',\n",
    "           'Data Sets/_lmi_excel_QCEW_2021_Composición Industrial Municipio 2.csv',\n",
    "          'Data Sets/_lmi_excel_QCEW_2021_Composición Industrial Municipio 3.csv',\n",
    "          'Data Sets/_lmi_excel_QCEW_2021_Composición Industrial Municipio 4.csv']\n",
    "df21 = combined_df(files21, 2021)\n",
    "\n",
    "files22= ['Data Sets/2022_1.csv',\n",
    "         'Data Sets/2022_2.csv',\n",
    "         'Data Sets/2022_3.csv',\n",
    "         'Data Sets/2022_4.csv']\n",
    "df22 = combined_df(files22, 2022)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "id": "7cdf4fb4",
   "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>fipstate</th>\n",
       "      <th>town_code</th>\n",
       "      <th>naics</th>\n",
       "      <th>empflag</th>\n",
       "      <th>emp_nf</th>\n",
       "      <th>emp</th>\n",
       "      <th>qp1_nf</th>\n",
       "      <th>qp1</th>\n",
       "      <th>ap_nf</th>\n",
       "      <th>ap</th>\n",
       "      <th>...</th>\n",
       "      <th>unnamed: 24</th>\n",
       "      <th>unnamed: 25</th>\n",
       "      <th>unnamed: 26</th>\n",
       "      <th>salrio total municipio segundo trimestre 2019</th>\n",
       "      <th>salrio total municipio primer trimestre 2021</th>\n",
       "      <th>salrio total municipio tercer  trimestre 2021</th>\n",
       "      <th>salrio total municipio cuarto trimestre 2021</th>\n",
       "      <th>salrio total municipio primr trimestre 2022</th>\n",
       "      <th>salario total municipio segundo trimestre 2022</th>\n",
       "      <th>salario total municipio tercer trimestre 2022</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>72.0</td>\n",
       "      <td>1</td>\n",
       "      <td>------</td>\n",
       "      <td>NaN</td>\n",
       "      <td>G</td>\n",
       "      <td>706.0</td>\n",
       "      <td>G</td>\n",
       "      <td>2672.0</td>\n",
       "      <td>G</td>\n",
       "      <td>10719.0</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\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>72.0</td>\n",
       "      <td>1</td>\n",
       "      <td>23----</td>\n",
       "      <td>B</td>\n",
       "      <td>D</td>\n",
       "      <td>0.0</td>\n",
       "      <td>D</td>\n",
       "      <td>0.0</td>\n",
       "      <td>D</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>72.0</td>\n",
       "      <td>1</td>\n",
       "      <td>236///</td>\n",
       "      <td>A</td>\n",
       "      <td>D</td>\n",
       "      <td>0.0</td>\n",
       "      <td>D</td>\n",
       "      <td>0.0</td>\n",
       "      <td>D</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>72.0</td>\n",
       "      <td>1</td>\n",
       "      <td>2361//</td>\n",
       "      <td>A</td>\n",
       "      <td>D</td>\n",
       "      <td>0.0</td>\n",
       "      <td>D</td>\n",
       "      <td>0.0</td>\n",
       "      <td>D</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>72.0</td>\n",
       "      <td>1</td>\n",
       "      <td>23611/</td>\n",
       "      <td>A</td>\n",
       "      <td>D</td>\n",
       "      <td>0.0</td>\n",
       "      <td>D</td>\n",
       "      <td>0.0</td>\n",
       "      <td>D</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\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",
       "      <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>13755</th>\n",
       "      <td>NaN</td>\n",
       "      <td>999</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13756</th>\n",
       "      <td>NaN</td>\n",
       "      <td>999</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13757</th>\n",
       "      <td>NaN</td>\n",
       "      <td>999</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13758</th>\n",
       "      <td>NaN</td>\n",
       "      <td>999</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13759</th>\n",
       "      <td>NaN</td>\n",
       "      <td>999</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>210488 rows × 59 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "       fipstate  town_code   naics empflag emp_nf    emp qp1_nf     qp1 ap_nf  \\\n",
       "0          72.0          1  ------     NaN      G  706.0      G  2672.0     G   \n",
       "1          72.0          1  23----       B      D    0.0      D     0.0     D   \n",
       "2          72.0          1  236///       A      D    0.0      D     0.0     D   \n",
       "3          72.0          1  2361//       A      D    0.0      D     0.0     D   \n",
       "4          72.0          1  23611/       A      D    0.0      D     0.0     D   \n",
       "...         ...        ...     ...     ...    ...    ...    ...     ...   ...   \n",
       "13755       NaN        999     NaN     NaN    NaN    NaN    NaN     NaN   NaN   \n",
       "13756       NaN        999     NaN     NaN    NaN    NaN    NaN     NaN   NaN   \n",
       "13757       NaN        999     NaN     NaN    NaN    NaN    NaN     NaN   NaN   \n",
       "13758       NaN        999     NaN     NaN    NaN    NaN    NaN     NaN   NaN   \n",
       "13759       NaN        999     NaN     NaN    NaN    NaN    NaN     NaN   NaN   \n",
       "\n",
       "            ap  ... unnamed: 24  unnamed: 25 unnamed: 26  \\\n",
       "0      10719.0  ...         NaN          NaN         NaN   \n",
       "1          0.0  ...         NaN          NaN         NaN   \n",
       "2          0.0  ...         NaN          NaN         NaN   \n",
       "3          0.0  ...         NaN          NaN         NaN   \n",
       "4          0.0  ...         NaN          NaN         NaN   \n",
       "...        ...  ...         ...          ...         ...   \n",
       "13755      NaN  ...         NaN          NaN         NaN   \n",
       "13756      NaN  ...         NaN          NaN         NaN   \n",
       "13757      NaN  ...         NaN          NaN         NaN   \n",
       "13758      NaN  ...         NaN          NaN         NaN   \n",
       "13759      NaN  ...         NaN          NaN         NaN   \n",
       "\n",
       "      salrio total municipio segundo trimestre 2019  \\\n",
       "0                                               NaN   \n",
       "1                                               NaN   \n",
       "2                                               NaN   \n",
       "3                                               NaN   \n",
       "4                                               NaN   \n",
       "...                                             ...   \n",
       "13755                                           NaN   \n",
       "13756                                           NaN   \n",
       "13757                                           NaN   \n",
       "13758                                           NaN   \n",
       "13759                                           NaN   \n",
       "\n",
       "      salrio total municipio primer trimestre 2021  \\\n",
       "0                                              NaN   \n",
       "1                                              NaN   \n",
       "2                                              NaN   \n",
       "3                                              NaN   \n",
       "4                                              NaN   \n",
       "...                                            ...   \n",
       "13755                                          NaN   \n",
       "13756                                          NaN   \n",
       "13757                                          NaN   \n",
       "13758                                          NaN   \n",
       "13759                                          NaN   \n",
       "\n",
       "      salrio total municipio tercer  trimestre 2021  \\\n",
       "0                                               NaN   \n",
       "1                                               NaN   \n",
       "2                                               NaN   \n",
       "3                                               NaN   \n",
       "4                                               NaN   \n",
       "...                                             ...   \n",
       "13755                                           NaN   \n",
       "13756                                           NaN   \n",
       "13757                                           NaN   \n",
       "13758                                           NaN   \n",
       "13759                                           NaN   \n",
       "\n",
       "      salrio total municipio cuarto trimestre 2021  \\\n",
       "0                                              NaN   \n",
       "1                                              NaN   \n",
       "2                                              NaN   \n",
       "3                                              NaN   \n",
       "4                                              NaN   \n",
       "...                                            ...   \n",
       "13755                                          NaN   \n",
       "13756                                          NaN   \n",
       "13757                                          NaN   \n",
       "13758                                          NaN   \n",
       "13759                                          NaN   \n",
       "\n",
       "      salrio total municipio primr trimestre 2022  \\\n",
       "0                                             NaN   \n",
       "1                                             NaN   \n",
       "2                                             NaN   \n",
       "3                                             NaN   \n",
       "4                                             NaN   \n",
       "...                                           ...   \n",
       "13755                                         NaN   \n",
       "13756                                         NaN   \n",
       "13757                                         NaN   \n",
       "13758                                         NaN   \n",
       "13759                                         NaN   \n",
       "\n",
       "      salario total municipio segundo trimestre 2022  \\\n",
       "0                                                NaN   \n",
       "1                                                NaN   \n",
       "2                                                NaN   \n",
       "3                                                NaN   \n",
       "4                                                NaN   \n",
       "...                                              ...   \n",
       "13755                                            NaN   \n",
       "13756                                            NaN   \n",
       "13757                                            NaN   \n",
       "13758                                            NaN   \n",
       "13759                                            NaN   \n",
       "\n",
       "      salario total municipio tercer trimestre 2022  \n",
       "0                                               NaN  \n",
       "1                                               NaN  \n",
       "2                                               NaN  \n",
       "3                                               NaN  \n",
       "4                                               NaN  \n",
       "...                                             ...  \n",
       "13755                                             0  \n",
       "13756                                             0  \n",
       "13757                                             0  \n",
       "13758                                             0  \n",
       "13759                                             0  \n",
       "\n",
       "[210488 rows x 59 columns]"
      ]
     },
     "execution_count": 40,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#concat\n",
    "df13_19 = pd.concat([df13,df14,df15,df16,df17,df18,df19])\n",
    "df13_19\n",
    "\n",
    "df20_22 = pd.concat([df20,df21,df22])\n",
    "df20_22\n",
    "\n",
    "#translating and renaming columns\n",
    "\n",
    "def rename_columns(x):\n",
    "    new_names={'industria':'industry','cty':'town_code','municipio':'town_name','unidades':'establishments',\n",
    "               'empleo promedio':'average_employment','promedio':'average','est':'establishments','fipscty':'town_code'}\n",
    "    x.columns=x.columns.str.strip()\n",
    "    x.rename(columns=new_names, inplace=True)\n",
    "    return x\n",
    "\n",
    "df12_19 = rename_columns(df13_19)\n",
    "df20_22 = rename_columns(df20_22)\n",
    "\n",
    "#final concat\n",
    "df13_22 = pd.concat([df13_19, df20_22], axis = 0)\n",
    "df13_22"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "id": "d62ca4f6",
   "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>town_code</th>\n",
       "      <th>naics</th>\n",
       "      <th>establishments</th>\n",
       "      <th>year</th>\n",
       "      <th>town_name</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>------</td>\n",
       "      <td>130</td>\n",
       "      <td>2013</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>23----</td>\n",
       "      <td>8</td>\n",
       "      <td>2013</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1</td>\n",
       "      <td>236///</td>\n",
       "      <td>5</td>\n",
       "      <td>2013</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1</td>\n",
       "      <td>2361//</td>\n",
       "      <td>5</td>\n",
       "      <td>2013</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1</td>\n",
       "      <td>23611/</td>\n",
       "      <td>5</td>\n",
       "      <td>2013</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",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13755</th>\n",
       "      <td>999</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>2022</td>\n",
       "      <td>No Codificado</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13756</th>\n",
       "      <td>999</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>2022</td>\n",
       "      <td>No Codificado</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13757</th>\n",
       "      <td>999</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>2022</td>\n",
       "      <td>No Codificado</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13758</th>\n",
       "      <td>999</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>2022</td>\n",
       "      <td>No Codificado</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13759</th>\n",
       "      <td>999</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>2022</td>\n",
       "      <td>No Codificado</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>210488 rows × 5 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "       town_code   naics establishments  year      town_name\n",
       "0              1  ------            130  2013            NaN\n",
       "1              1  23----              8  2013            NaN\n",
       "2              1  236///              5  2013            NaN\n",
       "3              1  2361//              5  2013            NaN\n",
       "4              1  23611/              5  2013            NaN\n",
       "...          ...     ...            ...   ...            ...\n",
       "13755        999     NaN              0  2022  No Codificado\n",
       "13756        999     NaN              0  2022  No Codificado\n",
       "13757        999     NaN              0  2022  No Codificado\n",
       "13758        999     NaN              0  2022  No Codificado\n",
       "13759        999     NaN              0  2022  No Codificado\n",
       "\n",
       "[210488 rows x 5 columns]"
      ]
     },
     "execution_count": 41,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#dropping unneeded columns\n",
    "\n",
    "\n",
    "columns_to_drop = ['fipstate','empflag', 'emp_nf', 'emp', 'qp1_nf',\n",
    "                   'qp1', 'ap_nf', 'ap', 'n1_4', 'n5_9', 'n10_19', 'n20_49',\n",
    "                   'n50_99', 'n100_249', 'n250_499', 'n500_999', 'n1000', 'n1000_1',\n",
    "                   'n1000_2', 'n1000_3', 'n1000_4', 'censtate', 'cencty', 'unnamed: 10', 'unnamed: 11',\n",
    "                   'unnamed: 12', 'unnamed: 13', 'unnamed: 14', 'unnamed: 15',\n",
    "                   'unnamed: 16', 'unnamed: 17', 'unnamed: 18', 'unnamed: 19',\n",
    "                   'unnamed: 20', 'unnamed: 21', 'unnamed: 22', 'unnamed: 23',\n",
    "                   'unnamed: 24', 'average_employment','salrio total municipio cuarto trimestre 2021',\n",
    "                   'salrio total municipio tercer  trimestre 2021','salrio total municipio primer trimestre 2021',\n",
    "                   'salrio total municipio primr trimestre 2022','salario total municipio segundo trimestre 2022',\n",
    "                   'salario total municipio tercer trimestre 2022','average_employment',\n",
    "                   'salrio total municipio segundo trimestre 2019','salario total','unnamed: 8','unnamed: 9',\n",
    "                   'unnamed: 25','unnamed: 26','n<5','average','industry']\n",
    "\n",
    "df13_22.drop(columns = columns_to_drop, inplace=True)\n",
    "\n",
    "df13_22"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "id": "ab46337f",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "Index: 210488 entries, 0 to 13759\n",
      "Data columns (total 5 columns):\n",
      " #   Column          Non-Null Count   Dtype \n",
      "---  ------          --------------   ----- \n",
      " 0   town_code       210488 non-null  int64 \n",
      " 1   naics           209103 non-null  object\n",
      " 2   establishments  210488 non-null  object\n",
      " 3   year            210488 non-null  int64 \n",
      " 4   town_name       41280 non-null   object\n",
      "dtypes: int64(2), object(3)\n",
      "memory usage: 9.6+ MB\n"
     ]
    }
   ],
   "source": [
    "df13_22.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "id": "cd10c4fc",
   "metadata": {},
   "outputs": [],
   "source": [
    "#updating column dtypes\n",
    "df13_22[\"establishments\"]=pd.to_numeric(df13_22[\"establishments\"], errors='coerce')\n",
    "df13_22['year'] = pd.to_datetime(df13_22['year'], format='%Y')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "id": "349d24f5",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "town_code              0\n",
       "naics                  0\n",
       "establishments         0\n",
       "year                   0\n",
       "town_name         169208\n",
       "dtype: int64"
      ]
     },
     "execution_count": 44,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#dropping null rows based on columns\n",
    "df13_22.dropna(subset=['naics','establishments'], inplace=True)\n",
    "df13_22.isna().sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "id": "faf622a4",
   "metadata": {},
   "outputs": [],
   "source": [
    "#dropping null rows based on having no useful data\n",
    "no_towns_naics = df13_22[(df13_22['town_name'] == 'No Codificado') | (df13_22['naics'] == '------')].index\n",
    "multi_town = df13_22[(df13_22['town_name'] == 'Multi Municipio') | (df13_22['town_name'] == 'Statewide')].index\n",
    "\n",
    "df13_22.drop(no_towns_naics, inplace=True)\n",
    "df13_22.drop(multi_town, inplace=True)\n",
    "\n",
    "df13_22['town_name'].replace('nan',None,inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 46,
   "id": "88bc2954",
   "metadata": {},
   "outputs": [],
   "source": [
    "#standardizing naics column. We just want to look at overall industry, not get into the nitty gritty subcategories\n",
    "\n",
    "def replace_naics(naics_code):\n",
    "    # Dictionary mapping NAICS codes to industry names\n",
    "    naics_dict = {\n",
    "    \"11\": \"11\",\"21\": \"21\",\"22\": \"22\",\"23\": \"23\",\"31\": \"31\",\"32\": \"32\",\"33\": \"33\",\"42\": \"42\",\"44\": \"44\",\"45\": \"45\",\n",
    "    \"48\": \"48\",\"49\": \"49\",\"51\": \"51\",\"52\": \"52\",\"53\": \"53\",\"54\": \"54\",\"55\": \"55\",\"56\": \"56\",\"61\": \"61\",\"62\": \"62\",\"71\":\"71\",\n",
    "    \"72\": \"72\",\"81\": \"81\",\"92\": \"92\"\n",
    "}\n",
    "    \n",
    "    industry_code = naics_code[:2]  # Extract first two digits of NAICS code\n",
    "    return naics_dict.get(industry_code, \"00\")\n",
    "\n",
    "\n",
    "df13_22['naics'] = df13_22['naics'].apply(replace_naics)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "id": "7a43b351",
   "metadata": {},
   "outputs": [],
   "source": [
    "#dropping rows of the bottom 10 industries that are tiny\n",
    "df13_22 = df13_22[df13_22['naics'] != 61 | 51 | 71 | 49 | 21 | 55 | 22 | 11 | 92]\n",
    "\n",
    "df13_22['naics'] = pd.to_numeric(df13_22['naics'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 77,
   "id": "4de035bb",
   "metadata": {},
   "outputs": [],
   "source": [
    "df13_22['town_name'] = replace_code_with_town(df13_22, 'town_code')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 80,
   "id": "7920ad84",
   "metadata": {},
   "outputs": [],
   "source": [
    "#we decided to group the towns by region to make graphing much more streamlined\n",
    "df13_22['region'] = replace_town_with_region(df13_22,'town_code')\n",
    "\n",
    "#making sure all towns labeled accurately\n",
    "df13_22['town_name'] = replace_code_with_town(df13_22, 'town_code')\n",
    "\n",
    "#resetting the index\n",
    "df13_22 = df13_22.reset_index(drop=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 81,
   "id": "ba5f36e6",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "town_name\n",
       "San Juan     9247\n",
       "Bayamon      6810\n",
       "Caguas       6350\n",
       "Guaynabo     6321\n",
       "Ponce        6062\n",
       "             ... \n",
       "Maunabo      1069\n",
       "Culebra       984\n",
       "Maricao       774\n",
       "995             0\n",
       "Statewide       0\n",
       "Name: count, Length: 80, dtype: int64"
      ]
     },
     "execution_count": 81,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df13_22['town_name'].value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "62f3a7a5",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Pickle the DataFrame to use elsewhere\n",
    "df13_22.to_pickle('full_clean_df.pkl')\n",
    "\n",
    "# Step 1: Load the DataFrame from the pickle file\n",
    "df = pd.read_pickle('full_clean_df.pkl')\n",
    "\n",
    "# Step 2: Save the DataFrame to a CSV file\n",
    "df.to_csv('pr_biz_dataset_clean.csv', index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "091176f6",
   "metadata": {},
   "outputs": [],
   "source": [
    "df"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.11.7"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}