Reshape Data: Pivot - Practice Coding Problems

Reshape Data: Pivot - Problem

Database Easy

Transform Your Data with Pivot Operations

You're given a DataFrame containing weather data with city, month, and temperature information. Your task is to reshape this data by pivoting it so that:

• Each row represents a specific month
• Each column represents a different city
• The values show the temperature for that city in that month

This is a common data transformation operation used in analytics and reporting. Think of it as rotating your data table to get a different perspective on the same information.

Input DataFrame:

city     | month | temperature
---------|-------|------------
London   | Jan   | 5
London   | Feb   | 8
Paris    | Jan   | 7
Paris    | Feb   | 10

Output (Pivoted):

month | London | Paris
------|--------|------
Jan   | 5      | 7
Feb   | 8      | 10

Input & Output

basic_weather.py — Basic Weather Data

$ Input: weather = pd.DataFrame({ 'city': ['London', 'London', 'Paris', 'Paris'], 'month': ['Jan', 'Feb', 'Jan', 'Feb'], 'temperature': [5, 8, 7, 10] })

› Output: month London Paris 0 Jan 5 7 1 Feb 8 10

💡 Note: Simple pivot operation transforms long-form weather data into a month-by-city comparison table. Each city becomes a column, each month becomes a row.

multiple_cities.py — Multiple Cities Dataset

$ Input: weather = pd.DataFrame({ 'city': ['London', 'Paris', 'Berlin', 'London', 'Paris', 'Berlin'], 'month': ['Jan', 'Jan', 'Jan', 'Feb', 'Feb', 'Feb'], 'temperature': [5, 7, 2, 8, 10, 6] })

› Output: month Berlin London Paris 0 Jan 2 5 7 1 Feb 6 8 10

💡 Note: With more cities, the pivot creates additional columns. Cities are automatically sorted alphabetically, and the table shows temperature comparison across all cities for each month.

missing_data.py — Handling Missing Values

$ Input: weather = pd.DataFrame({ 'city': ['London', 'Paris', 'London'], 'month': ['Jan', 'Jan', 'Feb'], 'temperature': [5, 7, 8] })

› Output: month London Paris 0 Jan 5 7 1 Feb 8 NaN

💡 Note: When data is missing for certain month-city combinations, pandas automatically fills those cells with NaN (Not a Number) values, preserving the table structure.

Visualization

Tap to expand

Understanding the Visualization

Original Layout

Cards are arranged in a long list format - one card per city-month combination

Identify Structure

Recognize that months should become row headers and cities should become column headers

Reorganize Cards

Physically move cards into a grid where month-city intersections show temperatures

Final Grid

Result is a structured comparison table perfect for analysis

Key Takeaway

🎯 Key Insight: Pivot operations transform data perspective from record-by-record to dimension-by-dimension analysis, making patterns and comparisons immediately visible.

Time & Space Complexity

Time Complexity

⏱️

O(n)

Single pass through data with efficient grouping operations

✓ Linear Growth

Space Complexity

O(m×c)

Output size determined by unique months × unique cities

✓ Linear Space

Constraints

1 ≤ number of records ≤ 10⁴
City and month names are non-empty strings
Temperature values are integers between -50 and 50
Each city-month combination appears at most once
At least one complete record must be present

Asked in

G Google 45 f Meta 38 a Amazon 42 ⊞ Microsoft 35

The optimal solution uses pandas' built-in pivot() function, which efficiently transforms the DataFrame structure in O(n) time. The key insight is leveraging pandas' internal C-optimized algorithms for grouping and reshaping rather than manual iteration. Alternative approaches include manual pivot construction, but pandas provides the most readable and performant solution for this data transformation task.

Common Approaches

Approach	Time	Space	Notes
✓ Pandas Pivot Function (Optimal)	O(n)	O(m×c)	Use pandas built-in pivot() method for optimal performance
Manual Pivot Construction	O(n²)	O(m×c)	Manually iterate through data to build pivot structure

Pandas Pivot Function (Optimal) — Algorithm Steps

Use df.pivot() with appropriate parameters
Specify 'month' as index (rows)
Specify 'city' as columns
Specify 'temperature' as values
Pandas handles grouping and reshaping internally

