How do I convert below column to date format?-CodePudding

    hourlyCalories <- hourlyCalories_merged[,c(1,2,3)] %>%
    separate("ActivityHour", into = c("Date", "Hour", "AM_PM"), sep = " ") %>% 
    unite("ActivityHour", "Hour", "AM_PM", sep = " ")

    Id      Date ActivityHour Calories
    1   1503960366 4/12/2016  12:00:00 AM       81
    2   1503960366 4/12/2016   1:00:00 AM       61
    3   1503960366 4/12/2016   2:00:00 AM       59
    4   1503960366 4/12/2016   3:00:00 AM       47

Below is the code I tried, but it would return NA, could you please help how to convert them to date?

hourlyCalories %>%
mutate("ActivityHour"=as.Date("ActivityHour", format = "%I:%M:%S %p"))

 Id      Date ActivityHour Calories
 1   1503960366 4/12/2016         NA       81
 2   1503960366 4/12/2016         NA       61
 3   1503960366 4/12/2016         NA       59
 4   1503960366 4/12/2016         NA       47

CodePudding user response：

library(lubridate)

hourlyCalories %>%
mutate("ActivityHour"= as.Date(dmy_hms(ActivityHour)))

# OR

library(stringr)

hourlyCalories %>%
mutate("ActivityHour"= as.Date(str_remove(ActivityHour, " AM| PM"),format="%d/%m/%Y %I:%M:%S", tz="UTC")))

CodePudding user response：

First, you should make a reproducible example.

Second, I believe the following code should work:

hourlyCalories %>%
 mutate(ActivityHour=readr::parse_date(ActivityHour, 
  format = "%d/%m/%Y"))

hourlyCalories %>%
  mutate(ActivityHour=as.Date(ActivityHour, format = "%d/%m/%Y")

See:

> readr::parse_date('4/12/2016', "%d/%m/%Y")
[1] "2016-12-04"
> as.Date('4/12/2016', format = "%d/%m/%Y")
[1] "2016-12-04"

Please notice that you should not put quotes around the variable name! Use mutate(ActivityHour=as.Date(ActivityHour)) instead of mutate("ActivityHour"=as.Date("ActivityHour"))