0

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.

mrax25
  • 1
  • 2

1 Answers1

2

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)

incorrect object

The fix:

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

excel file, fixed

r2evans
  • 141,215
  • 6
  • 77
  • 149
  • I could not get this to work. FYI, the class of Rel is "matrix" "array". When I run a variable that is filtered off the original df it works. Something like this- A <- df %>% filter(Name == "Matt")) will write properly to excel. But as soon as I run that "Rel" variable with the correlations, it just returns a 1 in the 1st column/row. – mrax25 Mar 02 '23 at 22:40
  • FYI, the class of `class(cor(mtcars[,1:3]))` is _also_ `c("matrix", "array")`. I don't see how `df %>% filter(..)` can work on a matrix, it should fail. Otherwise, there's nothing I can do: the reprex I provided is complete, reproduces your symptom, and is fixed by converting to a frame. We don't have your `"Cumulative_2023.xlsx"`, perhaps you can add the output from `dput(Rel)` into your question and we can see what else is different about your data. – r2evans Mar 03 '23 at 21:39
  • This solved it.... so simple. df1 <- as.data.frame(df). Worked once I converted the Matrix to a Data Frame. Thank you all for the help. – mrax25 Mar 06 '23 at 05:07