Tutorial

Pandas melt() and unmelt using pivot() function

Published on August 3, 2022
Default avatar

By Pankaj

Pandas melt() and unmelt using pivot() function

While we believe that this content benefits our community, we have not yet thoroughly reviewed it. If you have any suggestions for improvements, please let us know by clicking the “report an issue“ button at the bottom of the tutorial.

Pandas melt() function is used to change the DataFrame format from wide to long. It’s used to create a specific format of the DataFrame object where one or more columns work as identifiers. All the remaining columns are treated as values and unpivoted to the row axis and only two columns - variable and value.

1. Pandas melt() Example

The use of melt() function is more clear when looked through an example.

import pandas as pd

d1 = {"Name": ["Pankaj", "Lisa", "David"], "ID": [1, 2, 3], "Role": ["CEO", "Editor", "Author"]}

df = pd.DataFrame(d1)

print(df)

df_melted = pd.melt(df, id_vars=["ID"], value_vars=["Name", "Role"])

print(df_melted)

Output:

     Name  ID    Role
0  Pankaj   1     CEO
1    Lisa   2  Editor
2   David   3  Author

   ID variable   value
0   1      Name  Pankaj
1   2      Name    Lisa
2   3      Name   David
3   1      Role     CEO
4   2      Role  Editor
5   3      Role  Author

We can pass the ‘var_name’ and ‘value_name’ parameters to change the column names of ‘variable’ and ‘value’.

df_melted = pd.melt(df, id_vars=["ID"], value_vars=["Name", "Role"], var_name="Attribute", value_name="Value")

Recommended Read: Python Pandas Tutorial

2. Multiple Columns as id_vars

Let’s see what happens when we pass multiple columns as the id_vars parameter.

df_melted = pd.melt(df, id_vars=["ID", "Name"], value_vars=["Role"])
print(df_melted)

Output:

   ID    Name variable   value
0   1  Pankaj     Role     CEO
1   2    Lisa     Role  Editor
2   3   David     Role  Author

3. Skipping Columns in melt() Function

It’s not required to use all the rows from the source DataFrame. Let’s skip the “ID” column in the next example.

df_melted = pd.melt(df, id_vars=["Name"], value_vars=["Role"])
print(df_melted)

Output:

     Name variable   value
0  Pankaj     Role     CEO
1    Lisa     Role  Editor
2   David     Role  Author

4. Unmelting DataFrame using pivot() function

We can use pivot() function to unmelt a DataFrame object and get the original dataframe. The pivot() function ‘index’ parameter value should be same as the ‘id_vars’ value. The ‘columns’ value should be passed as the name of the ‘variable’ column.

import pandas as pd

d1 = {"Name": ["Pankaj", "Lisa", "David"], "ID": [1, 2, 3], "Role": ["CEO", "Editor", "Author"]}

df = pd.DataFrame(d1)

# print(df)

df_melted = pd.melt(df, id_vars=["ID"], value_vars=["Name", "Role"], var_name="Attribute", value_name="Value")

print(df_melted)

# unmelting using pivot()

df_unmelted = df_melted.pivot(index='ID', columns='Attribute')

print(df_unmelted)

Output:

   ID Attribute   Value
0   1      Name  Pankaj
1   2      Name    Lisa
2   3      Name   David
3   1      Role     CEO
4   2      Role  Editor
5   3      Role  Author

            Value        
Attribute    Name    Role
ID                       
1          Pankaj     CEO
2            Lisa  Editor
3           David  Author

The unmelted DataFrame values are the same as the original DataFrame. But, the columns and index need some minor changes to make it exactly like the original data frame.

df_unmelted = df_unmelted['Value'].reset_index()
df_unmelted.columns.name = None
print(df_unmelted)

Output:

   ID    Name    Role
0   1  Pankaj     CEO
1   2    Lisa  Editor
2   3   David  Author

Reference: pandas melt() API Doc

Thanks for learning with the DigitalOcean Community. Check out our offerings for compute, storage, networking, and managed databases.

Learn more about us


About the authors
Default avatar
Pankaj

author

Still looking for an answer?

Ask a questionSearch for more help

Was this helpful?
 

Try DigitalOcean for free

Click below to sign up and get $200 of credit to try our products over 60 days!

Sign up

Join the Tech Talk
Success! Thank you! Please check your email for further details.

Please complete your information!

Get our biweekly newsletter

Sign up for Infrastructure as a Newsletter.

Hollie's Hub for Good

Working on improving health and education, reducing inequality, and spurring economic growth? We'd like to help.

Become a contributor

Get paid to write technical tutorials and select a tech-focused charity to receive a matching donation.

Welcome to the developer cloud

DigitalOcean makes it simple to launch in the cloud and scale up as you grow — whether you're running one virtual machine or ten thousand.

Learn more
DigitalOcean Cloud Control Panel