pivot_table / melt

import pandas as pd
import numpy as np

# 1) Sample data — sales by region by quarter
sales = pd.DataFrame({
    'region':  ['NSW', 'NSW', 'VIC', 'VIC', 'QLD', 'QLD', 'NSW', 'VIC'],
    'quarter': ['Q1', 'Q2', 'Q1', 'Q2', 'Q1', 'Q2', 'Q1', 'Q1'],
    'amount':  [100, 150, 80, 90, 60, 70, 25, 10],
})

# 2) pivot — strict; errors on duplicates
sales_unique = sales.drop_duplicates(subset=['region', 'quarter'])
sales_unique.pivot(index='region', columns='quarter', values='amount')
# quarter   Q1    Q2
# region
# NSW       100   150
# VIC        80    90
# QLD        60    70

# pivot fails on duplicates:
# sales.pivot(index='region', columns='quarter', values='amount')
# → ValueError: Index contains duplicate entries

# 3) pivot_table — aggregates duplicates
sales.pivot_table(index='region', columns='quarter', values='amount', aggfunc='sum')
# Same shape, but NSW Q1 = 125 (100 + 25), VIC Q1 = 90 (80 + 10)

sales.pivot_table(index='region', columns='quarter', values='amount', aggfunc='mean')
sales.pivot_table(index='region', columns='quarter', values='amount', aggfunc=['sum', 'mean'])

# Multiple aggregations + multiple value columns
sales.pivot_table(
    index='region',
    columns='quarter',
    values='amount',
    aggfunc={'amount': ['sum', 'mean', 'count']},
    margins=True,                            # add totals row + column
    margins_name='Total',
    fill_value=0,                             # replace NaN with 0
)

# 4) Hierarchical columns + rows
sales = pd.DataFrame({
    'region':  ['NSW']*4 + ['VIC']*4,
    'channel': ['retail','retail','online','online']*2,
    'quarter': ['Q1','Q2','Q1','Q2']*2,
    'amount':  [100, 150, 50, 75, 80, 90, 40, 55],
})
sales.pivot_table(
    index=['region', 'channel'],
    columns='quarter',
    values='amount',
    aggfunc='sum',
)
# quarter            Q1   Q2
# region  channel
# NSW     online      50   75
#         retail     100  150
# VIC     online      40   55
#         retail      80   90

# 5) crosstab — frequency / cross-tabulation
survey = pd.DataFrame({
    'gender':   ['M','M','F','F','M','F','F','M','F','M'],
    'response': ['yes','no','yes','yes','no','yes','no','yes','yes','no'],
})
pd.crosstab(survey['gender'], survey['response'])
# response  no  yes
# gender
# F           1   4
# M           2   3

# Normalize to percentages
pd.crosstab(survey['gender'], survey['response'], normalize='index')
pd.crosstab(survey['gender'], survey['response'], normalize='columns')
pd.crosstab(survey['gender'], survey['response'], normalize=True)             # over total

# With margins (row/column totals)
pd.crosstab(survey['gender'], survey['response'], margins=True, margins_name='Total')

# 6) melt — wide → long (inverse of pivot)
wide = pd.DataFrame({
    'name':   ['Mara', 'Sam'],
    'jan':    [100, 200],
    'feb':    [150, 180],
    'mar':    [200, 220],
})
wide.melt(id_vars='name', var_name='month', value_name='sales')
#    name month  sales
# 0  Mara   jan    100
# 1   Sam   jan    200
# 2  Mara   feb    150
# 3   Sam   feb    180
# ...

# Multiple ID columns
wide.melt(
    id_vars=['name', 'region'],
    value_vars=['jan', 'feb', 'mar'],
    var_name='month',
    value_name='sales',
)

# 7) stack + unstack — multi-index reshape
df = sales.pivot_table(index='region', columns='quarter', values='amount', aggfunc='sum')
df.stack()                                  # columns → inner row index
df.stack().unstack()                         # inverse
df.stack().unstack(level='quarter')          # explicit level

# 8) Computed columns inside pivot
sales['high_value'] = sales['amount'] > 75
sales.pivot_table(
    index='region',
    columns='high_value',
    values='amount',
    aggfunc='count',
    fill_value=0,
)

# 9) Custom aggregation functions
def range_size(x): return x.max() - x.min()

sales.pivot_table(
    index='region', columns='quarter', values='amount',
    aggfunc=range_size,
)

# Lambdas
sales.pivot_table(
    index='region', columns='quarter', values='amount',
    aggfunc=lambda x: x.quantile(0.9),
)

# 10) Pivot for time-series (date → columns)
ts = pd.DataFrame({
    'date':   pd.to_datetime(['2024-01-01','2024-01-02','2024-01-01','2024-01-02']),
    'metric': ['users','users','revenue','revenue'],
    'value':  [100, 110, 5000, 5500],
})
ts.pivot_table(index='date', columns='metric', values='value')
# metric      revenue  users
# date
# 2024-01-01    5000.0  100.0
# 2024-01-02    5500.0  110.0

# 11) Pivot then plot
import matplotlib.pyplot as plt
wide = sales.pivot_table(index='region', columns='quarter', values='amount', aggfunc='sum')
wide.plot.bar(stacked=True)
plt.tight_layout()

# 12) Performance tips
# • For huge data, prefer groupby + unstack over pivot_table (it's a thin wrapper anyway)
# • Use observed=True with categorical columns to avoid materialising unused categories
# • Sort beforehand for repeatable column ordering
# • Polars / DuckDB outperform pandas for very large pivots

# 13) Common bugs
# • pivot fails on duplicates → use pivot_table with aggfunc
# • Wrong aggfunc → silent miscount; 'sum' counts None as 0, 'count' ignores NaN
# • Missing fill_value → NaN columns where no data
# • Long-format columns containing nulls → silently dropped from index
# • Stack/unstack on MultiIndex with NaN level → 'Series have different lengths'
# • melt with var_name reserved word → name collision; pick distinct names
# • crosstab vs pivot_table for frequency — both work; crosstab is shorter for plain counts
# • Multi-aggfunc returns MultiIndex columns; flatten with df.columns.map('_'.join)

import pandas as pd
pivot = df.pivot_table(
    index='month', columns='product',
    values='amount', aggfunc='sum', fill_value=0,
)
long = pivot.reset_index().melt(id_vars='month')