Melt for wide-to-long reshaping in Data Analysis Python - Step-by-Step Execution

Concept Flow - Melt for wide-to-long reshaping

Start with wide DataFrame

↓

Select id_vars and value_vars

↓

Apply pd.melt()

↓

Create long DataFrame with columns: id_vars + variable + value

↓

Use long DataFrame for analysis or visualization

This flow shows how we start with a wide table, choose which columns to keep fixed (id_vars) and which to reshape (value_vars), then apply melt to get a long table.

Execution Sample

Data Analysis Python

import pandas as pd

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

long_df = pd.melt(df, id_vars=['Name'], var_name='Subject', value_name='Score')

This code reshapes a wide DataFrame of student scores into a long format with one score per row.

Execution Table

Step	Action	DataFrame Shape	DataFrame Preview
1	Create wide DataFrame	(2, 3)	[{'Name': 'Anna', 'Math': 90, 'Science': 85}, {'Name': 'Bob', 'Math': 80, 'Science': 88}]
2	Call pd.melt with id_vars=['Name']	(4, 3)	[{'Name': 'Anna', 'Subject': 'Math', 'Score': 90}, {'Name': 'Bob', 'Subject': 'Math', 'Score': 80}, {'Name': 'Anna', 'Subject': 'Science', 'Score': 85}, {'Name': 'Bob', 'Subject': 'Science', 'Score': 88}]
3	Result is long DataFrame	(4, 3)	[{'Name': 'Anna', 'Subject': 'Math', 'Score': 90}, {'Name': 'Bob', 'Subject': 'Math', 'Score': 80}, {'Name': 'Anna', 'Subject': 'Science', 'Score': 85}, {'Name': 'Bob', 'Subject': 'Science', 'Score': 88}]

💡 All wide columns except 'Name' melted into 'Subject' and 'Score' columns, reshaping complete.

Variable Tracker

Variable	Start	After pd.melt	Final
df	[{'Name': 'Anna', 'Math': 90, 'Science': 85}, {'Name': 'Bob', 'Math': 80, 'Science': 88}]	N/A	Same as start
long_df	N/A	[{'Name': 'Anna', 'Subject': 'Math', 'Score': 90}, {'Name': 'Bob', 'Subject': 'Math', 'Score': 80}, {'Name': 'Anna', 'Subject': 'Science', 'Score': 85}, {'Name': 'Bob', 'Subject': 'Science', 'Score': 88}]	Same as after pd.melt

Key Moments - 3 Insights

Why do we specify 'id_vars' in pd.melt?

The 'id_vars' columns stay fixed and identify each row in the long format. Without them, we lose the link to original rows. See execution_table step 2 where 'Name' stays as is.

What happens to columns not in 'id_vars' during melt?

They become part of two new columns: one for the original column names ('variable') and one for their values ('value'). In our example, 'Math' and 'Science' become 'Subject' and 'Score' columns (execution_table step 2).

Why does the number of rows increase after melting?

Because each original row with multiple value columns turns into multiple rows, one per value column. Here, 2 rows with 2 score columns become 4 rows (execution_table step 2).

Visual Quiz - 3 Questions

Look at the execution_table at step 2. What is the value of 'Subject' for the third row in long_df?

A Math

B Science

C Name

D Score

💡 Hint

Check the 'DataFrame Preview' column in execution_table step 2; the third row has 'Subject' as 'Science'.

At which step does the DataFrame shape change from (2, 3) to (4, 3)?

A Step 2

B Step 3

C Step 1

D Never

💡 Hint

Look at the 'DataFrame Shape' column in execution_table; shape changes at step 2.

If we did not specify 'id_vars' in pd.melt, what would happen to the 'Name' column?

A It would stay as a fixed column

B It would be dropped

C It would be melted into variable and value columns

D It would cause an error

💡 Hint

Recall key_moments about 'id_vars' role; without it, all columns except index are melted.

Concept Snapshot

pd.melt reshapes wide DataFrames to long format.
Use id_vars to keep columns fixed.
value_vars are melted into variable and value columns.
Result has more rows, fewer columns.
Useful for tidy data and plotting.

Full Transcript

We start with a wide DataFrame containing student names and their scores in Math and Science. Using pd.melt, we specify 'Name' as the id_vars to keep it fixed. The other columns, 'Math' and 'Science', are melted into two new columns: 'Subject' for the original column names and 'Score' for their values. This reshapes the data from wide to long format, increasing the number of rows. The execution table shows the DataFrame shape and preview at each step. Key moments clarify why id_vars are needed and how columns transform. The visual quiz tests understanding of the reshaping process and the role of parameters.

Practice

1. What does the melt function do in data analysis?

easy

A. It merges two dataframes horizontally.

B. It converts wide data format to long data format by stacking columns.

C. It filters rows based on a condition.

D. It sorts data rows based on column values.

Solution

1. Step 1: Understand data formats

   Wide data has many columns representing variables; long data stacks these variables into fewer columns.

