Aggregating dates?

[algo-se]

I have not found info regarding this, excuse me if it´s duplicate.

Is it posible to aggregate dates? The built-in aggregate functions do not work, but maybe it can be done with JS?

REPREX:

data <- data.frame(
  group = c("A", "A", "B", "B", "B"),
  percent = c(0.95, 0.5, 0.11, 0.12, 0.23),
  date = as.Date(c("2019-01-02", "2019-03-15", "2019-09-22", "2019-09-22", "2020-09-22"))
)

library(reactable)
reactable(data,
          groupBy = "group",
          columns = list(
            percent = colDef(aggregate = "max"),
            date = colDef(aggregate = "max") # this does not work
          ))

[glin]

It is possible to aggregate dates, but the built-in min and max aggregate functions only work on numbers. So for now, you'll have to write a custom aggregate function for dates. Here's an example of this you can adapt:

library(reactable)

data <- data.frame(
  group = c("A", "A", "B", "B", "B"),
  percent = c(0.95, 0.5, 0.11, 0.12, 0.23),
  date = as.Date(c("2019-01-02", "2019-03-15", "2019-09-22", "2019-09-22", "2020-09-22"))
)

# Aggregate function that gets the max of an array of date strings
maxDate <- JS("function maxDate(values) {
  const dates = values.map(function (v) {
    // Format YYYY-MM-DD dates in local time, not UTC.
    // Ignore ISO 8601 dates otherwise, i.e., YYYY-MM-DDTHH:MM:SS[Z]
    // http://blog.dygraphs.com/2012/03/javascript-and-dates-what-mess.html
    if (v.includes('-') && !v.includes('T') && !v.includes('Z')) {
      v = v.replace(/-/g, '/')
    }
    return new Date(v)
  })
  const date = new Date(Math.max.apply(null, dates))
  // Format as YYYY-MM-DD
  return [
    date.getFullYear(),
    (date.getMonth() + 1).toString().padStart(2, '0'),
    date.getDate().toString().padStart(2, '0')
  ].join('-')
}")

reactable(
  data,
  groupBy = "group",
  columns = list(
    percent = colDef(aggregate = "max"),
    date = colDef(aggregate = maxDate)
  )
)

I'll probably add some variant of this example to reactable itself. I think it'd be a nice addition, especially since it's really painful to work with dates in JavaScript. The only reason why min and max don't support dates is just that I hadn't thought of it before.

[sctyner]

I'm in a situation where I want to aggregate start/end times in a process. I would love a min/max datetime aggregate function. Thank you for this example.

[glin]

The min and max aggregate functions now work on dates and date-times in the development version (3d5f143). It turned out to be much simpler than the ugly example I came up with earlier 😅, thanks for opening this.

Here's what the original example code should produce now:

library(reactable)

data <- data.frame(
  group = c("A", "A", "B", "B", "B"),
  percent = c(0.95, 0.5, 0.11, 0.12, 0.23),
  date = as.Date(c("2019-01-02", "2019-03-15", "2019-09-22", "2019-09-22", "2020-09-22"))
)

reactable(
  data,
  groupBy = "group",
  columns = list(
    percent = colDef(aggregate = "max"),
    date = colDef(aggregate = "max")
  )
)