rwrite.table

How to add a row of text above the output table when using write.table to copy and paste a data frame?

Suppose we have the following data frame called data, produced by the code immediately beneath:

> data
  ID Period Values
1  1      1      5
2  1      2     10
3  1      3     15
4  2      1     12
5  2      2      0
6  2      3      2
7  3      1      4
8  3      2     -8
9  3      3      3

data <- 
  data.frame(
    ID = (c(1,1,1,2,2,2,3,3,3)),
    Period = as.numeric(c(1, 2, 3, 1, 2, 3, 1, 2, 3)),
    Values = as.numeric(c(5, 10, 15, 12, 0, 2, 4, -8, 3))
  )

Next, we use the below simple code in base R to copy and paste data into an Excel sheet:

write.table(x = data,
            file = "clipboard",
            sep = "\t",
            row.names = FALSE,
            col.names = TRUE
  )

When copy/pasting data into Excel, I'd like a row of text describing the table inserted immediately above the table (such as: Table name is "Data"), as shown in the image below.

How can the above code be modified to insert a text row above the table? In base R preferably?

enter image description here

Solution

  • Clipboard alone

    writeLines(
  c("table name is mtcars",
    capture.output(write.table(mtcars[1:3,], sep = "\t", row.names = FALSE))),
  "clipboard")

    ... and then paste into Excel. I've run into issues in the past when the data has embedding issues (embedded tabs, etc) and perhaps something in the chain (including "me") did not handle all things correctly.

    On windows, one could replace writeLines(.., "clipboard") with writeClipboard, but that function is windows only. On other OSes, one can install the clipr package for clipboard reading/writing.

    Using files

    writeLines("table name is mtcars", con = "somefile.csv")
write.table(mtcars[1:3,], "somefile.csv", row.names = FALSE, append = TRUE, sep = ",")
# Warning in write.table(mtcars[1:3, ], "somefile.csv", row.names = FALSE,  :
#   appending column names to file

    (One cannot use write.csv, since it does not tolerate append=TRUE, complaining attempt to set 'append' ignored.)

    Resulting file:

    table name is mtcars
"mpg","cyl","disp","hp","drat","wt","qsec","vs","am","gear","carb"
21,6,160,110,3.9,2.62,16.46,0,1,4,4
21,6,160,110,3.9,2.875,17.02,0,1,4,4
22.8,4,108,93,3.85,2.32,18.61,1,1,4,1

    It opens in Excel as

    excel snapshot