The Project | About Us | Contribute | Donations | License
HOME My courses Completed
◄ Previous
◄

Pandas for Data Manipulation

Next ►
►

NumPy gave you speed, but in exchange for anonymous numbers: row 2 "was" Don Quixote only because you remembered it. pandas wraps those arrays in two named structures — the Series and the DataFrame — and adds everything a real analysis needs: reading CSV in one line, proper dates, cleaning of dirty data and, above all, groupby. This is the lesson where we finally answer, with data, the questions Ana has been carrying since the end of M10: which day of the week sells the most, which title wins on the web, and which one gets browsed a lot but bought little.

Contents

  1. Series and DataFrame: anatomy
  2. read_csv: M6's DictReader on steroids
  3. Inspection: head, info, describe, shape
  4. Selection: columns, loc/iloc and boolean masks
  5. New columns and dates with .dt
  6. Cleaning: isna, fillna, dropna, duplicated
  7. groupby: answering Ana's questions
  8. sort_values and value_counts
  9. merge: joining with the catalog
  10. to_csv: saving results

Series and DataFrame: anatomy

pip install pandas

A Series is a NumPy array with labels (an index); a DataFrame is a table: several Series sharing an index, one per column.

flowchart LR
    subgraph DF["DataFrame df"]
        direction LR
        I["index<br>0<br>1<br>2"] --- C1["date<br>2026-01-02<br>2026-01-02<br>2026-01-03"] --- C2["title<br>The Odyssey<br>Hamlet<br>Don Quixote"] --- C3["amount<br>12.50<br>19.90<br>15.71"]
    end
    C3 -.->|"each column is<br>a Series<br>(an ndarray + index)"| S["df['amount']"]
import pandas as pd    # universal alias, like np

s = pd.Series([12.50, 9.95, 15.90, 21.00],
              index=["The Odyssey", "Hamlet", "Don Quixote", "Faust"])
print(s["Faust"])      # 21.0  -> access by label, like a dict (M4)
print(s.mean())        # 14.8375 -> vectorized maths, like NumPy (11-02)

The Series is the perfect hybrid between M4's dict (access by key) and 11-02's ndarray (vectorization). The DataFrame, between a list of dicts (the DictReader rows) and a 2D matrix.

read_csv: M6's DictReader on steroids

In M6 you read sales.csv with csv.DictReader, converting types by hand row by row. The promise was that pandas would do that "on steroids". Here's the payoff:

df = pd.read_csv("data/sales_2026.csv", parse_dates=["date"])

One line and everything's done: the file opened and closed (M8's with is included), the header detected, units as an integer, amount as a float and — thanks to parse_dates — date as a real date, not text. What in M6 was fifteen lines of loop and conversions.

csv.DictReader (M6) pd.read_csv
Iterates row by row (lazy, minimal memory) Loads everything into memory at once
Everything is str: you convert Infers types; parse_dates for dates
One question = one loop One question = one expression

M8's honesty still stands: if the file doesn't fit in memory, the generator pipeline wins. For Papyrus's 487 rows (and for almost anything that fits in RAM, which nowadays is a lot), pandas is the tool.

Inspection: the first thing after loading any data

print(df.shape)     # (487, 5)  -> 487 rows, 5 columns
print(df.head(3))
        date        title  units  amount channel
0 2026-01-02  The Odyssey      1   12.50   store
1 2026-01-02       Hamlet      2   19.90     web
2 2026-01-03  Don Quixote      1   15.71   store
df.info()      # types and nulls per column: date datetime64[ns], units int64...
print(df["amount"].describe())
count    487.000000
mean      15.030000
std        8.420000
min        9.830000
25%        9.950000
50%       12.500000
75%       15.900000
max       63.600000

describe is 11-01's statistics in a single call — and it confirms what we saw there: the mean (15.03) above the median (12.50), with a maximum of 63.60 EUR (a Sant Jordi bundle) pulling upwards.

Selection: columns, loc/iloc and masks

