在R中转换多列数据类型时遭遇NA强制转换错误的求助

问题描述 #

我正在做一个初学者案例研究，已经在R中导入了相关数据，但在检查多列数据类型时遇到了问题。我想把以下3列从字符格式转换为数值格式：1) started_at 2) ended_at 3) ride_length。最初我成功转换了格式，但收到了NA's by coercion的错误提示。我也尝试修改CSV文件格式后重新导入，但没有效果。

我使用了以下代码：

cols.num <- c("started_at","ended_at","ride_length")
jan_2022[cols.num] <- sapply(jan_2022[cols.num], as.numeric)
sapply(jan_2022,class)
summary(jan_2022)

这段代码确实把数据类型改成了数值型，但出现了NA's by coercion错误。

数据示例 #

structure(list(ride_id = c("C2F7DD78E82EC875", "A6CF8980A652D272", 
"BD0F91DFF741C66D", "CBB80ED419105406", "DDC963BFDDA51EEA"), 
    rideable_type = c("electric_bike", "electric_bike", "classic_bike", 
    "classic_bike", "classic_bike"), started_at = c("1/13/2022 11:59", 
    "1/10/2022 8:41", "1/25/2022 4:53", "1/4/2022 0:18", "1/20/2022 1:31"
    ), ended_at = c("1/13/2022 12:02", "1/10/2022 8:46", "1/25/2022 4:58", 
    "1/4/2022 0:33", "1/20/2022 1:37"), start_station_name = c("Glenwood Ave & Touhy Ave", 
    "Glenwood Ave & Touhy Ave", "Sheffield Ave & Fullerton Ave", 
    "Clark St & Bryn Mawr Ave", "Michigan Ave & Jackson Blvd"
    ), start_station_id = c("525", "525", "TA1306000016", "KA1504000151", 
    "TA1309000002"), end_station_name = c("Clark St & Touhy Ave", 
    "Clark St & Touhy Ave", "Greenview Ave & Fullerton Ave", 
    "Paulina St & Montrose Ave", "State St & Randolph St"), end_station_id = c("RP-007", 
    "RP-007", "TA1307000001", "TA1309000021", "TA1305000029"), 
    start_lat = c(42.0128005, 42.012763, 41.92560188, 41.983593, 
    41.87785), start_lng = c(-87.665906, -87.6659675, -87.65370804, 
    -87.669154, -87.62408), end_lat = c(42.01256012, 42.01256012, 
    41.92533, 41.961507, 41.88462107), end_lng = c(-87.67436712, 
    -87.67436712, -87.6658, -87.671387, -87.62783423), member_casual = c("casual", 
    "casual", "member", "casual", "member"), ride_length = c("0:02:57", 
    "0:04:21", "0:04:21", "0:14:56", "0:06:02"), day_of_week = c(5L, 
    2L, 3L, 3L, 5L)), row.names = c(NA, 5L), class = "data.frame")

问题原因 #

直接用as.numeric()转换日期时间字符串和时长字符串会失败，因为这些字符串不符合数值的格式规则，R无法直接解析，只能强制转换生成NA。需要先把它们转换成对应的日期时间类型或时长类型，再根据需求转为数值（比如时间戳或总秒数）。

解决方案 #

方案一：使用lubridate包（推荐） #

lubridate包专门用于处理日期时间数据，语法简洁易读：

# 安装并加载包
install.packages("lubridate")
library(lubridate)

# 解析日期时间列并转为数值时间戳
jan_2022$started_at <- mdy_hm(jan_2022$started_at)
jan_2022$ended_at <- mdy_hm(jan_2022$ended_at)
jan_2022$started_at_num <- as.numeric(jan_2022$started_at)
jan_2022$ended_at_num <- as.numeric(jan_2022$ended_at)

# 解析时长列并转为总秒数
jan_2022$ride_length <- hms(jan_2022$ride_length)
jan_2022$ride_length_num <- as.numeric(jan_2022$ride_length, units = "secs")

方案二：基础R实现（无需额外包） #

如果不想安装第三方包，可以用基础R函数处理：

# 解析日期时间列
jan_2022$started_at <- strptime(jan_2022$started_at, format = "%m/%d/%Y %H:%M")
jan_2022$ended_at <- strptime(jan_2022$ended_at, format = "%m/%d/%Y %H:%M")
# 转为数值时间戳
jan_2022$started_at_num <- as.numeric(jan_2022$started_at)
jan_2022$ended_at_num <- as.numeric(jan_2022$ended_at)

# 处理时长列：拆分时分秒计算总秒数
jan_2022$ride_length_num <- sapply(strsplit(jan_2022$ride_length, ":"), function(x) {
  as.numeric(x[1])*3600 + as.numeric(x[2])*60 + as.numeric(x[3])
})

验证结果 #

运行以下代码检查转换后的数据类型和统计信息：

# 查看指定列的类型
sapply(jan_2022[, c("started_at_num", "ended_at_num", "ride_length_num")], class)
# 查看数值列的统计摘要
summary(jan_2022[, c("started_at_num", "ended_at_num", "ride_length_num")])

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