phase-one-trade.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 43,
   "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": "markdown",
   "metadata": {},
   "source": [
    "This file creates the trade file behind the [Phase One Tracker](https://www.tradewartracker.com/). It proceeds in several steps.\n",
    "\n",
    "1. Grabs the trade data\n",
    "\n",
    "2. Constructs the relavent Phase One product catagories and the associated goals/targets.\n",
    "\n",
    "3. Maps the data to the county level."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "metadata": {},
   "outputs": [],
   "source": [
    "trade_type = \"exports\"\n",
    "\n",
    "my_key = \"&key=34e40301bda77077e24c859c6c6c0b721ad73fc7\"\n",
    "# This is my key. I'm nice and I have it posted. If you will be doing more with this\n",
    "# please get your own key!"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "---\n",
    "#### Grabe the trade data using the Census's API"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "metadata": {},
   "outputs": [],
   "source": [
    "def census_trade(url, trade_type, country, product_level):\n",
    "    \n",
    "    r = requests.get(url) \n",
    "    \n",
    "    print(r)\n",
    "    \n",
    "    df = pd.DataFrame(r.json()[1:]) # This then converts it to a dataframe\n",
    "    # Note that the first entry is the labels\n",
    "\n",
    "    df.columns = r.json()[0]\n",
    "\n",
    "    df.time = pd.to_datetime(df.time, format=\"%Y-%m\")\n",
    "    # This is so I can call this correctly...\n",
    "    \n",
    "    if trade_type == \"imports\":\n",
    "        \n",
    "        trade_label = country + \"_\" + trade_type\n",
    "        \n",
    "        df[trade_label] = df[\"CON_VAL_MO\"].astype(float)\n",
    "        \n",
    "        df[product_level] = df[\"I_COMMODITY\"].astype(str)\n",
    "        \n",
    "        df.drop([\"CON_VAL_MO\", \"I_COMMODITY\", \"COMM_LVL\"], axis = 1, inplace = True)\n",
    "        \n",
    "    if trade_type == \"exports\":\n",
    "    \n",
    "        trade_label = country + \"_\" + trade_type\n",
    "        \n",
    "        df[trade_label] = df[\"ALL_VAL_MO\"].astype(float)\n",
    "\n",
    "        df[product_level] = df[\"E_COMMODITY\"].astype(str)\n",
    "        \n",
    "        df[\"china_exports_quant\"] = df[\"QTY_1_MO\"].astype(float)\n",
    "    \n",
    "        df[\"quant_type\"] = df[\"UNIT_QY1\"].astype(str)\n",
    "        \n",
    "        df.drop([\"ALL_VAL_MO\", \"E_COMMODITY\", \"COMM_LVL\", \"QTY_1_MO\", \"UNIT_QY1\"], axis = 1, inplace = True)\n",
    "    \n",
    "    return df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 46,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<Response [200]>\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "Timestamp('2020-10-01 00:00:00')"
      ]
     },
     "execution_count": 46,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "end_use = \"hs?get=E_COMMODITY,ALL_VAL_MO,UNIT_QY1,QTY_1_MO\"\n",
    "\n",
    "url = \"https://api.census.gov/data/timeseries/intltrade/exports/\" + end_use \n",
    "url = url + my_key + \"&time==from+2013-01\" + \"&COMM_LVL=HS6\"\n",
    "\n",
    "url = url + \"&CTY_CODE=5700\"\n",
    "\n",
    "df = census_trade(url, trade_type, \"china\", \"hs6\")\n",
    "\n",
    "df[\"hs4\"] = df[\"hs6\"].str[0:4]\n",
    "\n",
    "df.time.max()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "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>time</th>\n",
       "      <th>CTY_CODE</th>\n",
       "      <th>china_exports</th>\n",
       "      <th>hs6</th>\n",
       "      <th>china_exports_quant</th>\n",
       "      <th>quant_type</th>\n",
       "      <th>hs4</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2013-01-01</td>\n",
       "      <td>5700</td>\n",
       "      <td>287000.0</td>\n",
       "      <td>481031</td>\n",
       "      <td>0.0</td>\n",
       "      <td>-</td>\n",
       "      <td>4810</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2013-01-01</td>\n",
       "      <td>5700</td>\n",
       "      <td>311650.0</td>\n",
       "      <td>481160</td>\n",
       "      <td>0.0</td>\n",
       "      <td>-</td>\n",
       "      <td>4811</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2013-01-01</td>\n",
       "      <td>5700</td>\n",
       "      <td>63701.0</td>\n",
       "      <td>481620</td>\n",
       "      <td>0.0</td>\n",
       "      <td>-</td>\n",
       "      <td>4816</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2013-01-01</td>\n",
       "      <td>5700</td>\n",
       "      <td>40265.0</td>\n",
       "      <td>490290</td>\n",
       "      <td>0.0</td>\n",
       "      <td>-</td>\n",
       "      <td>4902</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2013-01-01</td>\n",
       "      <td>5700</td>\n",
       "      <td>324939.0</td>\n",
       "      <td>480431</td>\n",
       "      <td>0.0</td>\n",
       "      <td>-</td>\n",
       "      <td>4804</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        time CTY_CODE  china_exports     hs6  china_exports_quant quant_type  \\\n",
       "0 2013-01-01     5700       287000.0  481031                  0.0          -   \n",
       "1 2013-01-01     5700       311650.0  481160                  0.0          -   \n",
       "2 2013-01-01     5700        63701.0  481620                  0.0          -   \n",
       "3 2013-01-01     5700        40265.0  490290                  0.0          -   \n",
       "4 2013-01-01     5700       324939.0  480431                  0.0          -   \n",
       "\n",
       "    hs4  \n",
       "0  4810  \n",
       "1  4811  \n",
       "2  4816  \n",
       "3  4902  \n",
       "4  4804  "
      ]
     },
     "execution_count": 47,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "---\n",
    "#### Bring in the Phase One Product list"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 48,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "['2701', '2709', '2710', '2711', '2711', '2711', '2711', '2713', '2713', '2905']\n"
     ]
    }
   ],
   "source": [
    "dfproducts = pd.read_csv(\".\\\\data\"+ \"\\\\annex-6-1.csv\", dtype = {\"hs4\": str, \"hs4_o\": str})\n",
    "\n",
    "not_energy = dfproducts.high_catagory != \"3. Energy\"\n",
    "\n",
    "energy_list = list(dfproducts.loc[~not_energy,\"hs4\"])\n",
    "\n",
    "print(energy_list)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Next step, we will remove the energy products, these will be added on below from the ``phase-one-energy`` notebook"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 49,
   "metadata": {},
   "outputs": [],
   "source": [
    "df = df[df[\"hs4\"].isin(energy_list) == False]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now merge the non energy products with the product lists"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 50,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_phaseone = df.merge(dfproducts, left_on = \"hs4\", right_on = \"hs4\", how = \"left\", indicator = True)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Grabe the column names so the energy data will be the same order otherwise pandas complains"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 51,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "['time', 'CTY_CODE', 'china_exports', 'hs6', 'china_exports_quant', 'quant_type', 'hs4', 'hs4_o', 'description', 'low_catagory', 'high_catagory', '_merge']\n"
     ]
    }
   ],
   "source": [
    "cnames = list(df_phaseone.columns)\n",
    "print(cnames)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Then read in the energy data set"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 52,
   "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>time</th>\n",
       "      <th>CTY_CODE</th>\n",
       "      <th>hs4_o</th>\n",
       "      <th>hs6</th>\n",
       "      <th>china_exports</th>\n",
       "      <th>china_exports_quant</th>\n",
       "      <th>quant_type</th>\n",
       "      <th>description</th>\n",
       "      <th>low_catagory</th>\n",
       "      <th>high_catagory</th>\n",
       "      <th>hs4</th>\n",
       "      <th>_merge</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>561</th>\n",
       "      <td>2013-04-01</td>\n",
       "      <td>5700</td>\n",
       "      <td>271111</td>\n",
       "      <td>271111</td>\n",
       "      <td>119031.0</td>\n",
       "      <td>996.0</td>\n",
       "      <td>M3</td>\n",
       "      <td>Liquefied natural gas</td>\n",
       "      <td>Liquefied natural gas</td>\n",
       "      <td>3. Energy</td>\n",
       "      <td>2711</td>\n",
       "      <td>both</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>562</th>\n",
       "      <td>2013-05-01</td>\n",
       "      <td>5700</td>\n",
       "      <td>271111</td>\n",
       "      <td>271111</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>M3</td>\n",
       "      <td>Liquefied natural gas</td>\n",
       "      <td>Liquefied natural gas</td>\n",
       "      <td>3. Energy</td>\n",
       "      <td>2711</td>\n",
       "      <td>both</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>563</th>\n",
       "      <td>2013-06-01</td>\n",
       "      <td>5700</td>\n",
       "      <td>271111</td>\n",
       "      <td>271111</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>M3</td>\n",
       "      <td>Liquefied natural gas</td>\n",
       "      <td>Liquefied natural gas</td>\n",
       "      <td>3. Energy</td>\n",
       "      <td>2711</td>\n",
       "      <td>both</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>564</th>\n",
       "      <td>2013-07-01</td>\n",
       "      <td>5700</td>\n",
       "      <td>271111</td>\n",
       "      <td>271111</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>M3</td>\n",
       "      <td>Liquefied natural gas</td>\n",
       "      <td>Liquefied natural gas</td>\n",
       "      <td>3. Energy</td>\n",
       "      <td>2711</td>\n",
       "      <td>both</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>565</th>\n",
       "      <td>2013-08-01</td>\n",
       "      <td>5700</td>\n",
       "      <td>271111</td>\n",
       "      <td>271111</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>M3</td>\n",
       "      <td>Liquefied natural gas</td>\n",
       "      <td>Liquefied natural gas</td>\n",
       "      <td>3. Energy</td>\n",
       "      <td>2711</td>\n",
       "      <td>both</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>626</th>\n",
       "      <td>2020-06-01</td>\n",
       "      <td>5700</td>\n",
       "      <td>271111</td>\n",
       "      <td>271111</td>\n",
       "      <td>40687326.0</td>\n",
       "      <td>332510.0</td>\n",
       "      <td>M3</td>\n",
       "      <td>Liquefied natural gas</td>\n",
       "      <td>Liquefied natural gas</td>\n",
       "      <td>3. Energy</td>\n",
       "      <td>2711</td>\n",
       "      <td>both</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>627</th>\n",
       "      <td>2020-07-01</td>\n",
       "      <td>5700</td>\n",
       "      <td>271111</td>\n",
       "      <td>271111</td>\n",
       "      <td>44254302.0</td>\n",
       "      <td>328083.0</td>\n",
       "      <td>M3</td>\n",
       "      <td>Liquefied natural gas</td>\n",
       "      <td>Liquefied natural gas</td>\n",
       "      <td>3. Energy</td>\n",
       "      <td>2711</td>\n",
       "      <td>both</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>628</th>\n",
       "      <td>2020-08-01</td>\n",
       "      <td>5700</td>\n",
       "      <td>271111</td>\n",
       "      <td>271111</td>\n",
       "      <td>52565450.0</td>\n",
       "      <td>318840.0</td>\n",
       "      <td>M3</td>\n",
       "      <td>Liquefied natural gas</td>\n",
       "      <td>Liquefied natural gas</td>\n",
       "      <td>3. Energy</td>\n",
       "      <td>2711</td>\n",
       "      <td>both</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>629</th>\n",
       "      <td>2020-09-01</td>\n",
       "      <td>5700</td>\n",
       "      <td>271111</td>\n",
       "      <td>271111</td>\n",
       "      <td>47949278.0</td>\n",
       "      <td>340983.0</td>\n",
       "      <td>M3</td>\n",
       "      <td>Liquefied natural gas</td>\n",
       "      <td>Liquefied natural gas</td>\n",
       "      <td>3. Energy</td>\n",
       "      <td>2711</td>\n",
       "      <td>both</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>630</th>\n",
       "      <td>2020-10-01</td>\n",
       "      <td>5700</td>\n",
       "      <td>271111</td>\n",
       "      <td>271111</td>\n",
       "      <td>198693670.0</td>\n",
       "      <td>1652567.0</td>\n",
       "      <td>M3</td>\n",
       "      <td>Liquefied natural gas</td>\n",
       "      <td>Liquefied natural gas</td>\n",
       "      <td>3. Energy</td>\n",
       "      <td>2711</td>\n",
       "      <td>both</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>70 rows × 12 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "          time CTY_CODE   hs4_o     hs6  china_exports  china_exports_quant  \\\n",
       "561 2013-04-01     5700  271111  271111       119031.0                996.0   \n",
       "562 2013-05-01     5700  271111  271111            0.0                  0.0   \n",
       "563 2013-06-01     5700  271111  271111            0.0                  0.0   \n",
       "564 2013-07-01     5700  271111  271111            0.0                  0.0   \n",
       "565 2013-08-01     5700  271111  271111            0.0                  0.0   \n",
       "..         ...      ...     ...     ...            ...                  ...   \n",
       "626 2020-06-01     5700  271111  271111     40687326.0             332510.0   \n",
       "627 2020-07-01     5700  271111  271111     44254302.0             328083.0   \n",
       "628 2020-08-01     5700  271111  271111     52565450.0             318840.0   \n",
       "629 2020-09-01     5700  271111  271111     47949278.0             340983.0   \n",
       "630 2020-10-01     5700  271111  271111    198693670.0            1652567.0   \n",
       "\n",
       "    quant_type            description           low_catagory high_catagory  \\\n",
       "561         M3  Liquefied natural gas  Liquefied natural gas     3. Energy   \n",
       "562         M3  Liquefied natural gas  Liquefied natural gas     3. Energy   \n",
       "563         M3  Liquefied natural gas  Liquefied natural gas     3. Energy   \n",
       "564         M3  Liquefied natural gas  Liquefied natural gas     3. Energy   \n",
       "565         M3  Liquefied natural gas  Liquefied natural gas     3. Energy   \n",
       "..         ...                    ...                    ...           ...   \n",
       "626         M3  Liquefied natural gas  Liquefied natural gas     3. Energy   \n",
       "627         M3  Liquefied natural gas  Liquefied natural gas     3. Energy   \n",
       "628         M3  Liquefied natural gas  Liquefied natural gas     3. Energy   \n",
       "629         M3  Liquefied natural gas  Liquefied natural gas     3. Energy   \n",
       "630         M3  Liquefied natural gas  Liquefied natural gas     3. Energy   \n",
       "\n",
       "      hs4 _merge  \n",
       "561  2711   both  \n",
       "562  2711   both  \n",
       "563  2711   both  \n",
       "564  2711   both  \n",
       "565  2711   both  \n",
       "..    ...    ...  \n",
       "626  2711   both  \n",
       "627  2711   both  \n",
       "628  2711   both  \n",
       "629  2711   both  \n",
       "630  2711   both  \n",
       "\n",
       "[70 rows x 12 columns]"
      ]
     },
     "execution_count": 52,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "file = \".\\\\data\"+ \"\\\\phaseone-energy.parquet\"\n",
    "\n",
    "df_energy = pq.read_table(file).to_pandas()\n",
    "\n",
    "df_energy[df_energy.low_catagory == \"Liquefied natural gas\"]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "And then append it to the main dataframe"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 53,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_phaseone = df_phaseone.append(df_energy)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 54,
   "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>time</th>\n",
       "      <th>CTY_CODE</th>\n",
       "      <th>china_exports</th>\n",
       "      <th>hs6</th>\n",
       "      <th>china_exports_quant</th>\n",
       "      <th>quant_type</th>\n",
       "      <th>hs4</th>\n",
       "      <th>hs4_o</th>\n",
       "      <th>description</th>\n",
       "      <th>low_catagory</th>\n",
       "      <th>high_catagory</th>\n",
       "      <th>_merge</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>561</th>\n",
       "      <td>2013-04-01</td>\n",
       "      <td>5700</td>\n",
       "      <td>119031.0</td>\n",
       "      <td>271111</td>\n",
       "      <td>996.0</td>\n",
       "      <td>M3</td>\n",
       "      <td>2711</td>\n",
       "      <td>271111</td>\n",
       "      <td>Liquefied natural gas</td>\n",
       "      <td>Liquefied natural gas</td>\n",
       "      <td>3. Energy</td>\n",
       "      <td>both</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>562</th>\n",
       "      <td>2013-05-01</td>\n",
       "      <td>5700</td>\n",
       "      <td>0.0</td>\n",
       "      <td>271111</td>\n",
       "      <td>0.0</td>\n",
       "      <td>M3</td>\n",
       "      <td>2711</td>\n",
       "      <td>271111</td>\n",
       "      <td>Liquefied natural gas</td>\n",
       "      <td>Liquefied natural gas</td>\n",
       "      <td>3. Energy</td>\n",
       "      <td>both</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>563</th>\n",
       "      <td>2013-06-01</td>\n",
       "      <td>5700</td>\n",
       "      <td>0.0</td>\n",
       "      <td>271111</td>\n",
       "      <td>0.0</td>\n",
       "      <td>M3</td>\n",
       "      <td>2711</td>\n",
       "      <td>271111</td>\n",
       "      <td>Liquefied natural gas</td>\n",
       "      <td>Liquefied natural gas</td>\n",
       "      <td>3. Energy</td>\n",
       "      <td>both</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>564</th>\n",
       "      <td>2013-07-01</td>\n",
       "      <td>5700</td>\n",
       "      <td>0.0</td>\n",
       "      <td>271111</td>\n",
       "      <td>0.0</td>\n",
       "      <td>M3</td>\n",
       "      <td>2711</td>\n",
       "      <td>271111</td>\n",
       "      <td>Liquefied natural gas</td>\n",
       "      <td>Liquefied natural gas</td>\n",
       "      <td>3. Energy</td>\n",
       "      <td>both</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>565</th>\n",
       "      <td>2013-08-01</td>\n",
       "      <td>5700</td>\n",
       "      <td>0.0</td>\n",
       "      <td>271111</td>\n",
       "      <td>0.0</td>\n",
       "      <td>M3</td>\n",
       "      <td>2711</td>\n",
       "      <td>271111</td>\n",
       "      <td>Liquefied natural gas</td>\n",
       "      <td>Liquefied natural gas</td>\n",
       "      <td>3. Energy</td>\n",
       "      <td>both</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>626</th>\n",
       "      <td>2020-06-01</td>\n",
       "      <td>5700</td>\n",
       "      <td>40687326.0</td>\n",
       "      <td>271111</td>\n",
       "      <td>332510.0</td>\n",
       "      <td>M3</td>\n",
       "      <td>2711</td>\n",
       "      <td>271111</td>\n",
       "      <td>Liquefied natural gas</td>\n",
       "      <td>Liquefied natural gas</td>\n",
       "      <td>3. Energy</td>\n",
       "      <td>both</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>627</th>\n",
       "      <td>2020-07-01</td>\n",
       "      <td>5700</td>\n",
       "      <td>44254302.0</td>\n",
       "      <td>271111</td>\n",
       "      <td>328083.0</td>\n",
       "      <td>M3</td>\n",
       "      <td>2711</td>\n",
       "      <td>271111</td>\n",
       "      <td>Liquefied natural gas</td>\n",
       "      <td>Liquefied natural gas</td>\n",
       "      <td>3. Energy</td>\n",
       "      <td>both</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>628</th>\n",
       "      <td>2020-08-01</td>\n",
       "      <td>5700</td>\n",
       "      <td>52565450.0</td>\n",
       "      <td>271111</td>\n",
       "      <td>318840.0</td>\n",
       "      <td>M3</td>\n",
       "      <td>2711</td>\n",
       "      <td>271111</td>\n",
       "      <td>Liquefied natural gas</td>\n",
       "      <td>Liquefied natural gas</td>\n",
       "      <td>3. Energy</td>\n",
       "      <td>both</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>629</th>\n",
       "      <td>2020-09-01</td>\n",
       "      <td>5700</td>\n",
       "      <td>47949278.0</td>\n",
       "      <td>271111</td>\n",
       "      <td>340983.0</td>\n",
       "      <td>M3</td>\n",
       "      <td>2711</td>\n",
       "      <td>271111</td>\n",
       "      <td>Liquefied natural gas</td>\n",
       "      <td>Liquefied natural gas</td>\n",
       "      <td>3. Energy</td>\n",
       "      <td>both</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>630</th>\n",
       "      <td>2020-10-01</td>\n",
       "      <td>5700</td>\n",
       "      <td>198693670.0</td>\n",
       "      <td>271111</td>\n",
       "      <td>1652567.0</td>\n",
       "      <td>M3</td>\n",
       "      <td>2711</td>\n",
       "      <td>271111</td>\n",
       "      <td>Liquefied natural gas</td>\n",
       "      <td>Liquefied natural gas</td>\n",
       "      <td>3. Energy</td>\n",
       "      <td>both</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>70 rows × 12 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "          time CTY_CODE  china_exports     hs6  china_exports_quant  \\\n",
       "561 2013-04-01     5700       119031.0  271111                996.0   \n",
       "562 2013-05-01     5700            0.0  271111                  0.0   \n",
       "563 2013-06-01     5700            0.0  271111                  0.0   \n",
       "564 2013-07-01     5700            0.0  271111                  0.0   \n",
       "565 2013-08-01     5700            0.0  271111                  0.0   \n",
       "..         ...      ...            ...     ...                  ...   \n",
       "626 2020-06-01     5700     40687326.0  271111             332510.0   \n",
       "627 2020-07-01     5700     44254302.0  271111             328083.0   \n",
       "628 2020-08-01     5700     52565450.0  271111             318840.0   \n",
       "629 2020-09-01     5700     47949278.0  271111             340983.0   \n",
       "630 2020-10-01     5700    198693670.0  271111            1652567.0   \n",
       "\n",
       "    quant_type   hs4   hs4_o            description           low_catagory  \\\n",
       "561         M3  2711  271111  Liquefied natural gas  Liquefied natural gas   \n",
       "562         M3  2711  271111  Liquefied natural gas  Liquefied natural gas   \n",
       "563         M3  2711  271111  Liquefied natural gas  Liquefied natural gas   \n",
       "564         M3  2711  271111  Liquefied natural gas  Liquefied natural gas   \n",
       "565         M3  2711  271111  Liquefied natural gas  Liquefied natural gas   \n",
       "..         ...   ...     ...                    ...                    ...   \n",
       "626         M3  2711  271111  Liquefied natural gas  Liquefied natural gas   \n",
       "627         M3  2711  271111  Liquefied natural gas  Liquefied natural gas   \n",
       "628         M3  2711  271111  Liquefied natural gas  Liquefied natural gas   \n",
       "629         M3  2711  271111  Liquefied natural gas  Liquefied natural gas   \n",
       "630         M3  2711  271111  Liquefied natural gas  Liquefied natural gas   \n",
       "\n",
       "    high_catagory _merge  \n",
       "561     3. Energy   both  \n",
       "562     3. Energy   both  \n",
       "563     3. Energy   both  \n",
       "564     3. Energy   both  \n",
       "565     3. Energy   both  \n",
       "..            ...    ...  \n",
       "626     3. Energy   both  \n",
       "627     3. Energy   both  \n",
       "628     3. Energy   both  \n",
       "629     3. Energy   both  \n",
       "630     3. Energy   both  \n",
       "\n",
       "[70 rows x 12 columns]"
      ]
     },
     "execution_count": 54,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# then we can see the stuff with the orginal code\n",
    "df_phaseone[df_phaseone.hs4_o == \"271111\"]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 55,
   "metadata": {},
   "outputs": [],
   "source": [
    "concordance = pq.read_table(\".\\\\data\\\\alt_concordance.parquet\").to_pandas()\n",
    "\n",
    "concordance.head()\n",
    "\n",
    "dict_concordance = dict(zip(concordance.hs6,concordance.naics)) \n",
    "\n",
    "df_phaseone[\"naics\"] = df_phaseone[\"hs6\"].map(dict_concordance)\n",
    "\n",
    "df_phaseone[\"naics3\"] = df_phaseone[\"naics\"].str[0:3]\n",
    "# The NAICS codes are for mapping the data to the county level data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 56,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_phaseone.high_catagory = df_phaseone.high_catagory.fillna(\"not in aggreement\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Then the outfiles ``phaseone-tradedata.parquet`` is the main file used in ``phase-one-plots.ipynb`` notebook"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 57,
   "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>time</th>\n",
       "      <th>CTY_CODE</th>\n",
       "      <th>china_exports</th>\n",
       "      <th>hs6</th>\n",
       "      <th>china_exports_quant</th>\n",
       "      <th>quant_type</th>\n",
       "      <th>hs4</th>\n",
       "      <th>hs4_o</th>\n",
       "      <th>description</th>\n",
       "      <th>low_catagory</th>\n",
       "      <th>high_catagory</th>\n",
       "      <th>_merge</th>\n",
       "      <th>naics</th>\n",
       "      <th>naics3</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2013-01-01</td>\n",
       "      <td>5700</td>\n",
       "      <td>287000.0</td>\n",
       "      <td>481031</td>\n",
       "      <td>0.0</td>\n",
       "      <td>-</td>\n",
       "      <td>4810</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>not in aggreement</td>\n",
       "      <td>left_only</td>\n",
       "      <td>322130</td>\n",
       "      <td>322</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2013-01-01</td>\n",
       "      <td>5700</td>\n",
       "      <td>311650.0</td>\n",
       "      <td>481160</td>\n",
       "      <td>0.0</td>\n",
       "      <td>-</td>\n",
       "      <td>4811</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>not in aggreement</td>\n",
       "      <td>left_only</td>\n",
       "      <td>322220</td>\n",
       "      <td>322</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2013-01-01</td>\n",
       "      <td>5700</td>\n",
       "      <td>63701.0</td>\n",
       "      <td>481620</td>\n",
       "      <td>0.0</td>\n",
       "      <td>-</td>\n",
       "      <td>4816</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>not in aggreement</td>\n",
       "      <td>left_only</td>\n",
       "      <td>339940</td>\n",
       "      <td>339</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2013-01-01</td>\n",
       "      <td>5700</td>\n",
       "      <td>40265.0</td>\n",
       "      <td>490290</td>\n",
       "      <td>0.0</td>\n",
       "      <td>-</td>\n",
       "      <td>4902</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>not in aggreement</td>\n",
       "      <td>left_only</td>\n",
       "      <td>323111</td>\n",
       "      <td>323</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2013-01-01</td>\n",
       "      <td>5700</td>\n",
       "      <td>324939.0</td>\n",
       "      <td>480431</td>\n",
       "      <td>0.0</td>\n",
       "      <td>-</td>\n",
       "      <td>4804</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>not in aggreement</td>\n",
       "      <td>left_only</td>\n",
       "      <td>322121</td>\n",
       "      <td>322</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        time CTY_CODE  china_exports     hs6  china_exports_quant quant_type  \\\n",
       "0 2013-01-01     5700       287000.0  481031                  0.0          -   \n",
       "1 2013-01-01     5700       311650.0  481160                  0.0          -   \n",
       "2 2013-01-01     5700        63701.0  481620                  0.0          -   \n",
       "3 2013-01-01     5700        40265.0  490290                  0.0          -   \n",
       "4 2013-01-01     5700       324939.0  480431                  0.0          -   \n",
       "\n",
       "    hs4 hs4_o description low_catagory      high_catagory     _merge   naics  \\\n",
       "0  4810   NaN         NaN          NaN  not in aggreement  left_only  322130   \n",
       "1  4811   NaN         NaN          NaN  not in aggreement  left_only  322220   \n",
       "2  4816   NaN         NaN          NaN  not in aggreement  left_only  339940   \n",
       "3  4902   NaN         NaN          NaN  not in aggreement  left_only  323111   \n",
       "4  4804   NaN         NaN          NaN  not in aggreement  left_only  322121   \n",
       "\n",
       "  naics3  \n",
       "0    322  \n",
       "1    322  \n",
       "2    339  \n",
       "3    323  \n",
       "4    322  "
      ]
     },
     "execution_count": 57,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_phaseone.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 58,
   "metadata": {},
   "outputs": [],
   "source": [
    "out_file = \".\\\\data\"+ \"\\\\phaseone-tradedata.parquet\"\n",
    "\n",
    "pq.write_table(pa.Table.from_pandas(df_phaseone), out_file)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### This then constructs the benchmark and goal measures"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 59,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_phaseone.set_index(\"time\", inplace = True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 60,
   "metadata": {},
   "outputs": [],
   "source": [
    "grp = df_phaseone.loc[\"2017\"].groupby(\"high_catagory\")\n",
    "\n",
    "benchmarks = grp.agg({\"china_exports\": \"sum\"})\n",
    "\n",
    "benchmarks.columns = [\"2017 Values\"]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 61,
   "metadata": {},
   "outputs": [],
   "source": [
    "grp = df_phaseone.loc[\"2020\"].groupby(\"high_catagory\")\n",
    "\n",
    "current = grp.agg({\"china_exports\": \"sum\"})\n",
    "\n",
    "current.columns = [\"2020 Values\"]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 62,
   "metadata": {},
   "outputs": [],
   "source": [
    "benchmarks = benchmarks.merge(current, left_index = True, right_index = True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 63,
   "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 Values</th>\n",
       "      <th>2020 Values</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>high_catagory</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1. Manufactured Goods</th>\n",
       "      <td>5.015148e+10</td>\n",
       "      <td>4.225795e+10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2. Agriculture</th>\n",
       "      <td>2.096979e+10</td>\n",
       "      <td>1.756672e+10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3. Energy</th>\n",
       "      <td>7.649222e+09</td>\n",
       "      <td>7.171423e+09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>not in aggreement</th>\n",
       "      <td>5.026467e+10</td>\n",
       "      <td>2.837832e+10</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                        2017 Values   2020 Values\n",
       "high_catagory                                    \n",
       "1. Manufactured Goods  5.015148e+10  4.225795e+10\n",
       "2. Agriculture         2.096979e+10  1.756672e+10\n",
       "3. Energy              7.649222e+09  7.171423e+09\n",
       "not in aggreement      5.026467e+10  2.837832e+10"
      ]
     },
     "execution_count": 63,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "benchmarks"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 64,
   "metadata": {},
   "outputs": [],
   "source": [
    "# These are the goals from the AGREEMENT\n",
    "\n",
    "benchmarks[\"Goals\"] = 32900000000\n",
    "\n",
    "benchmarks.iloc[1,2] = 12500000000\n",
    "\n",
    "benchmarks.iloc[2,2] = 18500000000\n",
    "\n",
    "benchmarks.iloc[3,2] = np.nan"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Then the ``phaseone-goals.parquet`` file is the data file used to create the bar graph."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 65,
   "metadata": {},
   "outputs": [],
   "source": [
    "out_file = \".\\\\data\"+ \"\\\\phaseone-goals.parquet\"\n",
    "\n",
    "pq.write_table(pa.Table.from_pandas(benchmarks), out_file)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 66,
   "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 Values</th>\n",
       "      <th>2020 Values</th>\n",
       "      <th>Goals</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>high_catagory</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1. Manufactured Goods</th>\n",
       "      <td>5.015148e+10</td>\n",
       "      <td>4.225795e+10</td>\n",
       "      <td>3.290000e+10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2. Agriculture</th>\n",
       "      <td>2.096979e+10</td>\n",
       "      <td>1.756672e+10</td>\n",
       "      <td>1.250000e+10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3. Energy</th>\n",
       "      <td>7.649222e+09</td>\n",
       "      <td>7.171423e+09</td>\n",
       "      <td>1.850000e+10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>not in aggreement</th>\n",
       "      <td>5.026467e+10</td>\n",
       "      <td>2.837832e+10</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                        2017 Values   2020 Values         Goals\n",
       "high_catagory                                                  \n",
       "1. Manufactured Goods  5.015148e+10  4.225795e+10  3.290000e+10\n",
       "2. Agriculture         2.096979e+10  1.756672e+10  1.250000e+10\n",
       "3. Energy              7.649222e+09  7.171423e+09  1.850000e+10\n",
       "not in aggreement      5.026467e+10  2.837832e+10           NaN"
      ]
     },
     "execution_count": 66,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "benchmarks"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "---\n",
    "#### The code below then performs the projection of the data down to the county level\n",
    "\n",
    "The first step is to group on NAICS. Note that how this is constructed is only Phase One coverd products are included. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 67,
   "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>CTY_CODE</th>\n",
       "      <th>china_exports</th>\n",
       "      <th>hs6</th>\n",
       "      <th>china_exports_quant</th>\n",
       "      <th>quant_type</th>\n",
       "      <th>hs4</th>\n",
       "      <th>hs4_o</th>\n",
       "      <th>description</th>\n",
       "      <th>low_catagory</th>\n",
       "      <th>high_catagory</th>\n",
       "      <th>_merge</th>\n",
       "      <th>naics</th>\n",
       "      <th>naics3</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",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2017-01-01</th>\n",
       "      <td>5700</td>\n",
       "      <td>361441.0</td>\n",
       "      <td>151800</td>\n",
       "      <td>0.0</td>\n",
       "      <td>-</td>\n",
       "      <td>1518</td>\n",
       "      <td>1518</td>\n",
       "      <td>Animal or vegetable fats, oils and their fract...</td>\n",
       "      <td>Other agricultural commodities</td>\n",
       "      <td>2. Agriculture</td>\n",
       "      <td>both</td>\n",
       "      <td>311613</td>\n",
       "      <td>311</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2017-01-01</th>\n",
       "      <td>5700</td>\n",
       "      <td>289709.0</td>\n",
       "      <td>152000</td>\n",
       "      <td>0.0</td>\n",
       "      <td>-</td>\n",
       "      <td>1520</td>\n",
       "      <td>1520</td>\n",
       "      <td>Glycerol (glycerine), whether or not pure; gly...</td>\n",
       "      <td>Other agricultural commodities</td>\n",
       "      <td>2. Agriculture</td>\n",
       "      <td>both</td>\n",
       "      <td>325611</td>\n",
       "      <td>325</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2017-01-01</th>\n",
       "      <td>5700</td>\n",
       "      <td>36631.0</td>\n",
       "      <td>152110</td>\n",
       "      <td>0.0</td>\n",
       "      <td>-</td>\n",
       "      <td>1521</td>\n",
       "      <td>1521</td>\n",
       "      <td>Vegetable waxes (other than triglycerides), be...</td>\n",
       "      <td>Other agricultural commodities</td>\n",
       "      <td>2. Agriculture</td>\n",
       "      <td>both</td>\n",
       "      <td>325998</td>\n",
       "      <td>325</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2017-01-01</th>\n",
       "      <td>5700</td>\n",
       "      <td>2285893.0</td>\n",
       "      <td>160239</td>\n",
       "      <td>0.0</td>\n",
       "      <td>-</td>\n",
       "      <td>1602</td>\n",
       "      <td>1602</td>\n",
       "      <td>Other prepared or preserved meat, meat offal o...</td>\n",
       "      <td>Meat</td>\n",
       "      <td>2. Agriculture</td>\n",
       "      <td>both</td>\n",
       "      <td>311615</td>\n",
       "      <td>311</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2017-01-01</th>\n",
       "      <td>5700</td>\n",
       "      <td>225955.0</td>\n",
       "      <td>160510</td>\n",
       "      <td>0.0</td>\n",
       "      <td>-</td>\n",
       "      <td>1605</td>\n",
       "      <td>1605</td>\n",
       "      <td>Crustaceans, molluscs and other aquatic invert...</td>\n",
       "      <td>Seafood</td>\n",
       "      <td>2. Agriculture</td>\n",
       "      <td>both</td>\n",
       "      <td>114112</td>\n",
       "      <td>114</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2020-06-01</th>\n",
       "      <td>5700</td>\n",
       "      <td>247440.0</td>\n",
       "      <td>290511</td>\n",
       "      <td>89182.0</td>\n",
       "      <td>L</td>\n",
       "      <td>2905</td>\n",
       "      <td>290511</td>\n",
       "      <td>Me thanol</td>\n",
       "      <td>Refined products</td>\n",
       "      <td>3. Energy</td>\n",
       "      <td>both</td>\n",
       "      <td>325199</td>\n",
       "      <td>325</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2020-07-01</th>\n",
       "      <td>5700</td>\n",
       "      <td>337838.0</td>\n",
       "      <td>290511</td>\n",
       "      <td>351566.0</td>\n",
       "      <td>L</td>\n",
       "      <td>2905</td>\n",
       "      <td>290511</td>\n",
       "      <td>Me thanol</td>\n",
       "      <td>Refined products</td>\n",
       "      <td>3. Energy</td>\n",
       "      <td>both</td>\n",
       "      <td>325199</td>\n",
       "      <td>325</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2020-08-01</th>\n",
       "      <td>5700</td>\n",
       "      <td>307627.0</td>\n",
       "      <td>290511</td>\n",
       "      <td>174207.0</td>\n",
       "      <td>L</td>\n",
       "      <td>2905</td>\n",
       "      <td>290511</td>\n",
       "      <td>Me thanol</td>\n",
       "      <td>Refined products</td>\n",
       "      <td>3. Energy</td>\n",
       "      <td>both</td>\n",
       "      <td>325199</td>\n",
       "      <td>325</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2020-09-01</th>\n",
       "      <td>5700</td>\n",
       "      <td>409390.0</td>\n",
       "      <td>290511</td>\n",
       "      <td>248558.0</td>\n",
       "      <td>L</td>\n",
       "      <td>2905</td>\n",
       "      <td>290511</td>\n",
       "      <td>Me thanol</td>\n",
       "      <td>Refined products</td>\n",
       "      <td>3. Energy</td>\n",
       "      <td>both</td>\n",
       "      <td>325199</td>\n",
       "      <td>325</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2020-10-01</th>\n",
       "      <td>5700</td>\n",
       "      <td>227096.0</td>\n",
       "      <td>290511</td>\n",
       "      <td>142500.0</td>\n",
       "      <td>L</td>\n",
       "      <td>2905</td>\n",
       "      <td>290511</td>\n",
       "      <td>Me thanol</td>\n",
       "      <td>Refined products</td>\n",
       "      <td>3. Energy</td>\n",
       "      <td>both</td>\n",
       "      <td>325199</td>\n",
       "      <td>325</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>172497 rows × 13 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "           CTY_CODE  china_exports     hs6  china_exports_quant quant_type  \\\n",
       "time                                                                         \n",
       "2017-01-01     5700       361441.0  151800                  0.0          -   \n",
       "2017-01-01     5700       289709.0  152000                  0.0          -   \n",
       "2017-01-01     5700        36631.0  152110                  0.0          -   \n",
       "2017-01-01     5700      2285893.0  160239                  0.0          -   \n",
       "2017-01-01     5700       225955.0  160510                  0.0          -   \n",
       "...             ...            ...     ...                  ...        ...   \n",
       "2020-06-01     5700       247440.0  290511              89182.0          L   \n",
       "2020-07-01     5700       337838.0  290511             351566.0          L   \n",
       "2020-08-01     5700       307627.0  290511             174207.0          L   \n",
       "2020-09-01     5700       409390.0  290511             248558.0          L   \n",
       "2020-10-01     5700       227096.0  290511             142500.0          L   \n",
       "\n",
       "             hs4   hs4_o                                        description  \\\n",
       "time                                                                          \n",
       "2017-01-01  1518    1518  Animal or vegetable fats, oils and their fract...   \n",
       "2017-01-01  1520    1520  Glycerol (glycerine), whether or not pure; gly...   \n",
       "2017-01-01  1521    1521  Vegetable waxes (other than triglycerides), be...   \n",
       "2017-01-01  1602    1602  Other prepared or preserved meat, meat offal o...   \n",
       "2017-01-01  1605    1605  Crustaceans, molluscs and other aquatic invert...   \n",
       "...          ...     ...                                                ...   \n",
       "2020-06-01  2905  290511                                          Me thanol   \n",
       "2020-07-01  2905  290511                                          Me thanol   \n",
       "2020-08-01  2905  290511                                          Me thanol   \n",
       "2020-09-01  2905  290511                                          Me thanol   \n",
       "2020-10-01  2905  290511                                          Me thanol   \n",
       "\n",
       "                              low_catagory   high_catagory _merge   naics  \\\n",
       "time                                                                        \n",
       "2017-01-01  Other agricultural commodities  2. Agriculture   both  311613   \n",
       "2017-01-01  Other agricultural commodities  2. Agriculture   both  325611   \n",
       "2017-01-01  Other agricultural commodities  2. Agriculture   both  325998   \n",
       "2017-01-01                            Meat  2. Agriculture   both  311615   \n",
       "2017-01-01                         Seafood  2. Agriculture   both  114112   \n",
       "...                                    ...             ...    ...     ...   \n",
       "2020-06-01                Refined products       3. Energy   both  325199   \n",
       "2020-07-01                Refined products       3. Energy   both  325199   \n",
       "2020-08-01                Refined products       3. Energy   both  325199   \n",
       "2020-09-01                Refined products       3. Energy   both  325199   \n",
       "2020-10-01                Refined products       3. Energy   both  325199   \n",
       "\n",
       "           naics3  \n",
       "time               \n",
       "2017-01-01    311  \n",
       "2017-01-01    325  \n",
       "2017-01-01    325  \n",
       "2017-01-01    311  \n",
       "2017-01-01    114  \n",
       "...           ...  \n",
       "2020-06-01    325  \n",
       "2020-07-01    325  \n",
       "2020-08-01    325  \n",
       "2020-09-01    325  \n",
       "2020-10-01    325  \n",
       "\n",
       "[172497 rows x 13 columns]"
      ]
     },
     "execution_count": 67,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_phaseone.loc[\"2017\":]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 68,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_phaseone = df_phaseone.loc[\"2017\":]\n",
    "\n",
    "grp = df_phaseone.groupby([\"time\",\"naics3\"])\n",
    "\n",
    "exports_by_naics = grp.agg({\"china_exports\": \"sum\"})\n",
    "\n",
    "exports_by_naics.reset_index(inplace = True)\n",
    "\n",
    "exports_by_naics.set_index([\"naics3\"], inplace = True)\n",
    "\n",
    "##################################################################\n",
    "\n",
    "grp = df_phaseone[df_phaseone[\"high_catagory\"] != \"not in aggreement\"].groupby([\"time\",\"naics3\"])\n",
    "\n",
    "exports_phaseone = grp.agg({\"china_exports\": \"sum\"})\n",
    "\n",
    "exports_phaseone.reset_index(inplace = True)\n",
    "\n",
    "exports_phaseone.set_index([\"naics3\"], inplace = True)\n",
    "\n",
    "##################################################################\n",
    "\n",
    "exports_phaseone.rename(mapper = {\"china_exports\": \"phase_one_exports\"}, inplace = True, axis = 1)\n",
    "\n",
    "exports_by_naics = exports_by_naics.merge(exports_phaseone, \n",
    "                                          left_on = [\"naics3\", \"time\"], right_on = [\"naics3\", \"time\"])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 69,
   "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>time</th>\n",
       "      <th>china_exports</th>\n",
       "      <th>phase_one_exports</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>naics3</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>337</th>\n",
       "      <td>2020-10-01</td>\n",
       "      <td>14114724.0</td>\n",
       "      <td>18954.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>339</th>\n",
       "      <td>2020-10-01</td>\n",
       "      <td>401780019.0</td>\n",
       "      <td>307062729.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>910</th>\n",
       "      <td>2020-10-01</td>\n",
       "      <td>145336670.0</td>\n",
       "      <td>3848052.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>930</th>\n",
       "      <td>2020-10-01</td>\n",
       "      <td>32342721.0</td>\n",
       "      <td>5591726.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>990</th>\n",
       "      <td>2020-10-01</td>\n",
       "      <td>132032143.0</td>\n",
       "      <td>443204.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "             time  china_exports  phase_one_exports\n",
       "naics3                                             \n",
       "337    2020-10-01     14114724.0            18954.0\n",
       "339    2020-10-01    401780019.0        307062729.0\n",
       "910    2020-10-01    145336670.0          3848052.0\n",
       "930    2020-10-01     32342721.0          5591726.0\n",
       "990    2020-10-01    132032143.0           443204.0"
      ]
     },
     "execution_count": 69,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "exports_by_naics.tail()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The following functions do the following: \n",
    "\n",
    "1. Grab the BLS QCEW file for 2017\n",
    "\n",
    "2. Then create the exports per worker measure at the county level. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 70,
   "metadata": {},
   "outputs": [],
   "source": [
    "def download_bls():\n",
    "\n",
    "    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)\n",
    "\n",
    "########################################################################\n",
    "\n",
    "    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)\n",
    "    \n",
    "    return df_county, df_national"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 71,
   "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\"]],\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 = exports_by_naics.merge(new_df[[\"emp_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": 72,
   "metadata": {},
   "outputs": [],
   "source": [
    "def trade_by_naics(df):\n",
    "    # Simple function just to test about aggregation \n",
    "\n",
    "    china_exp_pc = (1/df[\"annual_avg_emplvl\"].sum())*(df[\"china_exports\"]*df[\"emp_wts\"]).sum()\n",
    "    \n",
    "    china_pho_pc = (1/df[\"annual_avg_emplvl\"].sum())*(df[\"phase_one_exports\"]*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",
    "    foo = {\"china_exp_pc\": [china_exp_pc],\n",
    "           \"china_pho_pc\": [china_pho_pc],\n",
    "          \"emplvl_2017\": df[\"annual_avg_emplvl\"].sum()}\n",
    "\n",
    "    return pd.DataFrame(foo)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 73,
   "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:3254: 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",
      "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": [
    "df_county, df_national = download_bls()\n",
    "\n",
    "#print(df_county.annual_avg_emplvl.sum())\n",
    "\n",
    "grp = df_county.groupby(\"area_fips\")\n",
    "\n",
    "trade_county = grp.apply(create_trade_weights)\n",
    "\n",
    "trade_county[\"china_exp_pc\"] = (trade_county[\"emplvl_2017\"]/\n",
    "                                    trade_county[\"total_employment\"])*trade_county[\"china_exp_pc\"]\n",
    "\n",
    "trade_county[\"china_pho_pc\"] = (trade_county[\"emplvl_2017\"]/\n",
    "                                    trade_county[\"total_employment\"])*trade_county[\"china_pho_pc\"]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 74,
   "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>china_exp_pc</th>\n",
       "      <th>china_pho_pc</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",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th rowspan=\"5\" valign=\"top\">10001</th>\n",
       "      <th>2017-01-01</th>\n",
       "      <td>58.234982</td>\n",
       "      <td>53.255948</td>\n",
       "      <td>2843.0</td>\n",
       "      <td>10001</td>\n",
       "      <td>29514.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2017-02-01</th>\n",
       "      <td>48.367256</td>\n",
       "      <td>43.743352</td>\n",
       "      <td>2843.0</td>\n",
       "      <td>10001</td>\n",
       "      <td>29514.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2017-03-01</th>\n",
       "      <td>42.193222</td>\n",
       "      <td>37.361727</td>\n",
       "      <td>2843.0</td>\n",
       "      <td>10001</td>\n",
       "      <td>29514.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2017-04-01</th>\n",
       "      <td>33.545767</td>\n",
       "      <td>29.282311</td>\n",
       "      <td>2843.0</td>\n",
       "      <td>10001</td>\n",
       "      <td>29514.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2017-05-01</th>\n",
       "      <td>33.787258</td>\n",
       "      <td>29.696272</td>\n",
       "      <td>2843.0</td>\n",
       "      <td>10001</td>\n",
       "      <td>29514.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                      china_exp_pc  china_pho_pc  emplvl_2017   fips  \\\n",
       "area_fips time                                                         \n",
       "10001     2017-01-01     58.234982     53.255948       2843.0  10001   \n",
       "          2017-02-01     48.367256     43.743352       2843.0  10001   \n",
       "          2017-03-01     42.193222     37.361727       2843.0  10001   \n",
       "          2017-04-01     33.545767     29.282311       2843.0  10001   \n",
       "          2017-05-01     33.787258     29.696272       2843.0  10001   \n",
       "\n",
       "                      total_employment  \n",
       "area_fips time                          \n",
       "10001     2017-01-01           29514.0  \n",
       "          2017-02-01           29514.0  \n",
       "          2017-03-01           29514.0  \n",
       "          2017-04-01           29514.0  \n",
       "          2017-05-01           29514.0  "
      ]
     },
     "execution_count": 74,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "trade_county.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 75,
   "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>china_exp_pc</th>\n",
       "      <th>china_pho_pc</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",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th rowspan=\"5\" valign=\"top\">10001</th>\n",
       "      <th>2017-01-01</th>\n",
       "      <td>58.234982</td>\n",
       "      <td>53.255948</td>\n",
       "      <td>2843.0</td>\n",
       "      <td>10001</td>\n",
       "      <td>29514.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2017-02-01</th>\n",
       "      <td>48.367256</td>\n",
       "      <td>43.743352</td>\n",
       "      <td>2843.0</td>\n",
       "      <td>10001</td>\n",
       "      <td>29514.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2017-03-01</th>\n",
       "      <td>42.193222</td>\n",
       "      <td>37.361727</td>\n",
       "      <td>2843.0</td>\n",
       "      <td>10001</td>\n",
       "      <td>29514.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2017-04-01</th>\n",
       "      <td>33.545767</td>\n",
       "      <td>29.282311</td>\n",
       "      <td>2843.0</td>\n",
       "      <td>10001</td>\n",
       "      <td>29514.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2017-05-01</th>\n",
       "      <td>33.787258</td>\n",
       "      <td>29.696272</td>\n",
       "      <td>2843.0</td>\n",
       "      <td>10001</td>\n",
       "      <td>29514.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                      china_exp_pc  china_pho_pc  emplvl_2017   fips  \\\n",
       "area_fips time                                                         \n",
       "10001     2017-01-01     58.234982     53.255948       2843.0  10001   \n",
       "          2017-02-01     48.367256     43.743352       2843.0  10001   \n",
       "          2017-03-01     42.193222     37.361727       2843.0  10001   \n",
       "          2017-04-01     33.545767     29.282311       2843.0  10001   \n",
       "          2017-05-01     33.787258     29.696272       2843.0  10001   \n",
       "\n",
       "                      total_employment  \n",
       "area_fips time                          \n",
       "10001     2017-01-01           29514.0  \n",
       "          2017-02-01           29514.0  \n",
       "          2017-03-01           29514.0  \n",
       "          2017-04-01           29514.0  \n",
       "          2017-05-01           29514.0  "
      ]
     },
     "execution_count": 75,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "trade_county.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "And we are set. The only final part is to add in some information from the census. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 76,
   "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": 77,
   "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": 78,
   "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>china_exp_pc</th>\n",
       "      <th>china_pho_pc</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",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th rowspan=\"5\" valign=\"top\">10001</th>\n",
       "      <th>2017-01-01</th>\n",
       "      <td>58.234982</td>\n",
       "      <td>53.255948</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>2017-02-01</th>\n",
       "      <td>48.367256</td>\n",
       "      <td>43.743352</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>2017-03-01</th>\n",
       "      <td>42.193222</td>\n",
       "      <td>37.361727</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>2017-04-01</th>\n",
       "      <td>33.545767</td>\n",
       "      <td>29.282311</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>2017-05-01</th>\n",
       "      <td>33.787258</td>\n",
       "      <td>29.696272</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": [
       "                      china_exp_pc  china_pho_pc  emplvl_2017   fips  \\\n",
       "area_fips time                                                         \n",
       "10001     2017-01-01     58.234982     53.255948       2843.0  10001   \n",
       "          2017-02-01     48.367256     43.743352       2843.0  10001   \n",
       "          2017-03-01     42.193222     37.361727       2843.0  10001   \n",
       "          2017-04-01     33.545767     29.282311       2843.0  10001   \n",
       "          2017-05-01     33.787258     29.696272       2843.0  10001   \n",
       "\n",
       "                      total_employment  int_area_fips  2017_income  \\\n",
       "area_fips time                                                       \n",
       "10001     2017-01-01           29514.0          10001      57647.0   \n",
       "          2017-02-01           29514.0          10001      57647.0   \n",
       "          2017-03-01           29514.0          10001      57647.0   \n",
       "          2017-04-01           29514.0          10001      57647.0   \n",
       "          2017-05-01           29514.0          10001      57647.0   \n",
       "\n",
       "                      2017_population  \n",
       "area_fips time                         \n",
       "10001     2017-01-01         173145.0  \n",
       "          2017-02-01         173145.0  \n",
       "          2017-03-01         173145.0  \n",
       "          2017-04-01         173145.0  \n",
       "          2017-05-01         173145.0  "
      ]
     },
     "execution_count": 78,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "trade_county.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 79,
   "metadata": {},
   "outputs": [],
   "source": [
    "file_path =  \".\\\\data\"+ \"\\\\phase_one_county.parquet\"\n",
    "\n",
    "pq.write_table(pa.Table.from_pandas(trade_county), file_path)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Then the ``phase_one_county.parquet`` file is the main file used in the ``phase-one-map.ipynb`` notebook."
   ]
  },
  {
   "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
}