Wide to long format conversion in Pandas - Cheat Sheet & Quick Revision

Recall & Review

beginner

What is the purpose of converting data from wide to long format?

Converting data from wide to long format helps organize data so each row represents one observation, making it easier to analyze and visualize.

beginner

Which pandas function is commonly used to convert data from wide to long format?

The pandas function pd.melt() is commonly used to convert data from wide to long format.

intermediate

In pd.melt(), what do the parameters id_vars and value_vars mean?

id_vars are columns to keep as identifiers (stay the same). value_vars are columns to unpivot from wide to long format.

beginner

What does the resulting DataFrame look like after converting wide to long format?

The resulting DataFrame has fewer columns but more rows. It has identifier columns, a variable column showing original column names, and a value column with corresponding data.

beginner

Why is long format preferred for many data analysis and visualization tasks?

Long format is preferred because many tools and libraries expect data where each row is one observation, making grouping, filtering, and plotting easier.

Which pandas function converts wide data to long format?

A pd.concat()

B pd.melt()

C pd.pivot()

D pd.merge()

pd.melt() is used to convert wide format data to long format.

In pd.melt(), which parameter specifies columns to keep unchanged?

A value_name

B value_vars

C var_name

D id_vars

id_vars are columns kept as identifiers during melting.

What happens to the number of rows when converting from wide to long format?

A Rows increase

B Rows stay the same

C Rows become zero

D Rows decrease

Rows increase because multiple columns are unpivoted into rows.

Which format is better for plotting with libraries like seaborn?

A Wide format

B Both are equally good

C Long format

D Neither is suitable

Long format is preferred for plotting because it organizes data by observation.

If you want to keep columns 'Name' and 'Year' fixed and unpivot columns 'Math' and 'Science', which pd.melt() call is correct?

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

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

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

D pd.melt(df)

You keep 'Name' and 'Year' fixed with id_vars and unpivot 'Math' and 'Science' with value_vars.

Explain how to convert a DataFrame from wide to long format using pandas. Include the key function and parameters.

Think about which columns stay the same and which become rows.

Why is long format data often easier to analyze and visualize compared to wide format?

Consider how data is structured for charts and summaries.

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