Wide to long format conversion in Pandas - Step-by-Step Execution

Concept Flow - Wide to long format conversion

Start with wide DataFrame

↓

Select columns to keep as id_vars

↓

Select columns to melt into long format

↓

Apply pd.melt() function

↓

Create long format DataFrame

↓

Use or visualize long DataFrame

Convert a wide table with many columns into a longer table with fewer columns by melting selected columns.

Execution Sample

Pandas

import pandas as pd

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

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

This code converts a wide DataFrame with subjects as columns into a long DataFrame with one subject column and one score column.

Execution Table

Step	Action	DataFrame State	Result
1	Create wide DataFrame	{'Name': ['Alice', 'Bob'], 'Math': [90, 80], 'English': [85, 88]}	DataFrame with 3 columns and 2 rows
2	Select id_vars=['Name']	Columns to keep: ['Name']	Name column stays as is
3	Select value_vars=['Math', 'English']	Columns to melt: ['Math', 'English']	These columns will be converted to rows
4	Apply pd.melt()	Melt columns into long format	DataFrame with columns: Name, Subject, Score
5	Resulting long DataFrame	Rows: 4 (2 names x 2 subjects)	DataFrame: Name Subject Score Alice Math 90 Bob Math 80 Alice English 85 Bob English 88
6	End	No further changes	Conversion complete

💡 All specified columns melted; long format DataFrame created

Variable Tracker

Variable	Start	After Step 1	After Step 4	Final
df	undefined	{'Name': ['Alice', 'Bob'], 'Math': [90, 80], 'English': [85, 88]}	Same wide DataFrame	Same wide DataFrame
long_df	undefined	undefined	Long format DataFrame with columns Name, Subject, Score	Same long format DataFrame

Key Moments - 2 Insights

Why do we need to specify id_vars in pd.melt()?

id_vars are columns to keep as identifiers and not melt. Without them, all columns would be melted, losing the original row identity. See execution_table step 2 and 3.

What happens if we don't specify var_name and value_name?

pd.melt() uses default column names 'variable' and 'value' for the melted columns. Specifying var_name and value_name gives meaningful names. See execution_table step 4.

Visual Quiz - 3 Questions

Look at the execution table at step 5, how many rows does the long DataFrame have?

A 2

B 3

C 4

D 6

💡 Hint

Check step 5 in execution_table: 2 names times 2 subjects equals 4 rows.

At which step do we specify which columns to keep as identifiers?

A Step 1

B Step 2

C Step 4

D Step 5

💡 Hint

See execution_table step 2 where id_vars=['Name'] is selected.

If we omit id_vars in pd.melt(), what happens to the 'Name' column?

A It becomes part of the melted variable column

B It is dropped from the DataFrame

C It stays as a column

D It becomes the index

💡 Hint

Without id_vars, all columns are melted, so 'Name' becomes part of the variable column (see key_moments question 1).

Concept Snapshot

pd.melt() converts wide to long format.
Use id_vars to keep columns as identifiers.
Use var_name and value_name to name melted columns.
Resulting DataFrame has fewer columns and more rows.
Useful for tidy data and plotting.

Full Transcript

We start with a wide DataFrame that has one row per person and multiple columns for subjects. We want to convert it to a long format where each row is one person-subject pair with a score. We keep the 'Name' column as an identifier using id_vars. We melt the subject columns into two columns: one for subject names and one for scores. The pd.melt() function does this conversion. The result is a longer DataFrame with columns Name, Subject, and Score. This format is easier for analysis and plotting. Key points are to specify id_vars to keep identifiers and optionally name the new columns with var_name and value_name.

Practice

1.

What does the pd.melt() function do in pandas?

easy

A. It converts data from wide format to long format by stacking columns into rows.

B. It sorts the DataFrame by column values.

C. It filters rows based on a condition.

D. It merges two DataFrames horizontally.

Solution

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

   The pd.melt() function is designed to transform a DataFrame from wide format to long format by stacking multiple columns into fewer columns, making data easier to analyze.

2. Step 2: Compare with other options

   Sorting, filtering, and merging are different operations and not the purpose of pd.melt().

3. Final Answer:

   It converts data from wide format to long format by stacking columns into rows. -> Option A

4. Quick Check:

   pd.melt() stacks columns into rows [OK]

Hint: Remember: melt stacks columns into rows for tidy data [OK]

Common Mistakes:

• Confusing melt with sort or filter functions
• Thinking melt merges DataFrames
• Assuming melt changes row order only

2.

Which of the following is the correct syntax to convert a DataFrame df from wide to long format keeping the column 'id' as identifier?

pd.melt(df, ?)

easy

A. id_vars='id'

B. value_vars='id'

C. index='id'

D. columns='id'

Solution

1. Step 1: Identify the parameter to keep identifier columns

   In pd.melt(), the id_vars parameter specifies columns to keep as identifiers and not unpivot.

2. Step 2: Check other parameters

   value_vars selects columns to unpivot, index and columns are not valid parameters for pd.melt().

3. Final Answer:

   id_vars='id' -> Option A

