Iurie
Iurie

Reputation: 2258

Concatenate data frames horizontally within a loop in R

I use the bellow function for comparisons between group levels with the pairwise.wilcox.test:

pairwise.wilcox <- function(data, x, factor) {
    require(reshape2)
    pw <- data.frame()
    for(i in x){
        pw <- pairwise.wilcox.test(data[ ,i], factor, p.adj = "bonf")
        #pairwise.wilcox.test output is reformatted
        pw <- melt(pw[[3]])
        #the first two columns of the reformatted output are merged
        pw$Var1 = paste(pw$Var2, pw$Var1, sep=" - ")
        #the second column is dropped
        drops <- c("Var2")
        pw <- pw[,!(names(pw) %in% drops)] 
        colnames(pw) <- c("Compared groups", "p-value")
        print(pw)
    }
 }

pairwise.wilcox(tabel2, c(2:4), tabel2$lot)

This is an excerpt of the output:

   Compared groups p-value
1      1StL - 3StL       1
2        1StL - IP       1
3       1StL - ISR       1
4       1StL - ISU       1
5       1StL - StM       1
6      3StL - 3StL      NA
7        3StL - IP       1
...
   Compared groups   p-value
1      1StL - 3StL 1,0000000
2        1StL - IP 0,1092484
3       1StL - ISR 1,0000000
4       1StL - ISU 1,0000000
5       1StL - StM 1,0000000
6      3StL - 3StL        NA
7        3StL - IP 1,0000000
...
   Compared groups p-value
1      1StL - 3StL       1
2        1StL - IP       1
3       1StL - ISR       1
4       1StL - ISU       1
5       1StL - StM       1
6      3StL - 3StL      NA
7        3StL - IP       1
...

What I want is to concatenate horizontally these data frames by the common first column, so the resulted output to looks like this:

   Compared groups p-value   p-value   p-value
1      1StL - 3StL       1 1,0000000       1
2        1StL - IP       1 0,1092484       1
3       1StL - ISR       1 1,0000000       1
4       1StL - ISU       1 1,0000000       1
5       1StL - StM       1 1,0000000       1
6      3StL - 3StL      NA        NA      NA
7        3StL - IP       1 1,0000000       1
...

How to do this? Keep in mind that I have more variables.

My data set (not all data):

