Long to wide format conversion in Pandas - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ Predict Output

intermediate

Output of pivot operation on a simple DataFrame

Given the following pandas DataFrame in long format, what is the output after applying pivot to convert it to wide format?

Pandas

import pandas as pd

df = pd.DataFrame({
    'Date': ['2023-01-01', '2023-01-01', '2023-01-02', '2023-01-02'],
    'City': ['NY', 'LA', 'NY', 'LA'],
    'Temperature': [30, 60, 28, 65]
})

wide_df = df.pivot(index='Date', columns='City', values='Temperature')
print(wide_df)

A

City        NY    LA
Date                
2023-01-01  30.0  60.0
2023-01-02  28.0  65.0

B

City        LA    NY
Date                
2023-01-01  60.0  30.0
2023-01-02  65.0  28.0

C

Date        2023-01-01  2023-01-02
City
LA               60.0       65.0
NY               30.0       28.0

D

Date        2023-01-01  2023-01-02
City
NY               30.0       28.0
LA               60.0       65.0

💡 Hint

Remember that pivot uses the index as rows and columns as columns in the new DataFrame.

The pivot method rearranges the DataFrame so that 'Date' becomes the index (rows), 'City' becomes the columns, and 'Temperature' fills the values. The columns are sorted alphabetically by default, so 'LA' comes before 'NY'.

❓ data_output

intermediate

Number of columns after pivot with multiple value columns

Consider this DataFrame with multiple measurement columns in long format. After pivoting with pivot using index='Date' and columns='City', how many columns will the resulting wide DataFrame have?

Pandas

import pandas as pd

df = pd.DataFrame({
    'Date': ['2023-01-01', '2023-01-01', '2023-01-02', '2023-01-02'],
    'City': ['NY', 'LA', 'NY', 'LA'],
    'Temperature': [30, 60, 28, 65],
    'Humidity': [55, 40, 60, 42]
})

# Attempt to pivot
# wide_df = df.pivot(index='Date', columns='City')  # What happens?

A 4 columns

B Raises a ValueError due to multiple values for one index/column pair

C 2 columns

D 1 column

💡 Hint

When no `values` is specified, `pivot` uses all remaining columns (`Temperature`, `Humidity`), creating MultiIndex columns: 2 × 2 cities = 4 columns.

`pivot` without `values` pivots all columns except `index` and `columns`, producing a DataFrame with MultiIndex columns ('Humidity'/'Temperature' × 'LA'/'NY') totaling 4 columns.

🚀 Application

advanced

Using pivot_table to handle duplicates in long to wide conversion

You have this long format DataFrame with duplicate entries for the same Date and City. Which code snippet correctly converts it to wide format by averaging duplicates?

Pandas

import pandas as pd

df = pd.DataFrame({
    'Date': ['2023-01-01', '2023-01-01', '2023-01-01', '2023-01-02'],
    'City': ['NY', 'NY', 'LA', 'LA'],
    'Temperature': [30, 32, 60, 65]
})

A wide_df = df.pivot_table(index='Date', columns='City', values='Temperature', aggfunc='mean')

B wide_df = df.pivot_table(index='Date', columns='City', values='Temperature', aggfunc='sum')

C wide_df = df.pivot(index='Date', columns='City', values='Temperature')

D wide_df = df.pivot(index='Date', columns='City')

💡 Hint

Use pivot_table with an aggregation function to handle duplicates.

Since there are duplicate Date-City pairs, pivot raises an error. pivot_table allows aggregation like mean or sum to combine duplicates.

🔧 Debug

advanced

Identify the error in this pivot code

What error will this code raise when trying to convert the long DataFrame to wide format?

Pandas

import pandas as pd

df = pd.DataFrame({
    'Date': ['2023-01-01', '2023-01-02'],
    'City': ['NY', 'LA'],
    'Temperature': [30, 60]
})

wide_df = df.pivot(index='City', columns='Date')
print(wide_df)

A TypeError: pivot() missing required argument 'values'

B ValueError: Index contains duplicate entries, cannot reshape

C No error, prints wide DataFrame

D KeyError: 'Temperature'

💡 Hint

`pivot` automatically uses remaining columns as `values` if not specified.

With one remaining column ('Temperature') after index='City' and columns='Date', `pivot` implicitly uses it. The code succeeds, printing a wide DataFrame with NaNs for missing combinations.

🧠 Conceptual

expert

Understanding the difference between pivot and pivot_table

Which statement correctly explains the difference between pivot and pivot_table in pandas when converting long to wide format?

A <code>pivot</code> can handle duplicate entries by aggregating them, while <code>pivot_table</code> cannot.

B <code>pivot</code> automatically fills missing values with zeros, while <code>pivot_table</code> leaves them as NaN.

C <code>pivot</code> and <code>pivot_table</code> are identical in functionality and usage.

D <code>pivot_table</code> allows aggregation of duplicate entries using functions like mean or sum, while <code>pivot</code> raises an error if duplicates exist.

💡 Hint

Think about how each function handles duplicates and missing data.

pivot is simpler but fails if duplicates exist. pivot_table is more flexible and allows aggregation to handle duplicates.

Practice

1. What does converting data from long to wide format using pivot in pandas mainly achieve?

easy

A. It removes duplicate rows from the dataset.

B. It sorts the data alphabetically by column names.

C. It turns row values into columns to compare data side by side.

D. It aggregates data by summing all values.

Solution

