如何用Pandas按日期条件为DataFrame添加status列？

算力阿峰

2026-8-21

问题

现有如下DataFrame：

id   value                 date
0    1   first  2022.06.25 16:47:11
1    1  second  2022.06.25 17:47:11
2    1  second  2022.06.26 12:47:11
3    2   first  2022.06.18 16:47:11
4    2  second  2022.06.18 16:55:11
5    3   first  2022.06.15 15:00:11
6    3   third  2022.06.15 16:47:11
7    3  fourth  2022.06.15 18:47:11
8    3   fifth  2022.06.17 10:47:11
9    4  second  2022.06.01 16:47:11
10   4   fifth  2022.06.02 07:47:11
11   5   first  2022.06.25 16:47:11
12   6   first  2022.06.10 16:47:11
13   6  second  2022.06.10 19:47:11
14   6   third  2022.06.11 08:47:11
15   7  fourth  2022.06.26 10:47:11
16   7   fifth  2022.06.27 15:47:11

需要完成以下操作：

先按id和date对DataFrame排序
按id分组后，根据以下规则添加新列status：
- 分组内前两行若日期（天）相同，status为ok
- 分组内存在多个日期，且该行与组内首行日期差超过24小时，status为nok
- 分组内同一日期的行超过2行，第2行之后的行status为norm
- 分组内日期不同，但与首行日期差小于24小时，status为norm

尝试用以下代码实现时，用transform计算每行与组内首行的时间差，结果只有每组首行有值，其余为NaT，无法继续后续判断：

import pandas as pd
import numpy as np
df = pd.DataFrame({'id' : [1,1,1,2,2,3,3,3,3,4,4,5,6,6,6,7,7],
                'value'  : ["first","second","second","first",
                            "second","first","third","fourth",
                            "fifth","second","fifth","first",
                            "first","second","third","fourth","fifth"],
                   'date' : ['2022.06.25 16:47:11','2022.06.25 17:47:11','2022.06.26 12:47:11','2022.06.18 16:47:11','2022.06.18 16:55:11', '2022.06.15 15:00:11','2022.06.15 16:47:11','2022.06.15 18:47:11','2022.06.17 10:47:11',
'2022.06.01 16:47:11','2022.06.02 07:47:11', '2022.06.25 16:47:11', '2022.06.10 16:47:11','2022.06.10 19:47:11','2022.06.11 08:47:11',
'2022.06.26 10:47:11', '2022.06.27 15:47:11']})
df['date'] = pd.to_datetime(df['date'], format='%Y.%m.%d %H:%M:%S')
df=df.sort_values(['id','date'])
df['time'] = df.groupby(['id'])['date'].transform(lambda x: x-df.groupby('id')['date'].head(1))

print (df)

运行结果：

id   value                date   time
0    1   first 2022-06-25 16:47:11 0 days
1    1  second 2022-06-25 17:47:11    NaT
2    1  second 2022-06-26 12:47:11    NaT
3    2   first 2022-06-18 16:47:11 0 days
4    2  second 2022-06-18 16:55:11    NaT
5    3   first 2022-06-15 15:00:11 0 days
6    3   third 2022-06-15 16:47:11    NaT
7    3  fourth 2022-06-15 18:47:11    NaT
8    3   fifth 2022-06-17 10:47:11    NaT
9    4  second 2022-06-01 16:47:11 0 days
10   4   fifth 2022-06-02 07:47:11    NaT
11   5   first 2022-06-25 16:47:11 0 days
12   6   first 2022-06-10 16:47:11 0 days
13   6  second 2022-06-10 19:47:11    NaT
14   6   third 2022-06-11 08:47:11    NaT
15   7  fourth 2022-06-26 10:47:11 0 days
16   7   fifth 2022-06-27 15:47:11    NaT

期望得到的结果：

