As a follow-up to a question I wrote a few days ago, I finally figured out how to copy to the clipboard to paste into other applications (read: Excel).
However, when using the function to copy and paste, the variable column headers are not lining up correctly when pasting.
Data (taken from a Flowing Data example I happened to be looking at):
data <- read.csv("http://datasets.flowingdata.com/post-data.txt")
Copy function:
write.table(file = pipe("pbcopy"), data, sep = "\t")
When loaded in, the data looks like this:
id views comments category
1 5019 148896 28 Artistic Visualization
2 1416 81374 26 Visualization
3 1416 81374 26 Featured
4 3485 80819 37 Featured
5 3485 80819 37 Mapping
6 3485 80819 37 Data Sources
There is a row number without a column variable name (1, 2, 3, 4, ...)
Using the read.table(pipe("pbpaste")) function, it will load back into R fine.
However, when I paste it into Excel, or TextEdit, the column name for the second variable will be in the first variable column name slot, like this:
id views comments category
1 5019 148896 28 Artistic Visualization
2 1416 81374 26 Visualization
3 1416 81374 26 Featured
4 3485 80819 37 Featured
5 3485 80819 37 Mapping
6 3485 80819 37 Data Sources
Which leaves the trailing column without a column name.
Is there a way to ensure the data copied to the clipboard is aligned and labeled correctly?
The row numbers do not have a column name in an R data.frame. They were not in the original dataset but they are put into the output to the clipboard unless you suppress it. The default for that option is set to TRUE but you can override it. If you want such a column as a named column, you need to make it. Try this when sending to excel.
df$rownums <- rownames(df)
edf <- df[ c( length(df), 1:(length(df)-1))] # to get the rownums/rownames first
write.table(file = pipe("pbcopy"), edf, row.names=FALSE, sep = "\t")