updating column values based on another data frame in R

Question

I've looked around stackoverflow and couldn't find what i was looking for, so if this is a duplicate post, sorry AND I'd greatly appreciate the link!

I have two data frames: CarDF and duplicateCarDF

ID <- c(1,2,3,4,5,6,7,8)
car <- c("acura", "audi", "benz", "benz", "bmw", "toyota", "toyota", "jeep")
year <- c(2001, 2002, '2004', '2016','1999', '2017', '2017',2005)

CarDF <- data.frame(ID, car, year)

ID2 <-c(4,7)
car2 <- c("benz2", "toyota2")
year2 <- c(2016, 2017)

duplicateCarDF <- data.frame(ID = ID2, car = car2, year = year2)

My goal is to update the cars in CarDF with the updated names in duplicateCarDF based on the IDs.

I've tried the following...

CarDF$car <- ifelse(duplicateCarDF$ID %in% CarDF$ID, duplicateCarDF$car, CarDF$car )

but it changes the car names to benz2 and toyota2 alternating. I just want to update the car for ID 4 and 7.

Any help would be greatly appreciated!

Frank · Accepted Answer · 2018-02-15 00:28:26Z

5

With data.table...

library(data.table)
setDT(CarDF)

CarDF[duplicateCarDF, on=.(ID), car := i.car]

   ID     car year
1:  1   acura 2001
2:  2    audi 2002
3:  3    benz 2004
4:  4   benz2 2016
5:  5     bmw 1999
6:  6  toyota 2017
7:  7 toyota2 2017
8:  8    jeep 2005

This is sometimes called an "update join".

answered Feb 15, 2018 at 0:28

Frank

66.9k8 gold badges104 silver badges190 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Kevin Arseneau · Accepted Answer · 2018-02-15 00:22:24Z

Using dplyr verbs we can left_join by ID and then conditionally replace car based on whether or not the new value is missing.

library(dplyr)

CarDF %>%
  left_join(
    duplicateCarDF %>%           # note: the year column doesn't add any
      select(ID, new_car = car), # value here unless you have duplicated ID values
    by = "ID"
  ) %>%
  mutate(
    car = if_else(
      is.na(new_car),
      as.character(car),    # note: I'm coercing these to character because
      as.character(new_car) # we've joined two df with different levels
    )
  ) %>%
  select(-new_car)

#   ID     car year
# 1  1   acura 2001
# 2  2    audi 2002
# 3  3    benz 2004
# 4  4   benz2 2016
# 5  5     bmw 1999
# 6  6  toyota 2017
# 7  7 toyota2 2017
# 8  8    jeep 2005

Kevin Arseneau · Accepted Answer · 2018-02-15 01:17:34Z

0

A base solution may be to use sapply on the index for your ifelse.

CarDF$car <- sapply(CarDF$ID, function(x) {
  ifelse(
    nrow(duplicateCarDF[duplicateCarDF$ID == x, ]) == 0, 
    as.character(CarDF[CarDF$ID == x, ]$car),
    as.character(duplicateCarDF[duplicateCarDF$ID == x, ]$car)
  )
})

# [1] "acura"   "audi"    "benz"    "benz2"   "bmw"     "toyota"  "toyota2" "jeep"

edited Feb 15, 2018 at 1:17

answered Feb 15, 2018 at 0:48

Kevin Arseneau

6,2641 gold badge24 silver badges41 bronze badges

Collectives™ on Stack Overflow

updating column values based on another data frame in R

3 Answers 3

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related