merge multiple DFs based on column names and row names in R [duplicate]

Question

I have 3 Dfs

I need to split it based on columns and row names like shown below

I tried merge_recurse option from reshape, looks like it merges row by row. Please help me to get the columns added side by side.

The final output should merge same columns from all DFs in to each new DFs like below,

  C1.DF1 C1.DF2 C1.DF3

G1  0      1     NA
G2  3      NA    1
G3  3      NA    2
G4  NA     0         NA
G5  NA     2         NA
G6  NA     NA    4
        
    C2.DF1  C2.DF2  C2.DF3
G1  0          2    NA
G2  1         NA    1
G3  1         NA    3
G4  NA         2    NA
G5  NA         1    NA
G6  NA        NA    3
        
        
  C3.DF1    C3.DF2  C3.DF3
G1  1          3     NA
G2  2          NA    1
G3  1          NA    4
G4  NA         2     NA
G5  NA         3     NA
G6  NA         NA    2

df1<- data.frame(C1=c(0,3,3),C2=c(0,1,1),C3=c(1,2,1),row.names = c("G1","G2","G3"))
df2<- data.frame(C1=c(1,0,2),C2=c(2,2,1),C3=c(3,2,3),row.names = c("G1","G4","G5"))
df3<- data.frame(C1=c(1,2,4),C2=c(1,3,3),C3=c(1,4,2),row.names = c("G2","G3","G6"))
df_list<- list(df1,df2,df3)
data<- merge_recurse(df_list)

This is what i got after using merge_recurse. (https://i.sstatic.net/wiwfS8eY.png)

Answer 1

Here is a base R solution.
Create a column with the row names and return the df with the new column in 1st place. Pipe to Reduce to merge the data sets into one output df.

See this SO post.

df1<- data.frame(C1=c(0,3,3),C2=c(0,1,1),C3=c(1,2,1),row.names = c("G1","G2","G3"))
df2<- data.frame(C1=c(1,0,2),C2=c(2,2,1),C3=c(3,2,3),row.names = c("G1","G4","G5"))
df3<- data.frame(C1=c(1,2,4),C2=c(1,3,3),C3=c(1,4,2),row.names = c("G2","G3","G6"))
df_list<- list(df1,df2,df3)

df_list |>
  lapply(\(x) {x$rn <- row.names(x); x[c(4, 1:3)]}) |> 
  Reduce(\(...) merge(..., all = TRUE), x = _)
#>   rn C1 C2 C3
#> 1 G1  0  0  1
#> 2 G1  1  2  3
#> 3 G2  1  1  1
#> 4 G2  3  1  2
#> 5 G3  2  3  4
#> 6 G3  3  1  1
#> 7 G4  0  2  2
#> 8 G5  2  1  3
#> 9 G6  4  3  2

^{Created on 2024-07-04 with reprex v2.1.0}

Answer 2

library(tidyverse)
df1 <- data.frame(C1 = c(0, 3, 3), C2 = c(0, 1, 1), C3 = c(1, 2, 1), row.names = c("G1", "G2", "G3"))
df2 <- data.frame(C1 = c(1, 0, 2), C2 = c(2, 2, 1), C3 = c(3, 2, 3), row.names = c("G1", "G4", "G5"))
df3 <- data.frame(C1 = c(1, 2, 4), C2 = c(1, 3, 3), C3 = c(1, 4, 2), row.names = c("G2", "G3", "G6"))
df_list <- list(df1, df2, df3)

(df_list2 <- map(df_list, rownames_to_column) |>
  set_names(c("DF1", "DF2", "DF3")))

(df_list3 <- imap_dfr(df_list2, \(d, nm){
          pivot_longer(d, cols = -rowname) |> 
            mutate(dfname = nm)
        }) |> arrange(name, rowname))

(split_list <- split(df_list3,
                     ~name))

(df_list4 <- map(split_list, \(d){
      pivot_wider(d,
        names_from = c("name", "dfname"),
        names_sep = ".",
        values_from = "value"
      )
    }))

$C1
# A tibble: 6 × 4
  rowname C1.DF1 C1.DF2 C1.DF3
  <chr>    <dbl>  <dbl>  <dbl>
1 G1           0      1     NA
2 G2           3     NA      1
3 G3           3     NA      2
4 G4          NA      0     NA
5 G5          NA      2     NA
6 G6          NA     NA      4

$C2
# A tibble: 6 × 4
  rowname C2.DF1 C2.DF2 C2.DF3
  <chr>    <dbl>  <dbl>  <dbl>
1 G1           0      2     NA
2 G2           1     NA      1
3 G3           1     NA      3
4 G4          NA      2     NA
5 G5          NA      1     NA
6 G6          NA     NA      3

$C3
# A tibble: 6 × 4
  rowname C3.DF1 C3.DF2 C3.DF3
  <chr>    <dbl>  <dbl>  <dbl>
1 G1           1      3     NA
2 G2           2     NA      1
3 G3           1     NA      4
4 G4          NA      2     NA
5 G5          NA      3     NA
6 G6          NA     NA      2