How to add data from one df to another based on whether a value exists in both dfs

user_18712845 0
Viewed 33

I have a data frame with a word list, and data frames with details about words (concreteness, age of acquisition, etc.). I need to add those details to my word list, but not all words from my word list are in the databases with details.

Example:

WordList$Words <- c("apple", "pear", "potato")

database$Words <- c("apple", "strawberry", "peach", "pear")

database$AoA <- c(3.1, 4.2, 5.0, 3.5)

I want it to check "does this word from the word list exist in the database? If yes, then put the database value of AoA for that word in the AoA column of the word list."

so that the data frame would end up like this:

Words AoA
apple 3.1
pear 3.5
potato NA

I tried variations of the following:

WordListAoA <- WordList %>% mutate(AoA = ifelse(Word %in% database$words, database$AoA, .x))

But I get an error for using $ ($ operator is invalid for atomic vectors) and I don't think this code would tell R which cell from the database to use in the word list.

Can anyone help?

dataframe if-statement r
1 Answers

your example is not set up properly, so you can get your original code to work or merge like Maël suggested.

Your example is not proper because you are using the $ with atomic vector, when it should be used to extract an element from a data object, such as a data frame. To properly set up your example and run your code:

WordList <- data.frame(Words = c("apple", "pear", "potato"))

database <- data.frame(Words = c("apple", "strawberry", "peach", "pear"),
                      AoA <- c(3.1, 4.2, 5.0, 3.5))

WordListAoA <- WordList %>%  mutate(AoA = ifelse(Words %in% database$Words, database$AoA, NA)) 

# Words AoA
# 1  apple 3.1
# 2   pear 4.2
# 3 potato  NA

To merge, you could use the base R approach:

merge(WordList, database, by = "Words", all.x = TRUE)

# Words AoA....c.3.1..4.2..5..3.5.
# 1  apple                        3.1
# 2   pear                        3.5
# 3 potato                         NA
user_121097880
Related