Problem Description

This problem asks you to transform a DataFrame from a long format to a wide format using a pivot operation.

You're given a DataFrame called weather with three columns:

• city: the name of a city (string/object type)
• month: the name of a month (string/object type)
• temperature: the temperature value (integer)

The initial data is in "long format" where each row represents a single temperature measurement for one city in one month.

Your task is to pivot this data so that:

• Each row represents a specific month
• Each city becomes its own column
• The temperature values fill the cells where a month and city intersect

For example, if the original data has a row with Jacksonville, January, 13, after pivoting:

• There will be a row for January
• There will be a column for Jacksonville
• The cell at the intersection of the January row and Jacksonville column will contain 13

The resulting DataFrame should have:

• The month as the index (row labels)
• City names as column headers
• Temperature values in the cells
• Rows sorted alphabetically by month name

This is a common data reshaping operation that makes it easier to compare temperatures across different cities for the same months, as each month's data for all cities appears in a single row.

Intuition

When we look at the transformation needed, we're essentially reorganizing data from a "tall" structure to a "wide" structure. Each piece of information (city, month, temperature) needs to be repositioned based on its role in the new layout.

The key insight is recognizing that this is a classic pivot operation where:

• We want months to become our row identifiers
• We want cities to spread out and become column headers
• We want temperatures to fill in as the values

Think of it like creating a spreadsheet or table where you want to quickly compare values across different categories. Instead of scanning through multiple rows to find all temperatures for a given month, we want them all in one row.

The pandas pivot() function is designed exactly for this type of reshaping. We need to tell it three things:

1. What should become the rows? → The months should be our index (rows)
2. What should become the columns? → The cities should spread out as columns
3. What values should fill the table? → The temperature values should populate the cells

This maps directly to the parameters of pivot():

• index='month' - makes each unique month a row
• columns='city' - makes each unique city a column
• values='temperature' - fills the resulting table with temperature values

The function automatically handles the matching - it knows to put the temperature of Jacksonville in January at the intersection of the January row and Jacksonville column. The result is a more readable format where you can easily compare temperatures across cities for any given month by looking at a single row.

Solution Approach

The solution uses pandas' built-in pivot() method to reshape the DataFrame. Here's how the implementation works:

def pivotTable(weather: pd.DataFrame) -> pd.DataFrame:
    return weather.pivot(index='month', columns='city', values='temperature')

The pivot() method takes three key parameters:

1. index='month': This parameter specifies which column should become the new index (row labels) of the pivoted DataFrame. Each unique value in the 'month' column becomes a row in the output.

2. columns='city': This parameter determines which column's unique values should become the new column headers. Each unique city name becomes a column in the output.

3. values='temperature': This parameter specifies which column contains the values that will populate the cells of the pivoted table. The temperature values are placed at the appropriate row-column intersections.

The algorithm internally:

• Groups the data by the unique combinations of index and column values
• Creates a new DataFrame structure with months as rows and cities as columns
• Maps each temperature value to its corresponding (month, city) position
• Automatically handles the sorting of the index alphabetically

For the given example:

• Input row: (Jacksonville, January, 13)
• Output position: Row labeled 'January', Column labeled 'Jacksonville', Value = 13

The method handles all the reshaping automatically without needing explicit loops or manual mapping. The resulting DataFrame has a more intuitive structure for cross-city temperature comparisons, with each row representing all cities' temperatures for a particular month.

Example Walkthrough

Let's walk through a small example to illustrate how the pivot operation transforms the data.

Starting DataFrame (Long Format):

       city      month  temperature
0    Boston    January           0
1    Boston   February           8
2    Austin    January          20
3    Austin   February          24

Step 1: Identify the pivot components

• Index (rows): month column → unique values are "January" and "February"
• Columns: city column → unique values are "Boston" and "Austin"
• Values: temperature column → these numbers will fill our new table

Step 2: Create the new structure The pivot operation creates a grid where:

• Rows are labeled with months
• Columns are labeled with cities
• Each cell needs to be filled with the appropriate temperature

Step 3: Map values to their positions

