bls_single_file.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\ProgramData\\Anaconda3\\lib\\site-packages\\linearmodels\\panel\\data.py:10: FutureWarning: The Panel class is removed from pandas. Accessing it from the top-level namespace will also be removed in the next version\n",
      "  from pandas import (Categorical, DataFrame, Index, MultiIndex, Panel, Series,\n"
     ]
    }
   ],
   "source": [
    "import pandas as pd             # data package\n",
    "import matplotlib.pyplot as plt # graphics \n",
    "import datetime as dt\n",
    "import numpy as np\n",
    "\n",
    "import requests, io             # internet and input tools  \n",
    "import zipfile as zf            # zip file tools \n",
    "import os  \n",
    "\n",
    "import pyarrow as pa\n",
    "import pyarrow.parquet as pq\n",
    "\n",
    "import statsmodels.formula.api as smf\n",
    "from linearmodels.iv import IV2SLS\n",
    "from linearmodels.panel import PanelOLS\n",
    "\n",
    "from census import Census # This is new..."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<function os.get_exec_path(env=None)>"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "os.get_exec_path"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "cwd = os.getcwd()\n",
    "\n",
    "trade_data = pq.read_table(cwd+ \"\\\\data\\\\total_trade_data_2015.parquet\").to_pandas()\n",
    "\n",
    "trade_data[\"time\"] = pd.to_datetime(trade_data.time)\n",
    "\n",
    "trade_data.set_index([\"area_fips\", \"time\"],inplace = True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "6.4691811550724125"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "trade_data.head()\n",
    "\n",
    "exposure = pd.qcut(trade_data.xs('2018-12-1', level=1).tariff, 4 ,labels = False)\n",
    "\n",
    "most_exposed = exposure[exposure == 3].index.tolist()\n",
    "\n",
    "trade_data.loc[most_exposed].xs('2018-12-1', level=1).tariff.mean()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "years = [16,17,18,19]\n",
    "\n",
    "empl_time_dict = {}\n",
    "\n",
    "foo = 0\n",
    "\n",
    "for xxx in years:\n",
    "    \n",
    "    \n",
    "    year = int(\"20\" + str(xxx))\n",
    "    \n",
    "    \n",
    "    empl_time_dict[\"year_{0}\".format(year)] = {\"1_month1_emplvl\":dt.datetime(year,1,1),\n",
    "                 \"1_month2_emplvl\":dt.datetime(year,2,1),\n",
    "                 \"1_month3_emplvl\":dt.datetime(year,3,1),\n",
    "                 \"2_month1_emplvl\":dt.datetime(year,4,1),\n",
    "                 \"2_month2_emplvl\":dt.datetime(year,5,1),\n",
    "                 \"2_month3_emplvl\":dt.datetime(year,6,1),\n",
    "                 \"3_month1_emplvl\":dt.datetime(year,7,1),\n",
    "                 \"3_month2_emplvl\":dt.datetime(year,8,1),\n",
    "                 \"3_month3_emplvl\":dt.datetime(year,9,1),\n",
    "                 \"4_month1_emplvl\":dt.datetime(year,10,1),\n",
    "                 \"4_month2_emplvl\":dt.datetime(year,11,1),\n",
    "                 \"4_month3_emplvl\":dt.datetime(year,12,1),}"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "print(\"\")\n",
    "print(\"**********************************************************************************\")\n",
    "print(\"Downloading and processing 2019 BLS file\")\n",
    "print(\"\")\n",
    "\n",
    "url = \"https://data.bls.gov/cew/data/files/2019/csv/2019_qtrly_singlefile.zip\"\n",
    "# This will read in the annual, single file. It's big, but has all we want...\n",
    "\n",
    "r = requests.get(url) \n",
    "\n",
    "# convert bytes to zip file  \n",
    "bls_q2019 = zf.ZipFile(io.BytesIO(r.content)) \n",
    "bls_q2019.extractall(cwd + \"\\\\bls_single_files\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 68,
   "metadata": {},
   "outputs": [],
   "source": [
    "print(\"\")\n",
    "print(\"**********************************************************************************\")\n",
    "print(\"Downloading and processing 2018 BLS file\")\n",
    "print(\"\")\n",
    "\n",
    "url = \"https://data.bls.gov/cew/data/files/2018/csv/2018_qtrly_singlefile.zip\"\n",
    "# This will read in the annual, single file. It's big, but has all we want...\n",
    "\n",
    "r = requests.get(url) \n",
    "\n",
    "# convert bytes to zip file  \n",
    "bls_q2018 = zf.ZipFile(io.BytesIO(r.content)) \n",
    "bls_q2018.extractall(cwd + \"\\\\bls_single_files\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 177,
   "metadata": {},
   "outputs": [],
   "source": [
    "print(\"\")\n",
    "print(\"**********************************************************************************\")\n",
    "print(\"Downloading and processing 2017 BLS file\")\n",
    "print(\"\")\n",
    "\n",
    "url = \"https://data.bls.gov/cew/data/files/2017/csv/2017_qtrly_singlefile.zip\"\n",
    "# This will read in the annual, single file. It's big, but has all we want...\n",
    "\n",
    "r = requests.get(url) \n",
    "\n",
    "# convert bytes to zip file  \n",
    "bls_q2017 = zf.ZipFile(io.BytesIO(r.content)) \n",
    "bls_q2017.extractall(cwd + \"\\\\bls_single_files\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 180,
   "metadata": {},
   "outputs": [],
   "source": [
    "print(\"\")\n",
    "print(\"**********************************************************************************\")\n",
    "print(\"Downloading and processing 2016 BLS file\")\n",
    "print(\"\")\n",
    "\n",
    "url = \"https://data.bls.gov/cew/data/files/2016/csv/2016_qtrly_singlefile.zip\"\n",
    "# This will read in the annual, single file. It's big, but has all we want...\n",
    "\n",
    "r = requests.get(url) \n",
    "\n",
    "# convert bytes to zip file  \n",
    "bls_q2016 = zf.ZipFile(io.BytesIO(r.content)) \n",
    "bls_q2016.extractall(cwd + \"\\\\bls_single_files\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [],
   "source": [
    "def bls_quarter_cat(df,agg_cat,cat,qrt,time_dict,var_name):\n",
    "    \n",
    "    # Take only private\n",
    "    df = df[df[\"qtr\"] == int(qrt)] \n",
    "    \n",
    "    df = df[df[\"own_code\"] == 5] \n",
    "\n",
    "# Take aggregate\n",
    "\n",
    "    df = df[df[\"agglvl_code\"] == agg_cat] \n",
    "    \n",
    "    df = df[df[\"industry_code\"] == cat] # Take all employment in all sectors\n",
    "    \n",
    "    rename_months = {\"month1_emplvl\": str(qrt) + \"_month1_emplvl\",\n",
    "             \"month2_emplvl\": str(qrt) + \"_month2_emplvl\",\n",
    "             \"month3_emplvl\": str(qrt) + \"_month3_emplvl\"}\n",
    "    \n",
    "    df.rename(rename_months, axis = 1, inplace = True)\n",
    "\n",
    "# Take only counties \n",
    "    \n",
    "    df.rename({\"area_fips\": \"GEOFIPS\"},axis = 1, inplace = True)\n",
    "\n",
    "    df[\"GEOFIPS\"] = df[\"GEOFIPS\"].astype(int)\n",
    "\n",
    "    df.set_index(\"GEOFIPS\", inplace = True)\n",
    "\n",
    "    df = df.reindex(trade_data.index.get_level_values(0).unique().astype(int).tolist())\n",
    "    # this part makes sure that we are always getting same counties as in the trade data set\n",
    "    # if we don't do this, things are floating around. The issue I think is for some of the really\n",
    "    # small counties, the employment in the catagory is withehld, so when we grab this stuff,\n",
    "    # the county goes missing\n",
    "\n",
    "    df = df.iloc[:,[4,5,6]].reset_index()\n",
    "    # This grabs only values we want, i.e. the employment for that quarter. So for example,\n",
    "    # in Q1, 13 = January, 14 = Febuary, 15 = March. And so forth for Q2...\n",
    "\n",
    "    df = df.melt(\"GEOFIPS\")\n",
    "\n",
    "    df.replace(time_dict,inplace = True)\n",
    "\n",
    "    df.rename({\"variable\":\"time\", \"value\": var_name, \"GEOFIPS\": \"area_fips\"}, axis = 1, inplace = True)\n",
    "    \n",
    "    df[\"area_fips\"] = df[\"area_fips\"].astype(str)\n",
    "    \n",
    "    df.set_index([\"area_fips\", \"time\"], inplace = True)\n",
    "    \n",
    "    #df.sort_values([\"area_fips\", \"time\"], inplace = True)\n",
    "    \n",
    "    return df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [],
   "source": [
    "def clean_bls_quarter(code,agg_cat,var_name):\n",
    "\n",
    "############################################################################  \n",
    "#code = \"44-45\"\n",
    "#agg_cat = 74\n",
    "#71 and 10 gets you total\n",
    "# 72 and 101 and 102 gets you goods, no goods\n",
    "# 74 and 44-45 gets you retail trade\n",
    "\n",
    "# Need an approach to get all the stuff in one file. \n",
    "\n",
    "    df = pd.DataFrame([])\n",
    "    quarter = [\"1\",\"2\",\"3\",\"4\"]\n",
    "\n",
    "############################################################################\n",
    "\n",
    "    #file_name = \"C:\\\\github\\\\consumption_and_tradewar\\\\sandbox\\\\bls_single_files\\\\2016.q1-q4.singlefile.csv\"\n",
    "    file_name = cwd + \"\\\\bls_single_files\\\\2016.q1-q4.singlefile.csv\"\n",
    "\n",
    "    df_year = pd.read_csv(file_name, usecols= [\"area_fips\",\"own_code\",\"industry_code\",\"agglvl_code\",\n",
    "                                     'month1_emplvl','month2_emplvl', 'month3_emplvl',\"qtr\"])\n",
    "\n",
    "    for item in quarter:\n",
    "        \n",
    "        df = df.append(bls_quarter_cat(df_year,agg_cat, code , item, empl_time_dict[\"year_2016\"], var_name))\n",
    "\n",
    "############################################################################\n",
    "\n",
    "    #file_name = \"C:\\\\github\\\\consumption_and_tradewar\\\\sandbox\\\\bls_single_files\\\\2017.q1-q4.singlefile.csv\"\n",
    "    file_name = cwd + \"\\\\bls_single_files\\\\2017.q1-q4.singlefile.csv\"\n",
    "    \n",
    "    df_year = pd.read_csv(file_name, usecols= [\"area_fips\",\"own_code\",\"industry_code\",\"agglvl_code\",\n",
    "                                     'month1_emplvl','month2_emplvl', 'month3_emplvl',\"qtr\"])\n",
    "\n",
    "    for item in quarter:\n",
    "        \n",
    "        df = df.append(bls_quarter_cat(df_year,agg_cat, code , item, empl_time_dict[\"year_2017\"], var_name))\n",
    "\n",
    "\n",
    "############################################################################\n",
    "\n",
    "    #file_name = \"C:\\\\github\\\\consumption_and_tradewar\\\\sandbox\\\\bls_single_files\\\\2018.q1-q4.singlefile.csv\"\n",
    "    file_name = cwd + \"\\\\bls_single_files\\\\2018.q1-q4.singlefile.csv\"\n",
    "    \n",
    "    df_year = pd.read_csv(file_name, usecols= [\"area_fips\",\"own_code\",\"industry_code\",\"agglvl_code\",\n",
    "                                     'month1_emplvl','month2_emplvl', 'month3_emplvl',\"qtr\"])\n",
    "\n",
    "    for item in quarter:\n",
    "        \n",
    "        df = df.append(bls_quarter_cat(df_year,agg_cat, code , item, empl_time_dict[\"year_2018\"], var_name))\n",
    "    \n",
    "############################################################################\n",
    "\n",
    "    #file_name = \"C:\\\\github\\\\consumption_and_tradewar\\\\sandbox\\\\bls_single_files\\\\2019.q1-q2.singlefile.csv\"\n",
    "    file_name = cwd + \"\\\\bls_single_files\\\\2019.q1-q2.singlefile.csv\"\n",
    "    \n",
    "    df_year = pd.read_csv(file_name, usecols= [\"area_fips\",\"own_code\",\"industry_code\",\"agglvl_code\",\n",
    "                                     'month1_emplvl','month2_emplvl', 'month3_emplvl',\"qtr\"])\n",
    "    \n",
    "    quarter = [\"1\",\"2\"]\n",
    "\n",
    "    for item in quarter:\n",
    "    \n",
    "        df = df.append(bls_quarter_cat(df_year,agg_cat, code , item, empl_time_dict[\"year_2019\"], var_name))\n",
    "        \n",
    "    df.sort_values([\"area_fips\", \"time\"], inplace = True)\n",
    "    \n",
    "    return df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\ProgramData\\Anaconda3\\lib\\site-packages\\IPython\\core\\interactiveshell.py:3249: DtypeWarning: Columns (0) have mixed types. Specify dtype option on import or set low_memory=False.\n",
      "  if (await self.run_code(code, result,  async_=asy)):\n"
     ]
    }
   ],
   "source": [
    "print(\"\")\n",
    "print(\"**********************************************************************************\")\n",
    "print(\"Constructing Employment Series\")\n",
    "print(\"\")\n",
    "\n",
    "code = \"44-45\"\n",
    "agg_cat = 74\n",
    "\n",
    "df_rtl = clean_bls_quarter(code,agg_cat,\"emp_rtl\")\n",
    "\n",
    "#####################################################################33\n",
    "#71 and 10 gets you total\n",
    "\n",
    "code = \"10\"\n",
    "agg_cat = 71\n",
    "\n",
    "df_all = clean_bls_quarter(code,agg_cat,\"emp_all\")\n",
    "\n",
    "#####################################################################33\n",
    "#71 and 10 gets you total\n",
    "\n",
    "code = \"101\"\n",
    "agg_cat = 72\n",
    "\n",
    "df_gds = clean_bls_quarter(code,agg_cat,\"emp_gds\")\n",
    "\n",
    "#####################################################################33\n",
    "#71 and 10 gets you total\n",
    "\n",
    "code = \"102\"\n",
    "agg_cat = 72\n",
    "\n",
    "df_ngds = clean_bls_quarter(code,agg_cat,\"emp_ngds\")\n",
    "\n",
    "########################################################################\n",
    "\n",
    "df = df_rtl.merge(df_all, left_index = True, right_index = True)\n",
    "\n",
    "df = df.merge(df_gds, left_index = True, right_index = True)\n",
    "\n",
    "df = df.merge(df_ngds, left_index = True, right_index = True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "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></th>\n",
       "      <th>emp_rtl</th>\n",
       "      <th>emp_all</th>\n",
       "      <th>emp_gds</th>\n",
       "      <th>emp_ngds</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>area_fips</th>\n",
       "      <th>time</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td rowspan=\"42\" valign=\"top\">10001</td>\n",
       "      <td>2016-01-01</td>\n",
       "      <td>9269.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>38494.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2016-02-01</td>\n",
       "      <td>9236.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>38646.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2016-03-01</td>\n",
       "      <td>9342.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>38917.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2016-04-01</td>\n",
       "      <td>9376.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>39719.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2016-05-01</td>\n",
       "      <td>9265.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>40164.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2016-06-01</td>\n",
       "      <td>9345.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>39656.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2016-07-01</td>\n",
       "      <td>9441.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>39773.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2016-08-01</td>\n",
       "      <td>9495.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>39746.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2016-09-01</td>\n",
       "      <td>9372.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>39881.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2016-10-01</td>\n",
       "      <td>9509.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>40038.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2016-11-01</td>\n",
       "      <td>9859.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>40254.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2016-12-01</td>\n",
       "      <td>9762.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>40220.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2017-01-01</td>\n",
       "      <td>9463.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>39246.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2017-02-01</td>\n",
       "      <td>9458.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>39490.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2017-03-01</td>\n",
       "      <td>9453.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>39598.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2017-04-01</td>\n",
       "      <td>9482.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>39804.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2017-05-01</td>\n",
       "      <td>9498.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>39950.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2017-06-01</td>\n",
       "      <td>9519.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>40318.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2017-07-01</td>\n",
       "      <td>9410.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>39670.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2017-08-01</td>\n",
       "      <td>9403.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>39467.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2017-09-01</td>\n",
       "      <td>9496.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>40023.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2017-10-01</td>\n",
       "      <td>9441.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>39791.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2017-11-01</td>\n",
       "      <td>9927.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>40512.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2017-12-01</td>\n",
       "      <td>9858.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>40292.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2018-01-01</td>\n",
       "      <td>9336.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>39580.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2018-02-01</td>\n",
       "      <td>9248.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>39479.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2018-03-01</td>\n",
       "      <td>9341.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>39829.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2018-04-01</td>\n",
       "      <td>9402.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>40631.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2018-05-01</td>\n",
       "      <td>9489.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>41179.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2018-06-01</td>\n",
       "      <td>9486.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>41147.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2018-07-01</td>\n",
       "      <td>9348.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>39952.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2018-08-01</td>\n",
       "      <td>9314.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>39830.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2018-09-01</td>\n",
       "      <td>9240.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>40133.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2018-10-01</td>\n",
       "      <td>9310.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>40562.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2018-11-01</td>\n",
       "      <td>9557.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>40809.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2018-12-01</td>\n",
       "      <td>9529.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>40748.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2019-01-01</td>\n",
       "      <td>9287.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>40347.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2019-02-01</td>\n",
       "      <td>9225.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>40337.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2019-03-01</td>\n",
       "      <td>9200.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>40576.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2019-04-01</td>\n",
       "      <td>9195.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>41241.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2019-05-01</td>\n",
       "      <td>9238.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>41666.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2019-06-01</td>\n",
       "      <td>9336.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>41439.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td rowspan=\"8\" valign=\"top\">10003</td>\n",
       "      <td>2016-01-01</td>\n",
       "      <td>30792.0</td>\n",
       "      <td>247839.0</td>\n",
       "      <td>23622.0</td>\n",
       "      <td>224217.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2016-02-01</td>\n",
       "      <td>30470.0</td>\n",
       "      <td>246561.0</td>\n",
       "      <td>23481.0</td>\n",
       "      <td>223080.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2016-03-01</td>\n",
       "      <td>30792.0</td>\n",
       "      <td>248144.0</td>\n",
       "      <td>24015.0</td>\n",
       "      <td>224129.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2016-04-01</td>\n",
       "      <td>31029.0</td>\n",
       "      <td>250501.0</td>\n",
       "      <td>24094.0</td>\n",
       "      <td>226407.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2016-05-01</td>\n",
       "      <td>31213.0</td>\n",
       "      <td>251305.0</td>\n",
       "      <td>24257.0</td>\n",
       "      <td>227048.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2016-06-01</td>\n",
       "      <td>31276.0</td>\n",
       "      <td>252955.0</td>\n",
       "      <td>24598.0</td>\n",
       "      <td>228357.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2016-07-01</td>\n",
       "      <td>31306.0</td>\n",
       "      <td>253356.0</td>\n",
       "      <td>25067.0</td>\n",
       "      <td>228289.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2016-08-01</td>\n",
       "      <td>31358.0</td>\n",
       "      <td>252479.0</td>\n",
       "      <td>25191.0</td>\n",
       "      <td>227288.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                      emp_rtl   emp_all  emp_gds  emp_ngds\n",
       "area_fips time                                            \n",
       "10001     2016-01-01   9269.0       0.0      0.0   38494.0\n",
       "          2016-02-01   9236.0       0.0      0.0   38646.0\n",
       "          2016-03-01   9342.0       0.0      0.0   38917.0\n",
       "          2016-04-01   9376.0       0.0      0.0   39719.0\n",
       "          2016-05-01   9265.0       0.0      0.0   40164.0\n",
       "          2016-06-01   9345.0       0.0      0.0   39656.0\n",
       "          2016-07-01   9441.0       0.0      0.0   39773.0\n",
       "          2016-08-01   9495.0       0.0      0.0   39746.0\n",
       "          2016-09-01   9372.0       0.0      0.0   39881.0\n",
       "          2016-10-01   9509.0       0.0      0.0   40038.0\n",
       "          2016-11-01   9859.0       0.0      0.0   40254.0\n",
       "          2016-12-01   9762.0       0.0      0.0   40220.0\n",
       "          2017-01-01   9463.0       0.0      0.0   39246.0\n",
       "          2017-02-01   9458.0       0.0      0.0   39490.0\n",
       "          2017-03-01   9453.0       0.0      0.0   39598.0\n",
       "          2017-04-01   9482.0       0.0      0.0   39804.0\n",
       "          2017-05-01   9498.0       0.0      0.0   39950.0\n",
       "          2017-06-01   9519.0       0.0      0.0   40318.0\n",
       "          2017-07-01   9410.0       0.0      0.0   39670.0\n",
       "          2017-08-01   9403.0       0.0      0.0   39467.0\n",
       "          2017-09-01   9496.0       0.0      0.0   40023.0\n",
       "          2017-10-01   9441.0       0.0      0.0   39791.0\n",
       "          2017-11-01   9927.0       0.0      0.0   40512.0\n",
       "          2017-12-01   9858.0       0.0      0.0   40292.0\n",
       "          2018-01-01   9336.0       0.0      0.0   39580.0\n",
       "          2018-02-01   9248.0       0.0      0.0   39479.0\n",
       "          2018-03-01   9341.0       0.0      0.0   39829.0\n",
       "          2018-04-01   9402.0       0.0      0.0   40631.0\n",
       "          2018-05-01   9489.0       0.0      0.0   41179.0\n",
       "          2018-06-01   9486.0       0.0      0.0   41147.0\n",
       "          2018-07-01   9348.0       0.0      0.0   39952.0\n",
       "          2018-08-01   9314.0       0.0      0.0   39830.0\n",
       "          2018-09-01   9240.0       0.0      0.0   40133.0\n",
       "          2018-10-01   9310.0       0.0      0.0   40562.0\n",
       "          2018-11-01   9557.0       0.0      0.0   40809.0\n",
       "          2018-12-01   9529.0       0.0      0.0   40748.0\n",
       "          2019-01-01   9287.0       0.0      0.0   40347.0\n",
       "          2019-02-01   9225.0       0.0      0.0   40337.0\n",
       "          2019-03-01   9200.0       0.0      0.0   40576.0\n",
       "          2019-04-01   9195.0       0.0      0.0   41241.0\n",
       "          2019-05-01   9238.0       0.0      0.0   41666.0\n",
       "          2019-06-01   9336.0       0.0      0.0   41439.0\n",
       "10003     2016-01-01  30792.0  247839.0  23622.0  224217.0\n",
       "          2016-02-01  30470.0  246561.0  23481.0  223080.0\n",
       "          2016-03-01  30792.0  248144.0  24015.0  224129.0\n",
       "          2016-04-01  31029.0  250501.0  24094.0  226407.0\n",
       "          2016-05-01  31213.0  251305.0  24257.0  227048.0\n",
       "          2016-06-01  31276.0  252955.0  24598.0  228357.0\n",
       "          2016-07-01  31306.0  253356.0  25067.0  228289.0\n",
       "          2016-08-01  31358.0  252479.0  25191.0  227288.0"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.head(50)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [],
   "source": [
    "trade_employ = trade_data.merge(df, left_index = True, right_index = True, how = \"right\")\n",
    "# This is a place to be mindfull about time period, if we want \n",
    "# do left if you just want to conform with the trade data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "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></th>\n",
       "      <th>total_exp_pc</th>\n",
       "      <th>china_exp_pc</th>\n",
       "      <th>tariff</th>\n",
       "      <th>emplvl_2017</th>\n",
       "      <th>fips</th>\n",
       "      <th>total_employment</th>\n",
       "      <th>emp_rtl</th>\n",
       "      <th>emp_all</th>\n",
       "      <th>emp_gds</th>\n",
       "      <th>emp_ngds</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>area_fips</th>\n",
       "      <th>time</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td rowspan=\"42\" valign=\"top\">10001</td>\n",
       "      <td>2016-01-01</td>\n",
       "      <td>453.257185</td>\n",
       "      <td>47.280196</td>\n",
       "      <td>1.069532</td>\n",
       "      <td>2843.0</td>\n",
       "      <td>10001</td>\n",
       "      <td>29514.0</td>\n",
       "      <td>9269.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>38494.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2016-02-01</td>\n",
       "      <td>471.930726</td>\n",
       "      <td>47.211522</td>\n",
       "      <td>1.069499</td>\n",
       "      <td>2843.0</td>\n",
       "      <td>10001</td>\n",
       "      <td>29514.0</td>\n",
       "      <td>9236.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>38646.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2016-03-01</td>\n",
       "      <td>485.376760</td>\n",
       "      <td>35.078484</td>\n",
       "      <td>1.069500</td>\n",
       "      <td>2843.0</td>\n",
       "      <td>10001</td>\n",
       "      <td>29514.0</td>\n",
       "      <td>9342.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>38917.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2016-04-01</td>\n",
       "      <td>460.259354</td>\n",
       "      <td>27.991526</td>\n",
       "      <td>1.069500</td>\n",
       "      <td>2843.0</td>\n",
       "      <td>10001</td>\n",
       "      <td>29514.0</td>\n",
       "      <td>9376.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>39719.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2016-05-01</td>\n",
       "      <td>473.572638</td>\n",
       "      <td>28.235163</td>\n",
       "      <td>1.069499</td>\n",
       "      <td>2843.0</td>\n",
       "      <td>10001</td>\n",
       "      <td>29514.0</td>\n",
       "      <td>9265.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>40164.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2016-06-01</td>\n",
       "      <td>476.828261</td>\n",
       "      <td>28.236923</td>\n",
       "      <td>1.069499</td>\n",
       "      <td>2843.0</td>\n",
       "      <td>10001</td>\n",
       "      <td>29514.0</td>\n",
       "      <td>9345.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>39656.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2016-07-01</td>\n",
       "      <td>481.979929</td>\n",
       "      <td>36.513882</td>\n",
       "      <td>1.069499</td>\n",
       "      <td>2843.0</td>\n",
       "      <td>10001</td>\n",
       "      <td>29514.0</td>\n",
       "      <td>9441.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>39773.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2016-08-01</td>\n",
       "      <td>501.651039</td>\n",
       "      <td>41.120917</td>\n",
       "      <td>1.069499</td>\n",
       "      <td>2843.0</td>\n",
       "      <td>10001</td>\n",
       "      <td>29514.0</td>\n",
       "      <td>9495.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>39746.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2016-09-01</td>\n",
       "      <td>495.664401</td>\n",
       "      <td>42.858366</td>\n",
       "      <td>1.069499</td>\n",
       "      <td>2843.0</td>\n",
       "      <td>10001</td>\n",
       "      <td>29514.0</td>\n",
       "      <td>9372.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>39881.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2016-10-01</td>\n",
       "      <td>559.415478</td>\n",
       "      <td>86.841250</td>\n",
       "      <td>1.069499</td>\n",
       "      <td>2843.0</td>\n",
       "      <td>10001</td>\n",
       "      <td>29514.0</td>\n",
       "      <td>9509.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>40038.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2016-11-01</td>\n",
       "      <td>558.631462</td>\n",
       "      <td>84.709850</td>\n",
       "      <td>1.069499</td>\n",
       "      <td>2843.0</td>\n",
       "      <td>10001</td>\n",
       "      <td>29514.0</td>\n",
       "      <td>9859.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>40254.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2016-12-01</td>\n",
       "      <td>528.952068</td>\n",
       "      <td>71.799888</td>\n",
       "      <td>1.069499</td>\n",
       "      <td>2843.0</td>\n",
       "      <td>10001</td>\n",
       "      <td>29514.0</td>\n",
       "      <td>9762.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>40220.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2017-01-01</td>\n",
       "      <td>522.580597</td>\n",
       "      <td>58.368700</td>\n",
       "      <td>1.068759</td>\n",
       "      <td>2843.0</td>\n",
       "      <td>10001</td>\n",
       "      <td>29514.0</td>\n",
       "      <td>9463.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>39246.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2017-02-01</td>\n",
       "      <td>502.309185</td>\n",
       "      <td>48.422056</td>\n",
       "      <td>1.068802</td>\n",
       "      <td>2843.0</td>\n",
       "      <td>10001</td>\n",
       "      <td>29514.0</td>\n",
       "      <td>9458.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>39490.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2017-03-01</td>\n",
       "      <td>571.161328</td>\n",
       "      <td>42.286424</td>\n",
       "      <td>1.068895</td>\n",
       "      <td>2843.0</td>\n",
       "      <td>10001</td>\n",
       "      <td>29514.0</td>\n",
       "      <td>9453.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>39598.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2017-04-01</td>\n",
       "      <td>509.278875</td>\n",
       "      <td>33.702476</td>\n",
       "      <td>1.068895</td>\n",
       "      <td>2843.0</td>\n",
       "      <td>10001</td>\n",
       "      <td>29514.0</td>\n",
       "      <td>9482.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>39804.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2017-05-01</td>\n",
       "      <td>519.176693</td>\n",
       "      <td>33.878036</td>\n",
       "      <td>1.068895</td>\n",
       "      <td>2843.0</td>\n",
       "      <td>10001</td>\n",
       "      <td>29514.0</td>\n",
       "      <td>9498.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>39950.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2017-06-01</td>\n",
       "      <td>509.383926</td>\n",
       "      <td>30.102481</td>\n",
       "      <td>1.068895</td>\n",
       "      <td>2843.0</td>\n",
       "      <td>10001</td>\n",
       "      <td>29514.0</td>\n",
       "      <td>9519.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>40318.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2017-07-01</td>\n",
       "      <td>492.508803</td>\n",
       "      <td>32.581600</td>\n",
       "      <td>1.068895</td>\n",
       "      <td>2843.0</td>\n",
       "      <td>10001</td>\n",
       "      <td>29514.0</td>\n",
       "      <td>9410.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>39670.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2017-08-01</td>\n",
       "      <td>509.577144</td>\n",
       "      <td>35.329198</td>\n",
       "      <td>1.068895</td>\n",
       "      <td>2843.0</td>\n",
       "      <td>10001</td>\n",
       "      <td>29514.0</td>\n",
       "      <td>9403.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>39467.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2017-09-01</td>\n",
       "      <td>495.123610</td>\n",
       "      <td>44.380139</td>\n",
       "      <td>1.068895</td>\n",
       "      <td>2843.0</td>\n",
       "      <td>10001</td>\n",
       "      <td>29514.0</td>\n",
       "      <td>9496.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>40023.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2017-10-01</td>\n",
       "      <td>569.787487</td>\n",
       "      <td>73.830992</td>\n",
       "      <td>1.068895</td>\n",
       "      <td>2843.0</td>\n",
       "      <td>10001</td>\n",
       "      <td>29514.0</td>\n",
       "      <td>9441.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>39791.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2017-11-01</td>\n",
       "      <td>571.520102</td>\n",
       "      <td>73.114154</td>\n",
       "      <td>1.068895</td>\n",
       "      <td>2843.0</td>\n",
       "      <td>10001</td>\n",
       "      <td>29514.0</td>\n",
       "      <td>9927.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>40512.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2017-12-01</td>\n",
       "      <td>553.649765</td>\n",
       "      <td>59.845960</td>\n",
       "      <td>1.068895</td>\n",
       "      <td>2843.0</td>\n",
       "      <td>10001</td>\n",
       "      <td>29514.0</td>\n",
       "      <td>9858.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>40292.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2018-01-01</td>\n",
       "      <td>516.700915</td>\n",
       "      <td>48.624283</td>\n",
       "      <td>1.068805</td>\n",
       "      <td>2843.0</td>\n",
       "      <td>10001</td>\n",
       "      <td>29514.0</td>\n",
       "      <td>9336.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>39580.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2018-02-01</td>\n",
       "      <td>516.145992</td>\n",
       "      <td>44.607737</td>\n",
       "      <td>1.068897</td>\n",
       "      <td>2843.0</td>\n",
       "      <td>10001</td>\n",
       "      <td>29514.0</td>\n",
       "      <td>9248.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>39479.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2018-03-01</td>\n",
       "      <td>587.864923</td>\n",
       "      <td>42.979806</td>\n",
       "      <td>1.068894</td>\n",
       "      <td>2843.0</td>\n",
       "      <td>10001</td>\n",
       "      <td>29514.0</td>\n",
       "      <td>9341.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>39829.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2018-04-01</td>\n",
       "      <td>559.410137</td>\n",
       "      <td>36.213274</td>\n",
       "      <td>1.393797</td>\n",
       "      <td>2843.0</td>\n",
       "      <td>10001</td>\n",
       "      <td>29514.0</td>\n",
       "      <td>9402.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>40631.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2018-05-01</td>\n",
       "      <td>579.008640</td>\n",
       "      <td>33.254994</td>\n",
       "      <td>1.393798</td>\n",
       "      <td>2843.0</td>\n",
       "      <td>10001</td>\n",
       "      <td>29514.0</td>\n",
       "      <td>9489.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>41179.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2018-06-01</td>\n",
       "      <td>558.919118</td>\n",
       "      <td>28.428818</td>\n",
       "      <td>1.393798</td>\n",
       "      <td>2843.0</td>\n",
       "      <td>10001</td>\n",
       "      <td>29514.0</td>\n",
       "      <td>9486.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>41147.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2018-07-01</td>\n",
       "      <td>524.597360</td>\n",
       "      <td>25.288644</td>\n",
       "      <td>2.221774</td>\n",
       "      <td>2843.0</td>\n",
       "      <td>10001</td>\n",
       "      <td>29514.0</td>\n",
       "      <td>9348.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>39952.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2018-08-01</td>\n",
       "      <td>545.616139</td>\n",
       "      <td>25.092559</td>\n",
       "      <td>2.221774</td>\n",
       "      <td>2843.0</td>\n",
       "      <td>10001</td>\n",
       "      <td>29514.0</td>\n",
       "      <td>9314.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>39830.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2018-09-01</td>\n",
       "      <td>511.239593</td>\n",
       "      <td>21.133593</td>\n",
       "      <td>2.235515</td>\n",
       "      <td>2843.0</td>\n",
       "      <td>10001</td>\n",
       "      <td>29514.0</td>\n",
       "      <td>9240.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>40133.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2018-10-01</td>\n",
       "      <td>562.324040</td>\n",
       "      <td>24.854552</td>\n",
       "      <td>2.485311</td>\n",
       "      <td>2843.0</td>\n",
       "      <td>10001</td>\n",
       "      <td>29514.0</td>\n",
       "      <td>9310.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>40562.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2018-11-01</td>\n",
       "      <td>555.172968</td>\n",
       "      <td>25.784350</td>\n",
       "      <td>2.426361</td>\n",
       "      <td>2843.0</td>\n",
       "      <td>10001</td>\n",
       "      <td>29514.0</td>\n",
       "      <td>9557.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>40809.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2018-12-01</td>\n",
       "      <td>524.630933</td>\n",
       "      <td>23.745690</td>\n",
       "      <td>2.426361</td>\n",
       "      <td>2843.0</td>\n",
       "      <td>10001</td>\n",
       "      <td>29514.0</td>\n",
       "      <td>9529.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>40748.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2019-01-01</td>\n",
       "      <td>527.484006</td>\n",
       "      <td>29.783300</td>\n",
       "      <td>2.424109</td>\n",
       "      <td>2843.0</td>\n",
       "      <td>10001</td>\n",
       "      <td>29514.0</td>\n",
       "      <td>9287.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>40347.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2019-02-01</td>\n",
       "      <td>505.126798</td>\n",
       "      <td>33.942512</td>\n",
       "      <td>2.424092</td>\n",
       "      <td>2843.0</td>\n",
       "      <td>10001</td>\n",
       "      <td>29514.0</td>\n",
       "      <td>9225.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>40337.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2019-03-01</td>\n",
       "      <td>573.829215</td>\n",
       "      <td>35.695393</td>\n",
       "      <td>2.424058</td>\n",
       "      <td>2843.0</td>\n",
       "      <td>10001</td>\n",
       "      <td>29514.0</td>\n",
       "      <td>9200.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>40576.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2019-04-01</td>\n",
       "      <td>541.332336</td>\n",
       "      <td>27.687231</td>\n",
       "      <td>2.424058</td>\n",
       "      <td>2843.0</td>\n",
       "      <td>10001</td>\n",
       "      <td>29514.0</td>\n",
       "      <td>9195.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>41241.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2019-05-01</td>\n",
       "      <td>553.302909</td>\n",
       "      <td>33.524619</td>\n",
       "      <td>2.424057</td>\n",
       "      <td>2843.0</td>\n",
       "      <td>10001</td>\n",
       "      <td>29514.0</td>\n",
       "      <td>9238.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>41666.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2019-06-01</td>\n",
       "      <td>526.449004</td>\n",
       "      <td>37.436249</td>\n",
       "      <td>2.424056</td>\n",
       "      <td>2843.0</td>\n",
       "      <td>10001</td>\n",
       "      <td>29514.0</td>\n",
       "      <td>9336.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>41439.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td rowspan=\"8\" valign=\"top\">10003</td>\n",
       "      <td>2016-01-01</td>\n",
       "      <td>430.048151</td>\n",
       "      <td>29.819622</td>\n",
       "      <td>0.211465</td>\n",
       "      <td>9072.0</td>\n",
       "      <td>10003</td>\n",
       "      <td>249775.0</td>\n",
       "      <td>30792.0</td>\n",
       "      <td>247839.0</td>\n",
       "      <td>23622.0</td>\n",
       "      <td>224217.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2016-02-01</td>\n",
       "      <td>433.446708</td>\n",
       "      <td>28.284932</td>\n",
       "      <td>0.211465</td>\n",
       "      <td>9072.0</td>\n",
       "      <td>10003</td>\n",
       "      <td>249775.0</td>\n",
       "      <td>30470.0</td>\n",
       "      <td>246561.0</td>\n",
       "      <td>23481.0</td>\n",
       "      <td>223080.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2016-03-01</td>\n",
       "      <td>479.220412</td>\n",
       "      <td>36.789866</td>\n",
       "      <td>0.211466</td>\n",
       "      <td>9072.0</td>\n",
       "      <td>10003</td>\n",
       "      <td>249775.0</td>\n",
       "      <td>30792.0</td>\n",
       "      <td>248144.0</td>\n",
       "      <td>24015.0</td>\n",
       "      <td>224129.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2016-04-01</td>\n",
       "      <td>454.820218</td>\n",
       "      <td>33.759979</td>\n",
       "      <td>0.211466</td>\n",
       "      <td>9072.0</td>\n",
       "      <td>10003</td>\n",
       "      <td>249775.0</td>\n",
       "      <td>31029.0</td>\n",
       "      <td>250501.0</td>\n",
       "      <td>24094.0</td>\n",
       "      <td>226407.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2016-05-01</td>\n",
       "      <td>447.341372</td>\n",
       "      <td>34.152770</td>\n",
       "      <td>0.211466</td>\n",
       "      <td>9072.0</td>\n",
       "      <td>10003</td>\n",
       "      <td>249775.0</td>\n",
       "      <td>31213.0</td>\n",
       "      <td>251305.0</td>\n",
       "      <td>24257.0</td>\n",
       "      <td>227048.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2016-06-01</td>\n",
       "      <td>463.404573</td>\n",
       "      <td>33.126661</td>\n",
       "      <td>0.211466</td>\n",
       "      <td>9072.0</td>\n",
       "      <td>10003</td>\n",
       "      <td>249775.0</td>\n",
       "      <td>31276.0</td>\n",
       "      <td>252955.0</td>\n",
       "      <td>24598.0</td>\n",
       "      <td>228357.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2016-07-01</td>\n",
       "      <td>442.881011</td>\n",
       "      <td>32.788082</td>\n",
       "      <td>0.211466</td>\n",
       "      <td>9072.0</td>\n",
       "      <td>10003</td>\n",
       "      <td>249775.0</td>\n",
       "      <td>31306.0</td>\n",
       "      <td>253356.0</td>\n",
       "      <td>25067.0</td>\n",
       "      <td>228289.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2016-08-01</td>\n",
       "      <td>460.392618</td>\n",
       "      <td>33.688284</td>\n",
       "      <td>0.211466</td>\n",
       "      <td>9072.0</td>\n",
       "      <td>10003</td>\n",
       "      <td>249775.0</td>\n",
       "      <td>31358.0</td>\n",
       "      <td>252479.0</td>\n",
       "      <td>25191.0</td>\n",
       "      <td>227288.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                      total_exp_pc  china_exp_pc    tariff  emplvl_2017  \\\n",
       "area_fips time                                                            \n",
       "10001     2016-01-01    453.257185     47.280196  1.069532       2843.0   \n",
       "          2016-02-01    471.930726     47.211522  1.069499       2843.0   \n",
       "          2016-03-01    485.376760     35.078484  1.069500       2843.0   \n",
       "          2016-04-01    460.259354     27.991526  1.069500       2843.0   \n",
       "          2016-05-01    473.572638     28.235163  1.069499       2843.0   \n",
       "          2016-06-01    476.828261     28.236923  1.069499       2843.0   \n",
       "          2016-07-01    481.979929     36.513882  1.069499       2843.0   \n",
       "          2016-08-01    501.651039     41.120917  1.069499       2843.0   \n",
       "          2016-09-01    495.664401     42.858366  1.069499       2843.0   \n",
       "          2016-10-01    559.415478     86.841250  1.069499       2843.0   \n",
       "          2016-11-01    558.631462     84.709850  1.069499       2843.0   \n",
       "          2016-12-01    528.952068     71.799888  1.069499       2843.0   \n",
       "          2017-01-01    522.580597     58.368700  1.068759       2843.0   \n",
       "          2017-02-01    502.309185     48.422056  1.068802       2843.0   \n",
       "          2017-03-01    571.161328     42.286424  1.068895       2843.0   \n",
       "          2017-04-01    509.278875     33.702476  1.068895       2843.0   \n",
       "          2017-05-01    519.176693     33.878036  1.068895       2843.0   \n",
       "          2017-06-01    509.383926     30.102481  1.068895       2843.0   \n",
       "          2017-07-01    492.508803     32.581600  1.068895       2843.0   \n",
       "          2017-08-01    509.577144     35.329198  1.068895       2843.0   \n",
       "          2017-09-01    495.123610     44.380139  1.068895       2843.0   \n",
       "          2017-10-01    569.787487     73.830992  1.068895       2843.0   \n",
       "          2017-11-01    571.520102     73.114154  1.068895       2843.0   \n",
       "          2017-12-01    553.649765     59.845960  1.068895       2843.0   \n",
       "          2018-01-01    516.700915     48.624283  1.068805       2843.0   \n",
       "          2018-02-01    516.145992     44.607737  1.068897       2843.0   \n",
       "          2018-03-01    587.864923     42.979806  1.068894       2843.0   \n",
       "          2018-04-01    559.410137     36.213274  1.393797       2843.0   \n",
       "          2018-05-01    579.008640     33.254994  1.393798       2843.0   \n",
       "          2018-06-01    558.919118     28.428818  1.393798       2843.0   \n",
       "          2018-07-01    524.597360     25.288644  2.221774       2843.0   \n",
       "          2018-08-01    545.616139     25.092559  2.221774       2843.0   \n",
       "          2018-09-01    511.239593     21.133593  2.235515       2843.0   \n",
       "          2018-10-01    562.324040     24.854552  2.485311       2843.0   \n",
       "          2018-11-01    555.172968     25.784350  2.426361       2843.0   \n",
       "          2018-12-01    524.630933     23.745690  2.426361       2843.0   \n",
       "          2019-01-01    527.484006     29.783300  2.424109       2843.0   \n",
       "          2019-02-01    505.126798     33.942512  2.424092       2843.0   \n",
       "          2019-03-01    573.829215     35.695393  2.424058       2843.0   \n",
       "          2019-04-01    541.332336     27.687231  2.424058       2843.0   \n",
       "          2019-05-01    553.302909     33.524619  2.424057       2843.0   \n",
       "          2019-06-01    526.449004     37.436249  2.424056       2843.0   \n",
       "10003     2016-01-01    430.048151     29.819622  0.211465       9072.0   \n",
       "          2016-02-01    433.446708     28.284932  0.211465       9072.0   \n",
       "          2016-03-01    479.220412     36.789866  0.211466       9072.0   \n",
       "          2016-04-01    454.820218     33.759979  0.211466       9072.0   \n",
       "          2016-05-01    447.341372     34.152770  0.211466       9072.0   \n",
       "          2016-06-01    463.404573     33.126661  0.211466       9072.0   \n",
       "          2016-07-01    442.881011     32.788082  0.211466       9072.0   \n",
       "          2016-08-01    460.392618     33.688284  0.211466       9072.0   \n",
       "\n",
       "                       fips  total_employment  emp_rtl   emp_all  emp_gds  \\\n",
       "area_fips time                                                              \n",
       "10001     2016-01-01  10001           29514.0   9269.0       0.0      0.0   \n",
       "          2016-02-01  10001           29514.0   9236.0       0.0      0.0   \n",
       "          2016-03-01  10001           29514.0   9342.0       0.0      0.0   \n",
       "          2016-04-01  10001           29514.0   9376.0       0.0      0.0   \n",
       "          2016-05-01  10001           29514.0   9265.0       0.0      0.0   \n",
       "          2016-06-01  10001           29514.0   9345.0       0.0      0.0   \n",
       "          2016-07-01  10001           29514.0   9441.0       0.0      0.0   \n",
       "          2016-08-01  10001           29514.0   9495.0       0.0      0.0   \n",
       "          2016-09-01  10001           29514.0   9372.0       0.0      0.0   \n",
       "          2016-10-01  10001           29514.0   9509.0       0.0      0.0   \n",
       "          2016-11-01  10001           29514.0   9859.0       0.0      0.0   \n",
       "          2016-12-01  10001           29514.0   9762.0       0.0      0.0   \n",
       "          2017-01-01  10001           29514.0   9463.0       0.0      0.0   \n",
       "          2017-02-01  10001           29514.0   9458.0       0.0      0.0   \n",
       "          2017-03-01  10001           29514.0   9453.0       0.0      0.0   \n",
       "          2017-04-01  10001           29514.0   9482.0       0.0      0.0   \n",
       "          2017-05-01  10001           29514.0   9498.0       0.0      0.0   \n",
       "          2017-06-01  10001           29514.0   9519.0       0.0      0.0   \n",
       "          2017-07-01  10001           29514.0   9410.0       0.0      0.0   \n",
       "          2017-08-01  10001           29514.0   9403.0       0.0      0.0   \n",
       "          2017-09-01  10001           29514.0   9496.0       0.0      0.0   \n",
       "          2017-10-01  10001           29514.0   9441.0       0.0      0.0   \n",
       "          2017-11-01  10001           29514.0   9927.0       0.0      0.0   \n",
       "          2017-12-01  10001           29514.0   9858.0       0.0      0.0   \n",
       "          2018-01-01  10001           29514.0   9336.0       0.0      0.0   \n",
       "          2018-02-01  10001           29514.0   9248.0       0.0      0.0   \n",
       "          2018-03-01  10001           29514.0   9341.0       0.0      0.0   \n",
       "          2018-04-01  10001           29514.0   9402.0       0.0      0.0   \n",
       "          2018-05-01  10001           29514.0   9489.0       0.0      0.0   \n",
       "          2018-06-01  10001           29514.0   9486.0       0.0      0.0   \n",
       "          2018-07-01  10001           29514.0   9348.0       0.0      0.0   \n",
       "          2018-08-01  10001           29514.0   9314.0       0.0      0.0   \n",
       "          2018-09-01  10001           29514.0   9240.0       0.0      0.0   \n",
       "          2018-10-01  10001           29514.0   9310.0       0.0      0.0   \n",
       "          2018-11-01  10001           29514.0   9557.0       0.0      0.0   \n",
       "          2018-12-01  10001           29514.0   9529.0       0.0      0.0   \n",
       "          2019-01-01  10001           29514.0   9287.0       0.0      0.0   \n",
       "          2019-02-01  10001           29514.0   9225.0       0.0      0.0   \n",
       "          2019-03-01  10001           29514.0   9200.0       0.0      0.0   \n",
       "          2019-04-01  10001           29514.0   9195.0       0.0      0.0   \n",
       "          2019-05-01  10001           29514.0   9238.0       0.0      0.0   \n",
       "          2019-06-01  10001           29514.0   9336.0       0.0      0.0   \n",
       "10003     2016-01-01  10003          249775.0  30792.0  247839.0  23622.0   \n",
       "          2016-02-01  10003          249775.0  30470.0  246561.0  23481.0   \n",
       "          2016-03-01  10003          249775.0  30792.0  248144.0  24015.0   \n",
       "          2016-04-01  10003          249775.0  31029.0  250501.0  24094.0   \n",
       "          2016-05-01  10003          249775.0  31213.0  251305.0  24257.0   \n",
       "          2016-06-01  10003          249775.0  31276.0  252955.0  24598.0   \n",
       "          2016-07-01  10003          249775.0  31306.0  253356.0  25067.0   \n",
       "          2016-08-01  10003          249775.0  31358.0  252479.0  25191.0   \n",
       "\n",
       "                      emp_ngds  \n",
       "area_fips time                  \n",
       "10001     2016-01-01   38494.0  \n",
       "          2016-02-01   38646.0  \n",
       "          2016-03-01   38917.0  \n",
       "          2016-04-01   39719.0  \n",
       "          2016-05-01   40164.0  \n",
       "          2016-06-01   39656.0  \n",
       "          2016-07-01   39773.0  \n",
       "          2016-08-01   39746.0  \n",
       "          2016-09-01   39881.0  \n",
       "          2016-10-01   40038.0  \n",
       "          2016-11-01   40254.0  \n",
       "          2016-12-01   40220.0  \n",
       "          2017-01-01   39246.0  \n",
       "          2017-02-01   39490.0  \n",
       "          2017-03-01   39598.0  \n",
       "          2017-04-01   39804.0  \n",
       "          2017-05-01   39950.0  \n",
       "          2017-06-01   40318.0  \n",
       "          2017-07-01   39670.0  \n",
       "          2017-08-01   39467.0  \n",
       "          2017-09-01   40023.0  \n",
       "          2017-10-01   39791.0  \n",
       "          2017-11-01   40512.0  \n",
       "          2017-12-01   40292.0  \n",
       "          2018-01-01   39580.0  \n",
       "          2018-02-01   39479.0  \n",
       "          2018-03-01   39829.0  \n",
       "          2018-04-01   40631.0  \n",
       "          2018-05-01   41179.0  \n",
       "          2018-06-01   41147.0  \n",
       "          2018-07-01   39952.0  \n",
       "          2018-08-01   39830.0  \n",
       "          2018-09-01   40133.0  \n",
       "          2018-10-01   40562.0  \n",
       "          2018-11-01   40809.0  \n",
       "          2018-12-01   40748.0  \n",
       "          2019-01-01   40347.0  \n",
       "          2019-02-01   40337.0  \n",
       "          2019-03-01   40576.0  \n",
       "          2019-04-01   41241.0  \n",
       "          2019-05-01   41666.0  \n",
       "          2019-06-01   41439.0  \n",
       "10003     2016-01-01  224217.0  \n",
       "          2016-02-01  223080.0  \n",
       "          2016-03-01  224129.0  \n",
       "          2016-04-01  226407.0  \n",
       "          2016-05-01  227048.0  \n",
       "          2016-06-01  228357.0  \n",
       "          2016-07-01  228289.0  \n",
       "          2016-08-01  227288.0  "
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "trade_employ.head(50)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [],
   "source": [
    "trade_employ.total_employment.fillna(method='bfill', inplace = True)\n",
    "\n",
    "trade_employ.tariff.fillna(method='bfill', inplace = True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [],
   "source": [
    "dfrural = pd.read_excel(\"https://www2.census.gov/geo/docs/reference/ua/County_Rural_Lookup.xlsx\", skiprows=[0,1,2],\n",
    "                       nrows = 3142)\n",
    "\n",
    "dfrural[\"area_fips\"] = dfrural[\"2015 GEOID\"].astype(int)\n",
    "\n",
    "trade_employ.reset_index(inplace = True)\n",
    "\n",
    "trade_employ[\"area_fips\"] = trade_employ[\"area_fips\"].astype(int)\n",
    "\n",
    "trade_employ = trade_employ.merge(dfrural[[\"area_fips\", \"2010 Census \\nPercent Rural\",\n",
    "                             \"2010 Census Total Population\"]], left_on = \"area_fips\", right_on = \"area_fips\",\n",
    "                                 how = \"left\")\n",
    "\n",
    "trade_employ.set_index([\"area_fips\", \"time\"],inplace = True)\n",
    "\n",
    "trade_employ.rename({\"2010 Census \\nPercent Rural\": \"rural_share\",\n",
    "            \"2010 Census Total Population\": \"2010_population\"}, inplace = True, axis = 1)\n",
    "\n",
    "trade_employ[\"rural_share\"] = 0.01*trade_employ[\"rural_share\"]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [],
   "source": [
    "my_api_key = '34e40301bda77077e24c859c6c6c0b721ad73fc7'\n",
    "# This is my api_key\n",
    "\n",
    "c = Census(my_api_key)\n",
    "# This will create an object c which has methods associated with it.\n",
    "# We will see  these below.\n",
    "\n",
    "type(c) \n",
    "# Per the discussion below, try c.tab and see the options. \n",
    "\n",
    "code = (\"NAME\",\"B01001_001E\",\"B19013_001E\") # Same Codes:\n",
    "\n",
    "county_2017 = pd.DataFrame(c.acs5.get(code, \n",
    "                                         {'for': 'county:*'}, year=2017))\n",
    "                                         # Same deal, but we specify county then the wild card\n",
    "                                         # On the example page, there are ways do do this, only by state\n",
    "        \n",
    "county_2017 = county_2017.rename(columns = {\"B01001_001E\":\"2017_population\", \"B19013_001E\":\"2017_income\"})\n",
    "\n",
    "county_2017[\"GEOFIPS\"] = (county_2017[\"state\"] + county_2017[\"county\"]).astype(int)\n",
    "\n",
    "county_2017[\"2017_population\"] = county_2017[\"2017_population\"].astype(float)\n",
    "\n",
    "county_2017[\"2017_income\"] = county_2017[\"2017_income\"].astype(float)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [],
   "source": [
    "county_2017.set_index([\"GEOFIPS\"], inplace = True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [],
   "source": [
    "trade_employ.reset_index(inplace = True)\n",
    "\n",
    "trade_employ = trade_employ.merge(county_2017[[\"2017_income\",\"2017_population\"]],\n",
    "                                  left_on = \"area_fips\", right_index = True, how = \"left\")\n",
    "\n",
    "#trade_employ.drop(labels = \"index\", axis = 1, inplace = True)\n",
    "\n",
    "trade_employ.set_index([\"area_fips\", \"time\"],inplace = True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [],
   "source": [
    "file_path = cwd + \"\\\\data\\\\trade_employment_blssingle19.parquet\"\n",
    "\n",
    "pq.write_table(pa.Table.from_pandas(trade_employ.reset_index()), file_path)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "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>total_exp_pc</th>\n",
       "      <th>china_exp_pc</th>\n",
       "      <th>tariff</th>\n",
       "      <th>emplvl_2017</th>\n",
       "      <th>fips</th>\n",
       "      <th>total_employment</th>\n",
       "      <th>emp_rtl</th>\n",
       "      <th>emp_all</th>\n",
       "      <th>emp_gds</th>\n",
       "      <th>emp_ngds</th>\n",
       "      <th>rural_share</th>\n",
       "      <th>2010_population</th>\n",
       "      <th>2017_income</th>\n",
       "      <th>2017_population</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>time</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>2018-09-01</td>\n",
       "      <td>514.280421</td>\n",
       "      <td>38.330528</td>\n",
       "      <td>0.260085</td>\n",
       "      <td>9072.0</td>\n",
       "      <td>10003</td>\n",
       "      <td>249775.0</td>\n",
       "      <td>29784.0</td>\n",
       "      <td>254270.0</td>\n",
       "      <td>26262.0</td>\n",
       "      <td>228008.0</td>\n",
       "      <td>0.046024</td>\n",
       "      <td>538479.0</td>\n",
       "      <td>68336.0</td>\n",
       "      <td>555036.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2018-10-01</td>\n",
       "      <td>545.881585</td>\n",
       "      <td>39.658499</td>\n",
       "      <td>0.464673</td>\n",
       "      <td>9072.0</td>\n",
       "      <td>10003</td>\n",
       "      <td>249775.0</td>\n",
       "      <td>30244.0</td>\n",
       "      <td>257394.0</td>\n",
       "      <td>26398.0</td>\n",
       "      <td>230996.0</td>\n",
       "      <td>0.046024</td>\n",
       "      <td>538479.0</td>\n",
       "      <td>68336.0</td>\n",
       "      <td>555036.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2018-11-01</td>\n",
       "      <td>516.246195</td>\n",
       "      <td>37.360160</td>\n",
       "      <td>0.456547</td>\n",
       "      <td>9072.0</td>\n",
       "      <td>10003</td>\n",
       "      <td>249775.0</td>\n",
       "      <td>31428.0</td>\n",
       "      <td>259963.0</td>\n",
       "      <td>26256.0</td>\n",
       "      <td>233707.0</td>\n",
       "      <td>0.046024</td>\n",
       "      <td>538479.0</td>\n",
       "      <td>68336.0</td>\n",
       "      <td>555036.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2018-12-01</td>\n",
       "      <td>499.745029</td>\n",
       "      <td>39.971812</td>\n",
       "      <td>0.456547</td>\n",
       "      <td>9072.0</td>\n",
       "      <td>10003</td>\n",
       "      <td>249775.0</td>\n",
       "      <td>31706.0</td>\n",
       "      <td>260304.0</td>\n",
       "      <td>26435.0</td>\n",
       "      <td>233869.0</td>\n",
       "      <td>0.046024</td>\n",
       "      <td>538479.0</td>\n",
       "      <td>68336.0</td>\n",
       "      <td>555036.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2019-01-01</td>\n",
       "      <td>496.613756</td>\n",
       "      <td>34.459205</td>\n",
       "      <td>0.448531</td>\n",
       "      <td>9072.0</td>\n",
       "      <td>10003</td>\n",
       "      <td>249775.0</td>\n",
       "      <td>30079.0</td>\n",
       "      <td>253938.0</td>\n",
       "      <td>25984.0</td>\n",
       "      <td>227954.0</td>\n",
       "      <td>0.046024</td>\n",
       "      <td>538479.0</td>\n",
       "      <td>68336.0</td>\n",
       "      <td>555036.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2019-02-01</td>\n",
       "      <td>485.506472</td>\n",
       "      <td>35.747547</td>\n",
       "      <td>0.448519</td>\n",
       "      <td>9072.0</td>\n",
       "      <td>10003</td>\n",
       "      <td>249775.0</td>\n",
       "      <td>29736.0</td>\n",
       "      <td>253956.0</td>\n",
       "      <td>25848.0</td>\n",
       "      <td>228108.0</td>\n",
       "      <td>0.046024</td>\n",
       "      <td>538479.0</td>\n",
       "      <td>68336.0</td>\n",
       "      <td>555036.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2019-03-01</td>\n",
       "      <td>552.995665</td>\n",
       "      <td>47.108656</td>\n",
       "      <td>0.448519</td>\n",
       "      <td>9072.0</td>\n",
       "      <td>10003</td>\n",
       "      <td>249775.0</td>\n",
       "      <td>29888.0</td>\n",
       "      <td>255809.0</td>\n",
       "      <td>26280.0</td>\n",
       "      <td>229529.0</td>\n",
       "      <td>0.046024</td>\n",
       "      <td>538479.0</td>\n",
       "      <td>68336.0</td>\n",
       "      <td>555036.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2019-04-01</td>\n",
       "      <td>504.428530</td>\n",
       "      <td>36.677304</td>\n",
       "      <td>0.448519</td>\n",
       "      <td>9072.0</td>\n",
       "      <td>10003</td>\n",
       "      <td>249775.0</td>\n",
       "      <td>30013.0</td>\n",
       "      <td>257083.0</td>\n",
       "      <td>26451.0</td>\n",
       "      <td>230632.0</td>\n",
       "      <td>0.046024</td>\n",
       "      <td>538479.0</td>\n",
       "      <td>68336.0</td>\n",
       "      <td>555036.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2019-05-01</td>\n",
       "      <td>525.509009</td>\n",
       "      <td>39.118929</td>\n",
       "      <td>0.448517</td>\n",
       "      <td>9072.0</td>\n",
       "      <td>10003</td>\n",
       "      <td>249775.0</td>\n",
       "      <td>29945.0</td>\n",
       "      <td>257617.0</td>\n",
       "      <td>26518.0</td>\n",
       "      <td>231099.0</td>\n",
       "      <td>0.046024</td>\n",
       "      <td>538479.0</td>\n",
       "      <td>68336.0</td>\n",
       "      <td>555036.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2019-06-01</td>\n",
       "      <td>514.105599</td>\n",
       "      <td>37.623431</td>\n",
       "      <td>0.448517</td>\n",
       "      <td>9072.0</td>\n",
       "      <td>10003</td>\n",
       "      <td>249775.0</td>\n",
       "      <td>29778.0</td>\n",
       "      <td>257787.0</td>\n",
       "      <td>26632.0</td>\n",
       "      <td>231155.0</td>\n",
       "      <td>0.046024</td>\n",
       "      <td>538479.0</td>\n",
       "      <td>68336.0</td>\n",
       "      <td>555036.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "            total_exp_pc  china_exp_pc    tariff  emplvl_2017   fips  \\\n",
       "time                                                                   \n",
       "2018-09-01    514.280421     38.330528  0.260085       9072.0  10003   \n",
       "2018-10-01    545.881585     39.658499  0.464673       9072.0  10003   \n",
       "2018-11-01    516.246195     37.360160  0.456547       9072.0  10003   \n",
       "2018-12-01    499.745029     39.971812  0.456547       9072.0  10003   \n",
       "2019-01-01    496.613756     34.459205  0.448531       9072.0  10003   \n",
       "2019-02-01    485.506472     35.747547  0.448519       9072.0  10003   \n",
       "2019-03-01    552.995665     47.108656  0.448519       9072.0  10003   \n",
       "2019-04-01    504.428530     36.677304  0.448519       9072.0  10003   \n",
       "2019-05-01    525.509009     39.118929  0.448517       9072.0  10003   \n",
       "2019-06-01    514.105599     37.623431  0.448517       9072.0  10003   \n",
       "\n",
       "            total_employment  emp_rtl   emp_all  emp_gds  emp_ngds  \\\n",
       "time                                                                 \n",
       "2018-09-01          249775.0  29784.0  254270.0  26262.0  228008.0   \n",
       "2018-10-01          249775.0  30244.0  257394.0  26398.0  230996.0   \n",
       "2018-11-01          249775.0  31428.0  259963.0  26256.0  233707.0   \n",
       "2018-12-01          249775.0  31706.0  260304.0  26435.0  233869.0   \n",
       "2019-01-01          249775.0  30079.0  253938.0  25984.0  227954.0   \n",
       "2019-02-01          249775.0  29736.0  253956.0  25848.0  228108.0   \n",
       "2019-03-01          249775.0  29888.0  255809.0  26280.0  229529.0   \n",
       "2019-04-01          249775.0  30013.0  257083.0  26451.0  230632.0   \n",
       "2019-05-01          249775.0  29945.0  257617.0  26518.0  231099.0   \n",
       "2019-06-01          249775.0  29778.0  257787.0  26632.0  231155.0   \n",
       "\n",
       "            rural_share  2010_population  2017_income  2017_population  \n",
       "time                                                                    \n",
       "2018-09-01     0.046024         538479.0      68336.0         555036.0  \n",
       "2018-10-01     0.046024         538479.0      68336.0         555036.0  \n",
       "2018-11-01     0.046024         538479.0      68336.0         555036.0  \n",
       "2018-12-01     0.046024         538479.0      68336.0         555036.0  \n",
       "2019-01-01     0.046024         538479.0      68336.0         555036.0  \n",
       "2019-02-01     0.046024         538479.0      68336.0         555036.0  \n",
       "2019-03-01     0.046024         538479.0      68336.0         555036.0  \n",
       "2019-04-01     0.046024         538479.0      68336.0         555036.0  \n",
       "2019-05-01     0.046024         538479.0      68336.0         555036.0  \n",
       "2019-06-01     0.046024         538479.0      68336.0         555036.0  "
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "trade_employ.loc[10003].tail(10)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "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.6.6"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}