Wide and long: pivot_table and melt

In short: pivot_table makes a wide table with a column for each category, and melt brings back the long one with a row for each fact; you pick the shape by the question.

Problem

“Give it to me by month and category”

The manager wants the revenue of the year in one table: months down, categories across, like in Excel. You know how: join the lines with the orders, the products and the categories (merge) and group by two keys. Cancelled orders are not revenue, so you remove them. The gift card has no category and gets a name of its own, or it would drop out of the groups:

Pythondata: the shop tables

import pandas as pd

orders = pd.read_csv('year_orders.csv').rename(columns={'id': 'order_id'})
products = pd.read_csv('products.csv').rename(columns={'id': 'product_id'})
categories = pd.read_csv('categories.csv').rename(columns={'id': 'category_id', 'name': 'category'})
orders = orders[orders['status'] != 'cancelled']
orders['month'] = pd.to_datetime(orders['ordered_at']).dt.month
lines = pd.read_csv('year_items.csv').merge(orders[['order_id', 'month']], on='order_id')
lines = lines.merge(products[['product_id', 'category_id']], on='product_id', how='left')
lines = lines.merge(categories[['category_id', 'category']], on='category_id', how='left')
lines['category'] = lines['category'].fillna('Gift card')
lines['revenue'] = lines['quantity'] * lines['unit_price']
by_month = lines.groupby(['month', 'category'])['revenue'].sum().round(2)
print(len(by_month))
by_month.head(8)

import pandas as pd orders = pd.read_csv('year_orders.csv').rename(columns={'id': 'order_id'}) products = pd.read_csv('products.csv').rename(columns={'id': 'product_id'}) categories = pd.read_csv('categories.csv').rename(columns={'id': 'category_id', 'name': 'category'}) orders = orders[orders['status'] != 'cancelled'] orders['month'] = pd.to_datetime(orders['ordered_at']).dt.month lines = pd.read_csv('year_items.csv').merge(orders[['order_id', 'month']], on='order_id') lines = lines.merge(products[['product_id', 'category_id']], on='product_id', how='left') lines = lines.merge(categories[['category_id', 'category']], on='category_id', how='left') lines['category'] = lines['category'].fillna('Gift card') lines['revenue'] = lines['quantity'] * lines['unit_price'] by_month = lines.groupby(['month', 'category'])['revenue'].sum().round(2) print(len(by_month)) by_month.head(8)

▶ RunRestore the code

The numbers are right, but there are 84 rows: 12 months times 7 categories, one under another. To compare January with July, you search the list with your eyes. The table the manager wants is 12 rows and 7 columns with the same 84 numbers.

The month here is its number: 1 is January 2026, and 11 and 12 are November and December 2025.

The idea

Long and wide

The same numbers can sit in two shapes:

• Long: a row for every fact. The columns are month, category and revenue, and every month–category pair is a separate row.
• Wide: a row for every month and a column for every category. The category is no longer a column: its values have become column names.

From long to wide with the result of groupby takes one line: unstack() takes the last level of the index (the category) and turns it into columns:

Pythondata: the shop tables

import pandas as pd

orders = pd.read_csv('year_orders.csv').rename(columns={'id': 'order_id'})
products = pd.read_csv('products.csv').rename(columns={'id': 'product_id'})
categories = pd.read_csv('categories.csv').rename(columns={'id': 'category_id', 'name': 'category'})
orders = orders[orders['status'] != 'cancelled']
orders['month'] = pd.to_datetime(orders['ordered_at']).dt.month
lines = pd.read_csv('year_items.csv').merge(orders[['order_id', 'month']], on='order_id')
lines = lines.merge(products[['product_id', 'category_id']], on='product_id', how='left')
lines = lines.merge(categories[['category_id', 'category']], on='category_id', how='left')
lines['category'] = lines['category'].fillna('Gift card')
lines['revenue'] = lines['quantity'] * lines['unit_price']
by_month = lines.groupby(['month', 'category'])['revenue'].sum().round()
wide = by_month.unstack()
print(wide.shape)
wide.head(3)

import pandas as pd orders = pd.read_csv('year_orders.csv').rename(columns={'id': 'order_id'}) products = pd.read_csv('products.csv').rename(columns={'id': 'product_id'}) categories = pd.read_csv('categories.csv').rename(columns={'id': 'category_id', 'name': 'category'}) orders = orders[orders['status'] != 'cancelled'] orders['month'] = pd.to_datetime(orders['ordered_at']).dt.month lines = pd.read_csv('year_items.csv').merge(orders[['order_id', 'month']], on='order_id') lines = lines.merge(products[['product_id', 'category_id']], on='product_id', how='left') lines = lines.merge(categories[['category_id', 'category']], on='category_id', how='left') lines['category'] = lines['category'].fillna('Gift card') lines['revenue'] = lines['quantity'] * lines['unit_price'] by_month = lines.groupby(['month', 'category'])['revenue'].sum().round() wide = by_month.unstack() print(wide.shape) wide.head(3)

