requalityorders

check if id1 in table1 equal to id2 in table2, if yes add content from table1 to table2 in the "order" of id2 (without changing order)

table1:

id1    fname      lname         by     bm   bd

 362   FRANK     MUELLER        1967    9   27
 55    MARTIN    SCHWARZ        1967    2   17
 116   HERBERT   ZIMMERMANN     1961    11  6
 326   HANS      SCHMITT        1945    8   14
 96    UWE         NA           2000    7   5

table2

id2

55
96
116
116
96

if table numeric id1 equal to numeric id2 in table2 add the columns to table2 that match form table1 (fname, lname, by, bm, bd) with their content. NOTE: when adding to table2 the columns that match from table1, should be in the order of id2 (55,96,116,226,326,...)

when i did it i use this function:merge(tabl2,table1,by="id2",sort=FALSE) and i got this:

id2   fname      lname        by     bm   bd

55   MARTIN    SCHWARZ        1967    2   17
96   UWE         NA           2000    7   5
96   UWE         NA           2000    7   5
116  HERBERT   ZIMMERMANN     1961    11  6
116  HERBERT   ZIMMERMANN     1961    11  6
 .    .
 .    .

I got it with an order from a smaller number to the bigger number but what I want is to get the order as a column of id2 in table2 without changing the order of it.

Solution

  • You could try (using base R):

    df3 <- merge(df2, df1, by.x="id2", by.y="id1")

df3[match(df2$id2, df3$id2),]

    which gives you

        id2   fname      lname   by bm bd
1    55  MARTIN    SCHWARZ 1967  2 17
2    96     UWE       <NA> 2000  7  5
4   116 HERBERT ZIMMERMANN 1961 11  6
4.1 116 HERBERT ZIMMERMANN 1961 11  6
2.1  96     UWE       <NA> 2000  7  5

    Using dplyr:

    left_join(df2, df1, by=c("id2" = "id1"))

    yields

    # A tibble: 5 x 6
    id2 fname   lname         by    bm    bd
  <dbl> <chr>   <chr>      <dbl> <dbl> <dbl>
1    55 MARTIN  SCHWARZ     1967     2    17
2    96 UWE     NA          2000     7     5
3   116 HERBERT ZIMMERMANN  1961    11     6
4   116 HERBERT ZIMMERMANN  1961    11     6
5    96 UWE     NA          2000     7     5