How to quote just a column with write.csv/table in R?

Question

I have this result with my data after a write.csv in R:

Last_Name,Sales,Country,Quarter
Smith,$16,753.00 ,UK,Qtr 3
Johnson,$14,808.00 ,USA,Qtr 4
Williams,$10,644.00 ,UK,Qtr 2

and I want this result (which is the original format of my data):

Last_Name,Sales,Country,Quarter
Smith,"$16,753.00 ",UK,Qtr 3
Johnson,"$14,808.00 ",USA,Qtr 4
Williams,"$10,644.00 ",UK,Qtr 2

because obviously I have some problems with amounts!

but I don't want :

"Last_Name","Sales","Country","Quarter"
"Smith,"$16,753.00 ","UK","Qtr 3"
"Johnson","$14,808.00 ","USA","Qtr 4"
"Williams","$10,644.00 ","UK","Qtr 2"

Any ideas?

Try with `df1$Sales <- shQuote(df1$Sales)` and then write the csv with `quote = FALSE` — akrun, Apr 18 '18 at 08:08
`df1$Sales <- shQuote(df1$Sales)` doesn't work because it adds simple quotes and I need double quotes. — YannickN.fr, Apr 18 '18 at 09:37

Tim Biegeleisen · Accepted Answer · 2018-04-18T09:07:34.740

2

Try quoting the sales column by itself:

df$Sales <- paste0("\"", df$Sales, "\"")

Then call write.csv without quotes. Or, you may specify which columns you want quoted in your call to write.csv:

write.csv(file="out.csv", df, quote=c(2))

edited Apr 18 '18 at 09:07

answered Apr 18 '18 at 08:09

Tim Biegeleisen

502,043
27
286
360

`df$Sales <- paste0("\"", df$Sales, "\"")` works fine! but without the final space – YannickN.fr Apr 18 '18 at 09:04
`write.csv(file="out.csv", df, quote=c(2))` doesn't work properly because it also add quote to the header line : – YannickN.fr Apr 18 '18 at 09:08
1

I mean the whole header line. – YannickN.fr Apr 18 '18 at 09:16

MichaelChirico · Answer 2 · 2018-04-18T09:05:19.550

This is the default behavior of data.table::fwrite, which only quotes columns as needed (in your case, to disambiguate the internal comma of the Sales field):

library(data.table)
fwrite(y)
# Last_Name,Sales,Country,Quarter
# Smith,"$16,753.00 ",UK,Qtr 3
# Johnson,"$14,808.00 ",USA,Qtr 4
# Williams,"$10,644.00 ",UK,Qtr 2

I'm just writing to stdout for convenience, of course you can specify an output file as the second argument (file). You can also control this behavior with the quote argument; "auto", works as follows (from ?fwrite):

When "auto", character fields, factor fields and column names will only be surrounded by double quotes when they need to be; i.e., when the field contains the separator sep, a line ending \n, the double quote itself or (when list columns are present) sep2[2] (see sep2 below).

As a bonus, of course, fwrite is way (can be upwards of 100x) faster than write.csv.

**Amazing**, it works fine!!! It solved my problem just with `quote="auto"`. I guess now that my initial data has probably been produced with `fwrite`. — YannickN.fr, Apr 18 '18 at 10:10
It's probably going to be helpful for my work and I hope that it will be widely used. **Thank you!** — YannickN.fr, Apr 18 '18 at 10:31

How to quote just a column with write.csv/table in R?

2 Answers2