melt() for unpivoting in Pandas - Step-by-Step Execution

Concept Flow - melt() for unpivoting

Start with wide DataFrame

↓

Choose id_vars (fixed columns)

↓

Choose value_vars (columns to unpivot)

↓

Apply melt()

↓

Get long DataFrame with variable and value columns

↓

Use for easier analysis or plotting

The melt() function takes a wide table and turns selected columns into rows, making data longer and easier to analyze.

Execution Sample

Pandas

import pandas as pd

df = pd.DataFrame({
    'Name': ['Anna', 'Bob'],
    'Math': [90, 80],
    'Science': [85, 88]
})

melted = pd.melt(df, id_vars=['Name'], value_vars=['Math', 'Science'], var_name='Subject', value_name='Score')

This code changes a table of student scores from wide to long format, listing each subject and score per student.

Execution Table

Step	DataFrame State	Action	Resulting DataFrame Preview
1	Original df	Create DataFrame with columns Name, Math, Science	[Name, Math, Science] [Anna, 90, 85] [Bob, 80, 88]
2	df	Call pd.melt with id_vars=['Name'], value_vars=['Math', 'Science']	New columns: Name, Subject, Score
3	melted	Rows created for each subject per student	[Name, Subject, Score] [Anna, Math, 90] [Anna, Science, 85] [Bob, Math, 80] [Bob, Science, 88]
4	melted	Data is now long format, easier for analysis	Ready for plotting or grouping

💡 All specified value_vars columns are unpivoted into rows, process complete.

Variable Tracker

Variable	Start	After melt()
df	[Anna, 90, 85] [Bob, 80, 88]	Unchanged
melted	Not defined	[Anna, Math, 90] [Anna, Science, 85] [Bob, Math, 80] [Bob, Science, 88]

Key Moments - 2 Insights

Why do we specify id_vars and value_vars in melt()?

id_vars are columns to keep fixed (like 'Name'), value_vars are columns to turn into rows (like 'Math' and 'Science'). See execution_table step 2 where melt() is called with these parameters.

What happens to the original columns after melt()?

Columns in value_vars are removed from wide form and appear as values in the new 'variable' column (here named 'Subject'). See execution_table step 3 for the new DataFrame structure.

Visual Quiz - 3 Questions

Look at the execution table, what is the value of 'Subject' for the second row in the melted DataFrame?

A Name

B Science

C Math

D Score

💡 Hint

Check execution_table row 3 showing the melted DataFrame preview.

At which step does the DataFrame change from wide to long format?

A Step 1

B Step 2

C Step 3

D Step 4

💡 Hint

Look at execution_table rows 2 and 3 to see when the DataFrame structure changes.

If we add another subject column 'English' to value_vars, how would the melted DataFrame change?

A More rows per student, one for English scores

B Fewer rows, English scores replace Science

C No change, English ignored

D Columns 'Name' and 'English' swapped

💡 Hint

Recall that melt() creates one row per value_vars column per id_vars row, see variable_tracker for melted shape.

Concept Snapshot

pandas melt() unpivots data:
- id_vars: columns to keep fixed
- value_vars: columns to turn into rows
- var_name: name for new variable column
- value_name: name for new value column
Transforms wide data to long format for easier analysis.

Full Transcript

The melt() function in pandas changes a wide table into a long one by turning selected columns into rows. We start with a DataFrame with columns like Name, Math, and Science. We keep 'Name' fixed using id_vars, and unpivot 'Math' and 'Science' using value_vars. The result is a longer DataFrame with columns Name, Subject, and Score. Each student has one row per subject. This format is easier for grouping and plotting. Key points are choosing which columns to keep and which to unpivot. The execution table shows each step from original data to melted data. The variable tracker shows how the DataFrame changes. The quiz checks understanding of the new structure and how melt works.

Practice

1. What does the pandas.melt() function primarily do to a DataFrame?

easy

A. Filters rows based on a condition.

B. Sorts the DataFrame rows based on column values.

C. Transforms wide format data into long format data by unpivoting columns.

D. Aggregates data by grouping columns.

Solution

1. Step 1: Understand the purpose of melt()

   The melt() function is used to convert wide data (many columns) into long data (fewer columns, more rows).

2. Step 2: Identify the transformation type

   This process is called unpivoting, where columns become rows.

3. Final Answer:

   Transforms wide format data into long format data by unpivoting columns. -> Option C

4. Quick Check:

   melt() = unpivot wide to long [OK]

Hint: melt() turns columns into rows to reshape data [OK]

Common Mistakes:

• Confusing melt() with sorting or filtering functions
• Thinking melt() aggregates data
• Assuming melt() changes row order only

2. Which of the following is the correct syntax to unpivot columns 'A' and 'B' in DataFrame df using melt() while keeping column 'ID' fixed?

easy

A. pd.melt(df, id_vars=['ID'], value_vars=['A', 'B'])

B. pd.melt(df, value_vars=['ID'], id_vars=['A', 'B'])

