Seperating time and date format into two seperate columns.

[Alec_Watson]

Hi there,

I'm very new to r and have been struggling to see what is wrong with my code below which I shamlessly borrowed from another post. Im trying to split the started_at (2020-05-23 21:16:11) column into start_date and start_time columns. time is working ok but my date code is just returning NA values. Can anyone see whats wrong?

Many Thanks!

tripdata_2020to2021 %>%
separate(started_at, into = c("start_date", "start_time"), sep = " ", remove = FALSE) %>%
mutate(start_date = lubridate::as_date(start_date, format = "%y/%m/%d"), start_time = hms::as_hms(str_c(start_time, "00")))

[DavoWW]

Hi @Alec_Watson,
Welcome to the RStudio Community Forum.
Your code was very close to working - the format of the "start_date" input string was wrongly specified:

library(tidyverse)

tripdata_2020to2021 <- data.frame(started_at = c("2020-05-23 21:16:11",
                                                 "2020-05-24 22:16:11",
                                                 "2020-05-25 23:16:11"))
tripdata_2020to2021 %>%
  separate(
    started_at,
    into = c("start_date", "start_time"),
    sep = " ",
    remove = FALSE
  ) %>%
  mutate(
    start_date = lubridate::as_date(start_date, format = "%Y-%m-%d"),
    start_time = hms::as_hms(str_c(start_time, "00"))
  )
#>            started_at start_date start_time
#> 1 2020-05-23 21:16:11 2020-05-23   21:16:00
#> 2 2020-05-24 22:16:11 2020-05-24   22:16:00
#> 3 2020-05-25 23:16:11 2020-05-25   23:16:00

^{Created on 2021-05-24 by the reprex package (v2.0.0)}

[Alec_Watson]

@DavoWW

tripdata_2020to2021 %>%
  separate(
    started_at,
    into = c("start_date", "start_time"),
    sep = " ",
    remove = FALSE
  ) %>%
  mutate(
    start_date = lubridate::as_date(start_date, format = "%Y-%m-%d"),
    start_time = hms::as_hms(str_c(start_time, "00"))
  )

Thanks for the swift reply Davo, you are a legend. All working now.