如何在R中创建基于另一个数据框的条件列?

Question

我有一个dataframe1:

ID  Var1  Var2
1   40    45
2   30    35
3   70    65
4   70    15
5   40    15
6   80    45
7   20    15
8   20    15
9   50    35
10  70    25

我有第二个dataframe2:

ID  Error
1   0
2   1
5   1
6   1   
9   0
10  NA
21  0
22  NA

我想在dataframe1中创建一个新列，标签为"Error"除了在dataframe2中列出了Error或在dataframe2中列出了NA的id之外，所有内容都为0。输出将是:

ID  Var1  Var2  Error
1   40    45    0
2   30    35    1
3   70    65    0
4   70    15    0
5   40    15    1
6   80    45    1
7   20    15    0
8   20    15    0
9   50    35    0
10  70    25    NA

Answer 1

另一个解决方案:

library(tidyverse)
df1 <- data.frame(
ID = c(1L, 2L, 3L, 4L, 5L, 6L, 7L, 8L, 9L, 10L),
Var1 = c(40L, 30L, 70L, 70L, 40L, 80L, 20L, 20L, 50L, 70L),
Var2 = c(45L, 35L, 65L, 15L, 15L, 45L, 15L, 15L, 35L, 25L)
)
df2 <- data.frame(
ID = c(1L, 2L, 5L, 6L, 9L, 10L),
Error = c(0L, 1L, 1L, 1L, 0L, NA)
)
df2 %>% 
mutate(Error = replace_na(.$Error,55)) %>% 
left_join(df1, .) %>% 
mutate(Error = replace_na(.$Error,0)) %>% 
mutate(Error = ifelse(.$Error==55,NA,.$Error))
#> Joining, by = "ID"
#>    ID Var1 Var2 Error
#> 1   1   40   45     0
#> 2   2   30   35     1
#> 3   3   70   65     0
#> 4   4   70   15     0
#> 5   5   40   15     1
#> 6   6   80   45     1
#> 7   7   20   15     0
#> 8   8   20   15     0
#> 9   9   50   35     0
#> 10 10   70   25    NA

Answer 2

这是使用tibble::deframe的另一个选项，它将把df2变成命名向量，名称为ID，值为Error:

library(dplyr)
df1 %>% 
mutate(Error = ifelse(ID %in% df2$ID, tibble::deframe(df2)[as.character(ID)], 0))

以R为基底的近似解为:

lookup <- with(df2, setNames(Error, ID))
within(df1, Error <- ifelse(ID %in% df2$ID, lookup[as.character(ID)], 0))

ID Var1 Var2 Error
1   1   40   45     0
2   2   30   35     1
3   3   70   65     0
4   4   70   15     0
5   5   40   15     1
6   6   80   45     1
7   7   20   15     0
8   8   20   15     0
9   9   50   35     0
10 10   70   25    NA

Answer 3

在第二个数据中为NA值创建一个逻辑向量，以区分NA，然后执行一个连接，以便逻辑列将初始NA与left_join中创建的NA区分开来

library(dplyr)
df2 %>%
mutate(yes = is.na(Error)) %>% 
left_join(df1, .) %>% 
mutate(Error = case_when(is.na(yes) & 
is.na(Error) ~ 0L, TRUE ~ Error), yes = NULL) 

与产出

ID Var1 Var2 Error
1   1   40   45     0
2   2   30   35     1
3   3   70   65     0
4   4   70   15     0
5   5   40   15     1
6   6   80   45     1
7   7   20   15     0
8   8   20   15     0
9   9   50   35     0
10 10   70   25    NA

数据

df1 <- structure(list(ID = 1:10, Var1 = c(40L, 30L, 70L, 70L, 40L, 80L, 
20L, 20L, 50L, 70L), Var2 = c(45L, 35L, 65L, 15L, 15L, 45L, 15L, 
15L, 35L, 25L)), class = "data.frame", row.names = c(NA, -10L
))
df2 <- structure(list(ID = c(1L, 2L, 5L, 6L, 9L, 10L, 21L, 22L), Error = c(0L, 
1L, 1L, 1L, 0L, NA, 0L, NA)), class = "data.frame", row.names = c(NA, 
-8L))