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
                 Goods & Svcs                  
    iYear iMonth      Balance  Exports  Imports
226  2008      1      -61.139  150.167  211.306
227  2008      2      -64.349  152.433  216.781
228  2008      3      -60.065  152.089  212.154
229  2008      4      -63.527  156.757  220.284
230  2008      5      -62.152  158.799  220.950
231  2008      6      -60.670  163.608  224.279
232  2008      7      -66.841  165.379  232.220
233  2008      8      -62.107  162.659  224.766
234  2008      9      -60.500  153.586  214.086
235  2008     10      -60.190  151.364  211.554
236  2008     11      -44.724  142.400  187.124
237  2008     12      -42.463  132.372  174.835
240  2009      1      -37.842  125.294  163.136
241  2009      2      -27.767  127.342  155.109
242  2009      3      -29.162  125.998  155.161
243  2009      4      -29.706  124.389  154.095
244  2009      5      -25.373  126.220  151.593
245  2009      6      -26.366  128.891  155.257
246  2009      7      -32.790  130.847  163.638
247  2009      8      -31.266  131.439  162.705
248  2009      9      -34.491  136.141  170.632
249  2009     10      -33.827  140.401  174.228
250  2009     11      -37.203  141.370  178.573
251  2009     12      -37.983  144.718  182.700
                 Goods & Svcs                  
    iYear iMonth      Balance  Exports  Imports
352  2017      1      -46.879  191.430  238.309
353  2017      2      -44.171  192.340  236.510
354  2017      3      -43.909  192.536  236.446
355  2017      4      -46.074  192.194  238.268
356  2017      5      -45.823  192.772  238.595
357  2017      6      -44.803  194.778  239.580
358  2017      7      -44.221  195.160  239.382
359  2017      8      -44.163  195.594  239.757
360  2017      9      -44.407  198.352  242.760
361  2017     10      -46.986  198.629  245.615
362  2017     11      -48.952  202.295  251.246
363  2017     12      -51.889  204.992  256.881
366  2018      1      -53.090  201.276  254.366
367  2018      2      -55.719  204.713  260.432
368  2018      3      -47.448  209.233  256.681
369  2018      4      -46.454  209.101  255.555
370  2018      5      -43.511  212.855  256.366
371  2018      6      -46.910  211.077  257.987
372  2018      7      -51.444  208.989  260.434
373  2018      8      -54.868  207.475  262.343
374  2018      9      -55.699  210.622  266.321
375  2018     10      -56.534  210.698  267.232
376  2018     11      -50.529  209.325  259.854
377  2018     12      -59.900  205.393  265.293
380  2019      1      -51.134  207.355  258.488
381  2019      2      -49.285  209.905  259.190
382  2019      3      -50.002  211.966  261.968

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)
U.S. International Trade in Goods and Services, 1960 - Present
            Goods                     Goods & Svcs                    
    Year  Balance   Exports   Imports      Balance   Exports   Imports
