Using dcast.data.table with date values and aggregation

Question

Trying to figure this out. Suppose you have a data.table:

dt <- data.table (person=c('bob', 'bob', 'bob'), 
                  door=c('front door', 'front door', 'front door'),
                  type=c('timeIn', 'timeIn', 'timeOut'),
                  time=c(
as.POSIXct('2016 12 02 06 05 01', format = '%Y %m %d %H %M %S'),
as.POSIXct('2016 12 02 06 05 02', format = '%Y %m %d %H %M %S'),
as.POSIXct('2016 12 02 06 05 03', format = '%Y %m %d %H %M %S')                     )
)

I want to pivot it to look like this

person        door        timeIn             timeOut

bob           front door  min(<date/time>) max(<date/time>)

I can't seem to get the right syntax for dcast.data.table. I tried

dcast.data.table(
  dt, person + door ~ type, 
  value.var = 'time', 
  fun.aggregate = function(x) ifelse(type == 'timeIn', min(x), max(x))
)

which throws an error:

Aggregating function(s) should take vector inputs and return a single value (length=1).

I also tried:

 dcast.data.table(dt, person + door ~ type, value.var = 'time')

But the result throws away my dates

   person       door timeIn timeOut
1:    bob front door      2       1

Any suggestions would be appreciated. TIA

Uwe · Accepted Answer · 2016-12-24T11:59:37.200

There are several ways to achieve the desired result using dcast. jazzurro's solution does the aggregation before reshaping the result. The approaches here use dcast directly but may require some post-processing. We are using jazzurro's data which are tweaked to obey the UTC time zone and CRAN version 1.10.0 of data.table.

1. Getting `ifelse` to work

As reported in the Q,

dcast(
  dt, person + door ~ type, 
  value.var = 'time', 
  fun.aggregate = function(x) ifelse(type == 'timeIn', min(x), max(x))
)

returns an error message. The full text of the error message includes the hint to use the fill parameter. Unfortunately, ifelse() doesn't respect the POSIXct class (for details see ?ifelse) so this needs to be enforced.

With

dcast(
  dt, person + door ~ type, 
  value.var = 'time', 
  fun.aggregate = function(x) 
    lubridate::as_datetime(ifelse(type == 'timeIn', min(x), max(x))),
  fill = 0
)

we do get

#   person       door              timeIn             timeOut
#1:    ana front door 2016-12-02 07:06:01 2016-12-02 07:06:05
#2:    bob front door 2016-12-02 06:05:01 2016-12-02 06:05:05

2. Alternative to `ifelse`

ifelse's help page suggests

(tmp <- yes; tmp[!test] <- no[!test]; tmp)

as alternative. Following this advice,

dcast(
  dt, person + door ~ type, 
  value.var = 'time', 
  fun.aggregate = function(x) {
    test <- type == "timeIn"; tmp <- min(x); tmp[!test] = max(x)[!test]; tmp
    }
)

returns

#   person       door              timeIn             timeOut
#1:    ana front door 2016-12-02 07:06:01 2016-12-02 07:06:05
#2:    bob front door 2016-12-02 06:05:01 2016-12-02 06:05:05

Note that neither the fill parameter nor the coercion to POSIXct is needed.

3. Using enhanced `dcast`

With the latest versions of dcast.data.table we can provide a list of functions to fun.aggregate:

dcast(dt, person + door ~ type, value.var = 'time', fun = list(min, max))

returns

#   person       door     time_min_timeIn    time_min_timeOut     time_max_timeIn    time_max_timeOut
#1:    ana front door 2016-12-02 07:06:01 2016-12-02 07:06:03 2016-12-02 07:06:02 2016-12-02 07:06:05
#2:    bob front door 2016-12-02 06:05:01 2016-12-02 06:05:03 2016-12-02 06:05:02 2016-12-02 06:05:05

We can remove the unwanted columns and rename the others by

dcast(dt, person + door ~ type, value.var = 'time', fun = list(min, max))[
  , .(person, door, timeIn = time_min_timeIn, timeOut = time_max_timeOut)]

which gets us

#   person       door              timeIn             timeOut
#1:    ana front door 2016-12-02 07:06:01 2016-12-02 07:06:05
#2:    bob front door 2016-12-02 06:05:01 2016-12-02 06:05:05

Data

As mentioned above, we are using jazzurro's data

