Python按客户ID匹配最近日期新增DataFrame列的实现问题

我是Python新手，需要给以下DataFrame新增一列DTHR_OPERATION_bis，存储同一客户（按Id分组）下与当前行DTHR_OPERATION最接近的日期。尝试用merge_asof按最近日期匹配但未成功，希望确认该方案是否可行并寻求解决方法。

原始DataFrame #

import pandas as pd
from pandas import Timestamp

df = pd.DataFrame({'Id': {0: 'ae9b0886-7e2b-4c37-a3a3',
1: 'ae9b0886-7e2b-4c37-a3a3',
2: 'ae290c85-9dfb-440f-becb',
3: 'ae290c85-9dfb-440f-becb',
4: 'ae290c85-9dfb-440f-becb',
5: 'ae290c85-9dfb-440f-becb',
6: 'ae290c85-9dfb-440f-becb',
7: 'ae290c85-9dfb-440f-becb',
8: 'ae290c85-9dfb-440f-becb',
9: 'ae290c85-9dfb-440f-becb',
10: 'ae290c85-9dfb-440f-becb',
11: 'b92faffa-89cd-48db-aafd',
12: 'b92faffa-89cd-48db-aafd',
13: '88f8b058-8b8a-4a80-84a2',
14: '88f8b058-8b8a-4a80-84a2',
15: '88f8b058-8b8a-4a80-84a2',
16: '88f8b058-8b8a-4a80-84a2',
17: '88f8b058-8b8a-4a80-84a2',
18: '88f8b058-8b8a-4a80-84a2',
19: '88f8b058-8b8a-4a80-84a2'},
 'lastname': {0: 'Baco',
1: 'Baco',
2: 'Azi',
3: 'Azi',
4: 'Azi',
5: 'Azi',
6: 'Azi',
7: 'Azi',
8: 'Azi',
9: 'Azi',
10: 'Azi',
11: 'SOFTY',
12: 'SOFTY',
13: 'Dup',
14: 'Dup',
15: 'Dup',
16: 'Dup',
17: 'Dup',
18: 'Dup',
19: 'Dup'},
 'ID_VALIDATION': {0: 82552217,
1: 82544581,
2: 82538959,
3: 82405234,
4: 82376176,
5: 82358274,
6: 82347060,
7: 82294311,
8: 82203773,
9: 82176910,
10: 82575141,
11: 82396159,
12: 82393258,
13: 82364079,
14: 82382504,
15: 82532881,
16: 82163257,
17: 82267321,
18: 82341659,
19: 82305609},
 'DTHR_OPERATION': {0: Timestamp('2022-09-28 08:10:41'),
1: Timestamp('2022-09-28 12:06:44'),
2: Timestamp('2022-09-28 07:22:30'),
3: Timestamp('2022-09-23 07:23:13'),
4: Timestamp('2022-09-22 07:31:07'),
5: Timestamp('2022-09-21 15:38:03'),
6: Timestamp('2022-09-21 07:25:34'),
7: Timestamp('2022-09-19 17:00:03'),
8: Timestamp('2022-09-16 07:24:12'),
9: Timestamp('2022-09-15 07:21:46'),
10: Timestamp('2022-09-29 07:23:11'),
11: Timestamp('2022-09-22 16:08:38'),
12: Timestamp('2022-09-22 15:40:54'),
13: Timestamp('2022-09-22 07:03:44'),
14: Timestamp('2022-09-22 15:12:24'),
15: Timestamp('2022-09-28 07:03:53'),
16: Timestamp('2022-09-15 07:03:32'),
17: Timestamp('2022-09-19 07:03:53'),
18: Timestamp('2022-09-21 07:03:14'),
19: Timestamp('2022-09-20 07:03:47')},
 'TYPE_OPER_VALIDATION': {0: 1,
1: 1,
2: 1,
3: 1,
4: 1,
5: 1,
6: 1,
7: 1,
8: 1,
9: 1,
10: 1,
11: 3,
12: 1,
13: 1,
14: 1,
15: 1,
16: 1,
17: 1,
18: 1,
19: 1}})

尝试的代码 #

df1['DTHR_OPERATION_bis'] = df1.loc[:, 'DTHR_OPERATION']
df1.head()

tol = pd.Timedelta('1 day')
df3 = pd.merge_asof(left=df1['DTHR_OPERATION'],right=df1['DTHR_OPERATION_bis'],direction='nearest',tolerance=tol)

解决方案 #

方案1：分组后计算最近日期（直观易理解） #

按Id分组，对每个分组内的日期，计算当前日期与其他所有日期的时间差绝对值，排除自身后取差值最小的对应日期。

def get_nearest_date(group):
    dates = group['DTHR_OPERATION'].values
    nearest_dates = []
    for date in dates:
        # 计算当前日期与其他日期的时间差绝对值
        diffs = abs(dates - date)
        # 排除自身的差值（设为无穷大，避免选中自己）
        diffs[diffs == 0] = float('inf')
        # 找到最小差值对应的日期
        nearest_idx = diffs.argmin()
        nearest_dates.append(dates[nearest_idx])
    group['DTHR_OPERATION_bis'] = nearest_dates
    return group

df = df.groupby('Id').apply(get_nearest_date)

方案2：使用merge_asof实现（性能更优） #

merge_asof方案可行，但需要按Id分组处理，且要避免匹配到自身日期，步骤如下：

1. 先按Id和DTHR_OPERATION排序数据，满足merge_asof的排序要求
2. 复制一份数据作为右表，修改列名区分
3. 分组后执行merge_asof，并处理自身匹配的特殊情况

# 对原始数据按客户和日期排序
df_sorted = df.sort_values(['Id', 'DTHR_OPERATION'])
# 复制右表，修改日期列名
df_right = df_sorted[['Id', 'DTHR_OPERATION']].rename(columns={'DTHR_OPERATION': 'DTHR_OPERATION_bis'})

def merge_nearest(group):
    right_group = df_right[df_right['Id'] == group['Id'].iloc[0]]
    # 执行merge_asof，匹配同一客户下的最近日期
    merged = pd.merge_asof(
        group,
        right_group,
        on='DTHR_OPERATION',
        by='Id',
        direction='nearest',
        tolerance=pd.Timedelta('1 day')
    )
    # 处理自身匹配的情况：如果当前日期和匹配日期相同，重新匹配排除自身的最近日期
    mask = merged['DTHR_OPERATION'] == merged['DTHR_OPERATION_bis']
    if mask.any():
        for idx in merged[mask].index:
            current_date = merged.loc[idx, 'DTHR_OPERATION']
            filtered_right = right_group[right_group['DTHR_OPERATION_bis'] != current_date]
            if not filtered_right.empty:
                temp_merge = pd.merge_asof(
                    pd.DataFrame({'DTHR_OPERATION': [current_date], 'Id': [merged.loc[idx, 'Id']]}),
                    filtered_right,
                    on='DTHR_OPERATION',
                    by='Id',
                    direction='nearest',
                    tolerance=pd.Timedelta('1 day')
                )
                merged.loc[idx, 'DTHR_OPERATION_bis'] = temp_merge['DTHR_OPERATION_bis'].iloc[0]
    return merged

# 分组应用函数，得到结果后恢复原始数据顺序
df_result = df_sorted.groupby('Id', group_keys=False).apply(merge_nearest)
df_result = df_result.loc[df.index]

方案说明 #

• 方案1逻辑简单直观，适合数据量不大的场景；
• 方案2利用merge_asof的高效性，适合大数据量场景，需注意处理同一客户仅一行数据的情况（可根据需求将DTHR_OPERATION_bis设为NaN或保留原日期）。

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