Calculating a value in R to tie the next-highest ranking value

user_768619 0
Viewed 55

Given the following tibble, I'd like to mutate a new column indicating the additional count necessary to tie the next-highest in rank.

v <- tribble(
  ~rank, ~name, ~count,
  1, "Mary", 100,
  2, "Fred", 96,
  3, "Sue", 90, 
  3, "Michelle", 90,
  4, "Tom", 72
)

I've tried dplyr's lag function (v %>% mutate(toTie = lag(count) - count)). This works, but not when there are ties, as any tied observation after the first is compared to an observation with the same value. For example, after the mutate I have this:

   rank name     count toTie
  <dbl> <chr>    <dbl> <dbl>
1     1 Mary       100    NA
2     2 Fred        96     4
3     3 Sue         90     6
4     3 Michelle    90     0
5     4 Tom         72    18

This output correctly says Sue, who is ranked third, needs 6 to tie Fred, who is second. But because it compares Michelle to Sue (and not Fred), it says Michelle needs none to tie Sue. This is true, but not the intent. Michelle, like Sue, needs 6 to tie second-place Fred.

Any thoughts on a better approach would be most appreciated.

dplyr r
2 Answers

We can get the difference on the lag of the 'distinct' values of 'count' and do a right_join

library(dplyr)
v %>%
     distinct(count) %>% 
     mutate(ToTie = lag(count)- count) %>%
     right_join(v) %>%
     select(names(v), ToTie)

-output

# A tibble: 5 x 4
#   rank name     count ToTie
#  <dbl> <chr>    <dbl> <dbl>
#1     1 Mary       100    NA
#2     2 Fred        96     4
#3     3 Sue         90     6
#4     3 Michelle    90     6
#5     4 Tom         72    18

Or another option is fill

library(tidyr)
v %>%
     mutate(toTie = lag(count) - count, 
            toTie = na_if(toTie, 0)) %>% 
     fill(toTie)
user_37322710

You can use match() to index the difference at first occurrence.

library(dplyr)

v %>%
  mutate(toTie = c(NA, diff(-count))[match(count, count)])

# A tibble: 5 x 4
   rank name     count toTie
  <dbl> <chr>    <dbl> <dbl>
1     1 Mary       100    NA
2     2 Fred        96     4
3     3 Sue         90     6
4     3 Michelle    90     6
5     4 Tom         72    18
user_28352610
Related