Tables with pandas
Lesson 3 of 9
3Tables with pandas
The big pictureBetween a raw file and a model sit four operations that recur in every project: read, group, join, and decide about what is missing. Each decision changes what the model learns.
What the picture shows: Four raw rows go through groupby: they are split by city, the orders column is summed inside each group, and three cities come out. Riyadh appeared twice and became one row totalling 180.
Map of this part
The concepts in order, click any one to jump to it:
Two ways into the same idea
pandas gives you a table like Excel but inside code: named columns, rows, and you can ask "what are the average sales per city?" in one line. Do not delete empty cells right away: first ask why they are empty, because deleting them may delete an important story.
A DataFrame is Series columns sharing an index. groupby then agg aggregate, and merge joins two tables on a key like a JOIN. Missing NaN values are found with isna() and handled by dropping or fillna depending on why they are missing, with a column recording that they were.
Before You Start
🧱Arrays
You know shape and dtype from the arrays lesson, and a frame is at heart a set of named array columns.
📄CSV files
You have seen a CSV file or a spreadsheet, and you know each row is a record and each column a field.
The Core Idea
This lesson walks through four operations in order: read a table and tell a frame from a series, group, join two tables, then decide what to do with what is missing.
1Frames and series
A frame is a table with an index and named columns, and each column is a series sharing that index. So every selection from a frame hands you back one of the two.
Look at the picture: one column name in brackets returns a series with one dimension.
This small difference causes the strangest beginner errors, because a series and a frame do not accept the same operations.
2Grouping: split, apply, combine
Any question shaped like "how much per city" is a groupby, with no exceptions. It splits the rows by a column's value, applies a function to each group, then combines the results.
import pandas as pd
rows = [
{"city": "الرياض", "orders": 120, "revenue": 4800.0},
{"city": "جدة", "orders": 90, "revenue": 3150.0},
{"city": "الرياض", "orders": 60, "revenue": 2100.0},
{"city": "الدمام", "orders": 40, "revenue": None},
]
frame = pd.DataFrame(rows)
print(frame.shape)
print(list(frame.columns))
print(int(frame["orders"].sum()))
print(int(frame["revenue"].isna().sum()))
by_city = frame.groupby("city")["orders"].sum().sort_values(ascending=False)
for city, total in by_city.items():
print(f"{city}: {int(total)}")
Four lines gave you the shape, the columns, a total and a missing count, then groupby ranked the cities. Note that Riyadh appeared twice in the raw rows and became one row totalling 180.
3A join decides who survives
A join links two tables by a key, here the city name. Dammam is in the revenue table but not in the population table.
Look at the picture: a left join keeps all three rows and leaves an empty cell for Dammam. An inner join drops it silently.
import pandas as pd
frame = pd.DataFrame({
"city": ["الرياض", "جدة", "الدمام"],
"revenue": [6900.0, 3150.0, None],
})
population = pd.DataFrame({
"city": ["الرياض", "جدة", "أبها"],
"people": [7_000_000, 3_700_000, 1_100_000],
})
inner = frame.merge(population, on="city", how="inner")
left = frame.merge(population, on="city", how="left")
print(inner.shape, left.shape)
print(int(left["people"].isna().sum()))
filled = frame.copy()
filled["revenue"] = filled["revenue"].fillna(0.0)
print(float(frame["revenue"].mean()))
print(float(filled["revenue"].mean()))
The inner join returned two rows; the left join returned three with one empty cell. That difference is how a project loses twenty percent of its data with no warning. And look at the mean: 5025.0 before filling and 3350.0 after filling with zero, because zero is not a neutral value but a number that pulls the average toward itself.
4Missing values: drop or fill
NaN is not zero and not an empty string. Look at the picture: the mean revenue from the example above, before and after filling the gap with zero.
The decision depends on why the value is missing, not how many are missing. If the gaps are not random, dropping them biases your data on its own.
Ask first: why is this cell empty? Then choose dropping, the median, or a value from similar rows, and write down the reason.
How It Works, Step by Step: how groupby answers "how much per city"
It reads the key column, here city, and finds its distinct values.
It splits the rows into groups, so the two Riyadh rows go together and Jeddah and Dammam each get their own.
It applies the requested function, such as sum, to the chosen column inside each group on its own.
It combines the results into one series indexed by city name, which you can then rank with sort_values.
Worked example
Try it without code
Myth vs Reality
| Myth | Reality |
|---|---|
| A missing value can be treated as zero. | NaN is not zero. Filling with zero dropped the revenue mean in the lesson from 5025.0 to 3350.0, because zero is a number that pulls the average toward itself. |
| A merge that raised no error did what you wanted. | An inner join silently drops every row with no match, so silence does not mean success. Compare row counts before and after the merge. |
| Dropping rows with missing values is the neutral choice. | If the missingness is not random, dropping introduces bias of its own, because you remove a whole kind of row rather than random ones. |
Try it yourself
You have a sales table with a city column and a population table with the same column, but some city names are spelled one way and some another. You merged them with how="inner" on city. What is the likely result?
A merge compares strings character by character and has no idea two spellings mean one city. An inner join drops what does not match without a message, so you lose rows while believing the operation succeeded. Normalise the key column before merging, and print the row count before and after as a fixed habit.
Quick review cards
groupby really doeshow="inner"Check Your Understanding
why does mean() change before and after filling in the example above?
Because mean() ignores NaN entirely and divides by the count of present values, so it averaged two numbers. After filling with zero the third value joins the division and the mean drops. The lesson is that filling is never neutral: every fill is a hypothesis you add to your data.
What does frame["orders"] return, and what does frame[["orders"]] return?
The first is a series because you picked one column; the second is a frame with one column because you passed a list of columns.
Resources for this lesson
Grouped by type. Start with the official docs for precision, or an article or video for the explanation.
Official docs2
Done with this lesson?
Sign in to save your progress.