Python と AI を活用し CSV から経営報告書へ自動化する手法

**

# Moving Beyond Analysis By Hand

Every analyst has done this by hand. A CSV lands in your inbox, someone asks "so how did we do," and you spend an afternoon cleaning columns, building a few charts, and typing up what they mean.

We can automate most of that. In this walkthrough, we build a small pipeline in Python that takes a raw sales CSV, cleans it, runs the numbers, draws the charts, and asks an AI to draft the insights. The AI here is Claude Opus 4.8**. The model writes the first draft of the narrative in seconds. We still decide what is true.

Before any of that, the report needs a question. Ours is: how much revenue did we keep over these five weeks, and where did the rest go? Every step below answers a piece of it. Cleaning decides which rows count as money. The aggregates say where and when we lost it. The AI step turns those numbers into a summary an executive will read.

All the code is below so you can reproduce it, and the steps are the same for almost any dataset:

CSV → clean → explore → chart → AI insights → recommendations → report

# The Data

**

We use the product_sales.csv file, which contains 45 transaction rows. It is a dataset used in this interview question**. Keep in mind that in this article we are not solving the original problem. Each row is one payment event: a purchase or a refund, with a country, a date, an amount, and a status.

Here is the raw table preview.

Two things already stand out. Refunds are stored as negative amounts, and not every row is a completed sale. Both matter for the numbers we report.

We load it with Pandas:

import pandas as pd

df = pd.read_csv("product_sales.csv")

# Cleaning the Data

**

The cleaning step decides whether the totals are right. Three rows are pending or failed, so they are not money yet. We fix the types and keep only completed transactions:

df["transaction_date"] = pd.to_datetime(df["transaction_date"])
df["amount"] = pd.to_numeric(df["amount"], errors="coerce")

# Pending and failed transactions are not revenue yet.
settled = df[df["status"] == "completed"].copy()
settled["is_refund"] = settled["type"].eq("refund")

That drops 3 of 45 rows and leaves 42 completed transactions. If we had reported straight off the raw file, we would have counted a failed payment as a sale.

# Exploratory Analysis

The first half of the question: how much did we keep? Separate purchases from refunds and the headline numbers fall out. Refunds are already negative, so net revenue is just the sum of the amount column.

gross = settled.loc[~settled["is_refund"], "amount"].sum()
refunds = settled.loc[settled["is_refund"], "amount"].sum()   # negative
net = settled["amount"].sum()
refund_rate = -refunds / gross

print(f"gross   {gross:,.0f}")
print(f"refunds {refunds:,.0f}")
print(f"net     {net:,.0f}")
print(f"refund rate (value) {refund_rate:.0%}")

Output:

gross   12,975
refunds -4,875
net     8,100
refund rate (value) 38%

That is the whole story in four lines. We sold about $13,000 and gave back $4,875, so net revenue is $8,100. A 38% refund rate is high, and it is the kind of number that never shows up if you only sum positive amounts.

That gives the total. The second half of the question is where the money went, so we cut the data two ways. By country, to see which markets carry the net figure:

by_country = (settled.groupby("country")["amount"]
             .agg(net_revenue="sum", transactions="count")
             .sort_values("net_revenue", ascending=False))
print(by_country)

Canada is the surprise. Two completed orders, both refunded, so its net revenue is exactly zero.

Then by week, splitting purchases from refunds:

settled["week"] = settled["transaction_date"].dt.to_period("W").dt.start_time
weekly = settled.pivot_table(index="week", columns="is_refund",
                             values="amount", aggfunc="sum").fillna(0)
weekly.columns = ["purchases", "refunds"]
weekly["net"] = weekly.sum(axis=1)
print(weekly)

The first three weeks are net positive. The last three are net negative. Purchases stop in early May while refunds keep coming.

One more number explains the gap. Using original_transaction_id, we measure how long after a purchase each refund arrives.

purch_dates = (settled.loc[~settled["is_refund"], ["transaction_id", "transaction_date"]]
               .set_index("transaction_id")["transaction_date"])