structure(list(lot = structure(c(1L, 1L, 1L, 1L, 1L, 1L, 1L, 
1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 
1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 
1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 
1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 
1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 
1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 
1L, 1L, 1L, 1L, 1L, 1L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 
2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 
2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 
2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 
2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 
2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 
2L, 2L, 6L, 6L, 6L, 6L, 6L, 6L, 6L, 6L, 6L, 6L, 6L, 6L, 6L, 6L, 
6L, 6L, 6L, 6L, 6L, 6L, 6L, 6L, 6L, 6L, 6L, 6L, 6L, 6L, 6L, 6L, 
6L, 6L, 6L, 6L, 6L, 6L, 6L, 6L, 6L, 6L, 6L, 6L, 6L, 6L, 6L, 6L, 
6L, 6L, 6L, 6L, 6L, 6L, 6L, 6L, 6L, 6L, 6L, 6L, 6L, 6L, 6L, 6L, 
6L, 6L, 6L, 6L, 6L, 6L, 6L, 6L, 6L, 6L, 6L, 6L, 6L, 6L, 6L, 6L, 
6L, 6L, 6L, 5L, 5L, 5L, 5L, 5L, 5L, 5L, 5L, 5L, 5L, 5L, 5L, 5L, 
5L, 5L, 5L, 5L, 5L, 5L, 5L, 5L, 5L, 5L, 5L, 5L, 5L, 5L, 5L, 5L, 
5L, 5L, 5L, 5L, 5L, 5L, 5L, 5L, 5L, 5L, 5L, 5L, 5L, 5L, 5L, 5L, 
5L, 5L, 5L, 5L, 5L, 5L, 5L, 5L, 5L, 5L, 5L, 5L, 5L, 5L, 5L, 5L, 
5L, 5L, 5L, 5L, 5L, 5L, 5L, 5L, 5L, 5L, 5L, 5L, 5L, 5L, 5L, 5L, 
5L, 5L, 5L, 5L, 5L, 5L, 5L, 5L, 5L, 5L, 5L, 5L, 5L, 5L, 5L, 5L, 
5L, 5L, 5L, 5L, 5L, 5L, 5L, 3L, 3L, 3L, 3L, 3L, 3L, 3L, 3L, 3L, 
3L, 3L, 3L, 3L, 3L, 3L, 3L, 3L, 3L, 3L, 3L, 3L, 3L, 3L, 3L, 3L, 
3L, 3L, 3L, 3L, 3L, 3L, 3L, 3L, 3L, 3L, 3L, 3L, 3L, 3L, 3L, 3L, 
3L, 3L, 3L, 3L, 3L, 3L, 3L, 3L, 3L, 3L, 3L, 3L, 3L, 3L, 3L, 3L, 
3L, 3L, 3L, 3L, 3L, 3L, 3L, 3L, 3L, 3L, 3L, 3L, 3L, 3L, 3L, 3L, 
3L, 3L, 3L, 3L, 3L, 3L, 3L, 3L, 3L, 3L, 3L, 3L, 3L, 3L, 3L, 3L, 
3L, 3L, 3L, 3L, 3L, 3L, 3L, 3L, 3L, 3L, 3L, 4L, 4L, 4L, 4L, 4L, 
4L, 4L, 4L, 4L, 4L, 4L, 4L, 4L, 4L, 4L, 4L, 4L, 4L, 4L, 4L, 4L, 
4L, 4L, 4L, 4L, 4L, 4L, 4L, 4L, 4L, 4L, 4L, 4L, 4L, 4L, 4L, 4L, 
4L, 4L, 4L, 4L, 4L, 4L, 4L, 4L, 4L, 4L, 4L, 4L, 4L, 4L, 4L, 4L, 
4L, 4L, 4L, 4L, 4L, 4L, 4L, 4L, 4L, 4L, 4L, 4L, 4L, 4L, 4L, 4L, 
4L, 4L, 4L, 4L, 4L, 4L, 4L, 4L, 4L, 4L, 4L, 4L, 4L, 4L, 4L, 4L, 
4L, 4L, 4L, 4L, 4L, 4L, 4L, 4L, 4L, 4L, 4L, 4L, 4L, 4L, 4L), .Label = c("1StL", 
"3StL", "IP", "ISR", "ISU", "StM"), class = "factor"), RS.5_1 = c(2, 
3, 3, 2, 2, 2, 2, 3, 1, 1, 2, 3, 3, 2, 2, 1, 3, 2, 3, 2, 3, 1, 
3, 1, 3, 2, 3, 2, 2, 2, 2, 3, 3, 2, 2, 3, 2, 3, 2, 2, 2, 3, 3, 
3, 3, 2, 3, 3, 3, 2, 2, 2, 2, 3, 2, 3, 3, 2, 2, 1, 3, 2, 2, 3, 
2, 2, 3, 3, 3, 3, 3, 2, 2, 2, 2, 1, 3, 3, 3, 3, 3, 3, 1, 1, 3, 
2, 1, 2, 3, 2, 2, 3, 3, 3, 2, 3, 1, 3, 2, 2, 1, 1, 3, 2, 1, 3, 
1, 2, 2, 3, 2, 3, 2, 2, 2, 1, 3, 1, 2, 1, 1, 2, 2, 2, 3, 2, 2, 
2, 1, 3, 3, 1, 1, 2, 2, 2, 3, 1, 2, 2, 2, 1, 2, 2, 1, 3, 3, 3, 
2, 2, 2, 3, 2, 1, 2, 3, 3, 1, 1, 2, 2, 2, 3, 1, 1, 1, 2, 3, 2, 
2, 3, 2, 2, 3, 3, 3, 1, 3, 3, 1, 2, 3, 3, 3, 1, 3, 3, 3, 3, 1, 
3, 1, 3, 1, 2, 3, 3, 2, 3, 3, 1, 3, 2, 3, 3, 3, 2, 3, 3, 3, 3, 
3, 3, 3, 3, 3, 3, 2, 3, 3, 3, 2, 2, 1, 3, 2, 3, 2, 2, 2, 1, 3, 
3, 3, 1, 2, 3, 1, 1, 3, 3, 3, 2, 2, 1, 1, 2, 3, 1, 1, 2, 1, 1, 
2, 3, 3, 3, 1, 2, 3, 2, 2, 2, 2, 3, 3, 1, 1, 3, 3, 2, 2, 2, 1, 
3, 2, 2, 2, 2, 1, 3, 2, 2, 3, 3, 3, 1, 3, 2, 3, 2, 2, 2, 3, 2, 
3, 3, 2, 2, 2, 1, 2, 2, 2, 2, 2, 3, 2, 1, 2, 2, 1, 3, 2, 2, 2, 
3, 3, 2, 1, 2, 1, 2, 3, 3, 2, 3, 3, 2, 1, 3, 1, 2, 2, 2, 3, 1, 
2, 3, 3, 3, 3, 2, 2, 2, 2, 3, 3, 2, 1, 2, 2, 3, 1, 3, 1, 3, 3, 
3, 3, 3, 3, 2, 3, 3, 3, 2, 1, 1, 1, 3, 1, 1, 1, 3, 2, 2, 2, 3, 
3, 1, 3, 1, 2, 1, 1, 3, 3, 3, 2, 1, 1, 1, 2, 3, 1, 3, 3, 3, 2, 
3, 2, 2, 2, 1, 2, 2, 2, 1, 1, 1, 2, 2, 3, 3, 2, 2, 2, 1, 1, 1, 
3, 3, 3, 3, 3, 2, 3, 3, 1, 1, 3, 2, 2, 2, 3, 1, 3, 2, 3, 3, 3, 
2, 2, 3, 1, 2, 3, 3, 2, 3, 2, 3, 3, 3, 2, 2, 1, 3, 2, 3, 3, 2, 
3, 3, 3, 2, 1, 3, 3, 1, 1, 3, 1, 3, 3, 3, 3, 1, 2, 3, 2, 3, 2, 
3, 1, 3, 2, 2, 2, 2, 2, 1, 2, 1, 3, 2, 2, 2, 3, 2, 2, 2, 2, 2, 
1, 2, 3, 3, 3, 3, 2, 2, 3, 1, 2, 3, 2, 1, 3, 2, 3, 3, 3, 3, 3, 
3, 3, 3, 3, 1, 3, 1, 3, 3, 3, 3, 3, 2, 2, 1, 3, 3, 3, 3, 2, 3, 
3, 3, 2, 2, 3, 2, 1, 3, 1, 1, 2, 2, 3, 3, 3, 3, 2, 2, 3, 2, 2, 
2, 2, 2, 1, 3, 1, 2, 3, 1, 3, 3, 2, 2, 3), RS.5_2 = c(2, 2, 3, 
3, 1, 1, 1, 1, 1, 1, 2, 2, 1, 1, 1, 2, 3, 2, 1, 1, 2, 2, 2, 1, 
2, 1, 2, 2, 1, 1, 3, 2, 2, 2, 2, 1, 1, 1, 1, 2, 2, 1, 1, 2, 1, 
1, 3, 3, 1, 1, 2, 2, 1, 1, 3, 2, 1, 2, 1, 1, 2, 2, 1, 1, 1, 2, 
2, 1, 3, 3, 2, 1, 1, 1, 2, 2, 2, 2, 3, 2, 3, 1, 1, 1, 1, 2, 1, 
2, 2, 2, 2, 1, 1, 1, 1, 3, 1, 1, 2, 2, 1, 2, 1, 1, 1, 2, 2, 1, 
1, 3, 2, 1, 1, 1, 1, 1, 2, 1, 3, 2, 2, 1, 1, 1, 2, 2, 1, 1, 2, 
3, 1, 2, 2, 1, 1, 2, 3, 1, 2, 2, 2, 1, 2, 1, 1, 2, 3, 1, 1, 1, 
1, 3, 2, 2, 3, 3, 3, 2, 1, 1, 1, 1, 2, 2, 2, 2, 3, 2, 2, 2, 1, 
1, 1, 3, 1, 2, 2, 1, 3, 1, 2, 1, 2, 3, 1, 1, 3, 1, 2, 3, 1, 2, 
3, 1, 3, 3, 2, 2, 1, 3, 1, 1, 1, 2, 2, 3, 1, 1, 2, 1, 1, 1, 1, 
1, 1, 2, 2, 2, 2, 1, 1, 1, 3, 1, 1, 2, 3, 1, 3, 1, 1, 1, 3, 3, 
1, 1, 1, 1, 1, 2, 2, 3, 2, 1, 1, 3, 2, 3, 1, 2, 2, 2, 2, 2, 3, 
1, 1, 2, 3, 3, 2, 1, 1, 1, 3, 3, 2, 2, 1, 1, 1, 1, 1, 1, 1, 2, 
2, 2, 2, 2, 1, 1, 1, 2, 2, 1, 2, 1, 1, 2, 2, 1, 2, 2, 1, 2, 1, 
1, 1, 1, 1, 1, 2, 1, 1, 2, 1, 1, 1, 2, 2, 2, 3, 2, 1, 1, 1, 2, 
2, 2, 1, 2, 1, 1, 1, 2, 2, 3, 2, 2, 3, 2, 3, 2, 2, 2, 1, 2, 1, 
1, 3, 1, 2, 1, 2, 3, 1, 1, 1, 2, 2, 3, 3, 1, 3, 1, 2, 3, 2, 3, 
2, 1, 1, 1, 3, 1, 3, 2, 2, 2, 3, 1, 1, 1, 1, 1, 3, 3, 3, 3, 1, 
2, 1, 1, 2, 2, 1, 2, 2, 1, 2, 1, 1, 1, 3, 1, 3, 3, 3, 1, 3, 2, 
1, 1, 1, 1, 1, 2, 1, 1, 1, 1, 1, 3, 3, 1, 1, 2, 1, 1, 2, 3, 2, 
3, 3, 3, 2, 2, 1, 1, 2, 1, 3, 2, 3, 3, 2, 3, 1, 3, 3, 3, 2, 2, 
3, 1, 2, 3, 3, 2, 3, 1, 3, 3, 3, 1, 2, 1, 3, 2, 3, 3, 2, 3, 1, 
1, 2, 1, 2, 1, 1, 1, 2, 2, 2, 3, 1, 2, 1, 3, 3, 2, 2, 1, 2, 1, 
3, 3, 2, 2, 3, 2, 2, 2, 1, 3, 3, 3, 1, 1, 1, 1, 2, 1, 3, 1, 2, 
3, 2, 1, 1, 2, 1, 2, 2, 1, 1, 1, 2, 1, 3, 2, 2, 3, 3, 3, 1, 1, 
3, 3, 1, 3, 1, 2, 3, 2, 1, 1, 1, 1, 2, 3, 3, 3, 2, 1, 1, 2, 1, 
2, 1, 1, 3, 1, 2, 2, 2, 1, 1, 3, 1, 3, 2, 1, 1, 1, 1, 1, 1, 3, 
1, 2, 1, 1, 1, 3, 1, 1, 1, 1, 2, 1), RS.5_3 = c(1, 3, 3, 3, 1, 
1, 3, 2, 3, 2, 1, 3, 1, 3, 3, 3, 3, 1, 1, 1, 2, 1, 2, 1, 3, 1, 
2, 1, 3, 2, 1, 1, 3, 1, 2, 2, 1, 2, 3, 3, 1, 1, 2, 3, 3, 2, 3, 
3, 1, 2, 1, 2, 3, 1, 3, 1, 2, 3, 3, 2, 1, 3, 1, 2, 1, 2, 3, 2, 
2, 3, 2, 1, 1, 1, 3, 2, 1, 3, 3, 1, 3, 1, 2, 2, 3, 3, 2, 2, 3, 
2, 2, 2, 2, 1, 2, 3, 3, 2, 2, 1, 2, 3, 1, 1, 3, 1, 1, 1, 1, 3, 
2, 2, 2, 2, 2, 2, 2, 2, 3, 3, 1, 1, 2, 1, 1, 2, 2, 2, 3, 1, 2, 
2, 2, 1, 3, 1, 2, 1, 2, 2, 1, 3, 3, 2, 3, 3, 3, 1, 1, 3, 3, 1, 
1, 2, 2, 3, 1, 1, 2, 1, 2, 3, 2, 2, 2, 2, 3, 2, 1, 2, 2, 2, 2, 
3, 1, 3, 3, 3, 3, 2, 1, 1, 2, 3, 1, 3, 3, 3, 2, 2, 2, 2, 3, 2, 
3, 1, 3, 2, 2, 3, 1, 2, 1, 1, 3, 1, 3, 1, 2, 2, 2, 1, 1, 1, 1, 
3, 3, 1, 1, 3, 2, 3, 2, 1, 3, 3, 3, 2, 1, 3, 3, 3, 3, 3, 3, 1, 
1, 1, 1, 3, 3, 3, 2, 2, 1, 3, 3, 3, 3, 2, 3, 3, 2, 3, 2, 2, 1, 
3, 3, 3, 3, 1, 1, 1, 3, 3, 1, 2, 1, 1, 3, 1, 3, 2, 1, 3, 2, 2, 
3, 2, 1, 1, 1, 1, 1, 1, 1, 2, 1, 3, 2, 2, 2, 1, 1, 1, 3, 1, 3, 
1, 2, 2, 1, 3, 2, 1, 2, 1, 1, 2, 3, 3, 3, 2, 1, 2, 1, 3, 2, 3, 
1, 3, 3, 1, 3, 1, 1, 3, 3, 2, 2, 2, 1, 2, 1, 3, 2, 2, 1, 1, 3, 
2, 1, 1, 2, 3, 2, 2, 3, 1, 2, 3, 3, 1, 3, 2, 2, 1, 3, 3, 3, 3, 
1, 2, 2, 2, 3, 1, 3, 2, 3, 2, 2, 3, 1, 3, 2, 2, 3, 3, 1, 1, 1, 
1, 2, 1, 2, 1, 3, 1, 2, 1, 1, 1, 3, 2, 3, 3, 3, 1, 3, 3, 1, 1, 
1, 1, 2, 2, 1, 1, 1, 1, 1, 3, 3, 1, 1, 2, 1, 1, 3, 3, 3, 3, 3, 
3, 2, 3, 1, 2, 2, 1, 3, 1, 3, 2, 2, 3, 1, 3, 3, 3, 2, 1, 3, 1, 
3, 3, 3, 2, 2, 1, 3, 3, 3, 2, 2, 1, 3, 3, 3, 3, 1, 3, 1, 1, 3, 
1, 1, 1, 2, 1, 3, 2, 2, 3, 1, 1, 2, 3, 3, 3, 1, 2, 3, 1, 3, 2, 
3, 3, 3, 2, 3, 3, 2, 3, 2, 3, 2, 3, 1, 2, 3, 3, 3, 1, 2, 3, 2, 
1, 3, 2, 1, 1, 3, 1, 1, 1, 1, 3, 3, 1, 3, 3, 3, 2, 3, 1, 3, 3, 
3, 1, 3, 2, 3, 2, 1, 1, 2, 1, 3, 3, 3, 3, 3, 3, 3, 1, 2, 3, 1, 
1, 3, 1, 1, 3, 3, 1, 1, 1, 1, 3, 2, 1, 1, 1, 1, 2, 2, 1, 2, 2, 
1, 1, 3, 1, 1, 3, 3, 2, 1, 1)), .Names = c("lot", "RS.5_1", "RS.5_2", 
"RS.5_3"), row.names = c(NA, -582L), class = "data.frame")

