R语言处理logbook.csv：日期列清洗与代码错误修复求助

我正在处理用于全球用户燃油使用分析的logbook.csv数据集，核心任务是清洗date_fueled列——该列包含格式为「Apr 12 2020」的日期值，同时混杂带逗号的非日期值（如「Cooling System, Heating System, Lights, Spark Plugs」）。尝试用lubridate、parsedate、dplyr和readr清洗时，要么报错，要么所有日期都转为NA。重启RStudio后导入数据集出现警告：

library(readr)
logbook <- read_csv("C:/Users/theet/Downloads/logbook.csv")
Rows: 1174870 Columns: 9                                                                                                
── Column specification ─────────────────────────────────────────────────────────────
Delimiter: ","
chr (5): date_fueled, date_captured, cost_per_gallon, total_spent, user_url
dbl (3): gallons, mpg, miles
num (1): odometer

ℹ Use `spec()` to retrieve the full column specification for this data.
ℹ Specify the column types or set `show_col_types = FALSE` to quiet this message.
Warning message:
One or more parsing issues, call `problems()` on your data frame for details, e.g.:
  dat <- vroom(...)
  problems(dat) 
> View(logbook)

运行problems(logbook)得到解析错误：

problems(logbook)
# A tibble: 398 × 5
     row   col expected actual     file                                
   <int> <int> <chr>    <chr>      <chr>                               
 1  5409     4 a double 8,583.478  C:/Users/theet/Downloads/logbook.csv
 2  5790     8 a double 1,182.5    C:/Users/theet/Downloads/logbook.csv
 3  9681     8 a double 1,888.2    C:/Users/theet/Downloads/logbook.csv
 4 12023     4 a double 10,738.000 C:/Users/theet/Downloads/logbook.csv
 5 12140     7 a double 1,049.2    C:/Users/theet/Downloads/logbook.csv
 6 12140     8 a double 2,713.3    C:/Users/theet/Downloads/logbook.csv
 7 13609     8 a double 132,388.0  C:/Users/theet/Downloads/logbook.csv
 8 16234     4 a double 2,817.502  C:/Users/theet/Downloads/logbook.csv
 9 20879     4 a double 16,378.667 C:/Users/theet/Downloads/logbook.csv
10 26262     8 a double 49,725.2   C:/Users/theet/Downloads/logbook.csv
# ℹ 388 more rows
# ℹ Use `print(n = ...)` to see more rows

我用lubridate写的代码如下，运行后报错：

library(parsedate)
library(lubridate)
library(dplyr)
library(readr)

logbook2 <- read_csv("C:/Users/theet/Downloads/logbook.csv")

# Convert date_fueled to actual date objects
logbook2  <- logbook2 %>% 
  mutate(date_fueled = as.Date(date_fueled, format = "%b %d %Y")

# Replace NA values in date_fueled with NA

logbook2 <- logbook2 %>% 
  mutate(date_fueled = ifelse(is.na(date_fueled), NA, date_fueled))

head(logbook2)

报错信息：

Error: unexpected symbol in:
"#Replace NA values in date_fueled with NA
logbook2"

1. 修复直接语法错误 #

报错原因是**mutate行末尾缺少闭合括号**，导致代码语法中断，后续的logbook2被识别为意外符号。修正后这部分代码应为：

logbook2  <- logbook2 %>% 
  mutate(date_fueled = as.Date(date_fueled, format = "%b %d %Y"))

2. 移除冗余代码 #

你写的ifelse(is.na(date_fueled), NA, date_fueled)完全没必要——as.Date解析失败时本来就会返回NA，这行代码没有任何实际作用，直接删掉即可。

3. 优化日期解析逻辑 #

用as.Date硬指定格式会导致非日期值全部转为NA，且如果日期格式存在变体（比如单数字日期「Apr 9 2020」），%d可能解析失败。推荐用lubridate的mdy()函数，它能自动识别「月-日-年」格式的各种变体，同时保留非日期值为NA：

logbook2  <- logbook2 %>% 
  mutate(date_fueled = mdy(date_fueled))

4. 处理数值列的解析警告 #

从problems()结果来看，部分数值列（如gallons、odometer）因为包含千位分隔符,导致解析失败，有两种处理方式：

方法1：导入时指定列类型 #

用col_number()自动处理千位分隔符：

logbook2 <- read_csv(
  "C:/Users/theet/Downloads/logbook.csv",
  col_types = cols(
    gallons = col_number(),
    odometer = col_number(),
    mpg = col_number(),
    miles = col_number()
  )
)

方法2：导入后清洗数值列 #

如果已经导入数据，手动替换逗号并转换类型：

logbook2 <- logbook2 %>%
  mutate(
    across(c(gallons, odometer, mpg, miles), ~ as.numeric(gsub(",", "", .x)))
  )

5. 处理非日期值 #

date_fueled列中的非日期值（如系统部件列表），可根据需求选择：

• 保留为NA（默认行为）
• 标记为特定值（如"non-date"）：

logbook2 <- logbook2 %>%
  mutate(
    date_fueled = case_when(
      is.na(mdy(date_fueled)) ~ "non-date",
      TRUE ~ as.character(mdy(date_fueled))
    )
  )
# 若要保留日期类型，仅保留有效日期，其余转为NA
logbook2 <- logbook2 %>%
  mutate(date_fueled = mdy(date_fueled))

library(lubridate)
library(dplyr)
library(readr)

# 导入数据并处理数值列的千位分隔符
logbook2 <- read_csv(
  "C:/Users/theet/Downloads/logbook.csv",
  col_types = cols(
    gallons = col_number(),
    odometer = col_number(),
    mpg = col_number(),
    miles = col_number(),
    .default = col_character()
  )
)

# 清洗date_fueled列，转换为日期类型，无效值转为NA
logbook2 <- logbook2 %>%
  mutate(date_fueled = mdy(date_fueled))

# 查看结果
head(logbook2)

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