8

I have two identical data frames. Same column and row headings. One has correlations in it and one has p-values. I want to merge these two tables in an interleaved manner, so that the first column is the correlation, the next the p-value, the next the correlation etc.

So if columns for table 1 are A1, A2, A3... and table 2 are B1, B2, B3... I want the end table to be A1, B1, A2, B2, A3, B3....

I've tried cbind but this puts all the correlation columns first then all the p-value columns. No interleaving.

I'm wondering if there's an easy way.

Thanks for any help!

Henrik
  • 65,555
  • 14
  • 143
  • 159
user3801778
  • 97
  • 1
  • 4

4 Answers4

8

You got the first step right, which is cbinding. Let's say your data frames are d1 with n columns A1, A2, ...,An and d1 with n columns B1, B2, ..., Bn. Then d <- cbind(d1, d2) will give you the data frame containing all the information you wanted, and you just need to re-order its columns. This amounts to generating a vector (1, (n+1), 2, (n+2), ..., n, 2n) to index the data frame columns. You can do this as s <- rep(1:n, each = 2) + (0:1) * n. So finally, your desired data frame is d[s].

konvas
  • 14,126
  • 2
  • 40
  • 46
6

Similar to @akrun's answer but with indexes instead of names

#example data
set.seed(1)
d1 <- setNames(as.data.frame(matrix(sample(1:10,20, replace=T),5,5)), paste0("A",1:5))
d2 <- setNames(as.data.frame(matrix(sample(1:10,20, replace=T),5,5)), paste0("B",1:5))

#create indexes for the desired order
x <- order(c(1:ncol(d1), 1:ncol(d2)))
x
#[1]  1  6  2  7  3  8  4  9  5 10

#cbind d1 and d2, interleaving columns with x
d <- cbind(d1, d2)[,x]
flyingfinger
  • 690
  • 12
  • 16
1

Also, if the dimensions are the same,create an index

 rbind(colnames(dataframe1), colnames(dataframe2))

For example:

 set.seed(1)
 d1 <- setNames(as.data.frame(matrix(sample(1:10,20, replace=T),5,5)), paste0("A",1:5))
 d2 <- setNames(as.data.frame(matrix(sample(1:10,20, replace=T),5,5)), paste0("B",1:5))

 d <- cbind(d1,d2)

 indx <- rbind(names(d1),names(d2))

  d[,indx]
 # A1 B1 A2 B2 A3 B3 A4 B4 A5 B5
 #1  3 10  9  4  3  5  5  7  3 10
 #2  4  3 10  1  2  6  8  8  4  3
 #3  6  7  7  4  7  5 10  2  6  7
 #4 10  2  7  9  4  2  4  8 10  2
 #5  3  3  1  4  8  9  8  5  3  3
akrun
  • 874,273
  • 37
  • 540
  • 662
0

I have seen a similar solution to what akrun suggested, using the solution from https://coolbutuseless.github.io/2019/01/29/interleaving-vectors-and-matrices-part-1/ . You can just add the c() function to transform the output of rbind(colnames(),colnames()). Here is the solution:

#example data
set.seed(1)
d1 <- setNames(as.data.frame(matrix(sample(1:10,20, replace=T),5,5)), paste0("A",1:5))
d2 <- setNames(as.data.frame(matrix(sample(1:10,20, replace=T),5,5)), paste0("B",1:5))

indx <- c(rbind(colnames(d1), colnames(d2)))

d <- cbind(d1,d2)

final_data_frame <- d[,indx]
kdanielmorais
  • 1