Upvotes: 0

Views: 352

Answers (2)

jlhoward
jlhoward

Reputation: 59425

Not sure how much this adds, but you asked how to implement this in a function:

pw.wcx <- function(data,x,factor){
  result <- Reduce(function(x,y)merge(x,y,by="Var1"),
                   lapply(data[x],function(d){
                     pw <- pairwise.wilcox.test(d,factor,p.adj="bonf")[[3]]
                     transform(melt(pw),Var1=paste(Var2,Var1,sep="-"))[-2]}),
                   accumulate=FALSE)
  colnames(result) <- c("Compared Groups",names(data[x]))
  result
}
result <- pw.wcx(tabel2,2:4,tabel2$lot)
head(result)
#   Compared Groups RS.5_1    RS.5_2 RS.5_3
# 1       1StL-3StL      1 1.0000000      1
# 2         1StL-IP      1 0.1092484      1
# 3        1StL-ISR      1 1.0000000      1
# 4        1StL-ISU      1 1.0000000      1
# 5        1StL-StM      1 1.0000000      1
# 6       3StL-3StL     NA        NA     NA

So this generates a list of data frames using:

lapply(data[x],function(d){
  pw <- pairwise.wilcox.test(d,factor,p.adj="bonf")[[3]]
  transform(melt(pw),Var1=paste(Var2,Var1,sep="-"))[-2]})

