如何在Pandas中按周（周一至周日）分组多分类数据并统计记录数

解决方案（使用Pandas） #

以下代码可实现将数据按Category、Issue及周一至周日的周区间分组，统计每组记录数并按计数降序排列：

import pandas as pd

# 构造原始数据
data = [
    ["Bakes", "Back Brake failures", "11/28/2022"],
    ["Machines", "Oiling of the machines", "11/29/2022"],
    ["Cars", "windscreen broken", "11/30/2022"],
    ["Cars", "steering wheel is shaking", "11/30/2022"],
    ["Bakes", "The tyres are flat", "12/01/2022"],
    ["Machines", "Normal wear and tear", "12/02/2022"],
    ["Machines", "Normal wear and tear", "12/02/2022"],
    ["Cars", "warning lights are on", "12/03/2022"],
    ["Bakes", "Back Brake failures", "12/04/2022"],
    ["Machines", "Oiling of the machines", "12/05/2022"],
    ["Cars", "windscreen broken", "12/06/2022"],
    ["Bakes", "excessive emissions", "12/06/2022"],
    ["Bakes", "The tyres are flat", "12/07/2022"],
    ["Machines", "Normal wear and tear", "12/08/2022"],
    ["Cars", "warning lights are on", "12/09/2022"],
    ["Bakes", "Brake pads worn", "12/10/2022"],
    ["Machines", "Machine is consuming too much oil", "12/11/2022"],
    ["Cars", "the tyres are wearing unevenly", "12/11/2022"]
]

# 创建DataFrame
df = pd.DataFrame(data, columns=["Category", "Issue", "Date"])

# 转换日期格式并生成周区间（周一至周日）
df['Date'] = pd.to_datetime(df['Date'], format='%m/%d/%Y')
df['Week'] = df['Date'].dt.to_period('W-MON').apply(lambda x: f"{x.start_time.strftime('%Y-%m-%d')} 至 {x.end_time.strftime('%Y-%m-%d')}")

# 分组统计并排序
agg_result = df.groupby(['Category', 'Issue', 'Week']).size().reset_index(name='Count')
agg_result_sorted = agg_result.sort_values(by='Count', ascending=False)

# 输出结果
print(agg_result_sorted.to_markdown(index=False))

统计结果 #

内容的提问来源于stack exchange，提问作者alfred dowuona-owoo