Pandas按分组条件取前序非0vt值填充need_data为1的行实现方法

实现方法 #

你可以通过前向填充+条件赋值的方式实现需求，步骤如下：

1. 先转换日期格式并按用户、日期排序，保证时序正确，才能准确找到当前行之前最近的有效记录
2. 按用户分组，前向填充最近的非0vt对应的数值/日期作为辅助数据
3. 仅对满足need_data==1且vt==0的行，用填充后的有效数据赋值feed1列，其余行保留原始值

完整代码 #

import pandas as pd

# 构造原始数据集
df = pd.DataFrame({
    'user': {0: 848, 1: 848, 2: 848, 3: 848, 4: 848, 5: 848, 6: 848, 7: 848, 8: 848, 9: 848, 10: 848, 11: 848, 12: 848, 13: 848},
    'date': {0: '2005-02-05', 1: '2006-10-25', 2: '2006-11-07', 3: '2006-11-20', 4: '2006-12-04', 5: '2006-12-21', 6: '2007-01-08', 7: '2007-02-08', 8: '2007-03-08', 9: '2007-04-10', 10: '2007-11-28', 11: '2007-12-10', 12: '2009-01-07', 13: '2009-01-12'},
    'need_data': {0: 0, 1: 0, 2: 0, 3: 1, 4: 0, 5: 0, 6: 0, 7: 0, 8: 0, 9: 0, 10: 1, 11: 0, 12: 1, 13: 0},
    'vt': {0: 34.0, 1: 49.25, 2: 49.25, 3: 0.0, 4: 49.4, 5: 0.0, 6: 0.0, 7: 49.8, 8: 0.0, 9: 50.1, 10: 0.0, 11: 0.0, 12: 0.0, 13: 0.0},
}) 

# 日期格式化 + 按用户、日期排序保证时序正确
df['date'] = pd.to_datetime(df['date'])
df = df.sort_values(['user', 'date']).reset_index(drop=True)

# 按用户分组，前向填充最近的非0vt值、以及对应的日期
df['last_valid_vt'] = df['vt'].replace(0, pd.NA).groupby(df['user']).ffill()
df['last_valid_date'] = df['date'].where(df['vt']!=0, pd.NA).groupby(df['user']).ffill()

# 生成feed1列：示例为填充对应日期，和你给出的预期输出一致
# 如果需要填充vt的数值，把last_valid_date换成last_valid_vt即可
df['feed1'] = df.apply(lambda x: x['last_valid_date'] if (x['need_data'] == 1 and x['vt'] == 0) else x['date'], axis=1)

# 删去辅助列
df = df.drop(columns=['last_valid_vt', 'last_valid_date'])

print(df)

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