US Macroeconomic Data Explorer | Auto Sector

Auto Sector

A look at the auto sector, according to the Supplemental Estimates for Motor Vehicles from the U.S. Bureau of Economic Analysis.

In [1]:
import pandas as pd
import altair as alt

from io import BytesIO
from urllib.request import urlopen, Request
from urllib.error import HTTPError

uaString = 'Mozilla/5.0 (Macintosh; Intel Mac OS X 10_13_6) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/80.0.3987.122 Safari/537.36'

try:
    # Download from BEA (if available)
    req = Request("https://apps.bea.gov/national/xls/gap_hist.xlsx", data=None, headers={ 'User-Agent': uaString })

    data = urlopen(req)
except HTTPError:
    # Fallback to IPFS archive (Nov 2021 data)
    req = Request("https://cloudflare-ipfs.com/ipfs/QmQhaiTkWc6Akx2qZ6yC1qafYDEGkQDWVSp47zmwdYH6vE", data=None, headers={ 'User-Agent': uaString })

    data = urlopen(req)
    
df_ts_raw = pd.read_excel(BytesIO(data.read()), engine='openpyxl', sheet_name=None) #[0].iloc[:-1]

clean = lambda s: str(s).replace(".", "").replace("  ", " ").replace("-", "")

def relabel(df):
    tmp = df.copy()
    tmp.columns = ['Month', 'Year'] + [clean(c) for c in df.iloc[2, :].values][2:]
    tmp = tmp.iloc[4:]
    tmp = tmp.dropna(how='all')
    tmp = tmp.dropna(how='all', axis=1)
    tmp[tmp.columns[2:]] = tmp[tmp.columns[2:]].applymap(pd.to_numeric)
    tmp = tmp[tmp['Year'] != ' ']
    tmp['dt'] = pd.to_datetime(tmp['Month'] + "-" + tmp['Year'].apply(int).apply(str), format="%B-%Y", errors='coerce')
    
    return tmp.iloc[:, 2:]

def doChart(df, metric = 'US Production', title=None, color='blue'):
    return alt.Chart(df).mark_line(color=color).encode(
        alt.X('dt:T', axis=alt.Axis(title='')),
        alt.Y(f'{metric}:Q')
    ).properties(
        title=f'Historical {title} [{metric}]' if title else f'Historical {metric}',
        width=750,
        height=400,
        background='white'
    )
    
def doYoyChart(df, metric = 'US Production', title=None, color='teal'):
    yoy = df[['dt', metric]].set_index('dt').dropna().pct_change(12).apply(lambda v: v * 100).reset_index()
    
    return alt.Chart(yoy[yoy[metric] < 350]).mark_bar(width=1, color=color).encode(
        alt.X('dt:T', axis=alt.Axis(title='')),
        alt.Y(f'{metric}:Q', axis=alt.Axis(title='Year-over-year Growth [%]')),
        color=alt.condition(f"datum['{metric}'] < 0",
            alt.value('tomato'),
            alt.value(color)
        ),
        tooltip=[alt.Tooltip('dt:T', title='Date', format='%b %Y'), alt.Tooltip(f'{metric}:Q', title='YoY Change [%]', format=',.02f')]
    ).properties(
        title=f'Change in {title} [{metric}]' if title else f'Change in {metric}',
        width=750,
        height=400,
        background='white'
    )
In [2]:
%%capture

df_domestic_autos = relabel(df_ts_raw['Table 1'])

#df_domestic_autos.tail()
In [3]:
doChart(df_domestic_autos, 'Not seasonally adjusted (Thousands)', 'Domestic Autos', 'navy')
Out[3]:
In [4]:
doYoyChart(df_domestic_autos[-180:], 'Not seasonally adjusted (Thousands)', 'Domestic Auto Sales', color='indigo')
Out[4]:
In [5]:
df_foreign_autos = relabel(df_ts_raw['Table 2'])

#df_foreign_autos.head()
In [6]:
doChart(df_foreign_autos, 'Not seasonally adjusted (Thousands)', 'Foreign Autos')
Out[6]:
In [7]:
doYoyChart(df_foreign_autos, 'Not seasonally adjusted (Thousands)', 'Foreign Autos')
Out[7]:
In [8]:
df_light_trucks = relabel(df_ts_raw['Table 3'])

#df_light_trucks.head()
In [9]:
doChart(df_light_trucks, 'Not seasonally adjusted (Thousands)', 'Light Trucks')
Out[9]:
In [10]:
doYoyChart(df_light_trucks, 'Not seasonally adjusted (Thousands)', 'Light Trucks')
Out[10]:
In [11]:
df_foreign_light_trucks = relabel(df_ts_raw['Table 4'])

#df_foreign_light_trucks.head()
In [12]:
doChart(df_foreign_light_trucks, 'Not seasonally adjusted (Thousands)', 'Foreign Light Trucks')
Out[12]:
In [13]:
doYoyChart(df_foreign_light_trucks, 'Not seasonally adjusted (Thousands)', 'Foreign Light Trucks')
Out[13]:
In [14]:
df_heavy_trucks = relabel(df_ts_raw['Table 5'])

