R data.table finding the mode for a group of data

Question

I have the following data table x

id1 id2
a  x
a  x
a  y
b  z

For each combination of id1, id2 I can find the number of instances in the following way

x[,list(
    freq = .N
   ),by = "id1,id2"]

The above would yield

a x 2
a y 1
b z 1

Next I want to find the most frequent id2 for each id1, i.e. mode. So the expected result is

 a x 2
 b z 1

I can get there in a round about way, but is there a way to put a sequence number at the id1 level? Or some such hack that gets me to this efficiently and quickly, perhaps at the first step shown above? Thanks in advance

Answer 1

I'd do it this way:

setkey(dt[, list(freq = .N), by=list(id1, id2)], 
         id1, freq)[J(unique(id1)), mult="last"]
   id1 id2 freq
1:   a   x    2
2:   b   z    1

The idea is to first get the freq column (as you did). Then setkey on the resulting data.table with columns id1 and freq. This'll sort freq in ascending order already. With this, we can then do a by-without-by subsetting and combine it with mult="last" (because for every group, the last value will be the biggest, as it's sorted in ascending order).

This'll save a sort step for each grouping which can get time-consuming with increasing number of groups. Note that this does not handle ties. That is, if you've for same id1 two equal max values, then only one will be returned.