write.table writes unwanted leading empty column to header when has rownames

Question

check this example:

> a = matrix(1:9, nrow = 3, ncol = 3, dimnames = list(LETTERS[1:3], LETTERS[1:3]))
> a
  A B C
A 1 4 7
B 2 5 8
C 3 6 9

the table displays correctly. There are two different ways of writing it to file...

write.csv(a, 'a.csv') which gives as expected:

"","A","B","C"
"A",1,4,7
"B",2,5,8
"C",3,6,9

and write.table(a, 'a.txt') which screws up

"A" "B" "C"
"A" 1 4 7
"B" 2 5 8
"C" 3 6 9

indeed, an empty tab is missing.... which is a pain in the butt for downstream things. Is this a bug or a feature? Is there a workaround? (other than write.table(cbind(rownames(a), a), 'a.txt', row.names=FALSE)

Cheers, yannick

Marek · Accepted Answer · 2016-07-14T05:21:24.083

152

Citing ?write.table, section CSV files:

By default there is no column name for a column of row names. If col.names = NA and row.names = TRUE a blank column name is added, which is the convention used for CSV files to be read by spreadsheets.

So you must do

write.table(a, 'a.txt', col.names=NA)

and you get

"" "A" "B" "C"
"A" 1 4 7
"B" 2 5 8
"C" 3 6 9

edited Jul 14 '16 at 05:21

answered Mar 19 '10 at 15:45

Marek

49,472
15
99
121

5

@Marek, would it be possible to add a name to the rownames column? I.e., instead of "", add "ID" or something alike? – Dnaiel Nov 22 '13 at 19:22
2

@Dnaiel From what I know you can't. You may bind row names with data and give them names (as like in question). – Marek Dec 02 '13 at 21:02
1

@rusalkaguy Your edit has no point. This "extension" is in original question ("workaround other than") – Marek Jul 14 '16 at 05:24
How would you get the number in each column to line up under the col names? – rrs Feb 02 '17 at 17:14
@rrs You mean fixed width format? Look at [write.fwf from gdata package](https://www.rdocumentation.org/packages/gdata/versions/2.17.0/topics/write.fwf). And ask new question instead of comment. And why you ever need that?! – Marek Feb 02 '17 at 21:36
@Marek The write.fwf packages doesn't work. The column names are spaced differently from the column values. I need this because I'm trying to write a GAMS file using R and TABLE columns need to overlap their header. – rrs Feb 03 '17 at 15:06
@Marek you can use `tibble` to convert rownames to column with a desired ID: `tibble::rownames_to_column(var = "ID")` – Ömer An Oct 24 '19 at 02:03
@ÖmerAn Well, tibble was not avaiable at the time when answer was posted ;) – Marek Oct 24 '19 at 07:55

score 12 · Answer 2 · answered Jul 07 '16 at 21:16

12

A slight modification to @Marek very helpful answer WILL add a header to the rownames column: temporarily add the rownames as the first column in the data.frame, and write that, ignoring the real rownames.

> a = matrix(1:9, nrow = 3, ncol = 3, dimnames = list(LETTERS[1:3], LETTERS[1:3]))
> write.table(data.frame("H"=rownames(a),a),"a.txt", row.names=FALSE)

and you get

"H" "A" "B" "C"
"A" 1 4 7
"B" 2 5 8
"C" 3 6 9

answered Jul 07 '16 at 21:16

rusalkaguy

133
1
6

You should edit Marek's answer to include this, I think. – The SE I loved is dead Jul 07 '16 at 21:22

score 6 · Answer 3 · answered Jan 26 '17 at 19:30

For anyone working in the tidyverse (dplyr, etc.), the rownames_to_column() function from the tibble package can be used to easily convert row.names to a column, e.g.:

library('tibble')
a = as.data.frame(matrix(1:9, nrow=3, ncol=3, 
                  dimnames=list(LETTERS[1:3], LETTERS[1:3])))

a %>% rownames_to_column('my_id')

  my_id A B C
1     A 1 4 7
2     B 2 5 8
3     C 3 6 9

Combining this with the row.names=FALSE option in write.table() results in output with header names for all columns.

score 4 · Answer 4 · answered Apr 08 '18 at 16:15

4

For those who experience the same issue when saving a matrix with write.table() and want to keep the row.names column, there is actually an extremely simple solution:

 write.table(matrix,file="file.csv",quote=F,sep=";", row.names=T
             col.names=c("row_name_col;val1_col","val2_col"))

By doing that you're basically tricking the write.table function into creating a header label for the row.names column. The resulting .csv file would look like this:

row_name_col;val1_col;val2_col
row1;1;4 
row2;2;5 
row3;3;6

answered Apr 08 '18 at 16:15

LeCodex

1,636
14
20

1

I am trying col.names = c("row_name",colnames(matrix)) and get an error saying invalid 'col.names' specification. Any idea what is wrong? c("row_name",colnames(matrix)) gives the correct text. – MichaelE Jan 24 '19 at 19:00
1

`write.table` expect a header of length `ncol(matrix)` and you are giving it one more. I tried the above solution, it does not work, best is to move the rownames as a column like in other solutions – aurelien Jan 22 '20 at 14:24
`you're basically tricking the function` seems to me like it's a work-around for a long standing bug in write.table. I.e. it adds the rownames as data in an extra column but doesn't add a corresponding header name. The results is that the column names are all shifted one place over, so by default you get incorrect header names. – BdR Dec 14 '22 at 11:11

score 0 · Answer 5 · answered May 03 '15 at 18:35

I revised a simple function from @mnel, which adds flexibility by using connections. Here is the function:

my.write <- function(x, file, header, f = write.csv, ...){
# create and open the file connection
datafile <- file(file, open = 'wt')
# close on exit 
on.exit(close(datafile))
# if a header is defined, write it to the file (@CarlWitthoft's suggestion)
if(!missing(header)) {
writeLines(header,con=datafile, sep='\t')
writeLines('', con=datafile, sep='\n')
}
# write the file using the defined function and required addition arguments  
f(x, datafile,...)
}

You can specify the function to be 'write.table', 'write.csv', 'write.delim' etc.

Cheers!

write.table writes unwanted leading empty column to header when has rownames

5 Answers5

Linked

Related