0   1960    4.892    19.650    14.758        3.508    25.940    22.432
1   1961    5.571    20.108    14.537        4.195    26.403    22.208
2   1962    4.521    20.781    16.260        3.370    27.722    24.352
3   1963    5.224    22.272    17.048        4.210    29.620    25.410
4   1964    6.801    25.501    18.700        6.022    33.341    27.319
5   1965    4.951    26.461    21.510        4.664    35.285    30.621
6   1966    3.817    29.310    25.493        2.939    38.926    35.987
7   1967    3.800    30.666    26.866        2.604    41.333    38.729
8   1968    0.635    33.626    32.991        0.250    45.543    45.293
9   1969    0.607    36.414    35.807        0.091    49.220    49.129
10  1970    2.603    42.469    39.866        2.254    56.640    54.386
11  1971   -2.260    43.319    45.579       -1.302    59.677    60.979
12  1972   -6.416    49.381    55.797       -5.443    67.222    72.665
13  1973    0.911    71.410    70.499        1.900    91.242    89.342
14  1974   -5.505    98.306   103.811       -4.293   120.897   125.190
15  1975    8.903   107.088    98.185       12.404   132.585   120.181
16  1976   -9.483   114.745   124.228       -6.082   142.716   148.798
17  1977  -31.091   120.816   151.907      -27.246   152.301   179.547
18  1978  -33.927   142.075   176.002      -29.763   178.428   208.191
19  1979  -27.568   184.439   212.007      -24.565   224.131   248.696
20  1980  -25.500   224.250   249.750      -19.407   271.834   291.241
21  1981  -28.023   237.044   265.067      -16.172   294.398   310.570
22  1982  -36.485   211.157   247.642      -24.156   275.236   299.391
23  1983  -67.102   201.799   268.901      -57.767   266.106   323.874
24  1984 -112.492   219.926   332.418     -109.072   291.094   400.166
25  1985 -122.173   215.915   338.088     -121.880   289.070   410.950
26  1986 -145.081   223.344   368.425     -138.538   310.033   448.572
27  1987 -159.557   250.208   409.765     -151.684   348.869   500.552
28  1988 -126.959   320.230   447.189     -114.566   431.149   545.715
29  1989 -117.749   359.916   477.665      -93.141   487.003   580.144
30  1990 -111.037   387.401   498.438      -80.864   535.233   616.097
31  1991  -76.937   414.083   491.020      -31.135   578.344   609.479
32  1992  -96.897   439.631   536.528      -39.212   616.882   656.094
33  1993 -132.451   456.943   589.394      -70.311   642.863   713.174
34  1994 -165.831   502.859   668.690      -98.493   703.254   801.747
35  1995 -174.170   575.204   749.374      -96.384   794.387   890.771
36  1996 -191.000   612.113   803.113     -104.065   851.602   955.667
37  1997 -198.428   678.366   876.794     -108.273   934.453  1042.726
38  1998 -248.221   670.416   918.637     -166.140   933.174  1099.314
39  1999 -337.068   698.524  1035.592     -258.617   969.867  1228.485
40  2000 -446.783   784.940  1231.722     -372.517  1075.321  1447.837
41  2001 -422.370   731.331  1153.701     -361.511  1005.654  1367.165
42  2002 -475.245   698.036  1173.281     -418.955   978.706  1397.660
43  2003 -541.643   730.446  1272.089     -493.890  1020.418  1514.308
44  2004 -664.766   823.584  1488.349     -609.883  1161.549  1771.433
45  2005 -782.804   913.016  1695.820     -714.245  1286.022  2000.267
46  2006 -837.289  1040.905  1878.194     -761.716  1457.642  2219.358
47  2007 -821.196  1165.151  1986.347     -705.375  1653.548  2358.922
48  2008 -832.492  1308.795  2141.287     -708.726  1841.612  2550.339
49  2009 -509.694  1070.331  1580.025     -383.774  1583.053  1966.827
50  2010 -648.671  1290.279  1938.950     -495.225  1853.038  2348.263
51  2011 -740.999  1498.887  2239.886     -549.699  2125.947  2675.646
52  2012 -741.119  1562.630  2303.749     -537.408  2218.354  2755.762
53  2013 -700.539  1593.708  2294.247     -461.135  2294.199  2755.334
54  2014 -749.917  1635.563  2385.480     -489.584  2376.657  2866.241
55  2015 -761.868  1511.381  2273.249     -498.525  2266.691  2765.216
56  2016 -751.051  1456.957  2208.008     -502.001  2215.844  2717.846
57  2017 -807.495  1553.383  2360.878     -552.277  2351.072  2903.349
58  2018 -891.320  1672.331  2563.651     -622.106  2500.756  3122.862
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))
     Year  Current $bil  Chained 2009 $bil  Pct_Change
0  1929.0         104.6             1109.4         NaN
1  1930.0          92.2             1015.1   -8.500090
2  1931.0          77.4              950.0   -6.413161
3  1932.0          59.5              827.5  -12.894737
4  1933.0          57.2              817.3   -1.232628
      Year  Current $bil  Chained 2009 $bil  Pct_Change