df["title"]                # one column -> Series
df[["title", "amount"]]    # several columns -> DataFrame (note: a list inside the brackets)

For rows there are two accessors, and confusing them is the classic stumble:

loc iloc
Selects by index label integer position
df.loc[3] / df.iloc[3] The row whose label is 3 The fourth row, wherever it is
Range [0:3] Includes the end Excludes the end (like lists)
Typical use df.loc[mask, "amount"] "give me the first 10 rows"

With the default index (0, 1, 2...) they look identical; the moment you filter or reorder, they stop being so — the index keeps the original labels.

And the direct inheritance from 11-02: boolean masks work identically, with &, |, ~ and their parentheses:

web_sales = df[df["channel"] == "web"]
big_april = df[(df["amount"] > 30) & (df["date"].dt.month == 4)]
print(len(web_sales), len(big_april))    # 195 22

New columns and dates with .dt

Creating a column is assigning to a new name; the expression vectorizes on its own:

df["unit_amount"] = (df["amount"] / df["units"]).round(2)

# Was it charged at the member tariff? (the four canonical M3 values)
df["is_member"] = df["unit_amount"].isin([12.35, 9.83, 15.71, 20.75])
print(df["is_member"].sum())    # 141 member sales

datetime64 columns have the .dt accessor with everything we did in 11-01 with date.fromisoformat by hand:

df["month"] = df["date"].dt.month
df["weekday"] = df["date"].dt.dayofweek    # 0=Monday ... 6=Sunday
days = ["Mon", "Tue", "Wed", "Thu", "Fri", "Sat", "Sun"]
df["day_name"] = df["weekday"].map(dict(enumerate(days)))

Cleaning: the real world arrives dirty

The module's file is curated, but the raw dump Ana consolidates every night — sales_2026_raw.csv, 495 rows — brings M7's usual suspects: rows duplicated by a double click on the web and sales with an empty amount due to a save failure.

raw = pd.read_csv("data/sales_2026_raw.csv", parse_dates=["date"])
print(raw.shape)                     # (495, 5)
print(raw.duplicated().sum())        # 3  -> EXACTLY repeated rows
print(raw["amount"].isna().sum())    # 5  -> gaps (NaN) in amount

NaN (Not a Number) is the "missing data" marker. The options, applied with judgement:

clean = raw.drop_duplicates()               # 495 -> 492 rows
clean = clean.dropna(subset=["amount"])     # 492 -> 487 rows
print(clean.shape)                          # (487, 5)  -> our sales_2026.csv

What about fillna? Filling in makes sense when you know the correct value (raw["channel"].fillna("store") if you know the failure only happens at the till) or a defensible neutral one. Making up an average amount for a specific sale, on the other hand, contaminates the totals: here dropping is more honest than imputing. In M7 these corrupt rows raised exceptions; now they get statistical treatment. Always document how many rows you drop and why.

groupby: answering Ana's questions

groupby splits the DataFrame into groups, applies an aggregation to each one and reassembles the result. It's the M4 "accumulator dict" pattern and the Counter in a single call. Time for the questions from the end of M10.

What sells the most?

print(df.groupby("title")["units"].sum().sort_values(ascending=False))
title
Don Quixote    182
The Odyssey    154
Hamlet         121
Faust           63
Name: units, dtype: int64

Which day of the week sells the most? — question 2 of the cliffhanger, answered:

print(df.groupby("day_name")["units"].sum().reindex(days))
Mon     48
Tue     52
Wed     55
Thu     60
Fri     78
Sat    130
Sun     97

Saturday, with 130 units — almost triple Monday. The weekend (Sat+Sun) concentrates 44% of the units: Marta now knows on which days nobody can miss a shift at the store. (The reindex(days) forces chronological order; without it, pandas sorts alphabetically.)

Which title wins on each channel? — group by two columns and pivot for comfortable reading:

table = df.groupby(["channel", "title"])["units"].sum().unstack()
print(table)
title    Don Quixote  Faust  Hamlet  The Odyssey
channel
store            121     38      74           79
web               61     25      47           75

