Merging, appending and reshaping data

Why this matters

Data rarely arrives in the shape an analysis needs. Customer details are in one file and orders in another; this year's data sits beside last year's with slightly different columns; a report needs months across the top when the data has one row per month. Transforming data into the right shape is routine work, and every reshaping step can gain or lose rows without warning.

The exam asks you to recognise these transformations and choose the right one. This lesson covers merging on keys, appending mismatched data sets, pivoting between wide and long layouts, changing the grain of data, and the row count that checks every step.

The lesson

Merging on keys, and checking what failed to match

Merging combines two data sets side by side, matching rows on a key, and is the same operation as a SQL join, whether it is done in SQL, in a spreadsheet with a lookup function, or in Python with pandas' merge. The same choices apply: an inner merge keeps only matched rows, a left merge keeps every row of the main data set, and an outer merge keeps everything.

The step that is most often skipped is checking what failed to match. A left merge of orders to customers that finds no customer for some orders fills their customer columns with missing values. An inner merge drops those orders altogether. In both cases the merge succeeds and reports nothing.

The check is to count unmatched rows on each side, deliberately. Many tools can add an indicator showing whether each row matched on the left, the right, or both; pandas' merge has an indicator option for exactly this. Unmatched rows should be few, explained, and recorded. A spreadsheet VLOOKUP or XLOOKUP that returns #N/A is the same signal, and replacing it with blanks to tidy the sheet hides it.

It is also worth checking the result for fan-out, covered in the joins lesson: the merged data set should have exactly one row per row of the main data set unless the relationship is deliberately one-to-many.

Appending data sets whose columns differ

Appending stacks data sets end to end: January's file below December's, or three regional extracts into one. In SQL it is UNION ALL; in pandas, concat; in a spreadsheet, pasting rows below.

Appending is simple when every file has identical columns. In practice they drift: a column is renamed (cust_id becomes customer_id), a new column is added halfway through the year, columns appear in a different order, or a type changes. Each of these causes a specific failure.

• Renamed columns end up as two half-empty columns, one per name.
• New columns are empty for the earlier periods, which may be right, or may mean the data was never collected, and the difference matters.
• Reordered columns are matched by position in SQL UNION, so values end up in the wrong column, silently.
• Changed types can force a whole column to text.

The approach is to align the schemas first: map old names to current ones, put columns in the same order, convert types, and add missing columns explicitly. Adding a source column that records which file each row came from makes problems traceable after the append.

Wide against long, pivot and unpivot

The same data can be laid out two ways.

In long (or tidy) format, each row is one observation: one row per region per month, with columns region, month and sales. Long format is best for analysis, filtering, grouping and most charting tools, and it is how data is usually stored.

In wide format, one variable is spread across columns: one row per region, with a column for each month. Wide format is best for people reading a table, such as a report or a spreadsheet summary.

Pivoting turns long into wide, and unpivoting (also called melting) turns wide into long. A pivot table in a spreadsheet, pivot_table in pandas, and conditional aggregation in SQL all pivot. A common real task is the reverse: receiving a spreadsheet with a column per month and unpivoting it so it can be analysed and combined with other data.

Pivoting has one trap: if more than one row exists for the same row and column combination, values must be aggregated, summed or averaged, and a pivot that quietly takes the first value, or averages where a sum was meant, gives a misleading table.

Aggregating to a different grain

The grain of a data set is what one row represents: one order, one customer, one day, one region per month. Many analyses need data at a coarser grain than it was collected at: daily orders summarised to monthly totals, or order lines summarised to one row per customer.

Changing grain is aggregation, and two points need care. First, choose the right aggregate for each column: quantities are summed, prices are usually averaged or recalculated, counts of customers need COUNT DISTINCT rather than a sum, and descriptive fields such as a customer's region need a rule, because a customer may have orders in more than one. Second, never combine grains by accident: joining a monthly summary table back onto order-level data repeats each monthly figure on every order in that month, and summing it again multiplies it.

Knowing the grain of every table you touch, and stating it, prevents most of these errors.

The row count as the test of every reshape

Every transformation in this lesson has a predictable effect on the number of rows, and checking it is the simplest test there is.

• A left merge onto a unique key: same row count as the main data set.
• An append of files with 100 and 150 rows: 250 rows.
• A pivot of 4 regions by 12 months from long to wide: 4 rows.
• An unpivot of that table back to long: 48 rows.
• An aggregation of 2,000 orders to one row per customer who ordered: one row per distinct customer.

Predict the count, run the step, compare. A mismatch is always informative: extra rows mean duplicated keys or fan-out, missing rows mean unmatched keys or an unintended filter. Checking totals of a key measure, such as total quantity before and after a reshape, adds a second test: reshaping should move values around, not change their sum.