ref = settled[settled["is_refund"]].copy()
ref["lag_days"] = (ref["transaction_date"]
                   - ref["original_transaction_id"].map(purch_dates)).dt.days
print(ref["lag_days"].median())    # 20.0

The median refund lands 20 days after the sale. April's revenue is still being refunded in May.

# Building the Charts

We draw three charts with Matplotlib** and save them as PNG files.

import matplotlib.pyplot as plt

weekly[["purchases", "refunds"]].plot(kind="bar", color=["#2a9d8f", "#e76f51"])
plt.axhline(0, color="black", linewidth=0.8)
plt.title("Weekly gross purchases vs refunds")
plt.tight_layout(); plt.savefig("chart_weekly.png")

The weekly chart makes the pattern obvious: tall green bars in April, then the refund bars take over in May.

**

settled.groupby("transaction_date")["amount"].sum().sort_index().cumsum().plot()
plt.title("Cumulative net revenue over time")
plt.tight_layout(); plt.savefig("chart_cumulative.png")

by_country["net_revenue"].plot(kind="barh", color="#2a9d8f")
plt.title("Net revenue by country")
plt.tight_layout(); plt.savefig("chart_country.png")

# Generating AI Insights

Now we hand the numbers to the model. We build a short text summary of everything we found and print a prompt. You paste that prompt into Claude and paste the reply back into the notebook.

weekly_net = {d.date().isoformat(): round(v) for d, v in weekly["net"].items()}

summary = f"""Product sales, {settled['transaction_date'].min().date()} to {settled['transaction_date'].max().date()}.
Gross: ${gross:,.0f}  Refunds: ${-refunds:,.0f}  Net: ${net:,.0f}
Refund rate by value: {refund_rate:.0%}
Net revenue by country: {by_country['net_revenue'].round(0).to_dict()}
Weekly net: {weekly_net}
Median days from purchase to refund: 20"""

prompt = (
    "You are a data analyst writing for executives. "
    "Based on this summary, write 3 insights and 3 business "
    "recommendations. Be specific and cautious about small sample size.\n\n"
    + summary
)

print(prompt)

Output:

You are a data analyst writing for executives. Based on this summary, write 3 insights and 3 business recommendations. Be specific and cautious about small sample size.

Product sales, 2025-04-15 to 2025-05-22.
    Gross: $12,975  Refunds: $4,875  Net: $8,100
    Refund rate by value: 38%
    Net revenue by country: {'US': 7200.0, 'GB': 450.0, 'MX': 450.0, 'CA': 0.0}
    Weekly net: {'2025-04-14': 4200, '2025-04-21': 3975, '2025-04-28': 3600, '2025-05-05': -1350, '2025-05-12': -1425, '2025-05-19': -900}
    Median days from purchase to refund: 20

The model only sees the summary numbers. It reasons over clean aggregates, and no row-level data leaves your machine. Here is what Claude Opus 4.8 returned:

This is where a human has to stay in the loop. The model read the summary well, but it does not know that this is one product across five weeks and 42 rows. The caution about sample size is right, and we would not take any of these numbers to a board meeting without more history.

# Assembling the Executive Report

The last step assembles a self-contained report.html file with the headline metrics as cards, the three charts, and the AI text. The full builder is here.

Here is a snapshot of the report:

If you want to see the full report, download this HTML file and open it in your browser.

# Conclusion

The pipeline is short: clean the data, compute a few honest aggregates, draw three charts, and let the model draft the narrative. The cleaning step and the aggregates decide whether the report is right. The AI saves the hour you would spend writing it up.

Claude Opus 4.8 wrote clear, cautious insights from the summary, and it correctly flagged the small sample. It cannot verify the data or know the business context, so the recommendations are a first draft we edit. Run the companion script on your own CSV, change the column names, and you have a reporting tool you can point at the next file that lands in your inbox.

Nate Rosidi** is a data scientist and in product strategy. He's also an adjunct professor teaching analytics, and is the founder of StrataScratch, a platform helping data scientists prepare for their interviews with real interview questions from top companies. Nate writes on the latest trends in the career market, gives interview advice, shares data science projects, and covers everything SQL.