CODEWITHSUNDEEP
rdplyrdata.table
pyne
pyne

Reputation: 507

merge multiple date and time columns into a datetime column R

For data below:

> dt
         date   event1   event2   event3
1: 2016-04-27 10:25:15 11:05:45 13:00:09
2: 2016-04-27 10:25:15 11:05:45 13:00:09
3: 2016-04-27 10:25:15 11:05:45 13:00:09
4: 2016-04-27 10:25:15 11:05:45 13:00:09
5: 2016-04-27 10:25:15 11:05:45 13:00:09

I'd like to merge date with each of the event columns to make the event time columns into a datetime format. Desired output:

dt$event1 = as.POSIXct(paste(dt$date, dt$event1), format="%Y-%m-%d %H:%M:%S")
dt$event2 = as.POSIXct(paste(dt$date, dt$event2), format="%Y-%m-%d %H:%M:%S")
dt$event3 = as.POSIXct(paste(dt$date, dt$event3), format="%Y-%m-%d %H:%M:%S")
dt$date = NULL

  > dt
                event1              event2              event3
1: 2016-04-27 10:25:15 2016-04-27 11:05:45 2016-04-27 13:00:09
2: 2016-04-27 10:25:15 2016-04-27 11:05:45 2016-04-27 13:00:09
3: 2016-04-27 10:25:15 2016-04-27 11:05:45 2016-04-27 13:00:09
4: 2016-04-27 10:25:15 2016-04-27 11:05:45 2016-04-27 13:00:09
5: 2016-04-27 10:25:15 2016-04-27 11:05:45 2016-04-27 13:00:09

Since I have quite a large dataset with >300k rows and >20 event time columns, what's the most efficient way to do this at once for all event time columns in either dplyr or data.table please?

Sample data:

dt = data.table(date = rep(as.POSIXct("2016-04-27"),5), event1 = rep("10:25:15",5), event2 = rep("11:05:45",5), event3 = rep("13:00:09",5))

Upvotes: 1

Views: 669

Answers (3)

Maurits Evers
Maurits Evers

Reputation: 50668

Not sure what this has to do with a merge; isn't this just

dt[, event1_datetime := as.POSIXct(paste(date, event1))]
#         date   event1   event2   event3     event1_datetime
#1: 2016-04-27 10:25:15 11:05:45 13:00:09 2016-04-27 10:25:15
#2: 2016-04-27 10:25:15 11:05:45 13:00:09 2016-04-27 10:25:15
#3: 2016-04-27 10:25:15 11:05:45 13:00:09 2016-04-27 10:25:15
#4: 2016-04-27 10:25:15 11:05:45 13:00:09 2016-04-27 10:25:15
#5: 2016-04-27 10:25:15 11:05:45 13:00:09 2016-04-27 10:25:15

Update

For what it's worth, here's a data.table solution using melt and dcast

dt[, n := 1:.N]
dt <- melt(dt[, n := 1:.N], id.vars = c("date", "n"), value.name = "time")
dt[, datetime := as.POSIXct(paste(date, time))]
dt <- dcast(dt, date + n ~ variable, value.var = c("time", "datetime"))
dt[, n := NULL]
#         date time_event1 time_event2 time_event3     datetime_event1
#1: 2016-04-27    10:25:15    11:05:45    13:00:09 2016-04-27 10:25:15
#2: 2016-04-27    10:25:15    11:05:45    13:00:09 2016-04-27 10:25:15
#3: 2016-04-27    10:25:15    11:05:45    13:00:09 2016-04-27 10:25:15
#4: 2016-04-27    10:25:15    11:05:45    13:00:09 2016-04-27 10:25:15
#5: 2016-04-27    10:25:15    11:05:45    13:00:09 2016-04-27 10:25:15
#       datetime_event2     datetime_event3
#1: 2016-04-27 11:05:45 2016-04-27 13:00:09
#2: 2016-04-27 11:05:45 2016-04-27 13:00:09
#3: 2016-04-27 11:05:45 2016-04-27 13:00:09
#4: 2016-04-27 11:05:45 2016-04-27 13:00:09
#5: 2016-04-27 11:05:45 2016-04-27 13:00:09

Or all in one go

dcast(melt(dt[, n := 1:.N], id.vars = c("date", "n"), value.name = "time")[,
    datetime := as.POSIXct(paste(date, time))], 
    date + n ~ variable, value.var = c("time", "datetime"))[,
    n := NULL][]

Upvotes: 2

chinsoon12
chinsoon12

Reputation: 25225

A possible approach using .SDcols:

cols <- paste0(grep("^event", names(dt), value=TRUE), "_datetime")
dt[, (cols) := 
    lapply(.SD, function(x) as.POSIXct(paste(date, x), format="%Y-%m-%d %H:%M:%S")), 
        .SDcols=event1:event3]

output:

         date   event1   event2   event3     event1_datetime     event2_datetime     event3_datetime
1: 2016-04-27 10:25:15 11:05:45 13:00:09 2016-04-27 10:25:15 2016-04-27 11:05:45 2016-04-27 13:00:09
2: 2016-04-27 10:25:15 11:05:45 13:00:09 2016-04-27 10:25:15 2016-04-27 11:05:45 2016-04-27 13:00:09
3: 2016-04-27 10:25:15 11:05:45 13:00:09 2016-04-27 10:25:15 2016-04-27 11:05:45 2016-04-27 13:00:09
4: 2016-04-27 10:25:15 11:05:45 13:00:09 2016-04-27 10:25:15 2016-04-27 11:05:45 2016-04-27 13:00:09
5: 2016-04-27 10:25:15 11:05:45 13:00:09 2016-04-27 10:25:15 2016-04-27 11:05:45 2016-04-27 13:00:09

data:

library(data.table)
dt <- fread("date   event1   event2   event3
2016-04-27 10:25:15 11:05:45 13:00:09
2016-04-27 10:25:15 11:05:45 13:00:09
2016-04-27 10:25:15 11:05:45 13:00:09
2016-04-27 10:25:15 11:05:45 13:00:09
2016-04-27 10:25:15 11:05:45 13:00:09")

Upvotes: 1

Ronak Shah
Ronak Shah

Reputation: 388817

We can use mutate_at to add new columns

library(dplyr)

dt %>%
  mutate_at(vars(starts_with("event")), funs(as.POSIXct(paste0(date, .)))) %>%
  select(-date)

#               event1              event2              event3
#1 2016-04-27 10:25:15 2016-04-27 11:05:45 2016-04-27 13:00:09
#2 2016-04-27 10:25:15 2016-04-27 11:05:45 2016-04-27 13:00:09
#3 2016-04-27 10:25:15 2016-04-27 11:05:45 2016-04-27 13:00:09
#4 2016-04-27 10:25:15 2016-04-27 11:05:45 2016-04-27 13:00:09
#5 2016-04-27 10:25:15 2016-04-27 11:05:45 2016-04-27 13:00:09

Upvotes: 1

Related Questions