Wide to long format conversion in Pandas

Introduction

We change data from wide to long format to make it easier to analyze and visualize. Long format stacks related data into fewer columns, which many tools prefer.

You have survey data with one column per question and want one row per answer.

You want to plot time series data where each time point is a separate column.

You need to prepare data for statistical tests that require long format.

You want to group and summarize data by categories stored in column names.

Syntax

Pandas

pd.melt(frame, id_vars=None, value_vars=None, var_name=None, value_name='value', col_level=None)

frame is your original DataFrame.

id_vars are columns to keep as identifiers (not unpivoted).

Examples

This keeps the 'Name' column and converts 'Math' and 'Science' columns into two rows per name.

Pandas

pd.melt(df, id_vars=['Name'], value_vars=['Math', 'Science'])

Renames the new variable column to 'Subject' and the values column to 'Score'.

Pandas

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

Sample Program

This example converts a wide DataFrame with scores in separate columns into a long format with one score per row.

Pandas

import pandas as pd

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

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

Important Notes

Wide format has many columns for related data; long format stacks them into rows.

Use id_vars to keep columns that identify each row.

Renaming var_name and value_name makes the output clearer.

Summary

Wide to long conversion stacks columns into rows for easier analysis.

Use pd.melt() with id_vars to keep identifier columns.

Renaming columns in the output helps understand the data better.

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