Reshaping

# pivot(index, columns, values)
df.pivot(index="date", columns="city", values="temp")

# Multiple value columns
df.pivot(index="date", columns="city", values=["temp", "humidity"])

# Result: MultiIndex columns if multiple values

pd.pivot_table(
    df,
    values="sales",
    index="region",
    columns="year",
    aggfunc="sum",          # "sum"|"mean"|"count"|callable
    fill_value=0,           # replace NaN in result
    margins=True,           # add "All" row/column totals
    margins_name="Total",
)

# Multiple values and multiple aggfuncs
pd.pivot_table(
    df,
    values=["sales", "cost"],
    index="region",
    columns="year",
    aggfunc={"sales": "sum", "cost": "mean"},
)

# On the instance
df.pivot_table(index="dept", columns="level", values="salary", aggfunc="mean")

df.melt()
df.melt(id_vars=["id", "name"])         # keep these as identifier columns
df.melt(
    id_vars=["id"],
    value_vars=["jan", "feb", "mar"],   # only unpivot these columns
    var_name="month",                   # name for the new "variable" column
    value_name="sales",                 # name for the new "value" column
)

# pd.melt() is the standalone function, same interface
pd.melt(df, id_vars=["id"], value_vars=["a", "b"])

# stack(): moves the innermost column level into the row index
df.stack()                  # columns → innermost row level
df.stack(level=-1)          # same (default)
df.stack(level=0)           # move outermost column level
df.stack(future_stack=True) # use new pandas 2.x behavior (no NaN rows dropped)

# unstack(): moves the innermost row index level into columns
df.unstack()                # innermost row level → columns
df.unstack(level=0)         # move outermost row level
df.unstack("city")          # by level name

# Common pattern: stack then groupby
df.stack().groupby(level=0).mean()

# Columns: sales_2022, sales_2023, cost_2022, cost_2023
pd.wide_to_long(
    df,
    stubnames=["sales", "cost"],
    i="id",
    j="year",
    sep="_",
)

# Each element in a list becomes its own row
df["tags"].explode()
df.explode("tags")               # in-place column
df.explode("tags").reset_index(drop=True)

# Explode multiple columns simultaneously (pandas ≥ 1.3)
df.explode(["col_a", "col_b"])

pd.crosstab(df["gender"], df["dept"])                    # counts
pd.crosstab(df["gender"], df["dept"], normalize=True)    # proportions
pd.crosstab(df["gender"], df["dept"], normalize="index") # row %
pd.crosstab(df["gender"], df["dept"], normalize="columns") # col %
pd.crosstab(df["gender"], df["dept"], values=df["salary"], aggfunc="mean")
pd.crosstab(df["gender"], df["dept"], margins=True)      # add totals

pd.get_dummies(df["city"])
pd.get_dummies(df, columns=["city", "dept"])  # encode multiple cols
pd.get_dummies(df, columns=["city"], drop_first=True)   # drop one level (avoid multicollinearity)
pd.get_dummies(df, columns=["city"], dtype=int)         # 0/1 instead of bool
pd.get_dummies(df, columns=["city"], prefix="loc", prefix_sep="_")

# After pivot/pivot_table, flatten column MultiIndex to strings
df.columns = ["_".join(map(str, col)).strip("_") for col in df.columns]

# Flatten index
df.reset_index()

# Named levels
df.index.get_level_values("year")
df.index.get_level_values(0)

df.T          # swap rows and columns
df.transpose()

# Gotcha: if columns have mixed types, transposing converts all to object dtype

# Long format (tidy): one row per observation — preferred for groupby/merge
# Wide format: one row per entity, columns encode variables — good for display

# Wide → Long (melt)
tidy = df.melt(id_vars=["id", "country"], var_name="year", value_name="gdp")
tidy["year"] = tidy["year"].astype(int)

# Long → Wide (pivot)
wide = tidy.pivot(index="country", columns="year", values="gdp")