如何按id和日期分组生成Pandas DataFrame的timedelta列？

现有包含id和datetime列的Pandas DataFrame，数据如下：

import pandas as pd

df = pd.DataFrame({"id": ["a1", "a1", "a1", "a1", "a2", "a2", "a2", "a2", "a3", "a3", "a3", "a3"],
                   "datetime": ["2016-01-01 00:01:00.156",
                                "2016-01-01 12:00:00.425",
                                "2016-01-02 00:59:00.123",
                                "2016-01-02 14:16:00.548",
                                "2016-01-01 12:00:00.147",
                                "2016-01-01 13:59:00.123",
                                "2016-01-02 08:01:00.147",
                                "2016-01-02 18:49:00.123",
                                "2016-02-01 12:00:00.147",
                                "2016-02-01 13:59:00.123",
                                "2016-02-02 08:01:00.147",
                                "2016-02-02 18:49:00.123"]})
df["datetime"] = pd.to_datetime(df["datetime"])

需要生成timedelta列，规则为：按id和日期（YYYY-MM-DD）分组，取每组内最早的datetime作为datetime_baseline，计算每条数据的datetime与该基准的时间差（单位：分钟），预期输出如下：

id  datetime                datetime_baseline       timedelta
0   a1  2016-01-01 00:01:00.156 2016-01-01 00:01:00.156 0
1   a1  2016-01-01 12:00:00.425 2016-01-01 00:01:00.156 719
2   a1  2016-01-02 00:59:00.123 2016-01-02 00:59:00.123 0
3   a1  2016-01-02 14:16:00.548 2016-01-02 00:59:00.123 797
4   a2  2016-01-01 12:00:00.147 2016-01-01 12:00:00.147 0
5   a2  2016-01-01 13:59:00.123 2016-01-01 12:00:00.147 119
6   a2  2016-01-02 08:01:00.147 2016-01-02 08:01:00.147 0
7   a2  2016-01-02 18:49:00.123 2016-01-02 08:01:00.147 648
8   a3  2016-02-01 12:00:00.147 2016-02-01 12:00:00.147 0
9   a3  2016-02-01 13:59:00.123 2016-02-01 12:00:00.147 119
10  a3  2016-02-02 08:01:00.147 2016-02-02 08:01:00.147 0
11  a3  2016-02-02 18:49:00.123 2016-02-02 08:01:00.147 648

实际数据量超50万行，需要高效的解决方案。

针对50万行的大数据量，推荐使用Pandas的groupby结合transform方法，既保证效率又简洁实现需求：

1. 生成datetime_baseline列
   按id和datetime的日期部分分组，对每组的datetime取最小值，通过transform将结果映射回原DataFrame的每一行：

df['datetime_baseline'] = df.groupby(['id', df['datetime'].dt.date])['datetime'].transform('min')

2. 计算timedelta列（单位：分钟）
   直接计算时间差，将差值转换为秒后除以60取整：

df['timedelta'] = (df['datetime'] - df['datetime_baseline']).dt.total_seconds() // 60

3. 完整执行代码

import pandas as pd

df = pd.DataFrame({"id": ["a1", "a1", "a1", "a1", "a2", "a2", "a2", "a2", "a3", "a3", "a3", "a3"],
                   "datetime": ["2016-01-01 00:01:00.156",
                                "2016-01-01 12:00:00.425",
                                "2016-01-02 00:59:00.123",
                                "2016-01-02 14:16:00.548",
                                "2016-01-01 12:00:00.147",
                                "2016-01-01 13:59:00.123",
                                "2016-01-02 08:01:00.147",
                                "2016-01-02 18:49:00.123",
                                "2016-02-01 12:00:00.147",
                                "2016-02-01 13:59:00.123",
                                "2016-02-02 08:01:00.147",
                                "2016-02-02 18:49:00.123"]})
df["datetime"] = pd.to_datetime(df["datetime"])

# 生成基准时间列
df['datetime_baseline'] = df.groupby(['id', df['datetime'].dt.date])['datetime'].transform('min')
# 计算时间差（分钟）
df['timedelta'] = (df['datetime'] - df['datetime_baseline']).dt.total_seconds() // 60

print(df)

效率说明 #

groupby+transform是Pandas中处理分组映射的原生高效方法，避免了循环遍历，对于50万行的数据能快速完成计算，不会出现性能瓶颈。

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