基于重复记录生成新特征：Pandas多字段分组转年份列宽表

Hey there! Let's fix this pandas reshaping issue you're facing. First, I notice there's a small inconsistency in your expected output (the 90 value from 2016 is listed under 2014) — I'll assume that's a typo and focus on the core requirement: grouping by event and person, then pivoting years into columns while handling multiple price entries per year.

Step 1: Recreate the Original DataFrame #

First, let's start with your raw data:

import pandas as pd

data = {
    'event_name': ['event_a', 'event_a', 'event_a', 'event_a', 'event_b', 'event_b'],
    'event_person_firstname': ['foo', 'foo', 'foo', 'not', 'random', 'random'],
    'event_person_lastname': ['bar', 'bar', 'bar', 'same', 'name', 'name'],
    'price': [100, 42, 90, 80, 200, 42],
    'year': [2017, 2016, 2016, 2015, 2018, 2010]
}

df = pd.DataFrame(data)

Step 2: Handle Duplicate Entries per Year #

Since some person-event combinations have multiple prices in the same year, we need to add a sequence number to distinguish these entries. This lets us pivot them into separate columns (e.g., 2016_1, 2016_2):

# Add a counter for duplicate year entries within each group
df['entry_seq'] = df.groupby(
    ['event_name', 'event_person_firstname', 'event_person_lastname', 'year']
).cumcount() + 1

# Combine year and sequence into a new column name
df['year_column'] = df['year'].astype(str) + '_' + df['entry_seq'].astype(str)

Step 3: Pivot the Data #

Now we'll use pivot() to reshape the data, with our group columns as rows and the combined year-sequence strings as columns:

# Pivot the DataFrame
pivoted_df = df.pivot(
    index=['event_name', 'event_person_firstname', 'event_person_lastname'],
    columns='year_column',
    values='price'
).reset_index()

# Optional: Sort columns by year for readability
sorted_columns = sorted(pivoted_df.columns[3:], key=lambda x: int(x.split('_')[0]))
pivoted_df = pivoted_df[
    ['event_name', 'event_person_firstname', 'event_person_lastname'] + sorted_columns
]

Final Output #

The resulting DataFrame will look like this (NaN for missing values):

event_name event_person_firstname event_person_lastname  2010_1  2015_1  2016_1  2016_2  2017_1  2018_1
0    event_a                     foo                   bar     NaN     NaN    42.0    90.0   100.0     NaN
1    event_a                     not                   same     NaN    80.0     NaN     NaN     NaN     NaN
2    event_b                   random                   name    42.0     NaN     NaN     NaN     NaN   200.0

If You Want Aggregated Values Instead #

If you'd prefer to combine multiple prices per year (e.g., sum, average) instead of splitting into columns, use pivot_table() with an aggregation function:

aggregated_df = df.pivot_table(
    index=['event_name', 'event_person_firstname', 'event_person_lastname'],
    columns='year',
    values='price',
    aggfunc='sum',  # Replace with 'mean', 'max', etc. as needed
    fill_value=pd.NA
).reset_index()

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