Changing and summarising

Part of the free LogicWiz Python course, Module 10: NumPy and pandas. The examples and practice run in the app.

Meher has the week's orders in a DataFrame. Shankar bhaiya asks: "How much did each item earn? Which day was busiest? And why is Tuesday's samosa count blank?" Time to add columns, sort, fix missing values and group.

You'll learn to

• Add, rename and delete columns (and add a row)
• Sort with `sort_values`
• Find and handle missing values (`isna`, `fillna`, `dropna`)
• Summarise with `groupby` and `sum`/`mean`, and avoid chained assignment

Learn

Add, rename, delete

import pandas as pd

df = pd.DataFrame({"item": ["Chai", "Samosa", "Dosa"],
                   "qty": [30, 20, 8],
                   "price": [10, 15, 60]})
df["amount"] = df["qty"] * df["price"]        # a new column from others
df["veg"] = True                               # one value for every row
df = df.rename(columns={"qty": "plates"})
df = df.drop(columns=["veg"])
print(df)
df.loc[len(df)] = ["Lassi", 12, 40, 480]       # add a row at the end
print(df.shape)

rename and drop return a new DataFrame, so assign the result back (df = df.rename(...)).

Sorting

import pandas as pd

df = pd.DataFrame({"item": ["Chai", "Samosa", "Dosa", "Idli"],
                   "price": [10, 15, 60, 15],
                   "plates": [30, 20, 8, 12]})
print(df.sort_values("price"))
print(df.sort_values("plates", ascending=False)["item"].tolist())
print(df.sort_values(["price", "item"])["item"].tolist())   # tie broken by name

The index labels travel with their rows, which is why a sorted frame shows them out of order.

Missing values: NaN

Real data has gaps: someone forgot to enter Tuesday's samosas. pandas shows a gap as NaN ("not a number").

• df.isna() marks the gaps; df.isna().sum() counts them per column.
• df.fillna(0) or df.fillna({"qty": 0}) fills them.
• df.dropna() removes rows that have any gap.
• sum(), mean() and max() skip NaN automatically.

A NaN turns an integer column into floats, because NaN itself is a float.

Finding and fixing gaps

import pandas as pd
import numpy as np

df = pd.DataFrame({"name": ["Riya", "Kabir", "Meher"],
                   "marks": [82, np.nan, 77]})
print(df["marks"].isna().sum())
print(df["marks"].mean())           # NaN is skipped: (82 + 77) / 2
print(df.fillna({"marks": 0}))
print(df.dropna())

groupby: split, apply, combine

import pandas as pd

sales = pd.DataFrame({
    "day":    ["Mon", "Mon", "Tue", "Tue", "Tue"],
    "item":   ["Chai", "Dosa", "Chai", "Chai", "Dosa"],
    "amount": [300, 480, 250, 200, 600],
})
print(sales.groupby("item")["amount"].sum())
print(sales.groupby("day")["amount"].mean())
print(sales.groupby("item")["amount"].agg(["sum", "count"]))
print(sales.groupby("item")["amount"].sum().idxmax())

groupby("item") splits the rows into one group per item, ["amount"].sum() adds each group up, and the results are combined into a Series labelled by item. Groups come out sorted by key; pass sort=False to keep first-seen order.

Never trust chained assignment. df[df["item"] == "Chai"]["price"] = 12 first makes a filtered copy, then sets the price on that copy, which is thrown away. pandas 2 warns (SettingWithCopyWarning) and, depending on how the first step was done, may or may not change df; pandas 3 never does. Don't guess: do it in one .loc step: df.loc[df["item"] == "Chai", "price"] = 12.

Chained vs .loc

import pandas as pd

df = pd.DataFrame({"item": ["Chai", "Dosa"], "price": [10, 60]})
df[df["item"] == "Chai"]["price"] = 12        # chained: changes a temporary copy
print(df["price"].tolist())
df.loc[df["item"] == "Chai", "price"] = 12    # one .loc step: changes df
print(df["price"].tolist())

pandas also prints a warning for the first line (SettingWithCopyWarning in pandas 2). Read warnings; they are often bugs.

CBSE IP Class 12 asks you to add/delete rows and columns, rename, sort, and to use aggregate functions: max, min, sum, count, mean, median, mode, quantile. A typical practical: "Find the total sales per item", which is df.groupby("item")["amount"].sum(). Remember axis=1 in df.drop("col", axis=1) means a column.

In one line

Add columns with df["new"] = ...; rename, drop, sort_values, fillna, dropna return new frames (assign back); df.groupby("key")["col"].sum() summarises; change cells with one df.loc[mask, col] = ....

Interview corner

How does `groupby` work in pandas?

It follows split-apply-combine: rows are split into groups by key, a function such as sum, mean or count is applied to each group, and the results are combined into a Series or DataFrame indexed by the keys. Example: df.groupby("item")["amount"].sum(). Use .agg(["sum", "mean"]) for several at once and sort=False to keep first-seen order.

How do you handle missing values in pandas?

Detect them with isna()/isna().sum(), then either drop them with dropna() or fill them with fillna(value), a column mean (fillna(df.col.mean())), or forward fill (ffill()). Aggregations like sum and mean skip NaN by default. The right choice depends on what the gap means.

What is chained assignment and why is it a problem?

Writing df[mask]["col"] = value does two indexing steps: the first may return a copy, so the assignment can be lost. pandas 2 raises SettingWithCopyWarning because the result depends on whether a view or a copy came back; with copy-on-write (the default from pandas 3.0) it never modifies df. Use a single df.loc[mask, "col"] = value.