R: Attach multiple data frames consisting of one column in a unique dataframe and export this dataframe as an excel table

Question

I have data frames with variable numbers of rows and only one column that I'd like to combine into a single df and export to Excel.

There is no shared information between the dfs that allows me to perform the classic merging.

Are you aware of any efficient code that makes this operation easy and fast as I have so many dataframe? (see example)

DF_list = ls()

DF_list

[1] "DF_list"                                            "HLTclassic_left_120degree"                         
[3] "HLTclassic_left_180degree"                          "HLTclassic_left_240degree"                         
[5] "HLTclassic_left_300degree"                          "HLTclassic_left_60degree"                          
[7] "HLTclassic_left_Odegree"                            "HLTclassic_left_r120degree"                        
[9] "HLTclassic_left_r180degree"                         "HLTclassic_left_r240degree"                        
[11] "HLTclassic_left_r300degree"                         "HLTclassic_left_r60degree"                         
[13] "HLTclassic_left_rOdegree"                           "HLTclassic_right_120degree"                        
[15] "HLTclassic_right_180degree"                         "HLTclassic_right_240degree"                        
[17] "HLTclassic_right_300degree"                         "HLTclassic_right_60degree"                         
[19] "HLTclassic_right_Odegree"                           "HLTclassic_right_r120degree"                       
[21] "HLTclassic_right_r180degree"                        "HLTclassic_right_r240degree"                       
[23] "HLTclassic_right_r300degree"                        "HLTclassic_right_r60degree"                        
[25] "HLTclassic_right_rOdegree"                          "HLTcompatible_lefthemispace_lefthand_0degree"      
[27] "HLTcompatible_lefthemispace_lefthand_120degree"     "HLTcompatible_lefthemispace_lefthand_180degree"    
[29] "HLTcompatible_lefthemispace_lefthand_240degree"     "HLTcompatible_lefthemispace_lefthand_300degree"    
[31] "HLTcompatible_lefthemispace_lefthand_60degree"      "HLTcompatible_lefthemispace_righthand_0degree"     
[33] "HLTcompatible_lefthemispace_righthand_120degree"    "HLTcompatible_lefthemispace_righthand_180degree"   
[35] "HLTcompatible_lefthemispace_righthand_240degree"    "HLTcompatible_lefthemispace_righthand_300degree"   
[37] "HLTcompatible_lefthemispace_righthand_60degree"     "HLTcompatible_righthemispace_lefthand_0degree"     
[39] "HLTcompatible_righthemispace_lefthand_120degree"    "HLTcompatible_righthemispace_lefthand_180degree"   
[41] "HLTcompatible_righthemispace_lefthand_240degree"    "HLTcompatible_righthemispace_lefthand_300degree"   
[43] "HLTcompatible_righthemispace_lefthand_60degree"     "HLTcompatible_righthemispace_righthand_0degree"    
[45] "HLTcompatible_righthemispace_righthand_120degree"   "HLTcompatible_righthemispace_righthand_180degree"  
[47] "HLTcompatible_righthemispace_righthand_240degree"   "HLTcompatible_righthemispace_righthand_300degree"  
[49] "HLTcompatible_righthemispace_righthand_60degree"    "HLTincompatible_lefthemispace_lefthand_0degree"    
[51] "HLTincompatible_lefthemispace_lefthand_120degree"   "HLTincompatible_lefthemispace_lefthand_180degree"  
[53] "HLTincompatible_lefthemispace_lefthand_240degree"   "HLTincompatible_lefthemispace_lefthand_300degree"  
[55] "HLTincompatible_lefthemispace_lefthand_60degree"    "HLTincompatible_lefthemispace_righthand_0degree"   
[57] "HLTincompatible_lefthemispace_righthand_120degree"  "HLTincompatible_lefthemispace_righthand_180degree" 
[59] "HLTincompatible_lefthemispace_righthand_240degree"  "HLTincompatible_lefthemispace_righthand_300degree" 
[61] "HLTincompatible_lefthemispace_righthand_60degree"   "HLTincompatible_righthemispace_lefthand_0degree"   
[63] "HLTincompatible_righthemispace_lefthand_120degree"  "HLTincompatible_righthemispace_lefthand_180degree" 
[65] "HLTincompatible_righthemispace_lefthand_240degree"  "HLTincompatible_righthemispace_lefthand_300degree" 
[67] "HLTincompatible_righthemispace_lefthand_60degree"   "HLTincompatible_righthemispace_righthand_0degree"  
[69] "HLTincompatible_righthemispace_righthand_120degree" "HLTincompatible_righthemispace_righthand_180degree"
[71] "HLTincompatible_righthemispace_righthand_240degree" "HLTincompatible_righthemispace_righthand_300degree"
[73] "HLTincompatible_righthemispace_righthand_60degree"  "Poffenbersession1_lefthemispace_lefthand"          
[75] "Poffenbersession1_lefthemispace_righthand"          "Poffenbersession1_righthemispace_righthand"        
[77] "Poffenbersession1_rightthemispace_lefthand"         "Poffenbersession2_lefthemispace_lefthand"          
[79] "Poffenbersession2_lefthemispace_righthand"          "Poffenbersession2_righthemispace_righthand"        
[81] "Poffenbersession2_rightthemispace_lefthand"         "SNARC_compatible_lefthand_RTs"                     
[83] "SNARC_compatible_righthand_RTs"                     "SNARC_incompatible_lefthand_RTs"                   
[85] "SNARC_incompatible_righthand_RTs"                   "colordice_predictability_lefthand_blu"             
[87] "colordice_predictability_lefthand_red"              "colordice_predictability_lefthand_white"           
[89] "colordice_predictability_righthand_blu"             "colordice_predictability_righthand_red"            
[91] "colordice_predictability_righthand_white"

