Session 7 lab: Trends, returns and a decision dashboard¶

# Course data setup. Run this cell first; you do not need to understand it yet.
# It downloads the Superstore sample from Tableau's website, checks it is the
# exact version this course was written for, and prepares the order-lines table.
import hashlib
import os
import urllib.request

import matplotlib.pyplot as plt
import pandas as pd

SOURCE_URL = "https://public.tableau.com/app/sample-data/sample_-_superstore.xls"
EXPECTED_SHA256 = "5045ed4fec49cae2cc0dfc5ab0d204ba09efa767fe00c996822499e8cc42fbbd"
SOURCE_FILE = os.environ.get("COURSE_XLS_PATH", "sample_-_superstore.xls")

try:
    import xlrd  # noqa: F401  (needed to read .xls files)
except ImportError:
    import subprocess
    import sys

    subprocess.check_call([sys.executable, "-m", "pip", "install", "-q", "xlrd"])

if not os.path.exists(SOURCE_FILE):
    urllib.request.urlretrieve(SOURCE_URL, SOURCE_FILE)

with open(SOURCE_FILE, "rb") as f:
    digest = hashlib.sha256(f.read()).hexdigest()
if digest != EXPECTED_SHA256:
    print("WARNING: Tableau has changed this sample file since the course was written.")
    print("Your numbers will differ from the lesson. Ask your facilitator for the course copy.")


def load_orders():
    """Return one row per order line, orders placed 2023-01-01 to 2025-12-31."""
    raw = pd.read_excel(SOURCE_FILE, sheet_name="Orders")
    returns = pd.read_excel(SOURCE_FILE, sheet_name="Returns")

    # Orders with more than one Customer ID (a known data issue), found before filtering.
    ids_per_order = raw.groupby("Order ID")["Customer ID"].nunique()
    conflict_orders = set(ids_per_order[ids_per_order > 1].index)

    in_scope = (raw["Order Date"] >= "2023-01-01") & (raw["Order Date"] < "2026-01-01")
    df = raw.loc[in_scope].copy()

    df = df.rename(columns={
        "Row ID": "row_id", "Order ID": "order_id", "Order Date": "order_date",
        "Ship Date": "ship_date", "Ship Mode": "ship_mode", "Customer ID": "customer_id",
        "Segment": "segment", "Country/Region": "country_region", "Region": "region",
        "Category": "category", "Sub-Category": "sub_category", "Product ID": "product_id",
        "Product Name": "product_name", "Sales": "sales", "Quantity": "quantity",
        "Discount": "discount", "Profit": "profit",
    })
    df["returned_order_flag"] = df["order_id"].isin(set(returns["Order ID"])).astype(int)
    df["customer_order_conflict"] = df["order_id"].isin(conflict_orders).astype(int)
    df["customer_analysis_eligible"] = 1 - df["customer_order_conflict"]
    df["order_to_ship_days"] = (df["ship_date"] - df["order_date"]).dt.days
    df["order_month"] = df["order_date"].dt.to_period("M").dt.to_timestamp()

    columns = [
        "row_id", "order_id", "order_date", "ship_date", "ship_mode", "customer_id",
        "segment", "country_region", "region", "category", "sub_category", "product_id",
        "product_name", "sales", "quantity", "discount", "profit", "returned_order_flag",
        "customer_analysis_eligible", "customer_order_conflict", "order_to_ship_days",
        "order_month",
    ]
    return df[columns].sort_values("row_id").reset_index(drop=True)


pd.set_option("display.float_format", "{:,.2f}".format)
plt.rcParams.update({
    "figure.figsize": (8, 4),
    "axes.spines.top": False,
    "axes.spines.right": False,
    "axes.grid": True,
    "axes.axisbelow": True,
    "grid.color": "#e6e5e1",
    "axes.prop_cycle": plt.cycler(color=["#2a78d6", "#eb6834", "#1baf7a"]),
    "axes.titleweight": "bold",
    "axes.titlelocation": "left",
})
print("Setup complete.")

Setup complete.

df = load_orders()
print("Rows (order lines):", len(df))
print("Different orders:  ", df["order_id"].nunique())

Rows (order lines): 6815
Different orders:   3388

monthly = df.groupby("order_month")["sales"].sum()
monthly.head(6)