▶ RunRestore the code

The shape is (12, 7): 12 months and 7 categories. January: €593 kitchen, €2,038 dairy, €1,225 drinks.

The idea

Straight to wide: pivot_table

pivot_table does the grouping and the layout at once. You tell it four things:

• index: what goes in the rows (the month);
• columns: what becomes columns (the category);
• values: which column it works with (the revenue);
• aggfunc: how it works it out ('sum').

Pythondata: the shop tables

import pandas as pd

orders = pd.read_csv('year_orders.csv').rename(columns={'id': 'order_id'})
products = pd.read_csv('products.csv').rename(columns={'id': 'product_id'})
categories = pd.read_csv('categories.csv').rename(columns={'id': 'category_id', 'name': 'category'})
orders = orders[orders['status'] != 'cancelled']
orders['month'] = pd.to_datetime(orders['ordered_at']).dt.month
lines = pd.read_csv('year_items.csv').merge(orders[['order_id', 'month']], on='order_id')
lines = lines.merge(products[['product_id', 'category_id']], on='product_id', how='left')
lines = lines.merge(categories[['category_id', 'category']], on='category_id', how='left')
lines['category'] = lines['category'].fillna('Gift card')
lines['revenue'] = lines['quantity'] * lines['unit_price']
report = lines.pivot_table(index='month', columns='category', values='revenue', aggfunc='sum')
report.round().astype(int)

import pandas as pd orders = pd.read_csv('year_orders.csv').rename(columns={'id': 'order_id'}) products = pd.read_csv('products.csv').rename(columns={'id': 'product_id'}) categories = pd.read_csv('categories.csv').rename(columns={'id': 'category_id', 'name': 'category'}) orders = orders[orders['status'] != 'cancelled'] orders['month'] = pd.to_datetime(orders['ordered_at']).dt.month lines = pd.read_csv('year_items.csv').merge(orders[['order_id', 'month']], on='order_id') lines = lines.merge(products[['product_id', 'category_id']], on='product_id', how='left') lines = lines.merge(categories[['category_id', 'category']], on='category_id', how='left') lines['category'] = lines['category'].fillna('Gift card') lines['revenue'] = lines['quantity'] * lines['unit_price'] report = lines.pivot_table(index='month', columns='category', values='revenue', aggfunc='sum') report.round().astype(int)

▶ RunRestore the code

Now you can read it at a glance. December is the peak: the kitchen makes €3,241, over 4 times an ordinary month, and the gift card €2,160. Summer brings drinks and fruit: drinks are €1,756 in July against €1,225 in January. September is weak almost everywhere. Why, we will find out in the project.

More precisely, if you are curious

There is also pivot without “table”: it only lays out and does not add up. If a month–category pair appears in more than one row, pivot stops with an error, while pivot_table combines them with aggfunc. For raw lines you need pivot_table.

Try it

Units by category

How many units did each category sell each month? Put a table with the categories in the rows and the months in the columns in units.

Pythondata: the shop tables

import pandas as pd

orders = pd.read_csv('year_orders.csv').rename(columns={'id': 'order_id'})
products = pd.read_csv('products.csv').rename(columns={'id': 'product_id'})
categories = pd.read_csv('categories.csv').rename(columns={'id': 'category_id', 'name': 'category'})
orders = orders[orders['status'] != 'cancelled']
orders['month'] = pd.to_datetime(orders['ordered_at']).dt.month
lines = pd.read_csv('year_items.csv').merge(orders[['order_id', 'month']], on='order_id')
lines = lines.merge(products[['product_id', 'category_id']], on='product_id', how='left')
lines = lines.merge(categories[['category_id', 'category']], on='category_id', how='left')
lines['category'] = lines['category'].fillna('Gift card')
lines['revenue'] = lines['quantity'] * lines['unit_price']
# pivot_table(index=..., columns=..., values=..., aggfunc=...)
units = ...

