R语言按站点日期区间跨表计算均值为数据框新列赋值
2026-8-29
两个数据框的生成代码附在文末。
现有数据说明
- 第一个数据框
df1为3个不同站点的气象观测数据，结构预览如下：
site date temp X 2021-01-01 14 X 2021-01-02 NA X 2021-01-03 10 X 2021-01-04 14 X 2021-01-05 10 X 2021-01-06 10 X 2021-01-07 13 X 2021-01-08 12 X 2021-01-09 13 X 2021-01-10 7 X 2021-01-11 9 X 2021-01-12 6 X 2021-01-13 8 Y 2021-01-01 10 Y 2021-01-02 14 Y 2021-01-03 5 Y 2021-01-04 7 Y 2021-01-05 7 Y 2021-01-06 9 Y 2021-01-07 6 Y 2021-01-08 12 Y 2021-01-09 10 Y 2021-01-10 9 Y 2021-01-11 13 Y 2021-01-12 13 Y 2021-01-13 NA Y 2021-01-14 8 Y 2021-01-15 11 Y 2021-01-16 5 Y 2021-01-17 11 Y 2021-01-18 13 Y 2021-01-19 11 Y 2021-01-20 9 Y 2021-01-21 9 Y 2021-01-22 5 Y 2021-01-23 6 Y 2021-01-24 14 Y 2021-01-25 10 Y 2021-01-26 7 Z 2021-01-01 9 Z 2021-01-02 NA Z 2021-01-03 12 Z 2021-01-04 6 Z 2021-01-05 5 Z 2021-01-06 7 Z 2021-01-07 7 Z 2021-01-08 5 Z 2021-01-09 7 Z 2021-01-10 7 Z 2021-01-11 15 Z 2021-01-12 8 Z 2021-01-13 5 Z 2021-01-14 6 Z 2021-01-15 5 Z 2021-01-16 12 Z 2021-01-17 8 Z 2021-01-18 7 Z 2021-01-19 6 Z 2021-01-20 13 Z 2021-01-21 14 Z 2021-01-22 8 Z 2021-01-23 11 Z 2021-01-24 7
- 第二个数据框
df2为与气象站点同点位的野外观测数据：每个站点设置1台诱捕器，每隔数日清空诱捕器，分别统计捕获的不同物种个体数量。对df2中的每个站点，pose（诱捕器放置日期）始终为上一条记录withdrawal（诱捕器回收日期）的次日。本示例中物种信息存储在obs列，包含A、B、C、D、F、G共6类物种，freq列为对应物种的捕获个体数，结构预览如下：
site pose withdrawal obs freq X 2021-01-01 2021-01-03 A 31 X 2021-01-01 2021-01-03 B 42 X 2021-01-04 2021-01-05 A 14 X 2021-01-06 2021-01-13 D 16 X 2021-01-06 2021-01-13 F 36 Y 2021-01-01 2021-01-04 G 49 Y 2021-01-01 2021-01-04 A 29 Y 2021-01-01 2021-01-04 C 45 Y 2021-01-05 2021-01-14 D 25 Y 2021-01-05 2021-01-14 A 50 Y 2021-01-15 2021-01-14 B 40 Y 2021-01-19 2021-01-26 B 39 Z 2021-01-01 2021-01-03 C 25 Z 2021-01-04 2021-01-05 F 3 Z 2021-01-04 2021-01-05 B 16 Z 2021-01-06 2021-01-14 C 19 Z 2021-01-15 2021-01-19 A 12 Z 2021-01-15 2021-01-19 B 26 Z 2021-01-15 2021-01-19 F 2 Z 2021-01-20 2021-01-24 A 24
计算需求
需要在df2中新增mean_T列，存储df2每一条记录对应的时段平均气温。
例如
ID = 1的记录，平均气温需取df1中site = 'X'、日期为2021-01-01、2021-01-02、2021-01-03的temp值计算均值，计算时自动忽略temp中的NA值。
已有方案局限
针对结构更简单、df2中每个站点每个日期区间仅对应1条记录的场景，可使用以下拆分数据的代码计算平均气温，但无法适配当前同日期区间存在多条不同物种观测记录的场景：
df1 <- split(df1, with(df1, site), subset(df1, select = -site) ) df1 <- lapply(df1, function(x) x[(names(x) %in% c("ID", "date", "temp"))]) df2 <- split(df2, with(df2, site), subset(df2, select = -site) ) df2 <- lapply(df2, function(x) x[(names(x) %in% c("ID", "pose", "withdrawal"))]) library(dplyr) library(tidyr)
社区用户提供的参考代码
- @TarJae 提供的单站点均值计算代码：
mean_X <- df2$X %>% pivot_longer(-ID, values_to = "date") %>% full_join(df1$X, by= "date") %>% arrange(date) %>% fill(ID, .direction = "down") %>% group_by(ID) %>% summarise(mean_T = mean(temp, na.rm = TRUE)) %>% left_join(df2$X, by="ID")
- @Jon Spring 提供的可运行全量适配代码片段（已补充
na.rm=TRUE参数适配缺失值场景）：
df2 %>% mutate(days = (withdrawal - pose + 1) %>% as.integer) %>% tidyr::uncount(days, .id = "row") %>% transmute(ID, site, date = pose + row - 1) %>% left_join(df1) %>% group_by(ID) %>% summarize(mean_T = mean(temp, na.rm = TRUE)) %>% right_join(df2)
两个示例数据框的生成代码
df1 <- data.frame( site = c(rep('X', 13), rep('Y', 26), rep('Z', 24) ) , date = c( seq( as.Date("2021-01-01"), by="day", length.out=13 ), seq( as.Date("2021-01-01"), by="day", length.out=26 ), seq( as.Date("2021-01-01"), by="day", length.out=24 )) , temp = c(14, NA, 10, 14, 10, 10, 13, 12, 13, 7, 9, 6, 8, 10, 14, 5, 7, 7, 9, 6, 12, 10, 9, 13, 13, NA, 8, 11, 5, 11, 13, 11, 9, 9, 5, 6, 14, 10, 7, 9, NA, 12, 6, 5, 7, 7, 5, 7, 7, 15, 8, 5, 6, 5, 12, 8, 7, 6, 13, 14, 8, 11, 7) ) df2 <- data.frame( site = c( rep('X', 5), rep('Y', 7), rep('Z', 8) ) , pose = as.Date( c("2021-01-01", "2021-01-01", "2021-01-04", "2021-01-06", "2021-01-06", "2021-01-01", "2021-01-01", "2021-01-01", "2021-01-05", "2021-01-05", "2021-01-15", "2021-01-19" , "2021-01-01", "2021-01-04", "2021-01-04", "2021-01-06", "2021-01-15", "2021-01-15", "2021-01-15", "2021-01-20") ) , withdrawal = as.Date( c( "2021-01-03", "2021-01-03", "2021-01-05", "2021-01-13", "2021-01-13", "2021-01-04", "2021-01-04", "2021-01-04", "2021-01-14", "2021-01-14", "2021-01-14", "2021-01-26" , "2021-01-03", "2021-01-05", "2021-01-05", "2021-01-14", "2021-01-19", "2021-01-19", "2021-01-19", "2021-01-24" ) ) , obs = c( 'A', 'B', 'A', 'D', 'F', 'G', 'A', 'C', 'D', 'A', 'B', 'B' , 'C', 'F', 'B', 'C', 'A', 'B', 'F', 'A') , freq = c(31, 42, 14, 16, 36, 49, 29, 45, 25, 50, 40, 39, 25, 3, 16, 19, 12, 26, 2, 24) ) df2 <- cbind(ID = 1:nrow(df2), df2)
注：提问者英语非母语，若表述存在歧义可在评论区指出。
内容的提问来源于stack exchange，提问作者Jeanne Durivage
相关产品推荐
相关产品推荐
