get-ustrade-monthly.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\n",
    " \n",
    "from bokeh.palettes import brewer, Spectral6\n",
    "from bokeh.io import show, output_file, curdoc\n",
    "from bokeh.plotting import figure\n",
    "from bokeh.models import ColumnDataSource, HoverTool, Panel, Tabs, GeoJSONDataSource, LinearColorMapper\n",
    "from bokeh.models import ColorBar\n",
    "from bokeh.layouts import column, gridplot, row\n",
    "from bokeh.transform import factor_cmap\n",
    "from bokeh.models import NumeralTickFormatter, Title, Label, Paragraph, Div, CustomJSHover, BoxAnnotation"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Ok here is teh strategy, grab the monthly data from here:\n",
    "\n",
    "https://www.census.gov/foreign-trade/Press-Release/ft900_index.html\n",
    "\n",
    "it looks like everything is systematically organized so that this can be accomplished"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "current_month = 2"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "years = range(2003,2020)\n",
    "\n",
    "df = pd.DataFrame([])\n",
    "\n",
    "##########################################################################################\n",
    "\n",
    "for xxx in years:\n",
    "\n",
    "    year = str(xxx)\n",
    "    \n",
    "    url = \"https://www.census.gov/foreign-trade/Press-Release/\" \n",
    "    url = url + year + \"pr/final_revisions/exh11.xls\"\n",
    "    \n",
    "    #for some reason 2002 and back have final_revisions capitalized,\n",
    "    # its ok, this is all I need for this\n",
    "\n",
    "    foo = pd.read_excel(url,skiprows = 36, nrows = 12, header = None, usecols = [0,5,8])\n",
    "\n",
    "    foo.columns = [\"time\", \"exports\", \"imports\"]\n",
    "\n",
    "    foo[\"time\"] = foo[\"time\"] + \", \" + year\n",
    "    \n",
    "    foo.time = pd.to_datetime(foo.time)\n",
    "    \n",
    "    df = df.append(foo)\n",
    "\n",
    "##########################################################################################\n",
    "# Then let's add in 2020 which is organized a differently\n",
    "\n",
    "url = \"https://www.census.gov/foreign-trade/Press-Release/current_press_release/exh12.xlsx\"\n",
    "\n",
    "foo = pd.read_excel(url, skiprows = 24, nrows = 12, header = None, usecols = [0,5,8])\n",
    "\n",
    "foo.columns = [\"time\", \"exports\", \"imports\"]\n",
    "\n",
    "foo.time = foo.time.str.split(\"(\").str[0]\n",
    "\n",
    "foo[\"time\"] = foo[\"time\"] + \", \" + \"2020\"\n",
    "\n",
    "foo.time = pd.to_datetime(foo.time)\n",
    "\n",
    "df = df.append(foo)\n",
    "\n",
    "##########################################################################################\n",
    "# Then let's add in 2020 which is organized a differently\n",
    "\n",
    "url = \"https://www.census.gov/foreign-trade/Press-Release/current_press_release/exh12.xlsx\"\n",
    "\n",
    "foo = pd.read_excel(url, skiprows = 38, nrows = current_month, header = None, usecols = [0,5,8])\n",
    "\n",
    "foo.columns = [\"time\", \"exports\", \"imports\"]\n",
    "\n",
    "foo.time = foo.time.str.split(\"(\").str[0]\n",
    "\n",
    "foo[\"time\"] = foo[\"time\"] + \", \" + \"2021\"\n",
    "\n",
    "foo.time = pd.to_datetime(foo.time)\n",
    "\n",
    "df = df.append(foo)\n",
    "\n",
    "out_file = \".\\\\data\"+ \"\\\\aggregate-tradedata.parquet\"\n",
    "\n",
    "pq.write_table(pa.Table.from_pandas(df), out_file)\n",
    "\n",
    "df.set_index(\"time\", inplace = True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "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>exports</th>\n",
       "      <th>imports</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>time</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2003-01-01</th>\n",
       "      <td>54854</td>\n",
       "      <td>97491</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2003-02-01</th>\n",
       "      <td>55917</td>\n",
       "      <td>93154</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2003-03-01</th>\n",
       "      <td>63524</td>\n",
       "      <td>105842</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2003-04-01</th>\n",
       "      <td>59162</td>\n",
       "      <td>103869</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2003-05-01</th>\n",
       "      <td>59983</td>\n",
       "      <td>102068</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2020-10-01</th>\n",
       "      <td>132571</td>\n",
       "      <td>219502</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2020-11-01</th>\n",
       "      <td>127024</td>\n",
       "      <td>212746</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2020-12-01</th>\n",
       "      <td>132537</td>\n",
       "      <td>215465</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2021-01-01</th>\n",
       "      <td>127860</td>\n",
       "      <td>204818</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2021-02-01</th>\n",
       "      <td>123937</td>\n",
       "      <td>193812</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>218 rows × 2 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "            exports  imports\n",
       "time                        \n",
       "2003-01-01    54854    97491\n",
       "2003-02-01    55917    93154\n",
       "2003-03-01    63524   105842\n",
       "2003-04-01    59162   103869\n",
       "2003-05-01    59983   102068\n",
       "...             ...      ...\n",
       "2020-10-01   132571   219502\n",
       "2020-11-01   127024   212746\n",
       "2020-12-01   132537   215465\n",
       "2021-01-01   127860   204818\n",
       "2021-02-01   123937   193812\n",
       "\n",
       "[218 rows x 2 columns]"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [],
   "source": [
    "def growth_trade(foo):\n",
    "    # what this function does is take a dataframe and create a relative \n",
    "    \n",
    "    foo[\"import_growth\"] = 100*((df.imports/foo.imports.shift(12)) - 1)\n",
    "    \n",
    "    foo[\"export_growth\"] = 100*((df.exports/foo.exports.shift(12)) - 1)\n",
    "    \n",
    "    return foo"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [],
   "source": [
    "df = growth_trade(df)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [],
   "source": [
    "def make_covid_GR_df(df,trade_type):\n",
    "\n",
    "    covid_df = df.copy()\n",
    "\n",
    "    covid_df[\"flag\"] = np.nan\n",
    "\n",
    "    covid_df.loc[dt.datetime(2020,3,1),\"flag\"] = \"covid-shock\"\n",
    "\n",
    "    covid_df.flag.ffill(inplace = True)\n",
    "\n",
    "    covid_df.flag.bfill(inplace = True, limit = 12)\n",
    "\n",
    "    covid_df.reset_index(inplace = True)\n",
    "\n",
    "    covid_df = covid_df[covid_df[\"flag\"] == \"covid-shock\"]\n",
    "\n",
    "    covid_df.index = list(range(-12, (current_month + 12) -2))\n",
    "\n",
    "    covid_dates = covid_df[[\"time\"]]\n",
    "    \n",
    "    foo = trade_type\n",
    "    \n",
    "    covid_df = covid_df[[foo + \"_growth\"]]\n",
    "    \n",
    "    if foo == \"import\":\n",
    "        covid_df.columns = [\"Imports, Covid Shock\"]\n",
    "        \n",
    "    if foo == \"export\":\n",
    "        covid_df.columns = [\"Exports, Covid Shock\"]\n",
    "        \n",
    "#############################################################\n",
    "\n",
    "    GR_df = df.copy()\n",
    "\n",
    "    GR_df[\"flag\"] = np.nan\n",
    "\n",
    "    GR_df.loc[dt.datetime(2008,9,1),\"flag\"] = \"GR-shock\"\n",
    "\n",
    "    GR_df.flag.ffill(inplace = True, limit = 12)\n",
    "\n",
    "    GR_df.flag.bfill(inplace = True, limit = 12)\n",
    "\n",
    "    GR_df.reset_index(inplace = True)\n",
    "\n",
    "    GR_df = GR_df[GR_df[\"flag\"] == \"GR-shock\"]\n",
    "\n",
    "    GR_df.index = list(range(-12, 13))\n",
    "\n",
    "    GR_dates = GR_df[[\"time\"]]\n",
    "\n",
    "    GR_df = GR_df[[foo + \"_growth\"]]\n",
    "    \n",
    "    if foo == \"import\":\n",
    "        covid_df.columns = [\"Imports, Covid Shock\"]\n",
    "        GR_df.columns = [\"Imports, Financial Crisis\"]\n",
    "        \n",
    "    if foo == \"export\":\n",
    "        covid_df.columns = [\"Exports, Covid Shock\"]\n",
    "        GR_df.columns = [\"Exports, Financial Crisis\"]\n",
    "    \n",
    "\n",
    "    outdf = GR_df.merge(covid_df, left_index = True, right_index = True, how = \"left\")\n",
    "    \n",
    "    dates = GR_dates.merge(covid_dates, left_index = True, right_index = True, how = \"left\")\n",
    "    \n",
    "    return outdf, covid_dates, GR_dates"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [],
   "source": [
    "crl = [\"darkblue\",\"slategray\",\"crimson\"]\n",
    "\n",
    "background = \"#ffffff\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [],
   "source": [
    "def make_trade_time(df, trade_type):\n",
    "    \n",
    "    height = 533\n",
    "    width = 600\n",
    "\n",
    "    foobar, covid_dates, GR_dates = make_covid_GR_df(df,trade_type)\n",
    "    \n",
    "    if trade_type == \"import\":\n",
    "        title = \"The Growth of US Imports (GOODS) (%YoY) Around Shock\"\n",
    "    \n",
    "    if trade_type == \"export\":\n",
    "        title = \"The Growth of US Exports (GOODS) (%YoY) Around Shock\"\n",
    "    \n",
    "    p = figure(plot_height=height, plot_width = width, toolbar_location = 'below',\n",
    "           tools = \"box_zoom, reset\", title = title ) \n",
    "\n",
    "    numlines=len(foobar.columns)\n",
    "\n",
    "    multi_line_source = ColumnDataSource({\n",
    "        'xs': [foobar.index.values]*numlines,\n",
    "        'ys': [foobar[name].values for name in foobar.columns],\n",
    "        'label': [name for name in foobar.columns],\n",
    "        'color': [\"crimson\", \"darkblue\", \"slategrey\"],\n",
    "        \"legend\": [\"Financial Crisis\", \"Covid-19 Pandemic\"]})\n",
    "    \n",
    "    p.multi_line(xs= \"xs\",\n",
    "                ys= \"ys\",\n",
    "                line_width=4, line_alpha=0.75, line_color = \"color\",\n",
    "                 hover_line_alpha=0.75, hover_line_width = 5,\n",
    "                hover_line_color= \"color\", \n",
    "                legend_field=  \"legend\",\n",
    "                source = multi_line_source)\n",
    "    \n",
    "######################################################################################\n",
    "    singlesourceGR = ColumnDataSource({\n",
    "        'xs': foobar.index.values,\n",
    "        'ys': foobar.iloc[:,0].values,\n",
    "        \"dates\": np.array(GR_dates.time),\n",
    "        \"label\": [foobar.columns[0]]*len(foobar.index.values),\n",
    "    })\n",
    "    \n",
    "    cGR = p.circle(x=\"xs\", y=\"ys\", size=10,\n",
    "                    source = singlesourceGR, color = \"crimson\",alpha=0)\n",
    "    \n",
    "    singlesourceCovid = ColumnDataSource({\n",
    "        'xs': foobar.index.values,\n",
    "        'ys': foobar.iloc[:,1].values,\n",
    "        \"dates\": np.array(covid_dates.time),\n",
    "        \"label\": [foobar.columns[1]]*len(foobar.index.values),\n",
    "    })\n",
    "    \n",
    "    ccovid = p.circle(x=\"xs\", y=\"ys\", size=10,\n",
    "                    source = singlesourceCovid, color = \"darkblue\",alpha=0)\n",
    "        \n",
    "            \n",
    "    TIMETOOLTIPS = \"\"\"\n",
    "            <div style=\"background-color:#F5F5F5; opacity: 0.95; border: 5px 5px 5px 5px;\">\n",
    "            <div style = \"text-align:left;\">\n",
    "            <span style=\"font-size: 13px; font-weight: bold\"> @label\n",
    "             </span>\n",
    "             </div>\n",
    "             <div style = \"text-align:left;\">\"\"\"\n",
    "    \n",
    "    TIMETOOLTIPS = TIMETOOLTIPS + \"\"\"\n",
    "            <span style=\"font-size: 13px; font-weight: bold\"> @dates{%b %Y} $data_y{0.0}%</span>   \n",
    "            </div>\n",
    "            </div>\"\"\"\n",
    "    \n",
    "    #p.hover.renderers = [ctest]\n",
    "    p.add_tools(HoverTool(tooltips = TIMETOOLTIPS,  line_policy='nearest', formatters={'@dates': 'datetime'}, renderers = [cGR,ccovid]))\n",
    "    \n",
    "    p.title.text_font_size = '13pt'\n",
    "    p.background_fill_color = background \n",
    "    p.background_fill_alpha = 0.75\n",
    "    p.border_fill_color = background\n",
    "    \n",
    "    p.vbar(x = 0, \n",
    "           color='grey', top = 30, bottom = -50, width = 0.05, alpha = 0.50)\n",
    "\n",
    "    p.yaxis.axis_label_text_font_style = 'bold'\n",
    "    p.yaxis.axis_label_text_font_size = \"13px\"\n",
    "\n",
    "    p.yaxis.minor_tick_line_color = None\n",
    "    \n",
    "    p.x_range.start = -12\n",
    "    p.y_range.start = -40\n",
    "    p.y_range.end = 30\n",
    "    \n",
    "    p.xaxis.axis_label = 'Months Around Shock'\n",
    "    p.xaxis.axis_label_text_font_style = 'bold'\n",
    "    p.xaxis.axis_label_text_font_size = \"13px\"\n",
    "    \n",
    "    p.legend.orientation = \"vertical\"\n",
    "    p.legend.background_fill_color = background\n",
    "    p.legend.background_fill_alpha = 0.10\n",
    "    p.legend.label_text_font_size = \"1em\"\n",
    "    \n",
    "    p.outline_line_color = None\n",
    "    p.sizing_mode= \"scale_both\"\n",
    "    p.max_height = height\n",
    "    p.max_width = width\n",
    "    \n",
    "    p.toolbar.active_drag = None\n",
    "    p.toolbar.autohide = True\n",
    "    p.min_border_left = 0\n",
    "    p.min_border_bottom = 0\n",
    "\n",
    "    return p"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "BokehUserWarning: ColumnDataSource's columns must be of the same length. Current lengths: ('color', 3), ('label', 2), ('legend', 2), ('xs', 2), ('ys', 2)\n",
      "BokehUserWarning: ColumnDataSource's columns must be of the same length. Current lengths: ('dates', 24), ('label', 25), ('xs', 25), ('ys', 25)\n",
      "BokehUserWarning: ColumnDataSource's columns must be of the same length. Current lengths: ('color', 3), ('label', 2), ('legend', 2), ('xs', 2), ('ys', 2)\n",
      "BokehUserWarning: ColumnDataSource's columns must be of the same length. Current lengths: ('dates', 24), ('label', 25), ('xs', 25), ('ys', 25)\n"
     ]
    }
   ],
   "source": [
    "pimports = make_trade_time(df, \"import\")\n",
    "\n",
    "pexports = make_trade_time(df, \"export\")\n",
    "\n",
    "tab3 = Panel(child= pimports, title=\"Goods Imports % YoY\")\n",
    "\n",
    "tab4 = Panel(child= pexports, title=\"Goods Exports % YoY\")\n",
    "\n",
    "output_file('.\\\\docs\\\\' + \"us_agg_trade.html\")\n",
    "\n",
    "div0 = Div(text = \"\"\"Month 0 is September 2008 for the financial crisis series, Month 0 is March 2020 for the Covid-19 Pandemic series.\n",
    "All values are percent changes year over year. Hover tool reports the change and the calander date.\"\"\", max_width=555, background = background )\n",
    "div0.sizing_mode= \"scale_both\"\n",
    "        \n",
    "outfig = column(Tabs(tabs=[tab3,  tab4], tabs_location = \"above\"), div0, sizing_mode=\"scale_both\")\n",
    "\n",
    "\n",
    "show(outfig)"
   ]
  },
  {
   "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
}