python - pandas groupby agg 从一列中获取最大值并从另一列中获取值

Question

我有一个购买数据框:

product_id    count    timestamp           customer_id
1             1        2021-10-04 10:20    a
1             3        2021-10-04 10:21    b
2             4        2021-10-04 10:00    c
1             2        2021-10-03 10:00    c

我使用以下 groupby 和 agg 来创建计数总和和平均值以及最新购买时间戳的报告。

report = (
    df.groupby(product_id).agg(
        sum=pd.NamedAgg(column="count", aggfunc="sum"),
        mean_count=pd.NamedAgg(column="count", aggfunc="mean"),
        latest_purchase_time=pd.NamedAgg(column="timestamp", aggfunc="max")
    )
)

我想在此报告中包含与最新购买时间戳相对应的 customer_id。有办法做到这一点吗？

例如:

product_id    sum    mean_count    latest_purchase_timestamp   *customer_id*
1             6      2             2021-10-04 10:21            b
2             4      4             2021-10-04 10:00            c

Answer 1

首先将customer_id 转换为index，这样可以通过idxmax 的最大timestamp 获取值:

report = (
    df.set_index('customer_id')
      .groupby('product_id').agg(
        sum=pd.NamedAgg(column="count", aggfunc="sum"),
        mean_count=pd.NamedAgg(column="count", aggfunc="mean"),
        latest_purchase_time=pd.NamedAgg(column="timestamp", aggfunc="max"),
        customer_id=pd.NamedAgg(column="timestamp", aggfunc="idxmax")
    )
)
print (report)
            sum  mean_count latest_purchase_time customer_id
product_id                                                  
1             6           2  2021-10-04 10:21:00           b
2             4           4  2021-10-04 10:00:00           c