待翻译：Turn Any CSV into an Executive Report with Python and AI

AI 服务暂时不可用，以下为来源正文，待恢复后补全翻译。

--> Turn Any CSV into an Executive Report with Python and AI - KDnuggets --> Join Newsletter # 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. transaction_id product_id country transaction_date amount status type original_transaction_id TXN-10001 PROD-2891 US 2025-04-15 449.99 completed purchase TXN-10002 PROD-2891 US 2025-04-15 449.99 completed purchase TXN-10003 PROD-2891 CA 2025-04-15 449.99 completed purchase TXN-10004 PROD-2891 US 2025-04-17 449.99 completed purchase … … … … … … … … TXN-10045 PROD-2891 US 2025-05-11 -449.99 completed refund TXN-10044 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) country net_revenue transactions US 7199.84 38 GB 449.99 1 MX 449.99 1 CA 0.00 2 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) week purchases refunds net 2025-04-14 4649.89 -449.99 4199.90 2025-04-21 4274.90 -299.99 3974.91 2025-04-28 3599.92 0.00 3599.92 2025-05-05 449.99 -1799.96 -1349.97 2025-05-12 0.00 -1424.96 -1424.96 2025-05-19 0.00 -899.98 -899.98 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. Our Top 5 Free Course Recommendations --> Latest Posts Turn Any CSV into an Executive Report with Python and AI Honest Abacus AI Review: ChatLLM, DeepAgent, AI Studio & More I Replaced Pip, Virtualenv, and Poetry With uv: Here’s Why 7 Approaches to Reduce Inference Latency in Your LLM Workflows Does MiniMax Agent Actually Make Work Easier? A Guide to Saving Token Usage with Multi-Agent AI Top Posts 5 Books That Will Deepen Your Understanding of Large Language Models 7 Machine Learning Algorithms That Still Matter I Replaced Pip, Virtualenv, and Poetry With uv: Here’s Why A Beginner’s Guide to Working with Claude Design 7 Best Claude Code Alternatives for CLI Agentic Coding Kaggle + Google’s Free 5-Day Agentic AI Course Does MiniMax Agent Actually Make Work Easier? SQL vs Pandas vs AI Agents: Which Solves Analytics Problems Best? 5 Best AI Tools for Data Analysis You Should Try in 2026 7 Steps to Building and Deploying Your First Autonomous Agent Published on August 5, 2026 by Nate Rosidi No, thanks!