bills_of_lading.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Parsing Shipping Data"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<h2 id=\"tocheading\">Table of Contents</h2>\n",
    "<div id=\"toc\"></div>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "application/javascript": [
       "$.getScript('https://kmahelona.github.io/ipython_notebook_goodies/ipython_notebook_toc.js')"
      ],
      "text/plain": [
       "<IPython.core.display.Javascript object>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "%%javascript\n",
    "$.getScript('https://kmahelona.github.io/ipython_notebook_goodies/ipython_notebook_toc.js')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'en_US.UTF-8'"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# I often switch between editing notebooks from my PC to my Mac;\n",
    "# the code in this cell is to find the proper local data when I do so.\n",
    "\n",
    "import platform\n",
    "import os\n",
    "import locale\n",
    "import json\n",
    "import re\n",
    "from geopy import geocoders\n",
    "import numpy as np\n",
    "import pandas as pd\n",
    "import multiprocessing as mp\n",
    "import pickle\n",
    "import plotly.plotly as py\n",
    "from plotly.tools import set_credentials_file\n",
    "import plotly.offline as offline\n",
    "\n",
    "o_s = platform.system()\n",
    "paths = {}\n",
    "\n",
    "if o_s == 'Darwin':\n",
    "    paths['data_dir'] = '/Users/drew/data/Data_Aggregation'\n",
    "elif o_s == 'Windows':\n",
    "    paths['data_dir'] = r'C:\\Users\\DMacKellar\\Documents\\Data\\Data_Aggregation'\n",
    "\n",
    "locale.setlocale(locale.LC_ALL, '')\n",
    "\n",
    "paths['sea2017'] = os.path.join(paths['enigma_dir'], \n",
    "                                'seattle_shipping_2017.csv')\n",
    "\n",
    "sea2017_df = pd.read_csv(paths['sea2017'])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Introduction\n",
    "\n",
    "I was recently playing with Pandas' built-in DataReader module in a project to explore some financial markets' data.  Many online examples involve using it to draw historical stock price and volume data from Yahoo or Google Finance.  Yahoo's financial data is now completely defunct (not accessible via the pandas module; there is still a [web interface](https://finance.yahoo.com/)), and a warning has been added that the Google Finance data interface is prone to failure (and, indeed, I haven't been able to recover data with it, either).  So I was perusing [the docs](https://pandas-datareader.readthedocs.io/en/latest/) associated with the DataReader module.\n",
    "\n",
    "While looking over the other data sources listed in the Pandas Datareader docs page, I saw one listed as \"the world’s largest repository of structured public data\", referring to a site called [Enigma](https://public.enigma.com/).  It does indeed have some interesting and diverse sets of data, and I quickly settled on [Bills of Lading](https://public.enigma.com/spotlight/bill-of-lading) from among the links on the front page, a set which claimed to list the contents of tons of shipping from around the world.  The top few rows listed today happened to have Seattle, WA as the port of unlading, and I thought it would be a cool practice to try to categorize all of the arrivals in Seattle for a period, and how they change over time.  [Their API](http://docs.enigma.com/public/public_v20_user_api.html) involves installing a program called Postman; I downloaded it and set it up via my Google account.  I also signed up for Enigma using my Gmail account, so it appears to have transferred the credentials automatically, because all I had to do to open the API was copy the URL from Enigma's page (after clicking on the API button within the dataset's window), paste it into the top field in the Postman window, and it returned the basic data.\n",
    "\n",
    "I'm accessing this on 20180408, and the entire Bills of Lading dataset states that it has 27,788,013 rows.  But the date range is just for 2017.  They also have separate sets for each year going back to 2014.\n",
    "\n",
    "To be specific, the overall organization of this dataset within Enigma's system is:\n",
    "\n",
    "    International Trade & Shipping\n",
    "    -> United States Import Records\n",
    "\n",
    "But within that they have 3 different sections:\n",
    "       \n",
    "       -> United States Import Records - Bill of Lading Summary\n",
    "          United States Import Records - Cargo\n",
    "          United States Import Records - Toxic & Hazardous Materials\n",
    "          \n",
    "The first two have results for each year 2014-2017, but the toxic materials data just has 2015.\n",
    "\n",
    "Summaries for each of these are offered in the \"overview\" tab:\n",
    "\n",
    "\n",
    "*Bill of Lading Summary*:\n",
    "\n",
    "Bills of lading header information for incoming shipments regulated by U.S. Customs and Border Protection's Automated Manifest System (AMS) for 2017.\n",
    "Updated a month ago\n",
    "27,788,013 rows\n",
    "31 fields\n",
    "\n",
    "*Cargo Summary*:\n",
    "\n",
    "Cargo imports in 2017. The Automated Manifest System (AMS) is designed by U.S. Customs to facilitate cargo information between steam ship lines, airlines and rail carriers for shipments destined to or transiting the United States. Recently, the processing of other cargo transportation (electronic truck, rail and sea) manifests has been transitioned to Automated Commercial Environment (ACE). AMS only refers to electronic air manifests.\n",
    "Read less\n",
    "Updated 19 days ago\n",
    "32,861,196 rows\n",
    "9 fields\n",
    "\n",
    "*Toxic & Hazardous Materials*:\n",
    "\n",
    "Information for incoming shipments containing toxic and hazardous materials provided by U.S. Customs and Border Protection's Automated Manifest System (AMS) and Automated Commercial Environment (ACE).\n",
    "Read less\n",
    "Updated 9 months ago\n",
    "\n",
    "---\n",
    "\n",
    "Actually, after poking around for a bit, using their default browser-based data viewer is pretty handy, too.  Click on the leftmost icon that appears when hovering over any column name, and [you can enter specific strings to filter its content](http://docs.enigma.com/public/public_v20_user_filtering.html).  When I enter \"Seattle, Washington\" for Port of Unlading, it returns 27,100 of 27,788,013 rows in the Bill of Lading Summary - 2017 dataset.  There's then a button to export the resulting, filtered data set as a csv file, which in this case is ~14MB.  Import below:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "o_s = platform.system()\n",
    "paths = {}\n",
    "\n",
    "if o_s == 'Darwin':\n",
    "    paths['enigma_dir'] = '/Users/drew/data/Enigma'\n",
    "elif o_s == 'Windows':\n",
    "    paths['enigma_dir'] = r'C:\\Users\\DMacKellar\\Documents\\Data\\Enigma'\n",
    "\n",
    "paths['sea2017'] = os.path.join(paths['enigma_dir'], \n",
    "                                'seattle_shipping_2017.csv')\n",
    "\n",
    "sea2017_df = pd.read_csv(paths['sea2017'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "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>identifier</th>\n",
       "      <th>trade_update_date</th>\n",
       "      <th>run_date</th>\n",
       "      <th>vessel_name</th>\n",
       "      <th>port_of_unlading</th>\n",
       "      <th>estimated_arrival_date</th>\n",
       "      <th>foreign_port_of_lading</th>\n",
       "      <th>record_status_indicator</th>\n",
       "      <th>place_of_receipt</th>\n",
       "      <th>port_of_destination</th>\n",
       "      <th>...</th>\n",
       "      <th>shipper_comm_number_qualifier</th>\n",
       "      <th>shipper_comm_number</th>\n",
       "      <th>container_number</th>\n",
       "      <th>description_sequence_number</th>\n",
       "      <th>piece_count</th>\n",
       "      <th>description_text</th>\n",
       "      <th>harmonized_number</th>\n",
       "      <th>harmonized_value</th>\n",
       "      <th>harmonized_weight</th>\n",
       "      <th>harmonized_weight_unit</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>201712261102</td>\n",
       "      <td>2017-11-22T00:00:00</td>\n",
       "      <td>2017-12-26T00:00:00</td>\n",
       "      <td>NEW YORK EXPRESS</td>\n",
       "      <td>Seattle, Washington</td>\n",
       "      <td>2017-12-21T00:00:00</td>\n",
       "      <td>Hong Kong,Hong Kong</td>\n",
       "      <td>New</td>\n",
       "      <td>NANSHA CHINA</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>TCNU3666378</td>\n",
       "      <td>1</td>\n",
       "      <td>127</td>\n",
       "      <td>SOFA &amp; UPHOLSTERY PO418287/418285/418967 \"SH \"</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>201712261102</td>\n",
       "      <td>2017-11-22T00:00:00</td>\n",
       "      <td>2017-12-26T00:00:00</td>\n",
       "      <td>NEW YORK EXPRESS</td>\n",
       "      <td>Seattle, Washington</td>\n",
       "      <td>2017-12-21T00:00:00</td>\n",
       "      <td>Hong Kong,Hong Kong</td>\n",
       "      <td>New</td>\n",
       "      <td>NANSHA CHINA</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>YMLU8500198</td>\n",
       "      <td>1</td>\n",
       "      <td>136</td>\n",
       "      <td>SOFA &amp; UPHOLSTERY PO418287/418285/418967 \"SH \"</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>201712261102</td>\n",
       "      <td>2017-11-22T00:00:00</td>\n",
       "      <td>2017-12-26T00:00:00</td>\n",
       "      <td>NEW YORK EXPRESS</td>\n",
       "      <td>Seattle, Washington</td>\n",
       "      <td>2017-12-21T00:00:00</td>\n",
       "      <td>Hong Kong,Hong Kong</td>\n",
       "      <td>New</td>\n",
       "      <td>NANSHA CHINA</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>YMLU8687140</td>\n",
       "      <td>1</td>\n",
       "      <td>56</td>\n",
       "      <td>SOFA &amp; UPHOLSTERY PO418287/418285/418967 \"SH \"</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>201712261237</td>\n",
       "      <td>2017-11-23T00:00:00</td>\n",
       "      <td>2017-12-26T00:00:00</td>\n",
       "      <td>YM UNANIMITY</td>\n",
       "      <td>Seattle, Washington</td>\n",
       "      <td>2017-12-20T00:00:00</td>\n",
       "      <td>Tsingtao,China (Mainland)</td>\n",
       "      <td>New</td>\n",
       "      <td>QINGDAO</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>UACU5040364</td>\n",
       "      <td>1</td>\n",
       "      <td>1263</td>\n",
       "      <td>PE TARPAULIN THIS SHIPMENT CONTAINS NO WOOD P ...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>201712261259</td>\n",
       "      <td>2017-11-29T00:00:00</td>\n",
       "      <td>2017-12-26T00:00:00</td>\n",
       "      <td>NEW YORK EXPRESS</td>\n",
       "      <td>Seattle, Washington</td>\n",
       "      <td>2017-12-21T00:00:00</td>\n",
       "      <td>Pusan,South Korea</td>\n",
       "      <td>New</td>\n",
       "      <td>DALIAN CHINA</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>YMLU8721007</td>\n",
       "      <td>1</td>\n",
       "      <td>37</td>\n",
       "      <td>WOODEN DOORS</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 31 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "     identifier    trade_update_date             run_date       vessel_name  \\\n",
       "0  201712261102  2017-11-22T00:00:00  2017-12-26T00:00:00  NEW YORK EXPRESS   \n",
       "1  201712261102  2017-11-22T00:00:00  2017-12-26T00:00:00  NEW YORK EXPRESS   \n",
       "2  201712261102  2017-11-22T00:00:00  2017-12-26T00:00:00  NEW YORK EXPRESS   \n",
       "3  201712261237  2017-11-23T00:00:00  2017-12-26T00:00:00      YM UNANIMITY   \n",
       "4  201712261259  2017-11-29T00:00:00  2017-12-26T00:00:00  NEW YORK EXPRESS   \n",
       "\n",
       "      port_of_unlading estimated_arrival_date     foreign_port_of_lading  \\\n",
       "0  Seattle, Washington    2017-12-21T00:00:00        Hong Kong,Hong Kong   \n",
       "1  Seattle, Washington    2017-12-21T00:00:00        Hong Kong,Hong Kong   \n",
       "2  Seattle, Washington    2017-12-21T00:00:00        Hong Kong,Hong Kong   \n",
       "3  Seattle, Washington    2017-12-20T00:00:00  Tsingtao,China (Mainland)   \n",
       "4  Seattle, Washington    2017-12-21T00:00:00          Pusan,South Korea   \n",
       "\n",
       "  record_status_indicator place_of_receipt port_of_destination  \\\n",
       "0                     New     NANSHA CHINA                 NaN   \n",
       "1                     New     NANSHA CHINA                 NaN   \n",
       "2                     New     NANSHA CHINA                 NaN   \n",
       "3                     New          QINGDAO                 NaN   \n",
       "4                     New     DALIAN CHINA                 NaN   \n",
       "\n",
       "           ...            shipper_comm_number_qualifier shipper_comm_number  \\\n",
       "0          ...                                      NaN                 NaN   \n",
       "1          ...                                      NaN                 NaN   \n",
       "2          ...                                      NaN                 NaN   \n",
       "3          ...                                      NaN                 NaN   \n",
       "4          ...                                      NaN                 NaN   \n",
       "\n",
       "  container_number description_sequence_number piece_count  \\\n",
       "0      TCNU3666378                           1         127   \n",
       "1      YMLU8500198                           1         136   \n",
       "2      YMLU8687140                           1          56   \n",
       "3      UACU5040364                           1        1263   \n",
       "4      YMLU8721007                           1          37   \n",
       "\n",
       "                                    description_text harmonized_number  \\\n",
       "0     SOFA & UPHOLSTERY PO418287/418285/418967 \"SH \"               NaN   \n",
       "1     SOFA & UPHOLSTERY PO418287/418285/418967 \"SH \"               NaN   \n",
       "2     SOFA & UPHOLSTERY PO418287/418285/418967 \"SH \"               NaN   \n",
       "3  PE TARPAULIN THIS SHIPMENT CONTAINS NO WOOD P ...               NaN   \n",
       "4                                       WOODEN DOORS               NaN   \n",
       "\n",
       "  harmonized_value harmonized_weight harmonized_weight_unit  \n",
       "0              NaN               NaN                    NaN  \n",
       "1              NaN               NaN                    NaN  \n",
       "2              NaN               NaN                    NaN  \n",
       "3              NaN               NaN                    NaN  \n",
       "4              NaN               NaN                    NaN  \n",
       "\n",
       "[5 rows x 31 columns]"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sea2017_df.head()"
   ]
  },
  {
   "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>identifier</th>\n",
       "      <th>foreign_port_of_destination</th>\n",
       "      <th>description_sequence_number</th>\n",
       "      <th>piece_count</th>\n",
       "      <th>harmonized_number</th>\n",
       "      <th>harmonized_value</th>\n",
       "      <th>harmonized_weight</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>count</th>\n",
       "      <td>2.710000e+04</td>\n",
       "      <td>0.0</td>\n",
       "      <td>27100.000000</td>\n",
       "      <td>27100.000000</td>\n",
       "      <td>1.597600e+04</td>\n",
       "      <td>1.643600e+04</td>\n",
       "      <td>16436.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>mean</th>\n",
       "      <td>1.018439e+12</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2.278155</td>\n",
       "      <td>355.122509</td>\n",
       "      <td>3.327146e+07</td>\n",
       "      <td>2.503881e+05</td>\n",
       "      <td>11218.306461</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>std</th>\n",
       "      <td>9.064852e+11</td>\n",
       "      <td>NaN</td>\n",
       "      <td>8.894217</td>\n",
       "      <td>1087.079547</td>\n",
       "      <td>4.898259e+08</td>\n",
       "      <td>7.272369e+05</td>\n",
       "      <td>28254.145238</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>min</th>\n",
       "      <td>2.017123e+10</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>2.013000e+04</td>\n",
       "      <td>0.000000e+00</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25%</th>\n",
       "      <td>2.017123e+11</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>8.000000</td>\n",
       "      <td>2.931900e+05</td>\n",
       "      <td>0.000000e+00</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>50%</th>\n",
       "      <td>2.017123e+11</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>26.000000</td>\n",
       "      <td>4.409100e+05</td>\n",
       "      <td>0.000000e+00</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>75%</th>\n",
       "      <td>2.017123e+12</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>393.250000</td>\n",
       "      <td>8.422900e+05</td>\n",
       "      <td>4.243400e+05</td>\n",
       "      <td>23885.750000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>max</th>\n",
       "      <td>2.017123e+12</td>\n",
       "      <td>NaN</td>\n",
       "      <td>133.000000</td>\n",
       "      <td>101134.000000</td>\n",
       "      <td>9.404210e+09</td>\n",
       "      <td>9.748180e+06</td>\n",
       "      <td>487409.000000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         identifier  foreign_port_of_destination  description_sequence_number  \\\n",
       "count  2.710000e+04                          0.0                 27100.000000   \n",
       "mean   1.018439e+12                          NaN                     2.278155   \n",
       "std    9.064852e+11                          NaN                     8.894217   \n",
       "min    2.017123e+10                          NaN                     1.000000   \n",
       "25%    2.017123e+11                          NaN                     1.000000   \n",
       "50%    2.017123e+11                          NaN                     1.000000   \n",
       "75%    2.017123e+12                          NaN                     1.000000   \n",
       "max    2.017123e+12                          NaN                   133.000000   \n",
       "\n",
       "         piece_count  harmonized_number  harmonized_value  harmonized_weight  \n",
       "count   27100.000000       1.597600e+04      1.643600e+04       16436.000000  \n",
       "mean      355.122509       3.327146e+07      2.503881e+05       11218.306461  \n",
       "std      1087.079547       4.898259e+08      7.272369e+05       28254.145238  \n",
       "min         1.000000       2.013000e+04      0.000000e+00           0.000000  \n",
       "25%         8.000000       2.931900e+05      0.000000e+00           0.000000  \n",
       "50%        26.000000       4.409100e+05      0.000000e+00           0.000000  \n",
       "75%       393.250000       8.422900e+05      4.243400e+05       23885.750000  \n",
       "max    101134.000000       9.404210e+09      9.748180e+06      487409.000000  "
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sea2017_df.describe()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Counting Cities\n",
    "\n",
    "I'll start by just enumerating the sources of the material imported into our fair city (as of 2017, according to this data set):"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Vancouver, BC ,Canada         13102\n",
       "Pusan,South Korea              3240\n",
       "Shanghai ,China (Mainland)     2936\n",
       "Ningpo ,China (Mainland)       1999\n",
       "Yantian,China (Mainland)        999\n",
       "Hong Kong,Hong Kong             940\n",
       "Xiamen,China (Mainland)         777\n",
       "57037                           721\n",
       "Sines,Portugal                  373\n",
       "Auckland,New Zealand            317\n",
       "Manzanillo,Mexico               266\n",
       "Cartagena ,Colombia             181\n",
       "Puerto Quetzal ,Guatemala       148\n",
       "Kaohsiung,China (Taiwan)        142\n",
       "La Spezia,Italy                 121\n",
       "Name: foreign_port_of_lading, dtype: int64"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sea2017_df['foreign_port_of_lading'].value_counts()[:15]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The counts of numbers of entries in the table originating in each city might be interesting (and relatively straightforward) to visualize.  First, I'll need to clean up and reformat the city names.\n",
    "\n",
    "To begin with, it's apparent from the list above that some of the ports of lading are just numerical codes.  By Googling, I find [resources](https://www.cbp.gov/sites/default/files/assets/documents/2017-Feb/appendix_f_0.pdf) that can be used to translate them.  For instance, 57037 is Yangshan, China.  I wonder how many such values there are."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "57037    721\n",
       "57073      2\n",
       "35136      1\n",
       "Name: foreign_port_of_lading, dtype: int64"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "matches = sea2017_df['foreign_port_of_lading'].str.match(r'[\\d.]{5}')\n",
    "sea2017_df.loc[matches, 'foreign_port_of_lading'].value_counts()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Ok, just 3 unique entries.  That should be quick to fix.  Except that I can't seem to find any city corresponding to 35136.  It's probably an error in the data entry."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [],
   "source": [
    "origin_codes_dict = {'57037': 'Yangshan ,China (Mainland)', \n",
    "                     '57073': 'Nansha ,China (Mainland)',\n",
    "                     '35136': np.nan\n",
    "                    }\n",
    "\n",
    "sea2017_df['foreign_port_of_lading'].replace(origin_codes_dict, \n",
    "                                             inplace=True)\n",
    "\n",
    "# No point keeping record with no city listed\n",
    "sea2017_df = sea2017_df[sea2017_df['foreign_port_of_lading'].notnull()]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "count                     27099\n",
      "unique                       77\n",
      "top       Vancouver, BC ,Canada\n",
      "freq                      13102\n",
      "Name: foreign_port_of_lading, dtype: object\n"
     ]
    }
   ],
   "source": [
    "print(sea2017_df['foreign_port_of_lading'].describe())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now, let's see where we get our imports from.  I'll want to use [plotly](https://plot.ly/python/bubble-maps/) to make a bubble map in a global mercator projection.  To get the coordinates for the cities, I'd like to try programmatic access to [Google Geocoding](https://developers.google.com/maps/documentation/geocoding/start), provided via [geopy](https://pypi.python.org/pypi/geopy/1.9.1).  One question is whether the interface is smart enough to handle the messy strings provided in the data set, or whether I'll have to clean them up, first."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/anaconda3/lib/python3.6/site-packages/requests/__init__.py:80: RequestsDependencyWarning: urllib3 (1.22) or chardet (2.3.0) doesn't match a supported version!\n",
      "  RequestsDependencyWarning)\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "None not found\n",
      "None not found\n",
      "中鋼, 鳳宮里, 大林蒲, 小港區, 高雄市, 812, 臺灣 22.5447429 120.356598755463\n"
     ]
    }
   ],
   "source": [
    "import plotly.plotly as plotly\n",
    "from geopy.geocoders import Nominatim\n",
    "\n",
    "geolocator = Nominatim()\n",
    "location1 = geolocator.geocode(\"Kaohsiung,China (Taiwan)\")\n",
    "location2 = geolocator.geocode(\"Kaohsiung, China (Taiwan)\")\n",
    "location3 = geolocator.geocode(\"Kaohsiung, China\")\n",
    "locations = [location1, location2, location3]\n",
    "for location in locations:\n",
    "    if location:\n",
    "        print(location, location.latitude, location.longitude)\n",
    "    else:\n",
    "        print('{} not found'.format(location))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Ok, yeah; they need to be cleaned up.  We have cities always first, then a country, with a comma in between.  But we have some with no spaces on either side of the comma, with spaces on one side or the other, or on both sides, and none appear to be tolerated.  This definitely requires some regex.  Or maybe just '`.split()`' and '`.strip()`'."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Vancouver, BC, Canada    13102\n",
       "Pusan, South Korea        3240\n",
       "Shanghai, China           2936\n",
       "Ningpo, China             1999\n",
       "Yantian, China             999\n",
       "Hong Kong, Hong Kong       940\n",
       "Xiamen, China              777\n",
       "Yangshan, China            721\n",
       "Sines, Portugal            373\n",
       "Auckland, New Zealand      317\n",
       "Name: lading_formatted, dtype: int64"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import re\n",
    "\n",
    "words = re.compile('[A-z]+')\n",
    "\n",
    "def get_words(source):\n",
    "    stuff = re.sub(r'\\([^)]*\\)', '', source)\n",
    "    parts = stuff.split(sep=',')\n",
    "    formatted = []\n",
    "    for part in parts:\n",
    "        new_parts = []\n",
    "        part = part.strip()\n",
    "        parts_words = re.findall(words, part)\n",
    "        new_parts.append(' '.join(parts_words))\n",
    "        formatted.append(', '.join(new_parts))\n",
    "    return str(', '.join(formatted))\n",
    "\n",
    "sea2017_df['lading_formatted'] = sea2017_df['foreign_port_of_lading']\\\n",
    ".astype(str).apply(get_words)\n",
    "\n",
    "sea2017_df['lading_formatted'].value_counts()[:10]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Man, that took forever; I kept trying to exclude the junk in parentheses with a regex exclude clause, and couldn't get it to work.  It turns out quicker to create a new variable using `re.sub` to just get rid of anything in parentheses.  Anyways, now I want to feed them to geolocator iteratively, and build a new column with any lat/long I get out.\n",
    "\n",
    "I had some trouble with the default `Nominatim` geolocator, imported above, when trying to run the cell below.  It's specifically calling upon the [Openstreetmap server](http://geopy.readthedocs.io/en/latest/#geopy.geocoders.Nominatim), which leaves too many cities without coords.  Also, after trying to re-run them, I'm seeing timeout and other server-interaction errors.  So instead I ended up trying a couple others, and `ArcGIS` seems to work the best."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {
    "scrolled": false
   },
   "outputs": [],
   "source": [
    "geolocator = geocoders.ArcGIS()\n",
    "\n",
    "cities = sea2017_df['lading_formatted'].unique()\n",
    "lat_long = dict()\n",
    "failures = []\n",
    "\n",
    "for city in cities:\n",
    "    location = geolocator.geocode(city)\n",
    "    if location:\n",
    "        lat = location.latitude\n",
    "        long = location.longitude\n",
    "        lat_long[city] = (lat, long)\n",
    "    else:\n",
    "        failures.append(city)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[('Hong Kong, Hong Kong', (22.351958323000076, 114.1193859870001)),\n",
       " ('Tsingtao, China', (36.098610000000065, 120.37194000000011)),\n",
       " ('Pusan, South Korea', (35.10278000000005, 129.04028000000005)),\n",
       " ('Yantian, China', (26.853330000000028, 119.85750000000007)),\n",
       " ('Shanghai, China', (31.22222000000005, 121.45806000000005)),\n",
       " ('Ningpo, China', (29.86569000000003, 121.53916000000004)),\n",
       " ('Yangshan, China', (32.925670000000025, 119.8071900000001)),\n",
       " ('Vancouver, BC, Canada', (49.260380000000055, -123.11335999999994)),\n",
       " ('Sines, Portugal', (37.957250000000045, -8.86934999999994)),\n",
       " ('Yentai, China', (37.52803000000006, 121.3826600000001))]"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "from itertools import islice\n",
    "\n",
    "list(lat_long.items())[:10]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "77 0\n"
     ]
    }
   ],
   "source": [
    "print(len(lat_long.keys()), len(failures))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Using '`geolocator2 = geocoders.GoogleV3()`' yields:\n",
    "\n",
    "    GeocoderQueryError: HTTP Error 400: Bad Request\n",
    "\n",
    "Probably something outdated about the API/geocode interface.  I tried getting an API key, which worked, but it still returns '`Bad Request`'.\n",
    "\n",
    "Anyways, I ended up trying [ArcGIS](https://developers.arcgis.com/rest/geocode/api-reference/overview-world-geocoding-service.htm), and that was sufficient to get the rest."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [],
   "source": [
    "def lookup(city):\n",
    "    if city in lat_long.keys():\n",
    "        coords = lat_long[city]\n",
    "    else:\n",
    "        coords = np.nan\n",
    "    return coords\n",
    "\n",
    "sea2017_df['lat_long'] = sea2017_df['lading_formatted'].astype(str).apply(lookup)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {
    "scrolled": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Cities lacking lat-long: 0.00%\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>identifier</th>\n",
       "      <th>trade_update_date</th>\n",
       "      <th>run_date</th>\n",
       "      <th>vessel_name</th>\n",
       "      <th>port_of_unlading</th>\n",
       "      <th>estimated_arrival_date</th>\n",
       "      <th>foreign_port_of_lading</th>\n",
       "      <th>record_status_indicator</th>\n",
       "      <th>place_of_receipt</th>\n",
       "      <th>port_of_destination</th>\n",
       "      <th>...</th>\n",
       "      <th>container_number</th>\n",
       "      <th>description_sequence_number</th>\n",
       "      <th>piece_count</th>\n",
       "      <th>description_text</th>\n",
       "      <th>harmonized_number</th>\n",
       "      <th>harmonized_value</th>\n",
       "      <th>harmonized_weight</th>\n",
       "      <th>harmonized_weight_unit</th>\n",
       "      <th>lading_formatted</th>\n",
       "      <th>lat_long</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>201712261102</td>\n",
       "      <td>2017-11-22T00:00:00</td>\n",
       "      <td>2017-12-26T00:00:00</td>\n",
       "      <td>NEW YORK EXPRESS</td>\n",
       "      <td>Seattle, Washington</td>\n",
       "      <td>2017-12-21T00:00:00</td>\n",
       "      <td>Hong Kong,Hong Kong</td>\n",
       "      <td>New</td>\n",
       "      <td>NANSHA CHINA</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>TCNU3666378</td>\n",
       "      <td>1</td>\n",
       "      <td>127</td>\n",
       "      <td>SOFA &amp; UPHOLSTERY PO418287/418285/418967 \"SH \"</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Hong Kong, Hong Kong</td>\n",
       "      <td>(22.351958323000076, 114.1193859870001)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>201712261102</td>\n",
       "      <td>2017-11-22T00:00:00</td>\n",
       "      <td>2017-12-26T00:00:00</td>\n",
       "      <td>NEW YORK EXPRESS</td>\n",
       "      <td>Seattle, Washington</td>\n",
       "      <td>2017-12-21T00:00:00</td>\n",
       "      <td>Hong Kong,Hong Kong</td>\n",
       "      <td>New</td>\n",
       "      <td>NANSHA CHINA</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>YMLU8500198</td>\n",
       "      <td>1</td>\n",
       "      <td>136</td>\n",
       "      <td>SOFA &amp; UPHOLSTERY PO418287/418285/418967 \"SH \"</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Hong Kong, Hong Kong</td>\n",
       "      <td>(22.351958323000076, 114.1193859870001)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>201712261102</td>\n",
       "      <td>2017-11-22T00:00:00</td>\n",
       "      <td>2017-12-26T00:00:00</td>\n",
       "      <td>NEW YORK EXPRESS</td>\n",
       "      <td>Seattle, Washington</td>\n",
       "      <td>2017-12-21T00:00:00</td>\n",
       "      <td>Hong Kong,Hong Kong</td>\n",
       "      <td>New</td>\n",
       "      <td>NANSHA CHINA</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>YMLU8687140</td>\n",
       "      <td>1</td>\n",
       "      <td>56</td>\n",
       "      <td>SOFA &amp; UPHOLSTERY PO418287/418285/418967 \"SH \"</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Hong Kong, Hong Kong</td>\n",
       "      <td>(22.351958323000076, 114.1193859870001)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>201712261237</td>\n",
       "      <td>2017-11-23T00:00:00</td>\n",
       "      <td>2017-12-26T00:00:00</td>\n",
       "      <td>YM UNANIMITY</td>\n",
       "      <td>Seattle, Washington</td>\n",
       "      <td>2017-12-20T00:00:00</td>\n",
       "      <td>Tsingtao,China (Mainland)</td>\n",
       "      <td>New</td>\n",
       "      <td>QINGDAO</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>UACU5040364</td>\n",
       "      <td>1</td>\n",
       "      <td>1263</td>\n",
       "      <td>PE TARPAULIN THIS SHIPMENT CONTAINS NO WOOD P ...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Tsingtao, China</td>\n",
       "      <td>(36.098610000000065, 120.37194000000011)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>201712261259</td>\n",
       "      <td>2017-11-29T00:00:00</td>\n",
       "      <td>2017-12-26T00:00:00</td>\n",
       "      <td>NEW YORK EXPRESS</td>\n",
       "      <td>Seattle, Washington</td>\n",
       "      <td>2017-12-21T00:00:00</td>\n",
       "      <td>Pusan,South Korea</td>\n",
       "      <td>New</td>\n",
       "      <td>DALIAN CHINA</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>YMLU8721007</td>\n",
       "      <td>1</td>\n",
       "      <td>37</td>\n",
       "      <td>WOODEN DOORS</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Pusan, South Korea</td>\n",
       "      <td>(35.10278000000005, 129.04028000000005)</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 33 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "     identifier    trade_update_date             run_date       vessel_name  \\\n",
       "0  201712261102  2017-11-22T00:00:00  2017-12-26T00:00:00  NEW YORK EXPRESS   \n",
       "1  201712261102  2017-11-22T00:00:00  2017-12-26T00:00:00  NEW YORK EXPRESS   \n",
       "2  201712261102  2017-11-22T00:00:00  2017-12-26T00:00:00  NEW YORK EXPRESS   \n",
       "3  201712261237  2017-11-23T00:00:00  2017-12-26T00:00:00      YM UNANIMITY   \n",
       "4  201712261259  2017-11-29T00:00:00  2017-12-26T00:00:00  NEW YORK EXPRESS   \n",
       "\n",
       "      port_of_unlading estimated_arrival_date     foreign_port_of_lading  \\\n",
       "0  Seattle, Washington    2017-12-21T00:00:00        Hong Kong,Hong Kong   \n",
       "1  Seattle, Washington    2017-12-21T00:00:00        Hong Kong,Hong Kong   \n",
       "2  Seattle, Washington    2017-12-21T00:00:00        Hong Kong,Hong Kong   \n",
       "3  Seattle, Washington    2017-12-20T00:00:00  Tsingtao,China (Mainland)   \n",
       "4  Seattle, Washington    2017-12-21T00:00:00          Pusan,South Korea   \n",
       "\n",
       "  record_status_indicator place_of_receipt port_of_destination  \\\n",
       "0                     New     NANSHA CHINA                 NaN   \n",
       "1                     New     NANSHA CHINA                 NaN   \n",
       "2                     New     NANSHA CHINA                 NaN   \n",
       "3                     New          QINGDAO                 NaN   \n",
       "4                     New     DALIAN CHINA                 NaN   \n",
       "\n",
       "                     ...                     container_number  \\\n",
       "0                    ...                          TCNU3666378   \n",
       "1                    ...                          YMLU8500198   \n",
       "2                    ...                          YMLU8687140   \n",
       "3                    ...                          UACU5040364   \n",
       "4                    ...                          YMLU8721007   \n",
       "\n",
       "  description_sequence_number piece_count  \\\n",
       "0                           1         127   \n",
       "1                           1         136   \n",
       "2                           1          56   \n",
       "3                           1        1263   \n",
       "4                           1          37   \n",
       "\n",
       "                                    description_text harmonized_number  \\\n",
       "0     SOFA & UPHOLSTERY PO418287/418285/418967 \"SH \"               NaN   \n",
       "1     SOFA & UPHOLSTERY PO418287/418285/418967 \"SH \"               NaN   \n",
       "2     SOFA & UPHOLSTERY PO418287/418285/418967 \"SH \"               NaN   \n",
       "3  PE TARPAULIN THIS SHIPMENT CONTAINS NO WOOD P ...               NaN   \n",
       "4                                       WOODEN DOORS               NaN   \n",
       "\n",
       "  harmonized_value harmonized_weight harmonized_weight_unit  \\\n",
       "0              NaN               NaN                    NaN   \n",
       "1              NaN               NaN                    NaN   \n",
       "2              NaN               NaN                    NaN   \n",
       "3              NaN               NaN                    NaN   \n",
       "4              NaN               NaN                    NaN   \n",
       "\n",
       "       lading_formatted                                  lat_long  \n",
       "0  Hong Kong, Hong Kong   (22.351958323000076, 114.1193859870001)  \n",
       "1  Hong Kong, Hong Kong   (22.351958323000076, 114.1193859870001)  \n",
       "2  Hong Kong, Hong Kong   (22.351958323000076, 114.1193859870001)  \n",
       "3       Tsingtao, China  (36.098610000000065, 120.37194000000011)  \n",
       "4    Pusan, South Korea   (35.10278000000005, 129.04028000000005)  \n",
       "\n",
       "[5 rows x 33 columns]"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "print('Cities lacking lat-long: {0:.2f}%'.format(sea2017_df['lat_long'].isnull().sum() / sea2017_df.shape[1] * 100))\n",
    "sea2017_df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Ok, even though ArcGIS worked, it is finicky, and requires multiple tries, timing out often.  Since I've gotten the coordinates for the cities in a useable df, let's save this, and reload it in the future."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [],
   "source": [
    "paths['sea2017_cities_coords_pkl'] = \\\n",
    "os.path.join(paths['enigma_dir'], 'sea2017_cities_coords.pkl')\n",
    "\n",
    "with open(paths['sea2017_cities_coords_pkl'], 'wb') as f:\n",
    "    pickle.dump(sea2017_df, f)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'/Users/drew/data/Enigma'"
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "paths['enigma_dir']"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# ~~If Restarting the Notebook, Resume with Cell Below~~ (Outdated)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 50,
   "metadata": {},
   "outputs": [],
   "source": [
    "# I often switch between editing notebooks from my PC to my Mac;\n",
    "# the code in this cell is to find the proper local data when I do so.\n",
    "\n",
    "import platform\n",
    "import os\n",
    "import locale\n",
    "import platform\n",
    "import numpy as np\n",
    "import pandas as pd\n",
    "import multiprocessing as mp\n",
    "import pickle\n",
    "import seaborn as sns\n",
    "import matplotlib.pyplot as plt\n",
    "\n",
    "o_s = platform.system()\n",
    "paths = {}\n",
    "\n",
    "if o_s == 'Darwin':\n",
    "    paths['data_dir'] = '/Users/drew/data/Enigma'\n",
    "elif o_s == 'Windows':\n",
    "    paths['data_dir'] = r'C:\\Users\\DMacKellar\\Documents\\Data\\\n",
    "\\Enigma'\n",
    "\n",
    "locale.setlocale(locale.LC_ALL, '')\n",
    "\n",
    "for root, dirs, files in os.walk(paths['data_dir']):\n",
    "    for file in files:\n",
    "        file_under = str(file).replace('.', '_')\n",
    "        path = os.path.join(root, file)\n",
    "        paths[file_under] = path\n",
    "        \n",
    "# That should populate all data files.  Whenever a loading call fails, do:\n",
    "# print(*paths.keys(), sep='\\n')\n",
    "# And look for the key with the proper name (with underscore instead of period)\n",
    "\n",
    "with open(paths['sea2017_cities_coords_pkl'], 'rb') as pickle_file:\n",
    "    sea2017_df = pickle.load(pickle_file)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now I want to count the total number of containers arriving based on the city of origin, and move them into a new dataframe indexed by city name, and including the coordinates of each."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [],
   "source": [
    "city_coords = sea2017_df['lat_long'].groupby(\n",
    "    sea2017_df['lading_formatted']).unique().rename('coords')\n",
    "\n",
    "# The lat/long are trapped in unnecessary lists: bust them out\n",
    "city_coords = city_coords.apply(lambda x: x[0])\n",
    "\n",
    "city_counts = sea2017_df['lading_formatted'].value_counts().rename('containers')\n",
    "\n",
    "city_records = pd.concat([city_coords, city_counts], axis=1)\n",
    "\n",
    "# Actually, let's just make lat and long explicit columns\n",
    "city_records['lat'] = city_records['coords'].apply(lambda x: (x)[0])\n",
    "city_records['long'] = city_records['coords'].apply(lambda x: (x)[1])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "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>coords</th>\n",
       "      <th>containers</th>\n",
       "      <th>lat</th>\n",
       "      <th>long</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>Adelaide, Australia</th>\n",
       "      <td>(-34.92584999999997, 138.5998300000001)</td>\n",
       "      <td>13</td>\n",
       "      <td>-34.925850</td>\n",
       "      <td>138.599830</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>All Other Oman Ports, Oman</th>\n",
       "      <td>(42.26966173436233, 26.92581511332239)</td>\n",
       "      <td>1</td>\n",
       "      <td>42.269662</td>\n",
       "      <td>26.925815</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Anvers, Belgium</th>\n",
       "      <td>(51.22209000000004, 4.397680000000037)</td>\n",
       "      <td>6</td>\n",
       "      <td>51.222090</td>\n",
       "      <td>4.397680</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Auckland, New Zealand</th>\n",
       "      <td>(-36.84841014899996, 174.76437652000004)</td>\n",
       "      <td>317</td>\n",
       "      <td>-36.848410</td>\n",
       "      <td>174.764377</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Balboa, Panama</th>\n",
       "      <td>(8.370532224000044, -78.95069304799995)</td>\n",
       "      <td>90</td>\n",
       "      <td>8.370532</td>\n",
       "      <td>-78.950693</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                                              coords  \\\n",
       "Adelaide, Australia          (-34.92584999999997, 138.5998300000001)   \n",
       "All Other Oman Ports, Oman    (42.26966173436233, 26.92581511332239)   \n",
       "Anvers, Belgium               (51.22209000000004, 4.397680000000037)   \n",
       "Auckland, New Zealand       (-36.84841014899996, 174.76437652000004)   \n",
       "Balboa, Panama               (8.370532224000044, -78.95069304799995)   \n",
       "\n",
       "                            containers        lat        long  \n",
       "Adelaide, Australia                 13 -34.925850  138.599830  \n",
       "All Other Oman Ports, Oman           1  42.269662   26.925815  \n",
       "Anvers, Belgium                      6  51.222090    4.397680  \n",
       "Auckland, New Zealand              317 -36.848410  174.764377  \n",
       "Balboa, Panama                      90   8.370532  -78.950693  "
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "city_records.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Alright, let's try making the plot.  Since I don't know Plotly that well, I'll start by trying to import [a cell with an example from their docs](https://plot.ly/python/bubble-maps/) wholesale, then modify the most likely fields.  But I keep getting errors on the final line, referring to an error loading a URL or signing.  It appears that [Plotly wants you to get an API key](https://plot.ly/python/getting-started/#initialization-for-online-plotting), so I signed up and I'll store the key locally:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [],
   "source": [
    "import json\n",
    "\n",
    "# DCM code for retrieving credentials\n",
    "paths['plotly_cred'] = os.path.join(paths['data_dir'], 'plotly_api_credentials.json')\n",
    "\n",
    "with open(paths['plotly_cred'], 'r') as f:\n",
    "    cred = json.load(f)\n",
    "    \n",
    "plotly_username = cred['username']\n",
    "plotly_api_key = cred['api_key']"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "First, a bare-bones example to simply see if the map will render with the cities in their proper location:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 53,
   "metadata": {
    "scrolled": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<iframe id=\"igraph\" scrolling=\"no\" style=\"border:none;\" seamless=\"seamless\" src=\"https://plot.ly/~mackellardrew/45.embed\" height=\"525px\" width=\"100%\"></iframe>"
      ],
      "text/plain": [
       "<plotly.tools.PlotlyDisplay object>"
      ]
     },
     "execution_count": 53,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "trace = dict(type='scattergeo',\n",
    "             lon=city_records['long'].values,\n",
    "             lat=city_records['lat'].values,\n",
    "             text=city_records.index.values.tolist(),\n",
    "             mode='markers')\n",
    "paths['first_plotly_html'] = os.path.join(paths['data_dir'], 'first_plotly.html')\n",
    "offline.plot(fig, filename=paths['first_plotly_html'])\n",
    "py.iplot([trace])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Ok, cool; that was easy.  Now to adapt the tutorial's code to get the more elaborate example, where you partition the data into groups of cities based on some variable.  In this case, I'll choose based on the number of shipments they made to Seattle in 2017.  I'd like a dynamic way to do this, so I'll choose the `pd.qcut` method:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {
    "scrolled": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "0 (0.0, 2.0]\n",
      "1 (2.0, 22.0]\n",
      "2 (22.0, 13102.0]\n"
     ]
    }
   ],
   "source": [
    "city_records['containers'].quantile(np.round(np.linspace(0, 1, num=4))).values.tolist()\n",
    "\n",
    "group_labels = list(((np.rint(x.left).astype(int), \n",
    "                      np.round(x.right).astype(int)) for x in pd.qcut(x=city_records['containers'], q=5, precision=0, duplicates='drop').values.unique().sort_values()))\n",
    "\n",
    "group_labels\n",
    "for i, interval in enumerate(pd.qcut(x=city_records['containers'], q=3, precision=0).values.unique().sort_values()):\n",
    "    print(i, interval)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now, in the process of making the graph, I encountered a lot of problems that I ended up working through piecemeal; they'll be explained in the markdown cell directly after the graph below.  But, after figuring out what I needed to do, I cleaned up this notebook, and just kept what worked.  So, for now, just accept that the next two cells are necessary.  First, I move the city names in the summary dataframe out of the index and into its own column, which gets named 'city'.  Then, I specifically write a new column containing the text that I'll want to show up as hovertext in the plot."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {},
   "outputs": [],
   "source": [
    "city_records.reset_index(inplace=True)\n",
    "city_records.rename(columns={'index':'city'}, inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {},
   "outputs": [],
   "source": [
    "city_records['text'] = np.nan\n",
    "for i, row in city_records.iterrows():\n",
    "    city_records.loc[i, 'text'] = '{}: {} ship(s)'.format(row['city'], row['containers'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 55,
   "metadata": {
    "scrolled": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<iframe id=\"igraph\" scrolling=\"no\" style=\"border:none;\" seamless=\"seamless\" src=\"https://plot.ly/~mackellardrew/14.embed\" height=\"525px\" width=\"100%\"></iframe>"
      ],
      "text/plain": [
       "<plotly.tools.PlotlyDisplay object>"
      ]
     },
     "execution_count": 55,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "set_credentials_file(username=plotly_username, api_key=plotly_api_key)\n",
    "\n",
    "q = 3\n",
    "city_records['quant'] = pd.qcut(\n",
    "    x=city_records['containers'], q=q, precision=0, duplicates='drop')\n",
    "\n",
    "colors = [\"rgb(0,116,217)\",\"rgb(255,65,54)\",\"rgb(133,20,75)\",\"rgb(255,133,27)\",\"lightgrey\"]\n",
    "cities = []\n",
    "scale = 3\n",
    "log_scale = 100\n",
    "\n",
    "for i, (interval, group) in enumerate(city_records.groupby('quant')):\n",
    "    lim = interval\n",
    "    city = dict(\n",
    "        type = 'scattergeo',\n",
    "        locationmode = 'ISO-3',\n",
    "        lon = group['long'],\n",
    "        lat = group['lat'],\n",
    "        text = group['text'],\n",
    "        marker = dict(\n",
    "#             size = group['containers'] * scale, \n",
    "            size = np.log(group['containers']) * log_scale,\n",
    "            color = colors[i],\n",
    "            line = dict(width=0.5, color='rgb(40,40,40)'),\n",
    "            sizemode = 'area'\n",
    "        ),\n",
    "        name = '{:,} - {:,} ships sent'.format(\n",
    "            np.rint(lim.left).astype(int),\n",
    "            np.rint(lim.right).astype(int)) \n",
    "    )\n",
    "    cities.append(city)\n",
    "\n",
    "layout = dict(\n",
    "        title = 'Ports of Lading for Ships <br />Arriving in Seattle in 2017',\n",
    "        showlegend = True,\n",
    "        geo = dict(\n",
    "            scope='world',\n",
    "            projection=dict( type='Mercator' ),\n",
    "            showland = True,\n",
    "            landcolor = 'rgb(217, 217, 217)',\n",
    "            subunitwidth=1,\n",
    "            countrywidth=1,\n",
    "            subunitcolor=\"rgb(255, 255, 255)\",\n",
    "            countrycolor=\"rgb(255, 255, 255)\"\n",
    "        ),\n",
    "    )\n",
    "\n",
    "fig = dict( data=cities, layout=layout )\n",
    "paths['seattle_2017_plotly1_html'] = os.path.join(paths['data_dir'], 'seattle_2017_plotly1.html')\n",
    "offline.plot(fig, filename=paths['seattle_2017_plotly1_html'])\n",
    "py.iplot( fig, validate=False, filename='sea2017_ports_of_lading' )"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Lessons Learned from First Plotly Experience\n",
    "\n",
    "Ok, this one took me a long time to figure out.  It was kind of a chain reaction of failures in adapting the code from the [plotly bubble map example](https://plot.ly/python/bubble-maps/#united-states-bubble-map) where they varied the size of city markers based on population.  At first, I tried mostly just copying their entire set of code, and substituting my dataframe's lat and lon columns for theirs, but when it came to the legend, I didn't want to just shoehorn my data into their general approach.\n",
    "\n",
    "The tutorial example used 5 \"limit\" categories, defining the quantiles of population ranks for US cities.  The thinking was obviously to make a dynamic legend that you could click on to reduce the clutter of the map to focus on specific subsets of cities: the 50th through 3000th largest cities in the US are far more numerous and far less populous than the top 50; you might want to turn off the small cities if all you care about is major metropolitan areas.  Conversely, NYC leaves an obnoxiously large imprint on the map, covering up other areas that you might want to be able to check out, so you can turn off NY and LA and see the rest of the map better, if that's your focus.\n",
    "\n",
    "But their explicit assignment of those rank intervals was a kind of inflexible approach, so I wanted to redefine the legend to be broken up into cities that send more or less stuff to Seattle on the basis of the `df.qcut` method, which breaks your rows up into quantiles of some category, then pass those to the dict-building `for` loop from the tutorial code in order to build my own custom legend.  I tried initializing the `for` loop with:\n",
    "\n",
    "    for interval, group in city_records.groupby('quant'):\n",
    "\n",
    "That worked ok for passing lat and long coords straight from the dataframe, but when I went to adapt the hover text to display the number of ships that each city sent, I ran into trouble.  I tried using:\n",
    "\n",
    "    text = '{}: {} ships'.format(group.index, group['containers'])\n",
    "    \n",
    "But whereas the `lat`, `lon`, and marker.size categories would accept arrays as input and parse them correctly, when I tried to set the text that way, it ended up passing the same array for every single row, breaking the hover text.\n",
    "\n",
    "So then I got the bright idea to break out the data within the `groupby for` loop into individual rows, using:\n",
    "\n",
    "    for interval, group in city_records.groupby('quant'):\n",
    "        for index, row in group.iterrows():\n",
    "        \n",
    "And then set every lat, lon, and text value individually with each row's unique index.  And when I ran the cell, it would execute without complaint, but the resulting map would be empty: no legend, and no city markers.  I had previously noted that the cities might not be visible if you set the marker scale too small, in which case you have to fiddle with that parameter to make them apparent, but you could still always get hovertext when you moused over the map, meaning that the cities were still being plotted.  This time, the hovertext didn't show; plotly was obviously not successfully reading the cities specified by the `for` loop.\n",
    "\n",
    "I tried outputting the `cities` list of dicts and parsing it by eye to see if it made sense, and compared the formatting to the list of dicts output by the tutorial example (of US cities, sized by population), and couldn't detect any missing fields or other dumb mistakes that would cause it to fail to plot.  I tried a lot of comparisons to no effect; the dict looked ok.  Then, finally, I happened to try checking the last dict in the tutorial example, and noticed that it looked very different from the first.  It turns out that the `for` loop from the tutorial doesn't write an individual dict for each city; it writes one dict for each legend item, or group of cities: one for each of the five groups of ranked city sizes that the author of the tutorial defined in their example.  So plotly wasn't expecting 77 dicts in the list `cities`, each with their own neatly-contained and well-defined keys and values, but rather a dict with an array of lat coords, an array of lon coords, and, for the value assigned to key 'marker', a dict containing an array of sizes corresponding to each city.\n",
    "\n",
    "I tried wrenching the nested `for` loop using `iterrows` to fit this output, but couldn't get it to work.  So I abandoned the nested loop and went back to assigning variables from just the `group`s within `groupby`, but was still stuck with the problem of not being able to dynamically assign text.  So I reverted to the approach they took in the tutorial example: they explicitly create a new column containing the data from the other columns already in the dataframe.  Then you can pass an array of previously-defined strings to plotly in a way it apparently can understand, rather than making every entry the exact same array, with too much info for each city.\n",
    "\n",
    "I had a little further trouble assigning the new `'text'` column dynamically with the iterrows function and a `'{}: {} ship(s)'.format()` call; it turns out you [can't initialize an entirely new column that way](https://stackoverflow.com/a/31460668/8637821), so you create a new column just with `np.nan` first, and use the for loop to replace them.  Finally, pandas couldn't find the city names as indices when you use iterrows in that way, and I noticed that the tutorial example just had default integer row indices, so I had to use `reset_index` to move the city names into their own column, rename the column so that it wouldn't get confused with the actual row index any more, and finally got the plot to render with the city name and number of shipments as hovertext (hence the two cells above the plot, resetting the index and specifying a new text column for each city, prior to setting up the plot itself).\n",
    "\n",
    "~~The final wrinkle for now was that, even though you could now hide Vancouver by clicking on the proper legend dot, the other cities were still too small to be visible easily on the same scale, so I adapted the size variable by calling `np.log(group['containers'])` to reduce the variation between the sizes, then, since the markers were now too small, multiplied, rather than dividing, by the scale factor.  That's a bit of a fudge, but we all make compromises when visualizing data.~~  I go back and forth on this last point; I think that having their sizes manageable to see is useful, but taking the log really makes the range of shipping less intuitive.  You can comment out either of the above `size` parameters in the `markers` dict in order to see either approach.\n",
    "\n",
    "Note, also, that you can dynamically alter the graph by changing the number of quantiles set by the first arg, '`q`'.  But the `qcut` method seeks to make all the bins of the same size, and since the most cities send very very few ships, you end up with multiple quantiles that contain just one or two records, and since the intervals must be unique, it can't display them separately, meaning you can't get all that many quantiles to display this way.  You can avoid getting warnings for overlapping quantiles by calling the arg `drop_duplicates=True`, but it'll still plot fewer than you call for.  There may be a way to skew the quantiles to preferentially break up the groups that contain more ships, but I haven't found a way to do this yet."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Checking Against Other Cities/Years\n",
    "\n",
    "Now, the task is to take what I've learned, and generalize the pipeline so I can easily feed in different time ranges or cities of destination in order to do comparisons with others from the Enigma bills of lading dataset.  I think a good workflow would be to have a few cells that could accept a csv file containing a subset of the Enigma db's shipping data from any given year, focused on the city of destination, and then reformat the city names, retrieve coordinates, and map them by number of containers shipped, as I've done above for Seattle in 2017.  It would be cool to have a more programmatic interface to cut out some of the human-centric downloading and moving files around, and their [API](http://docs.enigma.com/public/public_v20_user_pandas.html#api_export) should ultimately be compatible with this, but for now I'll settle with just having the work I did above to map one city's imports extrapolate to others in a matter of minutes.\n",
    "\n",
    "Let's start with comparing Seattle's imports to that of a few other ports.  According to the frequency rank view of the summary statistics for the [Bill of Lading Summary db in Enigma for 2017](https://public.enigma.com/datasets/0293cd20-8580-4d30-b173-2ac27952b74b), the busiest ports that year were:\n",
    "\n",
    "    Long Beach, California:   118,493 records\n",
    "    Los Angeles, California:  105,329\n",
    "    New York/Newark, NJ:       94,620\n",
    "    Houston, Texas:            39,555\n",
    "    Tacoma, Washington:        37,106\n",
    "    \n",
    "I had previously heard from a friend that Long Beach was the busiest US port for cargo traffic, but didn't know that LA proper has almost as much total activity; together, SoCal is definitely the hub for import in the US.  Coming in at a close third is no surprise: NYC has been the commercial hub for the country for a long time, although they moved the bulk of traffic from being processed in Manhattan [over to New Jersey in the mid-20th century](https://en.wikipedia.org/wiki/New_York_Harbor#Container_shipping_and_air_travel).  It's interesting that Houston is next; I hadn't heard of them being a particularly big depot, although perhaps it makes sense, as it's geographically closer to the center of the continental states than New York or LA.  Finally, I had also previously heard that Tacoma is larger than Seattle when it comes to cargo traffic.\n",
    "\n",
    "That's a good cross-section of major commercial traffic in the US, let's try plotting them and seeing whether the city of origin for imports varies based on their destination among US ports.  I'll import the data, reformat the city names, download coords, and try to get them to plot in different subplots with plotly."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Specify a list of files to be opened, processed, and plotted\n",
    "cities_2017_paths = {'Long_Beach': paths['longbeach_shipping_2017_csv'],\n",
    "                     'Los_Angeles': paths['losangeles_shipping_2017_csv'],\n",
    "                     'NYC_Newark': paths['nyc_shipping_2017_csv'],\n",
    "                     'Houston': paths['houston_shipping_2017_csv'],\n",
    "                     'Tacoma': paths['tacoma_shipping_2017_csv'],\n",
    "                     'Seattle': paths['seattle_shipping_2017_csv']}\n",
    "cities_2017_dfs = {}\n",
    "\n",
    "# Import each into Pandas\n",
    "for city, path in cities_2017_paths.items():\n",
    "    cities_2017_dfs[city] = pd.read_csv(path)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 56,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Check for ports of lading with numeric code, rather than names\n",
    "def code_lookup(dict_of_dfs):\n",
    "    matches_set = set()\n",
    "    for city, df in dict_of_dfs.items():\n",
    "        matches = df['foreign_port_of_lading'].str.match(r'[\\d]+')\n",
    "        matches_series = df.loc[matches, \n",
    "                                'foreign_port_of_lading'].value_counts()\n",
    "        matches_set.update(matches_series.index.values)\n",
    "#         print('{}:\\n{}'.format(city, matches_series))\n",
    "    return matches_set\n",
    "\n",
    "code_matches = code_lookup(cities_2017_dfs)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Consolidating Data Aggregation/Cleanup; Applying to Other Ports of Unlading\n",
    "\n",
    "I'm going to try to recapitulate the process I used above to get the lat-long coords for ports of lading to Seattle, and extend it as smoothly as possible to the other cities.  I'll see how succinctly I can achieve that."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Specify a list of files to be opened, processed, and plotted\n",
    "cities_2017_paths = {'Long_Beach': paths['longbeach_shipping_2017_csv'],\n",
    "                     'Los_Angeles': paths['losangeles_shipping_2017_csv'],\n",
    "                     'NYC_Newark': paths['nyc_shipping_2017_csv'],\n",
    "                     'Houston': paths['houston_shipping_2017_csv'],\n",
    "                     'Tacoma': paths['tacoma_shipping_2017_csv'],\n",
    "                     'Seattle': paths['seattle_shipping_2017_csv']}"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "metadata": {},
   "outputs": [],
   "source": [
    "# DCM code for retrieving credentials\n",
    "paths['plotly_cred'] = os.path.join(paths['data_dir'], 'plotly_api_credentials.json')\n",
    "paths['google_cred'] = os.path.join(paths['data_dir'], 'google_api_cred.json')\n",
    "\n",
    "with open(paths['plotly_cred'], 'r') as f:\n",
    "    plotly_cred = json.load(f)\n",
    "with open(paths['google_cred'], 'r') as f:\n",
    "    google_cred = json.load(f)\n",
    "    \n",
    "plotly_username = plotly_cred['username']\n",
    "plotly_api_key = plotly_cred['api_key']\n",
    "google_api_key = google_cred['api_key']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Specify the dict of codes to replace with city names\n",
    "origin_codes_dict = {'57073': 'Nansha, China (Mainland)', \n",
    "                     '53304': 'Pipavav, India', \n",
    "                     '30110': 'Covenas, Columbia', \n",
    "                     '57037': 'Yangshan, China (Mainland)'\n",
    "                    }\n",
    "\n",
    "\n",
    "def import_shipping_data(paths):\n",
    "    cities_dfs = {}\n",
    "    # Import each port of unlading's shipping records into Pandas\n",
    "    for city, path in paths.items():\n",
    "        cities_dfs[city] = pd.read_csv(path)\n",
    "    return cities_dfs\n",
    "\n",
    "\n",
    "def replace_city_codes(cities_dfs, codes_dict):\n",
    "    # Initialize counters to print how many records were dropped\n",
    "    records_dropped = 0\n",
    "    total_records = 0\n",
    "    replaced = {}\n",
    "    # First, see if any 5-digit codes present in dfs\n",
    "    # were missing from dict; if so, add to dict as NaN\n",
    "    for city, df in cities_dfs.items():\n",
    "        matches = df.loc[:,'foreign_port_of_lading'].str.match(r'[\\d.]{5}')\n",
    "        match_list = df.loc[matches, \n",
    "                            'foreign_port_of_lading'].unique().tolist()\n",
    "        for match in match_list:\n",
    "            if match not in codes_dict:\n",
    "                codes_dict[match] = np.nan\n",
    "                print('\"{}\" not found in input dict; \\\n",
    "adding to dict as np.nan.'.format(match))\n",
    "            else:\n",
    "                pass\n",
    "        # Replace codes with cities where possible; drop the rest\n",
    "        new_df = df.copy()\n",
    "        new_df.loc[:,'foreign_port_of_lading'].replace(codes_dict, \n",
    "                                                       inplace=True)\n",
    "        # No point keeping record with no city listed\n",
    "        records_dropped += new_df['foreign_port_of_lading'].isnull().sum()\n",
    "        total_records += new_df.shape[0]\n",
    "        new_df = new_df[new_df.loc[:,'foreign_port_of_lading'].notnull()]\n",
    "        replaced[city] = new_df\n",
    "    # Add a print line to summarize how many records were discarded\n",
    "    print('\\n{} records were dropped from bills of lading \\\n",
    "because no foreign port was found with the numeric code given; \\\n",
    "amounting to {:.8f}% of all records.'.format(\n",
    "    records_dropped, records_dropped*100/total_records))\n",
    "    return replaced\n",
    "\n",
    "\n",
    "def get_words(source):\n",
    "    words = re.compile('[A-z]+')\n",
    "    # Get rid of '(Mainland)' and other parenthetical stuff\n",
    "    noparens = re.sub(r'\\([^)]*\\)', '', source)\n",
    "    # Separate city from country\n",
    "    parts = noparens.split(sep=',')\n",
    "    formatted = []\n",
    "    # Process city and country separately\n",
    "    for part in parts:\n",
    "        new_parts = []\n",
    "        # Get rid of leading/trailing whitespace\n",
    "        part = part.strip()\n",
    "        # Discard punctuation, numbers; any non-words\n",
    "        parts_words = re.findall(words, part)\n",
    "        # Separate individual words by a single space\n",
    "        new_parts.append(' '.join(parts_words))\n",
    "        # Re-join city and country, separated by comma and space\n",
    "        formatted.append(', '.join(new_parts))\n",
    "    return str(', '.join(formatted))\n",
    "\n",
    "\n",
    "def format_ports(cities_dfs):\n",
    "    new_port_dfs = {}\n",
    "    # Use pandas' vectorized function, \n",
    "    # rather than iterating over rows slowly\n",
    "    for city, df in cities_dfs.items():\n",
    "        df.loc[:,'foreign_port_of_lading'] = df.loc[:,\n",
    "            'foreign_port_of_lading'].astype(str).apply(get_words)\n",
    "        new_port_dfs[city] = df\n",
    "    return new_port_dfs\n",
    "\n",
    "\n",
    "def geolocate(cities_dfs):\n",
    "    # ArcGIS no longer tolerates me; use Google\n",
    "    geolocator = geocoders.GoogleV3(api_key=google_api_key,\n",
    "                                    timeout=100)\n",
    "    all_foreign = set()\n",
    "    lat_long = dict()\n",
    "    failures = []\n",
    "    # Don't spam the server with redundant calls;\n",
    "    # first gather all unique cities together\n",
    "    for city, df in cities_dfs.items():\n",
    "        all_foreign.update(\n",
    "            df.loc[:,'foreign_port_of_lading'].unique().astype(str))\n",
    "    # Feed all unique cities to server, save output\n",
    "    # Either in dict with coords, or else as failures\n",
    "    for port in all_foreign:\n",
    "        location = geolocator.geocode(port)\n",
    "        if location:\n",
    "            lat = location.latitude\n",
    "            long = location.longitude\n",
    "            lat_long[port] = (lat, long)\n",
    "        else:\n",
    "            failures.append(port)\n",
    "    return lat_long, failures\n",
    "\n",
    "            \n",
    "def lookup(port, lat_long_dict):\n",
    "    if port in lat_long_dict.keys():\n",
    "        coords = lat_long_dict[port]\n",
    "    else:\n",
    "        coords = np.nan\n",
    "    return coords\n",
    "\n",
    "\n",
    "def get_coords(cities_dfs, lat_long_dict):\n",
    "    coordinated = {}\n",
    "    # Again, use vectorized functions\n",
    "    for city, df in cities_dfs.items():\n",
    "        new_df = df.copy()\n",
    "        new_df['lat'] = np.nan\n",
    "        new_df['long'] = np.nan\n",
    "        new_df['lat_long'] = np.nan\n",
    "        new_df['lat_long'] = new_df['foreign_port_of_lading'].astype(\n",
    "            str).apply(lookup, lat_long_dict=lat_long_dict)\n",
    "        lat_dict = {}\n",
    "        long_dict = {}\n",
    "        for port, coords in lat_long_dict.items():\n",
    "            lat_dict[port] = coords[0]\n",
    "            long_dict[port] = coords[1]\n",
    "        new_df['lat'] = new_df['foreign_port_of_lading'].astype(\n",
    "            str).apply(lookup, lat_long_dict=lat_dict)\n",
    "        new_df['long'] = new_df['foreign_port_of_lading'].astype(\n",
    "            str).apply(lookup, lat_long_dict=long_dict)\n",
    "        # drop any without coords\n",
    "        empties = new_df.loc[:,'lat_long'].isnull().sum()\n",
    "        print('{:<12}\\nCoordinates couldn\\'t be found for {:>5} \\\n",
    "ports of lading; {:>.2f}% of all records'.format(\n",
    "            city, empties, empties/new_df.shape[0]*100))\n",
    "        new_df.dropna(subset=['lat_long'], inplace=True)\n",
    "        coordinated[city] = new_df\n",
    "    return coordinated\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "It took me significantly longer than I expected to refactor the approach that worked above for Seattle into a series of functions that would make for a straightforward pipeline to modify the dataframes for several ports of unlading.  I still don't have a lot of experience writing extended pipelines of functions; I could've left a lot of the code the same, but wanted to be able to invoke the whole process in as few lines as possible.\n",
    "\n",
    "I ended up having to write two new functions (`format_ports` and `get_coords`) to replace the `.apply(custom_func)` calls that I used in the original case to both reformat the `foreign_port_of_lading` strings and then to populate the `lat_long` column from the dict.  I could've left those calls separate but, again, I wanted to simplify.  I could also still write a single wrapper function to reduce the 5 lines in the cell below with a single call, but I found it useful to keep them separate while troubleshooting the various functions.  Now that they work I'll still keep them separate.\n",
    "\n",
    "I'm aware that defining a single class, with each of these as methods within, is probably the canonical, Pythonic way to go, but I have even less experience at present with proper class coding, so I'll pass on that for now."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "metadata": {
    "scrolled": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\"35136\" not found in input dict; adding to dict as np.nan.\n",
      "\"57045\" not found in input dict; adding to dict as np.nan.\n",
      "\"55225\" not found in input dict; adding to dict as np.nan.\n",
      "\"57011\" not found in input dict; adding to dict as np.nan.\n",
      "\"35180\" not found in input dict; adding to dict as np.nan.\n",
      "\n",
      "905 records were dropped from bills of lading because no foreign port was found with the numeric code given; amounting to 0.21435186% of all records.\n",
      "Long_Beach  \n",
      "Coordinates couldn't be found for 14260 ports of lading; 12.04% of all records\n",
      "Los_Angeles \n",
      "Coordinates couldn't be found for 13659 ports of lading; 12.97% of all records\n",
      "NYC_Newark  \n",
      "Coordinates couldn't be found for 26096 ports of lading; 27.70% of all records\n",
      "Houston     \n",
      "Coordinates couldn't be found for 19372 ports of lading; 49.58% of all records\n",
      "Tacoma      \n",
      "Coordinates couldn't be found for 11785 ports of lading; 31.76% of all records\n",
      "Seattle     \n",
      "Coordinates couldn't be found for     0 ports of lading; 0.00% of all records\n",
      "CPU times: user 18.3 s, sys: 1.69 s, total: 20 s\n",
      "Wall time: 20.8 s\n"
     ]
    }
   ],
   "source": [
    "%%time\n",
    "\n",
    "cities_2017_dfs = import_shipping_data(cities_2017_paths)\n",
    "cities_2017_dfs = replace_city_codes(cities_2017_dfs, origin_codes_dict)\n",
    "cities_2017_dfs = format_ports(cities_2017_dfs)\n",
    "# lat_long, failures = geolocate(cities_2017_dfs)\n",
    "cities_2017_dfs = get_coords(cities_2017_dfs, lat_long)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "dict_keys(['Long_Beach', 'Los_Angeles', 'NYC_Newark', 'Houston', 'Tacoma', 'Seattle'])"
      ]
     },
     "execution_count": 39,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "cities_2017_dfs.keys()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "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>identifier</th>\n",
       "      <th>trade_update_date</th>\n",
       "      <th>run_date</th>\n",
       "      <th>vessel_name</th>\n",
       "      <th>port_of_unlading</th>\n",
       "      <th>estimated_arrival_date</th>\n",
       "      <th>foreign_port_of_lading</th>\n",
       "      <th>record_status_indicator</th>\n",
       "      <th>place_of_receipt</th>\n",
       "      <th>port_of_destination</th>\n",
       "      <th>...</th>\n",
       "      <th>description_sequence_number</th>\n",
       "      <th>piece_count</th>\n",
       "      <th>description_text</th>\n",
       "      <th>harmonized_number</th>\n",
       "      <th>harmonized_value</th>\n",
       "      <th>harmonized_weight</th>\n",
       "      <th>harmonized_weight_unit</th>\n",
       "      <th>lat</th>\n",
       "      <th>long</th>\n",
       "      <th>lat_long</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>201712261102</td>\n",
       "      <td>2017-11-22T00:00:00</td>\n",
       "      <td>2017-12-26T00:00:00</td>\n",
       "      <td>NEW YORK EXPRESS</td>\n",
       "      <td>Seattle, Washington</td>\n",
       "      <td>2017-12-21T00:00:00</td>\n",
       "      <td>Hong Kong, Hong Kong</td>\n",
       "      <td>New</td>\n",
       "      <td>NANSHA CHINA</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>1</td>\n",
       "      <td>127</td>\n",
       "      <td>SOFA &amp; UPHOLSTERY PO418287/418285/418967 \"SH \"</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>22.351958</td>\n",
       "      <td>114.119386</td>\n",
       "      <td>(22.351958323000076, 114.1193859870001)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>201712261102</td>\n",
       "      <td>2017-11-22T00:00:00</td>\n",
       "      <td>2017-12-26T00:00:00</td>\n",
       "      <td>NEW YORK EXPRESS</td>\n",
       "      <td>Seattle, Washington</td>\n",
       "      <td>2017-12-21T00:00:00</td>\n",
       "      <td>Hong Kong, Hong Kong</td>\n",
       "      <td>New</td>\n",
       "      <td>NANSHA CHINA</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>1</td>\n",
       "      <td>136</td>\n",
       "      <td>SOFA &amp; UPHOLSTERY PO418287/418285/418967 \"SH \"</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>22.351958</td>\n",
       "      <td>114.119386</td>\n",
       "      <td>(22.351958323000076, 114.1193859870001)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>201712261102</td>\n",
       "      <td>2017-11-22T00:00:00</td>\n",
       "      <td>2017-12-26T00:00:00</td>\n",
       "      <td>NEW YORK EXPRESS</td>\n",
       "      <td>Seattle, Washington</td>\n",
       "      <td>2017-12-21T00:00:00</td>\n",
       "      <td>Hong Kong, Hong Kong</td>\n",
       "      <td>New</td>\n",
       "      <td>NANSHA CHINA</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>1</td>\n",
       "      <td>56</td>\n",
       "      <td>SOFA &amp; UPHOLSTERY PO418287/418285/418967 \"SH \"</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>22.351958</td>\n",
       "      <td>114.119386</td>\n",
       "      <td>(22.351958323000076, 114.1193859870001)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>201712261237</td>\n",
       "      <td>2017-11-23T00:00:00</td>\n",
       "      <td>2017-12-26T00:00:00</td>\n",
       "      <td>YM UNANIMITY</td>\n",
       "      <td>Seattle, Washington</td>\n",
       "      <td>2017-12-20T00:00:00</td>\n",
       "      <td>Tsingtao, China</td>\n",
       "      <td>New</td>\n",
       "      <td>QINGDAO</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>1</td>\n",
       "      <td>1263</td>\n",
       "      <td>PE TARPAULIN THIS SHIPMENT CONTAINS NO WOOD P ...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>36.098610</td>\n",
       "      <td>120.371940</td>\n",
       "      <td>(36.098610000000065, 120.37194000000011)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>201712261259</td>\n",
       "      <td>2017-11-29T00:00:00</td>\n",
       "      <td>2017-12-26T00:00:00</td>\n",
       "      <td>NEW YORK EXPRESS</td>\n",
       "      <td>Seattle, Washington</td>\n",
       "      <td>2017-12-21T00:00:00</td>\n",
       "      <td>Pusan, South Korea</td>\n",
       "      <td>New</td>\n",
       "      <td>DALIAN CHINA</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>1</td>\n",
       "      <td>37</td>\n",
       "      <td>WOODEN DOORS</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>35.102780</td>\n",
       "      <td>129.040280</td>\n",
       "      <td>(35.10278000000005, 129.04028000000005)</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 34 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "     identifier    trade_update_date             run_date       vessel_name  \\\n",
       "0  201712261102  2017-11-22T00:00:00  2017-12-26T00:00:00  NEW YORK EXPRESS   \n",
       "1  201712261102  2017-11-22T00:00:00  2017-12-26T00:00:00  NEW YORK EXPRESS   \n",
       "2  201712261102  2017-11-22T00:00:00  2017-12-26T00:00:00  NEW YORK EXPRESS   \n",
       "3  201712261237  2017-11-23T00:00:00  2017-12-26T00:00:00      YM UNANIMITY   \n",
       "4  201712261259  2017-11-29T00:00:00  2017-12-26T00:00:00  NEW YORK EXPRESS   \n",
       "\n",
       "      port_of_unlading estimated_arrival_date foreign_port_of_lading  \\\n",
       "0  Seattle, Washington    2017-12-21T00:00:00   Hong Kong, Hong Kong   \n",
       "1  Seattle, Washington    2017-12-21T00:00:00   Hong Kong, Hong Kong   \n",
       "2  Seattle, Washington    2017-12-21T00:00:00   Hong Kong, Hong Kong   \n",
       "3  Seattle, Washington    2017-12-20T00:00:00        Tsingtao, China   \n",
       "4  Seattle, Washington    2017-12-21T00:00:00     Pusan, South Korea   \n",
       "\n",
       "  record_status_indicator place_of_receipt port_of_destination  \\\n",
       "0                     New     NANSHA CHINA                 NaN   \n",
       "1                     New     NANSHA CHINA                 NaN   \n",
       "2                     New     NANSHA CHINA                 NaN   \n",
       "3                     New          QINGDAO                 NaN   \n",
       "4                     New     DALIAN CHINA                 NaN   \n",
       "\n",
       "                     ...                     description_sequence_number  \\\n",
       "0                    ...                                               1   \n",
       "1                    ...                                               1   \n",
       "2                    ...                                               1   \n",
       "3                    ...                                               1   \n",
       "4                    ...                                               1   \n",
       "\n",
       "  piece_count                                   description_text  \\\n",
       "0         127     SOFA & UPHOLSTERY PO418287/418285/418967 \"SH \"   \n",
       "1         136     SOFA & UPHOLSTERY PO418287/418285/418967 \"SH \"   \n",
       "2          56     SOFA & UPHOLSTERY PO418287/418285/418967 \"SH \"   \n",
       "3        1263  PE TARPAULIN THIS SHIPMENT CONTAINS NO WOOD P ...   \n",
       "4          37                                       WOODEN DOORS   \n",
       "\n",
       "  harmonized_number harmonized_value harmonized_weight harmonized_weight_unit  \\\n",
       "0               NaN              NaN               NaN                    NaN   \n",
       "1               NaN              NaN               NaN                    NaN   \n",
       "2               NaN              NaN               NaN                    NaN   \n",
       "3               NaN              NaN               NaN                    NaN   \n",
       "4               NaN              NaN               NaN                    NaN   \n",
       "\n",
       "         lat        long                                  lat_long  \n",
       "0  22.351958  114.119386   (22.351958323000076, 114.1193859870001)  \n",
       "1  22.351958  114.119386   (22.351958323000076, 114.1193859870001)  \n",
       "2  22.351958  114.119386   (22.351958323000076, 114.1193859870001)  \n",
       "3  36.098610  120.371940  (36.098610000000065, 120.37194000000011)  \n",
       "4  35.102780  129.040280   (35.10278000000005, 129.04028000000005)  \n",
       "\n",
       "[5 rows x 34 columns]"
      ]
     },
     "execution_count": 40,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "cities_2017_dfs['Seattle'].head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Ok, not too long a time to wait, considering the amount of data being processed.  Most of it was in the lookup time for retrieving the coordinates from Google using `geolocate`.  Note that, while I used ArcGIS for the Seattle df earlier in this notebook, it repeatedly refused to process so many ports of origin this time around.  It [sounds like](https://developers.arcgis.com/rest/geocode/api-reference/geocoding-free-vs-paid.htm) they aren't expecting you to do bulk access of their geolocator db, maybe just a few records at a time.  So I switched to [GoogleV3](https://developers.google.com/maps/documentation/geocoding/start), got an API key, and they were more patient with me (at least once I changed the default `timeout=1` arg upon initialization).  Let's see how many coords are missing:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "metadata": {},
   "outputs": [],
   "source": [
    "for city, df in cities_2017_dfs.items():\n",
    "    if df['lat_long'].isnull().sum() != 0:\n",
    "        print('{:<15}{:.6f}'.format(\n",
    "            city, df['lat_long'].isnull().sum()/df.shape[0]))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "metadata": {},
   "outputs": [],
   "source": [
    "for i, failure in enumerate(failures):\n",
    "    print('{:<4}{}'.format(i, failure))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "So there were significantly more records lost for destinations other than Seattle because Google didn't find the coordinates of the port of lading.  But from looking at the list of the `failures`, I can't blame Google at all; those are some strange, wonky-sounding origins in several cases.  The phenomenon of \"transshipment\" [throws a considerable monkey wrench into things](https://www.usitc.gov/publications/332/ec200404b.pdf), and I'm not gonna solve that issue here.\n",
    "\n",
    "Okay!  Now I'm ready to plot the various ports of lading for these respective US ports of unlading.  I'll drop the rows lacking coord data; I don't expect attempting to cover the exceptions found in `failures` will prove very edifying, and they still collectively represent around 1% or less for each of the cities (except Tacoma; and it could be interesting to look into why someday)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 46,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Houston      size before dropna: \t19699\n",
      "Houston      size after dropna: \t19699\n",
      "Long_Beach   size before dropna: \t104223\n",
      "Long_Beach   size after dropna: \t104223\n",
      "Los_Angeles  size before dropna: \t91661\n",
      "Los_Angeles  size after dropna: \t91661\n",
      "NYC_Newark   size before dropna: \t68123\n",
      "NYC_Newark   size after dropna: \t68123\n",
      "Seattle      size before dropna: \t27099\n",
      "Seattle      size after dropna: \t27099\n",
      "Tacoma       size before dropna: \t25321\n",
      "Tacoma       size after dropna: \t25321\n"
     ]
    }
   ],
   "source": [
    "cities_2017_dfs_clean = {}\n",
    "for city, df in cities_2017_dfs.items():\n",
    "    print('{:<12} size before dropna: \\t{}'.format(city, df.shape[0]))\n",
    "    cities_2017_dfs_clean[city] = df[df['lat_long'].notnull()]\n",
    "    print('{:<12} size after dropna: \\t{}'.format(\n",
    "        city, cities_2017_dfs_clean[city].shape[0]))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Ok, now I'll just want to pickle these (as well as the lat_long dict), so I can access them later:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 60,
   "metadata": {},
   "outputs": [],
   "source": [
    "cities = {'Houston, Texas':'', 'Long Beach, California':'',\n",
    "          'Los Angeles, California':'', 'Newark, New Jersey':'',\n",
    "          'Seattle, Washington':'', 'Tacoma, Washington':''}\n",
    "for city in cities.keys():\n",
    "    location = geolocator.geocode(city)\n",
    "    if location:\n",
    "        lat = location.latitude\n",
    "        long = location.longitude\n",
    "        cities[city] = (lat, long)\n",
    "unlading_coords = cities\n",
    "\n",
    "for city in cities_2017_dfs.keys():\n",
    "    name = '{}_2017_pkl'.format(city)\n",
    "    filename = '{}_2017.pkl'.format(city)\n",
    "    paths[name] = os.path.join(paths['data_dir'], filename)\n",
    "    \n",
    "for city, df in cities_2017_dfs_clean.items():\n",
    "    name = '{}_2017_pkl'.format(city)\n",
    "    with open(paths[name], 'wb') as f:\n",
    "        pickle.dump(df, f)\n",
    "        \n",
    "paths['lat_long_dict'] = os.path.join(paths['data_dir'], \n",
    "                                      'lat_long_dict.pkl')\n",
    "with open(paths['lat_long_dict'], 'wb') as f:\n",
    "    pickle.dump(lat_long, f)\n",
    "    \n",
    "paths['unlading_coords'] = os.path.join(paths['data_dir'], 'top_6_unlading.pkl')\n",
    "\n",
    "with open(paths['unlading_coords'], 'wb') as f:\n",
    "    pickle.dump(unlading_coords, f)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# If Resuming Notebook, Start With Cell Below"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 65,
   "metadata": {},
   "outputs": [],
   "source": [
    "# I often switch between editing notebooks from my PC to my Mac;\n",
    "# the code in this cell is to find the proper local data when I do so.\n",
    "\n",
    "import platform\n",
    "import os\n",
    "import locale\n",
    "import numpy as np\n",
    "import pandas as pd\n",
    "import multiprocessing as mp\n",
    "import pickle\n",
    "import json\n",
    "\n",
    "o_s = platform.system()\n",
    "paths = {}\n",
    "\n",
    "if o_s == 'Darwin':\n",
    "    paths['data_dir'] = '/Users/drew/data/Enigma'\n",
    "elif o_s == 'Windows':\n",
    "    paths['data_dir'] = r'C:\\Users\\DMacKellar\\Documents\\Data\\\n",
    "\\Enigma'\n",
    "\n",
    "locale.setlocale(locale.LC_ALL, '')\n",
    "\n",
    "# DCM code for retrieving credentials\n",
    "paths['plotly_cred'] = os.path.join(paths['data_dir'], 'plotly_api_credentials.json')\n",
    "paths['google_cred'] = os.path.join(paths['data_dir'], 'google_api_cred.json')\n",
    "\n",
    "with open(paths['plotly_cred'], 'r') as f:\n",
    "    plotly_cred = json.load(f)\n",
    "with open(paths['google_cred'], 'r') as f:\n",
    "    google_cred = json.load(f)\n",
    "    \n",
    "plotly_username = plotly_cred['username']\n",
    "plotly_api_key = plotly_cred['api_key']\n",
    "google_api_key = google_cred['api_key']\n",
    "\n",
    "for root, dirs, files in os.walk(paths['data_dir']):\n",
    "    for file in files:\n",
    "        file_under = str(file).replace('.', '_')\n",
    "        path = os.path.join(root, file)\n",
    "        paths[file_under] = path\n",
    "        \n",
    "# That should populate all data files.  Whenever a loading call fails, do:\n",
    "# print(*paths.keys(), sep='\\n')\n",
    "# And look for the key with the proper name (with underscore instead of period)\n",
    "\n",
    "# For now, just load the 6 dfs with lat_long coords\n",
    "cities_2017_dfs = {}\n",
    "for path in paths:\n",
    "    if path[-9:] == '_2017_pkl':\n",
    "        with open(paths[path], 'rb') as f:\n",
    "            name = str(path[:-9])\n",
    "            cities_2017_dfs[name] = pickle.load(f)\n",
    "\n",
    "# Load api credentials\n",
    "with open(paths['credentials_json'], 'r') as f:\n",
    "    credentials = json.load(f)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 80,
   "metadata": {},
   "outputs": [],
   "source": [
    "# For each port of unlading, aggregate ports of lading\n",
    "def get_counts(dict_of_dfs):\n",
    "    city_counts_dfs = {}\n",
    "\n",
    "    for city, df in dict_of_dfs.items():\n",
    "        # Getting tired of typing out 'foreign_port_of_lading' all the time\n",
    "        df1 = df.copy()\n",
    "        df1.rename(columns={'foreign_port_of_lading':'city'}, inplace=True)\n",
    "        grouped = df1.groupby(df1['city'])\n",
    "        containers = grouped.size().to_dict()\n",
    "        df1['containers'] = df1.loc[:, 'city'\n",
    "                                 ].apply(lambda x: containers[x])\n",
    "        cols = ['city', 'lat_long', \n",
    "                'lat', 'long', 'containers']\n",
    "        unique = df1.drop_duplicates(subset=['city'])\n",
    "        city_records = unique.loc[:, cols]\n",
    "\n",
    "        # Make a column to contain the 'text' data you want in hovertext:\n",
    "        city_records['text'] = np.nan\n",
    "        for i, row in city_records.iterrows():\n",
    "            city_records.loc[i, 'text'] = '{}: {} container(s)'.format(\n",
    "                row['city'], row['containers'])\n",
    "        city_counts_dfs[city] = city_records\n",
    "    return city_counts_dfs"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 81,
   "metadata": {
    "scrolled": false
   },
   "outputs": [],
   "source": [
    "city_counts_dfs = get_counts(cities_2017_dfs)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 82,
   "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>city</th>\n",
       "      <th>lat_long</th>\n",
       "      <th>lat</th>\n",
       "      <th>long</th>\n",
       "      <th>containers</th>\n",
       "      <th>text</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Hong Kong, Hong Kong</td>\n",
       "      <td>(22.351958323000076, 114.1193859870001)</td>\n",
       "      <td>22.351958</td>\n",
       "      <td>114.119386</td>\n",
       "      <td>940</td>\n",
       "      <td>Hong Kong, Hong Kong: 940 container(s)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Tsingtao, China</td>\n",
       "      <td>(36.098610000000065, 120.37194000000011)</td>\n",
       "      <td>36.098610</td>\n",
       "      <td>120.371940</td>\n",
       "      <td>23</td>\n",
       "      <td>Tsingtao, China: 23 container(s)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Pusan, South Korea</td>\n",
       "      <td>(35.10278000000005, 129.04028000000005)</td>\n",
       "      <td>35.102780</td>\n",
       "      <td>129.040280</td>\n",
       "      <td>3240</td>\n",
       "      <td>Pusan, South Korea: 3240 container(s)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>Yantian, China</td>\n",
       "      <td>(26.853330000000028, 119.85750000000007)</td>\n",
       "      <td>26.853330</td>\n",
       "      <td>119.857500</td>\n",
       "      <td>999</td>\n",
       "      <td>Yantian, China: 999 container(s)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>Shanghai, China</td>\n",
       "      <td>(31.22222000000005, 121.45806000000005)</td>\n",
       "      <td>31.222220</td>\n",
       "      <td>121.458060</td>\n",
       "      <td>2936</td>\n",
       "      <td>Shanghai, China: 2936 container(s)</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                    city                                  lat_long        lat  \\\n",
       "0   Hong Kong, Hong Kong   (22.351958323000076, 114.1193859870001)  22.351958   \n",
       "3        Tsingtao, China  (36.098610000000065, 120.37194000000011)  36.098610   \n",
       "4     Pusan, South Korea   (35.10278000000005, 129.04028000000005)  35.102780   \n",
       "5         Yantian, China  (26.853330000000028, 119.85750000000007)  26.853330   \n",
       "11       Shanghai, China   (31.22222000000005, 121.45806000000005)  31.222220   \n",
       "\n",
       "          long  containers                                    text  \n",
       "0   114.119386         940  Hong Kong, Hong Kong: 940 container(s)  \n",
       "3   120.371940          23        Tsingtao, China: 23 container(s)  \n",
       "4   129.040280        3240   Pusan, South Korea: 3240 container(s)  \n",
       "5   119.857500         999        Yantian, China: 999 container(s)  \n",
       "11  121.458060        2936      Shanghai, China: 2936 container(s)  "
      ]
     },
     "execution_count": 82,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "city_counts_dfs['Seattle'].head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "It looks like [this](https://plot.ly/python/map-subplots-and-small-multiples/) is the right reference for how to make multiple subplots with maps in plotly.  That example, however, does not use hovertext, and furthermore, each subplot only has one trace.  In the previous example from this notebook, where I made one map for Seattle, I grouped the ports of lading into separate traces, and had the city name and ships sent as the mouse-over text.  It'll require [some modification](https://community.plot.ly/t/adding-traces-to-subplots/4424) to the hyperlinked example code to get this to work.  As a fall-back, maybe I can abandon having multiple traces of groupings of ports of lading, and just use log scaling to make all markers visible on each subplot (since I wouldn't then be able to turn each group off individually).\n",
    "\n",
    "Ok, after playing around with the simpler, `fig.append_trace()` functionality, it's returning errors that \"`'xaxis' is not allowed in 'scattergeo'`\".  It looks like the normal way to do this is to specify a unique '`geo`' arg for each dict in `data`, then add a dict with that '`geo`' label as a keyword within the dict `layout`, and the value for that 'geo' keyword will itself be a dict, which will contain the keyword 'domain', the value of which will be another dict, which will have two keys, 'x' and 'y', each of which will have a list/array with two elements, which will be the limits along the entire figure's x and y axis, as to the outer bounds for that subplot.  [This example](https://plot.ly/python/mixed-subplots/) may be more fruitful a source of inspiration.\n",
    "\n",
    "So, the syntax appears to be:\n",
    "    \n",
    "    trace3 = {\n",
    "        \"geo\": \"geo3\", \n",
    "        \"lon\": df['Longitude'],\n",
    "        \"lat\": df['Latitude'],\n",
    "        \"hoverinfo\": 'text',\n",
    "        \"marker\": {\n",
    "            \"size\": 4,\n",
    "            \"opacity\": 0.8,\n",
    "            \"color\": '#CF1020',\n",
    "            \"colorscale\": 'Viridis'\n",
    "        }, \n",
    "        \"mode\": \"markers\", \n",
    "        \"type\": \"scattergeo\"\n",
    "    }\n",
    "    trace2 = {\n",
    "        \"geo\": \"geo4\"\n",
    "        ...\n",
    "    }\n",
    "    \n",
    "    data = [{trace1}, {trace2}]\n",
    "    \n",
    "    layout = {\n",
    "        ...\n",
    "        \"geo3\": {\n",
    "            \"domain\": {\n",
    "                \"x\": [0, 0.55], \n",
    "                \"y\": [0, 0.9]\n",
    "        }, \n",
    "        \n",
    "    }\n",
    "    \n",
    "It appears that, to get multiple traces in one subplot, you just use the same 'geo' keyword for each, dict appended to data; the layout will then group them all together.  So that's the reason for the somewhat more convoluted way the first example cited above (the one that outputs multiple US maps with WalMart stores over the years): plotly doesn't let you pass a unique axis object associated with each `data` dict when the object is a map, it instead requires that you specify what part of the [0, 1] space of the entire figure's x and y axes you want each trace to occupy, and you indicate that in the layout dict, not within the dict sent to the `data` list for each trace."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 85,
   "metadata": {
    "scrolled": false
   },
   "outputs": [],
   "source": [
    "def plotly_subplots(dict_of_dfs, title, q=4, scale=1, rows=3, cols=2):\n",
    "    set_credentials_file(username=plotly_username, \n",
    "                               api_key=plotly_api_key)\n",
    "\n",
    "    q = 4\n",
    "    colors = [\"rgb(0,116,217)\",\"rgb(255,65,54)\",\"rgb(133,20,75)\",\n",
    "              \"rgb(255,133,27)\",\"lightgrey\"]\n",
    "    data = []\n",
    "    scale = scale\n",
    "\n",
    "    layout = dict(\n",
    "        title = title,\n",
    "        font=dict(family='Arial, sans serif',\n",
    "                  size=22,\n",
    "                  color='rgb(0, 0, 0)'\n",
    "                ),\n",
    "        # showlegend = False,\n",
    "        autosize = True,\n",
    "        width = 1000,\n",
    "        height = 900,\n",
    "        hovermode = True,\n",
    "        legend = dict(\n",
    "            x=1,\n",
    "            y=0.5,\n",
    "            bgcolor=\"rgba(255, 255, 255, 0)\",\n",
    "            font = dict( size=11 ),\n",
    "        )\n",
    "    )\n",
    "\n",
    "    # Want one subplot per port of unlading, but multiple groups\n",
    "    # of ports of lading per subplot\n",
    "    geo_s = []\n",
    "    # Iterate over dict of dfs\n",
    "    for i, (city, df) in enumerate(dict_of_dfs.items()):\n",
    "        # Split each df up into quantiles of ports of lading\n",
    "        # Need a special case for Seattle, to keep # groups right\n",
    "        if city == 'Seattle':\n",
    "            q = 5\n",
    "        else:\n",
    "            q = 4\n",
    "        df['quant'] = pd.qcut(\n",
    "            x=df['containers'], q=q, precision=0, duplicates='drop')\n",
    "        # Specify geo_key, which will tell \n",
    "        # layout_dict where to place subplot\n",
    "        geo_key = 'geo'+str(i+1) if i != 0 else 'geo'\n",
    "        # Go through groups of cities and plot markers\n",
    "        for j, (interval, group) in enumerate(df.groupby('quant')):\n",
    "            lim = interval\n",
    "            label = '{}: <br />{} - {} containers'.format(\n",
    "                city.replace('_', ' '), \n",
    "                np.rint(lim.left).astype(int),\n",
    "                np.rint(lim.right).astype(int)-1)\n",
    "            group_data = dict(\n",
    "                type = 'scattergeo',\n",
    "                geo = geo_key,\n",
    "                locationmode = 'ISO-3',\n",
    "                lat = group['lat'],\n",
    "                lon = group['long'],\n",
    "                text = group['text'],\n",
    "                marker = dict(\n",
    "                    size = group['containers']/scale,\n",
    "    #                 size = np.log(group['containers'])*50,\n",
    "                    color = colors[j],\n",
    "                    line = dict(width=0.5, color='rgb(40,40,40)'),\n",
    "                    sizemode = 'area'\n",
    "                ),\n",
    "                name = label,\n",
    "                legendgroup = label\n",
    "            )\n",
    "            data.append(group_data)\n",
    "\n",
    "        # Add a clear map to show plot titles\n",
    "        data.append(\n",
    "            dict(\n",
    "                type = 'scattergeo',\n",
    "                showlegend = False,\n",
    "                # Always put the text in the same place;\n",
    "                #\n",
    "                lon = [-115],\n",
    "                lat = [-60],\n",
    "                geo = geo_key,\n",
    "                text = '{}'.format(city.replace('_', ' ')),\n",
    "                mode = 'text',\n",
    "                textfont=dict(\n",
    "                    family='Arial, sans serif',\n",
    "                    size=22,\n",
    "                    color='rgb(0, 0, 0)'\n",
    "                )\n",
    "            )\n",
    "        )\n",
    "\n",
    "        layout[geo_key] = dict(\n",
    "            scope='world',\n",
    "            projection=dict( type='Mercator' ),\n",
    "            showland = True,\n",
    "            landcolor = 'rgb(217, 217, 217)',\n",
    "            subunitwidth=1,\n",
    "            countrywidth=1,\n",
    "            subunitcolor=\"rgb(255, 255, 255)\",\n",
    "            countrycolor=\"rgb(255, 255, 255)\",\n",
    "            domain = dict( x = [], y = [] ),\n",
    "        )\n",
    "\n",
    "\n",
    "    z = 0\n",
    "    COLS = cols\n",
    "    ROWS = rows\n",
    "    for y in reversed(range(ROWS)):\n",
    "        for x in range(COLS):\n",
    "            geo_key = 'geo'+str(z+1) if z != 0 else 'geo'\n",
    "            layout[geo_key]['domain']['x'] = [float(x)/float(COLS), \n",
    "                                              float(x+1)/float(COLS)]\n",
    "            layout[geo_key]['domain']['y'] = [float(y)/float(ROWS), \n",
    "                                              float(y+1)/float(ROWS)]\n",
    "            z=z+1\n",
    "            if z > len(data):\n",
    "                break\n",
    "    subp_data = data\n",
    "    subp_layout = layout\n",
    "    \n",
    "    return data, layout"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 87,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<iframe id=\"igraph\" scrolling=\"no\" style=\"border:none;\" seamless=\"seamless\" src=\"https://plot.ly/~mackellardrew/32.embed\" height=\"900px\" width=\"1000px\"></iframe>"
      ],
      "text/plain": [
       "<plotly.tools.PlotlyDisplay object>"
      ]
     },
     "execution_count": 87,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "title = '<b>Ports of Lading Presented by <br />\\\n",
    "Ports of Unlading: 2017</b>'\n",
    "\n",
    "data, layout = plotly_subplots(city_counts_dfs, title=title)\n",
    "\n",
    "fig = { 'data':data, 'layout':layout }\n",
    "paths['subplots_2017_html'] = os.path.join(paths['data_dir'], 'subplots_2017.html')\n",
    "offline.plot(fig, filename=paths['subplots_2017_html'])\n",
    "py.iplot( fig, filename='Ports of Lading', height=900, width=1000 )"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## My First Plotly Maps in Subplots Figure\n",
    "\n",
    "Ok, after a lot of fiddling, I finally got that to look pretty much the way that I want.  With such a specific plotting agenda, it's probably difficult to avoid a lot of fiddling to get the parameters right.\n",
    "\n",
    "Another word on the learning curve: plotly is based on D3.js, i.e. Javascript, and thus has a different kind of feel than the typical Python library, in my experience.  In particular, it seems to involve a lot more dicts, and nested dicts.  The easy access to maps is pretty cool, but for some reason it makes it incompatible with easy creation/access to subplots, thus the comment in the cell above about having to create text annotation via a bunch of independent traces mapped to the same figure-grid coordinates.  Walking through [the example code](https://plot.ly/python/map-subplots-and-small-multiples/) about Walmart stores was very helpful in getting the plot to eventually render for me.  Basically, you feed everything in to one data object (a list of dicts) and one layout object (a dict), and link the two with the `geo` key arg.\n",
    "\n",
    "---\n",
    "\n",
    "So, finally: time to consider some implications of the data.  Yes, Seattle is unique in receiving so much traffic from Vancouver.  Tacoma has far, far less, and in general processes more East Asian traffic, and less from Latin America or Europe.  LA and LBC process the most cargo, overall, and most of it from China: Hong Kong (Yantian) is tops for LA, and Shanghai sends the most to Long Beach.  Both also get a considerable amount from Latin America and Europe; kind of like Seattle's profile, in that regard.\n",
    "\n",
    "**One significant point that I just realized: I've been saying (or at least implying) that individual rows in the data represent unique ships, but now see that they're individual containers, many of them on the same ship.  It's an example of not looking over your data enough in preliminary analysis before jumping into visualization and in-depth analysis.  So I've changed the legend above and will try to fix the rest of the notebook; if I overlook any prior to this point, please disregard.  Such a major oversight should have been fairly obvious, but I'm not really that familiar with the subject matter: 27 million containers a year is a lot more believable than 27 million ships in a year, but either one is still kind of hard to imagine.**\n",
    "\n",
    "The two major ports that aren't on the West Coast are interesting: Houston gets a lot more traffic from the Gulf side of Mexico, and from the Atlantic coast of South America, as well as being a pretty major import hub from Europe.  Newark, however, is of course *the* entrepot of Atlantic traffic, although they do also get a lot from South Asia and even East Asia.\n",
    "\n",
    "Some anomalies are interesting, however.  NYC receives a lot of cargo from a place called South Riding Point, Bahamas.  2,166 containers in 2017, to be exact.  There's no way that the Bahamas are a major exporter of just about anything on that scale, so this is probably more evidence of some kind of economic shenanigans wherein the place where goods are loaded for export isn't necessarily the same country where they're produced.  Other points: Africa and the Middle East aren't huge hotspots for exporting to the major US hubs, and, perhaps more surprisingly, neither is Australia.  This could be evidence that extremes of development in nations decrease the likelihood that it will be a big exporter to the US, but the amount of goods we get from Europe would seem to weaken such a conclusion.\n",
    "\n",
    "Then again, this is just one look at some of the biggest ports in the US, and visual analysis with a map, while intuitive and immediate, is less thorough, and the largest markers easily dominate the visual field when doing a quick survey.  The 6 cities shown above account for just over 400,000 rows of shipping records from 2017, out of a total 27 million rows in the entire dataset.  It would be interesting to create a heatmap of all lat-long origins of shipping bound to the US, either from the entire dataset (if my laptop can handle it), or just from these cities."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Average Lat-Lon for Ports of Lading\n",
    "\n",
    "First, though, let's just try something extremely simple: just find the 'center of mass' for each port of unlading:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 88,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "{'Houston, Texas': (29.76058000000006, -95.36967999999996), 'Long Beach, California': (33.76672000000008, -118.19239999999996), 'Los Angeles, California': (34.05349000000007, -118.24531999999999), 'Newark, New Jersey': (40.73197000000005, -74.17420999999996), 'Seattle, Washington': (47.60357000000005, -122.32944999999995), 'Tacoma, Washington': (47.255130000000065, -122.44163999999995)} \n",
      "\n",
      " {'Houston': 'Houston, Texas', 'Long_Beach': 'Long Beach, California', 'Los_Angeles': 'Los Angeles, California', 'NYC_Newark': 'Newark, New Jersey', 'Seattle': 'Seattle, Washington', 'Tacoma': 'Tacoma, Washington'}\n"
     ]
    }
   ],
   "source": [
    "# # Note: Already run; load from paths['top_6_unlading_pkl']\n",
    "\n",
    "# from geopy import geocoders\n",
    "\n",
    "# unlading_ports = ['Long Beach, California', 'Los Angeles, California', 'Tacoma, Washington',\n",
    "#                   'Seattle, Washington', 'Houston, Texas', 'Newark, New Jersey']\n",
    "# unlading_coords = dict()\n",
    "# failures = []\n",
    "# geolocator = geocoders.GoogleV3(api_key=google_api_key,\n",
    "#                                     timeout=100)\n",
    "# for port in unlading_ports:\n",
    "#         location = geolocator.geocode(port)\n",
    "#         if location:\n",
    "#             lat = location.latitude\n",
    "#             long = location.longitude\n",
    "#             unlading_coords[port] = (lat, long)\n",
    "#         else:\n",
    "#             failures.append(port)\n",
    "\n",
    "with open(paths['top_6_unlading_pkl'], 'rb') as f:\n",
    "    unlading_coords = pickle.load(f)\n",
    "    \n",
    "name_translator = dict()\n",
    "for (key, city) in zip(sorted(unlading_coords.keys()), sorted(cities_2017_dfs.keys())):\n",
    "    name_translator[city] = key\n",
    "print(unlading_coords, '\\n\\n', name_translator)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 91,
   "metadata": {
    "scrolled": false
   },
   "outputs": [],
   "source": [
    "def plotly_avg_coords(dict_of_dfs, title, scale=1E3, width=1000, height=600):\n",
    "    set_credentials_file(username=plotly_username, \n",
    "                               api_key=plotly_api_key)\n",
    "\n",
    "    colors = [\"rgb(0,116,217)\",\"rgb(255,65,54)\",\"rgb(133,20,75)\",\n",
    "              \"rgb(255,133,27)\",\"rgb(40,40,40)\",'rgb(100,200,50)']\n",
    "    data = []\n",
    "    scale = scale\n",
    "    log_scale = 3\n",
    "\n",
    "    layout = dict(\n",
    "        title = title,\n",
    "        font=dict(family='Arial, sans serif',\n",
    "                  size=18,\n",
    "                  color='rgb(0, 0, 0)'\n",
    "                ),\n",
    "        showlegend = True,\n",
    "        autosize = False,\n",
    "        width = width,\n",
    "        height = height,\n",
    "        hovermode = True,\n",
    "        legend = dict(\n",
    "            x=1,\n",
    "            y=0.5,\n",
    "            bgcolor=\"rgba(255, 255, 255, 0)\",\n",
    "            font = dict( size=11 ),\n",
    "        ),\n",
    "        geo = dict(\n",
    "                scope='world',\n",
    "                projection=dict( type='Mercator' ),\n",
    "                showland = True,\n",
    "                landcolor = 'rgb(217, 217, 217)',\n",
    "                subunitwidth=1,\n",
    "                countrywidth=1,\n",
    "                subunitcolor=\"rgb(255, 255, 255)\",\n",
    "                countrycolor=\"rgb(255, 255, 255)\"\n",
    "            )\n",
    "    )\n",
    "\n",
    "    # Iterate over dict of dfs\n",
    "    for i, (city, df) in enumerate(dict_of_dfs.items()):\n",
    "        city_read = '{}'.format(city.replace('_', ' '))\n",
    "        total_weights = df['containers'].sum()\n",
    "        lat_weight = df['lat'] * df['containers']\n",
    "        lat_avg = lat_weight.sum()/total_weights\n",
    "        lon_weight = df['long'] * df['containers']\n",
    "        lon_avg = lon_weight.sum()/total_weights\n",
    "        un_lat = unlading_coords[name_translator[city]][0]\n",
    "        un_lon = unlading_coords[name_translator[city]][1]\n",
    "        geo_key = 'geo'\n",
    "\n",
    "        # Go through cities and plot markers to 'center of mass'    \n",
    "        center_of_mass = dict(\n",
    "            type = 'scattergeo',\n",
    "            geo = geo_key,\n",
    "            locationmode = 'world',\n",
    "            lat = [lat_avg],\n",
    "            lon = [lon_avg],\n",
    "            text = '\"Center of mass\" of ports <br />\\\n",
    "    exporting to {}'.format(city_read),\n",
    "            showlegend = False,\n",
    "            marker = dict(\n",
    "                size = df['containers'].sum()/scale,\n",
    "    #             size = np.log(df['containers'].sum())*log_scale,\n",
    "                color = colors[i],\n",
    "                line = dict(width=0.5, color='rgb(40,40,40)'),\n",
    "                sizemode = 'area',\n",
    "                opacity = 1\n",
    "            ),\n",
    "            name = '{}'.format(city_read),\n",
    "            legendgroup = '{}'.format(city_read)\n",
    "        )\n",
    "        data.append(center_of_mass)\n",
    "\n",
    "        # Add a clear map to show port of unlading\n",
    "        un_port = dict(\n",
    "            type = 'scattergeo',\n",
    "            geo = geo_key,\n",
    "            locationmode = 'world',\n",
    "            lat = [un_lat],\n",
    "            lon = [un_lon],\n",
    "            text = '{}'.format(city_read),\n",
    "            marker = dict(\n",
    "                size = 10,\n",
    "                color = colors[i],\n",
    "                line = dict(width=0.5, color='rgb(40,40,40)'),\n",
    "                sizemode = 'area'\n",
    "            ),\n",
    "            name = '{}'.format(city_read),\n",
    "            showlegend = False\n",
    "#             legendgroup = '{}'.format(city_read)\n",
    "        )\n",
    "        data.append(un_port)\n",
    "\n",
    "        # One more: add lines connecting ports of unlading\n",
    "        # with their respective centers of mass\n",
    "        line = dict(\n",
    "            geo = 'geo',\n",
    "            type = 'scattergeo',\n",
    "            locationmode = 'world',\n",
    "            lon = [un_lon, lon_avg],\n",
    "            lat = [un_lat, lat_avg],\n",
    "            mode = 'lines',\n",
    "            name = city_read,\n",
    "            showlegend = True,\n",
    "            legendgroup = '{}'.format(city_read),\n",
    "            line = dict(\n",
    "                width = 1,\n",
    "                color = colors[i],\n",
    "            )\n",
    "        )\n",
    "        data.append(line)\n",
    "        \n",
    "    return data, layout"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 92,
   "metadata": {
    "scrolled": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<iframe id=\"igraph\" scrolling=\"no\" style=\"border:none;\" seamless=\"seamless\" src=\"https://plot.ly/~mackellardrew/38.embed\" height=\"600px\" width=\"1000px\"></iframe>"
      ],
      "text/plain": [
       "<plotly.tools.PlotlyDisplay object>"
      ]
     },
     "execution_count": 92,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "title = '<b>Average Lat-Lon of Ports of Lading <br />\\\n",
    "Presented by Ports of Unlading: 2017</b>'\n",
    "\n",
    "data, layout = plotly_avg_coords(city_counts_dfs, title)\n",
    "        \n",
    "fig = { 'data':data, 'layout':layout }\n",
    "paths['avg_plotly_2017_html'] = os.path.join(paths['data_dir'], 'avg_plotly_2017.html')\n",
    "offline.plot(fig, filename=paths['avg_plotly_2017_html'])\n",
    "py.iplot( fig, filename='CofM Ports of Lading', height=600, width=1200 )"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Ok, that took longer than I intended to format a new map, but it looks like a useful summary of the places from which different cities receive imports.  Tacoma, Long Beach, and LA all pretty solidly receive East Asian imports, overwhelming everything else.  But Seattle and Houston have as much stuff coming from East and West that the average longitude of a container coming in is pretty close to zero (Greenwich).  Just to see how stable this pattern is, let's compare to the earliest year for which Enigma has these data: 2014.\n",
    "\n",
    "When I went back to the relevant page, the difference is striking; while there are far fewer records for that year (the whole db is 11 million, as opposed to the ~28 million rows for the 2017 database), the top 6 ports of unlading account for a much larger proportion of overall traffic:\n",
    "\n",
    "    Frequency Rank for Port of Unlading in 2014:\n",
    "    Los Angeles -  2,025,070\n",
    "    Long Beach  -  1,670,075\n",
    "    NYC/Newark  -  1,178,130\n",
    "    Tacoma      -    908,218\n",
    "    Houston     -    676,386\n",
    "    Total rows  - 11,025,607\n",
    "    \n",
    "That means that Los Angeles handled 5% of the cargo in 2017 compared to what it received in 2014?  That doesn't seem right.  [This page](https://www.portoflosangeles.org/maritime/stats.asp) of annual traffic to the Port of Los Angeles contradicts Enigma's data, showing a gradual but consistent *increase* in containers (twenty-foot-equivalent units) handled by that port over the last decade, and indeed going back to the 80s.  Their monthly breakdown shows over 4 million inbound loaded TEUs for the last few years.  [Long Beach's site](http://www.polb.com/economics/stats/yearly_teus.asp) similarly lists pretty consistent volumes, between 3 and 4 million every year.  Obviously Enigma's data, while extensive, aren't comprehensive or authoritative.  Let's check the rest of the years for Enigma's data sets:\n",
    "\n",
    "    Frequency Rank for Port of Unlading in 2015:\n",
    "    Los Angeles -    291,567\n",
    "    Long Beach  -    237,690\n",
    "    NYC/Newark  -    211,192\n",
    "    Seattle     -    149,245\n",
    "    Tacoma      -    149,245\n",
    "    Total rows  -  1,914,262\n",
    "    \n",
    "    Frequency Rank for Port of Unlading in 2016:\n",
    "    Los Angeles -  5,024,788\n",
    "    NYC/Newark  -  4,468,536\n",
    "    Long Beach  -  3,979,732\n",
    "    Tacoma      -  2,272,418\n",
    "    Savannah    -  1,827,115\n",
    "    Total rows  - 29,209,522\n",
    "    \n",
    "Obviously, the datasets are highly variable.  The top ports of unlading across the years are pretty consistent, but the total data present are highly variable, and the number listed for each US port similarly fluctuate.  For a less biased look, [the Census Bureau](https://www.census.gov/foreign-trade/index.html) claims to be the official source of statistics on imports and exports in the US.  The [International Trade Administration](https://www.trade.gov/mas/ian/tradestatistics/index.asp) also keeps some data.  Their [latest summary](https://www.trade.gov/mas/ian/build/groups/public/@tg_ian/documents/webcontent/tg_ian_005537.pdf), for 2016, lists total imports for the US at 2.7 trillion USD that year.\n",
    "\n",
    "BTW, while looking into getting more complete access to summary statistics for Enigma's datasets, I came across [this page](https://docs.enigma.com/public/public_v20_user_python.html), which looks like the most accessible way to get programmatic access to the data."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Seattle Imports Across the Years\n",
    "\n",
    "The respective number of rows matching Seattle as the port of unlading for each year are:\n",
    "\n",
    "    2014 - 492,215\n",
    "    2015 - 149,267\n",
    "    2016 - 923,437\n",
    "    2017 -  27,100\n",
    "    \n",
    "Their csvs, once downloaded, occupy [302, 95, 523, 15] MBs, respectively, on the local disk."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 93,
   "metadata": {
    "scrolled": false
   },
   "outputs": [],
   "source": [
    "seattle_csvs = []\n",
    "for i in range(4, 8):\n",
    "    seattle_csvs.append('seattle_shipping_201{}_csv'.format(i))\n",
    "    \n",
    "seattle_lading_years = {}\n",
    "for file in seattle_csvs:\n",
    "    year = file.split(sep='_')[2]\n",
    "    seattle_lading_years[year] = pd.read_csv(paths[file], \n",
    "                                             usecols=['foreign_port_of_lading'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 94,
   "metadata": {
    "scrolled": true
   },
   "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>foreign_port_of_lading</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Shanghai ,China (Mainland)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Tanjung Pelepas,Malaysia</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Shanghai ,China (Mainland)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Shanghai ,China (Mainland)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Pusan,South Korea</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       foreign_port_of_lading\n",
       "0  Shanghai ,China (Mainland)\n",
       "1    Tanjung Pelepas,Malaysia\n",
       "2  Shanghai ,China (Mainland)\n",
       "3  Shanghai ,China (Mainland)\n",
       "4           Pusan,South Korea"
      ]
     },
     "execution_count": 94,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "seattle_lading_years['2014'].head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "First, some preliminary cleaning, before I can grab coords for each city.  Need to drop any NaN, and identify any 5-digit codes in place of city names."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 95,
   "metadata": {
    "scrolled": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "foreign_port_of_lading    1492\n",
      "dtype: int64\n",
      "foreign_port_of_lading    0\n",
      "dtype: int64\n",
      "foreign_port_of_lading    0\n",
      "dtype: int64\n",
      "foreign_port_of_lading    0\n",
      "dtype: int64\n"
     ]
    }
   ],
   "source": [
    "for year, series in seattle_lading_years.items():\n",
    "    print(series.isnull().sum())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 96,
   "metadata": {},
   "outputs": [],
   "source": [
    "seattle_lading_years['2014'].dropna(inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 97,
   "metadata": {},
   "outputs": [],
   "source": [
    "code_matches = code_lookup(seattle_lading_years)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 98,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "{'57045', '53304', '99999', '57073', '55225', '35180', '35136', '57037', '57019'}\n"
     ]
    }
   ],
   "source": [
    "print(code_matches)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 99,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "57045\n",
      "99999\n",
      "55225\n",
      "35180\n",
      "35136\n",
      "57019\n"
     ]
    }
   ],
   "source": [
    "origin_codes_dict = {'57073': 'Nansha, China (Mainland)', \n",
    "                     '53304': 'Pipavav, India', \n",
    "                     '30110': 'Covenas, Columbia', \n",
    "                     '57037': 'Yangshan, China (Mainland)'\n",
    "                    }\n",
    "for code in code_matches:\n",
    "    if code not in origin_codes_dict.keys():\n",
    "        print(code)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "That yielded just one new hit, when comparing to [these](https://www.cbp.gov/sites/default/files/assets/documents/2017-Feb/appendix_f_0.pdf) [two](http://customscodes.com/foreign_port_code.php) resources:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 103,
   "metadata": {},
   "outputs": [],
   "source": [
    "origin_codes_dict[57019] = 'Zhenjiang, China'"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Let's try both of the graph types above: I'll start with the averaged lat/lon approach.  First I need to clean up the port names and retrieve lat/lon for them, however:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 122,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2014        \n",
      "Coordinates couldn't be found for  1317 ports of lading; 0.27% of all records\n",
      "2015        \n",
      "Coordinates couldn't be found for   903 ports of lading; 0.61% of all records\n",
      "2016        \n",
      "Coordinates couldn't be found for  1615 ports of lading; 0.17% of all records\n",
      "2017        \n",
      "Coordinates couldn't be found for     1 ports of lading; 0.00% of all records\n",
      "CPU times: user 10.4 s, sys: 609 ms, total: 11 s\n",
      "Wall time: 11.9 s\n"
     ]
    }
   ],
   "source": [
    "%%time\n",
    "\n",
    "cities_2017_dfs = import_shipping_data(cities_2017_paths)\n",
    "seattle_lading_coded = replace_city_codes(seattle_lading_years, origin_codes_dict)\n",
    "seattle_lading_formatted = format_ports(seattle_lading_coded)\n",
    "\n",
    "lat_long, failures = geolocate(seattle_lading_formatted)\n",
    "\n",
    "seattle_lading_coords = get_coords(seattle_lading_formatted, lat_long)\n",
    "seattle_lading_counts = get_counts(seattle_lading_coords)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 123,
   "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>city</th>\n",
       "      <th>lat_long</th>\n",
       "      <th>lat</th>\n",
       "      <th>long</th>\n",
       "      <th>containers</th>\n",
       "      <th>text</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Hong Kong, Hong Kong</td>\n",
       "      <td>(22.396428, 114.109497)</td>\n",
       "      <td>22.396428</td>\n",
       "      <td>114.109497</td>\n",
       "      <td>940</td>\n",
       "      <td>Hong Kong, Hong Kong: 940 container(s)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Tsingtao, China</td>\n",
       "      <td>(36.067108, 120.382609)</td>\n",
       "      <td>36.067108</td>\n",
       "      <td>120.382609</td>\n",
       "      <td>23</td>\n",
       "      <td>Tsingtao, China: 23 container(s)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Pusan, South Korea</td>\n",
       "      <td>(35.1795543, 129.0756416)</td>\n",
       "      <td>35.179554</td>\n",
       "      <td>129.075642</td>\n",
       "      <td>3240</td>\n",
       "      <td>Pusan, South Korea: 3240 container(s)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>Yantian, China</td>\n",
       "      <td>(22.556499, 114.236875)</td>\n",
       "      <td>22.556499</td>\n",
       "      <td>114.236875</td>\n",
       "      <td>999</td>\n",
       "      <td>Yantian, China: 999 container(s)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>Shanghai, China</td>\n",
       "      <td>(31.2303904, 121.4737021)</td>\n",
       "      <td>31.230390</td>\n",
       "      <td>121.473702</td>\n",
       "      <td>2936</td>\n",
       "      <td>Shanghai, China: 2936 container(s)</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                    city                   lat_long        lat        long  \\\n",
       "0   Hong Kong, Hong Kong    (22.396428, 114.109497)  22.396428  114.109497   \n",
       "3        Tsingtao, China    (36.067108, 120.382609)  36.067108  120.382609   \n",
       "4     Pusan, South Korea  (35.1795543, 129.0756416)  35.179554  129.075642   \n",
       "5         Yantian, China    (22.556499, 114.236875)  22.556499  114.236875   \n",
       "11       Shanghai, China  (31.2303904, 121.4737021)  31.230390  121.473702   \n",
       "\n",
       "    containers                                    text  \n",
       "0          940  Hong Kong, Hong Kong: 940 container(s)  \n",
       "3           23        Tsingtao, China: 23 container(s)  \n",
       "4         3240   Pusan, South Korea: 3240 container(s)  \n",
       "5          999        Yantian, China: 999 container(s)  \n",
       "11        2936      Shanghai, China: 2936 container(s)  "
      ]
     },
     "execution_count": 123,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "seattle_lading_counts['2017'].head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 124,
   "metadata": {
    "scrolled": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "['High Seas, United States Off Shore Tanker Transshipment Points', 'All Other Southern Asia, N E C Ports, Southern Asia, N E C', 'All Other Oman Ports, Oman', 'Pursan, Turkey', 'Nagoya Ko, Japan', 'Port Redon, Vietnam', 'Puduchcheri, India']\n"
     ]
    }
   ],
   "source": [
    "print(failures)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Ok, that looks good.  I'll save the counts, and the dict."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 125,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "246 245\n",
      "246\n"
     ]
    }
   ],
   "source": [
    "with open(paths['lat_long_dict_pkl'], 'rb') as f:\n",
    "    old_dict = pickle.load(f)\n",
    "    \n",
    "print(len(old_dict.keys()), len(lat_long.keys()))\n",
    "new_dict = old_dict\n",
    "for city, coords in lat_long.items():\n",
    "    if city not in old_dict.keys():\n",
    "        new_dict[city] = coords\n",
    "        \n",
    "print(len(new_dict))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 126,
   "metadata": {},
   "outputs": [],
   "source": [
    "paths['seattle_annual_pkl'] = os.path.join(paths['data_dir'], 'seattle_annual.pkl')\n",
    "\n",
    "with open(paths['lat_long_dict_pkl'], 'wb') as f:\n",
    "    pickle.dump(new_dict, f)\n",
    "with open(paths['seattle_annual_pkl'], 'wb') as f:\n",
    "    pickle.dump(seattle_lading_counts, f)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 127,
   "metadata": {},
   "outputs": [],
   "source": [
    "with open(paths['seattle_annual_pkl'], 'rb') as f:\n",
    "    seattle_lading_counts = pickle.load(f)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now, to check the plots for multiple years of Seattle bills of lading."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 128,
   "metadata": {},
   "outputs": [],
   "source": [
    "for year in seattle_lading_counts.keys():\n",
    "    name_translator[year] = 'Seattle, Washington'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 129,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<iframe id=\"igraph\" scrolling=\"no\" style=\"border:none;\" seamless=\"seamless\" src=\"https://plot.ly/~mackellardrew/38.embed\" height=\"600px\" width=\"1000px\"></iframe>"
      ],
      "text/plain": [
       "<plotly.tools.PlotlyDisplay object>"
      ]
     },
     "execution_count": 129,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "title = '<b>Average Lat-Lon of Ports of Lading <br />\\\n",
    "Bound for Seattle: 2014-2017</b>'\n",
    "\n",
    "data, layout = plotly_avg_coords(seattle_lading_counts, title, scale=5E3)\n",
    "        \n",
    "fig = { 'data':data, 'layout':layout }\n",
    "paths['avg_plotly_seattle_html'] = os.path.join(paths['data_dir'], 'avg_plotly_seattle.html')\n",
    "offline.plot(fig, filename=paths['avg_plotly_seattle_html'])\n",
    "py.iplot( fig, filename='CofM Ports of Lading', height=600, width=1200 )"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Ok, so, for Seattle at least, the average number of containers coming from ports didn't shift very far East or West for the years 2014-2016.  The data are highly variable for those years; many more records from 2016 than 2014 (and note that I had to change the scale to make the markers more visible, compared to the 2017 data mapped earlier).  2017 has far fewer records for shipments to Seattle, and therefore it's less surprising that the geographic center of their distribution also shifted.  This almost certainly represents bias in sampling in the data, rather than an actual shift in the number and origin points of cargo received; as indicated above, Enigma's data are highly variable for other ports, and in ways that don't line up with the largest ports' own records.\n",
    "\n",
    "I found [some historical data](https://www.portseattle.org/About/Publications/Statistics/Seaport/Pages/10-Year-History.aspx) for the Port of Seattle, and there is indeed significant variety over the years indicated, but container imports between 500K and 1 million are actually believable.  For some reason the data on that page don't cover more recent years; the [most recent complete report](https://www.nwseaportalliance.com/sites/default/files/nwsa_annualreport_2016_full_0.pdf) I could find lists 3.6 million total TEUs, but doesn't break them down by import full/empty vs export full/empty.\n",
    "\n",
    "For the hell of it, let's look at the city-by-city breakdown of ports of lading for Seattle by year, too:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 131,
   "metadata": {
    "scrolled": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<iframe id=\"igraph\" scrolling=\"no\" style=\"border:none;\" seamless=\"seamless\" src=\"https://plot.ly/~mackellardrew/32.embed\" height=\"900px\" width=\"1000px\"></iframe>"
      ],
      "text/plain": [
       "<plotly.tools.PlotlyDisplay object>"
      ]
     },
     "execution_count": 131,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "title = '<b>Ports of Lading for Containers Received in Seattle, <br />\\\n",
    "Presented by Year: 2014-2017</b>'\n",
    "\n",
    "data, layout = plotly_subplots(seattle_lading_counts, scale=50, title=title, rows=2)\n",
    "\n",
    "fig = { 'data':data, 'layout':layout }\n",
    "paths['plotly_subplots_seattle_html'] = os.path.join(paths['data_dir'], 'plotly_subplots_seattle.html')\n",
    "offline.plot(fig, filename=paths['plotly_subplots_seattle_html'])\n",
    "py.iplot( fig, filename='Ports of Lading', height=900, width=1000 )"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Ok, not too surprising; the breakdown looks pretty much the same for most years; mostly East Asia, but also good representation of the E.U. and a smattering of Latin America.  Importantly, the phenomenon of a ton of cargo coming via British Columbia has existed for all of those years.\n",
    "\n",
    "I'd say that's enough for enumerating ports of lading for containers.  Let's move on."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Part 2: Checking Weights\n",
    "\n",
    "Visualizing the number of records associated with different cities shipping to Seattle is cool, but the quantity and quality of goods might not be well represented just by number of containers.\n",
    "\n",
    "A more nuanced look would involve checking the total volume of goods coming from these places, in which case you'd probably want raw tonnage, which might be extractable from the data set."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 114,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Missing data in harmonized_weight_unit\n",
      "  Long_Beach: 101,948 missing fields in 118,493 rows (86.0%).\n",
      " Los_Angeles:  92,615 missing fields in 105,329 rows (87.9%).\n",
      "  NYC_Newark:  73,543 missing fields in  94,620 rows (77.7%).\n",
      "     Houston:  29,955 missing fields in  39,555 rows (75.7%).\n",
      "      Tacoma:  30,343 missing fields in  37,106 rows (81.8%).\n",
      "     Seattle:  19,174 missing fields in  27,100 rows (70.8%).\n",
      "\n",
      "Missing data in harmonized_value\n",
      "  Long_Beach:  90,674 missing fields in 118,493 rows (76.5%).\n",
      " Los_Angeles:  69,553 missing fields in 105,329 rows (66.0%).\n",
      "  NYC_Newark:  65,123 missing fields in  94,620 rows (68.8%).\n",
      "     Houston:  22,015 missing fields in  39,555 rows (55.7%).\n",
      "      Tacoma:  14,121 missing fields in  37,106 rows (38.1%).\n",
      "     Seattle:  10,664 missing fields in  27,100 rows (39.4%).\n",
      "\n",
      "Missing data in description_text\n",
      "  Long_Beach:       0 missing fields in 118,493 rows ( 0.0%).\n",
      " Los_Angeles:       0 missing fields in 105,329 rows ( 0.0%).\n",
      "  NYC_Newark:       0 missing fields in  94,620 rows ( 0.0%).\n",
      "     Houston:       0 missing fields in  39,555 rows ( 0.0%).\n",
      "      Tacoma:       0 missing fields in  37,106 rows ( 0.0%).\n",
      "     Seattle:       0 missing fields in  27,100 rows ( 0.0%).\n"
     ]
    }
   ],
   "source": [
    "print('Missing data in harmonized_weight_unit')\n",
    "for city, df in cities_2017_dfs.items():\n",
    "    missing_rows = df['harmonized_weight_unit'].isnull().sum()\n",
    "    total_rows = df.shape[0]\n",
    "    print('{:>12}: {:>7,} missing fields in {:>7,} rows ({:>4}%).'.format(\n",
    "        city, missing_rows, total_rows, np.round(missing_rows/total_rows*100, 1)))\n",
    "    \n",
    "print('\\nMissing data in harmonized_value')\n",
    "for city, df in cities_2017_dfs.items():\n",
    "    missing_rows = df['harmonized_weight'].isnull().sum()\n",
    "    total_rows = df.shape[0]\n",
    "    print('{:>12}: {:>7,} missing fields in {:>7,} rows ({:>4}%).'.format(\n",
    "        city, missing_rows, total_rows, np.round(missing_rows/total_rows*100, 1)))\n",
    "\n",
    "print('\\nMissing data in description_text')\n",
    "for city, df in cities_2017_dfs.items():\n",
    "    missing_rows = df['description_text'].isnull().sum()\n",
    "    total_rows = df.shape[0]\n",
    "    print('{:>12}: {:>7,} missing fields in {:>7,} rows ({:>4}%).'.format(\n",
    "        city, missing_rows, total_rows, np.round(missing_rows/total_rows*100, 1)))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Or they might not.  There are a lot of missing data for weight of containers; many others have a weight of zero listed.  Obviously the data entry is not standardized and strictly enforced.  But some of the extended text descriptions include some accounting of weight:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 115,
   "metadata": {
    "scrolled": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(0, 'SOFA & UPHOLSTERY PO418287/418285/418967 \"SH \"') \n",
      "\n",
      "(1000, 'WELDING WIRE') \n",
      "\n",
      "(2000, '55  CTN GDSM SOFA___TEL_ 1-650-685 8808 FAX_ 1-650-685 8898 S/C_US0002D2X CY TO CY 1X40_HQFCL') \n",
      "\n",
      "(3000, \"LADIES' 100% LINEN KNITTED  CA RDIGAN\") \n",
      "\n",
      "(4000, \"CHEMICAL WOOD PULP, SODA OR SULPHATE, OTHER T HAN D - 'THUNDER BAY' FULLY BLEACHED HARDWOOD KRAFT PULP\") \n",
      "\n",
      "(5000, 'ROLLS') \n",
      "\n",
      "(6000, 'TABLEWARE AND KITCHENWARE OF PLASTICS PLASTIC KITCHEN WARE 669317 GROUP PN3 +1 817-442-818 7') \n",
      "\n",
      "(7000, 'PLASTIC SWITCHS') \n",
      "\n",
      "(8000, 'ORDER  0085910 ORIENTED STRAND BOARD SUMMARY ID  SUM2092 FREIGHT PREPAID HARMONIZATION COD ES  4410.12.00 NET UNIT  196.458 MSFT') \n",
      "\n",
      "(9000, 'CALCIUM STEARATE   HS CODE : 3 812.30   8255556   UDAB   SEA WAYBILL   FREIGHT COLLECT') \n",
      "\n",
      "(10000, 'DRIED LEGUMINOUS VEGETABLES, SHELLED, WHETHER OR N - X 100 LB BAGS CROP YEAR 2017: US CHIC KPEAS 2 X20 FCLCROP YEAR 2017: US CHICKPEAS 1 090 X 100 LB BAGS HS CODE: 0713202000 GROSS P OUNDS: 109,218.00 GROSS METRIC TON: 49.540 GR OSS KG: 49,540.00 NET POUNDS: 109,000.00 NET METRIC TON: 49.442 NET KG: 49,442.00 FREIGHTPREPAID DTHC PREPAID 14 FREE DEMURRAGE DAYS A T DESTINATION CAED: 01P233TCE45920171206801 A GENT AT DESTINATION DETAILS: MSC LANKA (PRIVA TE) LTD. 7TH FLOOR 193, DR DANISTER SILVA MAW ATHA COLOMBO 08 SRI LANKA PHONE+94 11 452 900 0 EMAILGENERAL=MSCLANKA.LK FAX+94 11 474 1771') \n",
      "\n",
      "(11000, 'WOOD SAWN OR CHIPPED LENGTHWISE, SLICED OR PE ELED, - DOUGLAS FIR KILN DRIED H.T. LUMBER (P SEUDOTSUGA MENZIESII) HEMLOCK KILN DRIED H.T. LUMBER (TSUGA HETEROPHYLLA) 45.8281 M3 19,42 1 FBM') \n",
      "\n",
      "(12000, 'HS CODE  020220 FROZEN BEEF CHUCK BONE IN CHU CK SHORT RIB   RIB BONE IN 123A SHORT RIB NET WT 50470.53 LBS NET WT 22893.28 KGS GRS WT 5 3375.09 LBS FREIGHT PREPAID MAINTAIN -18 DEG. CELSIUS   VENTS CLOSED CAED  01R054MC6323201 71202163') \n",
      "\n",
      "(13000, 'CHEMICAL NON-HAZ (N-(PHOSPHONOMETHYL)IMINODIA CETIC ACID(PMIDA),98 MIN) S C  RIC5081103 SCA C CODE OERT GROUP A1 NAC IDX THIS SHIPMENT CO NTAINS WOOD PACKAGING MATERIAL WHICH HAS BEEN PROPERLY TREATED') \n",
      "\n",
      "(14000, '30  PKG PRAIRIE MALT 2-ROW BARLEY MALT LOT_36 HSCODE - 1107.10.0000 CAED_ _ 02U356SC433920171200121') \n",
      "\n",
      "(15000, '6,300 CTNS = 9X1000ML INDO NESIAN COCONUT WAT ER \"FREI GHT PAYABLE/PREPAID BY ALL MARKET SI NGAPORE\"') \n",
      "\n",
      "(16000, 'GROSS WEIGHT PER LOT  19,129.00 PERMISO DE EMBARQUE ANACAFE  876ALDABRA REF  17082 SERVICE CONTRACT  S16ECE017 09.01.11 FCL/FCL. SHIPPER FDA  11616749222 FREIGHT PAYABLE AT BASEL, SWITERLAND BY COMMODITY SUPPLIES AG NET WEIGHT PER LOT  18,975.00GREEN COFFEE OF 69 KGS EACH ONE. GUATEMALA HARD BEAN EUROPEAN PREPARATION ARABICA GREEN COFFEE BEANS. CROP 2017/2018, BUYER CONTRACT  P29522') \n",
      "\n",
      "(17000, 'NUTS FH-171008 . . .') \n",
      "\n",
      "(18000, 'FURNITURE-UPHOLSTERY:UPHOLSTER Y   FREIGHT COLLECT   -222000 CHINA') \n",
      "\n",
      "(19000, 'BREADSTICKS,COOKIES,CORN FLOUR') \n",
      "\n",
      "(20000, 'OLIVE OIL EXTRA VIRGIN OLIVE OIL') \n",
      "\n",
      "(21000, 'ONE EMPTY CONTAINER CLEARED AGAINST CUSTOMS') \n",
      "\n",
      "(22000, 'HS CODE  071080 DAE  028-2017-40-00803547 \"FREIGHT AS PER AGREEMENT\"FROZEN BROCCOLI 40 BOXES BROCCOLI IQF NS3-2025-6 LOT072970 INVOICE  001-011-000033128 KN 17.200 KG; PA 0710802000 RYAN  76678823') \n",
      "\n",
      "(23000, '184 CTNS.MENS T-SHIRTS  67% POLYESTER-33% COTTON 629 CTNS. WOMENS T-SHIRTS  67% POLYESTER-33% COTTON 143 CTNS.MENS POLO SHIRTS  94% POLYESTER-6% LYCRAELASTA 86 CTNS.  WOMENS PULLOVERS  100%POLYESTER') \n",
      "\n",
      "(24000, 'PO NO. 209052-7 FG NO. 21-064 801 21-064 801A GREAT VAL 64CT DRY FLOOR WIPE PO NO. 209855-5 FG NO. 21-016 442A HEB 16CT DRY FLOOR WIPE FG NO. 21-016 705BGOOD & CLEAN 16CT DRY FLOOR WIPE FG NO. 21-616 465 HANNAFORD 16CT DRY FLOOR WIPE FG NO. 21-016L151 PUBLIX 16CT DRY FLOOR WIPE FG NO. 21-016 801A GREAT VAL 16CT DRY FLOOR WIPE') \n",
      "\n",
      "(25000, 'AUTO PARTS NON HAZ AUTO PARTS HS-CODE(S) 870829 CPO  375092 MOPAR BILL TO  03190 ORIGIN CODE  03173 ENTRY PORT CODE  58201DEALER CODE  33668') \n",
      "\n",
      "(26000, 'PU NYLON RAINWEARPVC COTTON PANTPU POLYESTER RAINWEAR') \n",
      "\n",
      "(27000, 'WOOD SAWN OR CHIPPED LENGTHWISE, SLICED OR PE ELED, - WOOD, KILN DRIED, PRE-CUT') \n",
      "\n"
     ]
    }
   ],
   "source": [
    "for desc in cities_2017_dfs['Seattle'].loc[::1000, 'description_text'].iteritems():\n",
    "    print(desc, '\\n')\n",
    "\n",
    "# sea2017_df.loc[::10, 'description_text']"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "I count about 5 rows with weight info among those 28 that I sampled.  They're pretty variable, but sense could be made of them.  I wonder whether the rows that lack values in the dedicated weight column are more likely to contain weight information in the text description.\n",
    "\n",
    "But identifying the weight data from such a nonstandard format is tricky.  I might just need to pass a series of key terms like 'WT' and 'WEIGHT' with OR operators in between to a re pattern, or I may want to try sending the strings through some NLTK function."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 116,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "((10664, 31), (8757, 31))"
      ]
     },
     "execution_count": 116,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import re\n",
    "\n",
    "prefixes = ['NET', 'GROSS', 'METRIC']\n",
    "triggers = ['WT', 'WEIGHT']\n",
    "units = ['POUNDS', 'LBS', 'TONS', 'KG', 'KGS']\n",
    "strings = [*prefixes, *triggers, *units]\n",
    "\n",
    "weight_words = ['NET', 'GROSS', 'METRIC', 'WT', 'WEIGHT', 'POUNDS',\n",
    "               'POUND', 'LBS', 'LB', 'TONS', 'TONNES', 'TON', 'KG', 'KGS']\n",
    "\n",
    "weight_words_string = '|'.join(x for x in weight_words)\n",
    "# pattern = '('+weight_words_string+'+[:?]\\s{1,2}\\d+)'\n",
    "# pattern = '(('+weight_words_string+')[:?]\\s{1,2}[\\d.,]+\\s{1,2}('+weight_words_string+'))'\n",
    "pattern = '('+weight_words_string+')'\n",
    "pattern = re.compile(pattern)\n",
    "pattern2 = re.compile('\\w+\\s\\d+')\n",
    "\n",
    "results = {}\n",
    "df = cities_2017_dfs['Seattle']\n",
    "df2 = df[df['harmonized_weight'].isnull()]# | df['harmonized_weight']==0]\n",
    "df3 = df[df['harmonized_weight'] == 0]\n",
    "df4 = pd.concat([df2, df3], axis=0)\n",
    "df5 = df4[~df4.index.duplicated(keep='first')]\n",
    "weight_matches = df5[df5['description_text'].str.match(pattern2)]\n",
    "# for i, v in df2.iteritems():\n",
    "#     if re.search(pattern, v):\n",
    "#         matches = re.search(pattern, v).groups()#[0]\n",
    "#         results[i] = matches\n",
    "# weight_matches = pd.Series(data=results)\n",
    "df2.shape, df3.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 117,
   "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>identifier</th>\n",
       "      <th>trade_update_date</th>\n",
       "      <th>run_date</th>\n",
       "      <th>vessel_name</th>\n",
       "      <th>port_of_unlading</th>\n",
       "      <th>estimated_arrival_date</th>\n",
       "      <th>foreign_port_of_lading</th>\n",
       "      <th>record_status_indicator</th>\n",
       "      <th>place_of_receipt</th>\n",
       "      <th>port_of_destination</th>\n",
       "      <th>...</th>\n",
       "      <th>shipper_comm_number_qualifier</th>\n",
       "      <th>shipper_comm_number</th>\n",
       "      <th>container_number</th>\n",
       "      <th>description_sequence_number</th>\n",
       "      <th>piece_count</th>\n",
       "      <th>description_text</th>\n",
       "      <th>harmonized_number</th>\n",
       "      <th>harmonized_value</th>\n",
       "      <th>harmonized_weight</th>\n",
       "      <th>harmonized_weight_unit</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>201712262236</td>\n",
       "      <td>2017-11-28T00:00:00</td>\n",
       "      <td>2017-12-26T00:00:00</td>\n",
       "      <td>NEW YORK EXPRESS</td>\n",
       "      <td>Seattle, Washington</td>\n",
       "      <td>2017-12-21T00:00:00</td>\n",
       "      <td>Yantian,China (Mainland)</td>\n",
       "      <td>New</td>\n",
       "      <td>HAIPHONG</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>SEGU5919545</td>\n",
       "      <td>1</td>\n",
       "      <td>1243</td>\n",
       "      <td>APPAREL, MEN S WOVEN SHIRT, LADIES STRIPES SH</td>\n",
       "      <td>620520.0</td>\n",
       "      <td>241660.0</td>\n",
       "      <td>12083.0</td>\n",
       "      <td>Kilograms</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29</th>\n",
       "      <td>201712263368</td>\n",
       "      <td>2017-12-01T00:00:00</td>\n",
       "      <td>2017-12-26T00:00:00</td>\n",
       "      <td>NEW YORK EXPRESS</td>\n",
       "      <td>Seattle, Washington</td>\n",
       "      <td>2017-12-21T00:00:00</td>\n",
       "      <td>Pusan,South Korea</td>\n",
       "      <td>New</td>\n",
       "      <td>BUSAN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>FCIU2664040</td>\n",
       "      <td>1</td>\n",
       "      <td>19</td>\n",
       "      <td>EDTA-ACID</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0</td>\n",
       "      <td>19057.0</td>\n",
       "      <td>Kilograms</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>30</th>\n",
       "      <td>201712263445</td>\n",
       "      <td>2017-12-01T00:00:00</td>\n",
       "      <td>2017-12-26T00:00:00</td>\n",
       "      <td>NEW YORK EXPRESS</td>\n",
       "      <td>Seattle, Washington</td>\n",
       "      <td>2017-12-21T00:00:00</td>\n",
       "      <td>57037</td>\n",
       "      <td>New</td>\n",
       "      <td>SHANGHAI, SHANGHA</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NYKU0728968</td>\n",
       "      <td>1</td>\n",
       "      <td>640</td>\n",
       "      <td>OFFICE CHAIR</td>\n",
       "      <td>940130.0</td>\n",
       "      <td>215008.0</td>\n",
       "      <td>10750.0</td>\n",
       "      <td>Kilograms</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>31</th>\n",
       "      <td>201712263500</td>\n",
       "      <td>2017-12-01T00:00:00</td>\n",
       "      <td>2017-12-26T00:00:00</td>\n",
       "      <td>NEW YORK EXPRESS</td>\n",
       "      <td>Seattle, Washington</td>\n",
       "      <td>2017-12-21T00:00:00</td>\n",
       "      <td>57037</td>\n",
       "      <td>New</td>\n",
       "      <td>SHANGHAI, SHANGHA</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>TCLU6640359</td>\n",
       "      <td>1</td>\n",
       "      <td>54</td>\n",
       "      <td>NAILHEADS HTS CODE 940161 PO 12030814562 IF A</td>\n",
       "      <td>940161.0</td>\n",
       "      <td>57708.0</td>\n",
       "      <td>2885.0</td>\n",
       "      <td>Kilograms</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>32</th>\n",
       "      <td>201712263681</td>\n",
       "      <td>2017-12-15T00:00:00</td>\n",
       "      <td>2017-12-26T00:00:00</td>\n",
       "      <td>NEW YORK EXPRESS</td>\n",
       "      <td>Seattle, Washington</td>\n",
       "      <td>2017-12-21T00:00:00</td>\n",
       "      <td>Pusan,South Korea</td>\n",
       "      <td>New</td>\n",
       "      <td>TIANJIN XINGANG,</td>\n",
       "      <td>3512</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>HLXU3272262</td>\n",
       "      <td>1</td>\n",
       "      <td>19</td>\n",
       "      <td>EDTA-ACID S/C S17ASC112 MR CODE BLUBRE1 XFAX +...</td>\n",
       "      <td>292249.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 31 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "      identifier    trade_update_date             run_date       vessel_name  \\\n",
       "13  201712262236  2017-11-28T00:00:00  2017-12-26T00:00:00  NEW YORK EXPRESS   \n",
       "29  201712263368  2017-12-01T00:00:00  2017-12-26T00:00:00  NEW YORK EXPRESS   \n",
       "30  201712263445  2017-12-01T00:00:00  2017-12-26T00:00:00  NEW YORK EXPRESS   \n",
       "31  201712263500  2017-12-01T00:00:00  2017-12-26T00:00:00  NEW YORK EXPRESS   \n",
       "32  201712263681  2017-12-15T00:00:00  2017-12-26T00:00:00  NEW YORK EXPRESS   \n",
       "\n",
       "       port_of_unlading estimated_arrival_date    foreign_port_of_lading  \\\n",
       "13  Seattle, Washington    2017-12-21T00:00:00  Yantian,China (Mainland)   \n",
       "29  Seattle, Washington    2017-12-21T00:00:00         Pusan,South Korea   \n",
       "30  Seattle, Washington    2017-12-21T00:00:00                     57037   \n",
       "31  Seattle, Washington    2017-12-21T00:00:00                     57037   \n",
       "32  Seattle, Washington    2017-12-21T00:00:00         Pusan,South Korea   \n",
       "\n",
       "   record_status_indicator   place_of_receipt port_of_destination  \\\n",
       "13                     New           HAIPHONG                 NaN   \n",
       "29                     New              BUSAN                 NaN   \n",
       "30                     New  SHANGHAI, SHANGHA                 NaN   \n",
       "31                     New  SHANGHAI, SHANGHA                 NaN   \n",
       "32                     New   TIANJIN XINGANG,                3512   \n",
       "\n",
       "            ...            shipper_comm_number_qualifier shipper_comm_number  \\\n",
       "13          ...                                      NaN                 NaN   \n",
       "29          ...                                      NaN                 NaN   \n",
       "30          ...                                      NaN                 NaN   \n",
       "31          ...                                      NaN                 NaN   \n",
       "32          ...                                      NaN                 NaN   \n",
       "\n",
       "   container_number description_sequence_number piece_count  \\\n",
       "13      SEGU5919545                           1        1243   \n",
       "29      FCIU2664040                           1          19   \n",
       "30      NYKU0728968                           1         640   \n",
       "31      TCLU6640359                           1          54   \n",
       "32      HLXU3272262                           1          19   \n",
       "\n",
       "                                     description_text harmonized_number  \\\n",
       "13      APPAREL, MEN S WOVEN SHIRT, LADIES STRIPES SH          620520.0   \n",
       "29                                          EDTA-ACID               NaN   \n",
       "30                                       OFFICE CHAIR          940130.0   \n",
       "31      NAILHEADS HTS CODE 940161 PO 12030814562 IF A          940161.0   \n",
       "32  EDTA-ACID S/C S17ASC112 MR CODE BLUBRE1 XFAX +...          292249.0   \n",
       "\n",
       "   harmonized_value harmonized_weight harmonized_weight_unit  \n",
       "13         241660.0           12083.0              Kilograms  \n",
       "29              0.0           19057.0              Kilograms  \n",
       "30         215008.0           10750.0              Kilograms  \n",
       "31          57708.0            2885.0              Kilograms  \n",
       "32              0.0               0.0                    NaN  \n",
       "\n",
       "[5 rows x 31 columns]"
      ]
     },
     "execution_count": 117,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# weight_matches[12000]\n",
    "# re.findall(r'[\\d,]+[.]?[\\d,]+', sea2017_df.loc[12000, 'description_text'])\n",
    "# sea2017_df.loc[12000, 'description_text']\n",
    "# weight_matches.head()\n",
    "weight_matches.shape\n",
    "# df5.shape\n",
    "df5.head()\n",
    "# cities_2017_dfs['Seattle'].head()\n",
    "df = cities_2017_dfs['Seattle']\n",
    "df[df.loc[:, 'harmonized_value'].notnull()].head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Ok, so that list of words for capturing descriptions that might mention the weight of the cargo yields results that are a little underwhelming: only ~7,000 of the 19,000 rows that have no weight listed."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Analysis\n",
    "\n",
    "One question that arises, in the case of Seattle, is why the overwhelming majority of shipments are coming from Vancouver, even though probably most of the ultimate origin of many imported goods is still China.  Vancouver is very close by, and may have a better port or otherwise have a different set of tariff/duties rules than the US, which would make it likely that foreign imports might shuttle through there.  It's a few years old, but [this article](https://www.joc.com/port-news/us-ports/us-importers-moving-more-containers-through-vancouver_20150618.html) states that many other US cities receive more imports lately from Vancouver, rather than Seattle or Tacoma, lending credence to the idea that Vancouver is a popular import point for foreign goods from overseas.  Actually, the article sites a labor dispute between longshoreman unions and ports in the PacNW as a driver for a temporary shift to Canada, which may have initiated a shift in traffic up North.  Still, the question as to why goods would pass through there on the way here seems a little puzzling.  A deeper dive into this issue could be interesting, or just too obscure to address adequately in a reasonable amount of time."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.6.2"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}