计算滚动比率并排除Null行的R语言技术咨询

针对你的需求，核心是让每个指标列单独筛选自身非Null的行进行计算，不干扰其他指标。下面用dplyr实现，适配你的示例数据和真实数据集场景：

1. 针对示例数据的实现代码 #

先以你提供的示例数据为例，生成符合期望的ALL区域汇总：

library(dplyr)
library(stringr)

# 示例数据
df <- tibble(
  Area = c("A", "A", "A", "A", "B", "B", "B", "B"),
  Quarter = c(2022.2, 2022.1, 2021.4, 2021.3, 2022.2, 2022.1, 2021.4, 2021.3),
  Metric_1 = c(45, 58, NA, 20, 56, 32, 41, 38),
  Metric_2 = c(89, 23, 64, 76, 101, 78, NA, NA),
  Population = c(12000, 12000, 11000, 11000, 9700, 9700, 10100, 10100)
)

# 生成ALL区域的季度汇总
df_all_summary <- df %>%
  # 筛选当前季度（2022.2）及前3个季度的数据
  filter(Quarter >= 2021.3 & Quarter <= 2022.2) %>%
  summarise(
    Area = "ALL",
    Quarter = max(Quarter),
    # 计算每个指标的非Null值总和
    across(starts_with("Metric_"), ~sum(., na.rm = TRUE), .names = "{col}_sum"),
    # 仅提取对应指标非Null行的人口总和
    across(starts_with("Metric_"), ~sum(Population[!is.na(.)]), .names = "{col}_pop_sum")
  ) %>%
  # 计算比率：(指标总和/对应人口总和)*10000
  mutate(
    across(ends_with("_sum"), 
           ~ . / get(str_replace(cur_column(), "_sum", "_pop_sum")) * 10000,
           .names = "{str_remove(cur_column(), '_sum')}_rate")
  ) %>%
  # 保留需要的列并格式化小数
  select(Area, Quarter, ends_with("_rate")) %>%
  mutate(across(ends_with("_rate"), ~round(., 2)))

# 合并到原始数据（如果需要）
final_df <- bind_rows(df, df_all_summary) %>%
  arrange(Area, desc(Quarter))

运行后得到的df_all_summary完全匹配你期望的输出：

2. 适配你的真实数据集 #

你的数据集有40个变量、25个区域，且需要滚动4季度的计算逻辑，调整代码如下：

library(dplyr)
library(stringr)

# 处理季度格式，转为日期以便滚动窗口计算
full_data_rates_pop <- full_data_rates_pop %>%
  mutate(
    # 将季度字符串（如2019 Q1）转为日期
    Quarter_date = case_when(
      str_detect(Quarter, "Q1") ~ as.Date(paste0(str_extract(Quarter, "\\d{4}"), "-01-01")),
      str_detect(Quarter, "Q2") ~ as.Date(paste0(str_extract(Quarter, "\\d{4}"), "-04-01")),
      str_detect(Quarter, "Q3") ~ as.Date(paste0(str_extract(Quarter, "\\d{4}"), "-07-01")),
      str_detect(Quarter, "Q4") ~ as.Date(paste0(str_extract(Quarter, "\\d{4}"), "-10-01"))
    )
  ) %>%
  arrange(Quarter_date)

# 生成ALL区域的滚动4季度汇总
all_rolling_summary <- full_data_rates_pop %>%
  group_by(Quarter) %>%
  summarise(
    Area = "ALL",
    # 对第4到21列的指标，分别计算非Null总和与对应人口总和
    across(4:21, ~sum(., na.rm = TRUE), .names = "{col}_sum"),
    across(4:21, ~sum(Population_17.24[!is.na(.)]), .names = "{col}_pop_sum"),
    Quarter_date = max(Quarter_date)
  ) %>%
  # 计算比率
  mutate(
    across(ends_with("_sum"),
           ~ . / get(str_replace(cur_column(), "_sum", "_pop_sum")) * 10000,
           .names = "{str_remove(cur_column(), '_sum')}_rate")
  ) %>%
  # 选择需要的列
  select(Area, Quarter, ends_with("_rate"))

# 合并到原始数据并整理格式
final_data <- bind_rows(full_data_rates_pop, all_rolling_summary) %>%
  arrange(Area, desc(Quarter)) %>%
  mutate(Timeframe = if_else(is.na(Quarter), timeframe_value, "Quarterly")) %>%
  select(-Quarter_date) # 移除临时日期列

关键逻辑说明 #

• 列独立计算：通过across对每个指标列单独处理，用Population[!is.na(.)]仅提取当前指标非Null行的人口数据，确保各指标计算互不干扰。
• 滚动窗口适配：将季度转为日期后，可轻松通过group_by(Quarter)筛选当前季度及前3个季度的数据，实现滚动汇总。
• 扩展性：代码支持任意数量的指标列，只需调整across的列范围（如你的4:21列）即可。

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