Reshape Data: Pivot

Reshape Data: Pivot - Problem

Database Easy

You have a DataFrame weather with the following structure:

Column Name	Type
city	object
month	object
temperature	int

Write a solution to pivot the data so that each row represents temperatures for a specific month, and each city is a separate column.

The result should transform data from a long format (multiple rows per month) to a wide format (one row per month with cities as columns).

Input & Output

Example 1 — Basic Weather Data

$ Input: weather_data = [["New York", "January", 25], ["Los Angeles", "January", 75], ["New York", "February", 30], ["Los Angeles", "February", 80]]

› Output: [["month", "Los Angeles", "New York"], ["February", 80, 30], ["January", 75, 25]]

💡 Note: Transform from long format (city-month-temp rows) to wide format where months become rows and cities become columns. Cities are sorted alphabetically.

Example 2 — Missing Data

$ Input: weather_data = [["Miami", "March", 85], ["Seattle", "April", 55], ["Miami", "April", 88]]

› Output: [["month", "Miami", "Seattle"], ["April", 88, 55], ["March", 85, null]]

💡 Note: When a city has no temperature data for a specific month, the pivot table shows null for that cell (Seattle has no March data).

Example 3 — Single Record

$ Input: weather_data = [["Chicago", "December", 15]]

› Output: [["month", "Chicago"], ["December", 15]]

💡 Note: Even with minimal data, the pivot structure is maintained with proper headers and single data row.

Constraints

1 ≤ weather_data.length ≤ 1000
weather_data[i].length = 3
weather_data[i] = [city, month, temperature]
All city and month names are non-empty strings
All temperatures are integers

Visualization

Tap to expand

Asked in

f Meta 15 G Google 12 ⊞ Microsoft 10 a Amazon 8

The key insight is to transform row-wise data into column-wise by using cities as column headers and months as row indices. The optimal approach uses a single-pass algorithm with hash maps to build the pivot structure efficiently. Time: O(n + m log m + c log c), Space: O(m×c).

Common Approaches

✓ Manual Dictionary Building

⏱️ Time: O(n) Space: O(m×c)

Create a nested dictionary structure where outer keys are months and inner keys are cities. Iterate through all records to populate the structure, then convert to final format.

Pandas-like Pivot Implementation

⏱️ Time: O(n + m log m + c log c) Space: O(m×c)

Use dictionaries and sets efficiently to build the pivot table in a single pass, mimicking pandas pivot functionality with better performance and cleaner code structure.

Manual Dictionary Building — Algorithm Steps

Step 1: Create nested dictionary structure
Step 2: Iterate through records and populate dictionary
Step 3: Convert to DataFrame format

Visualization

Tap to expand

Step-by-Step Walkthrough

Parse Input

Read weather records as [city, month, temperature]

Build Dictionary

Create month->city->temperature mapping

Format Output

Convert to tabular format with months as rows

Code -

solution.c — C

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

#define MAX_RECORDS 100
#define MAX_STR_LEN 50
#define MAX_CITIES 20
#define MAX_MONTHS 20

typedef struct {
    char city[MAX_STR_LEN];
    char month[MAX_STR_LEN];
    int temp;
} Record;

// Global arrays to avoid stack overflow
static int temps[MAX_MONTHS][MAX_CITIES];
static int hasTemp[MAX_MONTHS][MAX_CITIES];
static char cities[MAX_CITIES][MAX_STR_LEN];
static char months[MAX_MONTHS][MAX_STR_LEN];

typedef struct {
    int cityCount;
    int monthCount;
} PivotResult;

int findCity(const char* city, int cityCount) {
    for (int i = 0; i < cityCount; i++) {
        if (strcmp(cities[i], city) == 0) {
            return i;
        }
    }
    return -1;
}

int findMonth(const char* month, int monthCount) {
    for (int i = 0; i < monthCount; i++) {
        if (strcmp(months[i], month) == 0) {
            return i;
        }
    }
    return -1;
}

void sortStrings(char arr[][MAX_STR_LEN], int n) {
    for (int i = 0; i < n - 1; i++) {
        for (int j = 0; j < n - i - 1; j++) {
            if (strcmp(arr[j], arr[j + 1]) > 0) {
                char temp[MAX_STR_LEN];
                strcpy(temp, arr[j]);
                strcpy(arr[j], arr[j + 1]);
                strcpy(arr[j + 1], temp);
            }
        }
    }
}

