how to join two dataframes by updating value contains duplicate for matched ids in r

user_13080549 0
Viewed 50

I have dataset dm which contains 1000 rows and 15 columns and looks like :

dm <- "ID  NAME    col1    col2    col3    col4    col5
123  a1      M       20      2.0    56      3   
234  b1      M       25      2.2    54      3   
345  c1      F       23      2.3    52      2   
456  d1      M       24      2.5    50      2   
567  e1      F       26      2.6    48      2   
678  f1      F       28      2.8    46      1   
789  h1      M       29      2.9    44      1   
900  k1      M       27      3.1    42      0   
123  a1      M       27      3.2    40      0   
234  b1      M       27      3.4    38     -1   
345  c1      F       25      3.5    36     -1"
dm <- read.table(text = dm, header = TRUE)

and my other dataframe df2 contains 2000 rows with 5 columns looks like

df2 <- "ID  NAME        col1   col2 col3
123   a1           0    1   2
234   b1           1    0   1
345   c1           2    2   0
456   d1           0    1   1"
df2 <- read.table(text = df2, header = TRUE)

when i used dplyr right_join df3 <- right_join(df2,dm,c("ID","NAME"))

it comes correctly but what went wrong was the resulted output shows like this

ID  NAME    col1    col2    col3    col4    col5     col10  col12   col13   …
123   a1    M        20      2.0    56        3        0       1    2   
234   b1    M        25      2.2    54        3        1       0    1   
345   c1    F        23      2.3    52        2        2       2    0   
456   d1    M        24      2.5    50        2        0       1    1   
123   a1    M        20      2.0    56        3        NA      NA   NA  
234   b1    M        25      2.2    54        3        NA      NA   NA  
345   c1    F        23      2.3    52        2        NA      NA   NA  
456   d1    M        24      2.5    50        2        NA      NA   NA

Expected result:

    ID   NAME  col1     col2    col3    col4    col5     col10  col12   col13   …
    123   a1    M        20      2.0    56        3        0       1    2   
    234   b1    M        25      2.2    54        3        1       0    1   
    345   c1    F        23      2.3    52        2        2       2    0   
    456   d1    M        24      2.5    50        2        0       1    1   
    123   a1    M        20      2.0    56        3        0       1    2   
    234   b1    M        25      2.2    54        3        1       0    1   
    345   c1    F        23      2.3    52        2        2       2    0
    456   d1    M        24      2.5    50        2        0       1    1
dplyr left-join r merge
1 Answers

When performing a mutating join, the ordering of the rows and columns of x is preserved as much as possible with groups being taken from x. In your code, x = df2 when it should be y =.

Reordering the data frames with x = df1 and y = df2 yields:

Data:

library(dplyr)

df1 <- "ID  NAME    col1    col2    col3    col4    col5
123  a1      M       20      2.0    56      3   
234  b1      M       25      2.2    54      3   
345  c1      F       23      2.3    52      2   
456  d1      M       24      2.5    50      2   
567  e1      F       26      2.6    48      2   
678  f1      F       28      2.8    46      1   
789  h1      M       29      2.9    44      1   
900  k1      M       27      3.1    42      0   
123  a1      M       27      3.2    40      0   
234  b1      M       27      3.4    38     -1   
345  c1      F       25      3.5    36     -1"
df1 <- read.table(text = df1, header = TRUE)

df2 <- "ID  NAME        col1   col2 col3
123   a1           0    1   2
234   b1           1    0   1
345   c1           2    2   0
456   d1           0    1   1"
df2 <- read.table(text = df2, header = TRUE)

Code:

df3 <- df1 %>% 
right_join(df2, by = c("ID", "NAME")) #df1 is piped (%>%) into right_join as x, making y = df2.

Output:

#>    ID NAME col1.x col2.x col3.x col4 col5 col1.y col2.y col3.y
#> 1 123   a1      M     20    2.0   56    3      0      1      2
#> 2 234   b1      M     25    2.2   54    3      1      0      1
#> 3 345   c1      F     23    2.3   52    2      2      2      0
#> 4 456   d1      M     24    2.5   50    2      0      1      1
#> 5 123   a1      M     27    3.2   40    0      0      1      2
#> 6 234   b1      M     27    3.4   38   -1      1      0      1
#> 7 345   c1      F     25    3.5   36   -1      2      2      0

Created on 2020-08-16 by the reprex package (v0.3.0)

user_133567300
Related