如何将dat2的值插入到dat1指定列的对应Class行？

需要将dat2中的值匹配插入到dat1的Time1列，规则如下：

• 按ID列匹配
• dat1中Class=1的行，用dat2对应ID的Value1填充Time1
• dat1中Class=2的行，用dat2对应ID的Value2填充Time1
• Class=0的行保持原Time1值不变

示例数据 #

dat1<-read.table (text=" ID Rat Garden  Class   Time1   Time2   Time3
1   12  12  0   15  16  20
1   13  0   1   NA  NA  NA      
2   13  11  0   18  12  16
2   9   0   1   NA  NA  NA      
1   6   13  0   17  14  14
1   7   0   2   NA   NA  NA     
2   4   14  0   17  16  12
2   3   0   2   NA  NA  NA      
", header=TRUE)
 
dat2<-read.table (text=" ID Value1  Value2
1   6   7
2   5   4
", header=TRUE)

期望结果 #

ID  Rat Garden  Class   Time1   Time2   Time3
1   12  12  0   15  16  20
1   13  0   1   6       
2   13  11  0   18  12  16
2   9   0   1   5       
1   6   13  0   17  14  14
1   7   0   2   7       
2   4   14  0   17  16  12
2   3   0   2   4       

解决方案 #

方法1：Base R实现 #

先将dat2转换为长格式，再通过ID和Class的组合匹配填充：

# 将dat2转为长格式，对应Class=1和2
dat2_long <- reshape(dat2, direction = "long", varying = list(c("Value1", "Value2")), 
                     v.names = "Value", idvar = "ID", timevar = "Class", times = c(1,2))
row.names(dat2_long) <- NULL

# 匹配ID+Class组合，替换Time1的NA值
dat1$Time1 <- ifelse(dat1$Class %in% c(1,2), 
                     dat2_long$Value[match(paste(dat1$ID, dat1$Class), paste(dat2_long$ID, dat2_long$Class))],
                     dat1$Time1)

# 查看结果
dat1

方法2：Tidyverse(dplyr+tidyr)实现 #

用tidyverse工具链更直观，适合数据处理场景：

library(dplyr)
library(tidyr)

# 将dat2转为长格式，映射Class与Value的对应关系
dat2_long <- dat2 %>%
  pivot_longer(cols = starts_with("Value"), names_to = "Class", values_to = "Time1") %>%
  mutate(Class = as.integer(sub("Value", "", Class)))

# 左连接后合并Time1列，保留有效数据
dat1_result <- dat1 %>%
  left_join(dat2_long, by = c("ID", "Class")) %>%
  mutate(Time1 = coalesce(Time1.y, Time1.x)) %>%
  select(-Time1.x, -Time1.y)

# 查看结果
dat1_result

两种方法均可得到目标结果，可根据个人代码习惯选择。

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