Melting DataFrames in Pandas
Learn how to transform wide-format data into long-format data using
the Pandas melt() function. Understand identifier columns,
variable columns, value columns, and practical data transformation techniques.
1. Introduction to Melting
Melting is a data reshaping operation that converts a DataFrame from a wide format into a long format. It takes multiple columns and combines their column names into one column and their corresponding values into another.
In a wide-format DataFrame, related measurements often appear in separate columns. In a long-format DataFrame, those measurements are represented as rows, making the data easier to analyze, group, visualize, and combine.
Key Concepts
- Wide format: Different measurements are stored in separate columns.
- Long format: Measurement names and values are stored in separate columns.
- Identifier variables: Columns that remain unchanged during melting.
- Variable column: A column containing the names of the melted columns.
- Value column: A column containing the values from the melted columns.
2. Understanding Wide and Long Formats
Consider a DataFrame containing the marks of students in three subjects. Each subject has its own column, so the DataFrame is in wide format.
Melting converts subject columns into subject rows while retaining the student identifier.
3. The melt() Function
Pandas provides the melt() function to reshape a DataFrame
from wide format to long format.
pandas.melt(
frame,
id_vars=None,
value_vars=None,
var_name="variable",
value_name="value",
ignore_index=True
)
Important Parameters
| Parameter | Description |
|---|---|
frame |
The DataFrame to reshape. |
id_vars |
Columns to retain as identifier columns. |
value_vars |
Columns to unpivot. If omitted, all columns not specified in id_vars are used. |
var_name |
Name of the output column that stores the original column names. |
value_name |
Name of the output column that stores the corresponding values. |
ignore_index |
If True, creates a new sequential index in the result. The default is True. |
4. Creating a Sample DataFrame
First, import Pandas and create a DataFrame with student names and subject marks.
import pandas as pd
df = pd.DataFrame({
"Student": ["Asha", "Ravi", "Neha"],
"Math": [85, 78, 92],
"Science": [90, 82, 88],
"English": [88, 80, 95]
})
print(df)
Output
Student Math Science English
0 Asha 85 90 88
1 Ravi 78 82 80
2 Neha 92 88 95
The Student column identifies each student, while the
subject columns contain the values that can be melted into rows.
5. Basic Melting with id_vars
Use id_vars to preserve the student names and
value_vars to select the subject columns to melt.
long_df = pd.melt(
df,
id_vars=["Student"],
value_vars=["Math", "Science", "English"]
)
print(long_df)
Output
Student variable value
0 Asha Math 85
1 Ravi Math 78
2 Neha Math 92
3 Asha Science 90
4 Ravi Science 82
5 Neha Science 88
6 Asha English 88
7 Ravi English 80
8 Neha English 95
6. Renaming the Output Columns
The default output column names are variable and
value. Use var_name and
value_name to give them more meaningful names.
long_df = pd.melt(
df,
id_vars=["Student"],
value_vars=["Math", "Science", "English"],
var_name="Subject",
value_name="Marks"
)
print(long_df.head())
Output
Student Subject Marks
0 Asha Math 85
1 Ravi Math 78
2 Neha Math 92
3 Asha Science 90
4 Ravi Science 82
The resulting columns describe the data clearly: the student, the subject, and the marks obtained.
7. Melting All Non-Identifier Columns
You do not need to specify value_vars when you want to melt
every column except the identifier columns.
long_df = df.melt(
id_vars="Student",
var_name="Subject",
value_name="Marks"
)
print(long_df.head())
In this example, Pandas automatically selects Math,
Science, and English for melting because
Student is the identifier column.
value_vars, that column may also be
melted. Specify value_vars when you need precise control
over which columns are transformed.
8. Melting Multiple Identifier Columns
A DataFrame can contain more than one identifier column. For example, a sales DataFrame may have both a product name and a region.
sales = pd.DataFrame({
"Product": ["Laptop", "Phone"],
"Region": ["North", "South"],
"Q1": [100, 150],
"Q2": [120, 170]
})
long_sales = pd.melt(
sales,
id_vars=["Product", "Region"],
value_vars=["Q1", "Q2"],
var_name="Quarter",
value_name="Sales"
)
print(long_sales)
Output
Product Region Quarter Sales
0 Laptop North Q1 100
1 Phone South Q1 150
2 Laptop North Q2 120
3 Phone South Q2 170
Both Product and Region remain unchanged
as identifier columns. The quarter names and sales values are
transformed into the Quarter and Sales columns.
9. Melting a DataFrame with a Custom Index
By default, melt() uses ignore_index=True,
which creates a new sequential index. Set it to False
when you want to retain the original index labels.
df_indexed = df.set_index("Student")
result = df_indexed.reset_index().melt(
id_vars="Student",
value_vars=["Math", "Science"],
var_name="Subject",
value_name="Marks",
ignore_index=False
)
print(result)
Here, the original student index is first restored as a regular
Student column using reset_index().
The ignore_index=False option preserves the index labels
of the DataFrame being melted, which in this example are the
default row labels from the original DataFrame.
10. Understanding the Number of Rows
Melting generally creates one output row for each combination of original rows and selected value columns.
The row-count rule applies when all selected columns are melted and no rows are otherwise removed.
For example, if a DataFrame has 100 rows and you melt four value columns, the result normally contains 400 rows.
11. Melting and Missing Values
Melting reshapes values; it does not automatically remove missing values. If the original DataFrame contains missing values in selected columns, those values can appear in the melted result.
df_missing = pd.DataFrame({
"Student": ["Asha", "Ravi"],
"Math": [85, None],
"Science": [90, 82]
})
long_df = df_missing.melt(
id_vars="Student",
var_name="Subject",
value_name="Marks"
)
print(long_df)
To remove rows with missing marks after melting, use
dropna() on the relevant column.
clean_df = long_df.dropna(subset=["Marks"])
print(clean_df)
12. Common Mistakes and Best Practices
Common Mistakes
- Forgetting to specify identifier columns in
id_vars. - Melting columns that should have remained unchanged.
- Using unclear output names such as
variableandvaluein a complex analysis. - Assuming that melting automatically removes missing values.
- Expecting the melted DataFrame to retain the same number of rows as the original.
Best Practices
- Identify the columns that uniquely describe each observation.
- Use
value_varswhen only specific columns should be melted. - Choose meaningful names with
var_nameandvalue_name. - Inspect the output using
head(),shape, andinfo(). - Check missing values and data types before further analysis.
13. Revision Questions
- What is melting in Pandas?
- How does wide-format data differ from long-format data?
- What is the purpose of
id_vars? - What happens when
value_varsis omitted? - How can you customize the names of the output columns?
- How many rows are produced when 5 rows and 4 value columns are melted?
- Does the
melt()function automatically remove missing values?
14. Practical Exercises
Exercise 1: Student Marks
Create a DataFrame containing three students and their marks in
Mathematics, Science, and English. Convert the subject columns
into rows using melt().
Goal: Produce columns named Student, Subject, and Marks.
Exercise 2: Quarterly Sales
Create a DataFrame containing product names, regions, and sales for Q1, Q2, Q3, and Q4. Melt the quarterly columns while preserving product and region identifiers.
Goal: Produce columns named Product, Region, Quarter, and Sales.
Exercise 3: Clean the Melted Data
Add missing values to some of the measurements in your DataFrame. Melt the data and then remove rows where the measurement value is missing.
Goal: Practice combining melt() and dropna().
Summary
Melting is a fundamental Pandas reshaping technique that converts
wide-format data into long-format data. The melt() function
combines selected column names into a variable column and their
corresponding values into a value column. Use id_vars
to preserve identifier columns, value_vars to select
columns for transformation, and var_name and
value_name to make the output easier to understand.
After melting, inspect the resulting shape, index, data types, and
missing values before continuing with analysis.