How to check if values in one dataframe exist in another dataframe in R?

user_14766041 0
Viewed 3195

Suppose we have a data frame like this:

id  reply  user_name
1   NA     John
2   NA     Amazon
3   NA     Bob

And another data frame like this:

name  organisation
John  Amazon
Pat   Apple

Is there a way to fill in the reply column in the first data frame with 'True' or 'False' if the values in column 3 match either columns 1 or 2 in the second data frame? So for example, since John and Amazon from the second data frame exist in the first data frame, I want the first data frame to update as so:

id  reply  user_name
1   True   John
2   True   Amazon
3   False  Bob
dataframe r function loops
4 Answers

Try this using %in% and a vector for all values:

#Code
df1$reply <- df1$user_name %in% c(df2$name,df2$organisation)

Output:

df1
  id reply user_name
1  1  TRUE      John
2  2  TRUE    Amazon
3  3 FALSE       Bob

Some data used:

#Data1
df1 <- structure(list(id = 1:3, reply = c(NA, NA, NA), user_name = c("John", 
"Amazon", "Bob")), class = "data.frame", row.names = c(NA, -3L
))

#Data2
df2 <- structure(list(name = c("John", "Pat"), organisation = c("Amazon", 
"Apple")), class = "data.frame", row.names = c(NA, -2L))
user_20808480

We can use %in% in base R

df1$reply <- df1$user_name %in%  unlist(df2)

If we want to change the format of the logical to character string

df1$reply <- sub("^(.)(.*)", "\\1\\L\\2", df1$reply, perl = TRUE)
df1$reply
#[1] "True"  "True"  "False"

data

df1 <- structure(list(id = 1:3, reply = c(NA, NA, NA), user_name = c("John", 
"Amazon", "Bob")), class = "data.frame", row.names = c(NA, -3L
))

df2 <- structure(list(name = c("John", "Pat"), organisation = c("Amazon", 
"Apple")), class = "data.frame", row.names = c(NA, -2L))
user_37322710

Here's how you can get the exact output you're looking for with 3 lines of code!

df1 <- data.frame(id = 1:3, reply = NA, user.name = c("John", "Amazon", "Bob"), stringsAsFactors = F)
df2 <- data.frame(id = 1:2, name = c("John", "Pat"), organisation = c("Amazon", "Apple"), stringsAsFactors = F)

df1$reply <- df1$user.name %in%  unlist(df2) %>% as.character() %>% str_to_title()

Output

id reply user.name
 1  True      John
 2  True    Amazon
 3 False       Bob

You will need the packages dplyr, magrittr, and stringr, which I highly recommend for data wrangling of all kinds.

user_94140050

Building off the first answer, you can also solve this in a tidy way too.

#Building your dataframes
df1 <- data.frame(id = 1:3, reply = NA, user.name = c("John", "Amazon", "Bob"), stringsAsFactors = F)
df2 <- data.frame(id = 1:2, name = c("John", "Pat"), organisation = c("Amazon", "Apple"), stringsAsFactors = F)


  df1 %>%
    mutate(reply = user.name %in% c(df2$name, df2$organisation))

I like personally the tidy solution because then you can easily pipe through the result to get more insights--for instance, if you want to know how many people replied, that just takes one more line:

  df1 %>%
    mutate(reply = user.name %in% c(df2$name, df2$organisation)) %>%
    summarize(reply_sum = sum(reply))
user_98774450
Related