order_month
2023-01-01   14,518.06
2023-02-01    4,519.89
2023-03-01   56,933.91
2023-04-01   28,295.35
2023-05-01   26,319.77
2023-06-01   34,669.48
Name: sales, dtype: float64

all_months = pd.date_range("2023-01-01", "2025-12-01", freq="MS")
missing = all_months.difference(monthly.index)

print("Months expected:", len(all_months))
print("Months with Sales:", len(monthly))
print("Missing months:", len(missing))

Months expected: 36
Months with Sales: 36
Missing months: 0

monthly = monthly.reindex(all_months)

monthly.plot(title="Monthly Sales, orders placed 2023–2025")
plt.xlabel("Order month")
plt.ylabel("Sales (source monetary units)")
plt.show()

monthly_by_segment = df.groupby(["order_month", "segment"])["sales"].sum().unstack()

monthly_by_segment.plot(title="Monthly Sales by segment, 2023–2025")
plt.xlabel("Order month")
plt.ylabel("Sales (source monetary units)")
plt.legend(title="Segment")
plt.show()

by_year = pd.DataFrame({
    "year": monthly.index.year,
    "month": monthly.index.month,
    "sales": monthly.values,
})
seasons = by_year.pivot(index="month", columns="year", values="sales")

seasons.plot(title="Monthly Sales, one line per year")
plt.xlabel("Month of the year (1 = January)")
plt.ylabel("Sales (source monetary units)")
plt.xticks(range(1, 13))
plt.legend(title="Year")
plt.show()

for year, sales in monthly.groupby(monthly.index.year):
    highest = ", ".join(sales.nlargest(3).index.strftime("%b"))
    lowest = ", ".join(sales.nsmallest(2).index.strftime("%b"))
    print(year, "| highest:", highest, "| lowest:", lowest)

2023 | highest: Sep, Nov, Dec | lowest: Feb, Jan
2024 | highest: Nov, Dec, Sep | lowest: Feb, Jan
2025 | highest: Dec, Nov, Sep | lowest: Jan, Feb

growth = pd.DataFrame({"sales": monthly})
growth["previous_month"] = growth["sales"].shift(1)
growth["mom_growth"] = growth["sales"].pct_change()
growth["same_month_last_year"] = growth["sales"].shift(12)
growth["yoy_growth"] = growth["sales"].pct_change(periods=12)

# A comparison month with zero Sales gives no fair growth figure: treat it as blank.
growth["mom_growth"] = growth["mom_growth"].where(growth["previous_month"] != 0)
growth["yoy_growth"] = growth["yoy_growth"].where(growth["same_month_last_year"] != 0)

def as_pct(value):
    if pd.isna(value):
        return "no comparison"
    return f"{value:+.1%}"


def growth_table(table, months):
    rows = table.loc[months]
    return pd.DataFrame({
        "Sales": rows["sales"].map("{:,.0f}".format),
        "MoM growth": rows["mom_growth"].map(as_pct),
        "YoY growth": rows["yoy_growth"].map(as_pct),
    }, index=rows.index.strftime("%b %Y"))


growth_table(growth, ["2023-01-01", "2023-02-01", "2023-03-01",
                      "2024-01-01", "2025-01-01", "2025-12-01"])

growth[["mom_growth", "yoy_growth"]].isna().groupby(growth.index.year).sum()

fig, (ax_mom, ax_yoy) = plt.subplots(2, 1, figsize=(9, 6), sharex=True)

ax_mom.bar(growth.index, growth["mom_growth"] * 100, width=20)
ax_mom.set_title("Month-on-month growth swings with the seasons")
ax_mom.set_ylabel("MoM growth (%)")

ax_yoy.bar(growth.index, growth["yoy_growth"] * 100, width=20, color="#eb6834")
ax_yoy.set_title("Year-on-year growth compares like with like (2023 has no comparison)")
ax_yoy.set_ylabel("YoY growth (%)")
ax_yoy.set_xlabel("Order month")

fig.tight_layout()
plt.show()

# 1. Filter first, then compute growth
only_2025 = df[df["order_date"].dt.year == 2025]
monthly_2025 = only_2025.groupby("order_month")["sales"].sum()
filter_first = pd.DataFrame({
    "mom_growth": monthly_2025.pct_change(),
    "yoy_growth": monthly_2025.pct_change(periods=12),
})

# 2. Compute growth on all months first, then keep 2025
compute_first = growth.loc["2025", ["mom_growth", "yoy_growth"]]

