Transforming and reshaping data

In depth

Rules for derived variables

• Define it before you compute it. Write the formula, its unit and its edge-case rule in the data dictionary. Is the discount per order or per item? Does the night count include the check-out day? (It doesn’t: nights = check-out − check-in.)
• Let missing values propagate. If an input is missing, the result is missing. Arithmetic does this in most tools, but functions that skip missing values, and spreadsheets that treat a blank cell as zero, can turn a missing quantity into a total of $0.
• Guard ratios. A ratio with a zero or missing denominator is undefined, not zero. Decide what to record (missing, with a flag) and say so.
• Fix the clock. An order placed at 11:30 p.m. on 31 March in one time zone is in April in another. Decide which time zone defines “the month” and use it everywhere.
• Test before you use. Where the data already store the value (Lantern’s order_total, Harborview Hotel’s nights and total), count the disagreements and resolve them before deciding which column to keep.

Choosing bin edges

For $k$ equal-width bins on values from $x_{\min}$ to $x_{\max}$, every bin has width $w = (x_{\max} - x_{\min}) / k$. Equal-frequency bins use quantiles as edges: for four bins, the quartiles. The table bins twelve fictional Lantern order values, before discount, three ways: $14, $15.50, $17, $19.50, $22, $28, $33, $39, $46, $58, $98 and $116.

Equal-width edges are 14, 48, 82 and 116, each bin including its upper edge. The business bands use the $40 promotion threshold.

Each method has a typical failure. Equal-width bins are easy to explain, but a few large values stretch the range so that most values crowd into one bin: here nine of the twelve orders land in the first. Equal-frequency bins always fill evenly, but their edges fall wherever the data happen to put them, and one bin may mix very different values: the third holds a $46 order and a $116 order. Ties make quantiles awkward: if many values are identical, an edge can’t split them, and tools either merge bins or stop (pandas’ qcut raises an error by default when two edges coincide). Business bands answer the business question directly, but only someone who knows the business can set them.

State the boundary rule. A value that sits exactly on an edge must go into one bin only, and conventions differ: pandas’ cut includes the right edge of each bin by default, while many business rules include the left edge (“$40 and over”). Write the rule in the data dictionary. And if the bins will feed a predictive model, compute quantile edges from the training set only and apply the same edges to the test set, for the same reason you fit a scaler on training data: edges computed from all the data let information from the test set leak into training.

Group summaries and what an average means

An aggregation has three parts: the grouping variables (region and month), the variable being summarised (order total) and the function (sum). Write them down before you run it, then check the result: the group counts should add up to the number of rows you started with, and the group sums to the overall sum. If they don’t, rows went missing. A common cause is a missing grouping value: pandas’ groupby leaves out rows whose key is missing unless you ask for them with dropna=False, so orders with a blank region silently vanish from a regional summary.

Know what each function does with missing values. In SQL, count(*) counts rows, while count(rating) counts only the rows where rating is present, and sum adds only the values that are present. An average rating is therefore the average among customers who left a rating, which may be a very different group from all customers.

The commonest error with averages is averaging averages. Suppose you know the mean of each group, $\bar{x}_g$, and the number of rows in each group, $n_g$. The mean of all the rows is not the plain average of the group means but their weighted average, each group mean weighted by its group’s size:

A plain average of group means gives every group the same say, however many rows it has. That answers a different question, such as “what does the typical customer spend per order?” when each group is one customer. Say which one you computed, and never average a column of averages, percentages or ratios without asking what the weights should be.

Aggregating can even turn a comparison around. Suppose Lantern’s ratings looked like the table below. Within each product, subscribers rate higher than one-off buyers; yet across both products subscribers rate lower, because most of their ratings are for the product everyone rates lower. This reversal is known as Simpson’s paradox.

Subscribers: (80 × 4.0 + 20 × 4.8) ÷ 100 = 4.16. One-off buyers: (20 × 3.8 + 80 × 4.6) ÷ 100 = 4.44.

Neither summary is wrong; they answer different questions. Before you compare groups on an aggregate, check whether the comparison still holds within the subgroups that matter, and report the subgroup figures when it doesn’t.

Occasionally a question needs a finer granularity than the data have. Harborview Hotel stores one row per booking, but occupancy is counted per night: a booking from 30 January to 2 February 2026 contributes two nights to January and one to February. To count nights per month you expand each booking into one row per night, a deliberate one-to-many transformation, and the check is that the expanded table has exactly as many rows as the bookings have nights in total. You can only go finer when a rule says how to split each row; splitting a monthly total into days without such a rule would be inventing data.

