Is there a way in R to UPDATE one data frame from another data frame?

Question

I have two data frames in R and I need to update the values in one data frame with the value in another data frame. I could easily do this in SQL using UPDATEbut for whatever reason the SQLite in R doesn't seem to process the same way.

Sample example:

DF1 

col A   

xyz      
123

DF2 

col A   col B

xyz     abc
123     456

I would like to replace col A in DF1 with the values in col B from DF2 based on the fact that col A from DF1 are equal to values in col A from DF2. Given I am pretty new to R. in SQL this would be a simple UPDATE statement but I can't get the syntax to work in sqldf

this seems to do what I was needing - I just wasn't familiar with the syntax:

DF1$colA <- DF2[match(DF1$colA,DF2$colB),2]

what is your expected output ? I guess you are looking for `merge(df1, df2)` — Ronak Shah, Nov 15 '19 at 02:31
i would expect DF1 col A to be populated with the values in col B DF2 - I think that match function actually can do this I was just not understanding the syntax as a SQL guy... — XCCH004, Nov 15 '19 at 02:44
it does help - but I ended up going with the above. Thank you very much — XCCH004, Nov 15 '19 at 02:54
`DF1 <- sqldf("select Col_B as col_A from DF1 left join DF2 using (Col_A)")` — G. Grothendieck, Nov 18 '19 at 14:29

score 2 · Accepted Answer · answered Nov 15 '19 at 02:46

You can use an ifelse to check equality and replace with Col_B from df2 otherwise leave the value of Col_A in df1 as-is. Here is how:

df1$Col_A=ifelse(as.character(df1$Col_A) == as.character(df2$Col_A), as.character(df2$Col_B), as.character(df1$Col_A)
# df1
# Col_A
# 1   abc
# 2   456
# 3   567

Data

df1 <- data.frame(Col_A=c("xyz", "123", "567"))                                                                                                                                                                                                       
df2 <- data.frame(Col_A = c("xyz", "123"), 
                  Col_B = c("abc", "456"))

Hope this helps.

Is there a way in R to UPDATE one data frame from another data frame?

1 Answers1

Data