id   value                  date    status
0    1   first 2022-06-25 16:47:11  ok
1    1  second 2022-06-25 17:47:11  ok
2    1  second 2022-06-26 12:47:11  norm
3    2   first 2022-06-18 16:47:11  ok
4    2  second 2022-06-18 16:55:11  ok
5    3   first 2022-06-15 15:00:11  ok
6    3   third 2022-06-15 16:47:11  ok
7    3  fourth 2022-06-15 18:47:11  norm
8    3   fifth 2022-06-17 10:47:11  nok
9    4  second 2022-06-01 16:47:11  ok
10   4   fifth 2022-06-02 07:47:11  norm
11   5   first 2022-06-25 16:47:11  ok
12   6   first 2022-06-10 16:47:11  ok
13   6  second 2022-06-10 19:47:11  ok
14   6   third 2022-06-11 08:47:11  norm
15   7  fourth 2022-06-26 10:47:11  ok
16   7   fifth 2022-06-27 15:47:11  nok

需要解决transform的问题，或者提供更简便的实现方案。

解决方案

1. 修复transform计算时间差的问题

你之前的代码中，在transform的lambda函数里再次调用df.groupby('id')['date'].head(1)，会返回整个DataFrame分组后的首行集合，而非当前组的首行，导致无法正确广播计算。正确写法是在lambda内部获取当前组的首行：

df['time'] = df.groupby(['id'])['date'].transform(lambda x: x - x.iloc[0])

这样就能得到每行与组内首行的时间差，不会出现NaT。

2. 完整实现status列的高效方案

结合需求，我们可以先计算所需的辅助判断列，再用np.select批量赋值status，代码更清晰高效：

import pandas as pd
import numpy as np

# 初始化DataFrame并处理日期
df = pd.DataFrame({
    'id': [1,1,1,2,2,3,3,3,3,4,4,5,6,6,6,7,7],
    'value': ["first","second","second","first","second","first","third","fourth","fifth","second","fifth","first","first","second","third","fourth","fifth"],
    'date': ['2022.06.25 16:47:11','2022.06.25 17:47:11','2022.06.26 12:47:11','2022.06.18 16:47:11','2022.06.18 16:55:11', '2022.06.15 15:00:11','2022.06.15 16:47:11','2022.06.15 18:47:11','2022.06.17 10:47:11',
             '2022.06.01 16:47:11','2022.06.02 07:47:11', '2022.06.25 16:47:11', '2022.06.10 16:47:11','2022.06.10 19:47:11','2022.06.11 08:47:11','2022.06.26 10:47:11', '2022.06.27 15:47:11']
})
df['date'] = pd.to_datetime(df['date'], format='%Y.%m.%d %H:%M:%S')
df = df.sort_values(['id', 'date']).reset_index(drop=True)

# 计算辅助判断列
df['row_num'] = df.groupby('id').cumcount()  # 组内行号（从0开始）
df['first_date'] = df.groupby('id')['date'].transform('first')  # 组内首行日期
df['hour_diff'] = (df['date'] - df['first_date']).dt.total_seconds() / 3600  # 与首行的小时差
df['date_group'] = df.groupby(['id', df['date'].dt.date]).cumcount()  # 同一id+天内的行号

# 定义条件与对应值
conditions = [
    # 条件1：前两行且与首行日期天相同
    (df['row_num'] <= 1) & (df['date'].dt.date == df['first_date'].dt.date),
    # 条件2：与首行日期差超过24小时
    df['hour_diff'] > 24,
    # 条件3+4：同一日期第2行之后，或日期不同但差≤24小时
    (df['date_group'] > 1) | ((df['date'].dt.date != df['first_date'].dt.date) & (df['hour_diff'] <= 24))
]
values = ['ok', 'nok', 'norm']

# 赋值status列
df['status'] = np.select(conditions, values, default='ok')

# 保留目标列
result = df[['id', 'value', 'date', 'status']]
print(result)

代码说明

cumcount()：生成组内行号，用于判断是否为前两行
transform('first')：高效获取每组首行日期，避免重复分组计算
hour_diff：将时间差转换为小时，简化24小时阈值判断
date_group：按id+天分组后的行号，用于识别同一日期下的后续行
np.select：批量处理多条件判断，比多次loc赋值更高效

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

如何用Pandas按日期条件为DataFrame添加status列？

问题 #

解决方案 #

1. 修复transform计算时间差的问题 #

2. 完整实现status列的高效方案 #

代码说明 #

问题

解决方案

1. 修复transform计算时间差的问题

2. 完整实现status列的高效方案

代码说明