CODEWITHSUNDEEP
rxlsx
John Tarr
John Tarr

Reputation: 727

Stop R write.xlsx from turning wide data to long

I need to output data to xlsx, and the XLSX package seemed to be widely used.

However, using it seems to turn wide data to long data. I would like to keep the data wide when it exports to Excel.

library(xlsx)

myTable <- xtabs(mpg~carb+cyl, mtcars)

myTable

write.xlsx(myTable, "myFile.xlsx")

The above code is a minimal example that will demonstrate the problem.

The console will output myTable, as per the below:

> myTable
    cyl
carb     4     6     8
   1 137.9  39.5   0.0
   2 155.4   0.0  68.6
   3   0.0   0.0  48.9
   4   0.0  79.0  78.9
   6   0.0  19.7   0.0
   8   0.0   0.0  15.0

However, the Excel file will have the same data in long format, as per below:

    carb    cyl Freq
1   1   4   137.9
2   2   4   155.4
3   3   4   0
4   4   4   0
5   6   4   0
6   8   4   0
7   1   6   39.5
8   2   6   0
9   3   6   0
10  4   6   79
11  6   6   19.7
12  8   6   0
13  1   8   0
14  2   8   68.6
15  3   8   48.9
16  4   8   78.9
17  6   8   0
18  8   8   15

If anyone could help me understand why this is happening or how to correct it, I'd appreciate it.

Upvotes: 1

Views: 165

Answers (1)

A5C1D2H2I1M1N2O1R2T1
A5C1D2H2I1M1N2O1R2T1

Reputation: 193507

From the "details" section at ?write.xlsx, it mentions:

If x is not a data.frame it will be converted to one.

I presume it does so using as.data.frame.

When a table object is converted to a data.frame using as.data.frame, the original structure of the table is changed into a "long" format, and that's what you are seeing here.

To get around this, use as.data.frame.matrix, like this:

write.xlsx(as.data.frame.matrix(myTable), "myFile.xlsx")

Upvotes: 1

Related Questions