72  2001.0       10581.8            13262.1    0.998401
73  2002.0       10936.4            13493.1    1.741806
74  2003.0       11458.2            13879.1    2.860721
75  2004.0       12213.7            14406.4    3.799238
76  2005.0       13036.6            14912.5    3.513022
77  2006.0       13814.6            15338.3    2.855323
78  2007.0       14451.9            15626.0    1.875697
79  2008.0       14712.8            15604.7   -0.136311
80  2009.0       14448.9            15208.8   -2.537056
81  2010.0       14992.1            15598.8    2.564305
82  2011.0       15542.6            15840.7    1.550760
83  2012.0       16197.0            16197.0    2.249269
84  2013.0       16784.9            16495.4    1.842316
85  2014.0       17521.7            16899.8    2.451593
86  2015.0       18219.3            17386.7    2.881099
87  2016.0       18707.2            17659.2    1.567290
88  2017.0       19485.4            18050.7    2.216975
89  2018.0       20500.6            18571.3    2.884099

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])
<class 'pandas.core.frame.DataFrame'>
RangeIndex: 59 entries, 0 to 58
Data columns (total 8 columns):
(, Year)                   59 non-null object
(Goods, Balance)           59 non-null float64
(Goods, Exports)           59 non-null float64
(Goods, Imports)           59 non-null float64
(Goods & Svcs, Balance)    59 non-null float64
(Goods & Svcs, Exports)    59 non-null float64
(Goods & Svcs, Imports)    59 non-null float64
(GDP, )                    59 non-null float64
dtypes: float64(7), object(1)
memory usage: 3.8+ KB
None
             Goods                      Goods & Svcs                      
    Year   Balance   Exports    Imports      Balance    Exports    Imports
