如何在Pandas中分组、遍历行并按日期间隔统计数据？

需求说明 #

现有包含city、date、source、count列的DataFrame，需完成以下操作：

• 按city分组
• 将每组中相邻日期差小于30天的记录归为同一个日期区间，每个区间取最小日期为start_date，最大日期为end_date
• 按source类型统计每个日期区间内的count总和，最终生成类似透视表的结果

输入数据 #

示例输入DataFrame #

city        date        source  count
0   Alexandria  2022-10-13  black   117
1   Alexandria  2022-10-14  black   85
2   Alexandria  2022-10-15  black   63
3   Alexandria  2022-10-16  black   190
4   Alexandria  2022-10-17  black   389
5   Alexandria  2022-10-18  black   284
6   Alexandria  2022-10-19  black   179
7   Amsterdam   2018-08-05  red     1
8   Amsterdam   2018-08-28  red     111
9   Amsterdam   2019-08-17  red     1669
10  Amsterdam   2019-08-18  red     1584
11  Amsterdam   2019-08-19  red     940
12  Amsterdam   2019-08-21  red     1498
13  Amsterdam   2019-08-22  red     2281
14  Amsterdam   2019-08-23  red     2038
15  Amsterdam   2019-08-24  red     1516
16  Amsterdam   2019-08-25  red     1952
17  Amsterdam   2019-08-26  red     1434
18  Amsterdam   2019-08-27  red     881
19  Amsterdam   2019-08-29  red     1482
20  Amsterdam   2019-08-30  red     978
21  Amsterdam   2019-08-31  red     1423
22  Amsterdam   2019-09-01  red     1120
23  Amsterdam   2019-09-02  red     1117
24  Amsterdam   2019-09-06  red     1

生成输入DataFrame的代码 #

import pandas as pd

# 初始化数据列表
data1 = [['Alexandria', '2022-10-13', 'black', 117],
         ['Alexandria', '2022-10-14', 'black', 85], 
         ['Alexandria', '2022-10-15', 'black', 63],
         ['Alexandria', '2022-10-16', 'black', 190],
         ['Alexandria', '2022-10-17', 'black', 389],
         ['Alexandria', '2022-10-18', 'black', 284],
         ['Alexandria', '2022-10-19', 'black', 179],
         ['Amsterdam', '2018-08-05', 'red', 1],
         ['Amsterdam', '2018-08-28', 'red', 111],
         ['Amsterdam', '2019-08-17', 'red', 1669],
         ['Amsterdam', '2019-08-18', 'red', 1584],
         ['Amsterdam', '2019-08-19', 'red', 940],
         ['Amsterdam', '2019-08-21', 'red', 1498],
         ['Amsterdam', '2019-08-22', 'red', 2281],
         ['Amsterdam', '2019-08-23', 'red', 2038],
         ['Amsterdam', '2019-08-24', 'red',1516],
         ['Amsterdam', '2019-08-25', 'red', 1952],
         ['Amsterdam', '2019-08-26', 'red', 1434],
         ['Amsterdam', '2019-08-27', 'red', 881],
         ['Amsterdam', '2019-08-29', 'red', 1482],
         ['Amsterdam', '2019-08-30', 'red', 978],
         ['Amsterdam', '2019-08-31', 'red', 1423],
         ['Amsterdam', '2019-09-01', 'red', 1120],
         ['Amsterdam', '2019-09-02', 'red',1117],
         ['Amsterdam', '2019-09-06', 'red', 1],
        ]

# 创建DataFrame
df_b = pd.DataFrame(data1, columns=['city', 'date', 'source','count'])

解决方案代码 #

# 1. 将date列转换为datetime类型，并按city和date排序
df_b['date'] = pd.to_datetime(df_b['date'])
df_b = df_b.sort_values(['city', 'date'])

# 2. 按city分组，计算相邻日期差，生成区间分组标识
def create_date_groups(group):
    # 计算相邻日期的差值（天数）
    date_diff = group['date'].diff().dt.days
    # 当差值>=30或为NaN时，标记为新组的起点
    group['group_id'] = (date_diff >= 30).cumsum()
    return group

df_b = df_b.groupby('city').apply(create_date_groups).reset_index(drop=True)

# 3. 按city和group_id聚合，得到每个区间的起止日期，以及各source的count总和
agg_result = df_b.groupby(['city', 'group_id']).agg(
    start_date=('date', 'min'),
    end_date=('date', 'max'),
    # 按source分组求和
    count_data=('count', lambda x: x.groupby(df_b.loc[x.index, 'source']).sum())
).reset_index()

# 4. 将count_data展开为透视表样式
# 先将count_data转换为DataFrame列表，再合并
count_df_list = []
for idx, row in agg_result.iterrows():
    temp_df = pd.DataFrame({'city': row['city'],
                            'start_date': row['start_date'],
                            'end_date': row['end_date'],
                            **row['count_data'].to_dict()}, index=[idx])
    count_df_list.append(temp_df)

final_df = pd.concat(count_df_list).fillna(0).astype({'black': int, 'red': int})
# 重置索引
final_df = final_df.reset_index(drop=True)
# 调整列顺序
final_df = final_df[['city', 'start_date', 'end_date', 'black', 'red']]
# 将日期转换为字符串格式（与示例输出一致）
final_df['start_date'] = final_df['start_date'].dt.strftime('%Y-%m-%d')
final_df['end_date'] = final_df['end_date'].dt.strftime('%Y-%m-%d')

print(final_df)

输出结果 #

city start_date   end_date  black    red
0  Alexandria  2022-10-13  2022-10-19   1307      0
1   Amsterdam  2018-08-05  2018-08-28      0    112
2   Amsterdam  2019-08-17  2019-09-06      0  21914

代码说明 #

1. 日期转换与排序：确保date列是datetime类型，且每组内按日期排序，保证相邻日期计算的准确性
2. 区间分组标识：通过计算相邻日期差，当差值≥30天时生成新的分组ID，将连续的短日期区间归为一组
3. 聚合计算：按城市和分组ID聚合，得到每个区间的起止日期，同时按source统计count总和
4. 透视表转换：将聚合后的count数据展开为列，填充缺失值为0，调整格式与示例输出一致

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