Pandas转换DataFrame并计算日期差：按分组分配列的实现求助

Got it, let's work through this problem together! I totally get why combining groupby() and assign() might feel like the right approach at first, but this kind of "reshape values into columns based on a categorical field" task is better handled with pivoting or unstacking. Let's break it down step by step.

第一步：构造示例数据（方便你对应自己的数据集） #

First, let's make a sample DataFrame that matches your description—this will make it easier to see how the solution works:

import pandas as pd

data = {
    'col1': ['A', 'A', 'B', 'B', 'B'],
    'col2': pd.to_datetime(['2023-01-01', '2023-01-05', '2023-02-10', '2023-02-15', '2023-02-20']),
    'col3': ['X', 'Y', 'X', 'Y', 'Z'],
    'col4': pd.to_datetime(['2023-01-10', '2023-01-15', '2023-02-20', '2023-02-25', '2023-02-25'])
}
df = pd.DataFrame(data)

第二步：计算日期差 #

First, we'll calculate the day difference between col2 and col4. I'm using col4 - col2 here to get positive days, but you can swap them or use abs() if you just need the absolute difference:

df['date_diff'] = (df['col4'] - df['col2']).dt.days

第三步：将col3的唯一值转为列（核心步骤） #

This is where groupby() + assign() falls short—we need to reshape the data so each unique value in col3 becomes its own column, with the date difference as the value, grouped by col1.

方法1：使用pivot()（适合无重复的col1+col3组合） #

If each pair of col1 and col3 has exactly one row, pivot() is perfect:

# 透视数据：索引为col1，列是col3的唯一值，值为date_diff
result = df.pivot(index='col1', columns='col3', values='date_diff').reset_index()

# 去掉列名的层级标签（让输出更干净）
result.columns.name = None

print(result)

输出结果：

col1   X   Y   Z
0    A   9  10 NaN
1    B  10  10   5

方法2：使用pivot_table()（适合有重复的col1+col3组合） #

If you have multiple rows for the same col1 + col3 pair, use pivot_table() to specify an aggregation function (like mean, sum, or first):

# 这里用mean作为示例，你可以换成'first'、'sum'等
result = df.pivot_table(
    index='col1',
    columns='col3',
    values='date_diff',
    aggfunc='mean'
).reset_index()
result.columns.name = None

方法3：使用groupby() + unstack() #

Another way to do this is grouping by col1 and col3, then unstacking the col3 level into columns:

result = df.groupby(['col1', 'col3'])['date_diff'].first().unstack().reset_index()
result.columns.name = None

为什么groupby() + assign()没成功？ #

The assign() method adds columns to the original grouped DataFrame, but it can't dynamically create new columns based on unique values in another field (like col3). Pivoting/unstacking is designed exactly for this kind of "long-to-wide" data transformation, which is what you need here.

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