melt() for unpivoting in Pandas - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ Predict Output

intermediate

Output of melt() with id_vars and value_vars

What is the output DataFrame after running the following code?

Pandas

import pandas as pd

df = pd.DataFrame({
    'City': ['NY', 'LA'],
    '2019': [100, 200],
    '2020': [110, 210]
})

result = pd.melt(df, id_vars=['City'], value_vars=['2019', '2020'], var_name='Year', value_name='Population')
print(result)

A

  City  Year  Population
0   NY  2019         100
1   LA  2019         200
2   NY  2020         110
3   LA  2020         210

B

  City  Year  Population
0   NY  2019         110
1   LA  2019         210
2   NY  2020         100
3   LA  2020         200

C

  City  Year  Population
0   NY  2019         100
1   NY  2020         110
2   LA  2019         200
3   LA  2020         210

D

  City  Year  Population
0   NY  2019         200
1   LA  2019         100
2   NY  2020         210
3   LA  2020         110

💡 Hint

Remember melt keeps id_vars as is and stacks value_vars into rows.

The melt function keeps 'City' as identifier and unpivots '2019' and '2020' columns into 'Year' and 'Population'. The order is by rows then columns.

❓ data_output

intermediate

Number of rows after melting

Given a DataFrame with 3 rows and 4 columns, if you melt it using 1 column as id_vars and 3 columns as value_vars, how many rows will the melted DataFrame have?

Pandas

import pandas as pd

df = pd.DataFrame({
    'ID': [1, 2, 3],
    'A': [10, 20, 30],
    'B': [40, 50, 60],
    'C': [70, 80, 90]
})

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

A 12

B 3

C 9

D 6

💡 Hint

Each row is repeated for each value_var column.

There are 3 rows and 3 value_vars, so total rows after melt = 3 * 3 = 9.

🔧 Debug

advanced

Identify the error in melt usage

What error will this code raise and why?

Pandas

import pandas as pd

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

result = pd.melt(df, id_vars='Name', value_vars='Math,Science')
print(result)

A No error, prints melted DataFrame

B KeyError: 'Math,Science'

C ValueError: value_vars must be list-like

D TypeError: id_vars must be list-like, not str

💡 Hint

Check the type of value_vars argument.

value_vars='Math,Science' is a single string, so pandas looks for a column named exactly 'Math,Science' which does not exist, causing KeyError.

🚀 Application

advanced

Using melt to reshape sales data

You have a DataFrame with columns: 'Store', 'Jan_Sales', 'Feb_Sales', 'Mar_Sales'. You want to reshape it so that each row shows 'Store', 'Month', and 'Sales'. Which melt call achieves this?

Pandas

import pandas as pd

df = pd.DataFrame({
    'Store': ['A', 'B'],
    'Jan_Sales': [100, 150],
    'Feb_Sales': [110, 160],
    'Mar_Sales': [120, 170]
})

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

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

C pd.melt(df, value_vars=['Jan_Sales', 'Feb_Sales', 'Mar_Sales'], var_name='Month', value_name='Sales')

D pd.melt(df, id_vars=['Store'], value_vars=['Jan_Sales', 'Feb_Sales', 'Mar_Sales'], var_name='Month', value_name='Sales')

💡 Hint

Specify id_vars and value_vars explicitly to keep 'Store' and unpivot sales columns.

Option D correctly keeps 'Store' as id_vars and melts the sales columns into 'Month' and 'Sales'. Option D melts all columns including 'Store', causing wrong output.

🧠 Conceptual

expert

Understanding melt with multiple id_vars

Given a DataFrame with columns ['Region', 'Year', 'Q1', 'Q2'], what does melt(df, id_vars=['Region', 'Year'], value_vars=['Q1', 'Q2']) do?

A Creates a DataFrame with columns ['Region', 'Year', 'variable', 'value'], where 'Q1' and 'Q2' values are stacked under 'value' for each Region and Year.

B Creates a DataFrame with columns ['Region', 'Year', 'Q1', 'Q2'] unchanged.

C Creates a DataFrame with columns ['Region', 'variable', 'value'] dropping 'Year'.

D Creates a DataFrame with columns ['Year', 'variable', 'value'] dropping 'Region'.

💡 Hint

melt keeps all id_vars columns as is and stacks value_vars into two columns.

melt keeps 'Region' and 'Year' columns as identifiers and unpivots 'Q1' and 'Q2' into 'variable' and 'value' columns.

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