Unit 12 Project: Analyze a Small CSV

Ask, clean, calculate, chart, explain

Advanced 18 min

In this lesson

Everything in this unit, in one pass over one file. The dataset is a month of expenses, and the question is the obvious one: where is the money going?

The point of this project is the order. Load, look, clean, calculate, chart, explain. Skipping straight to the chart is the single most common way a data project goes wrong.

Explain it like I’m 5

A data project is a small investigation: ask a question, tidy the evidence, do the sums, draw the picture, then say what you found.

Start with the question, not the data

“Have a look at this data” is not a task you can finish, because nothing tells you when to stop. “Which category did I spend most on last month, and how big is the gap?” is answerable, and it tells you exactly which columns you need.

Write the question down before opening the file. It decides everything that follows, and it stops you producing five charts that each answer something nobody asked.

The whole pipeline

Six steps, in order, each one a line or two. Every technique here comes from this unit: a DataFrame to hold the table, a grouped summary to answer the question, and Matplotlib to draw it. The only new thing is putting them in sequence.

Example · analyze_spending.py 
import io
import pandas as pd
import matplotlib.pyplot as plt

CSV = """date,category,amount
2026-01-03,groceries,42.10
2026-01-05,transport,12.00
2026-01-09,groceries,38.75
2026-01-11,eating out,26.40
2026-01-15,transport,
2026-01-18,groceries,51.20
2026-01-22,eating out,31.90
2026-01-27,bills,88.00
"""

# 1. Load
spend = pd.read_csv(io.StringIO(CSV))

# 2. Inspect - before calculating anything
print("rows:", len(spend), "columns:", list(spend.columns))
print("missing amounts:", int(spend["amount"].isna().sum()))

# 3. Clean - one gap, filled with the median
spend["amount"] = spend["amount"].fillna(spend["amount"].median())

# 4. Summarize
by_category = spend.groupby("category")["amount"].sum().sort_values(ascending=False)
print()
print(by_category)

# 5. Chart
plt.figure(figsize=(6, 3.5))
by_category.plot(kind="bar", color="#2b7cd3")
plt.title("Spending by category")
plt.ylabel("total spent")
plt.show()

# 6. Conclude
print()
print("biggest category:", by_category.index[0])
print("total spent:", round(float(by_category.sum()), 2))
Output 
rows: 8 columns: ['date', 'category', 'amount']
missing amounts: 1

category
groceries     132.05
bills          88.00
eating out     58.30
transport      50.75
Name: amount, dtype: float64

biggest category: groceries
total spent: 329.1
Load, inspect, clean, summarize, chart, conclude.

Writing the conclusion

The code is not the deliverable. Nobody but you will read it. The deliverable is a few sentences someone can act on, and it should say three things: what you found, what you did to the data, and what it does not tell you.

For this dataset that is roughly:

Across January, groceries were the largest category at $132.05, about 40% of the $329.10 total and half again as much as bills, the next biggest. One transport entry had no amount and was filled with the median of the other amounts ($38.75), which inflates the transport figure. The true total is likely lower. Eight transactions in one month is a small sample, so this shows where the money went in January, not a reliable monthly pattern.

Note how much of that is caveat. The finding is one sentence; the honesty is two. That ratio is normal, and it is what distinguishes analysis from a chart with a confident caption.

Do the summarizing step yourself on a clean version of the expenses. Group the rows by category, total the amounts, and sort so the biggest is first. Then report which category is biggest and what percentage of the total it represents.

import io
import pandas as pd

CSV = """date,category,amount
2026-01-03,groceries,42.10
2026-01-05,transport,12.00
2026-01-09,groceries,38.75
2026-01-11,eating out,26.40
2026-01-18,groceries,51.20
2026-01-27,bills,88.00
"""

spend = pd.read_csv(io.StringIO(CSV))

# TODO: total the amount per category, biggest first
by_category = spend["amount"]

print(by_category)
print()
print("biggest:", by_category.index[0])
print("share of total:", round(100 * by_category.iloc[0] / by_category.sum()), "%")

Take it further

Three extensions, each using something from this unit on data you actually care about:

  • Use your own data. Export a bank statement or a spreadsheet as CSV and run the same six steps. Real data will be messier than this one, which is the point.
  • Add a time dimension. Convert the date column with pd.to_datetime() and plot spending per week as a line chart, since dates genuinely run in order.
  • Check the average is not lying. Draw a histogram of the individual amounts. If they split into a cluster of small transactions and a few large ones, the mean describes neither group.

Common mistake: Charting before understanding the columns

Why it happens:

The chart is the fun part and the visible output.

How to fix it:

Print head(), shape, and isna().sum() first. A chart of misread data is confidently wrong.

Common mistake: Not checking for missing values before averaging

Why it happens:

mean() skips them silently and returns a perfectly normal-looking number.

How to fix it:

Count them first, then decide to drop or fill, and say which you did.

Common mistake: Reporting numbers with no explanation

Why it happens:

The number feels like the answer, because producing it was the work.

How to fix it:

Write what it means, what you changed, and what it cannot tell you. That is the actual deliverable.

Common mistake: Hiding the cleaning decisions

Why it happens:

Filling a gap feels like housekeeping rather than a finding.

How to fix it:

Any value you invented can change the conclusion. State it, as the worked example does for the filled transport amount.

What is the first step in a data project?

What should you check before calculating averages?

Why write a conclusion in plain English?

Mini exercise (hard)

A scoreboard: six players, two teams, points each. Produce a per-team summary showing the total, mean and count of points, then report the top individual scorer and which team won overall.

Take the wheel. Complete the code, hit Run, and check your output right here.

import io
import pandas as pd

CSV = """name,team,points
Ada,red,18
Sam,blue,12
Rae,red,25
Kit,blue,7
Joe,red,14
Mia,blue,21
"""

scores = pd.read_csv(io.StringIO(CSV))

# TODO: per team, the sum, mean and count of points (round the mean to 1 dp,
#       and reset_index so team becomes a normal column)
by_team = scores

top = scores            # TODO: the single highest-scoring player

print(by_team)
print()
print("top scorer:", top["name"], "with", top["points"])
print("winning team:", by_team.loc[by_team["sum"].idxmax(), "team"])

What to learn next

You ran a complete analysis in order: question first, then load, inspect, clean, summarize, chart, and explain, then wrote a conclusion that stated the finding, admitted the filled-in value that inflated one category, and said what eight rows of data cannot tell you. That last part is what separates analysis from a chart with a confident caption.

That completes Unit 12. You can now describe what happened. The obvious next question is whether you can predict what happens next, which is Unit 13, where the same pandas tables become training data for your first machine-learning models. When you’re ready, keep building. For a longer build on messier data, From Messy Export to One-Page Report takes a real export all the way to a chart and a written conclusion.