In the store, Don Quixote rules (121); on the web, surprise: The Odyssey (75) beats Don Quixote (61). Ana's hypothesis: on the web, Julia's book club crowd carries more weight. Whatever the cause, the Flask home page showcase now knows which cover to feature.

What gets browsed but not bought? — question 1 of the cliffhanger. Sales alone aren't enough (we foresaw this in 11-01): you need the visits to /book/<title> from the M10 logs:

visits = pd.Series({"Don Quixote": 260, "The Odyssey": 220,
                    "Faust": 210, "Hamlet": 150}, name="web_visits")
web_purchases = df[df["channel"] == "web"].groupby("title")["units"].sum()
print((web_purchases / visits * 100).round(1).sort_values())
Faust          11.9
Don Quixote    23.5
Hamlet         31.3
The Odyssey    34.1
dtype: float64

There it is: Faust gets looked at a lot (210 visits, third in the catalog) but only 11.9% of the visits end in a purchase — a third of everyone else's conversion. With its 21.00 EUR price, the price hypothesis suggests itself. A fact, not an opinion: exactly what Ana was asking for.

sort_values and value_counts

df.sort_values("amount", ascending=False).head(3)     # the 3 biggest sales
df.sort_values(["date", "amount"])                     # multi-key, like M3's sorted(key=...)

print(df["title"].value_counts())
title
Don Quixote    168
The Odyssey    145
Hamlet         116
Faust           58
Name: count, dtype: int64

value_counts is literally M4's Counter on steroids: it counts transactions (rows), already sorted. Mind the nuance versus the earlier groupby: 168 Don Quixote sales add up to 182 units — counting rows and summing units are different questions.

merge: joining with the catalog

Sales don't know the author or the list price. The M5/M6 catalog does:

catalog = pd.DataFrame({
    "title":  ["The Odyssey", "Hamlet", "Don Quixote", "Faust"],
    "author": ["Homer", "Shakespeare", "Cervantes", "Goethe"],
    "price":  [12.50, 9.95, 15.90, 21.00],
})
catalog["member_price"] = (catalog["price"] * 1.04 * 0.95).round(2)
# the canonical M3 formula -> [12.35, 9.83, 15.71, 20.75]

full = df.merge(catalog, on="title", how="left")
print(full.groupby("author")["amount"].sum().round(2).sort_values(ascending=False))
author
Cervantes      2881.98
Homer          1918.90
Goethe         1319.20
Shakespeare    1199.53
Name: amount, dtype: float64

merge pairs rows by the shared column (on="title"), like the JOIN of the databases Django handled for you in M10. how="left" keeps every sale even if some title were missing from the catalog (it would be left with NaN — and isna() would rat it out: cleaning and merging keep an eye on each other).

to_csv: closing the circle

summary = df.groupby("title")["units"].sum().sort_values(ascending=False)
summary.to_csv("reports/units_by_title.csv")

The M6 reports/ directory now receives files born from one line, not from a loop. Tip: on DataFrames with a meaningless numeric index, to_csv(..., index=False) avoids a ghost Unnamed: 0 column when reading back.

