Convert from char to date-time and split date-time into different columns

str(sleep_data)
'data.frame': 413 obs. of 5 variables:
Id : num 1503960366 1503960366 1503960366 1503960366 1503960366 ... SleepDay : chr "4/12/2016 12:00:00 AM" "4/13/2016 12:00:00 AM" "4/15/2016 12:00:00 AM" "4/16/2016 12:00:00 AM" ...
TotalSleepRecords : int 1 2 1 2 1 1 1 1 1 1 ... TotalMinutesAsleep: int 327 384 412 340 700 304 360 325 361 430 ...
$ TotalTimeInBed : int 346 407 442 367 712 320 377 364 384 449 ...

i need to convert sleepday into date time and split into seperate columns.

"sleep_data$date <- mdy_hms(sleep_data$date_time)"

I used the above code and was able to create a date column
"$ date : POSIXct, format: "2016-04-12" "2016-04-13" ..."

I'not able to create a time column.

Can someone help me with the correct code

I used the lubridate package's parse_date_time() to convert SleepTime from character strings to date/time values. Then we can break the date and time apart with as.Date() and format:

# Some sample data...
df <- data.frame(
  SleepTime = c(
    "4/12/2016 12:00:00 AM", 
    "4/13/2016 12:00:00 AM", 
    "4/15/2016 12:00:00 AM"
  )
)


library(dplyr)
library(lubridate)

df <- df %>%
  mutate(
    SleepTime = parse_date_time(
      SleepTime, "m/d/Y I:M:S p"
    )
  ) %>% 
  mutate(
    Date = as.Date(SleepTime),
    Time = format(SleepTime, format = "%H:%M:%S")
  )

This yields:

   SleepTime       Date     Time
1 2016-04-12 2016-04-12 00:00:00
2 2016-04-13 2016-04-13 00:00:00
3 2016-04-15 2016-04-15 00:00:00
1 Like

Thanks for the solution. As you can see from the image i uploaded, there are two SleepDay column that appears when I select the first one I'm able to extract only the date(time column is not created) but when i select the last one i get both the time and date column. Also if you dont mind, can you please tell why you used an I and p(I:M:%S %p") here.
shot1

The "I" refers to an hour (1-12) in a time string, the p refers to an AM/PM indicator. You can find all these symbols by typing ? strptime in the R console.

I sort of made a mess in the original answer. When you use strptime() you need to include the percent sign with these all of these symbols (%M, %I , %p, etc.), but you don't need the percent sign with lubridate functions (it will just ignore them). I likely confused things by including some with the percent symbol and some without. I've corrected the code above.

1 Like

This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.

If you have a query related to it or one of the replies, start a new topic and refer back with a link.