R语言使用read.csv()读取csv文件数据被合并为单列的解决方法

问题说明 #

待处理CSV文件结构如下，文件前2行存在前置空行/表头偏移，时间字段因包含逗号被双引号包裹：

,,,,,,,,
,,,,a,b,c,d,e
,,,"01.01.2022, 00:00 - 01:00",82.7,57.98,0.0,0.0,0.0
,,,"01.01.2022, 01:00 - 02:00",87.6,50.05,15.0,25.570000000000004,383.55000000000007
,,,"01.01.2022, 02:00 - 03:00",87.6,41.33,0.0,0.0,0.0

需求为单独导入表头、单独导入数据部分，最终将读取到的表头设置为数据表列名。
初始实现代码如下，运行时出现异常：表头读取可正常识别多列条目，但数据导入时所有内容被归入同一列，未按CSV规则拆分：

file <- "path"

pnl <- read.csv(file, dec = ",")  #, skip = 1, header = TRUE)
headers <- read.csv(file, skip = 1, header = F, nrows = 1, as.is = T)

df  <- read.csv(file, skip = 2, header = F, as.is = T)

#or this
#df <- read.csv(file, skip = 2, header = F, nrow = 1,dec = ".",sep=",", quote = "\"")
colnames(df) <- headers

故障原因 #

• 读取表头和读取数据时，read.csv()的隐式参数不统一：读取数据时未显式指定分隔符、引号解析规则、小数位符号，导致被双引号包裹的时间字段内的逗号被误识别为列分隔符，触发列错位
• 注释的备选读取代码中错误添加了nrow = 1参数，会导致仅读取1行数据，无法完成全量导入
• 初始全量读取测试代码中dec = ","和实际文件数值用.作为小数位的格式不匹配，本身就存在参数错误

解决代码 #

仅使用read.csv()即可完成需求，核心是保证读取表头和读取数据时所有解析参数完全一致，避免列数不匹配：

file <- "your_file_path.csv"

# 读取表头：跳过第1行空行，仅读取第2行内容，显式指定所有解析参数
headers <- read.csv(
  file,
  skip = 1,
  header = FALSE,
  nrows = 1,
  as.is = TRUE,
  sep = ",",
  quote = "\"",
  dec = "."
)

# 读取数据：跳过前2行偏移内容，使用和读表头完全一致的解析参数
df <- read.csv(
  file,
  skip = 2,
  header = FALSE,
  as.is = TRUE,
  sep = ",",
  quote = "\"",
  dec = "."
)

# 为数据框赋值列名
colnames(df) <- as.character(headers[1, ])

# 可选操作：删除开头无意义的全空列
# df <- df[, colSums(is.na(df) | df == "") < nrow(df)]

运行后即可得到列拆分正常、列名匹配正确的数据框。

内容的提问来源于stack exchange，提问作者Priit Mets