pandas如何为f1、f2为NaT的DataFrame行按规则新增对应记录

原始DataFrame #

guest                  dat1           dat2                   f1               f2
guest1  2021-10-21 16:01:01  2021-10-21 08:19:17            NaT             NaT
guest2  2021-10-21 10:41:53  2021-10-24 07:53:57  2021-10-21 08:19:17  2021-10-21 10:41:53
guest3  2021-10-21 09:00:00  2021-10-21 10:00:00            NaT                  NaT
guest4  2021-10-21 10:00:00  2021-10-22 14:00:00  2021-10-21 10:00:00  2021-10-21 10:00:00

需求说明 #

• 当行的f1和f2均为NaT时，新增2行替换原有行
• 第一条新增行f1取值为2021-10-21 00:00:00，f2取值为当前行dat1的值
• 第二条新增行f1取值为当前行dat2的值，f2取值为2021-10-21 23:59:59
• 不符合条件的行保持原样不变

预期输出 #

guest                 dat1                  dat2                f1                 f2
  guest1  2021-10-21 16:01:01  2021-10-21 08:19:17       2021-10-21 00:00:00    2021-10-21 16:01:01
  guest1  2021-10-21 16:01:01  2021-10-21 08:19:17       2021-10-21 08:19:17     2021-10-21 23:59:59
  guest2  2021-10-21 10:41:53  2021-10-24 07:53:57       2021-10-21 08:19:17    2021-10-21 10:41:53
  guest3  2021-10-21 09:00:00  2021-10-21 10:00:00       2021-10-21 00:00:00    2021-10-21 09:00:00
  guest3  2021-10-21 09:00:00  2021-10-21 10:00:00       2021-10-21 10:00:00    2021-10-21 23:59:59          
  guest4  2021-10-21 10:00:00  2021-10-22 14:00:00       2021-10-21 10:00:00    2021-10-21 10:00:00

你写的重复行逻辑已经正确，只需要补充重复后的f1、f2赋值逻辑即可：

import pandas as pd
import numpy as np

# 先确保所有时间列是datetime64类型
df['dat1'] = pd.to_datetime(df['dat1'])
df['dat2'] = pd.to_datetime(df['dat2'])
df['f1'] = pd.to_datetime(df['f1'])
df['f2'] = pd.to_datetime(df['f2'])

# 1. 标记f1、f2均为NaT的行
mask = df['f1'].isna() & df['f2'].isna()
# 2. 按规则重复行
reps = np.where(mask, 2, 1)
df_expand = df.loc[np.repeat(df.index, reps)].reset_index(drop=True)
# 3. 给同一原始行生成的重复行标记序号（0/1）
df_expand['row_num'] = df_expand.groupby(np.repeat(df.index, reps)).cumcount()
# 4. 预定义固定时间值
start_time = pd.to_datetime('2021-10-21 00:00:00')
end_time = pd.to_datetime('2021-10-21 23:59:59')
# 生成扩展行对应的mask
expand_mask = mask.repeat(reps).values
# 5. 按规则更新f1和f2
cond0 = df_expand['row_num'] == 0
df_expand.loc[cond0 & expand_mask, 'f1'] = start_time
df_expand.loc[cond0 & expand_mask, 'f2'] = df_expand.loc[cond0 & expand_mask, 'dat1']

cond1 = df_expand['row_num'] == 1
df_expand.loc[cond1 & expand_mask, 'f1'] = df_expand.loc[cond1 & expand_mask, 'dat2']
df_expand.loc[cond1 & expand_mask, 'f2'] = end_time

# 清理临时列得到最终结果
df_final = df_expand.drop(columns=['row_num'])

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