如果满足另一列中的条件，则将数据从一列复制到另一列

我正在尝试创建一个标题为 Study_id、Conflict 的表，并创建第三列和第四列，其中包含与列中的值相对应的数据： Conflict

下面的代码实现了这个目的。然而它相当长，特别是因为我希望将其扩展以涵盖列中的数百个不同值：冲突

预先感谢您的任何指点

df <- data.frame(study_id=c("1", "1", "4", "4", "5"), 
                 Conflict=c("WATER.START", "WATER.STOP", "OIL.START", "NA", "WATER.STOP"), 
                 Result=c("TRUE", "TRUE", "TRUE", "NA", "TRUE"))

df2 <- data.frame(study_id=c("1", "2", "3", "4", "5"), 
                  WATER.start=c(1, 1, 2, NA, 6), 
                  WATER.truestart=c(1, 1, 2, NA, 25), 
                  WATER.stop=c(33, 3, 2, NA, 8), 
                  WATER.truestop= c(34, 4, 2, NA, 8))

final <- left_join(df, df2, by ='study_id')

dd <- final %>% filter(Result == "TRUE" & Conflict == "WATER.START")
dd <- dd %>% subset(., Conflict == "WATER.START", 
                    select=c(study_id, Conflict, WATER.start, WATER.truestart))
dd <- dd %>% rename(initial=WATER.start) %>% rename(verification=WATER.truestart)

ee <- final %>% filter(Result == "TRUE" & Conflict == "WATER.STOP")
ee <- ee %>% subset(., Conflict == "WATER.STOP", 
                    select=c(study_id, Conflict, WATER.stop, WATER.truestop))
ee <- ee %>% rename(initial=WATER.stop) %>% rename(verification=WATER.truestop)

ff <- bind_rows(dd, ee)
gg <- ff %>% select(study_id, Conflict, initial, verification)

gg 
#   study_id    Conflict initial verification
# 1        1 WATER.START       1            1
# 2        1  WATER.STOP      33           34
# 3        5  WATER.STOP       8            8

首先，因为看起来你只关注c('WATER.START', 'WATER.STOP'), subset你的第一个df。接下来，对于每一个MARGIN=1（即每一行），我们apply匿名函数\(x)选择适当的列df2, using tolower匹配大小写，并且cbind在一起。最后使用重命名列setNames and rbind结果列表。

subset(df, Conflict %in% c('WATER.START', 'WATER.STOP')) |>
  apply(MARGIN=1, \(x) {
    mt <- match(tolower(x[2]), tolower(names(df2)))
    cbind(t(x[1:2]), df2[df2$study_id == x[1], c(mt, mt + 1)]) |>
    setNames(c('study_id', 'Conflict', 'initial', 'verification'))
}) |> do.call(what=rbind)
#   study_id    Conflict initial verification
# 1        1 WATER.START       1            1
# 2        1  WATER.STOP      33           34
# 5        5  WATER.STOP       8            8

你也可以使用字典a（这可能会扩展到您可能正在使用的其他级别）。

a <- c(WATER.START='WATER.start', WATER.STOP='WATER.stop')

subset(df, Conflict %in% c('WATER.START', 'WATER.STOP')) |>
  apply(MARGIN=1, \(x) {
    mt <- match(a[match(x[2], names(a))], names(df2))
    cbind(t(x[1:2]), df2[df2$study_id == x[1], c(mt, mt + 1)]) |>
      setNames(c('study_id', 'Conflict', 'initial', 'verification'))
  }) |> do.call(what=rbind)
#   study_id    Conflict initial verification
# 1        1 WATER.START       1            1
# 2        1  WATER.STOP      33           34
# 5        5  WATER.STOP       8            8

However,我认为你真正需要的是reshape您的数据。

## basic
reshape(df2, direction='long', idvar=1, varying=list(c(2, 4), c(3, 5))) 
#     study_id time WATER.start WATER.truestart
# 1.1        1    1           1               1
# 2.1        2    1           1               1
# 3.1        3    1           2               2
# 4.1        4    1          NA              NA
# 5.1        5    1           6              25
# 1.2        1    2          33              34
# 2.2        2    2           3               4
# 3.2        3    2           2               2
# 4.2        4    2          NA              NA
# 5.2        5    2           8               8

## enhanced
reshape(df2, dir='long', idvar='study_id',
        varying=list(c("WATER.start", "WATER.stop"), c("WATER.truestart", "WATER.truestop")),
        timevar='foo', times=c('water.start', 'water.stop'), v.names=c('initial', 'verification')) 
#               study_id         foo initial verification
# 1.water.start        1 water.start       1            1
# 2.water.start        2 water.start       1            1
# 3.water.start        3 water.start       2            2
# 4.water.start        4 water.start      NA           NA
# 5.water.start        5 water.start       6           25
# 1.water.stop         1  water.stop      33           34
# 2.water.stop         2  water.stop       3            4
# 3.water.stop         3  water.stop       2            2
# 4.water.stop         4  water.stop      NA           NA
# 5.water.stop         5  water.stop       8            8