Long to wide format conversion in Pandas - Step-by-Step Execution

Concept Flow - Long to wide format conversion

Start with long format DataFrame

↓

Choose index columns

↓

Choose columns to spread

↓

Choose values to fill

↓

Apply pivot or pivot_table

↓

Get wide format DataFrame

↓

End

Convert a long table with repeated identifiers into a wide table by spreading values across new columns.

Execution Sample

Pandas

import pandas as pd

df = pd.DataFrame({
    'Name': ['Anna', 'Anna', 'Bob', 'Bob'],
    'Year': [2020, 2021, 2020, 2021],
    'Score': [85, 88, 90, 92]
})

wide_df = df.pivot(index='Name', columns='Year', values='Score')

This code converts a long DataFrame of scores by year into a wide format with years as columns.

Execution Table

Step	Action	DataFrame State	Result
1	Create long DataFrame	Name, Year, Score columns with 4 rows	DataFrame with repeated Names and Years
2	Select index='Name', columns='Year', values='Score'	Prepare to pivot	Ready to reshape
3	Apply pivot()	Reshape data	Wide DataFrame with Names as index, Years as columns, Scores as values
4	Resulting DataFrame	Index: Anna, Bob; Columns: 2020, 2021	Anna: 85, 88; Bob: 90, 92
5	End	Wide format achieved	Conversion complete

💡 Pivot completed, long format converted to wide format with unique index-column pairs

Variable Tracker

Variable	Start	After pivot	Final
df	Long format DataFrame with 4 rows	Unchanged	Unchanged
wide_df	Not defined	Wide DataFrame with 2 rows and 2 columns	Wide DataFrame ready for use

Key Moments - 2 Insights

Why do we need to specify index, columns, and values in pivot?

Because pivot needs to know which column to use as row labels (index), which to spread across columns, and which contains the values to fill. See execution_table step 2 and 3.

What happens if there are duplicate index-column pairs in the long data?

pivot() will raise an error because it expects unique pairs. You would need pivot_table() with an aggregation function instead. This is not shown here but is important to remember.

Visual Quiz - 3 Questions

Look at the execution_table step 4, what value is at row 'Bob' and column 2021?

A 90

B 92

C 88

D 85

💡 Hint

Check the 'Result' column in step 4 of execution_table for Bob's 2021 score.

At which step does the DataFrame change from long to wide format?

A Step 1

B Step 2

C Step 3

D Step 5

💡 Hint

Look at the 'Action' and 'Result' columns in execution_table to find when pivot() is applied.

If the 'Year' column had duplicate entries for the same 'Name', what would happen when pivot() is called?

A It would raise an error due to duplicate index-column pairs

B It would ignore duplicates automatically

C It would work fine, creating multiple columns

D It would fill duplicates with NaN

💡 Hint

Refer to key_moments about pivot() behavior with duplicates.

Concept Snapshot

Long to wide format conversion:
Use pandas pivot() to reshape data.
Specify index (rows), columns (new columns), and values (cell values).
Data must have unique index-column pairs.
Result is a wide DataFrame with spread columns.
Use pivot_table() if duplicates exist.

Full Transcript

This visual execution shows how to convert a long format DataFrame into a wide format using pandas pivot(). We start with a DataFrame where each row has a Name, Year, and Score. By choosing 'Name' as the index, 'Year' as the columns, and 'Score' as the values, pivot() reshapes the data so each Name has one row and each Year becomes a column with the corresponding Score. The execution table traces each step, showing the DataFrame state before and after pivoting. Key moments clarify why specifying index, columns, and values is necessary and what happens if duplicates exist. The quiz tests understanding of the resulting table and pivot behavior. This method is useful to reorganize data for easier analysis or visualization.

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