Exporting a variable to Excel in R

Question

I created a correlation of several variables in R.

df <- read_excel("~/R/Track/Cumulative_2023.xlsx")
Rel <- cor(df[, c('Speed', 'Axis', 'Horz', 'Ext', 'Zone', 'Rel')], use="complete.obs")

I am using the code:

W <- write.xlsx(Rel, file = "~/R/Track/correlation.xlsx", sheetName = "Sheet1", colNames = TRUE, rowNames = TRUE)

When I run W I get a spreadsheet that has the number 1 in the 1st row/column. I can't figure it out. If I write.xls using the df variable instead of Rel, it writes it properly.

I should be able to export the Rel variable with no issues right?

I was expecting to export the Rel variable to Excel.

Answer 1

Inferring openxlsx::write.xlsx, it is getting confused by the the class of Rel. Wrap it in as.data.frame(.) and you'll get what you want.

Reprex:

cor(mtcars[,1:3])
#             mpg        cyl       disp
# mpg   1.0000000 -0.8521620 -0.8475514
# cyl  -0.8521620  1.0000000  0.9020329
# disp -0.8475514  0.9020329  1.0000000
openxlsx::write.xlsx(cor(mtcars[,1:3]), "mt.xlsx", colNames=TRUE, rowNames=TRUE)

The fix:

openxlsx::write.xlsx(as.data.frame(cor(mtcars[,1:3])), "mt.xlsx", colNames=TRUE, rowNames=TRUE)