Skip to content

MODULE 8 · LESSON 45 · Data tools and workflows

Pivot-style summaries

Build a grouped summary while explaining the aggregation, missing-value policy and retained detail.

By Laurence Svekis · Self-paced · All worked data is synthetic unless you supply your own source.

Try the practice ↓

PredictInspectCalculateCheckExplainTransfer

Your starting point

Prerequisites: Use the ideas and vocabulary from lesson 44; revisit its worked examples if needed.

Definition: A pivot-style summary groups records by one or more categories and applies an aggregation such as count, sum or mean. The summary is a derived view; it does not replace the original records or preserve every detail of their distribution.

Choose grouping fields intentionally

Grouping by region answers a different question from grouping by month or product. Each group combines records sharing the chosen label. Check spelling and whitespace first, because inconsistent labels can split one intended category into several. Do not merge categories merely because they look similar without understanding whether they represent distinct groups.

Select the aggregation that fits the field

Sum is appropriate for additive quantities such as non-overlapping revenue records. Mean summarizes valid numerical observations. Count can mean all rows or only nonmissing values depending on software; this workbench labels row count explicitly and also reports the number of valid numerical values. Summing identifiers or averaging category codes is usually meaningless despite being computationally possible.

Reconcile the summary with the source

Group row counts should add to the filtered record count when every row belongs to one group. Additive sums should reconcile with the corresponding overall sum under the same missing-value rules. Means do not add meaningfully across groups. Preserve counts and inspect small groups before drawing comparisons. A pivot is a compact answer to a specific question, not an automatic explanation of why groups differ.

Two worked examples

Grouped totals

Rows are North 10, North 20 and South 15. Grouped sums are North 30 and South 15; together they reconcile to 45. Grouped means are 15 and 15, which should not be added to describe an overall mean. The overall mean is also 15 because all three values total 45.

Missing values and counts

Group A has values 5 and blank; group B has 3 and 7. Row counts are two in each group, but valid numeric counts are one and two. A mean of 5 for both groups hides different amounts of observed numerical evidence. Report both counts when the distinction matters.

Where this becomes useful

A support manager groups tickets by channel. A pivot can show total ticket volume and mean response duration, while the original records remain necessary for investigating long waits or inconsistent priority definitions.

Make an attempt

Load Customer support in the workbench. Build summaries by channel using Count, Mean and Sum for minutes. Reconcile row counts, inspect valid numerical counts and explain why a sum of minutes and a mean of minutes answer different questions.

Open the accompanying interactive lab →

Misconception check

Claim: Group means can be added to obtain the overall mean.

Inspect the correction

Combine sums and valid counts, or use an appropriately weighted mean. The sum of group means has no general overall-mean interpretation.

Independent application

Create a grouped summary from a synthetic dataset using row count, valid count and one numerical aggregation. Reconcile it with the filtered source and describe a pattern plus one within-group feature that the summary alone cannot show.

Completion records practice and self-review. It is not certification of statistical competence.