将start_time/end_time转为POSIXlt对象后出现NA值的求助

我想给all_trips数据框添加秒级骑行时长，需要用difftime()函数，得先把start_time和end_time列转成POSIXlt对象再计算时间差，但转换后这两列全是NA值。

尝试的代码 #

all_trips$start_time <- as.POSIXlt(all_trips$start_time, format = "%m/%d/%Y %H:%M", tz = "UTC")
all_trips$end_time <- as.POSIXlt(all_trips$end_time, format = "%m/%d/%Y %H:%M", tz = "UTC")

预期结果 #

start_time: POSIXlt, format: "2023-01-01 12:00:00" "2023-01-02 13:00:00" ...
end_time  : POSIXlt, format: "2023-01-01 12:30:00" "2023-01-02 13:30:00" ...

实际结果 #

start_time: POSIXlt[1:5723606], format: NA NA NA ...
end_time  : POSIXlt[1:5723606], format: NA NA NA ...

数据样例 #

trip_id          bikeid        start_time   end_time from_station_name from_station_id to_station_name to_station_id user_type
  <chr>            <chr>         <chr>        <chr>   <chr>             <chr>           <chr>           <chr>         <chr>  
1 9340B064F0AEE130 electric_bike 7/23/2023 20:xx 7/23/2023 20:xx Kedzie St & 26th St 20204           Public Rack - 63rd St & Kedzie Ave 877           member 
2 D1460EE3CE0D8AF8 classic_bike  7/23/2023 13:xx 7/23/2023 13:xx Western Ave & Taylor St KA15043        Milwaukee Ave & Wood St 13033         member 
3 DF41BE31B895A25E classic_bike  7/23/2023 14:xx 7/23/2023 14:xx Western Ave & Taylor St KA15043        Damen Ave & Taylor St TA13051       member 
4 9624A293749EF703 electric_bike 7/21/2023 8:xx  7/21/2023 8:xx  Racine Ave & Taylor St 13155         Clinton St & Taylor St TA13052       member 
5 2F68A6A4CDB4C99A classic_bike  7/8/2023 15:xx  7/8/2023 15:xx  Clark St & Taylor St TA13091        Montrose Ave & Clark St TA13081       member 
6 9AEE973E6B941A9C classic_bike  7/10/2023 8:xx  7/10/2023 8:xx  Racine Ave & Taylor St 13155         Sangamon St & Taylor St TA13061       member

问题出在你用的格式字符串%m/%d/%Y %H:%M太严格：%H要求小时必须是两位数字（比如08而非8），但你的数据里存在7/10/2023 8…这种一位小时的记录，导致匹配失败返回NA。

给你两种解决办法：

办法1：修正基础R的格式字符串 #

把格式里的%H换成%k，%k可以匹配一位或两位的24小时制小时数，%m和%d本身能兼容一位/两位的月、日数字：

all_trips$start_time <- as.POSIXlt(all_trips$start_time, format = "%m/%d/%Y %k:%M", tz = "UTC")
all_trips$end_time <- as.POSIXlt(all_trips$end_time, format = "%m/%d/%Y %k:%M", tz = "UTC")

办法2：用lubridate包（更省心） #

lubridate包的mdy_hm函数能自动识别月/日/年+小时:分钟的格式，不用纠结格式字符串的细节：
首先安装并加载包：

install.packages("lubridate")
library(lubridate)

然后转换时间列：

all_trips$start_time <- mdy_hm(all_trips$start_time, tz = "UTC")
all_trips$end_time <- mdy_hm(all_trips$end_time, tz = "UTC")

计算秒级骑行时长 #

转换成功后，直接用difftime()计算：

all_trips$ride_duration <- difftime(all_trips$end_time, all_trips$start_time, units = "secs")

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