Mutate Top N Rows Without Throwing Away the Other Rows

I have the following data.frame below. I would like to create a new column w (for weight). w should equal 1 / n for the industries that have the n highest returns for each given date and should equal 0 for the rest of the industries. I can group_by(date) and use top_n(3, wt = return) to filter the top industries and then mutate(w = 1/n), but how can I mutate without throwing away the other industries where w = 0?

structure(list(date = structure(c(16556, 16556, 16556, 16556, 
16556, 16556, 16556, 16556, 16556, 16556, 16587, 16587, 16587, 
16587, 16587, 16587, 16587, 16587, 16587, 16587, 16617, 16617, 
16617, 16617, 16617, 16617, 16617, 16617, 16617, 16617), class = "Date"), 
    industry = c("Hlth", "Txtls", "BusEq", "Fin", "ElcEq", "Food", 
    "Beer", "Books", "Cnstr", "Carry", "Clths", "Txtls", "Fin", 
    "Games", "Cnstr", "Meals", "Hlth", "Hshld", "Telcm", "Rtail", 
    "Smoke", "Games", "Clths", "Rtail", "Servs", "Meals", "Food", 
    "Hlth", "Beer", "Trans"), return = c(4.89, 4.37, 4.02, 2.99, 
    2.91, 2.03, 2, 1.95, 1.86, 1.75, 4.17, 4.09, 1.33, 1.26, 
    0.42, 0.29, 0.08, -0.11, -0.45, -0.48, 9.59, 6, 5.97, 5.78, 
    5.3, 4.15, 4.04, 3.67, 3.51, 3.27)), row.names = c(NA, -30L
), class = c("tbl_df", "tbl", "data.frame"))

# A tibble: 30 x 3
   date       industry return
   <date>     <chr>     <dbl>
 1 2015-05-01 Hlth       4.89
 2 2015-05-01 Txtls      4.37
 3 2015-05-01 BusEq      4.02
 4 2015-05-01 Fin        2.99
 5 2015-05-01 ElcEq      2.91
 6 2015-05-01 Food       2.03
 7 2015-05-01 Beer       2   
 8 2015-05-01 Books      1.95
 9 2015-05-01 Cnstr      1.86
10 2015-05-01 Carry      1.75
# ... with 20 more rows

EDIT: How would you handle ties? Suppose there is a tie for third place. The third place weight should be split between 3rd and 4th place (assuming only 2 are tied) with weights of (1/n)/2. The 1st and 2nd place weights stay at 1/n.

EDIT: Suppose n = 3. The top 3 A2 values for each A1 should get a weight w of 1/3 if there are no ties. If there is a tie for 3rd place (T3), then we have (1st, 2nd, T3, T3) and I would like weights to be 1/3, 1/3, 1/6, 1/6 to maintain a total weight of 1. This is only for 3rd place however. (1st, T2, T2) should have weights of 1/3, 1/3, 1/3. (T1, T1, T2, T2) should have weights of 1/3, 1/3, 1/6, 1/6, etc.

structure(list(A1 = structure(c(1L, 1L, 1L, 1L, 1L, 2L, 2L, 2L, 
    2L), .Label = c("A", "B"), class = "factor"), A2 = c(1, 3, 3, 
    4, 5, 6, 7, 8, 8)), row.names = c(NA, -9L), class = "data.frame")

The output for df should be:

> df
  A1 A2  w
1  A  1  0 
2  A  3  0.1666
3  A  3  0.1666 
4  A  4  0.3333
5  A  5  0.3333
6  B  6  0
7  B  7  0.3333
8  B  8  0.3333
9  B  8  0.3333
6

2 Answers

We could create a condition with ifelse. After grouping by 'date', arrange the dataset based on the 'date', and 'return' in descending order, then create the 'w' by creating the condition that if the row_number() is less than 'n', then divide 'return' by 'n' or else return 0

n <- 3
df1 %>%
   group_by(date) %>%
   arrange(date, -return) %>% 
   mutate(w = ifelse(row_number() <= n, return/n, 0))

If we are using top_n, then create the column 'w' in the filtered dataset and join with the original

df1 %>% 
  group_by(date) %>% 
  top_n(return, n = 3) %>% 
  mutate(w = return/n()) %>% 
  right_join(df1)  %>% 
  mutate(w = replace_na(w, 0))
2

We can group by date then sort the return variable get the last 3 enteries (top 3) and return return/n or else 0.

library(dplyr)
n <- 3
df %>%
  group_by(date) %>%
  mutate(w = ifelse(return %in% tail(sort(return), n), return/n, 0))


