Python and AI: Revolutionizing CSV Analysis into Executive Reports

Transforming Manual Analysis into Intelligent Automation

Every analyst has faced this situation: a CSV file lands in your inbox, accompanied by a question about performance. You find yourself spending hours cleaning columns, generating graphs, and writing conclusions. However, thanks to technological advancements, it is now possible to automate much of this process. This article guides you in creating a pipeline in Python that takes a raw sales CSV file, cleans it, performs the necessary calculations, generates graphs, and uses AI, specifically Claude Opus 4.8, to draft insights. This AI model is capable of producing a first draft of the narrative in seconds, although the final fact-checking remains in human hands.

Before diving into the analysis, it is crucial to define a central question for the report. In our case, we seek to understand how much revenue was retained over a five-week period and where the rest was lost. Each step of the process contributes to answering this question. Data cleaning identifies which transactions count as revenue, aggregates reveal where and when losses occurred, and AI transforms these results into a readable summary for decision-makers.

The complete code is provided to allow for the reproduction of this process, and the described steps apply to almost any similar dataset:

• CSV → cleaning → exploration → graph → AI insights → recommendations → report

In this example, we use the product_sales.csv file, which contains 45 transaction lines. This file is often used in interview contexts to illustrate analytical questions. Each line represents a payment event, whether a purchase or a refund, with details such as country, date, amount, and transaction status.

Data Cleaning

Data cleaning is a crucial step that ensures the accuracy of totals. Among the initial 45 lines, three are marked as pending or failed, meaning they cannot be considered revenue. We proceed to correct data types and keep only completed transactions:

import pandas as pd
df = pd.read_csv("product_sales.csv")
df["transaction_date"] = pd.to_datetime(df["transaction_date"])
df["amount"] = pd.to_numeric(df["amount"], errors="coerce")

Pending and failed transactions are excluded from revenue calculations.

settled = df[df["status"] == "completed"].copy()
settled["is_refund"] = settled["type"].eq("refund")

This eliminates 3 out of 45 lines, leaving 42 completed transactions. Had we used the raw file, we would have falsely counted a failed payment as a sale.

Exploratory Analysis

The first part of our question focuses on the amount retained. By separating purchases from refunds, key figures emerge. Since refunds are already recorded as negatives, net revenue is simply the sum of the amounts.

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%}")

Refund Rate (Value): 38%

In summary, we sold approximately $13,000 and refunded $4,875, resulting in a net revenue of $8,100. A refund rate of 38% is notably high, which would not be evident if one simply summed the positive amounts.

To understand where the money was lost, we analyze the data by country and by week. By country, we identify the markets contributing to 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 stands out surprisingly with two completed orders, both refunded, meaning its net revenue is zero.

Next, we analyze the data by week, distinguishing 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)

The first three weeks show a positive net balance, while the last three are negative. Purchases cease in early May, but refunds continue.

An additional figure sheds light on this gap. Using original_transaction_id, we measure the delay between a purchase and its refund:

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 occurs 20 days after the sale, indicating that revenues from April continue to be refunded in May.

Creating Graphs

We use Matplotlib to generate three graphs, saved 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 graph clearly shows the pattern: large green bars in April, followed by refund bars 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

We now pass the figures to the AI model. A textual summary of the findings is constructed and presented as a prompt. You paste this prompt into Claude and insert the response into the notebook.

weekly_net = {d.date().isoformat(): round(v) for d, v in weekly["net"].items()}
summary = f"""Product Sales, from {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 Between Purchase and Refund: 20"""

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

The model only sees the summarized figures. It reasons based on clean aggregates, and no row-level data leaves your machine. Here’s what Claude Opus 4.8 returned:

This is where human involvement must remain. The model read the summary well, but it does not know that this is a single product over five weeks and 42 lines. Caution regarding the sample size is warranted, and we would not take any of these figures to a board meeting without more historical context.

Assembling the Executive Report

The final step assembles a standalone report.html file with key indicators presented as cards, the three graphs, and the AI text. The complete builder is here.

Here’s a preview of the report:

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

The pipeline is short: clean the data, calculate some honest aggregates, draw three graphs, and let the model draft the narrative. The cleaning step and the aggregates determine whether the report is correct. The AI saves you the hour you would have spent writing it.

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

Nate Rosidi is a data scientist and product strategy expert. He is also an adjunct professor teaching analytics and is the founder of StrataScratch, a platform helping data scientists prepare for interviews with real interview questions from major companies. Nate writes about the latest trends in the job market, offers interview tips, shares data science projects, and covers everything related to SQL.