4. Quick Check:

   Use id_vars to keep identifier columns [OK]

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

Common Mistakes:

• Using value_vars instead of id_vars to keep columns
• Trying to use index or columns parameters
• Not specifying id_vars and losing identifier columns

3.

Given the DataFrame df:

   id  Jan  Feb  Mar
0   1   10   20   30
1   2   15   25   35

What is the output of:

pd.melt(df, id_vars='id', var_name='Month', value_name='Sales')

medium

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

B. A DataFrame with columns: id, Month, Sales and 6 rows stacking Jan, Feb, Mar values.

C. A DataFrame with columns: Month, Sales only, dropping id.

D. A DataFrame with columns: id, Jan, Feb, Mar unchanged.

Solution

1. Step 1: Understand pd.melt() with id_vars and renaming

   The id_vars='id' keeps the 'id' column fixed. The other columns 'Jan', 'Feb', 'Mar' are unpivoted into two columns named 'Month' and 'Sales' as specified by var_name and value_name.

2. Step 2: Calculate the resulting rows and columns

   Each original row with 3 month columns becomes 3 rows, so total 2*3=6 rows. Columns are 'id', 'Month', 'Sales'.

3. Final Answer:

   A DataFrame with columns: id, Month, Sales and 6 rows stacking Jan, Feb, Mar values. -> Option B

4. Quick Check:

   melt stacks columns into rows with new names [OK]

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

Common Mistakes:

• Expecting original wide format unchanged
• Dropping id column by mistake
• Thinking var_name/value_name cause errors

4.

Consider this code snippet:

df = pd.DataFrame({
  'id': [1, 2],
  'A': [10, 20],
  'B': [30, 40]
})

pd.melt(df, id_vars='id', value_vars=['A', 'B', 'X'], var_name='Variable')

It raises an error. What is the likely cause?

medium

A. The var_name parameter is invalid.

B. The id_vars parameter should be a list, not a string.

C. The value_vars list contains columns not in the DataFrame.

D. The DataFrame has duplicate column names.

Solution

1. Step 1: Verify columns in DataFrame

   The DataFrame has columns 'id', 'A', 'B'. The value_vars=['A', 'B', 'X'] includes 'X' which does not exist, causing an error.

2. Step 2: Check other options

   id_vars='id' accepts string for single column. var_name is valid. No duplicate columns.

3. Final Answer:

   The value_vars list contains columns not in the DataFrame. -> Option C

4. Quick Check:

   value_vars must all exist in df.columns [OK]

Hint: value_vars columns must exist in DataFrame [OK]

Common Mistakes:

• Specifying non-existent columns in value_vars
• Thinking id_vars must always be a list
• Confusing var_name as invalid parameter

5.

You have a DataFrame df with columns ['Country', 'Year_2019', 'Year_2020', 'Year_2021'] representing sales data. You want to convert it to long format with columns ['Country', 'Year', 'Sales'] where the 'Year' column contains the year numbers (e.g., '2019') without the 'Year_' prefix. Which code snippet correctly performs this transformation?

hard

A. pd.melt(df, id_vars='Country').rename(columns={'variable':'Year', 'value':'Sales'})

B. pd.melt(df, id_vars=['Country'], value_vars=['Year_2019', 'Year_2020', 'Year_2021'], var_name='Year', value_name='Sales')

C. pd.melt(df, id_vars='Country', var_name='Year', value_name='Sales')

D. pd.melt(df, id_vars='Country', value_vars=['Year_2019', 'Year_2020', 'Year_2021'], var_name='Year', value_name='Sales').assign(Year=lambda x: x['Year'].str.replace('Year_', ''))

Solution

1. Step 1: Use pd.melt() with id_vars and value_vars

   We keep 'Country' fixed using id_vars and specify the year columns in value_vars to unpivot them.

2. Step 2: Rename columns and clean year names

   Using var_name='Year' and value_name='Sales' renames columns. Then, we remove the 'Year_' prefix from year values using str.replace() for clarity.

3. Step 3: Compare options

   pd.melt(df, id_vars='Country', var_name='Year', value_name='Sales') misses specifying value_vars, so all columns except 'Country' are melted, which may be okay but less explicit. pd.melt(df, id_vars=['Country'], value_vars=['Year_2019', 'Year_2020', 'Year_2021'], var_name='Year', value_name='Sales') does not clean year names. pd.melt(df, id_vars='Country').rename(columns={'variable':'Year', 'value':'Sales'}) does not specify value_vars and renames columns after melting but also misses cleaning year names.

4. Final Answer:

   pd.melt(df, id_vars='Country', value_vars=['Year_2019', 'Year_2020', 'Year_2021'], var_name='Year', value_name='Sales').assign(Year=lambda x: x['Year'].str.replace('Year_', '')) -> Option D

5. Quick Check:

   Specify value_vars and clean year names after melt [OK]

Hint: Use assign with str.replace to clean melted column values [OK]

Common Mistakes:

• Not specifying value_vars and melting unwanted columns
• Forgetting to clean year names after melt
• Renaming columns after melt without cleaning values