R语言统计数据框指定值在连续月份时间段内的出现次数

测试数据 #

df1 <- data.frame(x1 =
 c("b","e","f","g","a","d","c","d","h","d","i","j"), x2 = c("Aug 2017",
 "Aug 2017", "Aug 2017","Sep 2017","Sep 2017","Sep 2017","Oct
 2017","Oct 2017","Oct 2017","Nov 2017","Nov 2017","Nov 2017"), x3 =
 c(456,678,876,987,123,324,345,564,333,255,687,476))

需求规则 #

统计x1列指定值在x2列的出现规律：返回该值连续出现在相邻月份的最长月份总数，不连续的月份段分开计数。

规则示例：

• df1中'd'出现在Sep 2017、Oct 2017、Nov 2017三个连续月，返回3
• 若'd'出现在Aug 2017、Oct 2017、Nov 2017，仅Oct、Nov连续，返回2

实现代码 #

依赖dplyr做数据处理、lubridate做日期计算，未安装可先运行install.packages(c("dplyr","lubridate"))。

library(dplyr)
library(lubridate)

# 传入数据框和目标x1值，返回最长连续出现月数
calc_max_consecutive_months <- function(input_df, target_x1) {
  # 提取目标值出现过的所有月份，去重后转标准日期格式排序
  appear_months <- input_df %>%
    filter(x1 == target_x1) %>%
    mutate(month_start = dmy(paste0("01 ", x2))) %>%
    pull(month_start) %>%
    unique() %>%
    sort()
  
  # 边界情况处理
  if (length(appear_months) == 0) return(0)
  if (length(appear_months) == 1) return(1)
  
  # 计算相邻两个出现月份的间隔
  month_gap <- sapply(2:length(appear_months), function(i) {
    interval(appear_months[i-1], appear_months[i]) %/% months(1)
  })
  
  # 间隔不为1则拆分连续段，统计各段长度取最大值
  group_id <- cumsum(c(1, month_gap != 1))
  max_len <- max(table(group_id))
  
  return(as.integer(max_len))
}

验证测试 #

1. 测试df1中的'd'：

calc_max_consecutive_months(df1, "d")
# 运行返回 [1] 3，符合预期

2. 构造提到的df2场景测试：

df2 <- data.frame(
  x1 = rep("d", 3),
  x2 = c("Aug 2017", "Oct 2017", "Nov 2017")
)
calc_max_consecutive_months(df2, "d")
# 运行返回 [1] 2，符合预期

如果需要批量计算x1列所有值的最长连续月数，直接遍历所有唯一值即可：

all_x1_vals <- unique(df1$x1)
result_df <- data.frame(
  x1 = all_x1_vals,
  max_consecutive_months = sapply(all_x1_vals, function(v) calc_max_consecutive_months(df1, v))
)

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