Merge by row index and column label

Question

> df1
   x  y
1 13  7
2 14  8
4 16 10
5 17 11
6 18 12


> df2
  index_df1 y
1         2 a
2         4 d
3         5 e
4         6 f

How can I merge df1 with df2, with the index of df1 and the column "index_df1" of df2. Like:

z <- merge(df1, df2, by.x = 0, by.y = "index_df1", all.x = TRUE)

Add rownames as column, then merge as usual, link provided by @CPak above. — zx8754, May 23 '18 at 19:56

MKR · Answer 1 · 2018-05-23T20:05:59.400

0

You can use dplyr::left_join to merge two data.frames as:

library(dplyr)

df1 %>% mutate(rn = row_number()) %>%
  left_join(df2, by = c("rn" = "index_df1")) %>%
  select(-rn)

#    x y.x  y.y
# 1 13   7 <NA>
# 2 14   8    a
# 3 15   9 <NA>
# 4 16  10    d
# 5 17  11    e
# 6 18  12    f

Updated: Based on feedback from OP. If rows are not sequential in df1 then an option could be as:

# Use `index_df1` to add a column having value of x in df1
df2$x <- df1[df2$index_df1,"x"]

# Now, merge
merge(df1, df2[,c("y", "x")], by.x = "x", by.y = "x", all.x = TRUE)
#    x y.x  y.y
# 1 13   7 <NA>
# 2 14   8    a
# 3 15   9 <NA>
# 4 16  10    d
# 5 17  11    e
# 6 18  12    f

edited May 23 '18 at 20:05

answered May 23 '18 at 19:51

MKR

19,739
4
23
33

I forgot to add that the index in df1 is not continuous, as I deleted some rows on it. – Chris May 23 '18 at 19:54
Why are we using full_join? – zx8754 May 23 '18 at 19:55
@zx8754 Good point. I should have used `left_join` as OP intended you use `all.x = TRUE` – MKR May 23 '18 at 19:57
@Chris Then you can add a column in `df2` as `df2$x <- df1[df2$index_df1,"x"]` and then merge `df1` and `df2` – MKR May 23 '18 at 20:01

Merge by row index and column label

1 Answers1