Advanced

Long and wide

The same data has two shapes. Wide is one row per thing with a column per measurement; long is one row per measurement. Analysis is easier in one shape and output is easier in the other, and converting between them is a normal step, not a sign of trouble.

What you will see

north [3, 5] and south [9, 0] - the 0 is the rule you chose for absent cells; make sure it was your rule.

About 2 min of reading

Try It

Pivot long rows into a grid.

1

Long data

rows = [('north','jan',3),('south','jan',9),('north','feb',5)] grid = {} for r, m, v in rows: grid.setdefault(r, {})[m] = v print(grid)

Before you run it, say out loud what it will print. Then open the answer.

What it prints
{'north': {'jan': 3, 'feb': 5}, 'south': {'jan': 9}}.
2

Print the grid with a gap rule

months = ['jan','feb'] for r in sorted(grid): print(r, [grid[r].get(m, 0) for m in months])

Before you run it, say out loud what it will print. Then open the answer.

What it prints
north [3, 5] and south [9, 0] - the 0 is the rule you chose for absent cells; make sure it was your rule.

Put it in order

Before you write anything, put these lines back in the right order. Click a line to move it up one step. This support step is easier than writing code from scratch, and it is here to make the last example stick.

Data Flow

Records pivot into a grid and back.
1 Store one row per observation
↓
2 Index by row key then column key
↓
3 Fill absent cells with a stated rule
↓
4 Print the grid
Line by line

Read the first example one line at a time. A line that prints shows what appears on screen; the rest set things up quietly.

rows = [('north','jan',3),('south','jan',9),('north','feb',5)] sets nothing on screen
grid = {} sets nothing on screen
for r, m, v in rows: sets nothing on screen
grid.setdefault(r, {})[m] = v sets nothing on screen
print(grid) {'north': {'jan': 3, 'feb': 5}, 'south': {'jan': 9}}.
Why this works
Column names that are data

If your columns are jan, feb, mar, the month is a value hiding in a name. Any code that adds a month breaks, and any code that averages across columns averages the wrong thing. Melt those columns into a month field.

Missing cells need a rule

A grid with gaps has cells that do not exist. Decide whether absent means zero or unknown, and fill the grid consistently. The two answers give different totals, and the difference is usually the whole bug.

Examples

Long is the storage shape

One row per observation: (region, month, value). It extends by adding rows, never by adding columns, so a new month needs no code change. This is the shape to store and the shape a database wants.

Wide is the display shape

Months across the top, regions down the side, so a reader scans a grid. Build it from long at print time; never store it, because a new month means a new column and old code that does not know about it.

Pivot with a nested dictionary

grid = {} then grid.setdefault(row['region'], {})[row['month']] = row['value']. That is the whole pivot. The reverse is a double loop over the grid emitting one row per cell.

Words this lesson uses

Each one is defined here on this site. Open the entry for the full definition and the other words it points at.

← Joining two datasets
Previous Lesson
Joining two datasets
Next Lesson
Loops versus vectorised work
Loops versus vectorised work →