write.table is not outputting a header for row names

Question

I try to write out a matrix to csv while retaining rownames (c.f. Export matrix in r).

However when I do it using write.table() all the columns get shifted to the left (so the first data column header appears above the rownames column).

"PC1","PC2","PC3","PC4"
"Murder",0.0417043206282872,-0.04482165626967,0.0798906594208106,-0.994921731246978
"Assault",0.995221281426497,-0.0587600278572231,-0.0675697350838042,0.03893829763516
"UrbanPop",0.0463357461197111,0.976857479909889,-0.200546287353865,-0.0581691430589317
"Rape",0.0751555005855469,0.200718066450337,0.974080592182492,0.0723250196376096

I tried the following (to manually add an extra column):

merged.pca <- prcomp(USArrests)

write.table(merged.pca$rotation, file = "rotation.csv", sep = ",", col.names = c("rowname",colnames(merged.pca$rotation)))

Unfortunately this fails with:

Error in write.table(merged.pca$rotation, file = "rotation.csv", sep = ",",  : 
  invalid 'col.names' specification

TBH I have no clue what this error means. Is it something about the argument being a list and not a vector?

Pls provide us with some sample data. I guess you want to use `paste()` for you new col.names rather than `c()`, — vaettchen, Jun 22 '13 at 17:08
Why would I want paste()? I'm just trying to prepend a column in front of all the regular ones (PC1, PC2, ..). The output is what you get from prcomp, you can just put any valid matrix there, eg USArrests — Jakub Bochenski, Jun 22 '13 at 18:24
OK, I've added the USArrests into the code, and also sample output — Jakub Bochenski, Jun 22 '13 at 18:28

score 11 · Accepted Answer · answered Jun 23 '13 at 12:14

11

Per the help for write.table, you want to specify col.names=NA:

write.table(merged.pca$rotation, file="rotation.csv", col.names=NA, sep=",")

Yes, I think it's a bit silly too. Note that write.csv will do this for you automatically.

answered Jun 23 '13 at 12:14

Hong Ooi

56,353
13
134
187

I must be going blind - somehow I missed this in the dosc. I accept this one since it's an exact anwser to my question. – Jakub Bochenski Jun 23 '13 at 12:45
4

@Hong Ooi Is it possible to add a name to the rownames column instead of the blank space? I.e., call it "ID" instead of blank... – Dnaiel Nov 22 '13 at 19:25

score 4 · Answer 2 · answered Jun 22 '13 at 18:18

4

If all else fails, you could make the rownames as column, e. g.

res <- transform(merged.pca$rotation, rowname=rownames(merged.pca$rotation))
write.table(res, "rotation.csv", row.names=FALSE)

answered Jun 22 '13 at 18:18

Karsten W.

17,826
11
69
103

+1 for workable solution. Still it seems there should be something more straightforward. Actually I expected I was just missing something/being stupid – Jakub Bochenski Jun 22 '13 at 18:30

score 1 · Answer 3 · answered Jun 23 '13 at 12:08

Not sure whether I completely understand but my shot is:

You save your matrix simply with

write.csv( merged.pca$rotation, file = "rotation.csv", row.names = TRUE )

You load it (as a data.frame!) with

x <- read.csv( "rotation.csv" )

and bring it back to the previous format with

row.names( x ) <- x[,1]
x <- as.matrix( x[-1] )

Is that what you wanted?

score 0 · Answer 4 · answered Oct 31 '13 at 19:14

This solution saves the column structure (ie., for Excel) and names:

To write a file in MacRoman for simple use in Mac Excel 2004/8

write.csv(x, file = "foo.csv", fileEncoding = "macroman")

or for Windows Excel 2007/10

write.csv(x, file = "foo.csv", fileEncoding = "UTF-16LE")

write.table is not outputting a header for row names

4 Answers4

To write a file in MacRoman for simple use in Mac Excel 2004/8

or for Windows Excel 2007/10