-1

I have vector/table y which I generated using:

y <- (x/rowSums(x))*100

Expected output

Then, I aimed to output the variable y into excel file (goal is to append multiple files)

write.xlsx(y, file="Test.xlsx", asTable=TRUE, sheetName="Distribution", append=F) #Append =F as this is first sheet

The excel fiel Test.xlsx however has the data in 2 columns

Output excel file

I figured that write.xlsx can't handle vector format, so I used cbind(y) to convert to matrix. While this worked, I lost the column names. Thus, was unsuccessful in retrieving.

I think there is an easy fix but I couldn't find it. So, how can I output a vector table Tinto write.xlsx without losing data?

Thanks

BioProgram
  • 684
  • 2
  • 13
  • 28
  • If we check `?xlsx::write.xlsx` it says under *description*: *Write a **data.frame** to an Excel workbook.*. Try `y <- as.data.frame(y)` and writing that to the .xlsx – dario Mar 16 '20 at 07:13
  • That actually doesnt work. The problem is that write.xlsx writes it as data.frame which changes the structure. – BioProgram Mar 16 '20 at 14:25
  • It would be way easier to help you if you added a a [minimal reproducible example](https://stackoverflow.com/questions/5963269/how-to-make-a-great-r-reproducible-example/5963610#5963610) and gave some more information than just : *does not work*... otherwise everybody has to guess what your actual problem is. – dario Mar 16 '20 at 14:28
  • What type of object is `x`? – Todd Burus Mar 16 '20 at 14:37

1 Answers1

0

You should be able to get what you want by to adding the argument row.names=F, i.e.

write.xlsx(y, file="Test.xlsx", asTable=TRUE, 
           sheetName="Distribution", append=F, row.names=F)

Note, you may need to remove the asTable argument. My version of write.xlsx threw an error with its inclusion.

Todd Burus
  • 963
  • 1
  • 6
  • 20
  • hmm I tried this now. row.names=F actually is deleting the row.names, which I want to keep. It doesn't fix the problem. Just tried it. Agree with removing asTable though. – BioProgram Mar 16 '20 at 03:21
  • I'm not sure I understand then. You want to remove the column of 1,2,3,etc, correct? If not, what exactly is it you're trying to remove? Maybe mock up an example of what you hope to see as the end product. – Todd Burus Mar 16 '20 at 03:26
  • I dont ant to remove any columns. I just want the output to be as in the first table above. But instead, the output ends up being as 2 column file. – BioProgram Mar 16 '20 at 04:03
  • Okay, I see now. Does the output file you're getting flatten the entire table into that one column or is that just the first column of data? – Todd Burus Mar 16 '20 at 04:16
  • The entire data – BioProgram Mar 16 '20 at 04:23
  • Okay. I would try converting `y` to a dataframe using `as.data.frame` and seeing what happens. BTW, what was the structure of the original `x`? – Todd Burus Mar 16 '20 at 05:32