Replace values from a column of one dataframe by values from another dataframe

Question

I am trying to find the solution to this problem, I have two data frames, one is like

DF1

faID    uID
1     20909
1     6661
1     1591
1     28065
1     42783
1     3113
1     21647
1     3825
2     134766
2     271168
2     16710
2     4071608
2     2046526
2     5081272

and another data frame look like this

DF2

uID   user_cent_w
1591    15844
42783   466
21647   1514
29695   13958
94120   3615
83098   128
138776  709
90352   991
115384  8039
74483   128

I want to add a new column user_cent to DF1 and the value of that column match the values of uID in DF2 or replace the values of uID in DF1 by the value of user_cent_w in DF2, i.e., if uID of DF1 matches the value of DF2, i.e., user_cent_w then replace uID by user_cent_w values.

I have tried the solution from

replace value in dataframe based on another data frame

but this replaces the values of faID as well in DF1.

My expected output will look like this:

faID   user_cent_w
  1      15844
  1      466
  1      1514
  1      13958
  1      3615
  1      128
  1      709
  1      991
  1      8039
  1      128
  1      6489
  1      1781
  2      5735
  2      2072
  2      128
  2      128
  2      2304
  2      9301
  2      1282

try merge(df1, df2, by = "uID", all.x = TRUE); does this approximate what you wish to achieve? — Alex
– Alex, Commented Aug 27, 2014 at 7:04
yep it works in both merge and the probable solution by @akrun thank you — Naveed Khan Wazir
– Naveed Khan Wazir, Commented Aug 27, 2014 at 7:37

akrun · Accepted Answer · 2014-08-27 07:18:31Z

1

Try:

 library(dplyr)
 res <- left_join(df1,df2,by="uID")
 res$uID[!is.na(res$user_cent_w)] <- res$user_cent_w[!is.na(res$user_cent_w)]
 res[,1:2]
   res[,1:2]
  #  faID     uID
  #1     1   20909
  #2     1    6661
  #3     1   15844
  #4     1   28065
  #5     1     466
  #6     1    3113
  #7     1    1514
  #8     1    3825
  #9     2  134766
  #10    2  271168
  #11    2   16710
  #12    2 4071608
  #13    2 2046526
  #14    2 5081272

Or

  left_join(df1, df2, by="uID") %>% 
  mutate(uID=ifelse(is.na(user_cent_w), uID, user_cent_w)) %>%
  select(-user_cent_w)

edited Aug 27, 2014 at 7:18

answered Aug 27, 2014 at 7:11

akrun

891k38 gold badges590 silver badges700 bronze badges

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

5 Comments

Naveed Khan Wazir Over a year ago

after a long time realized there is small problem and that is that some of the value of variable(faID) matches the values of (uID) so the code is not replacing that values i.e for example "faID" has a value 2090 and this is also in "uID" . so the code does not replace the value and leaves the uID (which is a number not a value of that user-cent-w.

akrun Over a year ago

@user3841811 Did you updated the dataset and the expected result?

Naveed Khan Wazir Over a year ago

I think the code is doing well what the problem is that my computer didnot calculate the value for user_cent_w for all of the user(uID)

Naveed Khan Wazir Over a year ago

yes 2nd code is doing my job the 1st one is just confirming the centrality values

akrun Over a year ago

@user3841811 If it is not the code problem, what do you want me to do?

Community · Accepted Answer · 2020-06-20 09:12:55Z

Although this old question already has an accepted answer, I would like to add two data.table solutions for the sake of completeness.

The first one creates a new object

library(data.table)
# coerce to data.table and right join on uID
result <- setDT(DF2)[setDT(DF1), on = "uID"][
  # replace uID by user_cent_w where available, remove column
  !is.na(user_cent_w), uID := user_cent_w][, -"user_cent_w"]
result

        uID faID
 1:   20909    1
 2:    6661    1
 3:   15844    1
 4:   28065    1
 5:     466    1
 6:    3113    1
 7:    1514    1
 8:    3825    1
 9:  134766    2
10:  271168    2
11:   16710    2
12: 4071608    2
13: 2046526    2
14: 5081272    2

The second one updates DF1 in place while joining which avoids to copy the object in order to save memory and time:

setDT(DF1)[setDT(DF2), on = "uID", uID := ifelse(is.na(user_cent_w), uID, user_cent_w)]
DF1

    faID     uID
 1:    1   20909
 2:    1    6661
 3:    1   15844
 4:    1   28065
 5:    1     466
 6:    1    3113
 7:    1    1514
 8:    1    3825
 9:    2  134766
10:    2  271168
11:    2   16710
12:    2 4071608
13:    2 2046526
14:    2 5081272

Data

DF1 <- structure(list(faID = c(1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 2L, 2L, 
2L, 2L, 2L, 2L), uID = c(20909L, 6661L, 1591L, 28065L, 42783L, 
3113L, 21647L, 3825L, 134766L, 271168L, 16710L, 4071608L, 2046526L, 
5081272L)), .Names = c("faID", "uID"), row.names = c(NA, -14L
), class = "data.frame")

DF2 <- structure(list(uID = c(1591L, 42783L, 21647L, 29695L, 94120L, 
83098L, 138776L, 90352L, 115384L, 74483L), user_cent_w = c(15844L, 
466L, 1514L, 13958L, 3615L, 128L, 709L, 991L, 8039L, 128L)), .Names = c("uID", 
"user_cent_w"), row.names = c(NA, -10L), class = "data.frame")

Collectives™ on Stack Overflow

Replace values from a column of one dataframe by values from another dataframe

2 Answers 2

5 Comments

Data

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

Data

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related