CODEWITHSUNDEEP
r
Neil
Neil

Reputation: 8247

split date and time in different columns of dataframe in R

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.

Upvotes: 3

Views: 5129

Answers (3)

john
john

Reputation: 434

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

Upvotes: 0

austensen
austensen

Reputation: 3017

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

Upvotes: 2

Adam Quek
Adam Quek

Reputation: 7163

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

Upvotes: 0

Related Questions