Reshaping & aggregation

Long and wide formats, and when each helps

Imagine you run three small shops — North, South, and East — and you wrote down how much each one sold in January, February, and March. There are two natural ways to arrange those nine numbers in a table, and the difference between them turns out to be one of the most useful ideas in all of data wrangling.

The first arrangement is the one a human spreadsheet user would reach for: one row per shop, and one column for each month. This is called wide format, because as you add more months the table grows wider with more columns.

store  jan  feb  mar
North  120  135  150
South   90  100  110
East   200  180  220

The second arrangement looks stranger at first, but computers love it. Here every single measurement gets its own row, and we use a column to say which shop and which month it belongs to. This is called long format, because as you add data the table grows taller with more rows.

store  month  sales
North  jan    120
North  feb    135
North  mar    150
South  jan     90
South  feb    100
South  mar    110
East   jan    200
East   feb    180
East   mar    220

Notice that no information has been gained or lost — both tables hold exactly the same nine numbers. They are the same data wearing different clothes. The long table is exactly what we called tidy data in the previous guide: one row per observation, one column per variable, one value per cell. The wide table breaks that rule, because the months January, February, and March are really values of a single variable (month), yet here they have been spread out into column names.

Pivoting: wide to long and back

Since the two shapes hold the same data, you must be able to convert freely between them. That conversion is called pivoting, or reshaping — see pivoting/reshaping. It is one of the two or three operations you will perform most often in your career, so it pays to get the words straight.

Going from wide to long is often called melting (or unpivoting, or gathering). Picture the wide table's column headers — jan, feb, mar — melting down into the cells of a new column. Each of those three headers, repeated for each shop, becomes a value in a month column, and the numbers underneath line up in a sales column.

Going the other way, from long to wide, is often called pivoting (or spreading, or casting). You pick one column to become the new headers (here, month) and one column to fill the cells (here, sales). Each distinct month becomes its own column again.

# wide -> long  (melt / unpivot)
long = sales_wide.melt(
    id_vars='store',      # keep these as identifying columns
    var_name='month',     # old column headers go here
    value_name='sales')   # the numbers go here

# long -> wide  (pivot)
wide = long.pivot(
    index='store',        # one row per store
    columns='month',      # one column per month
    values='sales')       # fill cells with sales

Why bother converting at all? Because different tools demand different shapes. A line chart of sales over time wants long data (one row per point on the line). A correlation matrix or a printed comparison table often wants wide data. You will constantly reshape on the way into a chart or a model, then perhaps reshape the results back into a wide table for a report.

Group-by: the split-apply-combine pattern

Reshaping moves data around without summarizing it. The moment you want to summarize — total sales per shop, average rating per product, number of orders per day — you reach for grouping, formally group-by aggregation. It is the single most-used analysis operation there is, and once it clicks you will see it everywhere.

The mental model has a famous name: split-apply-combine. You split the rows into groups, apply a summary calculation inside each group, then combine the one-number-per-group answers into a small result table. See split-apply-combine.

1. Split: cut the long table into groups — one group for North's three rows, one for South's, one for East's. The grouping column is the key.
2. Apply: run a summary calculation separately inside each group — for instance, add up the three sales numbers to get a total.
3. Combine: stack the per-group answers into a new, smaller table with one row per group.

For our shop data, grouping by store and summing sales gives North = 120 + 135 + 150 = 405, South = 90 + 100 + 110 = 300, and East = 200 + 180 + 220 = 600. Nine rows of detail collapse into three rows of insight.

# pandas
summary = (sales_long
    .groupby('store')['sales']
    .sum())

# the same idea in SQL
# SELECT store, SUM(sales) AS total_sales
# FROM   sales
# GROUP  BY store;

Aggregations that answer real questions

The summary calculation you apply inside each group is called an aggregation function: it takes many values and returns one. Summing was just the first example. The everyday family is small and worth memorizing: count (how many), sum (total), mean (average), min and max (smallest and largest), median (the middle value), and standard deviation (a measure of spread).

Most real questions are secretly just an aggregation paired with a grouping. The trick to becoming fluent is to translate plain English into that pair. The question chooses the aggregation function; the phrase 'per X' or 'for each X' chooses the grouping column.

1. 'What is the total sales for each shop?' -> group by shop, aggregate sales with sum.
2. 'What is the average order value per region?' -> group by region, aggregate order_value with mean.
3. 'How many active users did we have each day?' -> group by day, aggregate user_id with a count of distinct values.
4. 'What is the highest temperature recorded in each city?' -> group by city, aggregate temperature with max.

The most common aggregation, the group mean, deserves its formula. Read it in words first: for a group g, add up every value in the group and divide by how many there are. The notation below just says that precisely.

The mean of group g: sum the values that belong to g, then divide by n_g, the count in that group. For North, that is (120 + 135 + 150) / 3 = 135.

You will usually compute several aggregations at once — a total, an average, and a count side by side — because each tells you something different and the count guards against being fooled by a tiny group.

