CODEWITHSUNDEEP
rdplyr
Blaze9
Blaze9

Reputation: 193

R - Merge two cells from different dataframes together

I have two dataframes with the same matching column/row values as outlined below:

df1

    ID1 ID2 ID3 ID4 ID5 ID6
Response1   A       D   A       F
Response2   B       D   B       E
Response3   C   D   D   D       A
Resposne4   D       A   A       B

and df2

  ID1   ID2 ID3 ID4 ID5 ID6
Response1   A       C   N       E
Response2   F           D       
Response3   E   B   C   A       B
Resposne4   X       A           A
Response5   F       A       C

I want to combine the matching column/rows separated by a ;. The order doesn't matter, df1 or df2 can come first. In the example below I have df2 first

And here is the expected output:

df3

    ID1 ID2 ID3 ID4 ID5 ID6
Response1   A;A     C;D N;A     E;F
Response2   F;B     D3  D;B     E
Response3   E;C B;D C;D A;D     B;A
Resposne4   X;D     A;A A       A;B
Response5   F       A       C

I generally try and include some test code where I'm getting close to the answer, but I honestly don't even know where to begin for this.

Thank you!

Upvotes: 0

Views: 42

Answers (2)

rjen
rjen

Reputation: 1972

A tidyverse option.

library(dplyr)
library(stringr)

df2 %>%
  mutate(Response = row_number()) %>%
  bind_rows(mutate(df1, Response = row_number())) %>%
  group_by(Response) %>%
  summarise(across(everything(), ~ str_c(., collapse = ';'))) %>%
  mutate(Response = str_c('Response', Response, sep = ' '))

# # A tibble: 5 x 7
#   Response   ID1   ID2   ID3   ID4   ID5   ID6  
#   <chr>      <chr> <chr> <chr> <chr> <chr> <chr>
# 1 Response 1 A;A   NA    C;D   N;A   NA    E;F  
# 2 Response 2 F;B   NA    NA    D;B   NA    NA   
# 3 Response 3 E;C   B;D   C;D   A;D   NA    B;A  
# 4 Response 4 X;D   NA    A;A   NA    NA    A;B  
# 5 Response 5 F     NA    A     NA    C     NA

Upvotes: 1

Ronak Shah
Ronak Shah

Reputation: 388907

We can get the common rownames from the two dataframes and paste the two dataframes of same sizes.

df3 <- df2
common_rows <- intersect(rownames(df1), rownames(df2))
df3[common_rows, ] <- trimws(paste(as.matrix(df2[common_rows, ]), as.matrix(df1), sep = ';'), whitespace = ';')
df3

#          ID1 ID2 ID3 ID4 ID5 ID6
#Response1 A;A     C;D N;A     E;F
#Response2 F;B       D D;B       E
#Response3 E;C B;D C;D A;D     B;A
#Resposne4 X;D     A;A   A     A;B
#Response5   F       A       C

Upvotes: 1

Related Questions