Converting mixed times into 24 hour format-CodePudding

I currently have a dataset with multiple different time formats(AM/PM, numeric, 24hr format) and I'm trying to turn them all into 24hr format. Is there a way to standardize mixed format columns?

Current sample data

time
12:30 PM
03:00 PM
0.961469907
0.913622685
0.911423611
09:10 AM
18:00

Desired output

new_time
12:30:00
15:00:00
23:04:31 
21:55:37 
21:52:27
09:10:00
18:00:00

I know how to do them all individually(an example below), but is there a way to do it all in one go because I have a large amount of data and can't go line by line?

#for numeric time
> library(chron)
> x <- c(0.961469907, 0.913622685, 0.911423611)
> times(x)
[1] 23:04:31 21:55:37 21:52:27

CodePudding user response：

The decimal times are a pain but we can parse them first, feed them back as a character then use lubridate's parse_date_time to do them all at once

library(tidyverse)
library(chron)

# Create reproducible dataframe
df <-
  tibble::tibble(
    time = c(
      "12:30 PM", 
      "03:00 PM", 
      0.961469907, 
      0.913622685, 
      0.911423611, 
      "09:10 AM", 
      "18:00")
  )

# Parse times
df <- 
  df %>% 
  dplyr::mutate(
    time_chron = chron::times(as.numeric(time)),
    time_chron = if_else(
      is.na(time_chron), 
      time, 
      as.character(time_chron)),
    time_clean = lubridate::parse_date_time(
      x = time_chron,
      orders = c(
        "%I:%M %p", # HH:MM AM/PM 12 hour format
        "%H:%M:%S", # HH:MM:SS 24 hour format
        "%H:%M")),  # HH:MM 24 hour format
    time_clean = hms::as_hms(time_clean)) %>% 
  select(-time_chron)

Which gives us

> df 
# A tibble: 7 × 2
  time        time_clean
  <chr>       <time>    
1 12:30 PM    12:30:00  
2 03:00 PM    15:00:00  
3 0.961469907 23:04:31  
4 0.913622685 21:55:37  
5 0.911423611 21:52:27  
6 09:10 AM    09:10:00  
7 18:00       18:00:00