R语言按条件时间范围匹配筛选生猪重量与农户收入关联数据集

高效实现方案 #

完全不需要嵌套for循环，向量化操作或关联匹配方案的性能远高于循环实现，尤其数据量过万时性能差距会非常明显。以下提供两种常用的R语言实现方案：

方案1：tidyverse实现（可读性优先，适合十万级以内数据）

library(tidyverse)

# 构造数据集A
df_a <- tibble(
  ID = c(1,1,1,2,3,3),
  Year = c(2011,2012,2015,2018,2002,2009),
  Weight = c(27.59,36.5,40.29,56.9,26.1,86.8)
)

# 构造数据集B，提前剔除无效NA记录
df_b <- tibble(
  ID = c(3,1,1,2),
  Year = c(2002,2013,2012,NA),
  Revenue = c(5,3,1,NA)
) %>% drop_na()

# 按ID汇总每个农户的所有申报年份
b_agg <- df_b %>% 
  group_by(ID) %>% 
  summarise(report_years = list(Year))

# 关联匹配生成最终结果
result <- df_a %>% 
  left_join(b_agg, by = "ID") %>% 
  rowwise() %>% 
  mutate(
    # 提取符合时间范围的最近申报年份
    valid_year = ifelse(is.null(report_years), NA, 
                        max(report_years[report_years <= Year & Year - report_years <= 2], na.rm = T)),
    Status = case_when(
      is.null(report_years) ~ "Exclude, no Income recorded",
      is.na(valid_year) | is.infinite(valid_year) ~ "Exclude, no Income before recorded weight and within 2 years range",
      T ~ paste0("Include, Income reported on ", valid_year)
    )
  ) %>% 
  select(-report_years, -valid_year)

方案2：data.table非等值连接实现（性能优先，适合百万级以上数据）

library(data.table)
setDT(df_a)
setDT(df_b)

# 重命名B的年份列避免冲突
setnames(df_b, "Year", "report_year")

# 非等值连接直接匹配符合时间条件的申报记录
df_a[df_b[!is.na(report_year)], on = .(ID, Year >= report_year, Year <= report_year + 2), 
     valid_year := i.report_year, allow.cartesian = T]

# 保留每个生猪重量记录对应的最近有效申报年份
df_a <- df_a[, .SD[which.max(valid_year)], by = .(ID, Year, Weight)]

# 生成状态标注
df_a[, Status := fcase(
  is.na(valid_year) & !ID %in% df_b$ID, "Exclude, no Income recorded",
  is.na(valid_year), "Exclude, no Income before recorded weight and within 2 years range",
  default = paste0("Include, Income reported on ", valid_year)
)]

# 清理多余字段
df_a[, valid_year := NULL]

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