Merge multiple data frames with partially matching rows

Question

I have data frames with lists of elements such as NAMES. There are different names in dataframes, but most of them match together. I'd like to combine all of them in one list in which I'd see whether some names are missing from any of df.

DATA sample for df1:

  X                    x
1 1 rh_Structure/Focus_S
2 2 rh_Structure/Focus_C
3 3 lh_Structure/Focus_S
4 4 lh_Structure/Focus_C
5 5   RH_Type-Function-S
6 6        RH_REFERENT-S

and for df2

  X                    x
1 1 rh_Structure/Focus_S
2 2 rh_Structure/Focus_C
3 3 lh_Structure/Focus_S
4 4 lh_Structure/Focus_C
5 5            UCZESTNIK
6 6                COACH

and expected result would be:

                  NAME. df1 df2
1                COACH  NA   6
2 lh_Structure/Focus_C   4   4
3 lh_Structure/Focus_S   3   3
4        RH_REFERENT-S   6  NA
5 rh_Structure/Focus_C   2   2
6 rh_Structure/Focus_S   1   1
7   RH_Type-Function-S   5  NA
8            UCZESTNIK  NA   5

I can do that with merge.data.frame(df1,df2,by = "x", all=T), but the I can't do it with more df with similar structure. Any help would be appreciated.

What's the problem with using merge or *_join? Is it just that you want to merge more than 2 dataframes? If so, take a look at Reduce or purrr::reduce — divibisan

thelatemail thelatemail · Accepted Answer · 2019-03-13T23:08:20

It might be easier to work with this in a long form. Just rbind all the datasets below one another with a flag for which dataset they came from. Then it's relatively straightforward to get a tabulation of all the missing values (and as an added bonus, you can see if you have any duplicates in any of the source datasets):

dfs <- c("df1","df2")
dfall <- do.call(rbind, Map(cbind, mget(dfs), src=dfs))
table(dfall$x, dfall$src)

#                        df1 df2
#   COACH                  0   1
#   lh_Structure/Focus_C   1   1
#   lh_Structure/Focus_S   1   1
#   RH_REFERENT-S          1   0
#   rh_Structure/Focus_C   1   1
#   rh_Structure/Focus_S   1   1
#   RH_Type-Function-S     1   0
#   UCZESTNIK              0   1

Merge multiple data frames with partially matching rows

1 Answers