How to update a column in one dataframe based on another dataframe?

Question

I am needing to see how to update one column in a data frame where the employeeID matches that of another data frame.

For example:

df1 > 

empID     salary
1         10000
2         15000
3         0

df2 >

empID     salary2
1         10000
2         15000
3         20000

I am needing to see how to update it where df1$salary = 0, then update it where df1$empID = df2$empID.

I tried this but received "No such column: salary2" error :

df1$salary <- ifelse(df1$salary == 0,sqldf("UPDATE df1 SET salary = salary2 WHERE df1.empID = df2.empID"),df1$salary)

In SQL update updates a table rather than returning a value. You need sqldf(c("update ...", "select * from df1")) to actually return something or else instead of using update use the appropriate SQL select statement. — G. Grothendieck
– G. Grothendieck, Commented May 31, 2019 at 14:55

Sotos · Accepted Answer · 2019-05-31 13:41:35Z

2

Here is another option with merge,

transform(merge(df1, df2, by = 'empID'), 
               salary = replace(salary, salary == 0, salary2[salary == 0]), 
               salary2 = NULL)

#  empID salary
#1     1  10000
#2     2  15000
#3     3  20000

You can also use ifelse instead of replace for salary, i.e.

salary = ifelse(salary == 0, salary2, salary)

answered May 31, 2019 at 13:41

Sotos

51.6k6 gold badges35 silver badges69 bronze badges

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

Comments

Ronak Shah · Accepted Answer · 2019-05-31 13:42:24Z

1

We could do

#find empID in df1 where salary is 0
inds <- df1$empID[df1$salary == 0]

#match empID with df2 and get respective salary and update df1
df1$salary[inds] <- df2$salary2[match(inds, df2$empID)]
df1
#  empID salary
#1     1  10000
#2     2  15000
#3     3  20000

This should also work if you have multiple entries with 0 in df1.

We can do the same using ifelse like

ifelse(df1$salary == 0, df2$salary2[match(df1$empID, df2$empID)], df1$salary)

edited May 31, 2019 at 13:42

answered May 31, 2019 at 13:36

Ronak Shah

391k20 gold badges173 silver badges237 bronze badges

2 Comments

jwalls91 Over a year ago

I see, is there any way I can implement this within my ifelse statement though? There is other conditions I am checking for as well with the real dataframe that I did not include in my sample code.

Ronak Shah Over a year ago

@jwalls91 updated the post using ifelse approach.

Collectives™ on Stack Overflow

How to update a column in one dataframe based on another dataframe?

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related