C. df.melt(id_vars=['A', 'B'], value_vars=['ID'])

D. df.melt(value_vars=['ID'], id_vars=['A', 'B'])

Solution

1. Step 1: Identify correct parameter usage

   id_vars are columns to keep fixed; value_vars are columns to unpivot. Here, 'ID' is fixed, 'A' and 'B' are unpivoted.

2. Step 2: Match syntax with parameters

   pd.melt(df, id_vars=['ID'], value_vars=['A', 'B']) correctly uses pd.melt(df, id_vars=['ID'], value_vars=['A', 'B']). Other options swap or misuse parameters.

3. Final Answer:

   pd.melt(df, id_vars=['ID'], value_vars=['A', 'B']) -> Option A

4. Quick Check:

   id_vars = fixed columns, value_vars = columns to unpivot [OK]

Hint: id_vars = fixed columns; value_vars = columns to unpivot [OK]

Common Mistakes:

• Swapping id_vars and value_vars
• Using DataFrame method without pd prefix incorrectly
• Passing wrong columns to parameters

3. Given the DataFrame df below:

   ID  Jan  Feb
0  1   10   20
1  2   30   40

What is the output of:

pd.melt(df, id_vars=['ID'], var_name='Month', value_name='Sales')

medium

A. DataFrame with columns: Month, Sales only

B. DataFrame with columns: ID, Month, Sales and 4 rows unpivoted from Jan and Feb

C. DataFrame with columns: ID, Jan, Feb unchanged

D. Error due to missing parameters

Solution

1. Step 1: Understand melt() parameters

   id_vars=['ID'] keeps 'ID' fixed. var_name='Month' renames the variable column. value_name='Sales' renames the value column.

2. Step 2: Predict output structure

   Jan and Feb columns become rows under 'Month' with their values under 'Sales'. Result has 4 rows (2 IDs x 2 months).

3. Final Answer:

   DataFrame with columns: ID, Month, Sales and 4 rows unpivoted from Jan and Feb -> Option B

4. Quick Check:

   melt() output = long format with renamed columns [OK]

Hint: melt() creates rows for each value_vars entry per id_vars row [OK]

Common Mistakes:

• Expecting original wide format output
• Ignoring var_name and value_name renaming
• Thinking melt() drops id_vars

4. Consider this code snippet:

df = pd.DataFrame({'ID':[1,2],'A':[10,20],'B':[30,40]})
result = pd.melt(df, id_vars=['A'], value_vars=['ID', 'B'])

What is the main issue with this code?

medium

A. The id_vars column 'A' should be a fixed identifier, but it is a value column here.

B. Syntax error due to wrong parameter names.

C. No issue; code runs correctly and melts as expected.

D. value_vars contains 'ID' which is usually an identifier and should be in id_vars.

Solution

1. Step 1: Analyze id_vars and value_vars roles

   id_vars should be columns to keep fixed (usually identifiers). value_vars are columns to unpivot.

2. Step 2: Check column roles in code

   'ID' is usually an identifier and should be in id_vars, but here it is in value_vars, which is unusual and likely a mistake.

3. Final Answer:

   value_vars contains 'ID' which is usually an identifier and should be in id_vars. -> Option D

4. Quick Check:

   Identifiers belong in id_vars, not value_vars [OK]

Hint: Keep identifiers in id_vars, not in value_vars [OK]

Common Mistakes:

• Swapping id_vars and value_vars
• Assuming melt() ignores column roles
• Thinking code has syntax error

5. You have a DataFrame with columns: ['Date', 'Store', 'Product_A', 'Product_B']. You want to reshape it so each row shows Date, Store, Product (either 'Product_A' or 'Product_B'), and Sales (the value). Which melt() call achieves this?

hard

A. pd.melt(df, id_vars=['Date', 'Store'], var_name='Product', value_name='Sales')

B. pd.melt(df, id_vars=['Product_A', 'Product_B'], var_name='Product', value_name='Sales')

C. pd.melt(df, value_vars=['Date', 'Store'], var_name='Product', value_name='Sales')

D. pd.melt(df, id_vars=['Date'], value_vars=['Store', 'Product_A', 'Product_B'])

Solution

1. Step 1: Identify fixed and unpivot columns

   'Date' and 'Store' are identifiers to keep fixed (id_vars). 'Product_A' and 'Product_B' are value columns to unpivot.

2. Step 2: Use melt() with correct parameters

   Use id_vars=['Date', 'Store'], var_name='Product' to name the unpivoted column, and value_name='Sales' for values.

3. Final Answer:

   pd.melt(df, id_vars=['Date', 'Store'], var_name='Product', value_name='Sales') -> Option A

4. Quick Check:

   Keep identifiers fixed, unpivot product columns [OK]

Hint: Keep identifiers in id_vars; unpivot product columns with var_name [OK]

Common Mistakes:

• Putting product columns in id_vars
• Unpivoting identifier columns
• Not renaming var_name or value_name for clarity