Nick Knauer Nick Knauer - 3 months ago 9
R Question

Top 3 Rates by ID

I am trying to figure out the top 3 rates by employee but keep the place column.

This is what everything looks like:

place<-c('Dest1', 'Dest2', 'Dest3', 'Dest4', 'Dest5', 'Dest6', 'Dest1', 'Dest2', 'Dest3', 'Dest4', 'Dest5', 'Dest6')
rate <- c(0.5, 0.6, 0.7, 0.2, 0.5, 0.9, 0.8,0.9,0.2,0.5,0.3,0.7)
employee <- c('A','A','A', 'A', 'A', 'A', 'B', 'B', 'B', 'B', 'B', 'B')
employ.data <- data.frame(employee, rate, place)


Dataframe looks like this:

employee rate place
1 A 0.5 Dest1
2 A 0.6 Dest2
3 A 0.7 Dest3
4 A 0.2 Dest4
5 A 0.5 Dest5
6 A 0.9 Dest6
7 B 0.8 Dest1
8 B 0.9 Dest2
9 B 0.2 Dest3
10 B 0.5 Dest4
11 B 0.3 Dest5
12 B 0.7 Dest6


How do I extract the top 3 rates by
employee
and keep the
place
column as well. I know how to do
group_by
in
dplyr
but that will eliminate the
place
column.

I want the final result to look like this:

employee rate place
2 A 0.6 Dest2
3 A 0.7 Dest3
6 A 0.9 Dest6
7 B 0.8 Dest1
8 B 0.9 Dest2
12 B 0.7 Dest6


Thanks!

Answer

After grouping by 'employee', we could arrange the 'rate' in descending order and slice the first 3 rows.

employ.data %>% 
      group_by(employee) %>%
      arrange(desc(rate)) %>% 
      slice(1:3)

Or using filter with rank to subset the rows after grouping by 'employee'.

employ.data %>%
    group_by(employee) %>% 
    filter(rank(-rate) <4)
#     employee  rate  place
#    <fctr> <dbl> <fctr>
#1        A   0.6  Dest2
#2        A   0.7  Dest3
#3        A   0.9  Dest6
#4        B   0.8  Dest1
#5        B   0.9  Dest2
#6        B   0.7  Dest6

Or with top_n which is basically a wrapper for filter with min_rank

employ.data %>%
       group_by(employee) %>%
       top_n(3, wt=rate)