Level 3 · Turn the budget sheet into a long table

Why wide sheets need melting

People build spreadsheets wide: one row per period, one column per channel, because that’s easy to read and type. Code prefers them long: one row per period and channel, with the channel stored as a value in a column. A long table can be grouped by channel, merged with actual spend on quarter and channel, filtered with == "paid" and charted with one line per channel. A wide one needs a different line of code for every column.

melt is the tool that goes from wide to long. It’s the reverse of pivot_table.

The syntax

wide.melt(
    id_vars="quarter",     # columns that identify a row and stay as they are
    var_name="channel",    # name for the column that will hold the old column names
    value_name="budget",   # name for the column that will hold the cell values
)

Every column not listed in id_vars is melted. If you only want some of them, list those in value_vars=[...]. Without var_name and value_name, pandas calls the new columns variable and value, which you’d only rename afterwards.

The row count is easy to predict: rows × melted columns. A 4-row sheet with 3 channel columns becomes 12 rows. Check it with len() after melting.

A worked example: spend by year

Here is yearly marketing spend as a wide grid, the kind you’d get from a pivot or a spreadsheet:

spend = marketing_spend.assign(year=marketing_spend["spend_date"].dt.year)
wide = spend.pivot_table(index="year", columns="channel",
                         values="amount", aggfunc="sum").round(2).reset_index()
# channel  year     email       paid     social
# 0        2023  28989.02  253948.40   99253.03
# ...

long = wide.melt(id_vars="year", var_name="channel", value_name="amount")
#    year channel     amount
# 0  2023   email   28989.02
# 1  2024   email   32876.47
# 2  2025   email   38322.22
# 3  2023    paid  253948.40
# ...  (9 rows)

Notice the .reset_index() before melting: melt only sees ordinary columns, so year must be a column, not the index, to be kept as an identifier.

Row order after a melt

melt stacks the melted columns one after another: all the email rows, then all the paid rows, then social. That’s rarely the order you want to show, so sort afterwards and reset the index:

long.sort_values(["year", "channel"]).reset_index(drop=True)

Common mistakes

• Forgetting id_vars. The identifier gets melted along with the measures and its labels end up in the value column.
• Melting a table whose identifier is still the index. Call .reset_index() first.
• Leaving the default variable and value names, which mean nothing to the next reader.
• Expecting the original row order. Melted rows come grouped by the old column; sort them.