How do I convert below column to date format?

Question

    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

It's hard to help with a question about data types without example data that contains the exact data types, like from calling `dput`. Otherwise, it's unclear whether these are actual date objects, or strings, or something else. [See here](https://stackoverflow.com/q/5963269/5325862) on making a reproducible example — camille, Jan 03 '22 at 03:21
You're trying to convert the string `"ActivityHour"` to a date, which obviously isn't going to work; you need to be converting that column. Same with the `typeof` call in your comment. You also don't seem to have a date there, you have a time, so `as.Date` probably won't work anyway. All of this is why a reproducible example is necessary for a question like this — camille, Jan 03 '22 at 17:05

Edison Wu · Answer 1 · 2022-01-03T03:41:13.973

0

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")))

edited Jan 03 '22 at 03:41

answered Jan 03 '22 at 03:33

Edison Wu

47
2

this give me NA as well – MichaelP Jan 03 '22 at 03:50

score 0 · Answer 2 · answered Jan 03 '22 at 03:46

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"))

or

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"))

score 0 · Answer 3 · answered Jan 03 '22 at 13:11

Is Date separate from ActivityHour? Or should they be combined?

Combined:


library(lubridate)

mdy_hms(paste0(df1$Date," ",df1$ActivityHour)) 
[1] "2016-04-12 00:00:00 UTC" "2016-04-12 01:00:00 UTC"
[3] "2016-04-12 02:00:00 UTC" "2016-04-12 03:00:00 UTC"
#You'll have to figure out time zone, you'll need "tz = ..."

If you want ActivityHour separate:

hms(hourlyCalories$ActivityHour)
[1] "12H 0M 0S" "1H 0M 0S"  "2H 0M 0S"  "3H 0M 0S"

When you provide code in a question, it should be complete. In other words, even though they're common packages, you should include your library(tidyr) and library(dplyr) statements at the start of your question's code.

score 0 · Answer 4 · answered Jan 04 '22 at 05:52

0

thank you all, I managed to do it with below.

hourlyCalories <- mutate(hourlyCalories, 
"ActivityHour"=strptime(ActivityHour, format = "%I:%M:%S %p")) %>% 
mutate("ActivityHour"=format(ActivityHour, format = "%H"))

answered Jan 04 '22 at 05:52

MichaelP

1

sorry about the unclear question, you all have some very good advice – MichaelP Jan 04 '22 at 06:12
As it’s currently written, your answer is unclear. Please [edit] to add additional details that will help others understand how this addresses the question asked. You can find more information on how to write good answers [in the help center](/help/how-to-answer). – Community Jan 04 '22 at 09:08

How do I convert below column to date format?

4 Answers4