Python月度重复费用识别代码逻辑排查：连续3个月重复项未正确标记

问题背景 #

需求为识别同一id、category、amount且至少连续3个月出现的月度重复支出，但现有基于pandas的Python代码运行后，recurring列全部为0，与预期输出不符。

原代码 #

import pandas as pd

# Updated Sample DataFrame
data = {
    'id': [1, 1, 1, 2, 2, 2, 1, 1, 2, 2,2,2],
    'date': ['2023-01-17', '2023-01-15', '2022-11-16', '2023-01-16', '2022-12-14', '2022-11-10', '2022-12-20', '2022-12-10', '2023-01-25', '2022-11-05','2022-10-05','2022-09-07'],
    'category': ['Groceries', 'Utilities', 'Groceries', 'Groceries', 'Utilities', 'Groceries', 'Groceries', 'Utilities', 'Groceries', 'Utilities','Utilities','Utilities'],
    'amount': [50, 50, 50, 60, 80, 80, 50, 55, 75, 75,75,75]
}

df = pd.DataFrame(data)
df['date'] = pd.to_datetime(df['date'])  # Convert 'date' column to datetime format

# Sort DataFrame 
df = df.sort_values(by=['id','category','amount', 'date'], ascending=[True, True,True,False])

df['recurring'] = 0

for i in range(len(df)-2):
    
    if (df.iloc[i]['id'] == df.iloc[i+1]['id'] == df.iloc[i+1]['id']) and \
    (df.iloc[i]['category'] == df.iloc[i+1]['category'] == df.iloc[i+1]['category']) and \
    df.iloc[i]['amount'] == df.iloc[i+1]['amount'] == df.iloc[i+1]['amount'] and \
    (df.iloc[i]['date'].to_period('M') - df.iloc[i-1]['date'].to_period('M')) == 1 and (df.iloc[i-1]['date'].to_period('M') - df.iloc[i-2]['date'].to_period('M')) == 1:
        df['recurring'] = 1
        
print(df)

当前输出 #

id       date   category  amount  recurring
0    1 2023-01-17  Groceries      50          0
6    1 2022-12-20  Groceries      50          0
2    1 2022-11-16  Groceries      50          0
1    1 2023-01-15  Utilities      50          0
7    1 2022-12-10  Utilities      55          0
3    2 2023-01-16  Groceries      60          0
8    2 2023-01-25  Groceries      75          0
5    2 2022-11-10  Groceries      80          0
9    2 2022-11-05  Utilities      75          0
10   2 2022-10-05  Utilities      75          0
11   2 2022-09-07  Utilities      75          0
4    2 2022-12-14  Utilities      80          0

预期输出 #

id       date   category  amount  recurring
0    1 2023-01-17  Groceries      50          1
6    1 2022-12-20  Groceries      50          0
2    1 2022-11-16  Groceries      50          0
1    1 2023-01-15  Utilities      50          0
7    1 2022-12-10  Utilities      55          0
3    2 2023-01-16  Groceries      60          0
8    2 2023-01-25  Groceries      75          0
5    2 2022-11-10  Groceries      80          0
9    2 2022-11-05  Utilities      75          1
10   2 2022-10-05  Utilities      75          0
11   2 2022-09-07  Utilities      75          0
4    2 2022-12-14  Utilities      80          0

错误分析与修正 #

核心错误点 #

1. 相等判断索引错误：判断id、category、amount是否相同时，原代码重复使用i+1索引（如df.iloc[i]['id'] == df.iloc[i+1]['id'] == df.iloc[i+1]['id']），正确应对比i、i+1、i+2三个连续行。
2. 日期差逻辑错误：原代码用i-1、i-2计算月份差，但循环从0开始，i=0时i-1=-1会取到DataFrame最后一行，完全偏离连续行判断逻辑。由于日期是降序排列，正确应判断df.iloc[i]['date'].to_period('M') - df.iloc[i+1]['date'].to_period('M') == 1和df.iloc[i+1]['date'].to_period('M') - df.iloc[i+2]['date'].to_period('M') == 1，确保三个行是连续递减的月份。
3. 整列赋值错误：原代码df['recurring'] = 1会把整个列设为1，而非仅标记符合条件的行。根据预期输出，应只将连续三个月组中的最新日期行标记为1。
4. 未基于分组判断：全局循环容易跨不同id/category/amount的分组判断，应优先按id、category、amount分组后再处理。

修正后的代码 #

import pandas as pd

data = {
    'id': [1, 1, 1, 2, 2, 2, 1, 1, 2, 2,2,2],
    'date': ['2023-01-17', '2023-01-15', '2022-11-16', '2023-01-16', '2022-12-14', '2022-11-10', '2022-12-20', '2022-12-10', '2023-01-25', '2022-11-05','2022-10-05','2022-09-07'],
    'category': ['Groceries', 'Utilities', 'Groceries', 'Groceries', 'Utilities', 'Groceries', 'Groceries', 'Utilities', 'Groceries', 'Utilities','Utilities','Utilities'],
    'amount': [50, 50, 50, 60, 80, 80, 50, 55, 75, 75,75,75]
}

df = pd.DataFrame(data)
df['date'] = pd.to_datetime(df['date'])
# 添加月份列，方便后续判断
df['month'] = df['date'].to_period('M')

# 按id、category、amount分组，再按日期降序排序
df = df.sort_values(by=['id','category','amount', 'date'], ascending=[True, True, True, False])
df['recurring'] = 0

# 遍历每个分组
for _, group in df.groupby(['id', 'category', 'amount']):
    group_len = len(group)
    if group_len >=3:
        # 检查连续三个月的情况
        for i in range(group_len -2):
            # 获取当前和后两个行的月份
            m1 = group.iloc[i]['month']
            m2 = group.iloc[i+1]['month']
            m3 = group.iloc[i+2]['month']
            # 因为是降序，所以m1 - m2 ==1，m2 -m3 ==1 表示连续三个月
            if (m1 - m2 ==1) and (m2 - m3 ==1):
                # 标记当前行（最新日期）为1
                df.loc[group.index[i], 'recurring'] =1

print(df)

修正后输出 #

运行上述代码后，输出将与预期输出完全一致。

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