Trouble in calculating minima by group in R

Question

Using the answer in here, I have successfully calculated minima and maxima by group many times. This time it doesn't work and I don't understand why. Here is a reproducible example.

example <- structure(
  list(ID = 1:10, 
       date = 
         c("2005-05-09", "2006-09-18", "1996-06-14", "1997-01-06", 
           "1997-03-13", "1997-05-06", "1990-01-04", "1990-01-11", 
           "1989-12-28", "1989-12-28"), 
       name = c("a", "a", "a", "a", "a", "a", "b", "b", "b", "b")), 
  .Names = c("ID", "date", "name"), 
  class = c("data.table", "data.frame"), 
  row.names = c(NA, -10L))

example[example[, .I[which.min(date)], by=c("name")]$V1]

I was expecting something like:

1996-06-14    a
1989-12-28    b

but instead I get an empty data table. Why?

Your `date` column is a string. You need to convert it a `IDate` class first, e.g., `example[, date := as.IDate(date)]` — David Arenburg, May 29 '18 at 11:26

G. Grothendieck · Accepted Answer · 2018-05-29T12:17:01.770

1

Below let:

library(data.table)
DT <- as.data.table(example)

1) If you replace date with xtfrm(date) in your code it will work.

DT[DT[, .I[which.min(xtfrm(date))], by=c("name")]$V1]

giving:

   ID       date name
1:  3 1996-06-14    a
2:  9 1989-12-28    b

2) This gives just one minimum for each group:

DT[, .SD[which.min(xtfrm(date))], by = name]

giving:

   name ID       date
1:    a  3 1996-06-14
2:    b  9 1989-12-28

3) This gives all the minima for each group:

DT[, .SD[date == min(date)], by = name]

giving:

   name ID       date
1:    a  3 1996-06-14
2:    b  9 1989-12-28
3:    b 10 1989-12-28

edited May 29 '18 at 12:17

answered May 29 '18 at 11:41

G. Grothendieck

254,981
17
203
341

That's brilliant, thank you! What's the meaning of .SD? I have never seen it before. – Nonancourt May 29 '18 at 12:20
1

When by= is used it refers to the current subset of rows otherwise all rows. – G. Grothendieck May 29 '18 at 13:46

Trouble in calculating minima by group in R

1 Answers1