check_months = ["2025-01-01", "2025-02-01"]
pd.DataFrame({
    "MoM, filter first": filter_first.loc[check_months, "mom_growth"].map(as_pct),
    "MoM, compute first": compute_first.loc[check_months, "mom_growth"].map(as_pct),
    "YoY, filter first": filter_first.loc[check_months, "yoy_growth"].map(as_pct),
    "YoY, compute first": compute_first.loc[check_months, "yoy_growth"].map(as_pct),
}, index=pd.DatetimeIndex(check_months).strftime("%b %Y"))

returned = df[df["returned_order_flag"] == 1]

returns_by_segment = pd.DataFrame({
    "orders": df.groupby("segment")["order_id"].nunique(),
    "orders_on_returns_list": returned.groupby("segment")["order_id"].nunique(),
})
returns_by_segment.loc["All segments"] = [
    df["order_id"].nunique(),
    returned["order_id"].nunique(),
]
returns_by_segment["share_on_list"] = (
    returns_by_segment["orders_on_returns_list"] / returns_by_segment["orders"]
).map("{:.1%}".format)
returns_by_segment

def draw_dashboard(data, scope):
    # 1. Segment KPIs
    kpis = pd.DataFrame({
        "sales": data.groupby("segment")["sales"].sum(),
        "orders": data.groupby("segment")["order_id"].nunique(),
        "listed": data[data["returned_order_flag"] == 1].groupby("segment")["order_id"].nunique(),
    }).fillna(0).astype({"listed": int}).sort_values("sales")
    kpis["share_on_list"] = kpis["listed"] / kpis["orders"]

    # 2. Complete monthly series (0 where the selection has no lines)
    trend = data.groupby("order_month")["sales"].sum().reindex(all_months, fill_value=0)

    # 3. YoY growth on the full series; zero comparison month means no comparison
    yoy = trend.pct_change(periods=12).where(trend.shift(12) != 0)

    fig, (ax1, ax2, ax3) = plt.subplots(3, 1, figsize=(9, 11))

    ax1.barh(kpis.index, kpis["sales"])
    for i, share in enumerate(kpis["share_on_list"]):
        ax1.text(kpis["sales"].iloc[i], i, f"  {share:.1%} of orders on returns list", va="center")
    ax1.set_title(f"{kpis.index[-1]} has the most Sales; returns-list shares are close")
    ax1.set_xlabel("Sales (source monetary units)")

    ax2.plot(trend.index, trend.values)
    ax2.set_title("Sales peak towards the end of each year and dip every January–February")
    ax2.set_xlabel("Order month")
    ax2.set_ylabel("Sales (source monetary units)")

    ax3.bar(yoy.index, yoy * 100, width=20, color="#1baf7a")
    ax3.axhline(0, color="black", linewidth=0.8)
    ax3.set_title("Compared with the same month a year earlier (2023 has no comparison)")
    ax3.set_xlabel("Order month")
    ax3.set_ylabel("YoY growth (%)")

    fig.suptitle(f"Retail dashboard: {scope}", fontweight="bold", x=0.02, ha="left")
    fig.text(0.02, 0.005, "Scope: orders placed Jan 2023–Dec 2025. Sales in source monetary units. "
             "Fictitious sample data. Returns list = membership only, no refund amounts.",
             fontsize=8, color="#555555")
    fig.tight_layout(rect=[0, 0.02, 1, 1])
    plt.show()
    kpis["share_on_list"] = (kpis["share_on_list"] * 100).round(1)
    return kpis.rename(columns={"share_on_list": "share_on_list_%"})


draw_dashboard(df, "all regions")

# 1. Change "West" to another region: "East", "Central" or "South".
# 2. Run the cell. Read every title: is each message still true for this region?
# 3. If a title is no longer true, write down what it should say instead.
region = "West"
draw_dashboard(df[df["region"] == region], f"{region} region only")

# U.S. Census Bureau, Construction Spending (value put in place), as mirrored in
# Tableau's sample-data catalog. The file is read straight into memory; nothing is saved to disk.
import hashlib
import io
import urllib.request

CONS_URL = ("https://public.tableau.com/app/sample-data/"
            "enigma-us.gov_.census.eits_.vip-97d8678900cbb89fee543a41aa21ab9a_2_0.csv")
