Case Study 1: CSV Sales Report Automation

Scenario

You receive a raw sales export every day or week in CSV format. The data contains transaction details for various products sold by the business. Your task is to automate the reporting process by loading the CSV file, calculating sales metrics, and generating a visual summary of business performance.

Suggested Libraries

• pandas
• matplotlib

Sample Files You May Create

• sales_data.csv — the raw export
• sales_report.py — your solution script
• sales_chart.png — the generated chart

Requirements

• Load sales data from a CSV file.
• Validate that required columns exist.
• Calculate total sales revenue.
• Summarize sales by product.
• Summarize sales by time period.
• Generate a chart showing business performance.
• Output useful summary information for management review.

Have a genuine attempt at this before revealing the solution below. Think about what should happen if the CSV is missing a column, or if it's empty — should your script crash, or fail gracefully?

💡 Show Answer

import pandas as pd
import matplotlib.pyplot as plt
from pathlib import Path

def load_sales_data(csv_path):
    df = pd.read_csv(csv_path, parse_dates=["Date"])
    # Basic sanity checks - fail loudly rather than silently computing garbage
    required_cols = {"Date", "Product", "Quantity", "UnitPrice"}
    missing = required_cols - set(df.columns)
    if missing:
        raise ValueError(f"CSV is missing required columns: {missing}")
    df["TotalSale"] = df["Quantity"] * df["UnitPrice"]
    return df

def summarize_sales(df):
    total_sales = df["TotalSale"].sum()
    sales_by_product = df.groupby("Product")["TotalSale"].sum().sort_values(ascending=False)
    sales_by_month = (
        df.set_index("Date")["TotalSale"]
        .resample("ME")
        .sum()
    )
    return {
        "total_sales": total_sales,
        "sales_by_product": sales_by_product,
        "sales_by_month": sales_by_month,
    }

def generate_chart(summary, output_path="sales_chart.png"):
    fig, axes = plt.subplots(1, 2, figsize=(14, 5))

    summary["sales_by_product"].plot(kind="bar", ax=axes[0], color="steelblue")
    axes[0].set_title("Total Sales by Product")
    axes[0].set_ylabel("Sales")
    axes[0].tick_params(axis="x", rotation=45)

    summary["sales_by_month"].plot(kind="line", marker="o", ax=axes[1], color="darkorange")
    axes[1].set_title("Monthly Sales Trend")
    axes[1].set_ylabel("Sales")

    fig.suptitle(f"Total Sales: {summary['total_sales']:,.2f}", fontsize=14, fontweight="bold")
    fig.tight_layout()
    fig.savefig(output_path, dpi=150)
    plt.close(fig)
    return output_path

def run(csv_path):
    print("Loading CSV...")
    df = load_sales_data(csv_path)

    print("Summarizing data...")
    summary = summarize_sales(df)

    print("Generating chart...")
    chart_path = generate_chart(summary)

    print(f"Total sales: {summary['total_sales']:,.2f}")
    print(f"Chart saved to: {chart_path}")
    print("\nTop products:\n", summary["sales_by_product"].head())

if __name__ == "__main__":
    run("./sales_data.csv")

Notes:

• resample("ME") (month-end) needs a DatetimeIndex, which is why Date is set as the index before resampling.
• Wrap load_sales_data in a try/except at the call site in production so a malformed CSV doesn't crash a scheduled job silently — log the error and alert instead.
• If the source is genuinely an .xlsx file rather than .csv, swap pd.read_csv for pd.read_excel(path, engine="openpyxl") — everything downstream is identical.