then combines them using Reduce(...) as in the other answer.

EDIT: Response to OP's comments.

pw.wcx <- function(data,x,factor){
  result <- Reduce(function(x,y)merge(x,y,by="Var1"),
                   lapply(x,function(i){
                     pw <- pairwise.wilcox.test(data[,i],factor,p.adj="bonf")[[3]]
                     transform(melt(pw,value.name=names(data)[i]),Var1=paste(Var2,Var1,sep="-"))[-2]}),
                   accumulate=FALSE)
  colnames(result)[1] <- "Compared Groups"
  result
}

This version will avoid the warnings, but I do not recommend it. This basically uses lapply(...) as a glorified for-loop, which does not take advantage of R's very efficient list processing (e.g., the data frame is subsetted once per iteration using data[,i] instead of just once at the beginning, using data[x].

Upvotes: 2

JasonAizkalns
JasonAizkalns

Reputation: 20483

Your structure statement is not working for me...I think this is the essence of your question?

df1 <- data.frame(group=c('A', 'B', 'C'), result1=c(1, 2, 3))
df2 <- data.frame(group=c('A', 'B', 'C'), result2=c(4, 5, 6))
df3 <- data.frame(group=c('A', 'B', 'C'), result3=c(7, 8, 9))

result.temp <- merge(df1, df2)
result      <- merge(result.temp, df3)

result
#   group result1 result2 result3
# 1     A       1       4       7
# 2     B       2       5       8
# 3     C       3       6       9

Otherwise, if they are always the same length, why not just cbind?

Per @Roland's advice in the comment:

df.list <- list(df1, df2, df3)
Reduce(function(x, y) merge(x, y, all=T, by=c("group")), df.list, accumulate=F)

Upvotes: 1

Related Questions