如何基于日期区间高效拼接Pandas DataFrame并聚合客户属性

核心思路是用Pandas矢量化操作替代嵌套循环，将时间复杂度从O(M*N)降至O(M+N)，大规模数据集下性能提升可达数百倍。 #

实现步骤 #

1. 标准化时间字段：将所有日期字符串转换为pandas datetime类型，避免字符串比较的性能损耗和逻辑错误
2. 关联同客户记录：按CUST_ID关联两个表，一次性完成同客户所有记录的匹配，替代循环判断客户ID相等的逻辑
3. 区间过滤：用矢量化条件筛选出访问时间落在属性生效区间的记录
4. 分组聚合：按访问记录唯一标识分组，聚合得到属性数组和拼接字符串

完整可运行代码 #

import pandas as pd

# 原始数据构造
visit_data = [[1, 1, '2009-08-28 00:00:00.000000'],
        [1, 2, '2009-08-31 00:00:00.000000'],
        [20, 11, '2009-08-27 00:00:00.000000'],
        [20, 21, '2009-08-31 00:00:00.000000']] 
customer_attr = [[1,'RED', '2008-08-27 00:00:00.000000','2008-08-28 00:00:00.000000'],
                [1,'BLUE', '2008-08-28 00:00:00.000000','2010-08-28 00:00:00.000000'],
                [1, 'BLACK', '2009-08-29 00:00:00.000000', '2010-08-28 00:00:00.000000'],
                [20,'YELLOW', '2008-08-26 00:00:00.000000','2009-08-28 00:00:00.000000'],
                [20,'BLUE', '2008-08-28 00:00:00.000000','2010-08-28 00:00:00.000000'],
                [20, 'BLACK', '2009-08-29 00:00:00.000000', '2010-09-03 00:00:00.000000'],
                ]
visit_df = pd.DataFrame(visit_data, columns=['CUST_ID', 'VISIT_ID', 'VISIT_DTM'])
customer_df = pd.DataFrame(customer_attr, columns=['CUST_ID', 'ATTR', 'START_DTM', 'END_DTM'])

# 1. 转换所有时间字段为datetime类型
visit_df['VISIT_DTM'] = pd.to_datetime(visit_df['VISIT_DTM'])
customer_df['START_DTM'] = pd.to_datetime(customer_df['START_DTM'])
customer_df['END_DTM'] = pd.to_datetime(customer_df['END_DTM'])

# 2. 清理ATTR字段多余的单引号
customer_df['ATTR'] = customer_df['ATTR'].str.strip("'")

# 3. 按客户ID关联两个表
merged_df = visit_df.merge(customer_df, on='CUST_ID', how='left')

# 4. 过滤访问时间落在属性生效区间的记录
filtered_df = merged_df[
    (merged_df['VISIT_DTM'] >= merged_df['START_DTM']) 
    & (merged_df['VISIT_DTM'] <= merged_df['END_DTM'])
]

# 5. 分组聚合得到属性数组和拼接字符串
result_df = filtered_df.groupby(
    ['CUST_ID', 'VISIT_ID', 'VISIT_DTM'], as_index=False
).agg(
    ATTR_ARRAY=('ATTR', list),
    ATTR_STR=('ATTR', '-'.join)
)

# 6. 补全没有匹配到任何属性的访问记录
result_df = visit_df[['CUST_ID', 'VISIT_ID', 'VISIT_DTM']].merge(
    result_df, on=['CUST_ID', 'VISIT_ID', 'VISIT_DTM'], how='left'
)
# 空值处理
result_df['ATTR_ARRAY'] = result_df['ATTR_ARRAY'].fillna("").apply(list)
result_df['ATTR_STR'] = result_df['ATTR_STR'].fillna("")

# 打印结果验证
print(result_df)

输出结果说明 #

运行代码得到的结果和预期完全一致，VISIT_DTM如果需要转回字符串格式，可以用result_df['VISIT_DTM'] = result_df['VISIT_DTM'].dt.strftime('%Y-%m-%d %H:%M:%S.%f')调整格式。

内容的提问来源于stack exchange，提问作者Matthew David Jankowski