CONS_SHA256 = "6cf9ad9f20c8eeb2c5b33d9cc29b8c648579a52ff50a24d3f6f5f1ba332b8b34"

cons_bytes = urllib.request.urlopen(CONS_URL).read()
if hashlib.sha256(cons_bytes).hexdigest() != CONS_SHA256:
    print("WARNING: this file has changed since the course was written. Tell your facilitator.")
cons_raw = pd.read_csv(io.BytesIO(cons_bytes))
print("Rows, columns:", cons_raw.shape)
cons_raw.head(3)

Rows, columns: (50208, 20)

print("Category codes:", cons_raw["cat_code"].nunique())
print("Units:", cons_raw["dt_unit"].unique())
print("Rows with no value:", cons_raw["val"].isna().sum(), "of", len(cons_raw))
cons_raw[cons_raw["cat_code"].isin(["XXXX", "AXXXX"])].groupby(
    ["cat_code", "cat_desc", "is_adj", "dt_unit"])["val"].count()

Category codes: 38
Units: ['MLN$' 'PCT' nan]
Rows with no value: 33216 of 50208





cat_code  cat_desc                            is_adj  dt_unit
AXXXX     Annual Rate for Total Construction  1       MLN$       531
XXXX      Total Construction                  0       MLN$       531
                                              1       PCT          0
Name: val, dtype: int64

selected = cons_raw[
    (cons_raw["cat_code"] == "XXXX")
    & (cons_raw["dt_code"] == "T")
    & (cons_raw["et_idx"] == 0)
    & (cons_raw["geo_code"] == "US")
    & (cons_raw["is_adj"] == 0)
].copy()
selected["month"] = pd.to_datetime(selected["per_name"])

print("Rows:", len(selected), "| distinct months:", selected["month"].nunique())
print("Units:", selected["dt_unit"].unique(), "| blank values:", selected["val"].isna().sum())

spend = selected.set_index("month")["val"].sort_index()
calendar = pd.date_range(spend.index.min(), spend.index.max(), freq="MS")
print("First month:", spend.index.min().date(), "| last month:", spend.index.max().date(),
      "| missing months:", len(calendar.difference(spend.index)))
spend = spend.reindex(calendar)

Rows: 177 | distinct months: 177
Units: ['MLN$'] | blank values: 0
First month: 2002-01-01 | last month: 2016-09-01 | missing months: 0

# 1. Run the cell and describe the long-run trend in one sentence. When did it turn?
# 2. In the overlay, which months are high and which are low every year?
# 3. Change the two highlighted years, for example to 2008 and 2009. What changed?
highlight = {2015: "#2a78d6", 2016: "#eb6834"}

fig, (ax1, ax2) = plt.subplots(1, 2, figsize=(11, 3.8))
ax1.plot(spend.index, spend / 1000, color="#2a78d6")
ax1.set_title("US construction spending, monthly")
ax1.set_ylabel("Billions of US dollars (not adjusted)")

table = pd.DataFrame({"year": spend.index.year, "month": spend.index.month, "value": spend.values / 1000})
by_year = table.pivot(index="month", columns="year", values="value")
for year in by_year.columns:
    if year not in highlight:
        ax2.plot(by_year.index, by_year[year], color="#d4d2cc", linewidth=1)
for year, colour in highlight.items():
    ax2.plot(by_year.index, by_year[year], color=colour, linewidth=2.5, label=str(year))
ax2.set_title("Same months, every year (grey = other years)")
ax2.set_xlabel("Month of the year")
ax2.set_xticks(range(1, 13))
ax2.legend()
fig.tight_layout()
plt.show()

# 1. Run the cell. In which years was YoY growth negative for most months?
# 2. Read the year-to-date table: which months does it cover, and why not the full year?
# 3. Change last_month to 6 (January–June). Does the ranking of 2015 vs 2016 change?
yoy = spend.pct_change(periods=12, fill_method=None)

fig, ax = plt.subplots()
ax.bar(yoy.index, yoy * 100, width=25, color=["#2a78d6" if v >= 0 else "#eb6834" for v in yoy.fillna(0)])
ax.axhline(0, color="black", linewidth=0.8)
ax.set_title("Year-on-year growth of monthly construction spending (2002 has no comparison)")
ax.set_xlabel("Month")
ax.set_ylabel("YoY growth (%)")
plt.show()

