How do I create a conditional column based off another dataframe in R?-CodePudding

I have a 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

I have a second dataframe2:

I would like to make a new column in dataframe1 labelled "Error" that is 0 for everything except for IDs that have an Error listed in dataframe2 or an NA listed in dataframe2. The output would be:

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

CodePudding user response：

Another solution:

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

CodePudding user response：

Here's another option using tibble::deframe, which will turn df2 into a named vector with the names being ID and the values being Error:

library(dplyr)

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

A similar solution in base R would be:

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

Output

   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

CodePudding user response：

Create a logical vector in the second data for NA values to differentiate the NA and then do a join so that the logical column differentiates the initial NA from the ones created in left_join

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)

-output

 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

data

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))