R function write.xlsx only convert 1 data

user_13097756 0
Viewed 247

I have 4 columns and 34 rows of data. I tried to export it into excel with xlsx format using write.xlsx. But when I convert it, the excel file only shows 1 data.

library(openxlsx)
data = scale(DATA2)
write.xlsx(data, "outpu2t.xlsx");

This is my data

enter image description here

and this is the output

enter image description here

r
3 Answers

The key consideration here is that the output of the scale() function is an object of type matrix() when write.xlsx() requires an input of type data.frame(). The following code creates a data frame, uses scale() to scale it, and prints the structure to show that the data frame has bene converted to a matrix().

df <- data.frame(matrix(runif(4 * 34),ncol=4))
str(df)

> df <- data.frame(matrix(runif(4 * 34),ncol=4))
> str(df)
'data.frame':   34 obs. of  4 variables:
 $ X1: num  0.438 0.134 0.671 0.392 0.613 ...
 $ X2: num  0.9 0.793 0.668 0.351 0.275 ...
 $ X3: num  0.201 0.892 0.74 0.788 0.14 ...
 $ X4: num  0.996 0.619 0.492 0.904 0.615 ...

scaledData <- scale(df)
str(scaledData)

> scaledData <- scale(df)
> str(scaledData)
 num [1:34, 1:4] -0.174 -1.386 0.752 -0.36 0.521 ...
 - attr(*, "dimnames")=List of 2
  ..$ : NULL
  ..$ : chr [1:4] "X1" "X2" "X3" "X4"
 - attr(*, "scaled:center")= Named num [1:4] 0.482 0.591 0.508 0.471
  ..- attr(*, "names")= chr [1:4] "X1" "X2" "X3" "X4"
 - attr(*, "scaled:scale")= Named num [1:4] 0.251 0.206 0.306 0.29
  ..- attr(*, "names")= chr [1:4] "X1" "X2" "X3" "X4"

We can solve the problem by casting the output of scale() with data.frame().

The following code generates a 4 x 34 matrix, scales it, and casts to a data.frame() as part of write.xlsx().

aMatrix <- matrix(runif(4 * 34),ncol=4)
library(openxlsx)
write.xlsx(data.frame(scale(aMatrix)),"./data/aSpreadsheet.xlsx")

The resulting spreadsheet looks like this when viewed in Microsoft Excel.

enter image description here

Note that writexl::write_xlsx() will also fail when passed an input of type matrix(), so this is not a tidyverse vs. openxlsx problem.

b <- scale(aMatrix)
write_xlsx(b,"./data/aSpreadsheetWritexl.xlsx")

...generates the following error:

> write_xlsx(b,"./data/aSpreadsheetWritexl.xlsx")
Error in write_xlsx(b, "./data/aSpreadsheetWritexl.xlsx") : 
  Argument x must be a data frame or list of data frames
user_84719310

I am cognisant that you asked this question pointing to the package {openxlsx}. I used this a while back as well and ran into multiple problems. Being biased and leaning towards the {tidyverse} family, there is a cool package that comes from that part of the R/RStudio ecosystem: {writexl}.

If not yet installed: install.packages("writexl")

Then run the following without pain ... and it does not require to install other fancy stuff/dependencies/etc:

library(writexl)

# create a reproducible data set of 34 rows
my_data <- iris[1:34,]

# write-out my_data to the data subfolder in the project - configure as appropriate for your environment
write_xlsx(x = my_data, path = "./data/my_data.xlsx")

This gets you without problems:

enter image description here

user_39201020

The solution is to convert matrix data into a data frame.

data <- as.data.frame(data)

Then,

write.xlsx(data, "outpu2t.xlsx")

will work as expected

user_66790660
Related