# Pivot Tables and Reshaping — NumPy / Pandas / scikit-learn

Source: https://www.skillbyai.com/en/numpy-pandas-sklearn/g-pivot

> Long and wide formats.

## 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).

```python
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.

**Quiz:** What happens to duplicate (month, city) pairs in pivot_table?

- [x] They are combined with the aggregation function
- [ ] They raise an error
- [ ] Only the first is kept
- [ ] They become new columns

*Answer:* They are combined with the aggregation function. pivot_table aggregates; pivot would fail.