Counting and grouping: collections and statistics

Why counting and grouping matter

Most business questions are counts or totals by group. How many orders came from each city? What is the total sale per item? Which complaint type is most common? In SQL this is GROUP BY, in pandas it is groupby, and in Excel it is a pivot table. Learning it in plain Python first shows you exactly what those tools do.

Counter: how many of each

Counter, from the collections module, takes a list and counts how many times each value appears. It behaves like a dict from value to count. Asking for a value that never appeared gives 0, not a KeyError.

from collections import Counter

cities = ["Pune", "Delhi", "Pune", "Mumbai", "Pune", "Delhi"]
c = Counter(cities)
print(c)                  # Counter({'Pune': 3, 'Delhi': 2, 'Mumbai': 1})
print(c["Pune"], c["Goa"])  # 3 0
print(c.most_common(2))   # [('Pune', 3), ('Delhi', 2)]

most_common breaks ties by the order in which values were first seen. If you need a fixed rule, such as alphabetical order for ties, sort yourself: sorted(c.items(), key=lambda kv: (-kv[1], kv[0])). The minus sign on the count sorts the biggest count first.

Grouping with defaultdict

Grouping means putting rows into buckets by a key, such as city. With a normal dict you must check whether the bucket exists before adding to it. defaultdict(list) creates an empty list automatically the first time you use a new key, so the loop stays short.

from collections import defaultdict

orders = [("Pune", 250), ("Delhi", 900), ("Pune", 120), ("Chennai", 640), ("Delhi", 300)]
by_city = defaultdict(list)
for city, amount in orders:
    by_city[city].append(amount)
print(dict(by_city))   # {'Pune': [250, 120], 'Delhi': [900, 300], 'Chennai': [640]}

Group, then aggregate

To aggregate means to squash a group of values into one number: a sum, a count, an average, a maximum. Group first, then aggregate each bucket. For a running total you can also skip the lists and use defaultdict(int), which starts every new key at 0.

totals = {city: sum(amounts) for city, amounts in by_city.items()}
print(totals)   # {'Pune': 370, 'Delhi': 1200, 'Chennai': 640}

totals2 = defaultdict(int)
for city, amount in orders:
    totals2[city] += amount
print(dict(totals2) == totals)   # True

A preview of the statistics module

Python's statistics module gives common summaries in one call. The mean is the ordinary average, the median is the middle value after sorting, and the mode is the most common value. Module 2 explains when each one is the right choice and teaches you to compute them by hand.

import statistics

marks = [72, 85, 85, 90, 64]
print(statistics.mean(marks))     # 79.2
print(statistics.median(marks))   # 85
print(statistics.mode(marks))     # 85

The same idea in pandas and SQL

import pandas as pd

df = pd.DataFrame({"city": ["Pune", "Delhi", "Pune", "Chennai", "Delhi"],
                   "amount": [250, 900, 120, 640, 300]})
print(df["city"].value_counts())            # Pune 2, Delhi 2, Chennai 1
print(df.groupby("city")["amount"].sum())   # Chennai 640, Delhi 1200, Pune 370

Common mistakes

• KeyError when adding to a normal dict: totals[city] += amount fails for a new city. Fix: use defaultdict(int), or totals[city] = totals.get(city, 0) + amount.
• Counting text that is not clean: "Pune", "pune" and "Pune " become three different keys. Fix: clean first, for example city.strip().title().
• Trusting tie order from most_common when the order matters. Fix: sort with an explicit key such as (-count, name).
• Calling statistics.mean on an empty list, which raises StatisticsError. Fix: check that the list has values first.