R中加载CSV文件列未正确分隔的问题求助

问题背景 #

我尝试在R语言中加载CSV文件并转换为DataFrame，使用了以下代码：

read.csv("ASV_file.csv", sep = ",", header = FALSE) # (1)
readr::read_csv("ASV_file.csv", sep = ",", header = FALSE) # (2)

但每行数据未被正确分隔，而是被识别为单个列。使用readLines()读取的前两行数据如下：

[1] "\",\"\"id\"\",\"\"A-1\"\",\"\"A-2\"\",\"\"A-3\"\",\"\"A-4\"\",\"\"A-5\"\",\"\"A-6\"\",\"\"A-7\"\",\"\"A-8\"\",\"\"A-9\"\",\"\"A-10\"\",\"\"A-11\"\",\"\"A-12\"\",\"\"A-13\"\",\"\"A-14\"\",\"\"A-15\"\",\"\"A-16\"\",\"\"A-17\"\",\"\"A-18\"\",\"\"A-19\"\",\"\"A-20\"\",\"\"A-21\"\",\"\"A-22\"\",\"\"A-23\"\",\"\"A-24\"\",\"\"A-25\"\",\"\"A-26\"\",\"\"A-27\"\",\"\"A-28\"\",\"\"A-29\"\",\"\"A-30\"\",\"\"A-31\"\",\"\"Kingdom\"\",\"\"Phylum\"\",\"\"Class\"\",\"\"Order\"\",\"\"Family\"\",\"\"Genus\"\",\"\"Species\"\""
[2] "\"1,\"\"ae82\"\"",0,10,13,16,26,29,36,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,\"\"d__Bacteria\"\",\"\"Bacteroidota\"\",\"\"Bacteroidia\"\",\"\"Bacteroidales\"\",\"\"Bacteroidales_BS\"\",NA,NA\"" 

用Excel打开该CSV文件并按逗号分隔列时，可正常拆分列。文本编辑器打开的文件内容如下：

","\"id\"","\"A-1\"","\"A-2\"","\"A-3\"","\"A-4\"","\"A-5\"","\"A-6\"","\"A-7\"","\"A-8\"","\"A-9\"","\"A-10\"","\"A-11\"","\"A-12\"","\"A-13\"","\"A-14\"","\"A-15\"","\"A-16\"","\"A-17\"","\"A-18\"","\"A-19\"","\"A-20\"","\"A-21\"","\"A-22\"","\"A-23\"","\"A-24\"","\"A-25\"","\"A-26\"","\"A-27\"","\"A-28\"","\"A-29\"","\"A-30\"","\"A-31\"","\"Kingdom\"","\"Phylum\"","\"Class\"","\"Order\"","\"Family\"","\"Genus\"","\"Species\"

"1,\"\"ae826634247e3eeae6757a595938319d\"\",0,10,13,16,26,29,36,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,\"\"d__Bacteria\"\",\"\"Bacteroidota\"\",\"\"Bacteroidia\"\",\"\"Bacteroidales\"\",\"\"Bacteroidales_BS11_gut_group\"\",NA,NA"

请问是否需要将分隔符设置为逗号以外的字符？

问题原因与解决办法 #

不需要更换逗号作为分隔符，问题出在文件中使用了""作为转义的双引号，而R的默认读取规则没有适配这种格式。

基础R read.csv() 解决方案 #

修改参数，指定quote = "\"\""（用转义符包裹两个双引号），同时注意文件第一行是表头，设置header = TRUE：

df <- read.csv("ASV_file.csv", sep = ",", quote = "\"\"", header = TRUE)

readr::read_csv() 解决方案 #

同样需要指定转义引号规则为quote = "\"\""，read_csv默认会自动识别表头，因此可以去掉header = FALSE参数：

library(readr)
df <- read_csv("ASV_file.csv", sep = ",", quote = "\"\"")

补充说明 #

Excel能正确解析是因为它默认支持这种""转义双引号的CSV格式，而R的默认quote参数是"\""（单个双引号），导致把整行当成一个被引号包裹的字段。指定quote = "\"\""后，R就能正确识别被""包裹的字段，同时用逗号分隔其他内容。

内容的提问来源于stack exchange，提问作者S. Jeon