将日期转换为标准化日期列：Pandas DataFrame透视问题咨询

Got it, you're on the right track with pivot—we just need to add a sequential identifier for each date within each group first. Here's a step-by-step approach that will get you exactly the output you want:

Step 1: Sort the DataFrame to ensure dates are in order #

First, we sort by the grouping columns (Name, Spec1, Spec2) and then Date so that the first occurrence of a date in each group is indeed the earliest one:

import pandas as pd
import numpy as np

# Your original DataFrame setup
df = pd.DataFrame({'Name': {0: "A", 1: "A", 2:"B", 3: "B", 4: "A"}, 
                   'Spec1': {0: '1', 1: '1', 2:'5', 3: '5', 4: '1'}, 
                   'Spec2': {0: '2a', 1: '2a', 2:np.nan, 3: np.nan, 4: '2a'}, 
                   'Date': {0: '10/1/2007', 1: '11/1/2007', 2:'7/1/2009', 3: '8/1/2009', 4: '12/1/2007'}, 
                   'Value': {0: 5, 1: 10, 2: 3, 3: 4, 4: 8} }, 
                  columns=['Name', 'Spec1', 'Spec2','Date','Value'])
df['Date'] = pd.to_datetime(df['Date'])

# Sort the DataFrame
df_sorted = df.sort_values(['Name', 'Spec1', 'Spec2', 'Date'])

Step 2: Add a sequential date counter per group #

We use groupby combined with cumcount() to assign a number (starting at 1) to each row within its group. This will become our Date1, Date2, etc. column identifier:

df_sorted['Date_seq'] = df_sorted.groupby(['Name', 'Spec1', 'Spec2']).cumcount() + 1

Step 3: Pivot the DataFrame #

Now we pivot using the grouping columns as the index, the sequential counter as columns, and Value as the values. Then we reset the index to turn the grouping columns back into regular columns:

pivoted_df = df_sorted.pivot(
    index=['Name', 'Spec1', 'Spec2'],
    columns='Date_seq',
    values='Value'
).reset_index()

Step 4: Rename the columns to match your desired format #

Finally, we rename the numbered columns to Date1, Date2, Date3:

# Rename the columns
pivoted_df.columns = ['Name', 'Spec1', 'Spec2'] + [f'Date{i}' for i in pivoted_df.columns if isinstance(i, int)]

Final Output #

Running this code will give you exactly the structure you wanted:

Name Spec1 Spec2  Date1  Date2  Date3
0    A     1    2a      5     10      8
1    B     5   NaN      3      4    NaN

This approach works even if you have more than 3 dates per group—it will automatically create Date4, Date5, etc. as needed. And it correctly handles NaN values in Spec2 since pandas treats NaN as a valid group key in groupby.

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