Merge multiple dataframes while preserving row names

Question

I have 5 tables that I am trying to merge while preserving the row names while adding each pertinent column onto the table in an RMarkdown File. I am showing two of my tables for the example...

TABLE 1 k1_1
k1_1

TABLE 2 k1_2
k1_2

I want the final table to look like this:
Final table K1
but with the two remaining colums added to the right of "V1"

I can merge the two together using K1 <- merge(k1_1, k1_2, by="row.names") but when I try K1 <- merge(k1_1, k1_2, k1_3, k1_4, k1_5, by = "row.names") I have no luck

These were standard data frames with the row names as columns then I used s.data.frame(t()) to transform to this table structure.

I have also tried multiple different methods with no success.

k1_1 <- head(by_source_baseline)[1]
k1_2 <- head(by_source_allocation)[1]
k1_3 <- head(by_source_allocation)[1]
k1_4 <- head(by_source_allocation)[1]
k1_5 <- head(by_source_allocation)[1]
kable(list(k1_1, k1_2, k1_3, k1_4, k1_5))

I have also tried using merge(), various join functions, rbind.fill( ), and bind_rows()

Welcome to StackOverflow! Please post example data as text (ideally copy-pastable code such as the output of `dput(k1_1)`) rather than images. This will get you one step closer to a [minimal reproducible example](https://stackoverflow.com/questions/5963269/how-to-make-a-great-r-reproducible-example) — Aurèle, Apr 14 '23 at 12:51
The key to the answer is `reduce(x, join_function)` or `Reduce(join_function, x)` — GuedesBF, Apr 14 '23 at 13:24

score 1 · Answer 1 · answered Apr 14 '23 at 13:22

We can put the elements in a list, then transfer the rownames_to_column(), and finally reduce the resulting list with left_join

library(purrr)
library(dplyr)

df_list  <- list(k1_1, k1_2, k1_3, k1_4, k1_5)

df_list |>
    map(\(x) rownames_to_column(x, var = "Row.names")) |>
    reduce(left_join)

Nick Glättli · Answer 2 · 2023-04-14T13:02:27.793

As I understand it, you cannot merge simply more than 2 data frames like that. When you do it like this, it works. Maybe take a look here: merge 3 data.frames by column names

library(tidyverse)

char <- c("a", "b", "c", "d")
v1 <- c(1:4)
v2 <- c(5:8)
v3 <- c(9:12)

k1 <- data.frame(v1, row.names = char)
k2 <- data.frame(v2, row.names = char)
k3 <- data.frame(v3, row.names = char)

k1 <- rownames_to_column(k1, var= "name")
k2 <- rownames_to_column(k2, var= "name")
k3 <- rownames_to_column(k3, var= "name")

full_join(k1, k2) %>%
  full_join(., k3)
#> Joining with `by = join_by(name)`
#> Joining with `by = join_by(name)`
#>   name v1 v2 v3
#> 1    a  1  5  9
#> 2    b  2  6 10
#> 3    c  3  7 11
#> 4    d  4  8 12

^{Created on 2023-04-14 with reprex v2.0.2}

score 0 · Answer 3 · answered Apr 14 '23 at 13:19

solution

dfs <- list(k1_1, k1_2, k1_3, k1_4)

Reduce(f = \(x, y) merge(x, y, by = "Row.names"), lapply(dfs, \(x) cbind(Row.names = rownames(x), x)))

some data

k1_1 <- data.frame(V1 = 1:10, row.names = LETTERS[1:10])
k1_2 <- data.frame(V2 = 2:11, row.names = LETTERS[1:10])
k1_3 <- data.frame(V3 = 3:12, row.names = LETTERS[1:10])
k1_4 <- data.frame(V4 = 4:13, row.names = LETTERS[1:10])

result

   Row.names V1 V2 V3 V4
1          A  1  2  3  4
2          B  2  3  4  5
3          C  3  4  5  6
4          D  4  5  6  7
5          E  5  6  7  8
6          F  6  7  8  9
7          G  7  8  9 10
8          H  8  9 10 11
9          I  9 10 11 12
10         J 10 11 12 13

Merge multiple dataframes while preserving row names

3 Answers3