import pandas as pd orders = pd.read_csv('year_orders.csv').rename(columns={'id': 'order_id'}) products = pd.read_csv('products.csv').rename(columns={'id': 'product_id'}) categories = pd.read_csv('categories.csv').rename(columns={'id': 'category_id', 'name': 'category'}) orders = orders[orders['status'] != 'cancelled'] orders['month'] = pd.to_datetime(orders['ordered_at']).dt.month lines = pd.read_csv('year_items.csv').merge(orders[['order_id', 'month']], on='order_id') lines = lines.merge(products[['product_id', 'category_id']], on='product_id', how='left') lines = lines.merge(categories[['category_id', 'category']], on='category_id', how='left') lines['category'] = lines['category'].fillna('Gift card') lines['revenue'] = lines['quantity'] * lines['unit_price'] # pivot_table(index=..., columns=..., values=..., aggfunc=...) units = ...

Check▶ Run

The idea

Empty cells: fill_value

In the wide table every pair has a cell, even when the data has no such pair. Here is how much each customer bought from each category in the 60 orders:

Pythondata: the shop tables

import pandas as pd

orders = pd.read_csv('orders.csv').rename(columns={'id': 'order_id'})
products = pd.read_csv('products.csv').rename(columns={'id': 'product_id'})
categories = pd.read_csv('categories.csv').rename(columns={'id': 'category_id', 'name': 'category'})
lines = pd.read_csv('order_items.csv').merge(orders[['order_id', 'customer_id']], on='order_id')
lines = lines.merge(products[['product_id', 'category_id']], on='product_id', how='left')
lines = lines.merge(categories[['category_id', 'category']], on='category_id', how='left')
lines['revenue'] = lines['quantity'] * lines['unit_price']
spend = lines.pivot_table(index='customer_id', columns='category', values='revenue', aggfunc='sum')
print(spend.isna().sum().sum())
spend.head(4)

import pandas as pd orders = pd.read_csv('orders.csv').rename(columns={'id': 'order_id'}) products = pd.read_csv('products.csv').rename(columns={'id': 'product_id'}) categories = pd.read_csv('categories.csv').rename(columns={'id': 'category_id', 'name': 'category'}) lines = pd.read_csv('order_items.csv').merge(orders[['order_id', 'customer_id']], on='order_id') lines = lines.merge(products[['product_id', 'category_id']], on='product_id', how='left') lines = lines.merge(categories[['category_id', 'category']], on='category_id', how='left') lines['revenue'] = lines['quantity'] * lines['unit_price'] spend = lines.pivot_table(index='customer_id', columns='category', values='revenue', aggfunc='sum') print(spend.isna().sum().sum()) spend.head(4)

▶ RunRestore the code

35 cells are NaN. Customer 3 bought no bread, cleaning or drinks. Here empty means “zero purchases”, not “we do not know”. So it is right to replace it with zero: fill_value=0.

Pythondata: the shop tables

import pandas as pd

orders = pd.read_csv('orders.csv').rename(columns={'id': 'order_id'})
products = pd.read_csv('products.csv').rename(columns={'id': 'product_id'})
categories = pd.read_csv('categories.csv').rename(columns={'id': 'category_id', 'name': 'category'})
lines = pd.read_csv('order_items.csv').merge(orders[['order_id', 'customer_id']], on='order_id')
lines = lines.merge(products[['product_id', 'category_id']], on='product_id', how='left')
lines = lines.merge(categories[['category_id', 'category']], on='category_id', how='left')
lines['revenue'] = lines['quantity'] * lines['unit_price']
spend = lines.pivot_table(index='customer_id', columns='category', values='revenue', aggfunc='sum', fill_value=0)
spend.head(4)

import pandas as pd orders = pd.read_csv('orders.csv').rename(columns={'id': 'order_id'}) products = pd.read_csv('products.csv').rename(columns={'id': 'product_id'}) categories = pd.read_csv('categories.csv').rename(columns={'id': 'category_id', 'name': 'category'}) lines = pd.read_csv('order_items.csv').merge(orders[['order_id', 'customer_id']], on='order_id') lines = lines.merge(products[['product_id', 'category_id']], on='product_id', how='left') lines = lines.merge(categories[['category_id', 'category']], on='category_id', how='left') lines['revenue'] = lines['quantity'] * lines['unit_price'] spend = lines.pivot_table(index='customer_id', columns='category', values='revenue', aggfunc='sum', fill_value=0) spend.head(4)

▶ RunRestore the code

This is the case from the lesson on summaries where 0 is the right replacement: the zero really means zero. If the cell were empty because a report was missing, the zero would lie.

The idea

The totals: margins

margins=True adds an All row and column with the totals. Two of the categories and the total are shown here:

Pythondata: the shop tables

import pandas as pd

