How do I reshape columns like sales_2020 and sales_2021 into rows in pandas?

The short version

When several columns share a common stub and differ only by a suffix, like sales_2020 and sales_2021, use pd.wide_to_long. It gathers each stub's columns into a single value column and puts the suffix into its own column. This is the right tool when plain melt would need a lot of manual tidying.

A worked example

import pandas as pd

wide = pd.DataFrame({
    "city": ["Leeds"],
    "sales_2020": [10],
    "sales_2021": [12],
})

long = pd.wide_to_long(
    wide, stubnames="sales", i="city", j="year", sep="_"
).reset_index()
print(long)

    city  year  sales
0  Leeds  2020     10
1  Leeds  2021     12

stubnames is the shared prefix, i is the column that identifies each row, j names the new column that will hold the suffix, and sep is the character between stub and suffix.

Why not just melt

melt can do this, but it turns sales_2020 into a single text label you then have to split into sales and 2020 yourself. wide_to_long understands the stub-and-suffix structure directly, so it separates them for you in one call. When your columns follow a clean stub_suffix naming pattern, wide_to_long is much less fiddly.

Handling several stubs at once

If you have two families of columns, say sales_2020, sales_2021 and cost_2020, cost_2021, pass a list of stub names and it lines them all up against the same suffix.

pd.wide_to_long(wide, stubnames=["sales", "cost"], i="city", j="year", sep="_")

This produces one sales and one cost column, both indexed by year, which would be awkward to achieve with melt.

The naming requirement

wide_to_long relies on the column names following the stub then sep then suffix pattern exactly. If some columns break the pattern, they are left alone or cause confusion, so rename inconsistent columns first. The suffix also becomes the j column's values, so a numeric suffix like a year comes through as usable numbers.

When the suffix is not numeric

The suffix does not have to be a year or a number. It can be any consistent token, such as sales_q1, sales_q2, and j then holds q1, q2. Set suffix=r"\w+" if the default numeric-only suffix matching misses non-numeric suffixes.

The bigger picture

This is a specialised reshaper for a very common real-world layout, spreadsheets where a measure is repeated across time periods as separate columns. Converting to long form first, one row per city per year, then makes grouping, filtering and plotting straightforward, which is why the reshape is usually worth doing before analysis.