如何将SQL导入的数据集按floor_date('month')分组生成横向频次统计表

我有一个从SQL导入的数据集，结构如下：

ID  Name  Date
1   John  2021-09-05
1   John  2021-08-14
1   John  2021-08-21
2   Kate  2021-03-04
3   Mary  2021-07-10
3   Mary  2021-06-13
4   Joel  2021-05-10
4   Joel  2021-05-31
4   Joel  2021-04-13
4   Joel  2021-07-11
4   Joel  2021-03-31
5   Mike  2021-04-02
5   Mike  2021-02-07

需要通过floor_date(df, 'month')按月份分组，统计每个用户每月的记录出现次数，生成如下格式的横向统计表：

Name | Feb 21 | Mar 21 | Apr 21 | May 21 | Jun 21 | Jul 21 | ....
John     0       0        0         0       0         0      ....
Joel     0       1        1         2       0         1      ....
Kate     0       1        0         0       0         0      ....
Mary     0       0        0         0       1         1      ....
Mike     1       0        1         0       0         0      ....       

R 实现代码 #

使用lubridate处理日期，dplyr做分组统计，tidyr完成透视：

library(dplyr)
library(lubridate)
library(tidyr)

# 替换为你的SQL导入数据集
df <- data.frame(
  ID = c(1,1,1,2,3,3,4,4,4,4,4,5,5),
  Name = c("John","John","John","Kate","Mary","Mary","Joel","Joel","Joel","Joel","Joel","Mike","Mike"),
  Date = c("2021-09-05","2021-08-14","2021-08-21","2021-03-04","2021-07-10","2021-06-13","2021-05-10","2021-05-31","2021-04-13","2021-07-11","2021-03-31","2021-04-02","2021-02-07")
)

# 核心处理流程
result <- df %>%
  mutate(Date = ymd(Date),
         Month = floor_date(Date, "month")) %>%  # 按月份截断日期
  count(Name, Month) %>%  # 统计每个用户每月的记录数
  mutate(Month_Label = format(Month, "%b %y")) %>%  # 格式化月份为Feb 21样式
  pivot_wider(names_from = Month_Label, values_from = n, values_fill = 0) %>%  # 转为横向表，缺失值填0
  arrange(Name)

print(result)

Python（Pandas）实现代码 #

用Pandas的日期处理和透视功能完成需求：

import pandas as pd

# 替换为你的SQL导入数据集
data = {
    "ID": [1,1,1,2,3,3,4,4,4,4,4,5,5],
    "Name": ["John","John","John","Kate","Mary","Mary","Joel","Joel","Joel","Joel","Joel","Mike","Mike"],
    "Date": ["2021-09-05","2021-08-14","2021-08-21","2021-03-04","2021-07-10","2021-06-13","2021-05-10","2021-05-31","2021-04-13","2021-07-11","2021-03-31","2021-04-02","2021-02-07"]
}
df = pd.DataFrame(data)

# 核心处理流程
df["Date"] = pd.to_datetime(df["Date"])
df["Month"] = df["Date"].dt.floor("M")  # 按月份截断日期，对应R的floor_date
count_df = df.groupby(["Name", "Month"]).size().reset_index(name="Count")

# 转为横向表并格式化列名
result = count_df.pivot(index="Name", columns="Month", values="Count").fillna(0).astype(int)
result.columns = result.columns.strftime("%b %y")  # 转为Feb 21样式
result = result.sort_index()

print(result)

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