Updating specific rows of one data frame using another, without non-specifically overwriting other entries (R)

Question

I'd like to use df1 to update df2, to produce a data frame that looks like df3. Any help would be much appreciated.

df1 <- data.frame(ID=c("D-10003","D-10004"), date=c(2,2), length=c(22,45))
df2 <- data.frame(ID=c("D-10001","D-10003","D-10002","D-10004","D-10005"), date=c(1,NA,NA,NA,2), hair=c(2,3,NA,2,3))
df3 <- data.frame(ID=c("D-10001","D-10003","D-10002","D-10004","D-10005"), date=c(1,2,NA,2,2),hair=c(2,3,NA,2,3))

I've been trying various variations on this, but it always seems to overwrite legitimate entries (eg for ID D-10001, date should remain 1), and I have no clue as to why.

df2$date<-df1[match(df2$ID, df1$ID),2]

Balter · Accepted Answer · 2017-08-31 19:54:53Z

1

df2$date[is.na(df2$date)] <- df1$date[match(df2$ID[is.na(df2$date)],df1$ID)]

answered Aug 31, 2017 at 19:54

Balter

1,0956 silver badges12 bronze badges

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

2 Comments

D.sen Over a year ago

This is my go to solution for matching situations, I would note that this looks for the first match, not all matches. So, for example, if you had D-10004 twice in df1 with two different date values, it would only map over the first date value.

Balter Over a year ago

@D.sen Absolutely yep. Probably should have commented that myself. Beware the duplicates. Thanks.

Ricardo Fernandes Campos · Accepted Answer · 2017-08-31 19:50:19Z

0

I'd suggest something like this:

sapply(dt1$ID,function(x)dt2$date[dt2$ID==x]<<-dt1$date[dt1$ID==x])

PS: no need to use assinging to anything. It's being assgined from within the function.

answered Aug 31, 2017 at 19:50

Ricardo Fernandes Campos

9117 silver badges19 bronze badges

Comments

cmaher · Accepted Answer · 2017-08-31 20:14:25Z

0

Here's a data.table solution:

library(data.table)
setDT(df1)
setDT(df2)
df2[df1, date := df1$date, on = c(ID = "ID")]
#         ID date hair
# 1: D-10001    1    2
# 2: D-10003    2    3
# 3: D-10002   NA   NA
# 4: D-10004    2    2
# 5: D-10005    2    3

As in the first posted answer, there's no need for assignment, as df2 is modified in-place.

Here's another option that protects against overwriting non-NA values, even if the IDs match:

df2[df1, `:=` (date = ifelse(is.na(date), df1$date, date)), on = c(ID = "ID")]

edited Aug 31, 2017 at 20:14

answered Aug 31, 2017 at 19:53

cmaher

5,2641 gold badge24 silver badges34 bronze badges

Collectives™ on Stack Overflow

Updating specific rows of one data frame using another, without non-specifically overwriting other entries (R)

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related