Pivot Tables and Reshaping

pivot_table, melt, crosstab

Data is long (one row per observation: month, city, revenue) or wide (one column per city). pivot_table goes from long to wide with an aggregation, so duplicate entries (two Pune rows in February) are summed. melt goes back from wide to long, which most plotting and modelling tools prefer. crosstab counts combinations of two categories.

Long to wide and back, run

I ran this with Python 3.12.3 and pandas 3.0.6. The pivot sums February Pune revenue (60 + 40 = 100); melt returns to long format; crosstab counts rows per month and city (sorted alphabetically).

import pandas as pd

df = pd.DataFrame({
    "month": ["Jan", "Jan", "Feb", "Feb", "Feb"],
    "city": ["Delhi", "Pune", "Delhi", "Pune", "Pune"],
    "revenue": [100, 80, 120, 60, 40],
})
wide = df.pivot_table(index="month", columns="city", values="revenue",
                      aggfunc="sum", sort=False)
print(wide)
long = wide.reset_index().melt(id_vars="month", var_name="city", value_name="revenue")
print(long)
print(pd.crosstab(df["month"], df["city"]))

Output:

city   Delhi  Pune
month             
Jan      100    80
Feb      120   100
  month   city  revenue
0   Jan  Delhi      100
1   Feb  Delhi      120
2   Jan   Pune       80
3   Feb   Pune      100
city   Delhi  Pune
month             
Feb        1     2
Jan        1     1

Store long, present wide

Keep data long for processing and pivot to wide only for reports.

त्वरित जाँच: What happens to duplicate (month, city) pairs in pivot_table?

• They are combined with the aggregation function
• They raise an error
• Only the first is kept
• They become new columns