1. Step 1: Understand long vs wide format

   Long format has repeated rows for categories; wide format spreads categories into columns.

2. Step 2: Role of pivot in pandas

   Pivot reshapes data by turning unique row values into columns for side-by-side comparison.

3. Final Answer:

   It turns row values into columns to compare data side by side. -> Option C

4. Quick Check:

   Long to wide = row values become columns [OK]

Hint: Pivot turns row values into columns for easy comparison [OK]

Common Mistakes:

• Thinking pivot sorts data alphabetically
• Confusing pivot with aggregation functions
• Assuming pivot removes duplicates automatically

2. Which of the following is the correct syntax to convert a DataFrame df from long to wide format using pivot with index='Date', columns='City', and values='Temperature'?

easy

A. df.pivot(columns='Date', index='City', values='Temperature')

B. df.pivot(index='Date', columns='City', values='Temperature')

C. df.pivot(index='Temperature', columns='Date', values='City')

D. df.pivot(values='Date', index='City', columns='Temperature')

Solution

1. Step 1: Identify correct parameter order

   Pivot requires index (rows), columns, and values parameters in any order but with correct names.

2. Step 2: Match parameters to data roles

   Index='Date' means rows by date, columns='City' means columns by city, values='Temperature' fills data.

3. Final Answer:

   df.pivot(index='Date', columns='City', values='Temperature') -> Option B

4. Quick Check:

   Correct pivot syntax = df.pivot(index='Date', columns='City', values='Temperature') [OK]

Hint: Use pivot(index=rows, columns=cols, values=data) [OK]

Common Mistakes:

• Swapping index and columns parameters
• Using wrong column names in parameters
• Passing parameters in wrong order causing errors

3. Given the DataFrame df below:

   Date    City  Temperature
0  2023-01-01  NY     30
1  2023-01-01  LA     60
2  2023-01-02  NY     28
3  2023-01-02  LA     62

What is the output of df.pivot(index='Date', columns='City', values='Temperature')?

medium

A. City LA NY
Date
2023-01-01 60 30
2023-01-02 62 28

B. City NY LA
Date
2023-01-01 30 60
2023-01-02 28 62

C. Date 2023-01-01 2023-01-02
City
LA 60 62
NY 30 28

D. City LA NY
Date
2023-01-01 30 60
2023-01-02 28 62

Solution

1. Step 1: Understand pivot parameters

   Index='Date' means rows are dates; columns='City' means columns are cities; values='Temperature' fills data.

2. Step 2: Arrange data accordingly

   For 2023-01-01, LA=60, NY=30; for 2023-01-02, LA=62, NY=28; columns sorted alphabetically by default.

3. Final Answer:

   City LA NY Date 2023-01-01 60 30 2023-01-02 62 28 -> Option A

4. Quick Check:

   Pivot output matches City LA NY Date 2023-01-01 60 30 2023-01-02 62 28 [OK]

Hint: Pivot columns sorted alphabetically by default [OK]

Common Mistakes:

• Mixing up row and column labels
• Assuming city order is NY then LA
• Confusing index and columns roles

4. You try to run df.pivot(index='Date', columns='City', values='Temperature') but get a ValueError: "Index contains duplicate entries, cannot reshape". What is the most likely cause?

medium

A. The columns parameter should be a list, not a string.

B. The DataFrame is missing the Temperature column.

C. The pivot function requires numeric index values only.

D. The DataFrame has multiple rows with the same Date and City combination.

Solution

1. Step 1: Understand pivot restrictions

   Pivot requires unique pairs of index and columns to create a single value cell.

2. Step 2: Identify cause of duplicate error

   If multiple rows share the same Date and City, pivot cannot decide which value to use, causing the error.

3. Final Answer:

   The DataFrame has multiple rows with the same Date and City combination. -> Option D

4. Quick Check:

   Duplicate index-column pairs cause ValueError [OK]

Hint: Check for duplicate index-column pairs before pivot [OK]

Common Mistakes:

• Assuming missing columns cause this error
• Thinking pivot needs numeric index only
• Believing columns param must be a list

5. You have a DataFrame df with columns ['Date', 'City', 'Measurement', 'Value'] where Measurement can be 'Temperature' or 'Humidity'. How do you convert this long format into a wide format with Date as index and separate columns for each City and Measurement combination showing Value?

hard

A. Use df.pivot(index='Date', columns=['City', 'Measurement'], values='Value')

B. Use df.pivot(index=['Date', 'City'], columns='Measurement', values='Value')

C. Use df.pivot(index='Measurement', columns=['Date', 'City'], values='Value')

D. Use df.pivot(index='Date', columns='Value', values=['City', 'Measurement'])

Solution

1. Step 1: Understand multi-level columns in pivot

   Pivot allows multiple columns by passing a list to the columns parameter to create hierarchical columns.

2. Step 2: Apply correct parameters

   Index='Date' sets rows, columns=['City', 'Measurement'] creates multi-level columns, values='Value' fills data.

3. Final Answer:

   Use df.pivot(index='Date', columns=['City', 'Measurement'], values='Value') -> Option A

4. Quick Check:

   Multi-index columns = Use df.pivot(index='Date', columns=['City', 'Measurement'], values='Value') [OK]

Hint: Pass list to columns for multi-level wide format [OK]

Common Mistakes:

• Passing multiple columns to index instead of columns
• Swapping values and columns parameters
• Using wrong columns for values parameter