How do I reshape data between wide and long in pandas?

The short version

Use pivot_table to spread a category across columns (long to wide), and melt to stack columns back into rows (wide to long). Most analysis and plotting is easier in long format, one row per observation, so the common move is to melt a wide spreadsheet before working with it.

Long to wide with pivot_table

import pandas as pd

long = pd.DataFrame({
    "city": ["Leeds", "Leeds", "Bath"],
    "month": ["Jan", "Feb", "Jan"],
    "sales": [1, 2, 3],
})

wide = long.pivot_table(index="city", columns="month", values="sales", aggfunc="sum")
print(wide)

month  Feb  Jan
city
Bath   NaN  3.0
Leeds  2.0  1.0

index sets the rows, columns sets the new column headers, and values fills the cells. pivot_table also aggregates, so it copes when a city and month combination appears more than once. Plain pivot does not aggregate and will raise on duplicates, which is why pivot_table is the safer default.

Wide to long with melt

tidy = wide.reset_index().melt(id_vars="city", var_name="month", value_name="sales")
tidy = tidy.dropna()
print(tidy)

id_vars lists the columns to keep as they are, and everything else is unpivoted into two columns, one holding the old column names and one holding the values.

Why long format is worth the trouble

In long format, each variable has its own column, so groupby, filtering and plotting all become one-liners. Wide format is fine for a final report table a human reads, but it fights you during analysis because the information you want to group by is spread across column headers rather than sitting in a column.

The NaN gap after pivoting

Notice Bath has no February figure, so its cell is NaN. That is correct: there was no such row. Decide whether to leave it, fill it with fillna(0) if a missing month truly means zero sales, or drop it. Do not fill with zero automatically, because "no data" and "zero sales" are different claims.