根据列匹配返回其他行中存在的值

Question

我有以下数据：

df <- data.frame(Business_Date = c("01/01/2018", "01/01/2019", "01/01/2020"), Business_Date_2019 = c("01/01/2019","01/01/2019","01/01/2019"), Television = c("A","B","C"))

我想创建第四栏，其中包含2019年这一特定日期的电视：

desired_output <- data.frame(Business_Date = c("01/01/2018", "01/01/2019", "01/01/2020"), Business_Date_2019 = c("01/01/2019","01/01/2019","01/01/2019"), Television = c("A","B","C"),
                             Television_2019 = c("B","B","B"))

然而，当我写到：

df$Television_2019 <- case_when(
  df$Business_Date == df$Business_Date_2019 ~ df$Television,
  TRUE ~ NA_character)

它不返回desired_output：

  Business_Date Business_Date_2019 Television Television_2019
1    01/01/2018         01/01/2019          A            <NA>
2    01/01/2019         01/01/2019          B               B
3    01/01/2020         01/01/2019          C            <NA>

由于第1行和第3行在第二列"01/01/2019"中也有，所以我也希望在第四列中为这些行返回"B"。

如何编写从df返回desired_output的脚本？

Answer 1

您可以通过创建一个命名的向量作为查找表来实现这一点：

tv_lookup <- setNames(df$Television, df$Business_Date)
df$Television_2019 <- tv_lookup[df$Business_Date_2019]

df
#   Business_Date Business_Date_2019 Television Television_2019
# 1    01/01/2018         01/01/2019          A               B
# 2    01/01/2019         01/01/2019          B               B
# 3    01/01/2020         01/01/2019          C               B

case_when(df$Business_Date == df$Business_Date_2019)之所以没有给出您想要的，是因为它将Business_Date_2019中的每个元素与Business_Date中相同位置的元素进行比较。对于示例数据，这将导致：

"01/01/2018" == "01/01/2019"  # FALSE, therefore <NA>
"01/01/2019" == "01/01/2019"  # TRUE, therefore "B"
"01/01/2020" == "01/01/2019"  # FALSE, therefore <NA>