replace values with other values from another data frame with conditions, the others are unchanged

Question

my dummy data looks like:

df1:

rsID---------chr-----position

x--------------12------319

x--------------12------322

x--------------12------327

x--------------12------321

x--------------14-------412

x--------------15-------3123

x--------------17-------413

df2:

rsID---------chr-----position

rs12---------14-------412

rs20---------17-------413

My Expected result looks like:

rsID---------chr-----position

x--------------12------319

x--------------12------322

x--------------12------327

x--------------12------321

rs12---------14-------412

x-------------15-------3123

rs20---------17-------413

It means that I want to match rsIDs from df2 to that from df1 (in other words, replace 'x' from df1 with rsIDs from df2) only if corresponding values in two columns 'chr' and 'position' of df1 and df2 are the same. The other rsIDs do not change (still, 'x').

My attempt does not works:

df3 = ifelse(df1$chr == df2$chr & df1$position == df2$position, df1$rsID == df2$rsID, "x")

Ronak Shah · Accepted Answer · 2020-12-01 06:33:56Z

1

You can join the two dataframes and select non-NA value from rsID columns.

library(dplyr)

left_join(df1, df2, by = c('chr', 'position')) %>%
  mutate(rsID = coalesce(rsID.y, rsID.x)) %>%
  select(names(df1))

#  rsID chr position
#1    x  12      321
#2 rs12  14      412
#3    x  15     3123
#4 rs20  17      413

In base R :

transform(merge(df1, df2, by = c('chr', 'position'), all.x = TRUE), 
          rsID = ifelse(is.na(rsID.y), rsID.x, rsID.y))[names(df1)]

edited Dec 1, 2020 at 6:33

answered Dec 1, 2020 at 6:27

Ronak Shah

391k20 gold badges173 silver badges237 bronze badges

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

1 Comment

Huy Nguyen Over a year ago

I do not know whether your idea works well because there are two conditions: "only if corresponding values in two columns 'chr' and 'position' of df1 and df2 are the same". To my understanding, your code just matches corresponding values in the 'chr' column. Anw, I will give it a go

Collectives™ on Stack Overflow

replace values with other values from another data frame with conditions, the others are unchanged

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related