Pandas数据帧按日期匹配规则删除行的问题修正

需求 #

若DataFrame中某行Buy Exit Date的日期部分（YYYY/MM/DD）与下一行Buy Date的日期部分匹配，则删除下一行。

原始数据 #

数据初始化代码 #

import pandas as pd
data = {
    'Buy Date': ['2022/11/07 15:00:00', '2022/11/11 12:00:00', '2022/11/24 15:00:00', '2022/12/01 12:00:00', '2022/12/14 09:00:00'],
    'Buy Exit Date': ['2022/11/11 09:00:00', '2022/11/24 12:00:00', '2022/12/01 09:00:00', '2022/12/06 09:00:00', '2022/12/16 09:00:00'],
    'ln_entry': [3232.899902, 3315.000000, 3381.699951, 3476.149902, 3364.699951]
}
df = pd.DataFrame(data)

原始DataFrame #

Buy Date          Buy Exit Date    ln_entry
0  2022/11/07 15:00:00  2022/11/11 09:00:00  3232.899902
1  2022/11/11 12:00:00  2022/11/24 12:00:00  3315.000000
2  2022/11/24 15:00:00  2022/12/01 09:00:00  3381.699951
3  2022/12/01 12:00:00  2022/12/06 09:00:00  3476.149902
4  2022/12/14 09:00:00  2022/12/16 09:00:00  3364.699951

期望输出 #

Buy Date          Buy Exit Date    ln_entry
0  2022/11/07 15:00:00  2022/11/11 09:00:00  3232.899902
1  2022/12/14 09:00:00  2022/12/16 09:00:00  3364.699951

当前代码问题 #

当前代码错误标记当前行为待删除对象，而实际需要删除的是下一行：

df['Buy Date'] = pd.to_datetime(df['Buy Date'])
df['Buy Exit Date'] = pd.to_datetime(df['Buy Exit Date'])
df['Buy Date Only'] = df['Buy Date'].dt.date
df['Buy Exit Date Only'] = df['Buy Exit Date'].dt.date
df['Next_Buy_Date_Only'] = df['Buy Date Only'].shift(-1)
to_remove = df['Buy Exit Date Only'] == df['Next_Buy_Date_Only']
df_cleaned = df[~to_remove]
df_cleaned = df_cleaned.drop(columns=['Buy Date Only', 'Buy Exit Date Only', 'Next_Buy_Date_Only'])
print(df_cleaned)

当前错误输出 #

Buy Date          Buy Exit Date    ln_entry
3  2022-12-01 12:00:00  2022-12-06 09:00:00  3476.149902
4  2022-12-14 09:00:00  2022-12-16 09:00:00  3364.699951

修正后的代码 #

核心改动：标记那些与上一行Buy Exit Date日期匹配的Buy Date行（即目标下一行）为待删除对象：

import pandas as pd
data = {
    'Buy Date': ['2022/11/07 15:00:00', '2022/11/11 12:00:00', '2022/11/24 15:00:00', '2022/12/01 12:00:00', '2022/12/14 09:00:00'],
    'Buy Exit Date': ['2022/11/11 09:00:00', '2022/11/24 12:00:00', '2022/12/01 09:00:00', '2022/12/06 09:00:00', '2022/12/16 09:00:00'],
    'ln_entry': [3232.899902, 3315.000000, 3381.699951, 3476.149902, 3364.699951]
}
df = pd.DataFrame(data)

# 转换为datetime类型
df['Buy Date'] = pd.to_datetime(df['Buy Date'])
df['Buy Exit Date'] = pd.to_datetime(df['Buy Exit Date'])

# 提取日期部分
buy_dates = df['Buy Date'].dt.date
exit_dates = df['Buy Exit Date'].dt.date

# 标记需要删除的行：当前行的Buy日期等于上一行的Exit日期
to_remove = buy_dates == exit_dates.shift(1)

# 过滤掉待删除行并重置索引
df_cleaned = df[~to_remove].reset_index(drop=True)
print(df_cleaned)

修正后输出 #

Buy Date          Buy Exit Date    ln_entry
0  2022-11-07 15:00:00  2022-11-11 09:00:00  3232.899902
1  2022-12-14 09:00:00  2022-12-16 09:00:00  3364.699951

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