Python/Pandas多维数据透视求助：行转列后列数不足

Hey there! As a fellow Pandas learner, I totally get how pivoting can feel tricky when you're starting out. Let's break down why you're only getting 3 columns and how to adjust your code to get the full 6-column output you want.

The Likely Root Issue #

From what you described, your original code is probably only pivoting on the metric names (like "Average Response Time") but missing a second grouping dimension—think a shift, time period, or category—that splits each metric into two distinct columns. That's why you end up with 3 columns instead of the 6 you need!

Let's Assume Your Input Structure #

Since I can't see your input image, I'll go with a common structure that matches your desired output. Let's say your input_df looks like this (long format with date, a grouping column like shift, metric name, and value):

Solution: Pivot on Both Metric and Grouping Column #

Use pivot_table (or pivot) to include both the metric and your second grouping dimension (like Shift) in the columns. This will split each metric into two columns, giving you all 6 you need:

import pandas as pd

# Pivot the table using both Metric and Shift as column groups
output_df = input_df.pivot_table(
    index='Date',  # Keep Date as the row identifier
    columns=['Metric', 'Shift'],  # Group columns by both metric and shift
    values='Value'  # The values to populate the pivot table
).reset_index()

# Clean up column names to match your desired format
output_df.columns = [
    f"{metric} - {shift}" if col[0] != 'Date' else col[0]
    for col in output_df.columns.values
]

# Optional: Reorder columns to match your preferred sequence
desired_order = [
    'Date',
    'Average Response Time - Morning',
    'Average Response Time - Afternoon',
    'Call per hour - Morning',
    'Call per hour - Afternoon',
    'Number of calls - Morning',
    'Number of calls - Afternoon'
]
output_df = output_df[desired_order]

If Your Input Is Already in Wide Format #

If your input_df was originally wide (e.g., columns like Morning_Average), first convert it to long format with melt, then pivot back:

# Convert wide input to long format
melted_df = input_df.melt(
    id_vars='Date',
    var_name='Metric_Shift',
    value_name='Value'
)

# Split the combined column name into Shift and Metric
melted_df[['Shift', 'Metric']] = melted_df['Metric_Shift'].str.split('_', n=1, expand=True)

# Pivot to get your desired wide format
output_df = melted_df.pivot_table(
    index='Date',
    columns=['Metric', 'Shift'],
    values='Value'
).reset_index()

# Clean up column names
output_df.columns = [
    f"{metric} - {shift}" if col[0] != 'Date' else col[0]
    for col in output_df.columns.values
]

Why Your Original Code Failed #

Your initial code probably only specified Metric as the column to pivot on, like this:

# This would only produce 3 columns
bad_output = input_df.pivot(index='Date', columns='Metric', values='Value')

By adding the second grouping dimension (like Shift) to the columns parameter, you tell Pandas to split each metric into separate columns based on that dimension—resulting in the full 6 columns you're after.

内容的提问来源于stack exchange，提问作者Tad