• Row "January" + Column "Boston" → temperature = 0
• Row "January" + Column "Austin" → temperature = 20
• Row "February" + Column "Boston" → temperature = 8
• Row "February" + Column "Austin" → temperature = 24

Step 4: Execute the pivot

result = weather.pivot(index='month', columns='city', values='temperature')

Final DataFrame (Wide Format):

city       Austin  Boston
month                    
February       24       8
January        20       0

Notice how:

• Each month now occupies exactly one row
• Each city has its own column
• Temperature values are placed at the correct intersections
• The months are automatically sorted alphabetically ("February" before "January")
• We can now easily compare temperatures across cities for the same month by reading horizontally

This transformation makes it much easier to answer questions like "What were all cities' temperatures in January?" - just look at the January row instead of filtering through multiple rows in the original format.

Solution Implementation

Time and Space Complexity

Time Complexity: O(n log n) where n is the number of rows in the input DataFrame.

The pivot operation in pandas involves:

• Grouping data by the index and column keys, which requires sorting operations internally: O(n log n)
• Reshaping the data into the pivoted format: O(n)
• The overall complexity is dominated by the sorting step

Space Complexity: O(n) where n is the number of rows in the input DataFrame.

The space complexity includes:

• The output pivoted DataFrame which stores all the temperature values: O(n)
• Temporary data structures used during the pivot operation (hash maps for grouping, intermediate arrays): O(n)
• The total space requirement is linear with respect to the input size

Note: In the worst case where all month-city combinations are unique, the resulting pivoted table would have dimensions of O(m × c) where m is the number of unique months and c is the number of unique cities, but this still equals O(n) total cells.

Common Pitfalls

1. Duplicate Index-Column Combinations

The most critical pitfall occurs when the DataFrame contains duplicate combinations of (month, city) pairs. The pivot() method will raise a ValueError if there are multiple temperature values for the same month-city combination, as it cannot determine which value to use in the pivoted cell.

Example of problematic data:

# This will cause an error
weather = pd.DataFrame({
    'city': ['Jacksonville', 'Jacksonville', 'ElPaso'],
    'month': ['January', 'January', 'January'],
    'temperature': [13, 15, 5]  # Two different temps for Jacksonville in January
})

Solution: Use pivot_table() instead, which can handle duplicates by aggregating them:

def pivotTable(weather: pd.DataFrame) -> pd.DataFrame:
    # Use pivot_table with an aggregation function (e.g., mean, first, last)
    return weather.pivot_table(index='month', columns='city', values='temperature', 
                              aggfunc='mean')  # or 'first', 'last', etc.

2. Missing Data Handling

When not all cities have temperature data for every month, the pivot operation creates NaN (missing) values in the resulting DataFrame. This might cause issues in downstream processing.

Solution: Fill missing values with a default temperature or interpolate:

def pivotTable(weather: pd.DataFrame) -> pd.DataFrame:
    result = weather.pivot(index='month', columns='city', values='temperature')
    # Option 1: Fill with a default value
    return result.fillna(0)
    # Option 2: Forward fill or interpolate
    # return result.fillna(method='ffill')

3. Incorrect Month Sorting

The pivoted DataFrame sorts the index alphabetically, which means months appear as: April, August, December, February, etc., rather than in chronological order.

Solution: Reindex with proper month ordering:

def pivotTable(weather: pd.DataFrame) -> pd.DataFrame:
    result = weather.pivot(index='month', columns='city', values='temperature')
    # Define correct month order
    month_order = ['January', 'February', 'March', 'April', 'May', 'June',
                   'July', 'August', 'September', 'October', 'November', 'December']
    # Reindex to get chronological order (only include months present in data)
    present_months = [m for m in month_order if m in result.index]
    return result.reindex(present_months)

4. Data Type Issues

If the temperature column contains non-numeric values or mixed types, the pivot operation might succeed but produce unexpected results or make numerical operations difficult.

Solution: Ensure proper data type conversion before pivoting:

def pivotTable(weather: pd.DataFrame) -> pd.DataFrame:
    # Convert temperature to numeric, handling errors
    weather['temperature'] = pd.to_numeric(weather['temperature'], errors='coerce')
    return weather.pivot(index='month', columns='city', values='temperature')