Explore & Clean_Industry Data.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2020-06-04T03:14:40.362763Z",
     "start_time": "2020-06-04T03:14:39.135762Z"
    }
   },
   "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>series_id</th>\n",
       "      <th>year</th>\n",
       "      <th>value</th>\n",
       "      <th>industry_code_x</th>\n",
       "      <th>Month</th>\n",
       "      <th>supersector_code</th>\n",
       "      <th>data_type_code</th>\n",
       "      <th>series_title</th>\n",
       "      <th>supersector_name</th>\n",
       "      <th>data_type_text</th>\n",
       "      <th>short_title</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>CES0000000001</td>\n",
       "      <td>2015</td>\n",
       "      <td>140568.0</td>\n",
       "      <td>00000000</td>\n",
       "      <td>1</td>\n",
       "      <td>00</td>\n",
       "      <td>01</td>\n",
       "      <td>All employees, thousands, total nonfarm, seaso...</td>\n",
       "      <td>Total nonfarm</td>\n",
       "      <td>ALL EMPLOYEES, THOUSANDS</td>\n",
       "      <td>All employees</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>CES0000000001</td>\n",
       "      <td>2015</td>\n",
       "      <td>140839.0</td>\n",
       "      <td>00000000</td>\n",
       "      <td>2</td>\n",
       "      <td>00</td>\n",
       "      <td>01</td>\n",
       "      <td>All employees, thousands, total nonfarm, seaso...</td>\n",
       "      <td>Total nonfarm</td>\n",
       "      <td>ALL EMPLOYEES, THOUSANDS</td>\n",
       "      <td>All employees</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>CES0000000001</td>\n",
       "      <td>2015</td>\n",
       "      <td>140910.0</td>\n",
       "      <td>00000000</td>\n",
       "      <td>3</td>\n",
       "      <td>00</td>\n",
       "      <td>01</td>\n",
       "      <td>All employees, thousands, total nonfarm, seaso...</td>\n",
       "      <td>Total nonfarm</td>\n",
       "      <td>ALL EMPLOYEES, THOUSANDS</td>\n",
       "      <td>All employees</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>CES0000000001</td>\n",
       "      <td>2015</td>\n",
       "      <td>141194.0</td>\n",
       "      <td>00000000</td>\n",
       "      <td>4</td>\n",
       "      <td>00</td>\n",
       "      <td>01</td>\n",
       "      <td>All employees, thousands, total nonfarm, seaso...</td>\n",
       "      <td>Total nonfarm</td>\n",
       "      <td>ALL EMPLOYEES, THOUSANDS</td>\n",
       "      <td>All employees</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>CES0000000001</td>\n",
       "      <td>2015</td>\n",
       "      <td>141525.0</td>\n",
       "      <td>00000000</td>\n",
       "      <td>5</td>\n",
       "      <td>00</td>\n",
       "      <td>01</td>\n",
       "      <td>All employees, thousands, total nonfarm, seaso...</td>\n",
       "      <td>Total nonfarm</td>\n",
       "      <td>ALL EMPLOYEES, THOUSANDS</td>\n",
       "      <td>All employees</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       series_id  year     value industry_code_x  Month supersector_code  \\\n",
       "0  CES0000000001  2015  140568.0        00000000      1               00   \n",
       "1  CES0000000001  2015  140839.0        00000000      2               00   \n",
       "2  CES0000000001  2015  140910.0        00000000      3               00   \n",
       "3  CES0000000001  2015  141194.0        00000000      4               00   \n",
       "4  CES0000000001  2015  141525.0        00000000      5               00   \n",
       "\n",
       "  data_type_code                                       series_title  \\\n",
       "0             01  All employees, thousands, total nonfarm, seaso...   \n",
       "1             01  All employees, thousands, total nonfarm, seaso...   \n",
       "2             01  All employees, thousands, total nonfarm, seaso...   \n",
       "3             01  All employees, thousands, total nonfarm, seaso...   \n",
       "4             01  All employees, thousands, total nonfarm, seaso...   \n",
       "\n",
       "  supersector_name            data_type_text    short_title  \n",
       "0    Total nonfarm  ALL EMPLOYEES, THOUSANDS  All employees  \n",
       "1    Total nonfarm  ALL EMPLOYEES, THOUSANDS  All employees  \n",
       "2    Total nonfarm  ALL EMPLOYEES, THOUSANDS  All employees  \n",
       "3    Total nonfarm  ALL EMPLOYEES, THOUSANDS  All employees  \n",
       "4    Total nonfarm  ALL EMPLOYEES, THOUSANDS  All employees  "
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Dependencies and Setup\n",
    "import pandas as pd\n",
    "import numpy as np\n",
    "from datetime import datetime,date\n",
    "from sqlalchemy import create_engine\n",
    "\n",
    "# File to Load\n",
    "CESDF=pd.read_pickle('resources/CES2020.pkl',compression='gzip')\n",
    "\n",
    "# Output File (CSV)\n",
    "output_data_file = \"Resources/Industry_Data.csv\"\n",
    "\n",
    "# Preview of the DataFrame\n",
    "CESDF.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2020-06-04T03:14:40.377765Z",
     "start_time": "2020-06-04T03:14:40.365763Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "series_id            object\n",
       "year                  int64\n",
       "value               float64\n",
       "industry_code_x      object\n",
       "Month                 int32\n",
       "supersector_code     object\n",
       "data_type_code       object\n",
       "series_title         object\n",
       "supersector_name     object\n",
       "data_type_text       object\n",
       "short_title          object\n",
       "dtype: object"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Check for correct data types\n",
    "CESDF.dtypes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2020-06-04T03:14:41.252407Z",
     "start_time": "2020-06-04T03:14:41.231394Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['series_id', 'year', 'value', 'industry_code_x', 'Month',\n",
       "       'supersector_code', 'data_type_code', 'series_title',\n",
       "       'supersector_name', 'data_type_text', 'short_title'],\n",
       "      dtype='object')"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Check column names for spaces\n",
    "CESDF.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2020-06-04T03:14:43.202212Z",
     "start_time": "2020-06-04T03:14:43.184208Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "series_id           4020\n",
       "year                4020\n",
       "value               4020\n",
       "industry_code_x     4020\n",
       "Month               4020\n",
       "supersector_code    4020\n",
       "data_type_code      4020\n",
       "series_title        4020\n",
       "supersector_name    4020\n",
       "data_type_text      4020\n",
       "short_title         4020\n",
       "dtype: int64"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Identify incomplete rows\n",
    "CESDF.count()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2020-06-04T03:14:45.218485Z",
     "start_time": "2020-06-04T03:14:45.198486Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "CES6561000001    65\n",
       "CES7000000001    65\n",
       "CES4300000010    65\n",
       "CES4200000010    65\n",
       "CES8000000001    65\n",
       "                 ..\n",
       "CES9092000010    64\n",
       "CES6056000010    64\n",
       "CES9093000010    64\n",
       "CES7071000010    64\n",
       "CES9091000010    64\n",
       "Name: series_id, Length: 62, dtype: int64"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Check series values\n",
    "CESDF['series_id'].value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2020-06-04T03:14:47.315353Z",
     "start_time": "2020-06-04T03:14:47.279352Z"
    }
   },
   "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>series_id</th>\n",
       "      <th>year</th>\n",
       "      <th>value</th>\n",
       "      <th>industry_code_x</th>\n",
       "      <th>Month</th>\n",
       "      <th>supersector_code</th>\n",
       "      <th>data_type_code</th>\n",
       "      <th>series_title</th>\n",
       "      <th>supersector_name</th>\n",
       "      <th>data_type_text</th>\n",
       "      <th>short_title</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>CES0000000001</td>\n",
       "      <td>2015</td>\n",
       "      <td>140568.0</td>\n",
       "      <td>00000000</td>\n",
       "      <td>1</td>\n",
       "      <td>00</td>\n",
       "      <td>01</td>\n",
       "      <td>All employees, thousands, total nonfarm, seaso...</td>\n",
       "      <td>Total nonfarm</td>\n",
       "      <td>ALL EMPLOYEES, THOUSANDS</td>\n",
       "      <td>All employees</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>CES0000000001</td>\n",
       "      <td>2015</td>\n",
       "      <td>140839.0</td>\n",
       "      <td>00000000</td>\n",
       "      <td>2</td>\n",
       "      <td>00</td>\n",
       "      <td>01</td>\n",
       "      <td>All employees, thousands, total nonfarm, seaso...</td>\n",
       "      <td>Total nonfarm</td>\n",
       "      <td>ALL EMPLOYEES, THOUSANDS</td>\n",
       "      <td>All employees</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>CES0000000001</td>\n",
       "      <td>2015</td>\n",
       "      <td>140910.0</td>\n",
       "      <td>00000000</td>\n",
       "      <td>3</td>\n",
       "      <td>00</td>\n",
       "      <td>01</td>\n",
       "      <td>All employees, thousands, total nonfarm, seaso...</td>\n",
       "      <td>Total nonfarm</td>\n",
       "      <td>ALL EMPLOYEES, THOUSANDS</td>\n",
       "      <td>All employees</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>CES0000000001</td>\n",
       "      <td>2015</td>\n",
       "      <td>141194.0</td>\n",
       "      <td>00000000</td>\n",
       "      <td>4</td>\n",
       "      <td>00</td>\n",
       "      <td>01</td>\n",
       "      <td>All employees, thousands, total nonfarm, seaso...</td>\n",
       "      <td>Total nonfarm</td>\n",
       "      <td>ALL EMPLOYEES, THOUSANDS</td>\n",
       "      <td>All employees</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>CES0000000001</td>\n",
       "      <td>2015</td>\n",
       "      <td>141525.0</td>\n",
       "      <td>00000000</td>\n",
       "      <td>5</td>\n",
       "      <td>00</td>\n",
       "      <td>01</td>\n",
       "      <td>All employees, thousands, total nonfarm, seaso...</td>\n",
       "      <td>Total nonfarm</td>\n",
       "      <td>ALL EMPLOYEES, THOUSANDS</td>\n",
       "      <td>All employees</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       series_id  year     value industry_code_x  Month supersector_code  \\\n",
       "0  CES0000000001  2015  140568.0        00000000      1               00   \n",
       "1  CES0000000001  2015  140839.0        00000000      2               00   \n",
       "2  CES0000000001  2015  140910.0        00000000      3               00   \n",
       "3  CES0000000001  2015  141194.0        00000000      4               00   \n",
       "4  CES0000000001  2015  141525.0        00000000      5               00   \n",
       "\n",
       "  data_type_code                                       series_title  \\\n",
       "0             01  All employees, thousands, total nonfarm, seaso...   \n",
       "1             01  All employees, thousands, total nonfarm, seaso...   \n",
       "2             01  All employees, thousands, total nonfarm, seaso...   \n",
       "3             01  All employees, thousands, total nonfarm, seaso...   \n",
       "4             01  All employees, thousands, total nonfarm, seaso...   \n",
       "\n",
       "  supersector_name            data_type_text    short_title  \n",
       "0    Total nonfarm  ALL EMPLOYEES, THOUSANDS  All employees  \n",
       "1    Total nonfarm  ALL EMPLOYEES, THOUSANDS  All employees  \n",
       "2    Total nonfarm  ALL EMPLOYEES, THOUSANDS  All employees  \n",
       "3    Total nonfarm  ALL EMPLOYEES, THOUSANDS  All employees  \n",
       "4    Total nonfarm  ALL EMPLOYEES, THOUSANDS  All employees  "
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Isolate data to series data needed  - Industry level employment data\n",
    "series = [\"CES0000000001\",\"CES0500000001\",\"CES0600000001\",\"CES0700000001\",\"CES0800000001\",\n",
    "\"CES1000000001\",\"CES2000000001\",\"CES3000000001\",\"CES3100000001\",\"CES3200000001\",\n",
    "\"CES4000000001\",\"CES4200000001\",\"CES4300000001\",\"CES5000000001\",\"CES5500000001\",\n",
    "\"CES5552000001\",\"CES5553000001\",\"CES6000000001\",\"CES6054000001\",\"CES6055000001\",\n",
    "\"CES6056000001\",\"CES6500000001\",\"CES6561000001\",\"CES6562000001\",\"CES7000000001\",\n",
    "\"CES7071000001\",\"CES7072000001\",\"CES8000000001\",\"CES9000000001\",\"CES9091000001\",\n",
    "\"CES9092000001\",\"CES9093000001\"]\n",
    "\n",
    "condition = CESDF['series_id'].isin(series)\n",
    "CESSeriesDF = CESDF[condition]\n",
    "CESSeriesDF.head(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2020-06-04T03:15:08.498468Z",
     "start_time": "2020-06-04T03:15:08.478466Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(2080, 11)"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Get rows and columns count\n",
    "CESSeriesDF.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2020-06-04T03:18:01.735681Z",
     "start_time": "2020-06-04T03:18:01.650681Z"
    }
   },
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\Users\\danjr\\Anaconda3\\lib\\site-packages\\ipykernel_launcher.py:3: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  This is separate from the ipykernel package so we can avoid doing imports until\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>year</th>\n",
       "      <th>Month</th>\n",
       "      <th>value</th>\n",
       "      <th>short_title</th>\n",
       "      <th>date</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2015</td>\n",
       "      <td>1</td>\n",
       "      <td>140568.0</td>\n",
       "      <td>All employees</td>\n",
       "      <td>2015-1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2015</td>\n",
       "      <td>2</td>\n",
       "      <td>140839.0</td>\n",
       "      <td>All employees</td>\n",
       "      <td>2015-2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2015</td>\n",
       "      <td>3</td>\n",
       "      <td>140910.0</td>\n",
       "      <td>All employees</td>\n",
       "      <td>2015-3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2015</td>\n",
       "      <td>4</td>\n",
       "      <td>141194.0</td>\n",
       "      <td>All employees</td>\n",
       "      <td>2015-4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2015</td>\n",
       "      <td>5</td>\n",
       "      <td>141525.0</td>\n",
       "      <td>All employees</td>\n",
       "      <td>2015-5</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   year  Month     value    short_title    date\n",
       "0  2015      1  140568.0  All employees  2015-1\n",
       "1  2015      2  140839.0  All employees  2015-2\n",
       "2  2015      3  140910.0  All employees  2015-3\n",
       "3  2015      4  141194.0  All employees  2015-4\n",
       "4  2015      5  141525.0  All employees  2015-5"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Choose columns and create a date column with year and month\n",
    "df = CESSeriesDF[['year','Month','value','short_title']]\n",
    "df['date']=df['year'].astype(str) + \"-\"+ df['Month'].astype(str) \n",
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\Users\\danjr\\Anaconda3\\lib\\site-packages\\ipykernel_launcher.py:34: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>year</th>\n",
       "      <th>Month</th>\n",
       "      <th>value</th>\n",
       "      <th>short_title</th>\n",
       "      <th>date</th>\n",
       "      <th>industry</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2015</td>\n",
       "      <td>1</td>\n",
       "      <td>140568.0</td>\n",
       "      <td>All employees</td>\n",
       "      <td>2015-1</td>\n",
       "      <td>All Employees</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2015</td>\n",
       "      <td>2</td>\n",
       "      <td>140839.0</td>\n",
       "      <td>All employees</td>\n",
       "      <td>2015-2</td>\n",
       "      <td>All Employees</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2015</td>\n",
       "      <td>3</td>\n",
       "      <td>140910.0</td>\n",
       "      <td>All employees</td>\n",
       "      <td>2015-3</td>\n",
       "      <td>All Employees</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2015</td>\n",
       "      <td>4</td>\n",
       "      <td>141194.0</td>\n",
       "      <td>All employees</td>\n",
       "      <td>2015-4</td>\n",
       "      <td>All Employees</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2015</td>\n",
       "      <td>5</td>\n",
       "      <td>141525.0</td>\n",
       "      <td>All employees</td>\n",
       "      <td>2015-5</td>\n",
       "      <td>All Employees</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   year  Month     value    short_title    date       industry\n",
       "0  2015      1  140568.0  All employees  2015-1  All Employees\n",
       "1  2015      2  140839.0  All employees  2015-2  All Employees\n",
       "2  2015      3  140910.0  All employees  2015-3  All Employees\n",
       "3  2015      4  141194.0  All employees  2015-4  All Employees\n",
       "4  2015      5  141525.0  All employees  2015-5  All Employees"
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Rename to more meaningful short_title categories\n",
    "df['industry']=df['short_title'].replace({\n",
    "        'All employees':'All Employees',\n",
    "        'All employees accommodation and food services':'Accomm. & Food Services',\n",
    "        'All employees administrative and waste services': 'Admin. & Waste Services',\n",
    "        'All employees arts entertainment and recreation': 'Arts Ent. & Recreation',\n",
    "        'All employees construction':'Construction',\n",
    "        'All employees durable goods': 'Durable Goods',\n",
    "        'All employees education and health services': 'Edu. & Health Services',\n",
    "        'All employees educational services':'Educational Services',\n",
    "        'All employees federal':'Federal',\n",
    "        'All employees finance and insurance':'Finance & Insurance',\n",
    "        'All employees financial activities':'Financial Activities',\n",
    "        'All employees goods-producing':'Goods Producing',\n",
    "        'All employees government':'Government',\n",
    "        'All employees health care and social assistance':'Healthcare & Social Assistance',\n",
    "        'All employees information':'Information',\n",
    "        'All employees leisure and hospitality':'Leisure & Hospitality',\n",
    "        'All employees local government':'Local Government',\n",
    "        'All employees management of companies and enterprises':'Mgmt Companies & Enterprises',\n",
    "        'All employees manufacturing':'Manufacturing',\n",
    "        'All employees mining and logging':'Mining & Logging',\n",
    "        'All employees nondurable goods':'Nondurable Goods',\n",
    "        'All employees other services':'Other Services',\n",
    "        'All employees private':'Private',\n",
    "        'All employees private service-providing':'Private Service Providing',\n",
    "        'All employees professional and business services':'Professional & Business Services',\n",
    "        'All employees professional and technical services':'Professional & Technical Services',\n",
    "        'All employees real estate and rental and leasing':'Real Estate & Rental',\n",
    "        'All employees retail trade':'Retail Trade',\n",
    "        'All employees service-providing':'Service Providing',\n",
    "        'All employees state government':'State Government',\n",
    "        'All employees trade transportation and utilities':'Trade Transportation & Utilities',\n",
    "        'All employees transportation and warehousing':'Transportation & Warehousing',\n",
    "    })\n",
    "df.head()    "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "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>date</th>\n",
       "      <th>year</th>\n",
       "      <th>month</th>\n",
       "      <th>industry</th>\n",
       "      <th>number_of_persons</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2015-1</td>\n",
       "      <td>2015</td>\n",
       "      <td>1</td>\n",
       "      <td>All Employees</td>\n",
       "      <td>140568.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2015-2</td>\n",
       "      <td>2015</td>\n",
       "      <td>2</td>\n",
       "      <td>All Employees</td>\n",
       "      <td>140839.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2015-3</td>\n",
       "      <td>2015</td>\n",
       "      <td>3</td>\n",
       "      <td>All Employees</td>\n",
       "      <td>140910.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2015-4</td>\n",
       "      <td>2015</td>\n",
       "      <td>4</td>\n",
       "      <td>All Employees</td>\n",
       "      <td>141194.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2015-5</td>\n",
       "      <td>2015</td>\n",
       "      <td>5</td>\n",
       "      <td>All Employees</td>\n",
       "      <td>141525.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     date  year  month       industry  number_of_persons\n",
       "0  2015-1  2015      1  All Employees           140568.0\n",
       "1  2015-2  2015      2  All Employees           140839.0\n",
       "2  2015-3  2015      3  All Employees           140910.0\n",
       "3  2015-4  2015      4  All Employees           141194.0\n",
       "4  2015-5  2015      5  All Employees           141525.0"
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Rename columns and drop short_tite\n",
    "df=df.rename(\n",
    "    columns = {\n",
    "        'Month':'month',\n",
    "        'value':'number_of_persons'\n",
    "        })\n",
    "\n",
    "final_df = df[['date','year','month','industry','number_of_persons']]\n",
    "final_df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Export clean data to CSV for analysis\n",
    "final_df.to_csv(output_data_file, index = False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Push to SQLite Database\n",
    "engine = create_engine('sqlite:///Resources/Industry_Data.db', echo=False)\n",
    "final_df.to_sql('industry_data',con=engine,if_exists='replace')"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "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.7.4"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}