#df_heavy_trucks.head()
In [15]:
doChart(df_heavy_trucks, 'Not seasonally adjusted (Thousands)', 'Heavy Trucks')
Out[15]:
In [16]:
doYoyChart(df_heavy_trucks, 'Not seasonally adjusted (Thousands)', 'Heavy Trucks')
Out[16]:
In [17]:
df_light_vehicle = relabel(df_ts_raw['Table 6'])

#df_light_vehicle.head()
In [18]:
doChart(df_light_vehicle, 'Autos  not seasonally adjusted (Thousands)', 'Autos')
Out[18]:
In [19]:
doYoyChart(df_light_vehicle, 'Autos  not seasonally adjusted (Thousands)', 'Autos')
Out[19]:
In [20]:
df_domestic_production = relabel(df_ts_raw['Table 7'])

#df_domestic_production.head()
In [21]:
doChart(df_domestic_production, 'Not seasonally adjusted (Thousands)', 'Domestic Production')
Out[21]:
In [22]:
doYoyChart(df_domestic_production, 'Not seasonally adjusted (Thousands)', 'Domestic Production')
Out[22]:
In [23]:
%%capture

na_imports = df_ts_raw['Table 8'] #.head(10)

na_imports = na_imports.dropna(how='all', axis=1)

cols = ["-".join(map(clean, c)) for c in zip(['Canada']*3 + ['Mexico']*3, list(na_imports.iloc[3, :].values)[2:])]

na_imports.columns = ['Month', 'Year'] + cols
na_imports = na_imports.iloc[5:]
na_imports = na_imports.dropna(how='all')

#print(na_imports.dtypes)

na_imports = na_imports.set_index(['Month', 'Year']).applymap(pd.to_numeric).reset_index()
na_imports = na_imports[na_imports['Year'] != ' ']
na_imports['dt'] = pd.to_datetime(na_imports['Month'] + "-" + na_imports['Year'].apply(int).apply(str), format="%B-%Y", errors='coerce')
na_imports = na_imports.iloc[:, 2:]

na_imports = na_imports.groupby('dt').last().reset_index()

#na_imports.tail(40)
In [24]:
doChart(na_imports, 'Canada-Not seasonally adjusted (Thousands)', 'Canada Imports')
Out[24]:
In [25]:
doChart(na_imports, 'Mexico-Not seasonally adjusted (Thousands)', 'Mexico Imports')
Out[25]:
In [26]:
doYoyChart(na_imports, 'Canada-Not seasonally adjusted (Thousands)', 'Canada Imports')
Out[26]:
In [27]:
doYoyChart(na_imports, 'Mexico-Not seasonally adjusted (Thousands)', 'Mexico Imports')
Out[27]:
In [28]:
na_exports = df_ts_raw['Table 9'] #.head(10)

na_exports = na_exports.dropna(how='all', axis=1)

na_exports.columns = ['Month', 'Year'] + [clean(c) for c in na_exports.iloc[3, :].values][2:]
na_exports = na_exports.iloc[5:]

na_exports = na_exports.dropna(how='all')
na_exports[na_exports.columns[2:]] = na_exports[na_exports.columns[2:]].applymap(pd.to_numeric)
na_exports = na_exports[na_exports['Year'] != ' ']
na_exports['dt'] = pd.to_datetime(na_exports['Month'] + "-" + na_exports['Year'].apply(int).apply(str), format="%B-%Y", errors='coerce')
na_exports = na_exports.iloc[:, 2:]


#na_exports.head()
In [29]:
doChart(na_exports, 'Not seasonally adjusted (Thousands)', 'Exports')
Out[29]:
In [30]:
doYoyChart(na_exports, 'Not seasonally adjusted (Thousands)', 'Exports')
Out[30]:
In [31]:
df_domestic = df_ts_raw['Table 10'] #.head()

df_domestic = df_domestic.dropna(how='all', axis=1)

df_domestic.columns = ['Month', 'Year'] + [str(c).replace(".", "") for c in df_domestic.iloc[4, :].values][2:]
df_domestic = df_domestic.iloc[7:]

df_domestic = df_domestic.dropna(how='all')
df_domestic[df_domestic.columns[2:]] = df_domestic[df_domestic.columns[2:]].applymap(pd.to_numeric)
df_domestic = df_domestic[df_domestic['Year'] != ' ']
df_domestic['dt'] = pd.to_datetime(df_domestic['Month'] + "-" + df_domestic['Year'].apply(int).apply(str), format="%B-%Y", errors='coerce')
df_domestic = df_domestic.iloc[:, 2:]


#df_domestic.head()
In [32]:
doChart(df_domestic, 'US Production')
Out[32]:
In [33]:
c = doYoyChart(df_domestic, 'US Production')
c.save('autos.png')
c.display()
In [34]:
doChart(df_domestic, 'Canadian Imports')
Out[34]:
In [35]:
doYoyChart(df_domestic, 'Canadian Imports')
Out[35]:
In [36]:
doChart(df_domestic, 'Mexican Imports')
Out[36]:
In [37]:
doYoyChart(df_domestic, 'Mexican Imports')
Out[37]:
© kdunn926