Collapse two data frames into one where entries get concatenated

user_13922631 0
Viewed 70

I have two data frames with identical row.names and colnames. However, the entries in each field vary between the two data.frames.

Like the following structure:

df1 = data.frame(SETD2 = c('AMP', NA, ''), PIK3CA = c('', 'AMP', 'AMP'))

row.names(df1) = c('Sample1', 'Sample2', 'Sample3')

df2 = data.frame(SETD2 = c('', 'Nonstop', 'truncating'), PIK3CA = c('nonsense', 'InFrame', ''))

row.names(df2) = c('Sample1', 'Sample2', 'Sample3')

I am simply looking for an easy solution to merge these two data frames into a single one, where the desired output just looks like

df3 = data.frame(SETD2 = c('AMP;', 'NA;NonStop', 'truncating'), PIK3CA = c('nonsense', 'AMP;InFrame', 'AMP;')

As you can see, just that the individual entries gets merged and separated by a ';'

Many many thanks, chris.

dataframe r merge
2 Answers

These use only base R.

1) We first use Map to paste over the two data frames giving list L and then replace the df1 entries to convert L to a data frame.

L <- Map(function(...) paste(..., sep = ";"), df1, df2)
replace(df1, TRUE, L)

Alternately replace the last line with this:

out <- df1
out[] <- L

or with this:

as.data.frame(L, row.names = rownames(df1))

In each case we get:

              SETD2      PIK3CA
Sample1        AMP;   ;nonsense
Sample2  NA;Nonstop AMP;InFrame
Sample3 ;truncating        AMP;

2) An alternative is to convert both df1 and df2 to matrices since they can be pasted in parallel. We paste them together giving character vector p and then use replace or out[]<- as in (1). This alternative does not use Map or similar.

p <- paste(as.matrix(df1), as.matrix(df2), sep = ";")
replace(df1, TRUE, p)

or replace the last line above with:

out <- df1
out[] <- p
user_5165480

I would suggest a tidyverse approach which is close to what you want, using paste0() and bind_rows(). Here the code:

library(tidyverse)
#Code
bind_rows(df1 %>% mutate(id=row_number()),df2 %>% mutate(id=row_number())) %>%
  group_by(id) %>%
  summarise(SETD2=paste0(SETD2,collapse = ';'),
            PIK3CA=paste0(PIK3CA,collapse = ';')) %>% select(-id)

Output:

# A tibble: 3 x 2
  SETD2       PIK3CA     
  <chr>       <chr>      
1 AMP;        ;nonsense  
2 NA;Nonstop  AMP;InFrame
3 ;truncating AMP;
user_20808480
Related