Reshaping Tables

The finance team sends over a spreadsheet, with one column for every month. It looks tidy to a person. But try to plot it, or group it, and pandas fights you all the way. The data is fine. It's just the wrong shape. Learning to reshape a table is one of those small skills that saves you hours every single week.

• One column for every month
• Tidy to a person
• The wrong shape for a computer

Wide and long

Every table comes in two shapes. Wide tables spread values across many columns, which is good for reading. Long tables have one row for each measurement, which is good for computing. Pandas, SQL, and nearly every chart library want long data. So the usual routine is, receive it wide, convert it to long, do the analysis, and then go wide again for the final report.

Melt and pivot

Here's that finance sheet. Melt turns it into long format. We say which column identifies a row, and what to call the new columns. Run it. Three month columns have become one month column, and we've got six rows instead of two. Now you can group by month, or plot a line for each city, in one short line. Pivot goes the other way. Notice that we pick the month order by hand, because otherwise pandas would sort the months alphabetically, and February would come first.

import pandas as pd

wide = pd.DataFrame({"city": ["Oslo", "Lima"], "Jan": [120, 90], "Feb": [130, 85], "Mar": [150, 95]})
print(wide.to_string(index=False))

long = wide.melt(id_vars="city", var_name="month", value_name="sales")
print(long.to_string(index=False))

back = long.pivot(index="city", columns="month", values="sales")[["Jan", "Feb", "Mar"]]
print(back.to_string())

city  Jan  Feb  Mar
Oslo  120  130  150
Lima   90   85   95
city month  sales
Oslo   Jan    120
Lima   Jan     90
Oslo   Feb    130
Lima   Feb     85
Oslo   Mar    150
Lima   Mar     95
month  Jan  Feb  Mar
city
Lima    90   85   95
Oslo   120  130  150

Pivot tables that answer questions

A pivot table is a summary with two dimensions. Here, profit by region and year, with totals added by margins. Below that, crosstab with normalize gives shares, so each row adds up to one. Run it. Every region grew, and Europe is the largest. Now look at the second table. Each region has exactly the same mix of coffee, food, and tea. In real life, that would be suspicious, and it would be worth asking why. Here, it's because this dataset is simulated. Noticing things like that is a real skill.

import pandas as pd

sales = pd.read_csv("sales.csv", parse_dates=["date"])
sales["year"] = sales["date"].dt.year

table = sales.pivot_table(index="region", columns="year", values="profit",
                          aggfunc="sum", margins=True, margins_name="Total")
print((table / 1000).round(0).to_string())

shares = pd.crosstab(sales["region"], sales["category"], values=sales["revenue"],
                     aggfunc="sum", normalize="index")
print(shares.round(2).to_string())

year        2024    2025   Total
region
Africa     492.0   566.0  1058.0
Americas   346.0   396.0   742.0
Asia       425.0   492.0   916.0
Europe     685.0   790.0  1475.0
Total     1948.0  2244.0  4192.0
category  Coffee  Food   Tea
region
Africa      0.66  0.28  0.06
Americas    0.66  0.28  0.06
Asia        0.66  0.28  0.06
Europe      0.66  0.28  0.06

Summary