Visualization

Tap to expand

Step-by-Step Walkthrough

Input Analysis

Pandas analyzes the data structure and identifies grouping keys

Internal Grouping

Groups data by month (index) and city (columns) simultaneously

Memory Allocation

Allocates output DataFrame structure based on unique values

Value Assignment

Maps temperature values to correct positions in one pass

Code -

solution.c — C

#include <stdio.h>
#include <stdlib.h>
#include <string.h>

#define MAX_CITIES 10
#define MAX_MONTHS 12
#define MAX_RECORDS 100
#define MAX_NAME_LEN 20

typedef struct {
    char city[MAX_NAME_LEN];
    char month[MAX_NAME_LEN];
    int temperature;
} WeatherRecord;

typedef struct {
    char months[MAX_MONTHS][MAX_NAME_LEN];
    char cities[MAX_CITIES][MAX_NAME_LEN];
    int temperatures[MAX_MONTHS][MAX_CITIES];
    int month_count;
    int city_count;
} PivotResult;

// Hash-like lookup for efficiency
int findIndex(char arr[][MAX_NAME_LEN], int count, const char* item) {
    for (int i = 0; i < count; i++) {
        if (strcmp(arr[i], item) == 0) {
            return i;
        }
    }
    return -1;
}

PivotResult pivotWeatherOptimal(WeatherRecord* weather, int count) {
    PivotResult result = {0};
    
    // Initialize temperature matrix with invalid values
    for (int i = 0; i < MAX_MONTHS; i++) {
        for (int j = 0; j < MAX_CITIES; j++) {
            result.temperatures[i][j] = -999;
        }
    }
    
    // Single pass to build structure and fill data
    for (int i = 0; i < count; i++) {
        // Find or add month
        int month_idx = findIndex(result.months, result.month_count, weather[i].month);
        if (month_idx == -1) {
            strcpy(result.months[result.month_count], weather[i].month);
            month_idx = result.month_count;
            result.month_count++;
        }
        
        // Find or add city
        int city_idx = findIndex(result.cities, result.city_count, weather[i].city);
        if (city_idx == -1) {
            strcpy(result.cities[result.city_count], weather[i].city);
            city_idx = result.city_count;
            result.city_count++;
        }
        
        // Set temperature
        result.temperatures[month_idx][city_idx] = weather[i].temperature;
    }
    
    return result;
}

void printPivotResult(PivotResult* result) {
    printf("month");
    for (int c = 0; c < result->city_count; c++) {
        printf("\t%s", result->cities[c]);
    }
    printf("\n");
    
    for (int m = 0; m < result->month_count; m++) {
        printf("%s", result->months[m]);
        for (int c = 0; c < result->city_count; c++) {
            if (result->temperatures[m][c] != -999) {
                printf("\t%d", result->temperatures[m][c]);
            } else {
                printf("\t-");
            }
        }
        printf("\n");
    }
}

int main() {
    WeatherRecord weather[] = {
        {"London", "Jan", 5}, {"London", "Feb", 8},
        {"Paris", "Jan", 7}, {"Paris", "Feb", 10}
    };
    int count = 4;
    
    PivotResult result = pivotWeatherOptimal(weather, count);
    printPivotResult(&result);
    
    return 0;
}

Time & Space Complexity

Time Complexity

⏱️

O(n)

Single pass through data with efficient grouping operations

✓ Linear Growth

Space Complexity

O(m×c)

Output size determined by unique months × unique cities

✓ Linear Space

Constraints

1 ≤ number of records ≤ 10⁴
City and month names are non-empty strings
Temperature values are integers between -50 and 50
Each city-month combination appears at most once
At least one complete record must be present

42.0K Views

High Frequency

~12 min Avg. Time

1.9K Likes

Ln 1, Col 1

Smart Actions

💡 Explanation

AI Ready

💡 Suggestion Tab to accept Esc to dismiss

// Output will appear here after running code

Code Editor Closed

Click the red button to reopen

Input & Output

Visualization

Time & Space Complexity

Related Problems

Constraints

Common Approaches

Pandas Pivot Function (Optimal) — Algorithm Steps

Visualization

Code -

Time & Space Complexity

Constraints

Select Compiler