CODEWITHSUNDEEP
rdataframe
Simon
Simon

Reputation: 119

How to do lookup between 2 data frame

Here is my dataset:

type <- c("apple","orange","banana","others")
price <- c(10,9,8,7)
shop <- data.frame(type,price)

fruit <- c("apple","orange","banana","mango","lemon")
buy <- data.frame(fruit)

shop
    type price
1  apple    10
2 orange     9
3 banana     8
4 others     7

buy
   fruit
1  apple
2 orange
3 banana
4  mango
5  lemon

How can i have the result like this ?

   fruit   type price
1  apple  apple    10
2 orange orange     9
3 banana banana     8
4  mango others     7
5  lemon others     7

I tried to use merge or join, but mango and lemon is not in data.frame shop, it only return NA. I want to determine that fruit not in "type", all return to others and price at 7.

Upvotes: 0

Views: 39

Answers (1)

www
www

Reputation: 39174

A solution using dplyr. Notice that I changed the way you created example data frame because I don't want the columns to be factor. stringsAsFactors = FALSE makes them as character.

library(dplyr)

# Find out what are "others".
others <- fruit[!fruit %in% type]

dat <- buy %>%
  mutate(type = ifelse(fruit %in% others, "others", fruit)) %>%
  left_join(shop, by = "type")
dat
#    fruit   type price
# 1  apple  apple    10
# 2 orange orange     9
# 3 banana banana     8
# 4  mango others     7
# 5  lemon others     7

DATA

type <- c("apple","orange","banana","others")
price <- c(10,9,8,7)
shop <- data.frame(type,price, stringsAsFactors = FALSE)

fruit <- c("apple","orange","banana","mango","lemon")
buy <- data.frame(fruit, stringsAsFactors = FALSE)

Upvotes: 3

Related Questions