You are viewing a public preview
Sign in to save your progress, do assignments, join discussions and get feedback.
Now, in this period, we will use grouped summaries to answer simple business questions. Which region sold more units? Which product category contributed most? We will then study the relationship between quantity and unit price. This lesson combines the explanation and practice parts of Period 6. By the end, you should be able to build a summary table, check its totals, and write a careful conclusion. We will use one small sales example throughout the lesson so that every result is easy to follow.
By the end of this lesson you can:
groupby() and check that group totals add up.agg() and explain the difference between size and count.Key idea: Grouped summaries answer "which group sold more?", and correlation describes "do two numbers move together?". Neither explains why. Check totals and state limits.
The lecture uses six complete, valid transactions: regions North and South, product categories Tea and Coffee, quantity in units and unit price in thousand dong per unit. For example, T1 is two units of tea at 50 thousand dong each. With a real file, first check types, missing values, duplicate keys and labels.
df.groupby("region", dropna=False)["quantity"].sum() splits rows by region and adds the quantities: North 2 + 4 + 1 = 7, South 6 + 3 + 2 = 11. dropna=False keeps a group for a missing region label if one exists; it does not fill missing quantities. Then check the result: 7 + 11 = 18, which matches the source total. This simple check detects rows lost during grouping.
Named aggregation calculates several measures in one call. Each output name is followed by a pair: the source column and the calculation.
df.groupby("region", as_index=False).agg(
transactions=("transaction_id", "size"),
units=("quantity", "sum"),
avg_unit_price=("unit_price", "mean"),
)
as_index=False keeps region as an ordinary column. size counts all rows; count excludes missing values in the selected column. The average prices, 56.67 (North) and 53.33 (South), are simple means across transactions. They are not the average price paid per unit, which would need quantity weights.
A regional total can hide product differences. Grouping by both region and product_category gives one row per observed combination: North sold 6 tea units and 1 coffee unit; South sold 6 tea units and 5 coffee units. So the 4-unit gap between regions comes entirely from coffee in this example. That is a description, not an explanation; stock availability or customer numbers might be the reason.
pivot_table(index="region", columns="product_category", values="quantity", aggfunc="sum") shows the same result as a grid. Request sum explicitly, because the default is mean. Do not add fill_value=0 automatically: an empty cell may mean no transactions, incomplete coverage or an unknown value. A pivot table changes the layout; it does not fix source problems.
Pearson correlation r measures a linear relationship between two numeric variables, from -1 to 1. Positive means they tend to rise together; negative means one tends to fall as the other rises. Use individual transactions, not the two regional totals. In the example, r is about -0.896 on six complete pairs. Remove rows missing either value and report the count. A constant variable has no defined correlation, and a value near zero does not rule out a curved relationship, so always look at a scatter plot.
The negative correlation does not prove that lowering prices raises sales. Coffee and tea have different prices and may attract different customers, so category could explain part of the pattern. In a small sample, one unusual transaction can change r a lot.
Quantities, regions, categories and T1's price follow the lecture; the other prices are illustrative values chosen to reproduce the lecture's averages and correlation.
import pandas as pd
df = pd.DataFrame({
"transaction_id": ["T1", "T2", "T3", "T4", "T5", "T6"],
"region": ["North", "North", "North", "South", "South", "South"],
"product_category": ["Tea", "Tea", "Coffee", "Tea", "Coffee", "Coffee"],
"quantity": [2, 4, 1, 6, 3, 2],
"unit_price": [50, 40, 80, 30, 60, 70],
})
by_region = df.groupby("region", dropna=False)["quantity"].sum()
assert by_region.sum() == df["quantity"].sum() # 18: no row lost
print(by_region)
print(df.pivot_table(index="region", columns="product_category",
values="quantity", aggfunc="sum"))
pairs = df[["quantity", "unit_price"]].dropna()
r = pairs["quantity"].corr(pairs["unit_price"])
print(f"r = {r:.3f} on n = {len(pairs)} complete pairs")
Expected output: North 7, South 11; a pivot with North (Coffee 1, Tea 6) and South (Coffee 5, Tea 6); and r = -0.896 on n = 6 complete pairs. By category, tea totals 12 units and coffee 6, again 18 in total. A careful finding: "South sold 11 units compared with 7 in North; tea was equal, so coffee explained the difference. Quantity and unit price were negatively correlated in six transactions, but this small example does not establish a price effect."
count when you mean rows. count ignores missing values; size counts every row in the group.aggfunc="sum" you get means, not totals.r, n and a limitation, and check a scatter plot and meaningful subgroups.groupby(...)[col].sum() gives totals per group; dropna=False keeps missing-label groups visible.agg() creates clearly labelled measures in one table.pivot_table presents two-way summaries; set aggfunc and think before filling empty cells.r describes linear association on complete pairs; report the sample size and limits.Flashcards · Grouped Summaries and Relationships
9 wordsSplits rows into groups by one or more columns so a calculation can run on each group.
Keeps a separate group for missing labels instead of silently dropping those rows.
An `agg()` call where each output column is named and given a (column, function) pair.
`size` counts all rows in a group; `count` counts only non-missing values of a column.
Builds a grid summary with one variable as rows and another as columns; default aggregation is mean.
Confirming that grouped totals add up to the total of the detailed data.
A number from -1 to 1 measuring the strength and direction of a linear relationship.
An average price that weights each transaction by quantity, unlike a simple mean across transactions.
An association between two variables does not show that one causes the other.