Wide to long format conversion in Pandas - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ Predict Output

intermediate

Output of pandas melt function

What is the output DataFrame after running this code?

Pandas

import pandas as pd

df = pd.DataFrame({
    'id': [1, 2],
    'A_2020': [10, 20],
    'A_2021': [15, 25],
    'B_2020': [5, 10],
    'B_2021': [7, 14]
})

result = df.melt(id_vars=['id'], var_name='variable', value_name='value')
print(result)

A

   id variable  value
0   1   A_2020     10
1   2   A_2020     20
2   1   A_2021     15
3   2   A_2021     25
4   1   B_2020      5
5   2   B_2020     10
6   1   B_2021      7
7   2   B_2021     14

B

   id variable  value
0   1       A      10
1   2       A      20
2   1       A      15
3   2       A      25
4   1       B       5
5   2       B      10
6   1       B       7
7   2       B      14

C

   id  A_2020  A_2021  B_2020  B_2021
0   1      10      15       5       7
1   2      20      25      10      14

D

   id variable  value
0   1   2020_A     10
1   2   2020_A     20
2   1   2021_A     15
3   2   2021_A     25
4   1   2020_B      5
5   2   2020_B     10
6   1   2021_B      7
7   2   2021_B     14

💡 Hint

The melt function keeps id_vars as is and stacks other columns into two columns: variable and value.

The melt function converts wide columns into rows. Here, columns except 'id' become values in 'variable' column, and their data go into 'value'.

❓ data_output

intermediate

Number of rows after wide to long conversion

Given a DataFrame with 3 rows and 4 value columns (excluding id), how many rows will the DataFrame have after using pandas melt with id_vars=['id']?

A 7

B 12

C 15

D 3

💡 Hint

Each original row expands into multiple rows, one per value column melted.

Melt stacks all value columns into rows. With 3 rows and 4 value columns, total rows = 3 * 4 = 12.

🔧 Debug

advanced

Identify the error in this wide to long conversion code

What error does this code raise when run?

Pandas

import pandas as pd

df = pd.DataFrame({
    'id': [1, 2],
    'score_1': [10, 20],
    'score_2': [15, 25]
})

result = pd.melt(df, id_vars='id', var_name='test', value_name='score')
print(result)

A TypeError: id_vars must be list-like

B ValueError: var_name must be a list

C No error, prints melted DataFrame

D KeyError: 'id' not found in columns

💡 Hint

Check the type of id_vars argument in melt function.

id_vars expects a list of column names, but a string was passed. This causes a TypeError.

🚀 Application

advanced

Extract year and variable from melted column

After melting a DataFrame with columns like 'A_2020', 'B_2021', how can you split the 'variable' column into two new columns 'var' and 'year'?

Pandas

import pandas as pd

df = pd.DataFrame({
    'id': [1, 2],
    'A_2020': [10, 20],
    'B_2021': [5, 15]
})

melted = df.melt(id_vars=['id'], var_name='variable', value_name='value')

# Fill in the code to split 'variable' into 'var' and 'year'

A melted['var'], melted['year'] = zip(*melted['variable'].str.split('_'))

B melted['var'], melted['year'] = melted['variable'].split('_')

C melted[['var', 'year']] = melted['variable'].str.split('_', expand=True)

D

melted['var'] = melted['variable'].apply(lambda x: x.split('_')[0])
melted['year'] = melted['variable'].apply(lambda x: x.split('_')[1])

💡 Hint

Use pandas string split with expand=True to create multiple columns.

Using str.split('_', expand=True) returns a DataFrame with two columns which can be assigned to new columns.

🧠 Conceptual

expert

Why use wide to long format in data analysis?

Which of these is the best reason to convert data from wide to long format?

A Wide format is required for machine learning models.

B Wide format is always better for plotting with seaborn and matplotlib.

C Long format reduces the number of rows in the dataset.

D Long format allows easier use of groupby and aggregation functions in pandas.

💡 Hint

Think about how data shape affects grouping and summarizing.

Long format stacks variables into rows, making it easier to group and aggregate by variable types or time points.