dt <- structure(list(person = c("bob", "bob", "bob", "bob", "ana", 
"ana", "ana", "ana"), door = c("front door", "front door", "front door", 
"front door", "front door", "front door", "front door", "front door"
), type = c("timeIn", "timeIn", "timeOut", "timeOut", "timeIn", 
"timeIn", "timeOut", "timeOut"), time = structure(c(1480658701, 
1480658702, 1480658703, 1480658705, 1480662361, 1480662362, 1480662363, 
1480662365), class = c("POSIXct", "POSIXt"))), .Names = c("person", 
"door", "type", "time"), row.names = c(NA, -8L), class = c("data.table", 
"data.frame"))

but coerce the time zone to UTC.

With

dt[, time := lubridate::with_tz(time, "UTC")]

we have

dt
#   person       door    type                time
#1:    bob front door  timeIn 2016-12-02 06:05:01
#2:    bob front door  timeIn 2016-12-02 06:05:02
#3:    bob front door timeOut 2016-12-02 06:05:03
#4:    bob front door timeOut 2016-12-02 06:05:05
#5:    ana front door  timeIn 2016-12-02 07:06:01
#6:    ana front door  timeIn 2016-12-02 07:06:02
#7:    ana front door timeOut 2016-12-02 07:06:03
#8:    ana front door timeOut 2016-12-02 07:06:05

independent of local time zone.

Strange because Uwe Block's solution appeared to work yesterday but now running into an error dcast( dt, person + door ~ type, value.var = 'time', fun.aggregate = function(x) lubridate::as_datetime(ifelse(type == 'timeIn', min(x), max(x))), fill = 0 ) throws an error "error in ifelse (...) object 'type' not found — ds_practicioner, Dec 26 '16 at 18:33
Note - the reason for this is you need to use dcast.data.table instead of just dcast — ds_practicioner, Dec 26 '16 at 18:40
As long as `dt` is a `data.table` a simple `dcast` should suffice, I believe. — Uwe, Dec 26 '16 at 20:09

jazzurro · Answer 2 · 2016-12-23T23:59:53.950

This would be one way to achieve your goal. I modified your dt and created the following data set. For each person, I looked for the minimum time of timeIn and the maximum time of timeOut. Then, I applied dcast() to the result.

#   person       door    type                time
#1:    bob front door  timeIn 2016-12-02 06:05:01
#2:    bob front door  timeIn 2016-12-02 06:05:02
#3:    bob front door timeOut 2016-12-02 06:05:03
#4:    bob front door timeOut 2016-12-02 06:05:05
#5:    ana front door  timeIn 2016-12-02 07:06:01
#6:    ana front door  timeIn 2016-12-02 07:06:02
#7:    ana front door timeOut 2016-12-02 07:06:03
#8:    ana front door timeOut 2016-12-02 07:06:05

library(data.table)

dcast(
   dt[, .SD[(type == "timeIn" & time == min(time))|(type == "timeOut" & time == max(time))], by = person],
   person + door ~ type)

#   person       door              timeIn             timeOut
#1:    ana front door 2016-12-02 07:06:01 2016-12-02 07:06:05
#2:    bob front door 2016-12-02 06:05:01 2016-12-02 06:05:05

DATA

dt <- structure(list(person = c("bob", "bob", "bob", "bob", "ana", 
"ana", "ana", "ana"), door = c("front door", "front door", "front door", 
"front door", "front door", "front door", "front door", "front door"
), type = c("timeIn", "timeIn", "timeOut", "timeOut", "timeIn", 
"timeIn", "timeOut", "timeOut"), time = structure(c(1480658701, 
1480658702, 1480658703, 1480658705, 1480662361, 1480662362, 1480662363, 
1480662365), class = c("POSIXct", "POSIXt"))), .Names = c("person", 
"door", "type", "time"), row.names = c(NA, -8L), class = c("data.table", 
"data.frame"))

There may be a time-zone issue, but when using your `dt` structure I get different `time` values to those in your example — SymbolixAU, Dec 23 '16 at 23:53
@SymbolixAU Yeah, that should happen. I am not in your time zone. — jazzurro, Dec 23 '16 at 23:56
@SymbolixAU I specified tz = "UTC" when I created the sample data. I wonder if you can reproduce the same results by specifying that time zone. — jazzurro, Dec 24 '16 at 00:01
Not by reading it in directly; I have to convert it to numeric, then to POSIXct specifying `tz = "UTC"` this end. — SymbolixAU, Dec 24 '16 at 00:04

Using dcast.data.table with date values and aggregation

2 Answers2

1. Getting ifelse to work

2. Alternative to ifelse

3. Using enhanced dcast

Data

1. Getting `ifelse` to work

2. Alternative to `ifelse`

3. Using enhanced `dcast`