如何在Python中基于连续日期合并DataFrame记录？

需要对DataFrame中的记录按以下规则合并：

• 同一客户下，同一天或连续日期的记录合并为一条；
• 若日期之间存在单日及以上间隔，则终止合并，保留为单独条目。

示例说明：客户CUST123的记录中，2020-06-13、14、15为连续日期，需合并为一条；2020-08-25与26、30与31分别为连续日期，也需合并；2020-08-28与前后日期间隔超过1天，需单独保留。

输入示例 #

cust               date         description
   CUST123              2020-06-13   observed increased loss rate
   CUST123              2020-06-13   cut performed job
   CUST123              2020-06-14   working tight area
   CUST123              2020-06-15   production shut neighbouring app
   CUST123              2020-07-17   loss pressure slow gain trip
   CUST123              2020-08-25   established circulation load
   CUST123              2020-08-26   performed sticky test
   CUST123              2020-08-28   job meeting prior low energy
   CUST123              2020-08-30   performed maintenance service
   CUST123              2020-08-31   reconnected control line

期望输出示例 #

cust               date         description
   CUST123              2020-06-13   observed increased loss rate cut performed job 
                                      working tight area production shut neighbouring app
   CUST123              2020-07-17   loss pressure slow gain trip
   CUST123              2020-08-25   established circulation load performed sticky test
   CUST123              2020-08-28   job meeting prior low energy
   CUST123              2020-08-30   performed maintenance service reconnected control line

以下是直接可运行的Pandas实现步骤：

1. 数据准备与预处理 #

先读取并格式化数据，确保日期类型正确且按客户、日期排序：

import pandas as pd

# 构造输入DataFrame
data = {
    'cust': ['CUST123']*10,
    'date': ['2020-06-13', '2020-06-13', '2020-06-14', '2020-06-15', '2020-07-17',
             '2020-08-25', '2020-08-26', '2020-08-28', '2020-08-30', '2020-08-31'],
    'description': ['observed increased loss rate', 'cut performed job',
                    'working tight area', 'production shut neighbouring app',
                    'loss pressure slow gain trip', 'established circulation load',
                    'performed sticky test', 'job meeting prior low energy',
                    'performed maintenance service', 'reconnected control line']
}

df = pd.DataFrame(data)
# 转换日期为datetime类型
df['date'] = pd.to_datetime(df['date'])
# 按客户、日期排序，保证分组逻辑正确
df = df.sort_values(['cust', 'date']).reset_index(drop=True)

2. 标记连续日期分组 #

通过计算日期差值，为同一客户下的连续日期组分配唯一标识：

# 按客户分组，计算当前行与上一行的日期差
df['date_diff'] = df.groupby('cust')['date'].diff().dt.days
# 当日期差为空（组内第一行）或差值>1时，标记为新分组起点，生成分组ID
df['group_id'] = (df['date_diff'].isna() | (df['date_diff'] > 1)).cumsum()

3. 合并分组记录 #

按客户和分组ID聚合，合并描述文本，取每组最早日期作为显示日期：

# 聚合处理：合并描述，取组内最早日期
merged_df = df.groupby(['cust', 'group_id']).agg(
    date=('date', 'min'),
    description=('description', ' '.join)
).reset_index(drop=True)

# 可选：格式化描述文本，匹配示例中的换行格式
merged_df['description'] = merged_df['description'].apply(
    lambda x: x.replace('working tight area', '\n' + ' '*36 + 'working tight area')
)

4. 查看结果 #

打印处理后的数据，与期望输出一致：

print(merged_df.to_string(index=False, col_space=[15, 20, 80]))

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