orders = pd.read_csv('year_orders.csv').rename(columns={'id': 'order_id'})
products = pd.read_csv('products.csv').rename(columns={'id': 'product_id'})
categories = pd.read_csv('categories.csv').rename(columns={'id': 'category_id', 'name': 'category'})
orders = orders[orders['status'] != 'cancelled']
orders['month'] = pd.to_datetime(orders['ordered_at']).dt.month
lines = pd.read_csv('year_items.csv').merge(orders[['order_id', 'month']], on='order_id')
lines = lines.merge(products[['product_id', 'category_id']], on='product_id', how='left')
lines = lines.merge(categories[['category_id', 'category']], on='category_id', how='left')
lines['category'] = lines['category'].fillna('Gift card')
lines['revenue'] = lines['quantity'] * lines['unit_price']
report = lines.pivot_table(index='month', columns='category', values='revenue', aggfunc='sum', margins=True)
print(report.loc['All', 'All'].round(2))
report[['Kitchen', 'Drinks', 'All']].round().astype(int)

import pandas as pd orders = pd.read_csv('year_orders.csv').rename(columns={'id': 'order_id'}) products = pd.read_csv('products.csv').rename(columns={'id': 'product_id'}) categories = pd.read_csv('categories.csv').rename(columns={'id': 'category_id', 'name': 'category'}) orders = orders[orders['status'] != 'cancelled'] orders['month'] = pd.to_datetime(orders['ordered_at']).dt.month lines = pd.read_csv('year_items.csv').merge(orders[['order_id', 'month']], on='order_id') lines = lines.merge(products[['product_id', 'category_id']], on='product_id', how='left') lines = lines.merge(categories[['category_id', 'category']], on='category_id', how='left') lines['category'] = lines['category'].fillna('Gift card') lines['revenue'] = lines['quantity'] * lines['unit_price'] report = lines.pivot_table(index='month', columns='category', values='revenue', aggfunc='sum', margins=True) print(report.loc['All', 'All'].round(2)) report[['Kitchen', 'Drinks', 'All']].round().astype(int)

▶ RunRestore the code

The All column is the revenue of the month, and the All row is the revenue of the category for the year: €11,304 kitchen, €17,811 drinks. In the corner is the total: €111,186.20. September is €7,129, less than August (€8,813) and October (€9,920).

The idea

Back to long: melt

The manager sends the plan for the next three months. The table is from Excel and it is wide: a row per category and a column per month.

A wide table is good for reading, but not for working with. The month is not a column, it is in the column names, and there is nothing to group by. melt melts it back into a long one: id_vars is the column that stays, var_name names the column with the old column names, and value_name the column with the numbers:

Pythondata: the shop tables

analysis.pyplan.csv

import pandas as pd

plan = pd.read_csv('plan.csv')
print(plan.shape)
plan_long = plan.melt(id_vars='category', var_name='month', value_name='plan')
print(plan_long.shape)
plan_long.head(9)

import pandas as pd plan = pd.read_csv('plan.csv') print(plan.shape) plan_long = plan.melt(id_vars='category', var_name='month', value_name='plan') print(plan_long.shape) plan_long.head(9)

category,2026-11,2026-12,2027-01
Bread and pastry,1250,1550,1000
Cleaning,1150,1300,850
Dairy,2900,3700,2100
Drinks,1500,2000,1300
Fruit and vegetables,1750,2400,1600
Gift card,150,2300,120
Kitchen,1000,3400,650
 

category,2026-11,2026-12,2027-01 Bread and pastry,1250,1550,1000 Cleaning,1150,1300,850 Dairy,2900,3700,2100 Drinks,1500,2000,1300 Fruit and vegetables,1750,2400,1600 Gift card,150,2300,120 Kitchen,1000,3400,650

▶ RunRestore the code

7 rows and 4 columns become 21 rows and 3 columns: every number of the plan is a separate row. Now the month is a column, and groupby works:

Pythondata: the shop tables

analysis.pyplan.csv

import pandas as pd

plan = pd.read_csv('plan.csv')
plan_long = plan.melt(id_vars='category', var_name='month', value_name='plan')
plan_long.groupby('month')['plan'].sum()

import pandas as pd plan = pd.read_csv('plan.csv') plan_long = plan.melt(id_vars='category', var_name='month', value_name='plan') plan_long.groupby('month')['plan'].sum()

category,2026-11,2026-12,2027-01
Bread and pastry,1250,1550,1000
Cleaning,1150,1300,850
Dairy,2900,3700,2100
Drinks,1500,2000,1300
Fruit and vegetables,1750,2400,1600
Gift card,150,2300,120
Kitchen,1000,3400,650
 