PivotResult solution(Record* weatherData, int dataSize) {
    PivotResult result;
    result.cityCount = 0;
    result.monthCount = 0;
    
    // Initialize hasTemp array
    for (int i = 0; i < MAX_MONTHS; i++) {
        for (int j = 0; j < MAX_CITIES; j++) {
            hasTemp[i][j] = 0;
        }
    }
    
    if (dataSize == 0) {
        return result;
    }
    
    // Build cities set first (matching Python logic)
    for (int i = 0; i < dataSize; i++) {
        int cityIndex = findCity(weatherData[i].city, result.cityCount);
        if (cityIndex == -1) {
            strcpy(cities[result.cityCount], weatherData[i].city);
            result.cityCount++;
        }
    }
    
    // Sort cities (matching Python sorted(list(cities)))
    sortStrings(cities, result.cityCount);
    
    // Build pivot_dict: month -> city -> temperature
    for (int i = 0; i < dataSize; i++) {
        // Find or add month
        int monthIndex = findMonth(weatherData[i].month, result.monthCount);
        if (monthIndex == -1) {
            strcpy(months[result.monthCount], weatherData[i].month);
            monthIndex = result.monthCount++;
        }
        
        // Find city index in sorted cities
        int cityIndex = findCity(weatherData[i].city, result.cityCount);
        
        // Set temperature
        temps[monthIndex][cityIndex] = weatherData[i].temp;
        hasTemp[monthIndex][cityIndex] = 1;
    }
    
    // Sort months (matching Python sorted(pivot_dict.keys()))
    // We need to preserve the temp mapping, so we'll rebuild after sorting
    static int tempBackup[MAX_MONTHS][MAX_CITIES];
    static int hasTempBackup[MAX_MONTHS][MAX_CITIES];
    static char monthsBackup[MAX_MONTHS][MAX_STR_LEN];
    
    // Create backup with month names for sorting
    for (int i = 0; i < result.monthCount; i++) {
        strcpy(monthsBackup[i], months[i]);
        for (int j = 0; j < result.cityCount; j++) {
            tempBackup[i][j] = temps[i][j];
            hasTempBackup[i][j] = hasTemp[i][j];
        }
    }
    
    // Sort months
    sortStrings(months, result.monthCount);
    
    // Rebuild temp mapping with sorted month order
    for (int sortedMonthIdx = 0; sortedMonthIdx < result.monthCount; sortedMonthIdx++) {
        // Find original position of this month
        int originalMonthIdx = -1;
        for (int i = 0; i < result.monthCount; i++) {
            if (strcmp(monthsBackup[i], months[sortedMonthIdx]) == 0) {
                originalMonthIdx = i;
                break;
            }
        }
        
        // Copy temp data from original position to sorted position
        for (int j = 0; j < result.cityCount; j++) {
            temps[sortedMonthIdx][j] = tempBackup[originalMonthIdx][j];
            hasTemp[sortedMonthIdx][j] = hasTempBackup[originalMonthIdx][j];
        }
    }
    
    return result;
}

int parseWeatherData(const char* line, Record* weatherData) {
    int dataSize = 0;
    
    if (strcmp(line, "[]") == 0) {
        return 0;
    }
    
    const char* ptr = line + 1; // Skip first '['
    
    while (*ptr && *ptr != ']') {
        if (*ptr == '[') {
            ptr++; // Skip '['
            
            // Skip whitespace
            while (*ptr == ' ') ptr++;
            
            // Parse city (skip quote)
            if (*ptr == '"') {
                ptr++; // Skip opening quote
                
                int cityLen = 0;
                while (*ptr && *ptr != '"') {
                    weatherData[dataSize].city[cityLen++] = *ptr++;
                }
                weatherData[dataSize].city[cityLen] = '\0';
                ptr++; // Skip closing quote
            }
            
            // Skip comma and space
            while (*ptr && (*ptr == ',' || *ptr == ' ')) ptr++;
            
            // Parse month (skip quote)
            if (*ptr == '"') {
                ptr++; // Skip opening quote
                
                int monthLen = 0;
                while (*ptr && *ptr != '"') {
                    weatherData[dataSize].month[monthLen++] = *ptr++;
                }
                weatherData[dataSize].month[monthLen] = '\0';
                ptr++; // Skip closing quote
            }
            
            // Skip comma and space
            while (*ptr && (*ptr == ',' || *ptr == ' ')) ptr++;
            
            // Parse temperature
            int temp = 0;
            int negative = 0;
            if (*ptr == '-') {
                negative = 1;
                ptr++;
            }
            while (*ptr >= '0' && *ptr <= '9') {
                temp = temp * 10 + (*ptr - '0');
                ptr++;
            }
            weatherData[dataSize].temp = negative ? -temp : temp;
            
            // Skip to end of inner array
            while (*ptr && *ptr != ']') ptr++;
            if (*ptr == ']') ptr++; // Skip ']'
            
            dataSize++;
        } else {
            ptr++;
        }
    }
    
    return dataSize;
}

void printResult(PivotResult* result) {
    printf("[");
    
    // Print header
    printf("[\"month\"");
    for (int j = 0; j < result->cityCount; j++) {
        printf(", \"%s\"", cities[j]);
    }
    printf("]");
    
    // Print data rows
    for (int i = 0; i < result->monthCount; i++) {
        printf(", [\"%s\"", months[i]);
        for (int j = 0; j < result->cityCount; j++) {
            if (hasTemp[i][j]) {
                printf(", %d", temps[i][j]);
            } else {
                printf(", null");
            }
        }
        printf("]");
    }
    
    printf("]\n");
}

int main() {
    char line[10000];
    if (fgets(line, sizeof(line), stdin) == NULL) {
        printf("[]\n");
        return 0;
    }
    
    // Remove newline
    int len = strlen(line);
    if (len > 0 && line[len-1] == '\n') {
        line[len-1] = '\0';
    }
    
    Record weatherData[MAX_RECORDS];
    int dataSize = parseWeatherData(line, weatherData);
    
    PivotResult result = solution(weatherData, dataSize);
    
    printResult(&result);
    
    return 0;
}

Time & Space Complexity

Time Complexity

⏱️

O(n)

Single pass through all n records to build the pivot structure

⚡ Linearithmic

Space Complexity

O(m×c)

Store m months × c cities in the result structure

✓ Linear Space

23.4K Views

Medium Frequency

~15 min Avg. Time

890 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

Constraints

Visualization

Related Problems

Common Approaches

Manual Dictionary Building — Algorithm Steps

Visualization

Code -

Time & Space Complexity

Select Compiler