Skip to content

#253: Unpivot DataFrames in Pandas With Melt

The pivot method of Pandas is a great help to turn our DataFrame into a pivot table. But what can we do if we need to do the reverse and combine multiple columns into one column for a specific type?

The problem

My DataFrame had this structure:

Date JetPack Statify WP Statistics
2024-10-29 347 499 493
2024-10-30 362 480 476
2024-10-31 251 346 343

To create a barplot with Seaborn I would need the data in a format like this:

Date Tool Visitors
2024-10-29 JetPack 347
2024-10-30 JetPack 362
2024-10-31 JetPack 251
2024-10-29 Statify 499
2024-10-30 Statify 480
2024-10-31 Statify 346
2024-10-29 WP Statistics 493
2024-10-30 WP Statistics 476
2024-10-31 WP Statistics 343

That way the I can bind the Tool column to the hue= option and get the different bars.

The melt() function

Pandas offers us the melt() function to unpivot a DataFrame and turn it from wide to long.

We need to tell the melt() function what column is our id (id_vars=), how we want to name the value column (value_name=) and the name for the merged columns (var_name=). 

1
2
3
df_melted = df.melt(id_vars="Date",
        var_name="Tool", 
        value_name="Visitors")

We can check if Pandas transformed our new DataFrame the correct way:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
>>> print(df_melted)
         Date           Tool  Visitors
0  2024-10-29        JetPack       347
1  2024-10-30        JetPack       362
2  2024-10-31        JetPack       251
3  2024-10-29        Statify       499
4  2024-10-30        Statify       480
5  2024-10-31        Statify       346
6  2024-10-29  WP Statistics       493
7  2024-10-30  WP Statistics       476
8  2024-10-31  WP Statistics       343

What to do if we need the index column?

If one of the columns we need is currently the index column in the DataFrame, we first need to turn it into a regular column:

1
2
3
# Only when Index is set to a column we need
df = df.reset_index()
# call melt()

Then we can melt() the DataFrame as we did above.

Next

With the melt() function we can change the structure of our DataFrame. So far, I only used it to satisfy Seaborn, but there it was an incredible help.

Next week we explore a more often used function that allows us to rename columns in the DataFrame.