Skip to content
Tutorials Center
My learning

Topics

Lesson 33 of 64 ยท Grouping and Reshaping

Reshaping: melt, stack, wide to long

Change the shape of a DataFrame between wide and long with melt, stack, unstack and wide_to_long, then explode lists and one-hot encode with get_dummies.

The same information can be arranged in more than one shape. A spreadsheet of exam results usually has one row per student and one column per subject. A database or a plotting library often wants one row per student per subject. Neither layout is "right"; each one suits certain jobs. Reshaping is the skill of moving between them quickly and without losing information.

In the last lesson you used pivot tables to go from long to wide while summarising. This lesson covers the opposite direction (melt, stack, wide_to_long) and a few related reshapes: explode for list-valued cells and get_dummies for turning categories into indicator columns.

Wide versus long

In a wide table the values of one variable are spread across several columns. In a long table every observation gets its own row, and one column says which variable it is.

flowchart LR
    W["Wide<br/>student | math | physics | art"] -- "melt / stack / wide_to_long" --> L["Long<br/>student | subject | score"]
    L -- "pivot / unstack" --> W

Wide to long collapses the subject columns into two columns (name and value). Long to wide spreads them out again.

Many analysis tools prefer a tidy layout, a term from statistics with three rules:

  1. Each variable is a column.
  2. Each observation is a row.
  3. Each kind of observation lives in its own table.

In the wide exam table, "subject" is a variable, but its values (math, physics, art) are stored as column names. That breaks the first rule, which is why groupby, plotting and joins are often awkward on it.

melt: wide to long

DataFrame.melt() keeps some columns as identifiers (id_vars) and turns all the others (or just value_vars) into rows. var_name names the new column that holds the old column labels, and value_name names the column that holds the cell values.

Python melt and back again

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

Look at what happened to the row count: the wide table had 3 rows and the long table has 9 (3 students times 3 subjects). The data is the same, but the long table repeats the student name. That repetition is the price of the shape; it is also what lets you group, filter and plot by subject with one column.

When you omit value_vars, every column that is not in id_vars is melted. When you omit both, every column is melted. If the id columns live in the index instead, use df.reset_index() first, or call melt(ignore_index=False) to keep the original index on the result.

stack and unstack

stack() is melt's index-based cousin. It takes the column labels and pushes them into a new, innermost level of the row index, returning a Series. unstack() does the reverse. They are the natural tools when your identifier columns are already in the index (for example after set_index).

In pandas 3 stack() keeps missing values, so a wide table with n rows and m columns always gives n * m entries. Older versions silently dropped NaN rows by default; if you read older code, remember that difference.

Python stack, unstack and wide_to_long

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

pd.wide_to_long() handles a very common pattern: several measurements repeated per year (or month, or trial), named stub + separator + suffix. You give it the stubnames (score, hours), the identifier columns i, the name for the new column j (the suffix values go there), and the separator. It melts each family of columns and aligns them, so score and hours stay side by side. Doing the same by hand takes a melt per family plus a merge, so this function saves a lot of work.

The result of wide_to_long has a MultiIndex made of i and j. Call reset_index() to move them back into regular columns.

melt works on columns and returns a DataFrame with a fresh index. stack works on the index machinery and returns a Series with a MultiIndex. Choose melt when the identifiers are normal columns, and stack when you are already working with a MultiIndex.

explode: one row per list item

Sometimes a cell holds a list, for example the tags of a blog post. To count tags or group by tag, you need one row per tag. explode() does exactly that: it repeats the other columns for each list item.

Empty lists become a single row with NaN, and the original index label is repeated unless you pass ignore_index=True. You can create list cells by splitting strings with .str.split(",") (see Working with Text) and then exploding the result.

get_dummies: one-hot encoding

Machine learning models and some statistical routines need numbers, not category labels. One-hot encoding replaces a column with one indicator column per category: 1 when the row has that category and 0 otherwise. pd.get_dummies() does this.

By default the indicators are booleans (True/False). Pass dtype=int for 0 and 1. drop_first=True removes one category per column; that avoids perfectly redundant columns (the "dummy variable trap") in linear models, because the dropped category is implied when all other indicators are zero.

Python explode and get_dummies

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

When you pass a whole DataFrame with columns=[...], only those columns are encoded and the rest (here item and size) are kept. Without columns, pandas encodes every string or categorical column it finds, which is rarely what you want. For a single Series, get_dummies returns just the indicator columns.

Keep one caution in mind: encode after you split your data into training and test sets, or use categories fixed in advance, otherwise a category that appears only in new data produces a column your model has never seen.

Choosing a shape

Task Better shape
Reading by eye, reports, spreadsheets Wide
groupby, filtering by variable, joins Long
Plotting many series with one call Long (or wide with one column per line, depending on the library)
Storing data in a database Long
Correlating variables with each other Wide

It is normal to reshape back and forth inside one analysis: melt to compute with groupby, pivot to display.

Check your understanding

Quick check ยท 5 questions

of 5 answered You scored / 5 ยท perfect!

  1. 1. A DataFrame has the columns id, jan, feb and mar (sales per month). Which call gives one row per id and month?

    melt keeps id as an identifier and turns the jan, feb and mar columns into rows, with var_name holding the old column labels and value_name holding the cell values.
  2. 2. What does explode do with a cell that holds the list ["a", "b"]?

    explode produces one row for every list item and copies the values of the other columns. An empty list yields one row with NaN.
  3. 3. Which columns does pd.wide_to_long need for score_2023 and score_2024?

    wide_to_long parses names of the form stub + separator + suffix. You supply the stubs, the identifier columns and the name of the new column that receives the suffixes.
  4. 4. What does drop_first=True do in get_dummies?

    With k categories you only need k-1 indicators; the dropped category is the case where all remaining indicators are 0. It avoids redundant columns in linear models.
  5. 5. In pandas 3, what happens to NaN values when you call stack()?

    pandas 3 uses the newer stack implementation, which keeps missing values. A table with n rows and m columns always becomes n * m entries.

Summary

  • Wide tables spread a variable across columns; long (tidy) tables give each observation a row. Choose the shape that suits the next step.
  • melt(id_vars, value_vars, var_name, value_name) turns columns into rows; pivot goes back.
  • stack() and unstack() move column labels into and out of the row index; pandas 3 keeps NaN when stacking.
  • wide_to_long melts families of columns named stub_suffix in one call and keeps them aligned.
  • explode gives one row per list item, and get_dummies creates one indicator column per category (use dtype=int and drop_first as needed).

Next: Concatenating DataFrames