last_month = 9
ytd = spend[spend.index.month <= last_month]
ytd_by_year = ytd.groupby(ytd.index.year).agg(months="count", total="sum")
ytd_by_year["YoY"] = ytd_by_year["total"].pct_change().map("{:+.1%}".format)
ytd_by_year["total"] = (ytd_by_year["total"] / 1000).round(1)
ytd_by_year.rename(columns={"total": f"Jan–month {last_month} total ($bn)"}).tail(6)

# 1. Change "Consumer" to "Corporate" or "Home Office".
# 2. For each quarter, is the segment growing, flat or falling across the years?
# 3. Describe the segment's trend in three sentences.
segment = "Consumer"
one_segment = df[df["segment"] == segment]
quarterly = one_segment.groupby([one_segment["order_date"].dt.quarter,
                                 one_segment["order_date"].dt.year])["sales"].sum().unstack()
quarterly.index.name = "quarter"
quarterly

# 1. Change "Consumer" to another segment.
# 2. Change the months in the list to two or three months you want to inspect.
# 3. Is the MoM or the YoY figure the fairer one to report for each month? Why?
segment = "Consumer"
seg = df[df["segment"] == segment].groupby("order_month")["sales"].sum().reindex(all_months, fill_value=0)
seg_growth = pd.DataFrame({"sales": seg})
seg_growth["mom_growth"] = seg.pct_change().where(seg.shift(1) != 0)
seg_growth["yoy_growth"] = seg.pct_change(periods=12).where(seg.shift(12) != 0)
growth_table(seg_growth, ["2024-12-01", "2025-01-01", "2025-06-01"])

# 1. Run the cell. Which line shows the turning points most clearly?
# 2. Why do the trailing 12-month average and the adjusted line not match exactly in time?
# 3. Change window to 3. What happens to the seasonal wiggles?
saar = cons_raw[
    (cons_raw["cat_code"] == "AXXXX") & (cons_raw["dt_code"] == "T")
    & (cons_raw["et_idx"] == 0) & (cons_raw["geo_code"] == "US") & (cons_raw["is_adj"] == 1)
].assign(month=lambda d: pd.to_datetime(d["per_name"])).set_index("month")["val"].sort_index()
print("Adjusted annual-rate rows:", len(saar), "| same months as the actual series:",
      saar.index.equals(spend.index))

window = 12
ax = (spend / 1000).plot(color="#b8b6b0", label="Actual monthly (not adjusted)")
(saar / 12 / 1000).plot(ax=ax, color="#eb6834", linewidth=2, label="Adjusted annual rate ÷ 12")
(spend.rolling(window).mean() / 1000).plot(ax=ax, color="#2a78d6", linewidth=2,
                                           label=f"Trailing {window}-month average of actual")
ax.set_title("Three views of the same spending")
ax.set_xlabel("Month")
ax.set_ylabel("Billions of US dollars per month")
ax.legend(fontsize=8)
plt.show()

Adjusted annual-rate rows: 177 | same months as the actual series: True

january = growth.loc["2025-01-01"]
print("January 2025 Sales:            ", f"{january['sales']:,.0f}")
print("December 2024 Sales:           ", f"{january['previous_month']:,.0f}")
print("Month-on-month growth:         ", as_pct(january["mom_growth"]))
print("January 2024 Sales:            ", f"{january['same_month_last_year']:,.0f}")
print("Year-on-year growth:           ", as_pct(january["yoy_growth"]))
print("MoM after filtering to 2025:   ", as_pct(filter_first.loc["2025-01-01", "mom_growth"]))

January 2025 Sales:             18,830
December 2024 Sales:            75,533
Month-on-month growth:          -75.1%
January 2024 Sales:             18,462
Year-on-year growth:            +2.0%
MoM after filtering to 2025:    no comparison

# The colleague's code. It runs, but the comparison is unfair.
# 1. Find what is wrong with the 2016 figure.
# 2. Fix the comparison so every year covers the same months.
# 3. Rewrite the colleague's sentence so it is accurate, naming the months compared.
yearly = spend.groupby(spend.index.year).sum() / 1000
print("Yearly totals ($bn), the colleague's version:")
print(yearly.tail(6).round(1))

Yearly totals ($bn), the colleague's version:
2011     788.30
2012     850.50
2013     906.40
2014   1,005.60
2015   1,112.40
2016     863.20
Name: val, dtype: float64