How can i split date-time column "2019-05-01T08:00:00+00:00" into two date and time columns?

user_12434059 0
Viewed 166

I have tried to split date-time column of this format 2019-05-01T08:00:00+00:00 into two columns 'Date' and 'Time' to no avail. Column name is YearTime and I have coded it as follows:

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

Below is what I get:

YearTime                       Date          Time      
2019-05-01T08:00:00+00:00      2019-05-01    00:00:00
2019-05-01T08:15:00+00:00      2019-05-01    00:00:00

I don't need 00:00:00 under the 'Time' column, instead i need the following format:

    YearTime                       Date          Time      
    2019-05-01T08:00:00+00:00      2019-05-01    08:00:00
    2019-05-01T08:15:00+00:00      2019-05-01    08:15:00

FYI, the 'Time' column has 15-minute interval. Any idea of how I can achieve this?

dplyr r
4 Answers

Specify the format explicitly in as.POSIXct.

df$YearTime <- as.POSIXct(df$YearTime, format = '%Y-%m-%dT%H:%M:%S', tz = 'UTC')
df$Date <- as.Date(df$YearTime)
df$Time <- format(df$YearTime, format = "%H:%M:%S") 
df

#             YearTime       Date     Time
#1 2019-05-01 08:00:00 2019-05-01 08:00:00
#2 2019-05-01 08:15:00 2019-05-01 08:15:00

Using lubridate you can use ymd_hms -

library(dplyr)
library(lubridate)

df %>%
  mutate(YearTime = ymd_hms(YearTime), 
         Date = as.Date(YearTime), 
         Time = format(YearTime, format = "%H:%M:%S"))

data

It is easier to help if your provide data in a reproducible format.

df <- structure(list(YearTime = c("2019-05-01T08:00:00+00:00", 
"2019-05-01T08:15:00+00:00")), row.names = c(NA, -2L), class = "data.frame")
user_39629140

We could also use tidyr::separate and set sep argument to "T" character between your Date and Time values:

library(dplyr)
library(tidyr)

df %>%
  separate(YearTime, c("Date", "Time"), "T") %>%
  mutate(Date = as.POSIXct(Date, format = "%Y-%m-%d"), 
         Time = format(as.POSIXct(Time, format = "%H:%M:%S"), format = "%H:%M:%S"))

        Date     Time
1 2019-05-01 08:00:00
2 2019-05-01 08:15:00

If you want to keep your original YearTime value, set remove = FALSE in separate.

user_143145200

Here is another way: Using str_extract, regex, and lubridate functions:

library(dplyr)
library(stringr)
library(lubridate)

df %>%  
    mutate(Date = ymd(str_extract(YearTime, "\\d{4}-\\d{2}-\\d{2}")),
           Time = hms(str_extract_all(YearTime, "(?<=T).+(?=\\+)")))

Output:

  YearTime                  Date       Time     
  <chr>                     <date>     <Period> 
1 2019-05-01T08:00:00+00:00 2019-05-01 8H 0M 0S 
2 2019-05-01T08:15:00+00:00 2019-05-01 8H 15M 0S
user_133216470

We can use

library(dplyr)
library(lubridate)
library(data.table)
 df %>% 
    mutate(YearTime = ymd_hms(YearTime), Date = as.Date(YearTime),
     Time = as.ITime(YearTime))
             YearTime       Date     Time
1 2019-05-01 08:00:00 2019-05-01 08:00:00
2 2019-05-01 08:15:00 2019-05-01 08:15:00

Or in base R

transform(df, Date = trimws(YearTime, whitespace = "T.*"), 
      Time = trimws(YearTime, whitespace = ".*T|\\+.*"))
                   YearTime       Date     Time
1 2019-05-01T08:00:00+00:00 2019-05-01 08:00:00
2 2019-05-01T08:15:00+00:00 2019-05-01 08:15:00

data

df <- structure(list(YearTime = c("2019-05-01T08:00:00+00:00", 
"2019-05-01T08:15:00+00:00")), row.names = c(NA, -2L), class = "data.frame")
user_37322710
Related