How to separate my 1 column of time into Data and Time

user_17736799 0
Viewed 63

I'm very sorry (I'm brand new), as I understand this has been posted a million times but I want to understand not only the answer, but which methodology I should be using as a best practice. I've clicked 10+ links on how to do this, but I seem to mess it up on my data.

I have 3 columns of data in my heartrate_seconds.csv (ID, Date, Value), my date column is comprised of both the time and date which I'd like to separate (Ex: 4/12/2016 7:22:50 AM)

so far the closest I've been is using

heartrate_timeanddate <- heartrate_seconds %>% 
  mutate(Time = as.character(Time)) %>% 
  separate(Time, into = c("Date", "Time"), sep = " ") %>% 
  mutate(Time = as.Date(Date))

but this results in my newly added Time column as a date, how do I change this?

Other methods I tried that failed very early:

heartrate_seconds_Time <- separate(heartrate_seconds = df, col = Time, into = c('Date, Time'), sep = ' ')

heartrate_time <- data.frame(heartrate_seconds, Time = c('Date','Time'))
heartrate_time$date <- as.Date(heartrate_time$all)
heartrate_time$date <- format(as.Date(heartrate_time$all), format ="%H:%M:%S")

heartrate_seconds %>% 
  separate(Time, into = c('Date, Time'), sep = '\\\\') %>% 
  select(Date, Time)

I feel I'm using other peoples methods from other posts incorrectly and it's just pushed me into further confusion. So I've come seeking clarity. Thank you so much!

All of this just because I couldn't import the .csv into SQL, which I probably could've fixed in the schema but 1 thing at a time I guess.

r date
2 Answers

One simple option is to use separate as you did, but replace the first space with another character, then separate on that.

library(tidyverse)

df %>% 
  mutate(col = str_replace(date, "\\s", "|")) %>% 
  separate(col, into = c("Date", "Time"), sep = "\\|") %>% 
  mutate(Date = as.Date(Date, format="%d/%m/%Y"))

Output

                  date       Date       Time
1 4/12/2016 7:22:50 AM 2016-12-04 7:22:50 AM
2 6/12/2016 7:22:50 PM 2016-12-06 7:22:50 PM

Data

df <-
  structure(list(date = c(
    "4/12/2016 7:22:50 AM", "6/12/2016 7:22:50 PM"
  )),
  class = "data.frame",
  row.names = c(NA,-2L))

Another option is to use as.Date to convert the date into a variable of class Date. Then, for time variable, we can use format to extract the time component as a string.

df$Date <- as.Date(df$date)
df$Time <- format(as.POSIXct(df$date), format = "%H:%M:%S")

Output

                  date       Date     Time
1  2021-05-28 07:16:52 2021-05-28 07:16:52
2  2021-05-30 13:35:00 2021-05-30 13:35:00
3  2021-05-30 14:22:55 2021-05-30 14:22:55
4  2021-05-28 06:51:51 2021-05-28 06:51:51
5  2021-05-30 13:31:55 2021-05-30 13:31:55
6  2021-05-30 13:33:38 2021-05-30 13:33:38
7  2021-05-30 13:35:51 2021-05-30 13:35:51
8  2021-05-30 13:38:44 2021-05-30 13:38:44
9  2021-05-30 13:51:19 2021-05-30 13:51:19
10 2021-05-30 14:04:53 2021-05-30 14:04:53

Data

df <- structure(list(date = structure(c(1622186212, 1622381700, 1622384575, 
                                        1622184711, 1622381515, 1622381618, 1622381751, 1622381924, 1622382679, 
                                        1622383493), 
                                      tzone = "UTC", class = c("POSIXct", "POSIXt"))), 
                row.names = c(NA, -10L), class = c("tbl_df", "tbl", "data.frame"))
user_152931910

In your case, I would use either separate or the lubridate package.

tidyr::separate

Use tidyr::separate with parameter extra = "merge". This means that if you include less columns in into, the remaining information will be kept in the last column. Here, this means you can use it to get two columns with the separator " ".

library(dplyr)
library(tidyr)

df %>% 
  separate(date, into = c("Date","Time"), extra = "merge", sep = " ")

       Date       Time
1 4/12/2016 7:22:50 AM
2 6/12/2016 7:22:50 PM

lubridate

With the lubridate package, you can convert your column to a datetime object easily with dmy_hms. Then create new variables with date and format.

library(lubridate)
library(dplyr)

df %>% 
  mutate(date = dmy_hms(date),
         Date = date(date),
         Time = format(date, format = "%H:%M:%S"))

                 date       Date     Time
1 2016-12-04 07:22:50 2016-12-04 07:22:50
2 2016-12-06 19:22:50 2016-12-06 19:22:50
user_134606020
Related