0   1960  0.901917  3.622788   2.720870     0.646755   4.782448   4.135693
1   1961  0.990928  3.576663   2.585735     0.746176   4.696371   3.950196
2   1962  0.748634  3.441133   2.692499     0.558039   4.590495   4.032456
3   1963  0.819451  3.493647   2.674196     0.660392   4.646275   3.985882
4   1964  0.993572  3.725493   2.731921     0.879766   4.870855   3.991088
5   1965  0.666981  3.564731   2.897750     0.628317   4.753469   4.125152
6   1966  0.469265  3.603393   3.134128     0.361323   4.785591   4.424269
7   1967  0.441860  3.565814   3.123953     0.302791   4.806163   4.503372
8   1968  0.067503  3.574572   3.507069     0.026576   4.841395   4.814819
9   1969  0.059650  3.578420   3.518770     0.008943   4.836871   4.827928
10  1970  0.242523  3.956862   3.714339     0.210007   5.277183   5.067176
11  1971 -0.194008  3.718688   3.912696    -0.111769   5.122929   5.234698
12  1972 -0.501603  3.860605   4.362208    -0.425534   5.255414   5.680948
13  1973  0.063912  5.009822   4.945910     0.133296   6.401151   6.267855
14  1974 -0.356265  6.362024   6.718289    -0.277828   7.824036   8.101864
15  1975  0.528399  6.355748   5.827349     0.736186   7.869013   7.132827
16  1976 -0.506192  6.124960   6.631152    -0.324650   7.618021   7.942671
17  1977 -1.493467  5.803439   7.296907    -1.308771   7.315832   8.624604
18  1978 -1.442720  6.041631   7.484351    -1.265649   7.587515   8.853164
19  1979 -1.049290  7.020097   8.069387    -0.934990   8.530849   9.465839
20  1980 -0.892451  7.848318   8.740769    -0.679208   9.513667  10.192874
21  1981 -0.873807  7.391456   8.265263    -0.504272   9.179857   9.684128
22  1982 -1.091124  6.314881   7.406005    -0.722412   8.231234   8.953616
23  1983 -1.846505  5.553082   7.399587    -1.589626   7.322675   8.912328
24  1984 -2.786111  5.446949   8.233059    -2.701407   7.209580   9.910987
25  1985 -2.815695  4.976147   7.791841    -2.808942   6.662134   9.471076
26  1986 -3.167984  4.876932   8.044917    -3.025111   6.769871   9.795004
27  1987 -3.286312  5.153403   8.439714    -3.124156   7.185471  10.309606
28  1988 -2.424547  6.115461   8.540008    -2.187877   8.233691  10.421568
29  1989 -2.087156  6.379680   8.466836    -1.650968   8.632356  10.283324
30  1990 -1.862068  6.496638   8.358706    -1.356073   8.975751  10.331824
31  1991 -1.249363  6.724201   7.973563    -0.505594   9.391598   9.897192
32  1992 -1.486082  6.742497   8.228578    -0.601383   9.460945  10.062328
33  1993 -1.931167  6.662336   8.593503    -1.025151   9.373094  10.398245
34  1994 -2.275648  6.900579   9.176227    -1.351589   9.650538  11.002127
35  1995 -2.279802  7.529144   9.808945    -1.261620  10.398144  11.659764
36  1996 -2.365882  7.582131   9.948013    -1.289034  10.548637  11.837671
37  1997 -2.313328  7.908576  10.221904    -1.262276  10.894108  12.156384
38  1998 -2.738900  7.397449  10.136349    -1.833208  10.296752  12.129960
39  1999 -3.499933  7.253097  10.753029    -2.685340  10.070576  12.755926
40  2000 -4.357881  7.656233  12.014104    -3.633497  10.488583  14.122070
41  2001 -3.991476  6.911215  10.902691    -3.416347   9.503619  12.919966
42  2002 -4.345534  6.382685  10.728220    -3.830831   8.949069  12.779891
43  2003 -4.727121  6.374876  11.101997    -4.310363   8.905570  13.215933
44  2004 -5.442790  6.743116  12.185898    -4.993434   9.510214  14.503656
45  2005 -6.004664  7.003483  13.008146    -5.478767   9.864704  15.343471
46  2006 -6.060899  7.534818  13.595718    -5.513848  10.551460  16.065308
47  2007 -5.682270  8.062269  13.744539    -4.880846  11.441734  16.322574
48  2008 -5.658284  8.895621  14.553905    -4.817071  12.517074  17.334151
49  2009 -3.527563  7.407699  10.935262    -2.656078  10.956218  13.612296
50  2010 -4.326752  8.606393  12.933145    -3.303240  12.360096  15.663336
51  2011 -4.767536  9.643734  14.411270    -3.536725  13.678194  17.214919
52  2012 -4.575656  9.647651  14.223307    -3.317948  13.696080  17.014027
53  2013 -4.173626  9.494891  13.668518    -2.747321  13.668232  16.415552
54  2014 -4.279933  9.334500  13.614432    -2.794158  13.564078  16.358236
55  2015 -4.181654  8.295494  12.477148    -2.736247  12.441153  15.177400
56  2016 -4.014770  7.788215  11.802985    -2.683464  11.844873  14.528342
57  2017 -4.144103  7.972035  12.116138    -2.834312  12.065813  14.900125
58  2018 -4.347775  8.157473  12.505249    -3.034575  12.198453  15.233027
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))
Countries with which the U.S. has the Largest Deficits in Goods ($billion)

      Year       Country     Balance     Exports     Imports
4894  2018         China -419.162002  120.341426  539.503428
621   2018        Mexico  -81.517381  265.010357  346.527739
2528  2018       Germany  -68.250334   57.653808  125.904142
5089  2018         Japan  -67.629532   74.966716  142.596248
2304  2018       Ireland  -46.782205   10.686901   57.469106
4524  2018       Vietnam  -39.528312    9.683740   49.212053
3406  2018         Italy  -31.568966   23.153302   54.722268
4615  2018      Malaysia  -26.519236   12.864625   39.383861
4300  2018         India  -21.287394   33.120080   54.407474
558   2018        Canada  -19.753540  298.727543  318.481083
4496  2018      Thailand  -19.312134   12.588291   31.900425
2721  2018   Switzerland  -18.907812   22.230660   41.138472
4984  2018  Korea, South  -17.946373   56.344306   74.290679
2493  2018        France  -16.195470   36.326329   52.521799
5054  2018        Taiwan  -15.518770   30.242790   45.761560
2881  2018        Russia  -14.127839    6.667639   20.795478
4685  2018     Indonesia  -12.643490    8.226195   20.869685
3849  2018          Iraq  -10.558981    1.313196   11.872177
4083  2018  Saudi Arabia  -10.465528   13.624380   24.089908
2563  2018       Austria   -9.924186    3.569043   13.493229

