pandas · Chapter 17 of 22

Reshaping: melt, pivot and the MultiIndex

By the end of this chapter you can tell a wide table from a long one, turn wide into long with melt and back with pivot, read and select a MultiIndex, move a level between the rows and the columns with unstack and stack, rearrange levels, and flatten a table back to plain columns. Build it in your browser: a real editor and terminal, AI that explains, and tests that tell you when it works.

  • Part: Grouping and combining
  • 4 tasks
  • pandas
  • In the all labs plan

The first 2 chapters of pandas are free. This one comes with the all labs plan.

Why you need this

Harpreet keeps the year's sales targets the way people read them: one row a category, one column a month.

import pandas as pd

targets = pd.read_csv("/workspace/data/targets_wide.csv")
print(targets.shape)
print(targets.iloc[:, :4])
(4, 13)
  category   2025-10   2025-11  2025-12
0    saree  12710000  10520000  9840000
1  dupatta   1720000   1280000  1590000
2    stole    490000    750000   780000
3    kurta   1690000   1280000  1180000

It reads well: saree's October target is ₹1.27 crore, one glance. Now Shabnam asks how each category did against its target, month by month. The sales are one row a line, with the month as a value in a column (chapter 14 took it from placed_at). In the targets the month is a column name, and there is no month column to join on:

site = pd.read_csv("/workspace/data/orders_site.csv")
sales = site.groupby(site["placed_at"].str.slice(0, 7))["price_rs"].sum()
sales = sales.reset_index().rename(columns={"placed_at": "month"})
pd.merge(sales, targets, on="month")
KeyError: 'month'

With what you know, you would select each month's column, give it a month column of its own, and concat twelve pieces: it works, and it is twelve steps for what is one idea, "the month names are data". The same thing happens the other way round: chapter 12's groupby on two keys gives a long result with two-part labels, and Shabnam wants to read it as a grid. This chapter is the tools that change a table's shape without changing what it says.

What you build: 4 tasks

Each task has tests. You write the code in the lab's editor and press Check.

  1. Task 1

    targets_long.py: the targets as a long table

    Write targets_long(targets) in targets_long.py. targets is Harpreet's wide table as read_csv reads targets_wide.csv: a column category, then one column a month, "2025-10" to "2026-09", each holding that month's target in rupees. Return it as a long table, one row for each category and month.

    The result has exactly the columns category, month and target_rs, in that order. month is the month column's name, as text ("2025-10"); target_rs is the target, a whole number. The rows are every category for the first month (in the table's order), then every category for the next month, and so on, labelled 0, 1, 2 and so on: 48 rows for the real file.

    The Check also tries a table of 2 categories by 3 months, and 1 category by 1 month.

  2. Task 2

    achieved.py: each month against its target

    Write against_target(sales, targets) in achieved.py. sales has one row a website line, with the columns city, category, month (text, "2025-10") and line_rs (the line's amount, a decimal number); targets is the wide table of Task 1, as read_csv reads targets_wide.csv.

    Return a DataFrame with exactly the columns category, month, target_rs, actual_rs and achieved, in that order: one row for each category and month of the targets, in the order of Task 1 (every category for the first month, then the next), labelled 0, 1, 2 and so on. target_rs is the target; actual_rs is the sum of line_rs for that category in that month, a decimal number, and 0 when there is none; achieved is actual_rs divided by target_rs (0.5 is half the target).

    A category and month with no sales is still a row, with 0. Sales of a category or a month that has no target are left out. The Check tries the year's delivered lines with the real targets, and 6 small lines (one of a category with no target) with 2 categories by 3 months.

  3. Task 3

    city_table.py: a city by category table

    Write two functions in city_table.py. sales has one row a line, with the columns city, category, month and line_rs.

    city_by_category(sales) returns a DataFrame labelled by city, in alphabetical order, with one column for each category that appears in sales, in alphabetical order. Each cell is the sum of line_rs for that city and category, a decimal number, and 0 where the city sold none of that category (not a missing value).

    one_category(revenue, category) is given revenue, a Series with two levels of labels, city then category, as a groupby on those two columns gives the sum of line_rs, and one category as text. It returns a Series of that category's revenue labelled by city alone (one level of labels), in revenue's order, with only the cities that have that category.

    The Check tries the year's delivered lines (and the categories stole and kurta), and 6 small lines where Jaipur sold only a shawl, so Jaipur's other cells must be 0.

  4. Task 4 · interview question

    Interview: year_targets.py, a year of targets

    Harpreet's year_targets(targets) should give each category's target for the whole year, the sum of its month columns, from the wide table as read_csv reads targets_wide.csv. It stops with TypeError: can only concatenate str (not "int") to str. Fix it so it returns a Series labelled by category, in the table's order (saree, dupatta, stole, kurta for the real file), each value the category's yearly target as a whole number. The Check also tries a table of 2 categories by 3 months.

    Devika will ask: why did the sum try to add text to numbers, and when is the wide form the right shape for a table, and when is the long form?

What you will learn

  • Wide and long: the same facts as a grid, or as one row a fact.
  • melt: wide to long (id_vars, var_name, value_name).
  • pivot: long to wide again.
  • A MultiIndex: labels in levels, from a groupby on two keys; get_level_values, .loc with a tuple, xs.
  • unstack and stack: move a level from the rows to the columns and back, with fill_value.
  • swaplevel, sort_index and reorder_levels; flattening column names; reset_index as the plain way out.

More in Grouping and combining

Build "Reshaping: melt, pivot and the MultiIndex" yourself

Kargha, a Varanasi handloom brand, sells through a website, a marketplace and a store. You make one true picture of its sales. A real editor and terminal in your browser, AI that explains, and a Check that says when it works.

The first 2 chapters of pandas are free. This one comes with the all labs plan.