CODEWITHSUNDEEP
r
Karsten W.
Karsten W.

Reputation: 18500

write csv with two header lines?

Can I export a data.frame to csv with two header lines, one line for the column names, and one line for the comments on the column names? For instance,

d <- data.frame(a=c(1,3,4), b=c(5,6,7))
comment(d$a) <- "MWh"
comment(d$b) <- "%"
write.csv(d, "myfile.csv", ???)

Any hint appreciated, also on alternatives to comment()!

Upvotes: 10

Views: 6002

Answers (3)

RDavey
RDavey

Reputation: 1909

I have some small modifications to the excellent answer provided by @Gavin Simpson.

  • It's not obvious in the OP whether row names are required. I have removed these and amended the header offset

  • using fwrite() from the data.table package instead of write.table() reduces the processing time to 1/10 compared with the base function.

library(data.table)
write.csv3 <- function(data, file, header) {
  opts <- options(useFancyQuotes = FALSE)
  on.exit(options(opts)) 
  h1 <- paste(dQuote(c(header)), collapse = ",")
  h2 <- paste(dQuote(c(names(data))), collapse = ",")
  writeLines(paste(h1, h2, sep = "\n"), file)
  fwrite(data, file, sep = ",", append = TRUE, col.names = FALSE, row.names = FALSE)
}

Upvotes: 2

Chase
Chase

Reputation: 69251

Would rbind()ing your comments to the top of your data.frame be an option before writing out? If you make a new object, you won't impact the structure or class of the existing columns.

d <- data.frame(a=c(1,3,4), b=c(5,6,7))
output <- rbind(c("MHh", "%"), d)
write.csv(output, "output.csv")

Upvotes: 6

Gavin Simpson
Gavin Simpson

Reputation: 174968

If you want to preserve the numeric nature of the data, I don't think we can do it with an easy one-liner (the answer provided by @Chase changes the data type to character), but we can do it via a few manipulations that write names(d) and the two comments out to the file first and then uses write.table() to write the csv data lines, appending to the file we just wrote the names and comments to:

write.csv3 <- function(d, file) {
    opts <- options(useFancyQuotes = FALSE)
    on.exit(options(opts))
    h1 <- paste(dQuote(c("", names(d))), collapse = ",")
    h2 <- paste(dQuote(c("", comment(d$a), comment(d$b))), collapse = ",")
    writeLines(paste(h1, h2, sep = "\n"), file)
    write.table(d, file, sep = ",", append = TRUE, col.names = FALSE)
}

Here is an example:

> d <- data.frame(a=c(1,3,4), b=c(5,6,7))
> comment(d$a) <- "MWh"
> comment(d$b) <- "%"
> d
  a b
1 1 5
2 3 6
3 4 7
> write.csv3(d, file = "myfile.csv")

Which produces the following file:

$ cat myfile.csv 
"","a","b"
"","MWh","%"
"1",1,5
"2",3,6
"3",4,7

compared to that produced by @Chase's Answer:

$ cat output.csv 
"","a","b"
"1","MHh","%"
"2","1","5"
"3","3","6"
"4","4","7"

Between the two, you should have sufficient options.

Upvotes: 11

Related Questions