CASS Freight Index¶

A look at US freight indexes, according to reports obtained from Cass Information Systems.

import pandas as pd
import altair as alt
from os import environ

if environ.get("GITHUB_WORKFLOW"):
    raise RuntimeError("Requires manual updates")

# https://www.cassinfo.com/freight-audit-payment/cass-transportation-indexes/cass-freight-index
try:
    df_report = pd.read_excel('https://www.cassinfo.com/hubfs/Cass%20Indexes%20Historical%20Data%202022.xlsx', sheet_name=None)
except Exception as e:
    print(e)
    # Fallback to IPFS archive (March 2021 data)
    print("Data from IPFS fallback")
    df_report = pd.read_excel("https://ipfs.io/ipfs/QmQ9UjYbZsCnTtbguzi6kovRccGVDL6LRfY2o3mENHLStZ", sheet_name=None)

if 'Freight Index-Shipments' in df_report.keys():
    df_freight = df_report['Freight Index-Shipments'].iloc[6:, 1:4].dropna()
    df_freight.columns = ['Date', 'Index', 'YoY']
    df_freight.iloc[:, 1:] = df_freight.iloc[:, 1:].applymap(pd.to_numeric)
elif 'CFI-Shipments' in df_report.keys():
    df_freight = df_report['CFI-Shipments'].iloc[3:, 1:4].dropna()
    df_freight.columns = ['Date', 'Index', 'YoY']
    df_freight.iloc[:, 1:] = df_freight.iloc[:, 1:].applymap(pd.to_numeric)

if 'TL Linehaul Index' in df_report.keys():
    df_truckload = df_report['TL Linehaul Index'].iloc[5:, 1:4].dropna()
    df_truckload.columns = ['Date', 'Index', 'YoY']
    df_truckload.iloc[:, 1:] = df_truckload.iloc[:, 1:].applymap(pd.to_numeric)
elif 'TL Cost Index' in df_report.keys():
    df_truckload = df_report['TL Cost Index'].iloc[5:, 1:4].dropna()
    df_truckload.columns = ['Date', 'Index', 'YoY']
    df_truckload.iloc[:, 1:] = df_truckload.iloc[:, 1:].applymap(pd.to_numeric)
elif 'TL LH Index' in df_report.keys():
    df_truckload = df_report['TL LH Index'].iloc[6:, 1:4].dropna()
    df_truckload.columns = ['Date', 'Index', 'YoY']
    df_truckload.iloc[:, 1:] = df_truckload.iloc[:, 1:].applymap(lambda v: pd.to_numeric(v, errors='coerce'))
    
if 'Freight Index-Expenditures' in df_report.keys():  
    df_intermodal = df_report['Freight Index-Expenditures'].iloc[6:, 1:4].dropna()
    df_intermodal.columns = ['Date', 'Index', 'YoY']
    df_intermodal.iloc[:, 1:] = df_intermodal.iloc[:, 1:].applymap(pd.to_numeric)

elif 'Intermodal Cost Index' in df_report.keys():
    df_intermodal = df_report['Intermodal Cost Index'].iloc[5:, 1:4].dropna()
    df_intermodal.columns = ['Date', 'Index', 'YoY']
    df_intermodal.iloc[:, 1:] = df_intermodal.iloc[:, 1:].applymap(pd.to_numeric)

monthNames = {
    1: 'Jan',
    2: 'Feb',
    3: 'Mar',
    4: 'Apr',
    5: 'May',
    6: 'Jun',
    7: 'Jul',
    8: 'Aug',
    9: 'Sep',
    10: 'Oct',
    11: 'Nov',
    12: 'Dec',
}

df_freight['Month'] = df_freight.Date.apply(pd.to_datetime).apply(lambda v: monthNames.get(v.month))
df_freight['Year'] = df_freight.Date.apply(pd.to_datetime).apply(lambda v: v.year)
fieldOrdering = df_freight.Date.apply(pd.to_datetime).apply(lambda v: v.month).values.tolist()
df_freight['order'] = df_freight.Date.apply(pd.to_datetime).apply(lambda v: v.month).map(lambda x: fieldOrdering.index(x))

melted_freight = df_freight[['Month', 'Year', 'order', 'Index']].melt(id_vars=['Month', 'Year', 'order'])
chosenYears = [2007, 2008, 2009, 2019, 2020, 2021, 2022]

alt.Chart(melted_freight[melted_freight.Year.isin(chosenYears)]).mark_line(point=True).encode(
    alt.X('Month:O', sort=None, axis=alt.Axis(title='', labelAngle=0)),
    alt.Y('value:Q', axis=alt.Axis(title='Freight Index')),
    alt.Color('Year:N', title='Year'),
    alt.Order('order:Q')
).properties(
    title='Comparison of Annual Freight Index Trends',
    height=450,
    width=800,
    background='white'
)

freight_yoy = melted_freight[['Month', 'Year', 'value']].copy()
freight_yoy['date'] = pd.to_datetime(freight_yoy['Month'].apply(str) + " " + freight_yoy['Year'].apply(str), format='%b %Y')

freight_yoy = freight_yoy.set_index('date')['value'].pct_change(12).reset_index()

c = alt.Chart(freight_yoy).mark_bar(color='indigo', width=1.8).encode(
    alt.X('date:T', axis=alt.Axis(title=None)),
    alt.Y('value:Q', axis=alt.Axis(title='Year of Year Growth [%]', format='%')),
    color=alt.condition(f"datum.value < 0",
        alt.value('orange'),
        alt.value('indigo')
    ),
    tooltip=[alt.Tooltip('date:T'), alt.Tooltip('value:Q', title='% Change', format=',.02%')]
).properties(
    title='CASS Freight Index',
    height=450,
    width=800,
    background='white'
)

c.save('transportation-cass.png')
c.display()

df_truckload['Date'] = df_truckload.Date.apply(lambda v: pd.to_datetime(v.replace("*", ""), errors='coerce') if isinstance(v, str) else v)

alt.Chart(df_truckload).mark_bar(color='blue', width=3).encode(
    alt.X('Date:T', axis=alt.Axis(title=None)),
    alt.Y('YoY:Q', axis=alt.Axis(title='Year-over-year Index Growth [%]', format='%')),
    color=alt.condition(f"datum.YoY < 0",
        alt.value('darkorange'),
        alt.value('blue')
    ),
    tooltip=[alt.Tooltip('Date:T'), alt.Tooltip('YoY:Q', title='% Change', format=',.2%')]
).properties(
    title='CASS per-mile Truckload Pricing Index',
    height=450,
    width=800,
    background='white'
)

df_intermodal['Date'] = df_intermodal.Date.apply(lambda v: pd.to_datetime(v.replace("*", ""), errors='coerce') if isinstance(v, str) else v)

alt.Chart(df_intermodal).mark_bar(color='black', width=2).encode(
    alt.X('Date:T', axis=alt.Axis(title=None)),
    alt.Y('YoY:Q', axis=alt.Axis(title='Year-over-year Index Change [%]', format='%')),
    color=alt.condition(f"datum.YoY < 0",
        alt.value('orange'),
        alt.value('black')
    ),
    tooltip=[alt.Tooltip('Date:T'), alt.Tooltip('YoY:Q', title='% Change', format=',.2%')]
).properties(
    title='CASS per-mile Intermodal Pricing Index',
    height=450,
    width=800,
    background='white'
)