Kwker

Tutorial: group-by on a table

In this tutorial you summarize a table of sales: revenue, number of orders and the median order for every store, with the best stores first. You use kwker.frame, which works on pandas, Polars and pyarrow tables and follows each library's own rules for missing values. At the end you run it on five million rows and check it against pandas.

It takes about 15 minutes. You need Python with pandas and Kwker installed (Installation).

Step 1: the sales table

Each row is one order: the store it came from and the amount. One amount is missing.

PythonNeeds pandas: runs on your machine.
import numpy as np
import pandas as pd
import kwker.frame as sf

sales = pd.DataFrame({
    "store": ["oslo", "lima", "oslo", "kyiv", "lima", "oslo", "kyiv", "lima"],
    "amount": [120.0, 80.0, 45.5, 300.0, None, 60.0, 15.0, 99.5],
})
print(sales)
Output
  store  amount
0  oslo   120.0
1  lima    80.0
2  oslo    45.5
3  kyiv   300.0
4  lima     NaN
5  oslo    60.0
6  kyiv    15.0
7  lima    99.5

Step 2: revenue and orders per store

group_by makes one row per store. Name each result column and say how to fill it: ("amount", "sum") adds the amounts, and "size" counts the rows. As in pandas, the missing amount is skipped by the sum but still counts as an order.

PythonRuns on your machine.
per_store = sf.group_by(sales, "store", {"revenue": ("amount", "sum"), "orders": "size"})
print(per_store)
Output
  store  revenue  orders
0  kyiv    315.0       2
1  lima    179.5       3
2  oslo    225.5       3

The stores come out in sorted order, like SQL's GROUP BY store ORDER BY store.

Step 3: more statistics in the same call

Add as many columns as you need. count counts only the amounts that are there, and median is exact.

PythonRuns on your machine.
per_store = sf.group_by(sales, "store", {
    "revenue": ("amount", "sum"),
    "orders": "size",
    "paid": ("amount", "count"),
    "median": ("amount", "median"),
    "largest": ("amount", "max"),
})
print(per_store)
Output
  store  revenue  orders  paid  median  largest
0  kyiv    315.0       2     2  157.50    300.0
1  lima    179.5       3     2   89.75     99.5
2  oslo    225.5       3     3   60.00    120.0

Step 4: the best stores first

Sort the summary by revenue, largest first. For a "top stores" box you only need the first rows: top_k finds them without sorting the rest.

PythonRuns on your machine.
print(sf.sort(per_store, "revenue", descending=True))
print(sf.top_k(per_store, "revenue", 1, descending=True)["store"].tolist())
Output
  store  revenue  orders  paid  median  largest
0  kyiv    315.0       2     2  157.50    300.0
2  oslo    225.5       3     3   60.00    120.0
1  lima    179.5       3     2   89.75     99.5
['kyiv']

Step 5: five million rows

Make five million orders across 2,000 stores, with some amounts missing, and summarize them with four threads. Then compute the same table with pandas and compare. Sums of floating-point numbers can differ in the last digits when they are added in a different order, so compare those with np.allclose; counts must match exactly.

PythonRuns on your machine.
rng = np.random.default_rng(11)
n = 5_000_000
big = pd.DataFrame({
    "store": rng.integers(0, 2000, n),
    "amount": np.where(rng.random(n) < 0.01, np.nan, rng.gamma(2.0, 40.0, n)),
})

ours = sf.group_by(big, "store", {"revenue": ("amount", "sum"), "orders": "size", "median": ("amount", "median")},
                   threads=4)
theirs = big.groupby("store", as_index=False).agg(revenue=("amount", "sum"), orders=("amount", "size"),
                                                  median=("amount", "median"))
print(len(ours), "stores")
print(np.array_equal(ours["store"], theirs["store"]), np.array_equal(ours["orders"], theirs["orders"]))
print(np.allclose(ours["revenue"], theirs["revenue"]), np.allclose(ours["median"], theirs["median"]))
Output
2000 stores
True True
True True

What you built

Next steps