Python Notebooks

Census API: US exports by partner

Brian Dew, @bd_econ, August 24, 2018

The Census Bureau allows API access to data on international trade. This example retrieves the June 2018 US export values by partner country. Documentation for the Census international trade API is here. The list of variables is here

Requirements:

Python (3.6 used, probably works on 2.7+), requests, pandas.

Also requires API key, free from Census, which I've stored locally in a file called config.py.

In [1]:
# import packages
import requests
import pandas as pd
import numpy as np
import matplotlib.pyplot as plt

# Local file with API key
from config import census_key as key

API Parameters/ Settings

The variable names, caled "param" are from the Census list of variables.

In [2]:
# time series API: https://api.census.gov/data/timeseries.html
base = 'https://api.census.gov/data/timeseries/'
flow = 'intltrade/exports/hs'
param = 'CTY_CODE,CTY_NAME,ALL_VAL_MO'

# date needs manual update from release schedule:
# https://www.census.gov/foreign-trade/reference/release_schedule.html
time = '2018-06' 

url = f'{base}{flow}?get={param}&key={key}&time={time}'

Request data from API

In [3]:
# send request to census api and read response as json data
r = requests.get(url).json()

# print first five observations
r[:5]
Out[3]:
[['CTY_CODE', 'CTY_NAME', 'ALL_VAL_MO', 'time'],
 ['', 'TOTAL FOR ALL COUNTRIES', '145109955530', '2018-06'],
 ['1010', 'GREENLAND', '178847', '2018-06'],
 ['1220', 'CANADA', '26140730952', '2018-06'],
 ['1610', 'ST PIERRE AND MIQUELON', '118784', '2018-06']]

The first row contains the column headers, while the second row contains the total value of goods exports in June 2018. I capture this total value amount, in billions, for use later:

In [4]:
# store row with total exports (converted to billions)
tot = int(r[1][2]) / 1_000_000_000

print(f'June 2018 total goods exports: {tot:.1f}B')
June 2018 total goods exports: 145.1B

Cleaning up the data

The Census API provides an already clean output, as seen above. Pandas can read this as a dataframe, with not problems, for example. But the data also include some rows for country groups, which are not needed. All we need is the country name and the export value in billions of USD dollars. Countries have codes between 1000 and 8000.

In [5]:
# keep country name (pos 1) and export value in billions
s = (pd.Series({i['CTY_NAME']: (int(i['ALL_VAL_MO']) / 1_000_000_000) 
                for i in [dict(zip(r[0], v)) for v in r[1:]] 
                if i['CTY_CODE'].isdigit() 
                and int(i['CTY_CODE']) in range(1000, 8000)})
     .sort_values())

s.tail(5)
Out[5]:
UNITED KINGDOM     5.383692
JAPAN              6.226491
CHINA             11.115623
MEXICO            22.210991
CANADA            26.140731
dtype: float64

Plot the top 20 partners by export volume

I'm partial to very simple horizontal bar plots with value labels but no axis markings. The graph includes: a title, country labels, blue bars in lengths according to the series values, series value labels, and a footer.

In [6]:
# plot top 20 partners
s20 = s.tail(20)
s20.plot(kind='barh', figsize=(6,10), color='blue')

# add value labels to ends of bars and country names to start of bars
for i, val in enumerate(s20):
    plt.text(val+0.2, i-0.1, f'{val:.1f}  ({val / tot * 100:.1f}%)')
    plt.text(-0.5, i-0.1, s20.index[i], ha='right')
    
# add title and footer
plt.text(-10, 20, 'Top export destinations of US goods in June 2018, billions of USD', 
          fontsize=13, weight='bold')
plt.text(-10, -1.5, 'Source: Census Bureau; share of total in parentheses')

plt.axis('off')
plt.show()

Note for future work

The values shown above cover goods exports for one month, June 2018. Additional work could look at how exports are changing and try to differentiate the effects of tariffs, exchange rates, and things like changes in relative demand and relative factor prices.

The first step would to pull the same data for the latest full year. The combined 12 monthly values would be more stable and more representative of the current trading relationship with other countries.

The second step would be to pull the data for the previous full year and compare how the values change between the two one-year periods, in levels and in percentage terms.

The next step is to collect, by country (or currency) the USD exchange rate and the consumer price index. These can be used to determine whether changes to exchange rates or relative prices can explain large changes in US exports. Another check would be see whether another country is simply growing or shrinking so fast that it affects US exports. I'd also want to look at the US export price index from BLS.

The ideal end result would be a list of countries where trade with the US changes in an economically significant way (large USD value and also large percentage change) over the past year. The list would include information on whether the change can be explained by exchange rates or prices.