LeetCode Problem Workspace
Reshape Data: Pivot
Learn how to pivot data in a table so each row is a month and each city forms its own column efficiently.
0
Topics
1
Code langs
0
Related
Practice Focus
Easy · Reshape Data: Pivot core interview pattern
Answer-first summary
Learn how to pivot data in a table so each row is a month and each city forms its own column efficiently.
Ace coding interviews with Interview AiBoxInterview AiBox guidance for Reshape Data: Pivot core interview pattern
This problem focuses on reshaping tabular data using the pivot pattern. You need to convert rows into columns so each city has its own column and each row corresponds to a specific month. Using built-in pandas functions simplifies the process and avoids manual grouping errors.
Problem Statement
You are given a table containing city names, months, and temperatures. Transform the table so that each row represents temperatures for a specific month and each city appears as a separate column.
The resulting pivoted table should clearly display monthly temperatures for each city, matching the format shown in the examples, while maintaining correct month order and avoiding misalignment between cities and their temperatures.
Examples
Example 1
Input: See original problem statement.
Output: See original problem statement.
DataFrame weather +-------------+--------+ | Column Name | Type | +-------------+--------+ | city | object | | month | object | | temperature | int | +-------------+--------+
Example 2
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 | +----------+--------+--------------+
The table is pivoted, each column represents a city, and each row represents a specific month.
Constraints
Solution Approach
Use pandas pivot function
Apply pandas pivot with index as 'month', columns as 'city', and values as 'temperature'. This directly reshapes the data into the desired structure without manual loops.
Ensure correct sorting
After pivoting, sort the index (months) to maintain chronological order. Without sorting, months may appear unordered, which can confuse downstream analysis or tests.
Handle missing values carefully
If some city-month combinations are missing, fill them with NaN or a default value. Ignoring missing entries may lead to misaligned rows or inconsistent columns.
Complexity Analysis
| Metric | Value |
|---|---|
| Time | Depends on the final approach |
| Space | Depends on the final approach |
Time and space complexity depend on the number of rows and columns in the table. Pivoting is typically O(n) in time for scanning all rows and O(c*m) in space for storing the new table, where c is the number of cities and m is the number of months.
What Interviewers Usually Probe
- Do you recognize this as a data reshaping or pivot problem?
- Can you suggest a pandas function that avoids manual aggregation?
- How would you handle missing city-month temperature entries?
Common Pitfalls or Variants
Common pitfalls
- Forgetting to set the correct index, leading to a misaligned table.
- Not sorting months after pivoting, which may cause tests to fail.
- Overwriting original data instead of creating a clean pivoted table.
Follow-up variants
- Pivot by multiple indices such as year and month to handle multi-year data.
- Aggregate temperatures by mean or max if multiple entries exist per city-month.
- Pivot with additional columns like humidity or rainfall instead of temperature.
FAQ
What is the Reshape Data: Pivot pattern?
It involves transforming rows into columns so each unique value in one column becomes a new column, commonly using pandas pivot.
How do I pivot data by month and city in pandas?
Use df.pivot(index='month', columns='city', values='temperature') to convert rows into columns for each city.
How should missing month-city combinations be handled?
They can be left as NaN or filled with a default value to maintain table alignment and avoid errors.
Does the pivot function preserve row order?
Pivot does not automatically sort rows, so sorting by month after pivoting is recommended to ensure correct order.
Can this approach handle multiple measurements per city-month?
No, pivot requires unique index-column combinations. Aggregate values first with groupby if duplicates exist.
Solution
Solution 1
#### Python3
import pandas as pd
def pivotTable(weather: pd.DataFrame) -> pd.DataFrame:
return weather.pivot(index='month', columns='city', values='temperature')