CODEWITHSUNDEEP
r
Bagoes Heikhal
Bagoes Heikhal

Reputation: 91

R function write.xlsx only convert 1 data

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

Upvotes: 3

Views: 875

Answers (3)

Len Greski
Len Greski

Reputation: 10845

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

Upvotes: 3

josep maria porr&#224;
josep maria porr&#224;

Reputation: 1388

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

Upvotes: 0

Ray
Ray

Reputation: 2268

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

Upvotes: 0

Related Questions