category,2026-11,2026-12,2027-01 Bread and pastry,1250,1550,1000 Cleaning,1150,1300,850 Dairy,2900,3700,2100 Drinks,1500,2000,1300 Fruit and vegetables,1750,2400,1600 Gift card,150,2300,120 Kitchen,1000,3400,650

▶ RunRestore the code

The plan for December is €16,650, and for November €9,700.

Try it

Which shape for what

1. You have a long table plan_long with the columns category, month and plan. How do you get the plan for each month?

First make it wide, then add up every column by handYou cannot: the long table is only for peopleplan_long.groupby('month')['plan'].sum()

2. You want a line for each category by month with df.plot(), which draws one line for every column. Which table do you give it?

It does not matterLong: the columns month, category and revenueWide: the months in the rows, a column for each category

Check

Exercise

The report for the manager

Make the report from the first card: report with the revenue by month (rows) and category (columns), with the All totals. Then put in dec_share what part of the year's revenue December is.

Pythondata: the shop tables

import pandas as pd

orders = pd.read_csv('year_orders.csv').rename(columns={'id': 'order_id'})
products = pd.read_csv('products.csv').rename(columns={'id': 'product_id'})
categories = pd.read_csv('categories.csv').rename(columns={'id': 'category_id', 'name': 'category'})
orders = orders[orders['status'] != 'cancelled']
orders['month'] = pd.to_datetime(orders['ordered_at']).dt.month
lines = pd.read_csv('year_items.csv').merge(orders[['order_id', 'month']], on='order_id')
lines = lines.merge(products[['product_id', 'category_id']], on='product_id', how='left')
lines = lines.merge(categories[['category_id', 'category']], on='category_id', how='left')
lines['category'] = lines['category'].fillna('Gift card')
lines['revenue'] = lines['quantity'] * lines['unit_price']
report = ...
dec_share = ...

import pandas as pd orders = pd.read_csv('year_orders.csv').rename(columns={'id': 'order_id'}) products = pd.read_csv('products.csv').rename(columns={'id': 'product_id'}) categories = pd.read_csv('categories.csv').rename(columns={'id': 'category_id', 'name': 'category'}) orders = orders[orders['status'] != 'cancelled'] orders['month'] = pd.to_datetime(orders['ordered_at']).dt.month lines = pd.read_csv('year_items.csv').merge(orders[['order_id', 'month']], on='order_id') lines = lines.merge(products[['product_id', 'category_id']], on='product_id', how='left') lines = lines.merge(categories[['category_id', 'category']], on='category_id', how='left') lines['category'] = lines['category'].fillna('Gift card') lines['revenue'] = lines['quantity'] * lines['unit_price'] report = ... dec_share = ...

Check▶ Run

What is the plan for the three months in total for each category? Make plan long (plan_long with the columns category, month and plan) and put the total for each category in per_category.

Pythondata: the shop tables

analysis.pyplan.csv

import pandas as pd

plan = pd.read_csv('plan.csv')
plan_long = ...
per_category = ...
print(per_category)

import pandas as pd plan = pd.read_csv('plan.csv') plan_long = ... per_category = ... print(per_category)

category,2026-11,2026-12,2027-01
Bread and pastry,1250,1550,1000
Cleaning,1150,1300,850
Dairy,2900,3700,2100
Drinks,1500,2000,1300
Fruit and vegetables,1750,2400,1600
Gift card,150,2300,120
Kitchen,1000,3400,650
 

category,2026-11,2026-12,2027-01 Bread and pastry,1250,1550,1000 Cleaning,1150,1300,850 Dairy,2900,3700,2100 Drinks,1500,2000,1300 Fruit and vegetables,1750,2400,1600 Gift card,150,2300,120 Kitchen,1000,3400,650

Check▶ Run

Test

Check yourself

1. What does pivot_table work out if you do not give aggfunc?

The number of rowsThe sumThe mean

2. In the “customer × category” table, customer 3 bought no drinks, and the cell is NaN. When is fill_value=0 right?

Always, as long as the table is wideHere: NaN means “bought nothing”, and that is a real zeroNever: 0 is not “missing”

3. A table from Excel has the columns category, 2026-11, 2026-12 and 2027-01. What makes it long?

plan.unstack()plan.pivot_table(index='category')plan.melt(id_vars='category', var_name='month', value_name='plan')

4. What does margins=True add?

A percentage of the total in every cellAn All row and column with the totals of the rows and of the columnsEmpty space around the table

Check

Start the lesson over

Related lessons

• Builds on: New columns · Types and missing values · Groups: groupby · Joining: merge
• Continues in: The first chart · Which chart for which question · Honest charts · A/B test
• Tasks on this topic: Wrangling data