Steffen J. Steffen J. - 3 months ago 17
R Question

select one row per group with ifelse in data.table

I'm grouping a data.table and want to select from each group the first row where x == 1 or, if such a row does not exist, then the first row with any value in x

d <- data.table(
a = c(1,1,1, 2,2, 3,3),
x = c(0,1,0, 0,0, 1,1),
y = c(1,2,3, 1,2, 1,2)
)


this attempt

d[, ifelse(any(.SD[,x] == 1),.SD[x == 1][1], .SD[1]), by = a]


returns

a V1
1: 1 1
2: 2 0
3: 3 1


but i expected

a x y
1: 1 1 2
2: 2 0 1
3: 3 1 1


Any ideas how to get it right?

Answer

I think it's a good use case for both match and it's nomatch argument

d[, .SD[match(1L, x, nomatch = 1L)], by = a]
#    a x y
# 1: 1 1 2
# 2: 2 0 1
# 3: 3 1 1

This is basically, in case of no-match, returns 1, and as a result gives you the first row in the group. If there is a multiple match, then it will return the first one- as per your desire

Comments