如何转置含重复值的Pandas DataFrame？pivot尝试未生效

First, let's break down why your original pivot() call failed: the pivot() method requires that each combination of index and columns maps to a single unique value in the values column. Looking at your data, for example, when Year=2008 and Heading=TNMAB123, there are 4 different rate values (corresponding to combinations of Gender and day_type). Pandas can't automatically pick which value to use, so it throws an error.

Here are two solutions to get your desired structure, depending on what you need from the final output:

Solution 1: Create Multi-Level Columns (Include All Relevant Dimensions) #

If you want to preserve all the context from Gender and day_type in your columns, you can expand the columns parameter to include these features. This creates a multi-level column index that ensures every (Year, Heading, Gender, day_type) combination is unique:

import pandas as pd

# Your original DataFrame
df1 = pd.DataFrame({
    'Gender': ['Male','Male','Male','Male','Female','Female','Female','Female','Male','Male','Male','Male','Female','Female','Female','Female'],
    'Year': [2008,2008,2009,2009,2008,2008,2009,2009,2008,2008,2009,2009,2008,2008,2009,2009],
    'rate': [2.3,3.2,4.5,6.7,5.6,3.2,3.5,2.6,2.3,3.2,4.5,6.7,5.6,3.2,3.5,2.6],
    'Heading': ['TNMAB123']*8 + ['TNMAB456']*8,
    'target': [31.2,33.4,33.4,35.2,35.2,36.4,36.4,37.2]*2,
    'day_type': ['wk','wkend','wk','wkend','wk','wkend','wk','wkend']*2
})

# Pivot with multi-level columns
pivoted_df = df1.pivot(
    index='Year',
    columns=['Heading', 'Gender', 'day_type'],
    values='rate'
)

print(pivoted_df)

The output will have columns structured like TNMAB123 -> Male -> wk, so you can clearly see which rate corresponds to each subset of your data.

Solution 2: Use pivot_table to Aggregate Duplicate Values #

If you don't need to preserve all the granularity (or if you want to summarize repeated values), use pivot_table() instead. This method lets you specify an aggregation function to handle duplicate (index, columns) pairs. Since your data has identical rate values for matching combinations, functions like mean, first, or sum will all work:

# Aggregate to get a single value per (Year, Heading) pair
pivoted_table = df1.pivot_table(
    index='Year',
    columns='Heading',
    values='rate',
    aggfunc='mean'  # You can also use 'first' or 'sum' here
)

print(pivoted_table)

If you still want to include Gender and day_type but need to aggregate, just add them to the columns parameter:

pivoted_table_full = df1.pivot_table(
    index='Year',
    columns=['Heading', 'Gender', 'day_type'],
    values='rate',
    aggfunc='first'
)

print(pivoted_table_full)

Either approach will fix your pivot issue—pick the one that matches how you want to analyze your data!

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