Countries with which the U.S. has the Largest Exports in Goods ($billion)

      Year               Country     Balance     Exports     Imports
558   2018                Canada  -19.753540  298.727543  318.481083
621   2018                Mexico  -81.517381  265.010357  346.527739
4894  2018                 China -419.162002  120.341426  539.503428
5089  2018                 Japan  -67.629532   74.966716  142.596248
2269  2018        United Kingdom    5.416198   66.228172   60.811973
2528  2018               Germany  -68.250334   57.653808  125.904142
4984  2018          Korea, South  -17.946373   56.344306   74.290679
2339  2018           Netherlands   24.784737   49.390541   24.605805
1870  2018                Brazil    8.332804   39.494177   31.161373
5019  2018             Hong Kong   31.148402   37.459620    6.311218
2493  2018                France  -16.195470   36.326329   52.521799
4650  2018             Singapore    5.884593   33.141038   27.256445
4300  2018                 India  -21.287394   33.120080   54.407474
2374  2018               Belgium   14.222824   31.415753   17.192929
5054  2018                Taiwan  -15.518770   30.242790   45.761560
5124  2018             Australia   15.180510   25.306451   10.125941
3406  2018                 Italy  -31.568966   23.153302   54.722268
2721  2018           Switzerland  -18.907812   22.230660   41.138472
4146  2018  United Arab Emirates   14.540917   19.548341    5.007424
1835  2018                 Chile    3.973687   15.340120   11.366434

Countries with which the U.S. has the Largest Imports in Goods ($billion)

      Year         Country     Balance     Exports     Imports
4894  2018           China -419.162002  120.341426  539.503428
621   2018          Mexico  -81.517381  265.010357  346.527739
558   2018          Canada  -19.753540  298.727543  318.481083
5089  2018           Japan  -67.629532   74.966716  142.596248
2528  2018         Germany  -68.250334   57.653808  125.904142
4984  2018    Korea, South  -17.946373   56.344306   74.290679
2269  2018  United Kingdom    5.416198   66.228172   60.811973
2304  2018         Ireland  -46.782205   10.686901   57.469106
3406  2018           Italy  -31.568966   23.153302   54.722268
4300  2018           India  -21.287394   33.120080   54.407474
2493  2018          France  -16.195470   36.326329   52.521799
4524  2018         Vietnam  -39.528312    9.683740   49.212053
5054  2018          Taiwan  -15.518770   30.242790   45.761560
2721  2018     Switzerland  -18.907812   22.230660   41.138472
4615  2018        Malaysia  -26.519236   12.864625   39.383861
4496  2018        Thailand  -19.312134   12.588291   31.900425
1870  2018          Brazil    8.332804   39.494177   31.161373
4650  2018       Singapore    5.884593   33.141038   27.256445
2339  2018     Netherlands   24.784737   49.390541   24.605805
4083  2018    Saudi Arabia  -10.465528   13.624380   24.089908

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)
Annual Balance in Goods and Services by Selected Countries - BOP Basis ($billion)

                Country     2016     2017     2018
0                Brazil   21.555   28.479   30.063
1                Canada    7.406     2.76    1.211
2                 China -308.363 -335.704 -378.734
3                France  -12.515  -13.816    -13.3
4               Germany  -67.072  -66.728  -66.782
5             Hong Kong   28.867    35.06   34.255
6                 India  -29.641  -27.361  -24.294
7                 Italy  -31.196  -34.739  -35.274
8                 Japan  -56.398  -56.588  -57.999
9          Korea, South  -16.612   -9.297   -5.323
10               Mexico  -62.372  -68.745  -78.465
11         Saudi Arabia    9.183    5.292   -3.443
12            Singapore   18.383   20.303   18.087
13               Taiwan   -8.794  -14.396  -13.269
14       United Kingdom   15.066   15.576   19.572
15  All other countries   -9.499  -32.373  -48.411
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)
Quarterly Balance in Goods and Services by Selected Countries - BOP Basis ($billion)

                Country  2017Q3  2017Q4  2018Q1  2018Q2  2018Q3   2018Q4
