CODEWITHSUNDEEP
rdataframe
887
887

Reputation: 619

How to indicate two highest observations in group (with two conditions)?

Per request from Ronak. This is my data set:

Month      Week   Y     Name   Color
January     2     1.2    Joe    Red
January     2     3.3    Eric   Red
January     2     4.5    Mike   Blue
January     2     1.7    Brian  Blue
January     2     2.9    Pete   Red
January     2     6.8    Dave   Red
January     3     4.6    Joe    Red
January     3     5.1    Eric   Blue
January     3     2.1    Mike   Blue
January     3     6.9    Pete   Red
January     3     6.8    Dave   Red

...

I would like to create a new column ('Highest') which identifies the individuals with the two highest Y values in a given week (identifying them with A and B so it will be easier to create line segments later in my project) who also have the color 'Blue'.

Month      Week   Y     Name    Color Highest
January     2     1.2    Joe     Red    -
January     2     3.3    Eric    Red    B
January     2     4.5    Mike    Blue   A
January     2     1.7    Brian   Blue   -
January     2     2.9    Pete    Red    -
January     2     6.8    Dave    Red    -
January     3     4.6    Joe     Red    -
January     3     5.1    Eric    Blue   B
January     3     2.1    Mike    Blue   A
January     3     6.9    Pete    Red    -
January     3     6.8    Dave    Red    -

...

In short, I want the new column to indicate the highest two Y values when the week is two and the color is blue.

Upvotes: 0

Views: 29

Answers (1)

Ronak Shah
Ronak Shah

Reputation: 388817

You could use :

library(dplyr)
df %>%
  arrange(Month, Week, Color != 'Blue', desc(Y)) %>%
  group_by(Month, Week) %>%
  mutate(Highest = c('A','B', rep('-', n() - 2)))


#   Month    Week     Y Name  Color Highest
#   <chr>   <int> <dbl> <chr> <chr> <chr>  
# 1 January     2   4.5 Mike  Blue  A      
# 2 January     2   1.7 Brian Blue  B      
# 3 January     2   6.8 Dave  Red   -      
# 4 January     2   3.3 Eric  Red   -      
# 5 January     2   2.9 Pete  Red   -      
# 6 January     2   1.2 Joe   Red   -      
# 7 January     3   5.1 Eric  Blue  A      
# 8 January     3   2.1 Mike  Blue  B      
# 9 January     3   6.9 Pete  Red   -      
#10 January     3   6.8 Dave  Red   -      
#11 January     3   4.6 Joe   Red   -

Upvotes: 2

Related Questions