split date and time in different columns of dataframe in R

Question

I have a following dataframe in R

ID       Date1              Date2       

1        21-03-16 8:36      22-03-16 12:36
1        23-03-16 9:36      24-03-16 01:36
1        22-03-16 10:36     25-03-16 11:46
1        23-03-16 11:36     28-03-16 10:16

My desired dataframe is

ID    Date1        Date1_time    Date2          Date2_time
1     2016-03-21   08:36:00      2016-03-22     12:36:00
1     2016-03-23   09:36:00      2016-03-24     01:36:00
1     2016-03-22   10:36:00      2016-03-25     11:46:00
1     2016-03-23   11:36:00      2016-03-28     10:16:00

I can do this individually using strptime like following

df$Date1 <- strptime(df$Date1, format='%d-%m-%y %H:%M')
df$Date1_time <-  strftime(df$Date1 ,format="%H:%M:%S")
df$Date1 <- strptime(df$Date1, format='%Y-%m-%d')

But,I have many date columns to convert like above. How can I write function in R which will do this.

austensen · Answer 1 · 2017-06-02T12:53:03.100

You can do this with dplyr::mutate_at to operate on multiple columns. See select helpers for more info on efficiently specifying which columns to operate on.

Then you can use lubridate and hms for date and time functions.

library(dplyr)
library(lubridate)
library(hms)


df <- readr::read_csv(
'ID,Date1,Date2
1,"21-03-16 8:36","22-03-16 12:36"
1,"23-03-16 9:36","24-03-16 01:36"
1,"22-03-16 10:36","25-03-16 11:46"
1,"23-03-16 11:36","28-03-16 10:16"'
)

df

#> # A tibble: 4 x 3
#>      ID          Date1          Date2
#>   <int>          <chr>          <chr>
#> 1     1  21-03-16 8:36 22-03-16 12:36
#> 2     1  23-03-16 9:36 24-03-16 01:36
#> 3     1 22-03-16 10:36 25-03-16 11:46
#> 4     1 23-03-16 11:36 28-03-16 10:16

df %>% 
  mutate_at(vars(Date1, Date2), dmy_hm) %>% 
  mutate_at(vars(Date1, Date2), funs("date" = date(.), "time" = as.hms(.))) %>% 
  select(-Date1, -Date2)

#> # A tibble: 4 x 5
#>      ID Date1_date Date2_date Date1_time Date2_time
#>   <int>     <date>     <date>     <time>     <time>
#> 1     1 2016-03-21 2016-03-22   08:36:00   12:36:00
#> 2     1 2016-03-23 2016-03-24   09:36:00   01:36:00
#> 3     1 2016-03-22 2016-03-25   10:36:00   11:46:00
#> 4     1 2016-03-23 2016-03-28   11:36:00   10:16:00

When I run your code it give me `Error in as.fun_list(.funs, .env = parent.frame(), ...) : object 'as_datetime' not found` — Neil, Jun 02 '17 at 05:24
I replaced it with `as_date` but it gives me some weird formatted date `0021-03-16` and time as `00:00:00` — Neil, Jun 02 '17 at 05:46
Hey, sorry I totally missed that it was dmy not ymd format. I've changed the first function from `as_datetime()` to `dmy_hm()` and now it works — austensen, Jun 02 '17 at 12:54

score 0 · Answer 2 · answered Jun 02 '17 at 04:45

0

Using dplyr for manipulation:

convertTime <- function(x)as.POSIXct(x, format='%d-%m-%y %H:%M')

df %>% 
    mutate_at(vars(Date1, Date2), convertTime) %>% 
    group_by(ID) %>% 
    mutate_all(funs("date"=as.Date(.), "time"=format(., "%H:%M:%S")))


# Source: local data frame [4 x 7]
# Groups: ID [1]
# 
#      ID               Date1               Date2 Date1_date Date2_date Date1_time Date2_time
#   <int>              <dttm>              <dttm>     <date>     <date>      <chr>      <chr>
# 1     1 2016-03-22 12:36:00 2016-03-22 12:36:00 2016-03-22 2016-03-22   12:36:00   12:36:00
# 2     1 2016-03-24 01:36:00 2016-03-24 01:36:00 2016-03-23 2016-03-23   01:36:00   01:36:00
# 3     1 2016-03-25 11:46:00 2016-03-25 11:46:00 2016-03-25 2016-03-25   11:46:00   11:46:00
# 4     1 2016-03-28 10:16:00 2016-03-28 10:16:00 2016-03-28 2016-03-28   10:16:00   10:16:00

answered Jun 02 '17 at 04:45

Adam Quek

6,973
1
17
23

by running above code it gives me following error `Error in mutate_impl(.data, dots) : character string is not in a standard unambiguous format` I do not want to groupby by ID. Want it to run for every row – Neil Jun 02 '17 at 05:23
Don't worry about the `group_by`. It's not going to affect the mutate in this case, and it's used as a way to bypass the identifier variable, so that you do not need to specify all variables (e.g. `Date1` to `DateX`) with the `mutate_all` function. As for the error, can you please share what is the data-structure of your data.frame? – Adam Quek Jun 02 '17 at 05:35
Here's [something](https://stackoverflow.com/questions/14755425/what-are-the-standard-unambiguous-date-formats) associated with the error you'd encountered. For the last line, if you replace `"date"=as.Date(.)` with `"date"=format(., "%Y-%m-%d")`, do you still get the error? – Adam Quek Jun 02 '17 at 05:42
It gives `Error in mutate_impl(.data, dots) : invalid 'trim' argument` – Neil Jun 02 '17 at 05:43
I suspect there are some issues with the format of your whole data-set, which I doubt I could help you further with only the sample data above. – Adam Quek Jun 02 '17 at 05:48

score 0 · Answer 3 · answered Aug 10 '17 at 12:56

I have the same problem, you can try this may be help using strsplit

x <- df$Date1    
y = t(as.data.frame(strsplit(as.character(x),' ')))
    row.names(y) = NULL

    # store splitted data into new columns 
    df$date <- y[,1] # date column
    df$time <- y[,2] # time column

split date and time in different columns of dataframe in R

3 Answers3

Linked