Common Mistakes and Tips

  • SettingWithCopyWarning. Appears when chaining df[df["channel"]=="web"]["amount"] = ...: you're writing to a copy and the change is lost. Correct form: df.loc[df["channel"]=="web", "amount"] = ... — a single indexing with loc.
  • Forgetting parse_dates and grouping by text. "2026-04-23" > "2026-04-03" works by alphabetical luck, but .dt.dayofweek doesn't exist on strings. If df["date"].dtype is object, go back to the read_csv.
  • loc vs iloc after filtering. After web_sales = df[...], web_sales.iloc[0] is its first row, but web_sales.loc[0] can raise KeyError if label 0 got filtered out. If you don't need the old index: reset_index(drop=True).
  • Contagious, silent NaN. Any operation with NaN yields NaN, and aggregations skip it without warning (mean() of a column with gaps uses only the values present). Run df.isna().sum() right after loading, always.
  • Comparing with == NaN. df["amount"] == np.nan is always False (NaN isn't even equal to itself). Use isna() / notna().
  • Confusing value_counts (rows) with groupby(...).sum() (quantities). 168 transactions ≠ 182 units. Choose based on the question.

Exercises

  1. Compute the average basket (mean amount per transaction) for each channel in sales_2026.csv. Does it confirm the hypothesis from exercise 2 of 11-01 about why store and web differ? Add the mean of units per channel to reason it out.
  2. Which month brought in the most revenue (amount column)? And if you exclude the sales of Sant Jordi week (20 to 26 April)? Use .dt.month and a mask with dates (pd.Timestamp).
  3. With merge and the catalog: create the column discount_applied = price * units - amount (how much Papyrus left uncharged on each sale relative to the list price) and compute the total accumulated discount per title. Which title does the member tariff "cost" the most money?

Solutions

  1. print(df.groupby("channel")["amount"].mean().round(2))
# store    15.89
# web      13.74
print(df.groupby("channel")["units"].mean().round(2))
# store    1.07
# web      1.07
    The web basket is smaller, but units per transaction are almost identical: the difference is not that people buy more copies in the store, it's the mix of titles — the web sells proportionally more Odyssey and Hamlet (cheap) and the store more Quixote. The 11-01 hypothesis gets corrected with data: that's what the cycle was for.
  2. print(df.groupby(df["date"].dt.month)["amount"].sum().round(2).idxmax())   # 4
outside_sj = ~df["date"].between("2026-04-20", "2026-04-26")
print(df[outside_sj].groupby(df["date"].dt.month)["amount"].sum().idxmax())  # 5
    April wins with Sant Jordi week included; without it, April deflates and May wins. Business conclusion: the "good April" is really one good week — the stock order should be concentrated there, not spread across the month. (idxmax is 11-02's argmax, but returning the label.)
  3. full = df.merge(catalog[["title", "price"]], on="title", how="left")
full["discount_applied"] = (full["price"] * full["units"]
                            - full["amount"]).round(2)
print(full.groupby("title")["discount_applied"].sum().round(2)
          .sort_values(ascending=False))
    The biggest accumulated total belongs to Don Quixote: it's not the most discounted per unit, but it's the one members buy the most — volume rules. A fine point: the per-unit "discount" turns out surprisingly small (0.19 EUR on Don Quixote, not the 5% you'd expect) because the member tariff includes VAT and the list price doesn't; spotting that oddity by looking at the data — and asking why — is exactly the reflex this lesson wanted to train in you.

Conclusion

pandas has turned Ana's questions into answers: Saturday is the king of the week (130 units), the web has its own bestseller (The Odyssey, 75 versus Don Quixote's 61), and Faust is the catalog's window-shopping champion — 210 visits and only 11.9% conversion, with its price as the prime suspect. Along the way: Series and DataFrames as arrays with names, read_csv settling M6's promise, loc/iloc, masks inherited from 11-02, honest cleaning (495 → 487 rows, documented), groupby/value_counts as M4's accumulators on steroids, and merge joining sales with the catalog. But these answers are tables of numbers, and Ana isn't going to read tables: she wants to see the Sant Jordi peak, compare bars at a glance, hang a dashboard in the back room. Turning numbers into visual stories — and not lying while doing it — is Matplotlib, the next lesson.

◄ Previous
◄
Next ►
►

Python Programming Course

Module 1: Introduction to Python

Module 2: Control Structures

Module 3: Functions and Modules

Module 4: Data Structures

Module 5: Object-Oriented Programming

Module 6: File Handling

Module 7: Error and Exception Handling

Module 8: Advanced Topics

Module 9: Testing and Debugging

Module 10: Web Development with Python

Module 11: Data Science with Python

Module 12: Final Project

The Project | About Us | Contribute | Donations | License
© Copyright 2026. All rights reserved