0                Brazil   7.417   8.192   8.035   7.624   6.975    7.429
1                Canada     2.8   0.439   4.443  -1.274  -3.444    1.485
2                 China -83.153 -88.752 -93.944 -85.943 -96.283 -102.564
3                France  -3.048  -4.294  -3.937  -2.825  -3.499   -3.039
4               Germany -16.544 -17.072 -17.879 -16.131 -16.447  -16.326
5             Hong Kong   8.484   8.307  10.198   8.332   7.661    8.064
6                 India  -6.791   -7.04  -6.152  -6.637  -5.752   -5.753
7                 Italy  -8.664  -9.931  -9.013  -8.224  -8.618   -9.419
8                 Japan -14.698 -14.067 -15.011 -15.298 -13.589  -14.101
9          Korea, South  -2.684  -2.821    -0.3   -1.12  -2.066   -1.837
10               Mexico -16.226  -17.77 -18.026 -17.468 -21.554  -21.418
11         Saudi Arabia   2.844   1.186   0.919   0.069  -1.715   -2.717
12            Singapore   4.781    5.33   4.209    5.07   3.983    4.825
13               Taiwan  -4.239   -3.58  -4.097  -3.264  -1.992   -3.916
14       United Kingdom   3.682   4.295   5.849   5.119   3.743     4.86
15  All other countries  -7.565 -10.377 -14.877 -11.054 -10.826  -11.655

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)
      Year     Imports     Exports     Balance      GDP
4861  1985    3.861700    3.855700   -0.006000   4339.0
4862  1986    4.771000    3.106300   -1.664700   4579.6
4863  1987    6.293600    3.497300   -2.796300   4855.2
4864  1988    8.510900    5.021600   -3.489300   5236.4
4865  1989   11.989700    5.755400   -6.234300   5641.6
4866  1990   15.237400    4.806400  -10.431000   5963.1
4867  1991   18.969200    6.278200  -12.691000   6158.1
4868  1992   25.727500    7.418500  -18.309000   6520.3
4869  1993   31.539900    8.762900  -22.777000   6858.6
4870  1994   38.786800    9.281700  -29.505100   7287.2
4871  1995   45.543200   11.753700  -33.789500   7639.7
4872  1996   51.512800   11.992600  -39.520200   8073.1
4873  1997   62.557700   12.862200  -49.695500   8577.6
4874  1998   71.168600   14.241200  -56.927400   9062.8
4875  1999   81.788200   13.111100  -68.677100   9630.7
4876  2000  100.018200   16.185200  -83.833000  10252.3
4877  2001  102.278400   19.182300  -83.096100  10581.8
4878  2002  125.192600   22.127700 -103.064900  10936.4
4879  2003  152.436097   28.367943 -124.068154  11458.2
4880  2004  196.682034   34.427772 -162.254261  12213.7
4881  2005  243.470105   41.192010 -202.278095  13036.6
4882  2006  287.774353   53.673008 -234.101344  13814.6
4883  2007  321.442867   62.936892 -258.505975  14451.9
4884  2008  337.772628   69.732838 -268.039790  14712.8
4885  2009  296.373883   69.496679 -226.877205  14448.9
4886  2010  364.952634   91.911081 -273.041553  14992.1
4887  2011  399.371233  104.121524 -295.249709  15542.6
4888  2012  425.619083  110.516616 -315.102467  16197.0
4889  2013  440.430020  121.746189 -318.683831  16784.9
4890  2014  468.474895  123.657203 -344.817691  17521.7
4891  2015  483.201655  115.873365 -367.328290  18219.3
4892  2016  462.542005  115.545508 -346.996497  18707.2
4893  2017  505.469954  129.893587 -375.576368  19485.4
4894  2018  539.503428  120.341426 -419.162002  20500.6

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.