如何在R中按地理位置计算指定日期区间的TMAX均值？

问题背景 #

现有两个数据集：

• data.weather：包含两个地理位置（LAT+LON组合）、两年的TMAX数据，每个地点每年有10天记录
• data.locs：每行指定地理位置、年份、日期区间（DATE_0至DATE_1），需要为每行计算对应范围内的mean_TMAX

当前代码能识别日期区间，但会跨所有地理位置计算均值，未按LAT+LON+YEAR的组合筛选数据，导致结果错误。

现有代码 #

library(dplyr)
library(lubridate)

data.weather <- read.csv(text = "
LAT,LON,YEAR,DATE,TMAX
36,-89,2010,1/1/2010,25
36,-89,2010,1/2/2010,25
36,-89,2010,1/3/2010,25
36,-89,2010,1/4/2010,28
36,-89,2010,1/5/2010,28
36,-89,2010,1/6/2010,29
36,-89,2010,1/7/2010,25
36,-89,2010,1/8/2010,25
36,-89,2010,1/9/2010,25
36,-89,2010,1/10/2010,28
36,-89,2011,1/1/2011,26
36,-89,2011,1/2/2011,25
36,-89,2011,1/3/2011,28
36,-89,2011,1/4/2011,26
36,-89,2011,1/5/2011,27
36,-89,2011,1/6/2011,27
36,-89,2011,1/7/2011,28
36,-89,2011,1/8/2011,29
36,-89,2011,1/9/2011,27
36,-89,2011,1/10/2011,26
40,-96,2010,1/1/2010,29
40,-96,2010,1/2/2010,28
40,-96,2010,1/3/2010,25
40,-96,2010,1/4/2010,25
40,-96,2010,1/5/2010,28
40,-96,2010,1/6/2010,29
40,-96,2010,1/7/2010,26
40,-96,2010,1/8/2010,28
40,-96,2010,1/9/2010,26
40,-96,2010,1/10/2010,25
40,-96,2011,1/1/2011,29
40,-96,2011,1/2/2011,27
40,-96,2011,1/3/2011,29
40,-96,2011,1/4/2011,25
40,-96,2011,1/5/2011,28
40,-96,2011,1/6/2011,29
40,-96,2011,1/7/2011,29
40,-96,2011,1/8/2011,25
40,-96,2011,1/9/2011,25
40,-96,2011,1/10/2011,26
") %>%
  mutate(DATE = as.Date(DATE, format = "%m/%d/%Y"))

data.locs <- read.csv(text = "
LAT,LON,YEAR,DATE_0,DATE_1,GEN,PR
36,-89,2010,1/2/2010,1/9/2010,MN103,35
36,-89,2011,1/1/2011,1/10/2011,IA100,33
40,-96,2010,1/4/2010,1/8/2010,MN103,36
40,-96,2011,1/2/2011,1/6/2011,IA100,34
") %>%
  mutate(DATE_0 = as.Date(DATE_0, format = "%m/%d/%Y"),
         DATE_1 = as.Date(DATE_1, format = "%m/%d/%Y"))

tmax.calculation <- data.locs %>%
  group_by(LAT,LON,YEAR, GEN) %>%
  mutate(mean_TMAX = mean(data.weather$TMAX[data.weather$DATE %within% interval(DATE_0, DATE_1)]))

预期结果 #

LAT LON YEAR  DATE_0    DATE_1    GEN    PR  mean_TMAX
36  -89 2010  2010-01-02 2010-01-09 MN103 35  26.25
36  -89 2011  2011-01-01 2011-01-10 IA100 33  26.90
40  -96 2010  2010-01-04 2010-01-08 MN103 36  27.20
40  -96 2011  2011-01-02 2011-01-06 IA100 34  27.60

实际错误结果 #

LAT LON YEAR  DATE_0    DATE_1    GEN    PR  mean_TMAX
36  -89 2010  2010-01-02 2010-01-09 MN103 35  26.5625
36  -89 2011  2011-01-01 2011-01-10 IA100 33  27.0500
40  -96 2010  2010-01-04 2010-01-08 MN103 36  27.1000
40  -96 2011  2011-01-02 2011-01-06 IA100 34  27.1000

解决方案 #

方法一：逐行匹配筛选（rowwise） #

原代码的group_by无法限制全局数据集的筛选范围，改用rowwise让每一行的变量值仅指代当前行，同时添加LAT、LON、YEAR的匹配条件：

tmax.calculation <- data.locs %>%
  rowwise(LAT, LON, YEAR) %>%
  mutate(mean_TMAX = mean(data.weather$TMAX[data.weather$LAT == !!LAT & 
                                             data.weather$LON == !!LON & 
                                             data.weather$YEAR == !!YEAR & 
                                             data.weather$DATE %within% interval(DATE_0, DATE_1)])) %>%
  ungroup()

方法二：关联数据集后计算（更符合dplyr风格） #

先通过inner_join将两个数据集按LAT、LON、YEAR关联，再筛选日期区间，最后分组计算均值：

tmax.calculation <- data.locs %>%
  inner_join(data.weather, by = c("LAT", "LON", "YEAR")) %>%
  filter(DATE %within% interval(DATE_0, DATE_1)) %>%
  group_by(LAT, LON, YEAR, DATE_0, DATE_1, GEN, PR) %>%
  summarise(mean_TMAX = mean(TMAX), .groups = "drop")

两种方法均能得到预期结果，其中方法二逻辑更清晰，便于后续维护。

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