Thanks!

Try to make a minimal [reproducible example](http://stackoverflow.com/questions/5963269/how-to-make-a-great-r-reproducible-example) to make your problem more clear. Show sample input and make it clear what the desired output would be for that input. — MrFlick, Dec 21 '16 at 16:26

score 0 · Accepted Answer · edited May 23 '17 at 11:53

Combining Columns

new_df <- cbind.data.frame(df1[,1],df2[,3])

Use cbind to combine columns. The example above will combine the 1^st column of df1 and the 3^rd column of df2 into a new data frame.

Combining Columns of Differing Lengths:

Update: based on your comment, you seem to want to combine df's with differing numbers of rows, therefore you can use rbind.fill from plyr to fill in missing rows with NA (Source):

library(plyr)
combined <- rbind.fill(df1[,1], df2[,3])

However, you may have to use a custom cbind function (Source):

cbind.fill <- function(...){
    nm <- list(...) 
    nm <- lapply(nm, as.matrix)
    n <- max(sapply(nm, nrow)) 
    do.call(cbind, lapply(nm, function (x) 
        rbind(x, matrix(, n-nrow(x), ncol(x))))) 
}

Exporting to Excel

library(xlsx)
write.xlsx(new_df, "c:/myspreadsheet.xlsx")

Example

As of this writing you have not produced a dput so I will give an example that should work:

Here's a dput of the data I'm using (copy this and run it in R to have the same data to run the example with):

df <- structure(list(id = c(10, 14, 17, 18), Gender = structure(c(1L, 1L, 2L, 1L), .Label = c("F", "M"), class = "factor"), Col_Cold_1 = structure(c(4L, 2L, 1L, 3L), .Label = c("", "Bump", "muscle", "pain"), class = "factor"),     Col_Cold_2 = structure(c(4L, 2L, 3L, 1L), .Label = c("",     "NA", "pain", "sleep"), class = "factor"), Col_Cold_3 = structure(c(1L,     3L, 2L, 4L), .Label = c("NA", "hemaloma", "muscle", "pain"    ), class = "factor"), Col_Hot_1 = structure(c(4L, 3L, 2L,     1L), .Label = c("", "Callus", "NA", "infection"), class = "factor"),     Col_Hot_2 = structure(c(2L, 3L, 1L, 3L), .Label = c("infection",     "medication", "twitching"), class = "factor"), Col_Hot_3 = structure(c(4L,     2L, 1L, 3L), .Label = c("", "flutter", "medication", "walking"    ), class = "factor")), .Names = c("id", "Gender", "Col_Cold_1", "Col_Cold_2", "Col_Cold_3", "Col_Hot_1", "Col_Hot_2", "Col_Hot_3"), row.names = c(NA, 4L), class = "data.frame")

Here's what it looks like:

  id Gender Col_Cold_1 Col_Cold_2 Col_Cold_3 Col_Hot_1  Col_Hot_2  Col_Hot_3
1 10      F       pain      sleep         NA infection medication    walking
2 14      F       Bump         NA     muscle        NA  twitching    flutter
3 17      M                  pain   hemaloma    Callus  infection           
4 18      F     muscle                  pain            twitching medication

So let's say I want to combine id and Gender:

# first I'm going to separate the data
a<-as.data.frame(df[,1])
b<-as.data.frame(df[,2])

# > a
#   df[, 1]
# 1      10
# 2      14
# 3      17
# 4      18

#>  b
#   df[, 2]
# 1       F
# 2       F
# 3       M
# 4       F

# combining the data into a new data frame
d <- cbind.data.frame(a,b)

# > d
#   df[, 1] df[, 2]
# 1      10       F
# 2      14       F
# 3      17       M
# 4      18       F

# exporting to excel
install.packages("xlsx")
library(xlsx)
write.xlsx(d, "c:/myspreadsheet.xlsx")

This is the error message that I get when I run this code: new_df <- cbind.data.frame(colordice_predictability_lefthand_blu, colordice_predictability_lefthand_red) Error in data.frame(..., check.names = FALSE) : arguments imply differing number of rows: 17, 18. thing is that I have so many dataframes with so many different numbers of rows. I would need some code that makes this automatically. — Gianluca, Dec 21 '16 at 20:38
I updated the answer to address columns of differing lengths. This is untested though. If it gives an error, try Google before posting another comment. — Travis Heeter, Dec 21 '16 at 21:06
Thank you so much, the function works pretty well. The only problem is that I have to type in the name of all the dataframes that are contained in my global environment and they are 95! I have tried: DF_list = ls() combine <- cbind.fill(DF_list) but it doens't work as if I would manually type in the name of each dataframe. — Gianluca, Dec 21 '16 at 21:56
Again, I just googled "list all data frames R" and found this: `dfs <- Filter(function(x) is(x, "data.frame"), mget(ls()))` ([Source](http://stackoverflow.com/a/25510018/1152809)) — Travis Heeter, Dec 22 '16 at 15:38

R: Attach multiple data frames consisting of one column in a unique dataframe and export this dataframe as an excel table

1 Answers1

Combining Columns

Combining Columns of Differing Lengths:

Exporting to Excel

Example