Reshape Data: Pivot

Difficulty: Easy | Acceptance: 83.00% | Paid: No Topics: N/A

Problem Description

Write a solution to reshape the data from long format to wide format.

You are given a DataFrame df with columns:

• student: string - the student name
• subject: string - the subject name
• score: integer - the score obtained

Reshape the data so that:

• Each unique student becomes a row
• Each unique subject becomes a column
• The values are the scores

Return the reshaped DataFrame with student as the index and subjects as columns.

Table of Contents

• Examples
• Constraints
• Approach 1: Using pandas pivot()
• Approach 2: Using pandas pivot_table()
• Approach 3: Using groupby and unstack
• Approach 4: Using set_index and unstack

Examples

Example 1

Input:

+--------------+----------+-------------+
| city         | month    | temperature |
+--------------+----------+-------------+
| Jacksonville | January  | 13          |
| Jacksonville | February | 23          |
| Jacksonville | March    | 38          |
| Jacksonville | April    | 5           |
| Jacksonville | May      | 34          |
| ElPaso       | January  | 20          |
| ElPaso       | February | 6           |
| ElPaso       | March    | 26          |
| ElPaso       | April    | 2           |
| ElPaso       | May      | 43          |
+--------------+----------+-------------+

Output:

+----------+--------+--------------+
| month    | ElPaso | Jacksonville |
+----------+--------+--------------+
| April    | 2      | 5            |
| February | 6      | 23           |
| January  | 20     | 13           |
| March    | 26     | 38           |
| May      | 43     | 34           |
+----------+--------+--------------+

Explanation: The table is pivoted, each column represents a city, and each row represents a specific month.

Constraints

- 1 <= df.length <= 1000
- df.columns == ['student', 'subject', 'score']
- 'student' values are non-empty strings
- 'subject' values are non-empty strings
- 'score' values are integers

Approach 1: Using pandas pivot()

Intuition The pivot() function is designed specifically for this type of transformation, creating a new column for each unique value in the specified column.

Steps

• Use pivot() with index='student', columns='subject', and values='score'
• Reset the index to make ‘student’ a regular column
• Return the result

import pandas as pd

def reshapeData(df: pd.DataFrame) -> pd.DataFrame:
    return df.pivot(index='student', columns='subject', values='score').reset_index()

Complexity

• Time: O(n) where n is the number of rows
• Space: O(n) for storing the result
• Notes: Most efficient for simple pivot operations

Approach 2: Using pandas pivot_table()

Intuition The pivot_table() function is more flexible than pivot() and can handle duplicate index/column pairs by aggregating values.

Steps

• Use pivot_table() with index='student', columns='subject', and values='score'
• Reset the index to make ‘student’ a regular column
• Return the result

import pandas as pd

def reshapeData(df: pd.DataFrame) -> pd.DataFrame:
    return df.pivot_table(index='student', columns='subject', values='score').reset_index()

Complexity

• Time: O(n) where n is the number of rows
• Space: O(n) for storing the result
• Notes: More flexible than pivot() but slightly slower due to aggregation overhead

Approach 3: Using groupby and unstack

Intuition Group by the index column and unstack the column to pivot, which is essentially what pivot() does internally.

Steps

• Group by ‘student’ and ‘subject’
• Select the ‘score’ column
• Unstack the ‘subject’ level
• Reset the index

import pandas as pd

def reshapeData(df: pd.DataFrame) -> pd.DataFrame:
    return df.groupby(['student', 'subject'])['score'].first().unstack().reset_index()

Complexity

• Time: O(n) where n is the number of rows
• Space: O(n) for storing the result
• Notes: More verbose than pivot() but demonstrates understanding of the underlying operation

Approach 4: Using set_index and unstack

Intuition Set the index columns and then unstack one of them to create the pivot structure.

Steps

• Set ‘student’ and ‘subject’ as the index
• Select the ‘score’ column
• Unstack the ‘subject’ level
• Reset the index

import pandas as pd

def reshapeData(df: pd.DataFrame) -> pd.DataFrame:
    return df.set_index(['student', 'subject'])['score'].unstack().reset_index()

Complexity

• Time: O(n) where n is the number of rows
• Space: O(n) for storing the result
• Notes: Similar to groupby approach but uses index manipulation instead