# several aggregations in one pass (pandas)
summary = (sales_long
    .groupby('store')['sales']
    .agg(['sum', 'mean', 'count']))

# SQL
# SELECT store,
#        SUM(sales)  AS total_sales,
#        AVG(sales)  AS avg_sales,
#        COUNT(*)    AS n_months
# FROM   sales
# GROUP  BY store;

Window functions in one paragraph

Group-by has one limitation: it collapses each group down to a single row, so the detail rows vanish. But often you want to keep every original row and simply attach a per-group summary beside it — every shop's row carrying that shop's average next to it, or each sale shown as a percentage of its shop's total, or a running cumulative sum down a column. The tool that keeps the rows and adds a group-aware column is the window function (sometimes called an analytic function).

The key contrast is one sentence: group-by gives you fewer rows (one per group); a window function gives you the same number of rows, plus a new column. In SQL you write OVER (PARTITION BY ...) instead of GROUP BY; the PARTITION BY clause is the grouping, but the rows stay.

SELECT store, month, sales,
       AVG(sales) OVER (PARTITION BY store)            AS store_avg,
       sales - AVG(sales) OVER (PARTITION BY store)    AS vs_avg
FROM   sales;

Before window functions existed, you got the same result the long way: aggregate to a small per-group table, then join that summary back onto the detail rows by the group key. That still works and is worth understanding, because it shows what the window function is doing under the hood — and because a join is the right move when your summary lives in a separate table.

From raw rows to a summary table

Let us put the pieces together on something closer to real data. Suppose your app writes one row to an event log every time someone clicks 'buy': a timestamp, a user id, a country, and an amount. That raw log might have millions of rows and answer no question on its own. Reshaping and aggregation are the bridge from that raw pile to a table a chart or a model can use.

1. Start tidy. Confirm the log is one row per purchase, with clean columns for date, user, country, and amount. Fix the shape first if it is not.
2. Pick the grain of your answer. 'Revenue per country per day' means the result has one row per country-day; that pair is your grouping.
3. Group and aggregate. Group by country and date, then aggregate amount with sum (revenue) and user with a distinct count (buyers).
4. Reshape for the destination. A line chart wants this long; a country-by-month report wants it pivoted wide. Reshape last, once you know where it is going.

daily = (events
    .groupby(['country', 'date'])
    .agg(revenue=('amount', 'sum'),
         buyers=('user_id', 'nunique'))
    .reset_index())

# now reshape the summary for a report:
# one row per country, one column per month
report = daily.pivot_table(
    index='country',
    columns='date',
    values='revenue')

This shrink-then-reshape rhythm is the everyday backbone of analysis. Raw event rows feed exploration and dashboards through exactly this path — so much of exploratory data analysis is just trying group-by after group-by until a pattern jumps out. Get comfortable here and you can answer most business questions before lunch.

Reshaping and aggregation pitfalls

These operations are powerful, which means they fail quietly rather than loudly. The output still looks like a perfectly reasonable table; it is just wrong. Here are the traps that catch everyone at least once.

Trap 1 — losing the grain you needed. Aggregation throws away detail by design. If you collapse to daily totals and later someone asks for the hourly pattern, the answer is gone; you must go back to the raw rows. Always keep the raw data and treat each summary as a derived view, never as the only copy.

Trap 2 — joining before you aggregate. If you join two tables and one side has several rows per key, the rows multiply, and a later SUM silently double-counts. A revenue figure can come out two or three times too big. The fix is almost always to aggregate each table to the right grain first, then join the small summaries. Watch the row count before and after every join.

Trap 3 — missing values change the count. Most aggregation functions skip nulls. So mean ignores the blanks, and a count of a column counts only the non-blank cells, while a count of rows counts them all. If half your amount column is missing, an average over it is an average of the recorded half, not of everyone — which may be exactly wrong. Decide on purpose how to handle missing data before you aggregate.

Trap 4 — pivoting with duplicate keys. Pivot assumes each row maps to exactly one cell. If two rows want the same cell — say two records for North/jan — the pivot must either error or silently aggregate them, and a silent sum behind your back is the worst kind of surprise. A clean pivot needs a unique combination of row-and-column keys, the same uniqueness idea behind a primary key.

Trap 5 — the mean of means. It is tempting to average the per-group averages to get an overall average. That is only correct when the groups are the same size. Consider two regions: region A has 1000 orders averaging $10, region B has 10 orders averaging $50. The naive average of the two averages is $30 — but the true overall average is far lower, because A's thousand orders dominate.

The correct overall mean weights each group by its size n_g. Here that is (1000 x 10 + 10 x 50) / 1010 = 10500 / 1010 ~= $10.40, not the $30 the unweighted average claims.

1. Before aggregating, write down the grain of your answer in words: 'one row per ___'.
2. Aggregate each table to that grain first; join small summaries, not raw tables.
3. Check the row count after every join and every pivot — a surprise change means trouble.
4. Decide how nulls are handled, and always carry a count beside every mean and sum.
5. Keep the raw data; treat every reshaped or aggregated table as a reproducible derived view.