在R语言中按月计算新增捐赠者与取消捐赠者的差值

我有某慈善机构的捐赠者数据集，需要计算每月新增捐赠者数量减去取消订阅的捐赠者数量。部分未取消订阅的捐赠者，其EndDate列值为NA。

数据集结构如下：

Contact-ID      StartDate       EndDate
 1              2021-09-01      2021-10-01
 2              2021-09-01      2021-10-01
 3              2021-10-01  
 4              2021-10-01      2021-11-01

期望得到的结果格式：

2021-09         2
2021-10         0
2021-11        -1

以下提供两种常用数据处理工具的实现方法：

方法一：R语言（依赖dplyr和lubridate包） #

library(dplyr)
library(lubridate)

# 构造示例数据集
donors <- tibble(
  Contact_ID = c(1,2,3,4),
  StartDate = ymd(c("2021-09-01", "2021-09-01", "2021-10-01", "2021-10-01")),
  EndDate = ymd(c("2021-10-01", "2021-10-01", NA, "2021-11-01"))
)

# 统计每月新增捐赠者
new_donors <- donors %>%
  mutate(month = floor_date(StartDate, "month")) %>%
  count(month, name = "new_count")

# 统计每月取消订阅者（排除未取消的记录）
cancelled_donors <- donors %>%
  filter(!is.na(EndDate)) %>%
  mutate(month = floor_date(EndDate, "month")) %>%
  count(month, name = "cancel_count")

# 合并数据并计算净变化
result <- full_join(new_donors, cancelled_donors, by = "month") %>%
  mutate(
    new_count = replace_na(new_count, 0),
    cancel_count = replace_na(cancel_count, 0),
    net_change = new_count - cancel_count
  ) %>%
  select(month, net_change) %>%
  arrange(month) %>%
  mutate(month = format(month, "%Y-%m"))

print(result)

方法二：Python（依赖Pandas库） #

import pandas as pd

# 构造示例数据集
data = {
    "Contact-ID": [1,2,3,4],
    "StartDate": ["2021-09-01", "2021-09-01", "2021-10-01", "2021-10-01"],
    "EndDate": ["2021-10-01", "2021-10-01", None, "2021-11-01"]
}
df = pd.DataFrame(data)

# 转换日期格式并提取年月
df["StartDate"] = pd.to_datetime(df["StartDate"])
df["EndDate"] = pd.to_datetime(df["EndDate"])
df["start_month"] = df["StartDate"].dt.to_period("M")
df["end_month"] = df["EndDate"].dt.to_period("M")

# 统计每月新增和取消人数
new_counts = df.groupby("start_month").size().rename("new_count")
cancel_counts = df.dropna(subset=["EndDate"]).groupby("end_month").size().rename("cancel_count")

# 合并计算净变化并格式化输出
result = pd.concat([new_counts, cancel_counts], axis=1).fillna(0)
result["net_change"] = result["new_count"] - result["cancel_count"]
result = result.reset_index().rename(columns={"index": "month"})
result["month"] = result["month"].astype(str)

print(result[["month", "net_change"]].to_string(index=False))

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