Long to wide format conversion in Pandas - Mini Project: Build & Apply

Long to Wide Format Conversion with pandas

📖 Scenario: You work in a sales team that tracks monthly sales data for different products. The data is currently in a long format, where each row shows the sales of one product in one month. You want to convert this data into a wide format, where each product has one row and each month is a separate column showing the sales.

🎯 Goal: Convert a sales dataset from long format to wide format using pandas. You will create the initial data, set a configuration variable for the pivot, apply the pivot operation, and then display the final wide format table.

📋 What You'll Learn

Create a pandas DataFrame with sales data in long format

Create a variable to specify the column to pivot on

Use pandas pivot method to convert the data from long to wide format

Print the resulting wide format DataFrame

💡 Why This Matters

🌍 Real World

Sales teams and analysts often receive data in long format but need to see it in wide format for easier comparison and reporting.

💼 Career

Data scientists and analysts use data reshaping techniques like pivoting to prepare data for visualization, reporting, and further analysis.

1

Create the sales data in long format

Create a pandas DataFrame called sales_long with these exact columns and rows:
Product, Month, and Sales.
Include these rows exactly:
'A', 'Jan', 100, 'A', 'Feb', 120, 'B', 'Jan', 90, 'B', 'Feb', 110.

Pandas

import pandas as pd
# Create the sales_long DataFrame with Product, Month, and Sales columns
# Your code here

Hint

Use pd.DataFrame with a dictionary where keys are column names and values are lists of column values.

2

Set the pivot column variable

Create a variable called pivot_column and set it to the string 'Month'. This will be used to pivot the data by month.

Pandas

import pandas as pd
sales_long = pd.DataFrame({
    'Product': ['A', 'A', 'B', 'B'],
    'Month': ['Jan', 'Feb', 'Jan', 'Feb'],
    'Sales': [100, 120, 90, 110]
})
# Create pivot_column variable and set it to 'Month'
# Your code here

Hint

Just assign the string 'Month' to the variable pivot_column.

3

Pivot the data from long to wide format

Use the pandas pivot method on sales_long to create a new DataFrame called sales_wide. Use 'Product' as the index, pivot_column as the columns, and 'Sales' as the values.

Pandas

import pandas as pd
sales_long = pd.DataFrame({
    'Product': ['A', 'A', 'B', 'B'],
    'Month': ['Jan', 'Feb', 'Jan', 'Feb'],
    'Sales': [100, 120, 90, 110]
})
pivot_column = 'Month'
# Create sales_wide by pivoting sales_long
# Your code here

Hint

Use sales_long.pivot(index='Product', columns=pivot_column, values='Sales') to reshape the data.

4

Print the wide format DataFrame

Print the sales_wide DataFrame to display the sales data in wide format.

Pandas

import pandas as pd
sales_long = pd.DataFrame({
    'Product': ['A', 'A', 'B', 'B'],
    'Month': ['Jan', 'Feb', 'Jan', 'Feb'],
    'Sales': [100, 120, 90, 110]
})
pivot_column = 'Month'
sales_wide = sales_long.pivot(index='Product', columns=pivot_column, values='Sales')
# Print the sales_wide DataFrame
# Your code here

Hint

Use print(sales_wide) to show the wide format table.

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