mercredi 8 juillet 2020

flag row with the maximum number in each row

inspired by this question, I was wondering how can you flag the row with the max number by group, while keeping all the other rows as well, instead of removing them?

Lets say i want to look for the max in Value for every ID separately. Then flag the max row with an 1 and all the others with 0.

so, basically getting from this:

> ID    <- c(1,1,1,2,2,2,2,3,3)
> Value <- c(2,3,5,2,5,8,17,3,5)
> Event <- c(1,1,2,1,2,1,2,2,2)
> DF <- data.table(cbind(ID, Value, Event))
> DF
   ID Value Event
1:  1     2     1
2:  1     3     1
3:  1     5     2
4:  2     2     1
5:  2     5     2
6:  2     8     1
7:  2    17     2
8:  3     3     2
9:  3     5     2

to this:

DF
   ID Value Event flagMAX
1:  1     2     1       0
2:  1     3     1       0
3:  1     5     2       1
4:  2     2     1       0
5:  2     5     2       0
6:  2     8     1       1
7:  2    17     2       1
8:  3     3     2       0
9:  3     5     2       0

how can this be done? ideally with data.table

i tried :

DF[,flagMAX := ifelse(max(Value), 1, 0), by = "ID"]

but get all 1

any ideas?

Aucun commentaire:

Enregistrer un commentaire