如何在tidy data.frame中按日期匹配替换指定变量为其他变量的条件和

嘿，我来帮你搞定这个问题！你现在需要把长格式数据里a变量的每个日期值替换成同日期下a和f的和，其他变量保持原样对吧？这里有两个简洁的方案，比你之前冗长的方法高效多了。

方案1：使用dplyr（tidyverse风格，推荐） #

如果你习惯用tidyverse工具链，这个方法最简洁直观，一行核心代码就能完成需求：

library(dplyr)
library(lubridate)

# 先构建你的原始数据（和你提供的一致，整理了格式）
timestep <- c("10/31/1921","11/30/1921","12/31/1921","1/31/1922","2/28/1922","3/31/1922","4/30/1922","5/31/1922","6/30/1922","7/31/1922","8/31/1922","9/30/1922", 
              "10/31/1921","11/30/1921","12/31/1921","1/31/1922","2/28/1922","3/31/1922","4/30/1922","5/31/1922","6/30/1922","7/31/1922","8/31/1922","9/30/1922", 
              "10/31/1921","11/30/1921","12/31/1921","1/31/1922","2/28/1922","3/31/1922","4/30/1922","5/31/1922","6/30/1922","7/31/1922","8/31/1922","9/30/1922", 
              "10/31/1921","11/30/1921","12/31/1921","1/31/1922","2/28/1922","3/31/1922","4/30/1922","5/31/1922","6/30/1922","7/31/1922","8/31/1922","9/30/1922", 
              "10/31/1921","11/30/1921","12/31/1921","1/31/1922","2/28/1922","3/31/1922","4/30/1922","5/31/1922","6/30/1922","7/31/1922","8/31/1922","9/30/1922", 
              "10/31/1921","11/30/1921","12/31/1921","1/31/1922","2/28/1922","3/31/1922","4/30/1922","5/31/1922","6/30/1922","7/31/1922","8/31/1922","9/30/1922")
value <- c(0,0,4474,7027,32458,20702,29682,53150,20632,0,0,0,0,0,26569,22253,0,1894,25018,7119,0,2289,0,988,0,0,0,3869,8138,0,0,0,0,0,950,0,0,158,6028,2086,67193, 4191,22303,5584,0,0,222,0,345,54,78,4545,2,4,0,0,186,113,256,4665,5756,78,34,20,323,3,0,0,9,354,299,8735)
variable <- c(rep("a",12), rep("b",12), rep("c",12), rep("d",12), rep("e",12), rep("f",12))
df <- data.frame(timestep, value, variable)
df$timestep <- mdy(df$timestep)

# 核心处理代码
result_df <- df %>%
  group_by(timestep) %>%
  mutate(
    value = ifelse(variable == "a", sum(value[variable %in% c("a", "f")]), value)
  ) %>%
  ungroup()

# 验证结果是否符合预期
all(result_df$value == ExpectedValues) # 返回TRUE说明结果正确

思路解释： #

1. 用group_by(timestep)按日期分组，确保我们只在同一个日期内计算总和
2. mutate函数修改value列：当变量是a时，取该组内a和f的value之和；其他变量保持原值不变
3. 最后ungroup()取消分组，回到原始的数据结构

方案2：使用Base R（无需额外包） #

如果你不想加载任何第三方包，纯Base R也能轻松实现：

library(lubridate)

# 同样先构建原始数据（和上面一致）
timestep <- c("10/31/1921","11/30/1921","12/31/1921","1/31/1922","2/28/1922","3/31/1922","4/30/1922","5/31/1922","6/30/1922","7/31/1922","8/31/1922","9/30/1922", 
              "10/31/1921","11/30/1921","12/31/1921","1/31/1922","2/28/1922","3/31/1922","4/30/1922","5/31/1922","6/30/1922","7/31/1922","8/31/1922","9/30/1922", 
              "10/31/1921","11/30/1921","12/31/1921","1/31/1922","2/28/1922","3/31/1922","4/30/1922","5/31/1922","6/30/1922","7/31/1922","8/31/1922","9/30/1922", 
              "10/31/1921","11/30/1921","12/31/1921","1/31/1922","2/28/1922","3/31/1922","4/30/1922","5/31/1922","6/30/1922","7/31/1922","8/31/1922","9/30/1922", 
              "10/31/1921","11/30/1921","12/31/1921","1/31/1922","2/28/1922","3/31/1922","4/30/1922","5/31/1922","6/30/1922","7/31/1922","8/31/1922","9/30/1922", 
              "10/31/1921","11/30/1921","12/31/1921","1/31/1922","2/28/1922","3/31/1922","4/30/1922","5/31/1922","6/30/1922","7/31/1922","8/31/1922","9/30/1922")
value <- c(0,0,4474,7027,32458,20702,29682,53150,20632,0,0,0,0,0,26569,22253,0,1894,25018,7119,0,2289,0,988,0,0,0,3869,8138,0,0,0,0,0,950,0,0,158,6028,2086,67193, 4191,22303,5584,0,0,222,0,345,54,78,4545,2,4,0,0,186,113,256,4665,5756,78,34,20,323,3,0,0,9,354,299,8735)
variable <- c(rep("a",12), rep("b",12), rep("c",12), rep("d",12), rep("e",12), rep("f",12))
df <- data.frame(timestep, value, variable)
df$timestep <- mdy(df$timestep)

# 第一步：计算每个日期下a和f的总和
af_sum <- aggregate(value ~ timestep, data = df[df$variable %in% c("a","f"),], sum)
names(af_sum)[2] <- "af_total"

# 第二步：合并总和到原数据，替换a的value
result_df_base <- merge(df, af_sum, by = "timestep", all.x = TRUE)
result_df_base$value <- ifelse(result_df_base$variable == "a", result_df_base$af_total, result_df_base$value)

# 第三步：去掉临时列，恢复原始列顺序
result_df_base <- result_df_base[, c("timestep", "value", "variable")]

# 验证结果
all(result_df_base$value == ExpectedValues) # 返回TRUE说明正确

思路解释： #

1. 用aggregate函数提取a和f的记录，按日期计算它们的总和，得到一个日期-总和的映射表
2. 用merge把这个映射表合并到原数据中，确保每个日期都能匹配到对应的总和
3. 用ifelse替换a变量的value为对应的总和，其他变量不变
4. 最后清理临时列，恢复原始的数据结构

这两个方案都能高效处理你的需求，不管数据量多大，都不需要手动逐个日期处理，代码简洁易读，维护起来也方便。

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