I have a tsv that has some fields with some escaped double quotation marks (DQM). Those are fine, but the problem comes when the DQM delimiter comes after one of those and then when the tsv is opened it doesn’t recognize the last DQM and so the following field gets included with the previous.
For example, the line:
"9812" "tt0167609" "tvSeries" "L'homme du "Picardie"" "L'homme du "Picardie"" 0 "1968" "N" "13" "Drama"
when opened in a SS, places the first 3 fields fine. But the fourth field it includes all the way up to 1968 when what it should do is only put in the first "L'homme du "Picardie", the second of the same in the next field, and so on. And so the problem appears to be that it’s not recognizing the " after the ". I tried clicking different options when opening it in SS programs, but nothing fixes it.
Now I’ve found out that I can fix this before opening it in a SS program by replacing "" with """ in a text editor, but I’d like to be able to do it in R when the file is generated.
I’ve tried several combinations of strings, but I just can’t figure it out and I’m hoping someone can point me in the right direction. The following are just some of what I tried.
tv.Subset <- str_replace(tv.Subset, "\""", "\"""") – one of my first attempts, simply escape each character in the string
tv.Subset <- str_replace(tv.Subset, '\""', '\"""') – I wondered if single quotation marks for delimiters might be the trick
tv.Subset <- str_replace(tv.Subset, "\\\"\"", "\\\"\"\"") – I read that you need to do double backslashes to respect both R and regex
Thanks.
5
If I am understanding your question correctly, R is generating .tsv output files that you cannot open in spreadsheet programs due to the escaped double quotes in R’s output.
If so, here is a reproducible example where the third row is your original data:
df <- data.frame(f1=c(1,27,9812,9999),
f2=c('tt12345','tt54321','tt0167609','tt98765'),
f3=rep("tvSeries",4),
f4=c("Some string","Another string","L'homme du "Picardie"","Yet another string"),
f5=c("Here is another string","Another string again","L'homme du "Picardie"","Also a string"),
f6=c(0,0,0,0),
f7=c(1966,1967,1968,1969),
f8=rep("\N",4),
f9=c(11,12,13,14),
f10=rep('Drama',4))
write.table(df,'outfile.tsv')
Viewed in R, your data would look like this:
> print(df,row.names=F)
f1 f2 f3 f4 f5 f6 f7 f8 f9 f10
1 tt12345 tvSeries Some string Here is another string 0 1966 \N 11 Drama
27 tt54321 tvSeries Another string Another string again 0 1967 \N 12 Drama
9812 tt0167609 tvSeries L'homme du "Picardie" L'homme du "Picardie" 0 1968 \N 13 Drama
9999 tt98765 tvSeries Yet another string Also a string 0 1969 \N 14 Drama
And the command write.table(df,'outfile.tsv') produces this output file, which some spreadsheet programs would fail to read:
"f1" "f2" "f3" "f4" "f5" "f6" "f7" "f8" "f9" "f10"
"1" 1 "tt12345" "tvSeries" "Some string" "Here is another string" 0 1966 "N" 11 "Drama"
"2" 27 "tt54321" "tvSeries" "Another string" "Another string again" 0 1967 "N" 12 "Drama"
"3" 9812 "tt0167609" "tvSeries" "L'homme du "Picardie"" "L'homme du "Picardie"" 0 1968 "N" 13 "Drama"
"4" 9999 "tt98765" "tvSeries" "Yet another string" "Also a string" 0 1969 "N" 14 "Drama"
The solution is not to change ” in R like you’ve been trying to, but instead to just not have R surround strings with quotes in your output. That way the only quotes in the output are the quotes that are part of a title, and spreadsheet programs will be able to treat them accordingly.
Here is the command you can use to save your data without the escaped quotes: write.table(df,'outfile.tsv',quote=F,sep='t'). This will do two things:
- quote=F will stop R from putting quotes around strings in your output. Since those quotes aren’t there, it won’t escape the quotes in titles.
- sep=’t’ will make R separate output columns with a tab character rather than a space character (space is the default, and your example looks like it used space). You need sep=’t’ when quote=F so spreadsheet programs can tell spaces within strings are still part of the string and not separators.
Your new output file will look like this:
f1 f2 f3 f4 f5 f6 f7 f8 f9 f10
1 1 tt12345 tvSeries Some string Here is another string 0 1966 N 11 Drama
2 27 tt54321 tvSeries Another string Another string again 0 1967 N 12 Drama
3 9812 tt0167609 tvSeries L'homme du "Picardie" L'homme du "Picardie" 0 1968 N 13 Drama
4 9999 tt98765 tvSeries Yet another string Also a string 0 1969 N 14 Drama
As long as the new output file uses tab characters, most spreadsheet programs will have no problem reading it like this.
1