0

I'm trying to understand why part of my appended list is getting chopped off when exporting to excel. I can separate a dataframe by a grouping variable into separate lists:

data(iris)
split_tibble <- function(tibble, col = 'col') tibble %>% split(., .[, col])
spliris = split_tibble(iris,'Species') #creates list for each species having all variables

I have a separate list that looks like this:

mylist = list(cbind(col1 = c("val1","val2","val3","val4"),col2 = c("A","B","C","D")))
names(mylist) = "Table"

And I combine them into one list:

newlist = c(mylist,spliris) #looks correct so far

And I write out to an excel wb

#Create workbook with a sheet for each list element
library(openxlsx)
wb <- createWorkbook()
lapply(seq_along(newlist), function(i){
  addWorksheet(wb=wb, sheetName = names(newlist[i]))
  writeData(wb, sheet = i, newlist[[i]][-length(newlist[[i]])])
})

But when I save the workbook, the first sheet "Table" is incomplete and the two columns are just a single column.

Why does this happen? If I do not append the lists together and just write out the iris-lists it works perfectly:

#This works
wb <- createWorkbook()
    lapply(seq_along(spliris), function(i){
      addWorksheet(wb=wb, sheetName = names(spliris[i]))
      writeData(wb, sheet = i, spliris[[i]][-length(spliris[[i]])])
    })
Jonni
  • 804
  • 5
  • 16
  • I think it has to do with `mylist[[1]]` being of class `matrix`. Try and cast it as a `data.frame` object. – Justin Landis Dec 10 '20 at 21:49
  • 1
    Also, `length` behaves differently on matrix objects than data.frame objects. In reference to `[-length(newlist[[i]])]` I would be sure you want to always remove the last collumn. – Justin Landis Dec 10 '20 at 21:51
  • Ah, yes! the [-length... was the issue! The data.frame approach actually split up col1 and col2 to two separate sheets. But leaving it as a list, and removing the eliminate last column fixed it. The last column was always the grouping column so work when dealing with lists originating from same dataframe. Thanks for the insight! – Jonni Dec 10 '20 at 22:38

0 Answers0