Steffen J. -4 years ago 98
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?

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

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download