melt(): Wide to Long

Scorecards and spreadsheets are often wide: one column per season. That looks nice, but most pandas tools want long data — you cannot easily groupby("season") when seasons are column names instead of values. melt() "unpivots" the table: it turns those column headers into values of a new column.

wide = pd.DataFrame({
    "player": ["Kohli", "Rohit", "Gill"],
    "2022":   [341, 268, 483],
    "2023":   [639, 332, 890],
    "2024":   [741, 417, 426],
})
print(wide)

  player  2022  2023  2024
0  Kohli   341   639   741
1  Rohit   268   332   417
2   Gill   483   890   426

Definition

melt() reshapes a DataFrame from wide to long format: the id_vars columns are kept as identifiers, and every value_vars column is "unpivoted" into two columns — one holding the old column name (the variable) and one holding the cell value (the value). It is the inverse of pivot().

• id_vars — identifier columns that stay as they are and get repeated for every melted column (player).
• value_vars — the columns to unpivot (2022, 2023, 2024). Their names become data.
• variable / value — the two new columns; rename them with var_name and value_name.
• Tidy data — the long result follows the "tidy" rule: each variable is a column, each observation is a row.

Melt diagram

 WIDE (3 rows x 4 cols)                      LONG (9 rows x 3 cols)
 player  2022  2023  2024                    player  season  runs
 Kohli    341   639   741   ---melt--->      Kohli   2022    341
 Rohit    268   332   417   id_vars=player   Rohit   2022    268
 Gill     483   890   426   var_name=season  Gill    2022    483
          \____ ____/        value_name=runs  Kohli   2023    639
               |                             ...     ...     ...
     these 3 column NAMES become values      Gill    2024    426
     of the new "season" column
 rows after melt = rows (3) x melted columns (3) = 9

Syntax

df.melt(id_vars=None, value_vars=None, var_name=None, value_name="value", ignore_index=True)
pd.melt(df, id_vars=..., ...)     # same thing as a function

Example — melt the seasons

long = wide.melt(id_vars="player", var_name="season", value_name="runs")
print(long)
print(long.dtypes)

  player season  runs
0  Kohli   2022   341
1  Rohit   2022   268
2   Gill   2022   483
3  Kohli   2023   639
4  Rohit   2023   332
5   Gill   2023   890
6  Kohli   2024   741
7  Rohit   2024   417
8   Gill   2024   426
player      str
season      str
runs      int64
dtype: object

How it works: pandas walks through the value columns one at a time — first all players for 2022, then for 2023, then 2024 — writing the column name into season and the cell into runs. Note that season has dtype str: the values came from column names ("2022"), which are text. Convert with long["season"] = long["season"].astype(int) before doing maths or sorting numerically.

Example — default names and choosing value_vars

print(wide.melt(id_vars="player").head(4))
print(wide.melt(id_vars="player", value_vars=["2023", "2024"], var_name="season", value_name="runs"))

  player variable  value
0  Kohli     2022    341
1  Rohit     2022    268
2   Gill     2022    483
3  Kohli     2023    639
  player season  runs
0  Kohli   2023   639
1  Rohit   2023   332
2   Gill   2023   890
3  Kohli   2024   741
4  Rohit   2024   417
5   Gill   2024   426

Without var_name / value_name you get the generic variable / value. With value_vars, only the listed columns are melted and any column in neither list is dropped.

Example — several id columns, mixed stats

w2 = pd.DataFrame({
    "player": ["Kohli", "Bumrah"], "team": ["RCB", "MI"],
    "runs": [741, 15], "wickets": [0, 20],
})
print(w2.melt(id_vars=["player", "team"], var_name="stat", value_name="value"))

   player team     stat  value
0   Kohli  RCB     runs    741
1  Bumrah   MI     runs     15
2   Kohli  RCB  wickets      0
3  Bumrah   MI  wickets     20

Why long format? — melt then analyse

long["season"] = long["season"].astype(int)
print(long.groupby("season")["runs"].sum())

season
2022    1092
2023    1861
2024    1584
Name: runs, dtype: int64

Once seasons are values, every tool works: groupby, filtering (long[long["season"] >= 2023]), merging, and plotting libraries like seaborn (which expect long data, e.g. hue="season").

Round trip: melt ↔ pivot

back = long.pivot(index="player", columns="season", values="runs")
print(back)

season  2022  2023  2024
player
Gill     483   890   426
Kohli    341   639   741
Rohit    268   332   417

pivot is the exact reverse of melt (rows come back sorted by player, and the player/season labels are now the index/columns names).

Common mistakes

• ❌ Forgetting id_vars — the player names get melted too and you lose who scored what. ✅ Always list the identifier columns.
• ❌ Doing maths on the melted season column while it is str. ✅ astype(int) (or pd.to_datetime for dates) after melting.
• ❌ Expecting melt to keep columns that are in neither id_vars nor value_vars. ✅ They are dropped — add them to id_vars.
• ❌ Confusing melt's order: it is grouped by column, not by player. ✅ sort_values(["player", "season"]) if you want each player together.

Interview tip

Q: How do you convert a table with one column per month into a long table? A: df.melt(id_vars=["id_col"], var_name="month", value_name="amount"). id_vars are repeated, the month column names go into month and the cells into amount. pivot reverses it.