Explore & Clean_Demographic Data.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 44,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2020-06-03T17:09:29.356735Z",
     "start_time": "2020-06-03T17:09:28.158735Z"
    }
   },
   "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>Month</th>\n",
       "      <th>lfst_code</th>\n",
       "      <th>periodicity_code</th>\n",
       "      <th>series_title</th>\n",
       "      <th>activity_code</th>\n",
       "      <th>ages_code</th>\n",
       "      <th>class_code</th>\n",
       "      <th>duration_code</th>\n",
       "      <th>education_code</th>\n",
       "      <th>entr_code</th>\n",
       "      <th>expr_code</th>\n",
       "      <th>indy_text</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>LNS11000000</td>\n",
       "      <td>2015</td>\n",
       "      <td>157030.0</td>\n",
       "      <td>1</td>\n",
       "      <td>10</td>\n",
       "      <td>M</td>\n",
       "      <td>(Seas) Civilian Labor Force Level</td>\n",
       "      <td>0</td>\n",
       "      <td>00</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>00</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>All Industries</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>LNS11000000</td>\n",
       "      <td>2015</td>\n",
       "      <td>156644.0</td>\n",
       "      <td>2</td>\n",
       "      <td>10</td>\n",
       "      <td>M</td>\n",
       "      <td>(Seas) Civilian Labor Force Level</td>\n",
       "      <td>0</td>\n",
       "      <td>00</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>00</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>All Industries</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>LNS11000000</td>\n",
       "      <td>2015</td>\n",
       "      <td>156643.0</td>\n",
       "      <td>3</td>\n",
       "      <td>10</td>\n",
       "      <td>M</td>\n",
       "      <td>(Seas) Civilian Labor Force Level</td>\n",
       "      <td>0</td>\n",
       "      <td>00</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>00</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>All Industries</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>LNS11000000</td>\n",
       "      <td>2015</td>\n",
       "      <td>157060.0</td>\n",
       "      <td>4</td>\n",
       "      <td>10</td>\n",
       "      <td>M</td>\n",
       "      <td>(Seas) Civilian Labor Force Level</td>\n",
       "      <td>0</td>\n",
       "      <td>00</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>00</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>All Industries</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>LNS11000000</td>\n",
       "      <td>2015</td>\n",
       "      <td>157651.0</td>\n",
       "      <td>5</td>\n",
       "      <td>10</td>\n",
       "      <td>M</td>\n",
       "      <td>(Seas) Civilian Labor Force Level</td>\n",
       "      <td>0</td>\n",
       "      <td>00</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>00</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>All Industries</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     series_id  year     value  Month  lfst_code periodicity_code  \\\n",
       "0  LNS11000000  2015  157030.0      1         10                M   \n",
       "1  LNS11000000  2015  156644.0      2         10                M   \n",
       "2  LNS11000000  2015  156643.0      3         10                M   \n",
       "3  LNS11000000  2015  157060.0      4         10                M   \n",
       "4  LNS11000000  2015  157651.0      5         10                M   \n",
       "\n",
       "                        series_title  activity_code ages_code  class_code  \\\n",
       "0  (Seas) Civilian Labor Force Level              0        00           0   \n",
       "1  (Seas) Civilian Labor Force Level              0        00           0   \n",
       "2  (Seas) Civilian Labor Force Level              0        00           0   \n",
       "3  (Seas) Civilian Labor Force Level              0        00           0   \n",
       "4  (Seas) Civilian Labor Force Level              0        00           0   \n",
       "\n",
       "   duration_code education_code  entr_code  expr_code       indy_text  \n",
       "0              0             00          0          0  All Industries  \n",
       "1              0             00          0          0  All Industries  \n",
       "2              0             00          0          0  All Industries  \n",
       "3              0             00          0          0  All Industries  \n",
       "4              0             00          0          0  All Industries  "
      ]
     },
     "execution_count": 44,
     "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",
    "\n",
    "# File to Load\n",
    "LNSDF=pd.read_pickle('resources/LNS2020.pkl',compression='gzip')\n",
    "\n",
    "# Preview of the DataFrame\n",
    "LNSDF.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2020-06-03T17:09:29.372736Z",
     "start_time": "2020-06-03T17:09:29.360736Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "series_id            object\n",
       "year                  int64\n",
       "value               float64\n",
       "Month                 int64\n",
       "lfst_code             int64\n",
       "periodicity_code     object\n",
       "series_title         object\n",
       "activity_code         int64\n",
       "ages_code            object\n",
       "class_code            int64\n",
       "duration_code         int64\n",
       "education_code       object\n",
       "entr_code             int64\n",
       "expr_code             int64\n",
       "indy_text            object\n",
       "dtype: object"
      ]
     },
     "execution_count": 45,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Check for data types\n",
    "LNSDF.dtypes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 46,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2020-06-03T17:09:29.387734Z",
     "start_time": "2020-06-03T17:09:29.376736Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['series_id', 'year', 'value', 'Month', 'lfst_code', 'periodicity_code',\n",
       "       'series_title', 'activity_code', 'ages_code', 'class_code',\n",
       "       'duration_code', 'education_code', 'entr_code', 'expr_code',\n",
       "       'indy_text'],\n",
       "      dtype='object')"
      ]
     },
     "execution_count": 46,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Check column names for spaces\n",
    "LNSDF.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2020-06-03T17:09:29.403737Z",
     "start_time": "2020-06-03T17:09:29.391740Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "series_id           2080\n",
       "year                2080\n",
       "value               2080\n",
       "Month               2080\n",
       "lfst_code           2080\n",
       "periodicity_code    2080\n",
       "series_title        2080\n",
       "activity_code       2080\n",
       "ages_code           2080\n",
       "class_code          2080\n",
       "duration_code       2080\n",
       "education_code      2080\n",
       "entr_code           2080\n",
       "expr_code           2080\n",
       "indy_text           2080\n",
       "dtype: int64"
      ]
     },
     "execution_count": 47,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Identify incomplete rows\n",
    "LNSDF.count()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 48,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2020-06-03T17:09:29.466734Z",
     "start_time": "2020-06-03T17:09:29.437735Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "LNS11000000    65\n",
       "LNS11000002    65\n",
       "LNS13023653    65\n",
       "LNS13008396    65\n",
       "LNS13000089    65\n",
       "LNS13027662    65\n",
       "LNS13023621    65\n",
       "LNS13008516    65\n",
       "LNS13008636    65\n",
       "LNS13008876    65\n",
       "LNS13000000    65\n",
       "LNS13024230    65\n",
       "LNS13000036    65\n",
       "LNS13000003    65\n",
       "LNS13027659    65\n",
       "LNS13000001    65\n",
       "LNS13026638    65\n",
       "LNS13026637    65\n",
       "LNS13000006    65\n",
       "LNS13000091    65\n",
       "LNS13027689    65\n",
       "LNS13000009    65\n",
       "LNS13032183    65\n",
       "LNS13023569    65\n",
       "LNS13000012    65\n",
       "LNS13025699    65\n",
       "LNS13000093    65\n",
       "LNS13027660    65\n",
       "LNS13008756    65\n",
       "LNS11000001    65\n",
       "LNS13000002    65\n",
       "LNS13023705    65\n",
       "Name: series_id, dtype: int64"
      ]
     },
     "execution_count": 48,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Check series_id values and counts\n",
    "LNSDF['series_id'].value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 49,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "['LNS11000000' 'LNS11000001' 'LNS11000002' 'LNS13000000' 'LNS13000001'\n",
      " 'LNS13000002' 'LNS13000003' 'LNS13000006' 'LNS13000009' 'LNS13000012'\n",
      " 'LNS13000036' 'LNS13000089' 'LNS13000091' 'LNS13000093' 'LNS13008396'\n",
      " 'LNS13008516' 'LNS13008636' 'LNS13008756' 'LNS13008876' 'LNS13023569'\n",
      " 'LNS13023621' 'LNS13023653' 'LNS13023705' 'LNS13024230' 'LNS13025699'\n",
      " 'LNS13026637' 'LNS13026638' 'LNS13027659' 'LNS13027660' 'LNS13027662'\n",
      " 'LNS13027689' 'LNS13032183']\n"
     ]
    }
   ],
   "source": [
    "# Isolate to series data needed  - demographic unemployment data\n",
    "series = LNSDF['series_id'].unique()\n",
    "print(series)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 50,
   "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>series_id</th>\n",
       "      <th>year</th>\n",
       "      <th>value</th>\n",
       "      <th>Month</th>\n",
       "      <th>lfst_code</th>\n",
       "      <th>periodicity_code</th>\n",
       "      <th>series_title</th>\n",
       "      <th>activity_code</th>\n",
       "      <th>ages_code</th>\n",
       "      <th>class_code</th>\n",
       "      <th>duration_code</th>\n",
       "      <th>education_code</th>\n",
       "      <th>entr_code</th>\n",
       "      <th>expr_code</th>\n",
       "      <th>indy_text</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>LNS11000000</td>\n",
       "      <td>2015</td>\n",
       "      <td>157030.0</td>\n",
       "      <td>1</td>\n",
       "      <td>10</td>\n",
       "      <td>M</td>\n",
       "      <td>(Seas) Civilian Labor Force Level</td>\n",
       "      <td>0</td>\n",
       "      <td>00</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>00</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>All Industries</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>LNS11000000</td>\n",
       "      <td>2015</td>\n",
       "      <td>156644.0</td>\n",
       "      <td>2</td>\n",
       "      <td>10</td>\n",
       "      <td>M</td>\n",
       "      <td>(Seas) Civilian Labor Force Level</td>\n",
       "      <td>0</td>\n",
       "      <td>00</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>00</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>All Industries</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>LNS11000000</td>\n",
       "      <td>2015</td>\n",
       "      <td>156643.0</td>\n",
       "      <td>3</td>\n",
       "      <td>10</td>\n",
       "      <td>M</td>\n",
       "      <td>(Seas) Civilian Labor Force Level</td>\n",
       "      <td>0</td>\n",
       "      <td>00</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>00</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>All Industries</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>LNS11000000</td>\n",
       "      <td>2015</td>\n",
       "      <td>157060.0</td>\n",
       "      <td>4</td>\n",
       "      <td>10</td>\n",
       "      <td>M</td>\n",
       "      <td>(Seas) Civilian Labor Force Level</td>\n",
       "      <td>0</td>\n",
       "      <td>00</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>00</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>All Industries</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>LNS11000000</td>\n",
       "      <td>2015</td>\n",
       "      <td>157651.0</td>\n",
       "      <td>5</td>\n",
       "      <td>10</td>\n",
       "      <td>M</td>\n",
       "      <td>(Seas) Civilian Labor Force Level</td>\n",
       "      <td>0</td>\n",
       "      <td>00</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>00</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>All Industries</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     series_id  year     value  Month  lfst_code periodicity_code  \\\n",
       "0  LNS11000000  2015  157030.0      1         10                M   \n",
       "1  LNS11000000  2015  156644.0      2         10                M   \n",
       "2  LNS11000000  2015  156643.0      3         10                M   \n",
       "3  LNS11000000  2015  157060.0      4         10                M   \n",
       "4  LNS11000000  2015  157651.0      5         10                M   \n",
       "\n",
       "                        series_title  activity_code ages_code  class_code  \\\n",
       "0  (Seas) Civilian Labor Force Level              0        00           0   \n",
       "1  (Seas) Civilian Labor Force Level              0        00           0   \n",
       "2  (Seas) Civilian Labor Force Level              0        00           0   \n",
       "3  (Seas) Civilian Labor Force Level              0        00           0   \n",
       "4  (Seas) Civilian Labor Force Level              0        00           0   \n",
       "\n",
       "   duration_code education_code  entr_code  expr_code       indy_text  \n",
       "0              0             00          0          0  All Industries  \n",
       "1              0             00          0          0  All Industries  \n",
       "2              0             00          0          0  All Industries  \n",
       "3              0             00          0          0  All Industries  \n",
       "4              0             00          0          0  All Industries  "
      ]
     },
     "execution_count": 50,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Create a new dataframe with filtered series IDs\n",
    "condition = LNSDF['series_id'].isin(series)\n",
    "LNSSeriesDF = LNSDF[condition]\n",
    "LNSSeriesDF.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 51,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\Users\\danjr\\Anaconda3\\lib\\site-packages\\ipykernel_launcher.py:5: 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",
      "  \"\"\"\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>series_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>157030.0</td>\n",
       "      <td>(Seas) Civilian Labor Force Level</td>\n",
       "      <td>2015-1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2015</td>\n",
       "      <td>2</td>\n",
       "      <td>156644.0</td>\n",
       "      <td>(Seas) Civilian Labor Force Level</td>\n",
       "      <td>2015-2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2015</td>\n",
       "      <td>3</td>\n",
       "      <td>156643.0</td>\n",
       "      <td>(Seas) Civilian Labor Force Level</td>\n",
       "      <td>2015-3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2015</td>\n",
       "      <td>4</td>\n",
       "      <td>157060.0</td>\n",
       "      <td>(Seas) Civilian Labor Force Level</td>\n",
       "      <td>2015-4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2015</td>\n",
       "      <td>5</td>\n",
       "      <td>157651.0</td>\n",
       "      <td>(Seas) Civilian Labor Force Level</td>\n",
       "      <td>2015-5</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   year  Month     value                       series_title    date\n",
       "0  2015      1  157030.0  (Seas) Civilian Labor Force Level  2015-1\n",
       "1  2015      2  156644.0  (Seas) Civilian Labor Force Level  2015-2\n",
       "2  2015      3  156643.0  (Seas) Civilian Labor Force Level  2015-3\n",
       "3  2015      4  157060.0  (Seas) Civilian Labor Force Level  2015-4\n",
       "4  2015      5  157651.0  (Seas) Civilian Labor Force Level  2015-5"
      ]
     },
     "execution_count": 51,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# New df to isolate columns needed\n",
    "unemp_df = LNSSeriesDF[['year','Month','value','series_title']]\n",
    "\n",
    "#Cast year and month as strings\n",
    "unemp_df['date']=unemp_df['year'].astype(str) + \"-\"+ unemp_df['Month'].astype(str) \n",
    "unemp_df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "unemp_df = pd.pivot_table(unemp_df,index=[\"date\"],values=[\"value\"],columns=[\"series_title\"])\n",
    "unemp_df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "unemp_df.reset_index(inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 52,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\Users\\danjr\\Anaconda3\\lib\\site-packages\\pandas\\core\\frame.py:4133: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame\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",
      "  errors=errors,\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>number_of_persons</th>\n",
       "      <th>category</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>157030.0</td>\n",
       "      <td>(Seas) Civilian Labor Force Level</td>\n",
       "      <td>2015-1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2015</td>\n",
       "      <td>2</td>\n",
       "      <td>156644.0</td>\n",
       "      <td>(Seas) Civilian Labor Force Level</td>\n",
       "      <td>2015-2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2015</td>\n",
       "      <td>3</td>\n",
       "      <td>156643.0</td>\n",
       "      <td>(Seas) Civilian Labor Force Level</td>\n",
       "      <td>2015-3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2015</td>\n",
       "      <td>4</td>\n",
       "      <td>157060.0</td>\n",
       "      <td>(Seas) Civilian Labor Force Level</td>\n",
       "      <td>2015-4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2015</td>\n",
       "      <td>5</td>\n",
       "      <td>157651.0</td>\n",
       "      <td>(Seas) Civilian Labor Force Level</td>\n",
       "      <td>2015-5</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   year  month  number_of_persons                           category    date\n",
       "0  2015      1           157030.0  (Seas) Civilian Labor Force Level  2015-1\n",
       "1  2015      2           156644.0  (Seas) Civilian Labor Force Level  2015-2\n",
       "2  2015      3           156643.0  (Seas) Civilian Labor Force Level  2015-3\n",
       "3  2015      4           157060.0  (Seas) Civilian Labor Force Level  2015-4\n",
       "4  2015      5           157651.0  (Seas) Civilian Labor Force Level  2015-5"
      ]
     },
     "execution_count": 52,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Rename columns\n",
    "unemp_df.rename(columns={'Month':'month','value':'number_of_persons','series_title':'category'}, inplace=True)\n",
    "unemp_df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 53,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "['(Seas) Civilian Labor Force Level'\n",
      " '(Seas) Civilian Labor Force Level - Men'\n",
      " '(Seas) Civilian Labor Force Level - Women' '(Seas) Unemployment Level'\n",
      " '(Seas) Unemployment Level - Men' '(Seas) Unemployment Level - Women'\n",
      " '(Seas) Unemployment Level - White'\n",
      " '(Seas) Unemployment Level - Black or African American'\n",
      " '(Seas) Unemployment Level - Hispanic or Latino'\n",
      " '(Seas) Unemployment Level - 16-19 yrs.'\n",
      " '(Seas) Unemployment Level - 20-24 yrs.'\n",
      " '(Seas) Unemployment Level - 25-34 yrs.'\n",
      " '(Seas) Unemployment Level - 35-44 yrs.'\n",
      " '(Seas) Unemployment Level - 45-54 yrs.'\n",
      " '(Seas) Number Unemployed for Less than 5 Weeks'\n",
      " '(Seas) Number Unemployed for 15 Weeks & over'\n",
      " '(Seas) Number Unemployed for 27 Weeks & over'\n",
      " '(Seas) Number Unemployed for 5-14 Weeks'\n",
      " '(Seas) Number Unemployed for 15-26 Weeks'\n",
      " '(Seas) Unemployment Level - New Entrants'\n",
      " '(Seas) Unemployment Level - Job Losers'\n",
      " '(Seas) Unemployment Level - Job Losers on Layoff'\n",
      " '(Seas) Unemployment Level - Job Leavers'\n",
      " '(Seas) Unemployment Level - 55 yrs. & over'\n",
      " '(Seas) Unemployment Level - Job Losers Not on Layoff'\n",
      " '(Seas) Unemployment Level - Persons who Completed Temporary Jobs'\n",
      " '(Seas) Unemployment Level - Permanent Job Losers'\n",
      " '(Seas) Unemployment Level - Less than a High School Diploma, 25 yrs. & over'\n",
      " '(Seas) Unemployment Level - High School Graduates, No College, 25 yrs. & over'\n",
      " \"(Seas) Unemployment Level - Bachelor's degree and higher, 25 yrs. & over\"\n",
      " '(Seas) Unemployment Level - Some College or Associate Degree, 25 yrs. & over'\n",
      " '(Seas) Unemployment Level - Asian']\n"
     ]
    }
   ],
   "source": [
    "# View categories for renaming\n",
    "print(unemp_df['category'].unique())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 56,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Rename to more meaningful and shorter column names/categories\n",
    "unemp_df['category'].replace({\"(Seas) Civilian Labor Force Level\":\"Labor Force Total\",\n",
    "                                  \"(Seas) Civilian Labor Force Level - Men\":\"Labor Force Men\",\n",
    "                                  \"(Seas) Civilian Labor Force Level - Women\":\"Labor Force Women\",\n",
    "                                  \"(Seas) Unemployment Level\":\"Unemployment Total\",\n",
    "                                  \"(Seas) Unemployment Level - Men\":\"Men\",\n",
    "                                  \"(Seas) Unemployment Level - Women\":\"Women\",\n",
    "                                  \"(Seas) Unemployment Level - White\":\"Caucasian\",\n",
    "                                  \"(Seas) Unemployment Level - Black or African American\":\"African American\",\n",
    "                                  \"(Seas) Unemployment Level - Hispanic or Latino\":\"Hispanic\",\n",
    "                                  \"(Seas) Unemployment Level - Asian\":\"Asian\",\n",
    "                                  \"(Seas) Unemployment Level - 16-19 yrs.\":\"16-19\",\n",
    "                                  \"(Seas) Unemployment Level - 20-24 yrs.\":\"20-24\",\n",
    "                                  \"(Seas) Unemployment Level - 25-34 yrs.\":\"25-34\",\n",
    "                                  \"(Seas) Unemployment Level - 35-44 yrs.\":\"35-44\",\n",
    "                                  \"(Seas) Unemployment Level - 45-54 yrs.\":\"45-54\",\n",
    "                                  \"(Seas) Unemployment Level - New Entrants\":\"New Entrants\",\n",
    "                                  \"(Seas) Unemployment Level - Job Losers\":\"Job Losers\",\n",
    "                                  \"(Seas) Unemployment Level - Job Losers on Layoff\":\"Job Losers on Layoff\",\n",
    "                                  \"(Seas) Unemployment Level - Job Leavers\":\"Job Leavers\",\n",
    "                                  \"(Seas) Unemployment Level - Job Losers Not on Layoff\":\"Job Losers not on Layoff\",\n",
    "                                  \"(Seas) Unemployment Level - Persons who Completed Temporary Jobs\":\"Persons Completed Temp. Jobs\",\n",
    "                                  \"(Seas) Unemployment Level - Permanent Job Losers\":\"Permanent Job Losers\",\n",
    "                                  \"(Seas) Number Unemployed for Less than 5 Weeks\":\"Unemployed Less than 5 Weeks\",\n",
    "                                  \"(Seas) Number Unemployed for 5-14 Weeks\":\"Unemployed 5-14 Weeks\",\n",
    "                                  \"(Seas) Number Unemployed for 15 Weeks & over\":\"Unemployed 15 Weeks & over\",\n",
    "                                  \"(Seas) Number Unemployed for 15-26 Weeks\":\"Unemployed 15-26 Weeks\",\n",
    "                                  \"(Seas) Number Unemployed for 27 Weeks & over\":\"Unemployed 27 Weeks & over\",\n",
    "                                  \"(Seas) Unemployment Level - 55 yrs. & over\":\"55 & Over\",\n",
    "                                  \"(Seas) Unemployment Level - Less than a High School Diploma, 25 yrs. & over\":\"Less than HS Diploma\",\n",
    "                                  \"(Seas) Unemployment Level - High School Graduates, No College, 25 yrs. & over\":\"HS no College\",\n",
    "                                  \"(Seas) Unemployment Level - Some College or Associate Degree, 25 yrs. & over\":\"Some College or Associate Degree\",\n",
    "                                  \"(Seas) Unemployment Level - Bachelor's degree and higher, 25 yrs. & over\":\"Bachelors degree and higher\"\n",
    "                                 },inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 57,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Women                               65\n",
       "20-24                               65\n",
       "Hispanic                            65\n",
       "55 & Over                           65\n",
       "Unemployed 27 Weeks & over          65\n",
       "Job Losers on Layoff                65\n",
       "Unemployed 5-14 Weeks               65\n",
       "African American                    65\n",
       "25-34                               65\n",
       "Some College or Associate Degree    65\n",
       "HS No College                       65\n",
       "Asian                               65\n",
       "Labor Force Total                   65\n",
       "Labor Force Men                     65\n",
       "Job Losers                          65\n",
       "Unemployed 15-26 Weeks              65\n",
       "Persons Completed Temp. Jobs        65\n",
       "Job Leavers                         65\n",
       "Less than HS Diploma                65\n",
       "New Entrants                        65\n",
       "Unemployed Less than 5 Weeks        65\n",
       "35-44                               65\n",
       "Caucasian                           65\n",
       "Permanent Job Losers                65\n",
       "16-19                               65\n",
       "Unemployed 15 Weeks & over          65\n",
       "Job Losers not on Layoff            65\n",
       "45-54                               65\n",
       "Bachelor's degree and higher        65\n",
       "Men                                 65\n",
       "Unemployment Total                  65\n",
       "Labor Force Women                   65\n",
       "Name: category, dtype: int64"
      ]
     },
     "execution_count": 57,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Verify updated categories\n",
    "unemp_df['category'].value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 59,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Export clean data to CSV for analysis\n",
    "output_file = \"Resources/Demographics_Data.csv\"\n",
    "unemp_df.to_csv(output_file, index=False)"
   ]
  }
 ],
 "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
}