How to parse time-date (not date-time)-CodePudding

I have a dataframe with a few columns that contain time/ date information. I'm familiar with using lubridate to parse date-time (ie mm/dd/yyyy hh:mm:ss), but this dataframe has date time in reverse order (ie hh:mm:ss mm/dd/yyyy). How do I get this to read as a date/time? The column is currently reading as a character which is useless to me. Below is an example of what my dataset looks like. I can't make the "time_date" column read as a date -time.

df <- tribble(~activity, ~time_date, 
               "run", "15:06:17 03/08/2016", 
               "skip", "09:01:00 03/08/2016")

CodePudding user response：

You should first convert it to a date time with right format and after that you can use strftime with the desired format like this:

datetimes <- as.POSIXct(df$time_date, format = "%H:%M:%S %m/%d/%Y")
df$time_date <- strftime(datetimes, format = "%m/%d/%Y %H:%M:%S")
df
#> # A tibble: 2 × 2
#>   activity time_date          
#>   <chr>    <chr>              
#> 1 run      03/08/2016 15:06:17
#> 2 skip     03/08/2016 09:01:00

^{Created on 2023-01-04 with reprex v2.0.2}

CodePudding user response：

With dplyr and lubridate on character class data.

library(dplyr)
library(lubridate)

df %>% 
  rowwise() %>% 
  mutate(dd = strsplit(time_date, " "), 
         date_time = mdy_hms(paste(unlist(dd)[2], unlist(dd)[1])), 
         dd = NULL) %>%
  ungroup()
# A tibble: 2 × 3
  activity time_date           date_time          
  <chr>    <chr>               <dttm>             
1 run      15:06:17 03/08/2016 2016-03-08 15:06:17
2 skip     09:01:00 03/08/2016 2016-03-08 09:01:00

Alternatively using str_extract

df %>% 
  mutate(date_time = mdy_hms(paste(str_extract(time_date, " \\d /. "), 
                                   str_extract(time_date, "\\d :.  "))))
# A tibble: 2 × 3
  activity time_date           date_time          
  <chr>    <chr>               <dttm>             
1 run      15:06:17 03/08/2016 2016-03-08 15:06:17
2 skip     09:01:00 03/08/2016 2016-03-08 09:01:00

CodePudding user response：

You can use lubridate::parse_date_time() and specify the order as "HMS mdy":

library(dplyr)
library(lubridate)

df %>%
  mutate(date_time = parse_date_time(time_date, "HMS mdy"))

# A tibble: 2 × 3
  activity time_date           date_time          
  <chr>    <chr>               <dttm>             
1 run      15:06:17 03/08/2016 2016-03-08 15:06:17
2 skip     09:01:00 03/08/2016 2016-03-08 09:01:00