Pivot and melt

Here is revenue for two Lantern products from March to May 2026, as a finance report would show it, and the same values in long format.

Melting turns the three month columns into two: one for the old headers (month) and one for the cell values (revenue). The wide table holds 2 rows × 3 month columns = 6 values, so the long table has 6 rows; that product is the check. Notice the blank. In the wide table it is easy to read as “no sales”, but the long table forces a decision, because the row needs a value. Is Decaf’s April revenue $0 (nothing sold) or unknown (not recorded)? Only the source can say, and the answer changes Decaf’s total and its monthly average.

Pivoting goes the other way and needs exactly one value for each pair of row label and column label. If the long table holds two rows for House Blend in March, perhaps a duplicate that survived cleaning, there is nowhere to put the second value. Some tools refuse: pandas’ pivot raises an error when a pair has more than one value. Others aggregate: a pivot table sums or averages the values in each cell, depending on the function chosen (pandas’ pivot_table averages unless told otherwise), so a duplicate can disappear into a cell without trace. Check for repeated label pairs before you pivot.

Hadley Wickham’s paper “Tidy data” lists the commonest layouts that need reshaping, and the first is column headers that are values rather than variable names: months, years or age bands across the top of a table. Melting is the fix. In a spreadsheet it is done with a query tool (Excel’s Power Query has an Unpivot Columns command); in pandas it is melt; in SQL a common approach stacks one query per column with UNION ALL.

Join types and their row counts

Call the table you start from the left table and the table you bring in the right table. Join types differ in what they do with a row whose key has no match on the other side:

Inner join

Keeps only the rows whose key appears in both tables. Unmatched rows on either side are dropped.

Left join

Keeps every row of the left table; where the right table has no match, its columns are left missing. A right join is the same with the tables swapped.

Full outer join

Keeps every row of both tables, matching where it can and leaving missing values on whichever side has no match.

Row counts follow from the keys. For each key value $k$, let $a_k$ be its number of rows in the left table and $b_k$ its number in the right. Each left row is paired with every matching right row, so that key produces $a_k \times b_k$ rows. When the right table’s key is unique (every $b_k$ is 0 or 1), a left join returns exactly as many rows as the left table, which is what you nearly always want when you look up an attribute. When it isn’t, rows multiply. This is the row explosion, and it is easy to miss because nothing fails: the joined table looks normal, but every total computed from it is too large.

The cardinality of a relationship names the pattern. One-to-one: each key at most once on each side. One-to-many or many-to-one: unique on one side only, as with orders (many) and products (one). Many-to-many: repeated on both sides, which usually means that one table is at the wrong granularity or still holds duplicates. Decide which you expect before joining, and test it. pandas’ merge will test it for you: validate='many_to_one' checks that the right table’s keys are unique, and indicator=True adds a column saying whether each row matched on both sides or came from one side only.

Two details differ between tools. In SQL a missing key never matches anything, because comparing a value with NULL gives “unknown” rather than true, so rows with missing keys drop out of an inner join. pandas, by contrast, matches missing keys with each other, which can pair every blank-key row on one side with every blank-key row on the other. Spreadsheet lookups behave differently again: XLOOKUP returns a single match, by default the first it finds searching from the top, so a repeated key in the lookup table doesn’t multiply rows; it silently picks one of the conflicting values. Whatever the tool, resolve missing and repeated keys before you join.

A repeatable sequence

Order matters. Join after removing duplicates, not before, or the duplicates multiply. Derive and validate before you aggregate, because an aggregate hides the rows that produced a bad total. Bin after deciding about outliers, because one extreme value moves equal-width edges. And produce every output from the raw file by the same recorded steps, so that when next month’s export arrives you run them again instead of redoing the work by hand. After each step, run the same four checks:

1. Before: count the rows, and write down the granularity and key you expect afterwards.
2. After: count again and explain the difference. Deriving should add columns, not rows; melting should multiply the rows by the number of melted columns; a lookup join should leave the row count unchanged.
3. Reconcile a total: overall revenue should be the same before and after a reshape, a lookup join or an aggregation.
4. Inspect the exceptions: unmatched keys, missing derived values, empty bins, rows with a blank group.