# date       industry return     w
#   <date>     <chr>     <dbl> <dbl>
# 1 2015-05-01 Hlth       4.89  1.63
# 2 2015-05-01 Txtls      4.37  1.46
# 3 2015-05-01 BusEq      4.02  1.34
# 4 2015-05-01 Fin        2.99  0   
# 5 2015-05-01 ElcEq      2.91  0   
# 6 2015-05-01 Food       2.03  0   
# 7 2015-05-01 Beer       2     0   
#....

The base R equivalent of the same logic using ave

ave(df$return, df$date, FUN = function(x) ifelse(x %in% tail(sort(x), n), x/n, 0))

EDIT

As mentioned in comments, in case of ties OP wants to return (1/n)/2 or divide by number of ties we have.

For this I have created a new easier dataframe which makes it easy to understand what is going on.

df <- data.frame(A1 = rep(c("A", "B"),c(5, 4)), A2 = 1:9)
df$A2[2] <- 3

If we use the current code it gives

df %>%
   group_by(A1) %>%
   mutate(w = ifelse(A2 %in% tail(sort(A2), n), A2/n, 0))

  # A tibble: 9 x 3
  # Groups:   A1 [2]
#  A1       A2     w
# <fct> <int> <dbl>
#1 A         1  0   
#2 A         3  1   
#3 A         3  1   
#4 A         4  1.33
#5 A         5  1.67
#6 B         6  0   
#7 B         7  2.33
#8 B         8  2.67
#9 B         9  3   

which is not what we want. To avoid that, we can group by A2 again and for only those rows where w!=0 we divide it by number of occurrences of A2.

df %>%
  group_by(A1) %>%
  mutate(w = ifelse(A2 %in% tail(sort(A2), n), A2/n, 0)) %>%
  group_by(A2) %>%
  mutate(w1 = ifelse(w != 0, w/n(), w)) %>%
  ungroup()

# A1       A2     w    w1
#  <fct> <dbl> <dbl> <dbl>
#1 A         1  0     0   
#2 A         3  1     0.5 
#3 A         3  1     0.5 
#4 A         4  1.33  1.33
#5 A         5  1.67  1.67
#6 B         6  0     0   
#7 B         7  2.33  2.33
#8 B         8  2.67  2.67
#9 B         9  3     3   

Another EDIT

Turns out we just want to divide w only for the last group present. Moreover, the sum of all the w in each group should sum up to 1. For the updated dataset we can do

n <- 3

temp_df <- df %>%
            group_by(A1) %>%
            top_n(n, A2)


 temp_df %>%
     arrange(A1, A2) %>%
     mutate(w = ifelse(A2 == A2[1], 
    (1 - (1/n * sum(A2 != A2[1])))/sum(A2 == A2[1]), 1/n)) %>%
     bind_rows(anti_join(df, temp_df) %>%
                      mutate(w = 0)
    ) %>%
     arrange(A1, A2)


# A1       A2     w
#  <fct> <dbl> <dbl>
#1 A         1 0    
#2 A         3 0.167
#3 A         3 0.167
#4 A         4 0.333
#5 A         5 0.333
#6 B         6 0    
#7 B         7 0.333
#8 B         8 0.333
#9 B         8 0.333

Let's try another variation where we keep all the values of the group same.

df1 = df
df1$A2[6:9] <- 10


 temp_df <- df1 %>%
             group_by(A1) %>%
             top_n(n, A2)


  temp_df %>%
       arrange(A1, A2) %>%
       mutate(w = ifelse(A2 == A2[1], 
      (1 - (1/n * sum(A2 != A2[1])))/sum(A2 == A2[1]), 1/n)) %>%
       bind_rows(anti_join(df1, temp_df) %>%
                      mutate(w = 0)
       ) %>%
       arrange(A1, A2)


#  A1       A2     w
#  <fct> <dbl> <dbl>
#1 A         1 0    
#2 A         3 0.167
#3 A         3 0.167
#4 A         4 0.333
#5 A         5 0.333
#6 B        10 0.25 
#7 B        10 0.25 
#8 B        10 0.25 
#9 B        10 0.25 

The logic is we select the top 3 A2 values along with their groups using top_n. Using anti_join we get all the rows which are not in top 3 and assign a fixed weight w to them as 0. For the rows which are included in top 3 we get the last group rows and assign them the weight which is remaining after assigning the weights to non-last groups.

5

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Elena Rostova

Elena Rostova

Lead Health, Wellness & Medical Journalist

Elena Rostova holds a Master's degree in Public Health Journalism. She covers groundbreaking medical research, holistic wellness trends, mental health awareness, and nutritional science.

Share this article
Twitter Facebook Pinterest