2. Step 2: Role of melt function

   The melt function stacks columns into rows, converting wide data to long format.

3. Final Answer:

   It converts wide data format to long data format by stacking columns. -> Option B

4. Quick Check:

   melt = wide to long [OK]

Hint: Melt stacks columns into rows to reshape wide data [OK]

Common Mistakes:

• Confusing melt with sorting or filtering
• Thinking melt merges dataframes
• Assuming melt changes row order only

2. Which of the following is the correct syntax to melt a dataframe df keeping the column 'ID' fixed?

easy

A. pd.melt(df, id_vars='ID')

B. pd.melt(df, fixed='ID')

C. df.melt(id='ID')

D. pd.melt(df, keep='ID')

Solution

1. Step 1: Identify correct parameter for fixed columns

   The parameter to keep columns fixed during melt is id_vars.

2. Step 2: Check syntax correctness

   Only pd.melt(df, id_vars='ID') uses the correct parameter and function call.

3. Final Answer:

   pd.melt(df, id_vars='ID') -> Option A

4. Quick Check:

   Use id_vars to keep columns fixed [OK]

Hint: Use id_vars to keep columns fixed in melt [OK]

Common Mistakes:

• Using wrong parameter names like fixed or keep
• Calling melt as a dataframe method without pd
• Passing id instead of id_vars

3. Given the dataframe:

import pandas as pd
df = pd.DataFrame({
  'ID': [1, 2],
  'Math': [90, 80],
  'Science': [85, 95]
})

result = pd.melt(df, id_vars='ID', var_name='Subject', value_name='Score')
print(result)

What is the output?

medium

A. A dataframe with columns ID, Subject but no Score column.

B. A dataframe with columns ID, Math, Science unchanged.

C. A dataframe with columns ID, Subject, Score and 4 rows stacking Math and Science scores.

D. An error because var_name and value_name are invalid parameters.

Solution

1. Step 1: Understand melt with id_vars and renaming

   Keeping 'ID' fixed, melt stacks 'Math' and 'Science' into one column named 'Subject' and their values into 'Score'.

2. Step 2: Predict output rows and columns

   There are 2 rows originally and 2 subject columns, so output has 4 rows with columns 'ID', 'Subject', 'Score'.

3. Final Answer:

   A dataframe with columns ID, Subject, Score and 4 rows stacking Math and Science scores. -> Option C

4. Quick Check:

   melt stacks columns into rows with renamed columns [OK]

Hint: Melt stacks columns into rows, increasing row count [OK]

Common Mistakes:

• Expecting original wide format output
• Thinking var_name/value_name cause errors
• Ignoring increase in row count after melt

4. What is wrong with this code snippet?

import pandas as pd
df = pd.DataFrame({'ID':[1,2],'A':[10,20],'B':[30,40]})
result = pd.melt(df, id='ID', var_name='Variable', value_name='Value')
print(result)

medium

A. The parameter 'id' should be 'id_vars' in pd.melt.

B. The dataframe is missing columns to melt.

C. The melt function cannot rename columns.

D. The print statement is incorrect.

Solution

1. Step 1: Check parameter names in melt

   The correct parameter to specify fixed columns is id_vars, not id.

2. Step 2: Identify error cause

   Using id='ID' causes a TypeError because melt does not recognize 'id'.

3. Final Answer:

   The parameter 'id' should be 'id_vars' in pd.melt. -> Option A

4. Quick Check:

   Use id_vars, not id, to fix columns [OK]

Hint: Use id_vars, not id, to keep columns fixed in melt [OK]

Common Mistakes:

• Using 'id' instead of 'id_vars'
• Thinking melt cannot rename columns
• Assuming print causes error

5. You have a dataframe:

import pandas as pd
df = pd.DataFrame({
  'Student': ['Alice', 'Bob'],
  'Math_2023': [88, 92],
  'Science_2023': [95, 85],
  'Math_2024': [90, 94],
  'Science_2024': [93, 89]
})

You want to reshape it to long format with columns: Student, Year, Subject, and Score. Which approach is correct?

hard

A. Use melt with var_name=['Subject', 'Year'] to split automatically.

B. Use melt with id_vars=['Student', 'Year'] directly.

C. Use melt without id_vars and rename columns to 'Year' and 'Subject'.

D. Use melt with id_vars=['Student'], then split the variable column into 'Subject' and 'Year'.

Solution

1. Step 1: Melt keeping 'Student' fixed

   Use id_vars=['Student'] to keep student names fixed and stack the year-subject columns.

2. Step 2: Split the combined variable column

   The variable column contains strings like 'Math_2023'; split this into 'Subject' and 'Year' columns manually after melt.

3. Final Answer:

   Use melt with id_vars=['Student'], then split the variable column into 'Subject' and 'Year'. -> Option D

4. Quick Check:

   Melt then split combined columns for multi-level info [OK]

Hint: Melt first, then split combined variable names into parts [OK]

Common Mistakes:

• Trying to split variable names automatically in melt
• Using id_vars with non-existing columns
• Not splitting combined variable names after melt