Wide to Long with melt
Overview
Turning a column per month into a column that says which month.
What You Will Learn in This Lesson
By the end of this lesson, you will be able to:
- Say what wide and long mean.
- Melt a set of columns down into two.
- Choose what stays put with
id_vars. - Predict the row count after a melt.
Why This Matters
A spreadsheet is usually wide — a column per month — because that is readable. Every pandas operation you have learned wants long: one row per observation, with the thing that varies in a column of its own. You cannot group by month when month is twelve column headings.
Step 1: Why long is easier to work with
In a wide table the month is metadata — it lives in the column name, where no operation can reach it. In a long table it is data.
The same numbers, two shapes
Wide is easier to read; long is the only one you can group, filter or plot by month.
import pandas as pd
wide = pd.DataFrame({"name": ["Ada", "Bo"], "jan": [90, 70], "feb": [80, 60]})
print(wide)
print()
print(wide.melt(id_vars=["name"], var_name="month", value_name="score"))And now the question is answerable
Grouping by month is impossible in the wide shape and one line in the long one.
import pandas as pd
wide = pd.DataFrame({"name": ["Ada", "Bo"], "jan": [90, 70], "feb": [80, 60]})
long = wide.melt(id_vars=["name"], var_name="month", value_name="score")
print(long.groupby("month")["score"].mean())Mini Practice #1: Melt and group
Try It YourselfMelt this table and then take the mean score per month — the question the wide shape cannot answer.
Step 2: id_vars are what stays put
Everything named in id_vars is repeated down the rows; everything else is folded into the two new columns.
Naming the new columns
var_name is where the old column headings go; value_name is where their values go. Naming both is worth the keystrokes.
import pandas as pd
wide = pd.DataFrame({"name": ["Ada"], "jan": [90], "feb": [80]})
print(wide.melt(id_vars=["name"]))
print()
print(wide.melt(id_vars=["name"], var_name="month", value_name="score"))The row count is predictable
Rows times melted columns. Checking it catches an id_vars list that accidentally left something out.
import pandas as pd
wide = pd.DataFrame({"name": ["Ada", "Bo"], "jan": [90, 70], "feb": [80, 60]})
long = wide.melt(id_vars=["name"], var_name="month", value_name="score")
print(len(wide), "rows x 2 months =", len(long))Forget to list a column in id_vars and it gets melted too, which usually shows up as a row count twice what you predicted.
Mini Practice #2: Predict the row count
Try It YourselfThree people, four months. Say how many rows the melt gives before you run it.
Step 3: When to Use It
A column per period
Spreadsheets with jan, feb, mar columns become one month column and one value column.
df.melt(id_vars=["name"], var_name="month", value_name="score")Before grouping or charting
Long tables group, filter and plot directly; wide ones need a column per operation.
When Not to Use It
Do not melt the identifying columns. Anything that says which row this is belongs in id_vars, or it is turned into values and lost.
End-of-Lesson Exercises
Exercise 1: Melt the months down
Write to_long(df) that melts a table with a name column and one column per month into columns name, month, score, and returns the row count.
id_vars=["name"] keeps the name and folds every other column down. The row count is people times months.
Exercise 2: The best month, once it is a column
Write best_month(df) that melts a wide table with name and a column per month, and returns the month with the highest mean score. This is the question the wide shape cannot answer at all.
Melt with id_vars=["name"], group by the month column, take the mean of the score column, then idxmax for the label.