R语言中小写月份缩写的字符型日期转datetime失败求助

从机构网站下载的数据集里，有两个文件的date列为字符型，格式如"01-oct-2023 00:00:00"（月份为小写缩写），无法转换为datetime类型；其他文件日期为"01/09/2023 00:01:00"格式，可通过dmy_hm()正常转换。尝试多种方法均失败，附代码与数据示例寻求解决帮助。

数据示例与尝试的代码：

library(datapasta)
dpasta(subset_Rain_10.23)

tibble::tribble(
                  ~stno, ~year, ~month, ~day, ~hour, ~minute,                  ~date, ~ispeed, ~speed, ~idir, ~dir, ~imaxgust, ~maxgust, ~idirmgust, ~dirmgust, ~itimemgust, ~timemgust, ~iminspeed, ~minspeed, ~ispeedstdev, ~speedstdev, ~idrybulb, ~drybulb, ~igrasstemp, ~grasstemp, ~it5cm, ~t5cm, ~it10cm, ~t10cm, ~it20cm, ~t20cm, ~it30cm, ~t30cm, ~it50cm, ~t50cm, ~it100cm, ~t100cm, ~irelhum, ~relhum, ~icbl,     ~cbl, ~isoltot, ~soltot, ~irain, ~rain,
                   1275,  2023,     10,    1,     0,       0, "01-oct-2023 00:00:00",       4,     NA,     4,   NA,         4,       NA,          4,        NA,           4,         NA,          4,        NA,            4,          NA,         0,    15.19,           0,      14.25,      0, 13.74,       0,  13.52,       0,  13.24,       0,  13.19,       0,  13.29,        0,   14.17,        0,    91.5,     0,  1006.34,        0,       0,      0,     0,
                   1275,  2023,     10,    1,     0,       1, "01-oct-2023 00:01:00",       4,     NA,     4,   NA,         4,       NA,          4,        NA,           4,         NA,          4,        NA,            4,          NA,         0,    15.19,           0,      14.25,      0, 13.75,       0,  13.51,       0,  13.24,       0,   13.2,       0,  13.29,        0,   14.17,        0,      92,     0, 1006.335,        0,       0,      0,     0,
                   1275,  2023,     10,    1,     0,       2, "01-oct-2023 00:02:00",       4,     NA,     4,   NA,         4,       NA,          4,        NA,           4,         NA,          4,        NA,            4,          NA,         0,    15.19,           0,      14.24,      0, 13.75,       0,  13.51,       0,  13.24,       0,  13.19,       0,  13.29,        0,   14.17,        0,    91.7,     0,  1006.32,        0,       0,      0,     0,
                   1275,  2023,     10,    1,     0,       3, "01-oct-2023 00:03:00",       4,     NA,     4,   NA,         4,       NA,          4,        NA,           4,         NA,          4,        NA,            4,          NA,         0,    15.19,           0,      14.23,      0, 13.75,       0,  13.52,       0,  13.24,       0,   13.2,       0,  13.29,        0,   14.17,        0,    91.6,     0,  1006.33,        0,       0,      0,     0,
                   1275,  2023,     10,    1,     0,       4, "01-oct-2023 00:04:00",       4,     NA,     4,   NA,         4,       NA,          4,        NA,           4,         NA,          4,        NA,            4,          NA,         0,    15.19,           0,      14.23,      0, 13.75,       0,  13.51,       0,  13.25,       0,   13.2,       0,  13.29,        0,   14.17,        0,    91.7,     0, 1006.328,        0,       0,      0,     0,
                   1275,  2023,     10,    1,     0,       5, "01-oct-2023 00:05:00",       4,     NA,     4,   NA,         4,       NA,          4,        NA,           4,         NA,          4,        NA,            4,          NA,         0,     15.2,           0,      14.21,      0, 13.75,       0,  13.52,       0,  13.25,       0,   13.2,       0,  13.29,        0,   14.16,        0,    91.6,     0, 1006.315,        0,       0,      0,     0
                  )

str(subset_Rain_10.23)  # date is chr

# Convert from chr to datetime
library(lubridate)
library(dplyr)
library(parsedate)

df1<-subset_Rain_10.23$date<-as.POSIXct(subset_Rain_10.23$date, format="%d%b%Y:%H:%M:%S")  # returns format NA
str(df1)

df2<-subset_Rain_10.23%>%mutate(subset_Rain_10.23=lubridate::dmy_hms(subset_Rain_10.23)) # returns error in mutate, must be size 6 or 1, not 45

df3<-as.POSIXct(strptime(subset_Rain_10.23$date, format=%d%b%Y %H:%M:%S, tz= "UTC")) # error unexpected special

df4<- parse_date_time(subset_Rain_10.23, "01-oct-2023 00:00:00")  # unknown formats supplied: oct

df5<-parse_date_time(subset_Rain_10.23, "01-oct-2023 00:00:00", %d%b%Y %H:%M:%S, tz= "UTC")  # error unexpected special

方法1：lubridate的dmy_hms()忽略大小写 #

dmy_hms()默认对月份缩写大小写敏感，添加ignore.case=TRUE参数即可识别小写月份：

library(lubridate)
library(dplyr)

# 转换date列
converted_df <- subset_Rain_10.23 %>%
  mutate(date = dmy_hms(date, ignore.case = TRUE))

# 验证结果
str(converted_df$date)

方法2：base R的as.POSIXct()处理 #

方式A：调整locale支持小写月份 #

设置系统locale为英语，让%b能识别大小写月份缩写：

# 设置locale（Windows系统可能用"English_US.1252"）
Sys.setlocale("LC_TIME", "en_US.UTF-8")

# 转换日期，注意格式字符串要完全匹配（日期和时间之间是空格）
subset_Rain_10.23$date <- as.POSIXct(subset_Rain_10.23$date, 
                                     format = "%d-%b-%Y %H:%M:%S", 
                                     tz = "UTC")

方式B：将月份转为大写后再转换 #

用正则表达式把日期字符串中的小写月份转成大写，再用标准格式转换：

# 把3位小写月份替换为大写
subset_Rain_10.23$date <- gsub("([a-z]{3})", "\\U\\1", subset_Rain_10.23$date, perl = TRUE)

# 执行转换
subset_Rain_10.23$date <- as.POSIXct(subset_Rain_10.23$date, 
                                     format = "%d-%b-%Y %H:%M:%S", 
                                     tz = "UTC")

方法3：统一处理两种日期格式 #

如果需要同时兼容"01-oct-2023 00:00:00"和"01/09/2023 00:01:00"两种格式，用parse_date_time()指定多种格式规则：

library(lubridate)

subset_Rain_10.23$date <- parse_date_time(subset_Rain_10.23$date,
                                          orders = c("d-b-Y H:M:S", "d/m/Y H:M"),
                                          tz = "UTC")

之前方法失败的原因 #

• df1：格式字符串错误，日期和时间之间是空格而非冒号，且默认%b不识别小写月份；
• df2：mutate语法错误，应该传入date列而非整个数据集给dmy_hms()；
• df3：格式字符串未加引号，R无法识别为字符型参数；
• df4/df5：parse_date_time()的参数错误，第一个参数应为日期向量，第二个参数是格式规则而非示例日期。

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