How to Pivot and Group Aggregate in Python
Group records by a key, collect values, and apply an aggregate function (like sum) to build a pivot-style summary dictionary.
Python code
19 linesfrom collections import defaultdict
def pivot_group_aggregate(records, group_key, value_key, agg_func):
groups = defaultdict(list)
for record in records:
groups[record[group_key]].append(record[value_key])
return {key: agg_func(values) for key, values in groups.items()}
if __name__ == "__main__":
sales = [
{"region": "North", "amount": 100},
{"region": "South", "amount": 150},
{"region": "North", "amount": 200},
{"region": "West", "amount": 80},
{"region": "South", "amount": 120},
]
result = pivot_group_aggregate(sales, "region", "amount", sum)
print(result)Output
{'North': 300, 'South': 270, 'West': 80}How it works
The helper builds a defaultdict(list) keyed by the group key, appending each value for that group. After collecting all values, a dictionary comprehension applies the aggregate function (e.g., sum) to each list. This avoids manual loops and is flexible: any callable like len, min, max, or a custom lambda can be passed as agg_func. The result mimics a SQL GROUP BY or a pivot table without external dependencies.
Common mistakes
- Forgetting that `agg_func` must accept an iterable; passing `mean` from `statistics` without qualifying it (e.g., `statistics.mean`).
- Assuming the input records are sorted; grouping works regardless of order.
- Using a regular dict without `.get()` to add new groups, causing KeyError.
Variations
- Use `itertools.groupby` on a sorted list for a similar grouping.
- Switch to pandas with `df.groupby(group_key)[value_key].agg(agg_func)` for richer pivot tables.
Real-world use cases
- Aggregating daily sales totals by region for a dashboard report.
- Summarizing user activity counts by plan type in a SaaS billing system.
- Computing average response times per API endpoint from raw logs.
Sponsored
More from Big data & Spark
Keep learning
Related tutorials and quizzes for this topic.