如何用dplyr基于另一数据框的年份区间条件提取数据子集

完全不需要写for循环，使用dplyr（1.1.0及以上版本）内置的非等值连接功能就能高效完成需求，具体实现如下：

步骤1：加载依赖包与构造测试数据 #

library(dplyr)

# 构造lsp数据框
lsp <- structure(list(Depth = c(0.5, 1.5, 2.5, 3.5, 4.5, 5.5, 6.5, 7.5, 
8.5, 10.5, 13.5, 14.5, 18.5, 19.5, 27.5, 28.5, 32, 35.5, 40.5, 
41.5), RSL = c(0.03, 0.03, 0.01, 0.01, -0.04, -0.01, -0.03, 0, 
0.04, 0.03, 0, -0.01, -0.05, -0.07, -0.19, -0.24, -0.31, -0.31, 
-0.27, -0.29), RSL_err_1sig = c(0.1, 0.1, 0.1, 0.1, 0.1, 0.1, 
0.1, 0.1, 0.1, 0.1, 0.1, 0.1, 0.1, 0.1, 0.1, 0.1, 0.1, 0.1, 0.1, 
0.1), Age_mean = c(2001.754499, 1994.278776, 1987.678949, 1980.805889, 
1973.270485, 1965.018421, 1957.442729, 1952.134369, 1949.031929, 
1945.148184, 1939.132213, 1936.957531, 1927.311071, 1924.379033, 
1897.26123, 1892.977317, 1876.1995, 1858.135589, 1825.967544, 
1820.605298), Age.min = c(1996.752238, 1985.111654, 1977.483594, 
1968.26211, 1961.886124, 1958.219318, 1947.496532, 1943.084044, 
1941.761439, 1935.843414, 1923.952516, 1920.057048, 1906.228232, 
1902.242998, 1875.327613, 1869.925103, 1834.992176, 1811.928966, 
1784.998245, 1767.524866), Age.max = c(2006.75676, 2003.445898, 
1997.874304, 1993.349668, 1984.654846, 1971.817524, 1967.388926, 
1961.184694, 1956.302419, 1954.452954, 1954.31191, 1953.858014, 
1948.39391, 1946.515068, 1919.194847, 1916.029531, 1917.406824, 
1904.342212, 1866.936843, 1873.68573)), class = "data.frame", row.names = c(NA, 
-20L))

# 构造dynamics数据框
dynamics <- structure(list(Year = 1815:1820, dynamics = c(-76.01893261, -64.50519732, 
-66.06270761, -76.22822397, -72.35960029, -77.34157443)), row.names = c(NA, 
6L), class = "data.frame")

步骤2：核心匹配逻辑 #

使用inner_join()的非等值连接规则，直接匹配dynamics中Year落在lsp每行Age.min和Age.max区间的记录：

# 输出合并后的大表，每一行是匹配成功的lsp记录+对应的dynamics记录
result <- lsp %>%
  inner_join(
    dynamics,
    join_by(Year >= Age.min, Year <= Age.max)
  )

如果需要按lsp的每行单独存为子数据集，加一步嵌套即可：

# 每个lsp行对应一个嵌套的dynamics子集
result_nested <- lsp %>%
  left_join(
    dynamics,
    join_by(Year >= Age.min, Year <= Age.max)
  ) %>%
  group_by(Depth, Age.min, Age.max) %>% # 用lsp的唯一标识字段分组即可
  tidyr::nest(dynamics_subset = c(Year, dynamics))

低版本dplyr兼容方案 #

如果你的dplyr版本低于1.1.0不支持join_by，可以用笛卡尔积加过滤的方式实现：

result <- lsp %>%
  crossing(dynamics) %>%
  filter(Year >= Age.min, Year <= Age.max)

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