Goal: Grouped by quarter and name I want to have the top n names by count (see example below). So the desired output for top 1 (for the example below) would be:
2019 Q1 Klaus 2
2019 Q2 Karl 3
As this is just a toy example going forward I also want to have the top 4, 5 etc by count per quarter and name.
Do you have any good ideas how to implement this with data.table (no dplyr please). Many thanks!
library(data.table)
dt <- data.table(x = c("2019 Q1", "2019 Q1", "2019 Q1", "2019 Q2", "2019 Q2", "2019 Q2", "2019 Q2"),
y = c("Klaus", "Gustav", "Klaus", "Karl", "Karl", "Karl", "Stefan"))
# Structure of dt
# x y
# 1: 2019 Q1 Klaus
# 2: 2019 Q1 Gustav
# 3: 2019 Q1 Klaus
# 4: 2019 Q2 Karl
# 5: 2019 Q2 Karl
# 6: 2019 Q2 Karl
# 7: 2019 Q2 Stefan
dt[, .N, by = .(x, y)]
# Output:
# x y N
# 1: 2019 Q1 Klaus 2
# 2: 2019 Q1 Gustav 1
# 3: 2019 Q2 Karl 3
# 4: 2019 Q2 Stefan 1
You can first calculate the N per name and quarter and then order the data.table and afterwards pick the first n rows per quarter:
dt[, .N, by = .(x, y)][order(-N), head(.SD, 1), by = x]