county-projection-imports.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "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",
    "from census import Census # This is new...\n",
    "\n",
    "import requests, io             # internet and input tools  \n",
    "import zipfile as zf            # zip file tools \n",
    "import os  \n",
    "\n",
    "#import weightedcalcs as wc\n",
    "#import numpy as np\n",
    "\n",
    "import pyarrow as pa\n",
    "import pyarrow.parquet as pq"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\n",
      "**********************************************************************************\n",
      "Downloading and processing BLS file\n",
      "\n",
      "Type of zipfile object: <class 'zipfile.ZipFile'>\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\ProgramData\\Anaconda3\\lib\\site-packages\\IPython\\core\\interactiveshell.py:3063: DtypeWarning: Columns (0) have mixed types.Specify dtype option on import or set low_memory=False.\n",
      "  interactivity=interactivity, compiler=compiler, result=result)\n"
     ]
    }
   ],
   "source": [
    "print(\"\")\n",
    "print(\"**********************************************************************************\")\n",
    "print(\"Downloading and processing BLS file\")\n",
    "print(\"\")\n",
    "\n",
    "url = \"https://data.bls.gov/cew/data/files/2017/csv/2017_annual_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_sf = zf.ZipFile(io.BytesIO(r.content)) \n",
    "print('Type of zipfile object:', type(bls_sf))\n",
    "\n",
    "clist = ['area_fips', 'own_code', 'industry_code', 'agglvl_code', 'size_code',\n",
    "       'year', 'disclosure_code', 'annual_avg_estabs',\n",
    "       'annual_avg_emplvl', 'total_annual_wages','avg_annual_pay']\n",
    "\n",
    "df = pd.read_csv(bls_sf.open(bls_sf.namelist()[0]), usecols= clist)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Then the file below cleans stuff up. The most important is the `NAICS_county_level` which selects the NAICS aggregation and then the county aggregation. Website describing this is here:\n",
    "\n",
    "[https://data.bls.gov/cew/doc/titles/agglevel/agglevel_titles.htm](https://data.bls.gov/cew/doc/titles/agglevel/agglevel_titles.htm)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "NAICS_county_level = 75 \n",
    "# This is the code that will select only counties at the 3 digit NAICS level\n",
    "\n",
    "df_county = df[df.agglvl_code == NAICS_county_level].copy()\n",
    "\n",
    "df_county = df_county[df_county.own_code == 5]\n",
    "# Only grab private stuff\n",
    "\n",
    "df_county = df_county[(df_county.area_fips.str[0:2] != \"72\") & (df_county.area_fips.str[0:2] != \"78\")\n",
    "              & (df_county.area_fips.str[0:2] != \"02\") & (df_county.area_fips.str[0:2] != \"15\")]\n",
    "#Drop puerto rico, alaska, hawaii...this mayb not be doing what I think it is...as it looks like these guys are there\n",
    "# Does not matter as analysis is performed withthem, drop them when do the map. \n",
    "\n",
    "df_county[\"sup_ind\"] = df_county.industry_code.str[1].astype(int)\n",
    "# sometimes there are super industries floating around we want to drop them.\n",
    "# not clear if this matters with the conditioning all ready\n",
    "\n",
    "df_county = df_county[df_county[\"sup_ind\"] > 0]\n",
    "\n",
    "df_county.area_fips = df_county.area_fips.astype(str)\n",
    "\n",
    "df_national = df_county.groupby(\"industry_code\").agg({\"annual_avg_emplvl\": \"sum\"})\n",
    "\n",
    "df_national.reset_index(inplace = True)\n",
    "\n",
    "df_national.rename({\"annual_avg_emplvl\":\"nat_emplvl\"}, axis = 1, inplace = True)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Let's compute annual employment. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "115756851"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_county.annual_avg_emplvl.sum()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "which matches well with FRED (https://fred.stlouisfed.org/series/USPRIV) in 2017 (off by a couple million)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Read in Trade Data and Merge"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [],
   "source": [
    "imports_by_naics = pd.read_csv(\".//data//imports_by_naics.csv\", dtype= {\"naics3\": str})\n",
    "\n",
    "imports_by_naics.set_index([\"naics3\"], inplace = True)\n",
    "\n",
    "dftrade_17_naics3 = pd.read_csv(\".//data//2017_imports_by_naics.csv\", dtype= {\"naics3\": str})\n",
    "\n",
    "dftrade_17_naics3.set_index([\"naics3\"], inplace = True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "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>2017_china_trade</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>naics3</th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>111</th>\n",
       "      <td>6.168815e+08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>112</th>\n",
       "      <td>3.734622e+07</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>113</th>\n",
       "      <td>2.368698e+08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>114</th>\n",
       "      <td>2.323795e+09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>211</th>\n",
       "      <td>1.397256e+06</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        2017_china_trade\n",
       "naics3                  \n",
       "111         6.168815e+08\n",
       "112         3.734622e+07\n",
       "113         2.368698e+08\n",
       "114         2.323795e+09\n",
       "211         1.397256e+06"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "dftrade_17_naics3.head()\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_national = df_national.merge(dftrade_17_naics3[\"2017_china_trade\"],\n",
    "                                left_on = \"industry_code\", right_index = True, how = \"left\")\n",
    "\n",
    "df_national[\"2017_china_trade\"].replace(np.nan, 0, inplace = True)\n",
    "\n",
    "df_national[\"trd_wts\"] = (df_national[\"2017_china_trade\"]/df_national[\"2017_china_trade\"].sum())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Then check to make sure that the trade weights sum up to one."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "1.0"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_national.trd_wts.sum()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "---\n",
    "\n",
    "### Step 3 Merge trade data with the county data\n",
    "\n",
    "This is the most time consuming step (interms of compuation time). So start with the county data set, `groupby` county, then apply a function which will create (i) time varying exports (which are constructed with the 2017 weightes) and (ii) time varying tariffs (also constructed using the 2017) weights. \n",
    "\n",
    "The final want is a big dataframe that has county, time, export exposure and tariff exposure. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\n",
      "**********************************************************************************\n",
      "Constructing County-Level Tariffs and Exports\n",
      "\n"
     ]
    }
   ],
   "source": [
    "print(\"\")\n",
    "print(\"**********************************************************************************\")\n",
    "print(\"Constructing County-Level Tariffs and Exports\")\n",
    "print(\"\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [],
   "source": [
    "grp = df_county.groupby(\"area_fips\")\n",
    "\n",
    "# Let's just look at one of the groups...\n",
    "\n",
    "#grp.get_group(\"1001\").head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Below are the two key functions that deliver this. Basically it does the following: \n",
    "\n",
    "- Take a group at county level, merge it with the national level data set, so the resulting `df` has the county and nation.\n",
    "\n",
    "- Create the weights. \n",
    "\n",
    "- Then merge it with the exports, this will now be a df with exports varying over time, but with the fixed weights associated with each entry.\n",
    "\n",
    "- Then aggregate the national exports by NAICS by the county level weights, giving a county level time series of exports.\n",
    "\n",
    "---\n",
    "\n",
    "**Updates**\n",
    "\n",
    "- The tariff measure does the following: fix a county, take employment in industry $i$ and divide by total county employment, then sum up tariffs across industries with the weights being the county level share. The idea here is if all employment in a county is soy, then the \"effective\" tariff that the county faces is the soy tariff.\n",
    "\n",
    "In equation terms: here $c$ is county, $s$ is industry, $n$, below is nation.\n",
    "\n",
    "$\\tau_{c,t} = \\sum_{s\\in S}\\frac{L_{c,s}}{L_{c,S}} \\tau_{s,t}$\n",
    "\n",
    "Note that below, I make one further adjustment to make sure that $L_{c,S}$ is for all employment, not just the sum across $L_{c,s}$\n",
    "\n",
    "\n",
    "- The export measure: What am I doing: take a county's employment in industry $i$ and divide by **national** level employment in industry $i$. Then a \"county's\" exports is the the sum across industries, weighted by the county's share of national employment in each industry. The idea here is, if a county's has all national level employment in an industry, all that industries exports will be assigned to that county.\n",
    "\n",
    "$\\mbox{EX}_{c,t} = \\frac{1}{L_{c,S,2017}}\\sum_{s\\in S}\\frac{L_{c,s,2017}}{L_{n,s,2017}} \\mbox{EX}_{s,t}$\n",
    "\n",
    "and then I divide by total employment in the county to have a county per worker measure. This is done for exports to China and then export in total. Note that below, I make one further adjustment to make sure that $L_{c,S}$ is for all employment, not just the sum across $L_{c,s}$\n",
    "\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [],
   "source": [
    "def create_trade_weights(df):\n",
    "    # Takes in the county groupings and will return, for each county, a time series of export\n",
    "    # exposure, tariffs, and other statistics. \n",
    "\n",
    "    new_df = df.merge(df_national[[\"nat_emplvl\",\n",
    "                                   \"industry_code\", \"trd_wts\"]],\n",
    "                                  how = \"outer\", left_on = \"industry_code\", right_on = \"industry_code\")\n",
    "    # Merge the nation with the county, why, we want to make sure all the naics codes are lined up properly\n",
    "        \n",
    "    new_df[\"emp_wts\"] = (new_df.annual_avg_emplvl/new_df.nat_emplvl)\n",
    "     \n",
    "    # create the weights...\n",
    "        \n",
    "    foo_df = imports_by_naics.merge(new_df[[\"emp_wts\",\"trd_wts\",\n",
    "                                           \"industry_code\",\n",
    "                                          \"annual_avg_emplvl\"]], left_index = True, right_on = \"industry_code\")  \n",
    "    \n",
    "    # Now each weight is for a NAICS code, we will merge it with the export trade data set, so for all naics, all time...\n",
    "    # This is a big df whith all trade data and then the county's weights for each naics code\n",
    "    \n",
    "    foo_grp = foo_df.groupby(\"time\")\n",
    "    \n",
    "    # group by time. \n",
    "    \n",
    "    foo = foo_grp.apply(trade_by_naics)\n",
    "    \n",
    "    # Then for each time gropuing, we aggregate across the naics codes according to the weights above.\n",
    "    \n",
    "    foo = foo.droplevel(1)\n",
    "    \n",
    "    foo[\"fips\"] = df[\"area_fips\"].astype(str).iloc[0]\n",
    "    \n",
    "    # some cleaning of the df\n",
    "    \n",
    "    foo[\"total_employment\"] = new_df.annual_avg_emplvl.sum()\n",
    "    \n",
    "    # get total employment.\n",
    "    \n",
    "    return pd.DataFrame(foo)\n",
    "    "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [],
   "source": [
    "def trade_by_naics(df):\n",
    "    # Simple function just to test about aggregation \n",
    "\n",
    "    china_imp_pc = (1/df[\"annual_avg_emplvl\"].sum())*(df[\"china_trade\"]*df[\"emp_wts\"]).sum()\n",
    "    \n",
    "    total_imp_pc = (1/df[\"annual_avg_emplvl\"].sum())*(df[\"total_trade\"]*df[\"emp_wts\"]).sum()\n",
    "    # the first term multiplies trade by the county's share of national level employment\n",
    "    # then the outside term divides by number of workers in a county. \n",
    "    \n",
    "    #tariff_nwt_pc = (1/df[\"annual_avg_emplvl\"].sum())*(df[\"tariff_trd_w_avg\"]*df[\"emp_wts\"]).sum()\n",
    "    # This is the measure that makes most sense, need to justify it...\n",
    "    tariff =  ((df[\"annual_avg_emplvl\"]*df[\"tariff_trd_w_avg\"])/df[\"annual_avg_emplvl\"].sum()).sum()\n",
    "    # local employment share weighted tariff. So if all guys are in area are working in soy,\n",
    "    # then they are facing the soybean tariff....\n",
    "    \n",
    "    foo = {\"total_imp_pc\": [total_imp_pc],\n",
    "          \"china_imp_pc\": [china_imp_pc],\n",
    "           \"tariff\": [tariff],\n",
    "          \"emplvl_2017\": df[\"annual_avg_emplvl\"].sum()}\n",
    "\n",
    "    return pd.DataFrame(foo)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Then apply the function to the county groups"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\ProgramData\\Anaconda3\\lib\\site-packages\\ipykernel_launcher.py:4: RuntimeWarning: divide by zero encountered in double_scalars\n",
      "  after removing the cwd from sys.path.\n",
      "C:\\ProgramData\\Anaconda3\\lib\\site-packages\\ipykernel_launcher.py:4: RuntimeWarning: invalid value encountered in double_scalars\n",
      "  after removing the cwd from sys.path.\n",
      "C:\\ProgramData\\Anaconda3\\lib\\site-packages\\ipykernel_launcher.py:6: RuntimeWarning: divide by zero encountered in double_scalars\n",
      "  \n",
      "C:\\ProgramData\\Anaconda3\\lib\\site-packages\\ipykernel_launcher.py:6: RuntimeWarning: invalid value encountered in double_scalars\n",
      "  \n"
     ]
    }
   ],
   "source": [
    "trade_county = grp.apply(create_trade_weights)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "And we are done and output the file to where we want it"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "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_imp_pc</th>\n",
       "      <th>china_imp_pc</th>\n",
       "      <th>tariff</th>\n",
       "      <th>emplvl_2017</th>\n",
       "      <th>fips</th>\n",
       "      <th>total_employment</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",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th rowspan=\"5\" valign=\"top\">48123</th>\n",
       "      <th>2019-09-01</th>\n",
       "      <td>114485.752818</td>\n",
       "      <td>46449.517478</td>\n",
       "      <td>32.989823</td>\n",
       "      <td>600.0</td>\n",
       "      <td>48123</td>\n",
       "      <td>4137.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2019-10-01</th>\n",
       "      <td>115237.179352</td>\n",
       "      <td>40010.055528</td>\n",
       "      <td>32.989823</td>\n",
       "      <td>600.0</td>\n",
       "      <td>48123</td>\n",
       "      <td>4137.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2019-11-01</th>\n",
       "      <td>99380.664946</td>\n",
       "      <td>33064.010855</td>\n",
       "      <td>32.989823</td>\n",
       "      <td>600.0</td>\n",
       "      <td>48123</td>\n",
       "      <td>4137.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2019-12-01</th>\n",
       "      <td>103693.084538</td>\n",
       "      <td>34645.488358</td>\n",
       "      <td>32.989823</td>\n",
       "      <td>600.0</td>\n",
       "      <td>48123</td>\n",
       "      <td>4137.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2020-01-01</th>\n",
       "      <td>115467.020595</td>\n",
       "      <td>41316.863047</td>\n",
       "      <td>32.989434</td>\n",
       "      <td>600.0</td>\n",
       "      <td>48123</td>\n",
       "      <td>4137.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"5\" valign=\"top\">36035</th>\n",
       "      <th>2019-09-01</th>\n",
       "      <td>97199.727453</td>\n",
       "      <td>45363.001413</td>\n",
       "      <td>32.940729</td>\n",
       "      <td>1231.0</td>\n",
       "      <td>36035</td>\n",
       "      <td>9568.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2019-10-01</th>\n",
       "      <td>98404.233825</td>\n",
       "      <td>39292.346525</td>\n",
       "      <td>32.940729</td>\n",
       "      <td>1231.0</td>\n",
       "      <td>36035</td>\n",
       "      <td>9568.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2019-11-01</th>\n",
       "      <td>83226.331981</td>\n",
       "      <td>32433.032949</td>\n",
       "      <td>32.940729</td>\n",
       "      <td>1231.0</td>\n",
       "      <td>36035</td>\n",
       "      <td>9568.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2019-12-01</th>\n",
       "      <td>84177.689456</td>\n",
       "      <td>33662.187227</td>\n",
       "      <td>32.940729</td>\n",
       "      <td>1231.0</td>\n",
       "      <td>36035</td>\n",
       "      <td>9568.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2020-01-01</th>\n",
       "      <td>95201.626814</td>\n",
       "      <td>39860.880563</td>\n",
       "      <td>32.940651</td>\n",
       "      <td>1231.0</td>\n",
       "      <td>36035</td>\n",
       "      <td>9568.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2013</th>\n",
       "      <th>2020-01-01</th>\n",
       "      <td>4364.843586</td>\n",
       "      <td>212.706687</td>\n",
       "      <td>31.462373</td>\n",
       "      <td>1895.0</td>\n",
       "      <td>2013</td>\n",
       "      <td>1944.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>72067</th>\n",
       "      <th>2020-01-01</th>\n",
       "      <td>4364.843586</td>\n",
       "      <td>212.706687</td>\n",
       "      <td>31.462373</td>\n",
       "      <td>69.0</td>\n",
       "      <td>72067</td>\n",
       "      <td>768.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>38091</th>\n",
       "      <th>2020-01-01</th>\n",
       "      <td>4364.843586</td>\n",
       "      <td>212.706687</td>\n",
       "      <td>31.462373</td>\n",
       "      <td>61.0</td>\n",
       "      <td>38091</td>\n",
       "      <td>115.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>40073</th>\n",
       "      <th>2020-01-01</th>\n",
       "      <td>4364.843586</td>\n",
       "      <td>212.706687</td>\n",
       "      <td>31.462373</td>\n",
       "      <td>42.0</td>\n",
       "      <td>40073</td>\n",
       "      <td>3858.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26031</th>\n",
       "      <th>2020-01-01</th>\n",
       "      <td>4364.843586</td>\n",
       "      <td>212.706687</td>\n",
       "      <td>31.462373</td>\n",
       "      <td>26.0</td>\n",
       "      <td>26031</td>\n",
       "      <td>4698.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29063</th>\n",
       "      <th>2020-01-01</th>\n",
       "      <td>4364.843586</td>\n",
       "      <td>212.706687</td>\n",
       "      <td>31.462373</td>\n",
       "      <td>26.0</td>\n",
       "      <td>29063</td>\n",
       "      <td>1310.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>72117</th>\n",
       "      <th>2020-01-01</th>\n",
       "      <td>4364.843586</td>\n",
       "      <td>212.706687</td>\n",
       "      <td>31.462373</td>\n",
       "      <td>26.0</td>\n",
       "      <td>72117</td>\n",
       "      <td>739.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>72055</th>\n",
       "      <th>2020-01-01</th>\n",
       "      <td>4364.843586</td>\n",
       "      <td>212.706687</td>\n",
       "      <td>31.462373</td>\n",
       "      <td>23.0</td>\n",
       "      <td>72055</td>\n",
       "      <td>232.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"4\" valign=\"top\">72067</th>\n",
       "      <th>2019-09-01</th>\n",
       "      <td>4181.763302</td>\n",
       "      <td>205.086046</td>\n",
       "      <td>31.462356</td>\n",
       "      <td>69.0</td>\n",
       "      <td>72067</td>\n",
       "      <td>768.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2019-10-01</th>\n",
       "      <td>4517.453780</td>\n",
       "      <td>178.320454</td>\n",
       "      <td>31.462356</td>\n",
       "      <td>69.0</td>\n",
       "      <td>72067</td>\n",
       "      <td>768.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2019-11-01</th>\n",
       "      <td>4158.198680</td>\n",
       "      <td>166.979433</td>\n",
       "      <td>31.462356</td>\n",
       "      <td>69.0</td>\n",
       "      <td>72067</td>\n",
       "      <td>768.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2019-12-01</th>\n",
       "      <td>4223.539414</td>\n",
       "      <td>197.606290</td>\n",
       "      <td>31.462356</td>\n",
       "      <td>69.0</td>\n",
       "      <td>72067</td>\n",
       "      <td>768.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"3\" valign=\"top\">40073</th>\n",
       "      <th>2019-09-01</th>\n",
       "      <td>4181.763302</td>\n",
       "      <td>205.086046</td>\n",
       "      <td>31.462356</td>\n",
       "      <td>42.0</td>\n",
       "      <td>40073</td>\n",
       "      <td>3858.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2019-10-01</th>\n",
       "      <td>4517.453780</td>\n",
       "      <td>178.320454</td>\n",
       "      <td>31.462356</td>\n",
       "      <td>42.0</td>\n",
       "      <td>40073</td>\n",
       "      <td>3858.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2019-11-01</th>\n",
       "      <td>4158.198680</td>\n",
       "      <td>166.979433</td>\n",
       "      <td>31.462356</td>\n",
       "      <td>42.0</td>\n",
       "      <td>40073</td>\n",
       "      <td>3858.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                       total_imp_pc  china_imp_pc     tariff  emplvl_2017  \\\n",
       "area_fips time                                                              \n",
       "48123     2019-09-01  114485.752818  46449.517478  32.989823        600.0   \n",
       "          2019-10-01  115237.179352  40010.055528  32.989823        600.0   \n",
       "          2019-11-01   99380.664946  33064.010855  32.989823        600.0   \n",
       "          2019-12-01  103693.084538  34645.488358  32.989823        600.0   \n",
       "          2020-01-01  115467.020595  41316.863047  32.989434        600.0   \n",
       "36035     2019-09-01   97199.727453  45363.001413  32.940729       1231.0   \n",
       "          2019-10-01   98404.233825  39292.346525  32.940729       1231.0   \n",
       "          2019-11-01   83226.331981  32433.032949  32.940729       1231.0   \n",
       "          2019-12-01   84177.689456  33662.187227  32.940729       1231.0   \n",
       "          2020-01-01   95201.626814  39860.880563  32.940651       1231.0   \n",
       "2013      2020-01-01    4364.843586    212.706687  31.462373       1895.0   \n",
       "72067     2020-01-01    4364.843586    212.706687  31.462373         69.0   \n",
       "38091     2020-01-01    4364.843586    212.706687  31.462373         61.0   \n",
       "40073     2020-01-01    4364.843586    212.706687  31.462373         42.0   \n",
       "26031     2020-01-01    4364.843586    212.706687  31.462373         26.0   \n",
       "29063     2020-01-01    4364.843586    212.706687  31.462373         26.0   \n",
       "72117     2020-01-01    4364.843586    212.706687  31.462373         26.0   \n",
       "72055     2020-01-01    4364.843586    212.706687  31.462373         23.0   \n",
       "72067     2019-09-01    4181.763302    205.086046  31.462356         69.0   \n",
       "          2019-10-01    4517.453780    178.320454  31.462356         69.0   \n",
       "          2019-11-01    4158.198680    166.979433  31.462356         69.0   \n",
       "          2019-12-01    4223.539414    197.606290  31.462356         69.0   \n",
       "40073     2019-09-01    4181.763302    205.086046  31.462356         42.0   \n",
       "          2019-10-01    4517.453780    178.320454  31.462356         42.0   \n",
       "          2019-11-01    4158.198680    166.979433  31.462356         42.0   \n",
       "\n",
       "                       fips  total_employment  \n",
       "area_fips time                                 \n",
       "48123     2019-09-01  48123            4137.0  \n",
       "          2019-10-01  48123            4137.0  \n",
       "          2019-11-01  48123            4137.0  \n",
       "          2019-12-01  48123            4137.0  \n",
       "          2020-01-01  48123            4137.0  \n",
       "36035     2019-09-01  36035            9568.0  \n",
       "          2019-10-01  36035            9568.0  \n",
       "          2019-11-01  36035            9568.0  \n",
       "          2019-12-01  36035            9568.0  \n",
       "          2020-01-01  36035            9568.0  \n",
       "2013      2020-01-01   2013            1944.0  \n",
       "72067     2020-01-01  72067             768.0  \n",
       "38091     2020-01-01  38091             115.0  \n",
       "40073     2020-01-01  40073            3858.0  \n",
       "26031     2020-01-01  26031            4698.0  \n",
       "29063     2020-01-01  29063            1310.0  \n",
       "72117     2020-01-01  72117             739.0  \n",
       "72055     2020-01-01  72055             232.0  \n",
       "72067     2019-09-01  72067             768.0  \n",
       "          2019-10-01  72067             768.0  \n",
       "          2019-11-01  72067             768.0  \n",
       "          2019-12-01  72067             768.0  \n",
       "40073     2019-09-01  40073            3858.0  \n",
       "          2019-10-01  40073            3858.0  \n",
       "          2019-11-01  40073            3858.0  "
      ]
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**One more adjustment.** Notice that in the function, when we are merging, we are droping all the NAICS codes without trade. So these measures (total trade, china trade, and tariffs) are only conditional on being traded. This only matters in so far as the denominator, the ``df[\"annual_avg_emplvl\"].sum()`` is concerned. \n",
    "\n",
    "To make the adjustment then, we multiply the employment measure in the denominator and then divide through by the ``total_employment`` measure. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [],
   "source": [
    "trade_county[\"tariff\"] = (trade_county[\"emplvl_2017\"]/\n",
    "                              trade_county[\"total_employment\"])*trade_county[\"tariff\"]\n",
    "\n",
    "trade_county[\"china_imp_pc\"] = (trade_county[\"emplvl_2017\"]/\n",
    "                                    trade_county[\"total_employment\"])*trade_county[\"china_imp_pc\"]\n",
    "\n",
    "trade_county[\"total_imp_pc\"] = (trade_county[\"emplvl_2017\"]/\n",
    "                                    trade_county[\"total_employment\"])*trade_county[\"total_imp_pc\"]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "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_imp_pc</th>\n",
       "      <th>china_imp_pc</th>\n",
       "      <th>tariff</th>\n",
       "      <th>emplvl_2017</th>\n",
       "      <th>fips</th>\n",
       "      <th>total_employment</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",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th rowspan=\"10\" valign=\"top\">2013</th>\n",
       "      <th>2020-01-01</th>\n",
       "      <td>4254.824381</td>\n",
       "      <td>207.345253</td>\n",
       "      <td>30.669340</td>\n",
       "      <td>1895.0</td>\n",
       "      <td>2013</td>\n",
       "      <td>1944.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2019-09-01</th>\n",
       "      <td>4076.358774</td>\n",
       "      <td>199.916696</td>\n",
       "      <td>30.669323</td>\n",
       "      <td>1895.0</td>\n",
       "      <td>2013</td>\n",
       "      <td>1944.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2019-10-01</th>\n",
       "      <td>4403.587918</td>\n",
       "      <td>173.825751</td>\n",
       "      <td>30.669323</td>\n",
       "      <td>1895.0</td>\n",
       "      <td>2013</td>\n",
       "      <td>1944.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2019-11-01</th>\n",
       "      <td>4053.388117</td>\n",
       "      <td>162.770589</td>\n",
       "      <td>30.669323</td>\n",
       "      <td>1895.0</td>\n",
       "      <td>2013</td>\n",
       "      <td>1944.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2019-12-01</th>\n",
       "      <td>4117.081888</td>\n",
       "      <td>192.625473</td>\n",
       "      <td>30.669323</td>\n",
       "      <td>1895.0</td>\n",
       "      <td>2013</td>\n",
       "      <td>1944.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2020-02-01</th>\n",
       "      <td>3780.133680</td>\n",
       "      <td>155.089665</td>\n",
       "      <td>29.051986</td>\n",
       "      <td>1895.0</td>\n",
       "      <td>2013</td>\n",
       "      <td>1944.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2020-03-01</th>\n",
       "      <td>4708.596839</td>\n",
       "      <td>119.131824</td>\n",
       "      <td>29.051986</td>\n",
       "      <td>1895.0</td>\n",
       "      <td>2013</td>\n",
       "      <td>1944.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2019-06-01</th>\n",
       "      <td>4117.486294</td>\n",
       "      <td>177.746891</td>\n",
       "      <td>27.434596</td>\n",
       "      <td>1895.0</td>\n",
       "      <td>2013</td>\n",
       "      <td>1944.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2019-07-01</th>\n",
       "      <td>4432.215964</td>\n",
       "      <td>189.094610</td>\n",
       "      <td>27.434596</td>\n",
       "      <td>1895.0</td>\n",
       "      <td>2013</td>\n",
       "      <td>1944.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2019-08-01</th>\n",
       "      <td>4284.659687</td>\n",
       "      <td>192.246980</td>\n",
       "      <td>27.434594</td>\n",
       "      <td>1895.0</td>\n",
       "      <td>2013</td>\n",
       "      <td>1944.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>53023</th>\n",
       "      <th>2020-01-01</th>\n",
       "      <td>7494.167486</td>\n",
       "      <td>86.977535</td>\n",
       "      <td>26.672198</td>\n",
       "      <td>37.0</td>\n",
       "      <td>53023</td>\n",
       "      <td>37.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28055</th>\n",
       "      <th>2020-01-01</th>\n",
       "      <td>7494.167486</td>\n",
       "      <td>86.977535</td>\n",
       "      <td>26.672198</td>\n",
       "      <td>21.0</td>\n",
       "      <td>28055</td>\n",
       "      <td>21.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"4\" valign=\"top\">53023</th>\n",
       "      <th>2019-09-01</th>\n",
       "      <td>5059.902644</td>\n",
       "      <td>71.639175</td>\n",
       "      <td>26.670497</td>\n",
       "      <td>37.0</td>\n",
       "      <td>53023</td>\n",
       "      <td>37.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2019-10-01</th>\n",
       "      <td>5420.296106</td>\n",
       "      <td>77.641962</td>\n",
       "      <td>26.670497</td>\n",
       "      <td>37.0</td>\n",
       "      <td>53023</td>\n",
       "      <td>37.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2019-11-01</th>\n",
       "      <td>5437.728177</td>\n",
       "      <td>73.530644</td>\n",
       "      <td>26.670497</td>\n",
       "      <td>37.0</td>\n",
       "      <td>53023</td>\n",
       "      <td>37.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2019-12-01</th>\n",
       "      <td>6295.273914</td>\n",
       "      <td>87.279916</td>\n",
       "      <td>26.670497</td>\n",
       "      <td>37.0</td>\n",
       "      <td>53023</td>\n",
       "      <td>37.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"4\" valign=\"top\">28055</th>\n",
       "      <th>2019-09-01</th>\n",
       "      <td>5059.902644</td>\n",
       "      <td>71.639175</td>\n",
       "      <td>26.670497</td>\n",
       "      <td>21.0</td>\n",
       "      <td>28055</td>\n",
       "      <td>21.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2019-10-01</th>\n",
       "      <td>5420.296106</td>\n",
       "      <td>77.641962</td>\n",
       "      <td>26.670497</td>\n",
       "      <td>21.0</td>\n",
       "      <td>28055</td>\n",
       "      <td>21.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2019-11-01</th>\n",
       "      <td>5437.728177</td>\n",
       "      <td>73.530644</td>\n",
       "      <td>26.670497</td>\n",
       "      <td>21.0</td>\n",
       "      <td>28055</td>\n",
       "      <td>21.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2019-12-01</th>\n",
       "      <td>6295.273914</td>\n",
       "      <td>87.279916</td>\n",
       "      <td>26.670497</td>\n",
       "      <td>21.0</td>\n",
       "      <td>28055</td>\n",
       "      <td>21.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>53023</th>\n",
       "      <th>2020-02-01</th>\n",
       "      <td>6927.020112</td>\n",
       "      <td>60.717113</td>\n",
       "      <td>25.906499</td>\n",
       "      <td>37.0</td>\n",
       "      <td>53023</td>\n",
       "      <td>37.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28055</th>\n",
       "      <th>2020-02-01</th>\n",
       "      <td>6927.020112</td>\n",
       "      <td>60.717113</td>\n",
       "      <td>25.906499</td>\n",
       "      <td>21.0</td>\n",
       "      <td>28055</td>\n",
       "      <td>21.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>53023</th>\n",
       "      <th>2020-03-01</th>\n",
       "      <td>7467.221724</td>\n",
       "      <td>43.383713</td>\n",
       "      <td>25.906441</td>\n",
       "      <td>37.0</td>\n",
       "      <td>53023</td>\n",
       "      <td>37.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28055</th>\n",
       "      <th>2020-03-01</th>\n",
       "      <td>7467.221724</td>\n",
       "      <td>43.383713</td>\n",
       "      <td>25.906441</td>\n",
       "      <td>21.0</td>\n",
       "      <td>28055</td>\n",
       "      <td>21.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>53023</th>\n",
       "      <th>2019-06-01</th>\n",
       "      <td>5611.339600</td>\n",
       "      <td>82.188215</td>\n",
       "      <td>25.142376</td>\n",
       "      <td>37.0</td>\n",
       "      <td>53023</td>\n",
       "      <td>37.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                      total_imp_pc  china_imp_pc     tariff  emplvl_2017  \\\n",
       "area_fips time                                                             \n",
       "2013      2020-01-01   4254.824381    207.345253  30.669340       1895.0   \n",
       "          2019-09-01   4076.358774    199.916696  30.669323       1895.0   \n",
       "          2019-10-01   4403.587918    173.825751  30.669323       1895.0   \n",
       "          2019-11-01   4053.388117    162.770589  30.669323       1895.0   \n",
       "          2019-12-01   4117.081888    192.625473  30.669323       1895.0   \n",
       "          2020-02-01   3780.133680    155.089665  29.051986       1895.0   \n",
       "          2020-03-01   4708.596839    119.131824  29.051986       1895.0   \n",
       "          2019-06-01   4117.486294    177.746891  27.434596       1895.0   \n",
       "          2019-07-01   4432.215964    189.094610  27.434596       1895.0   \n",
       "          2019-08-01   4284.659687    192.246980  27.434594       1895.0   \n",
       "53023     2020-01-01   7494.167486     86.977535  26.672198         37.0   \n",
       "28055     2020-01-01   7494.167486     86.977535  26.672198         21.0   \n",
       "53023     2019-09-01   5059.902644     71.639175  26.670497         37.0   \n",
       "          2019-10-01   5420.296106     77.641962  26.670497         37.0   \n",
       "          2019-11-01   5437.728177     73.530644  26.670497         37.0   \n",
       "          2019-12-01   6295.273914     87.279916  26.670497         37.0   \n",
       "28055     2019-09-01   5059.902644     71.639175  26.670497         21.0   \n",
       "          2019-10-01   5420.296106     77.641962  26.670497         21.0   \n",
       "          2019-11-01   5437.728177     73.530644  26.670497         21.0   \n",
       "          2019-12-01   6295.273914     87.279916  26.670497         21.0   \n",
       "53023     2020-02-01   6927.020112     60.717113  25.906499         37.0   \n",
       "28055     2020-02-01   6927.020112     60.717113  25.906499         21.0   \n",
       "53023     2020-03-01   7467.221724     43.383713  25.906441         37.0   \n",
       "28055     2020-03-01   7467.221724     43.383713  25.906441         21.0   \n",
       "53023     2019-06-01   5611.339600     82.188215  25.142376         37.0   \n",
       "\n",
       "                       fips  total_employment  \n",
       "area_fips time                                 \n",
       "2013      2020-01-01   2013            1944.0  \n",
       "          2019-09-01   2013            1944.0  \n",
       "          2019-10-01   2013            1944.0  \n",
       "          2019-11-01   2013            1944.0  \n",
       "          2019-12-01   2013            1944.0  \n",
       "          2020-02-01   2013            1944.0  \n",
       "          2020-03-01   2013            1944.0  \n",
       "          2019-06-01   2013            1944.0  \n",
       "          2019-07-01   2013            1944.0  \n",
       "          2019-08-01   2013            1944.0  \n",
       "53023     2020-01-01  53023              37.0  \n",
       "28055     2020-01-01  28055              21.0  \n",
       "53023     2019-09-01  53023              37.0  \n",
       "          2019-10-01  53023              37.0  \n",
       "          2019-11-01  53023              37.0  \n",
       "          2019-12-01  53023              37.0  \n",
       "28055     2019-09-01  28055              21.0  \n",
       "          2019-10-01  28055              21.0  \n",
       "          2019-11-01  28055              21.0  \n",
       "          2019-12-01  28055              21.0  \n",
       "53023     2020-02-01  53023              37.0  \n",
       "28055     2020-02-01  28055              21.0  \n",
       "53023     2020-03-01  53023              37.0  \n",
       "28055     2020-03-01  28055              21.0  \n",
       "53023     2019-06-01  53023              37.0  "
      ]
     },
     "execution_count": 27,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "trade_county.sort_values(by = [\"tariff\",\"emplvl_2017\"], ascending = False).head(25)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "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)\n",
    "\n",
    "county_2017.set_index([\"GEOFIPS\"], inplace = True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {},
   "outputs": [],
   "source": [
    "trade_county.reset_index(inplace = True)\n",
    "\n",
    "trade_county[\"int_area_fips\"] = trade_county[\"area_fips\"].astype(int)\n",
    "\n",
    "trade_county = trade_county.merge(county_2017[[\"2017_income\",\"2017_population\"]],\n",
    "                                  left_on = \"int_area_fips\", right_index = True, how = \"left\")\n",
    "\n",
    "#trade_employ.drop(labels = \"index\", axis = 1, inplace = True)\n",
    "\n",
    "trade_county.set_index([\"area_fips\", \"time\"],inplace = True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "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_imp_pc</th>\n",
       "      <th>china_imp_pc</th>\n",
       "      <th>tariff</th>\n",
       "      <th>emplvl_2017</th>\n",
       "      <th>fips</th>\n",
       "      <th>total_employment</th>\n",
       "      <th>int_area_fips</th>\n",
       "      <th>2017_income</th>\n",
       "      <th>2017_population</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",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th rowspan=\"5\" valign=\"top\">10001</th>\n",
       "      <th>2015-01-01</th>\n",
       "      <td>455.305454</td>\n",
       "      <td>25.407092</td>\n",
       "      <td>0.751445</td>\n",
       "      <td>2843.0</td>\n",
       "      <td>10001</td>\n",
       "      <td>29514.0</td>\n",
       "      <td>10001</td>\n",
       "      <td>57647.0</td>\n",
       "      <td>173145.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2015-02-01</th>\n",
       "      <td>404.659655</td>\n",
       "      <td>20.680831</td>\n",
       "      <td>0.751448</td>\n",
       "      <td>2843.0</td>\n",
       "      <td>10001</td>\n",
       "      <td>29514.0</td>\n",
       "      <td>10001</td>\n",
       "      <td>57647.0</td>\n",
       "      <td>173145.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2015-03-01</th>\n",
       "      <td>501.924814</td>\n",
       "      <td>26.873311</td>\n",
       "      <td>0.751466</td>\n",
       "      <td>2843.0</td>\n",
       "      <td>10001</td>\n",
       "      <td>29514.0</td>\n",
       "      <td>10001</td>\n",
       "      <td>57647.0</td>\n",
       "      <td>173145.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2015-04-01</th>\n",
       "      <td>491.740587</td>\n",
       "      <td>25.616221</td>\n",
       "      <td>0.751465</td>\n",
       "      <td>2843.0</td>\n",
       "      <td>10001</td>\n",
       "      <td>29514.0</td>\n",
       "      <td>10001</td>\n",
       "      <td>57647.0</td>\n",
       "      <td>173145.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2015-05-01</th>\n",
       "      <td>459.010800</td>\n",
       "      <td>26.146841</td>\n",
       "      <td>0.751465</td>\n",
       "      <td>2843.0</td>\n",
       "      <td>10001</td>\n",
       "      <td>29514.0</td>\n",
       "      <td>10001</td>\n",
       "      <td>57647.0</td>\n",
       "      <td>173145.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                      total_imp_pc  china_imp_pc    tariff  emplvl_2017  \\\n",
       "area_fips time                                                            \n",
       "10001     2015-01-01    455.305454     25.407092  0.751445       2843.0   \n",
       "          2015-02-01    404.659655     20.680831  0.751448       2843.0   \n",
       "          2015-03-01    501.924814     26.873311  0.751466       2843.0   \n",
       "          2015-04-01    491.740587     25.616221  0.751465       2843.0   \n",
       "          2015-05-01    459.010800     26.146841  0.751465       2843.0   \n",
       "\n",
       "                       fips  total_employment  int_area_fips  2017_income  \\\n",
       "area_fips time                                                              \n",
       "10001     2015-01-01  10001           29514.0          10001      57647.0   \n",
       "          2015-02-01  10001           29514.0          10001      57647.0   \n",
       "          2015-03-01  10001           29514.0          10001      57647.0   \n",
       "          2015-04-01  10001           29514.0          10001      57647.0   \n",
       "          2015-05-01  10001           29514.0          10001      57647.0   \n",
       "\n",
       "                      2017_population  \n",
       "area_fips time                         \n",
       "10001     2015-01-01         173145.0  \n",
       "          2015-02-01         173145.0  \n",
       "          2015-03-01         173145.0  \n",
       "          2015-04-01         173145.0  \n",
       "          2015-05-01         173145.0  "
      ]
     },
     "execution_count": 31,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "trade_county.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\ProgramData\\Anaconda3\\lib\\site-packages\\pyarrow\\pandas_compat.py:383: FutureWarning: RangeIndex._start is deprecated and will be removed in a future version. Use RangeIndex.start instead\n",
      "  'start': level._start,\n",
      "C:\\ProgramData\\Anaconda3\\lib\\site-packages\\pyarrow\\pandas_compat.py:384: FutureWarning: RangeIndex._stop is deprecated and will be removed in a future version. Use RangeIndex.stop instead\n",
      "  'stop': level._stop,\n",
      "C:\\ProgramData\\Anaconda3\\lib\\site-packages\\pyarrow\\pandas_compat.py:385: FutureWarning: RangeIndex._step is deprecated and will be removed in a future version. Use RangeIndex.step instead\n",
      "  'step': level._step\n"
     ]
    }
   ],
   "source": [
    "file_path =  \".\\\\data\"+ \"\\\\imports_trade_data_2020.parquet\"\n",
    "\n",
    "pq.write_table(pa.Table.from_pandas(trade_county.reset_index()), file_path)"
   ]
  },
  {
   "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.7.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}