U.S. International Trade in Goods and Services, monthly, 1992 - Present¶
A CNBC article titled "US trade deficit rises to near 9½-year high" published on April 5, 2018 begins as follows:
The U.S. trade deficit increased to a near 9½-year high in February as both exports and imports rose to record highs, but the shortfall with China narrowed sharply.
The Commerce Department said on Thursday the trade gap rose 1.6 percent to \$57.6 billion. That was the highest level since October 2008 and followed a slightly downwardly revised \$56.7 billion shortfall in January.
The following python code verifies these numbers via data posted on the U.S. Census website.
In [107]:
import pandas as pd
import math
xx = pd.read_excel('https://www.census.gov/foreign-trade/statistics/historical/exhibit_history.xls', skiprows=4)
xx.columns = ('Month','Balance','Balance Goods','Balance Svcs',
'Exports','Exports Goods','Exports Svcs',
'Imports','Imports Goods','Imports Svcs')
for i in range(0,xx.shape[0]):
if (i % 14 == 0):
iyear = xx.loc[i,'Month']
xx.loc[i,'Month'] = ''
if (i % 14 == 1):
xx.loc[i,'Month'] = ''
xx.loc[i,'iYear'] = iyear
xx.loc[i,'iMonth'] = (i % 14) - 1
#xx.loc[i,'Year'] = xx.loc[i,'iYear'].astype(str).astype(float) + (xx.loc[i,'iMonth'].astype(float)-1) / 12
if math.isnan(pd.to_numeric(iyear)):
xx.loc[i,'Month'] = ''
yy = xx[xx['Month'] != ""]
yy = yy[yy['Balance'].notnull()]
#yy.loc[:,['Balance','Exports','Imports']] = yy.loc[:,['Balance','Exports','Imports']].astype(float)
yy.loc[:,'Balance'] = yy.loc[:,'Balance'].astype(float)
yy.loc[:,'Exports'] = yy.loc[:,'Exports'].astype(float)
yy.loc[:,'Imports'] = yy.loc[:,'Imports'].astype(float)
yy.loc[:,'iYear'] = yy.loc[:,'iYear'].astype(int)
yy.loc[:,'iMonth'] = yy.loc[:,'iMonth'].astype(int)
yy.loc[:,'Year'] = yy.loc[:,'iYear'] + (yy.loc[:,'iMonth']-1) / 12
#print(yy.dtypes)
yy.loc[:,['Balance','Exports','Imports']] /= 1000
zz = yy[yy['iYear'] >= 2008]
arrays = [['','Goods & Svcs','Goods','Svcs','Goods & Svcs','Goods','Svcs','Goods & Svcs','Goods','Svcs','','',''],
['Month','Balance','Balance','Balance','Exports','Exports','Exports','Imports','Imports','Imports','iYear','iMonth','Year']]
tuples = list(zip(*arrays))
index = pd.MultiIndex.from_tuples(tuples)
zz.columns = index
#print(zz[['iYear','iMonth','Balance','Imports','Exports']].head(14))
#print(zz[['iYear','iMonth','Balance','Imports','Exports']].tail(14))
print(zz.iloc[:,[10,11,1,4,7]].head(24))
print(zz.iloc[:,[10,11,1,4,7]].tail(27)) #UPDATE
As can be seen above, the numbers in the article are accurate. The trade deficit in goods and services rose from $56.7 billion in January to $57.6 billion in February. This is the highest level since it reached $60.2 billion in October of 2008. In any event, the following code creates a plot of the U.S. trade in goods and services since 1992.
In [108]:
import matplotlib.pyplot as plt
%matplotlib inline
fig = plt.figure(figsize=(10,8))
ax = fig.add_subplot(1, 1, 1)
ax.grid()
ax.set_title('U.S. Trade in Goods and Services, monthly, 1992 - Present')
ax.set_xlabel('Source: https://www.census.gov/foreign-trade/statistics/historical/exhibit_history.xls')
ax.set_ylabel('Billions of Dollars')
#ax.set_xlim([2008,2018])
#ax.set_ylim([-5,6])
#ax.set_yticks(range(-5,6))
ax.plot(yy.Year,yy.Imports,'r')
ax.plot(yy.Year,yy.Exports,'g')
ax.plot(yy.Year,yy.Balance,'b-')
#ax.plot(qq.Year,qq.Change1y,'r-')
#ax.plot(qq.Year,qq.Change1q,'g-')
#ax.axhline(y=3, color='k', linestyle='--')
ax.legend()
fig.savefig('trade92_1903.png') # UPDATE
In [109]:
fig = plt.figure(figsize=(10,8))
ax = fig.add_subplot(1, 1, 1)
ax.grid()
ax.set_title('U.S. Trade in Goods and Services, monthly, 2015 - Present')
ax.set_xlabel('Source: https://www.census.gov/foreign-trade/statistics/historical/exhibit_history.xls')
ax.set_ylabel('Billions of Dollars')
ax.set_xlim([2015,2020])
#ax.set_ylim([-5,6])
#ax.set_yticks(range(-5,6))
ax.plot(yy.Year,yy.Imports,'r')
ax.plot(yy.Year,yy.Exports,'g')
ax.plot(yy.Year,yy.Balance,'b-')
#ax.plot(qq.Year,qq.Change1y,'r-')
#ax.plot(qq.Year,qq.Change1q,'g-')
#ax.axhline(y=3, color='k', linestyle='--')
ax.legend()
fig.savefig('trade15_1903.png') # UPDATE
U.S. International Trade in Goods and Services, annual, 1960 - Present¶
Another file on the Census website gives annual trade data since 1960. The following code reads and outputs data from that file.
In [110]:
import pandas as pd
xx = pd.read_excel('https://www.census.gov/foreign-trade/statistics/historical/gands.xls', skiprows=5)
yy = xx.iloc[0:59,0:10] # UPDATE (0:59=1960-2018)
arrays = [[ '','Goods & Svcs','Goods','Services','Goods & Svcs','Goods','Services','Goods & Svcs','Goods','Services'],
['Year','Balance','Balance','Balance','Exports','Exports','Exports','Imports','Imports','Imports']]
tuples = list(zip(*arrays))
index = pd.MultiIndex.from_tuples(tuples)
yy.columns = index
yy.iloc[:,1:10] /= 1000
yy = yy.iloc[:,[0,2,5,8,1,4,7]]
print("U.S. International Trade in Goods and Services, 1960 - Present")
print(yy)
In [111]:
fig = plt.figure(figsize=(10,8))
ax = fig.add_subplot(1, 1, 1)
ax.grid()
ax.set_title('U.S. Trade in Goods and Services, annual, 1960 - Present')
ax.set_xlabel('Source: https://www.census.gov/foreign-trade/statistics/historical/gands.xls')
ax.set_ylabel('Billions of Dollars')
#ax.set_xlim([2008,2018])
#ax.set_ylim([-5,6])
#ax.set_yticks(range(-5,6))
ax.plot(yy.iloc[:,0],yy.iloc[:,6],'r')
ax.plot(yy.iloc[:,0],yy.iloc[:,5],'g')
ax.plot(yy.iloc[:,0],yy.iloc[:,4],'b')
ax.plot(yy.iloc[:,0],yy.iloc[:,3],'r--')
ax.plot(yy.iloc[:,0],yy.iloc[:,2],'g--')
ax.plot(yy.iloc[:,0],yy.iloc[:,1],'b--')
#ax.plot(yy.Year,yy.Exports,'g')
#ax.plot(yy.Year,yy.Balance,'b-')
#ax.plot(qq.Year,qq.Change1y,'r-')
#ax.plot(qq.Year,qq.Change1q,'g-')
#ax.axhline(y=3, color='k', linestyle='--')
ax.legend()
fig.savefig('trade60_18.png') # UPDATE
Read latest GDP figures from Bureau of Economic Analysis website¶
One problem with the prior graph is that it shows U.S trade as measured in billions of dollars. Over the years, the effect of each dollar lessens as the GDP grows. For this reason, it's instructive to look at trade data as a percent of GDP. The following code reads both the annual and quarterly GDP figures from the Bureau of Economic Analysis website and calculates the percent change between successive periods.
In [112]:
import pandas as pd
#xx = pd.read_excel('https://www.bea.gov/national/xls/gdplev.xlsx', skiprows=7)
xx = pd.read_csv('gdplev.csv', skiprows=7) #Save GDP file locally as a CSV file
aa = xx.iloc[0:90, 0:3] # UPDATE (0:90 = 2018) # was 0:89
aa.columns = ['Year','Current $bil','Chained 2009 $bil']
qq = xx.iloc[0:289, 4:7] #UPDATE (0:288 = 2018Q4 # was 0:287
qq.columns = ['Quarter','Current $bil','Chained 2009 $bil']
# Must convert strings to floats if read from CSV file
aa['Chained 2009 $bil'] = aa['Chained 2009 $bil'].str.replace(',', '')
qq['Chained 2009 $bil'] = qq['Chained 2009 $bil'].str.replace(',', '')
aa['Chained 2009 $bil'] = aa['Chained 2009 $bil'].astype(float)
qq['Chained 2009 $bil'] = qq['Chained 2009 $bil'].astype(float)
# Add to code from gdp_growth ipynb
aa['Current $bil'] = aa['Current $bil'].str.replace(',', '')
aa['Current $bil'] = aa['Current $bil'].astype(float)
aa['Pct_Change'] = aa.loc[:,'Chained 2009 $bil'].pct_change()*100
qq['Pct_Change'] = qq.loc[:,'Chained 2009 $bil'].pct_change()*100
print(aa.head())
print(aa.tail(18))
Output and plot trade data as percentage of GDP¶
The following code then displays and plots the trade data as a percent of GDP.
In [113]:
aa60 = aa[aa.iloc[:,0] >= 1960]
aa60.index = range(0,aa60.shape[0])
yy['GDP'] = 1
yy.iloc[:,7] = aa60.loc[:,'Current $bil']
#print(yy)
print(yy.info())
yy.iloc[:,1] *= (100/yy.iloc[:,7])
yy.iloc[:,2] *= (100/yy.iloc[:,7])
yy.iloc[:,3] *= (100/yy.iloc[:,7])
yy.iloc[:,4] *= (100/yy.iloc[:,7])
yy.iloc[:,5] *= (100/yy.iloc[:,7])
yy.iloc[:,6] *= (100/yy.iloc[:,7])
#print(yy.to_string(index=False))
print(yy.iloc[:,0:7])
In [114]:
fig = plt.figure(figsize=(10,8))
ax = fig.add_subplot(1, 1, 1)
ax.grid()
ax.set_title('U.S. Trade in Goods and Services, annual, 1960 - Present')
ax.set_xlabel('Source: https://www.census.gov/foreign-trade/statistics/historical/gands.xls')
ax.set_ylabel('Percent of GDP')
#ax.set_xlim([2008,2018])
#ax.set_ylim([-5,6])
#ax.set_yticks(range(-5,6))
ax.plot(yy.iloc[:,0],yy.iloc[:,6],'r')
ax.plot(yy.iloc[:,0],yy.iloc[:,5],'g')
ax.plot(yy.iloc[:,0],yy.iloc[:,4],'b')
ax.plot(yy.iloc[:,0],yy.iloc[:,3],'r--')
ax.plot(yy.iloc[:,0],yy.iloc[:,2],'g--')
ax.plot(yy.iloc[:,0],yy.iloc[:,1],'b--')
#ax.plot(yy.Year,yy.Exports,'g')
#ax.plot(yy.Year,yy.Balance,'b-')
#ax.plot(qq.Year,qq.Change1y,'r-')
#ax.plot(qq.Year,qq.Change1q,'g-')
#ax.axhline(y=3, color='k', linestyle='--')
ax.legend()
fig.savefig('trade60_18gdp.png') # UPDATE
List Countries with which the U.S. has the Largest Deficit, Exports, and Imports in Goods¶
In [115]:
xx = pd.read_excel('https://www.census.gov/foreign-trade/balance/country.xlsx', skiprows=0)
yy = xx[xx['CTY_CODE'] > 19]
yy = yy[yy['year'] == 2018] # UPDATE TO LAST FULL YEAR
yy['Balance'] = yy['EYR'] - yy['IYR']
yy = yy[['year','CTYNAME','Balance','EYR','IYR']]
yy.columns = ['Year','Country','Balance','Exports','Imports']
yy.iloc[:,[2,3,4]] /= 1000
yy['Year'] = yy['Year'].astype(int)
zz = yy.sort_values('Balance')
print("\nCountries with which the U.S. has the Largest Deficits in Goods ($billion)\n")
print(zz.head(20))
zz = yy.sort_values('Exports',ascending=False)
print("\nCountries with which the U.S. has the Largest Exports in Goods ($billion)\n")
print(zz.head(20))
zz = yy.sort_values('Imports',ascending=False)
print("\nCountries with which the U.S. has the Largest Imports in Goods ($billion)\n")
print(zz.head(20))
List Balance in Goods and Services by Selected Countries - BOP Basis¶
In [116]:
xx = pd.read_excel('https://www.census.gov/foreign-trade/Press-Release/current_press_release/exh20.xls', skiprows=5)
#xx.columns = ['Country','2016Q3','2016Q4','2017Q1','2017Q2','2017Q3','2017Q4','2015','2016','2017']
xx.columns = ['Country','2016Q4','2017Q1','2017Q2','2017Q3','2017Q4','2018Q1','2015','2016','2017']
xx.columns = ['Country','2017Q3','2017Q4','2018Q1','2018Q2','2018Q3','2018Q4','2016','2017','2018']
xx = xx.iloc[0:16,:]
xx.iloc[:,1:10] /= 1000
yy = xx.iloc[:,[0,7,8,9]]
print("\nAnnual Balance in Goods and Services by Selected Countries - BOP Basis ($billion)\n")
print(yy)
In [117]:
yy.index = yy.iloc[:,0]
yyn = yy.iloc[:,1:4]
fig = plt.figure(figsize=(10,8))
ax = fig.add_subplot(1, 1, 1)
ax.grid()
ax.set_title('Annual Balance in Goods and Services by Selected Countries - BOP Basis')
ax.set_xlabel('https://www.census.gov/foreign-trade/Press-Release/current_press_release/exh20.xls')
ax.set_ylabel('Billions of Dollars')
ax.plot(yyn.columns,yyn.iloc[1,:],'b')
ax.plot(yyn.columns,yyn.iloc[2,:],'r')
ax.plot(yyn.columns,yyn.iloc[4,:],color='orange',linestyle='dashed')
ax.plot(yyn.columns,yyn.iloc[8,:],'k--')
ax.plot(yyn.columns,yyn.iloc[10,:],'g--')
ax.legend()
fig.savefig("tradenat16_18.png") # UPDATE
In [118]:
yy = xx.iloc[:,[0,1,2,3,4,5,6]]
print("\nQuarterly Balance in Goods and Services by Selected Countries - BOP Basis ($billion)\n")
print(yy)
U.S. Trade in Goods with China, annual, 1985 - Present¶
The following code reads a file on the Census website which gives monthly and annual trade data since 1985 for all major countries. The following code reads and outputs annual data from that file for China.
In [119]:
xx = pd.read_excel('https://www.census.gov/foreign-trade/balance/country.xlsx', skiprows=0)
yy = xx.loc[xx['CTYNAME'] == "China",('year','IYR','EYR')].copy()
yy.columns = ['Year','Imports','Exports']
yy = yy[:-1]
yy['Imports'] /= 1000
yy['Exports'] /= 1000
yy['Balance'] = yy['Exports'] - yy['Imports']
aa85 = aa[aa.iloc[:,0] >= 1985]
yy['GDP'] = aa85.loc[:,'Current $bil'].values
print(yy)
The following code plots the annual U.S. Trade in Goods with China since 1985 in billions of dollars.
In [120]:
fig = plt.figure(figsize=(10,8))
ax = fig.add_subplot(1, 1, 1)
ax.grid()
ax.set_title('U.S. Trade in Goods with China, annual, 1985 - Present')
ax.set_xlabel('Source: https://www.census.gov/foreign-trade/balance/country.xlsx')
ax.set_ylabel('Billions of Dollars')
#ax.set_xlim([2008,2018])
#ax.set_ylim([-5,6])
#ax.set_yticks(range(-5,6))
ax.plot(yy.iloc[:,0],yy.iloc[:,1],'b')
ax.plot(yy.iloc[:,0],yy.iloc[:,2],'g')
ax.plot(yy.iloc[:,0],yy.iloc[:,3],'r')
ax.legend()
fig.savefig('trade_china_85_18.png') # UPDATE
The following code plots the annual U.S. Trade in Goods with China since 1985 as percent of the U.S. GDP.
In [121]:
yy.iloc[:,1] *= (100/yy.iloc[:,4])
yy.iloc[:,2] *= (100/yy.iloc[:,4])
yy.iloc[:,3] *= (100/yy.iloc[:,4])
fig = plt.figure(figsize=(10,8))
ax = fig.add_subplot(1, 1, 1)
ax.grid()
ax.set_title('U.S. Trade in Goods with China, annual, 1985 - Present')
ax.set_xlabel('Source: https://www.census.gov/foreign-trade/balance/country.xlsx')
ax.set_ylabel('Percent of GDP')
#ax.set_xlim([2008,2018])
#ax.set_ylim([-5,6])
#ax.set_yticks(range(-5,6))
ax.plot(yy.iloc[:,0],yy.iloc[:,1],'b')
ax.plot(yy.iloc[:,0],yy.iloc[:,2],'g')
ax.plot(yy.iloc[:,0],yy.iloc[:,3],'r')
ax.legend()
fig.savefig('trade_china_gdp_85_18.png') # UPDATE
Note: The Jupyter Notebook from which this post is generated can be found at http://econdataus.com/trade1903.ipynb. Links to additional Jupyter Notebooks can be found at http://econdataus.com/jupyter.html. Trade data for 1790-2006 can be found at http://www.econdataus.com/trade06.html.