CODEWITHSUNDEEP
rdplyr
cowboy
cowboy

Reputation: 661

Can we use R/Dplyr to Rank Based on Values in 2 Different Columns?

Using this simplistic example below, my goal is to create a function that will create a current_rank based on the Category_Rank (ascending) and Item_Points (descending), after removing Categories that achieve at least 15 total points. The idea is that once I update the underlying data the ranks (priorities) will change and allow for a new focus on items to obtain.

below is a sample dataset and the code i have started. My code successfully seems to filter out the Categories that have met the minimum point threshold (15) before dropping that category and moving on, but the current_rank isnt working as I would like it to.

sample2 <- tribble(
  ~Category , ~Category_Rank, ~Item, ~Item_Points, ~Points_Obtained,
  'Shelter', 2, 'Tent',       5, 0,
  'Shelter', 2, 'House',     10, 0,
  'Shelter', 2, 'Hotel',     20, 0,
  'Shelter', 2, 'Yurt',       2, 0,
  'Food',    1, 'Protein',    5, 5,
  'Food',    1, 'Fruit',      2, 0,
  'Food',    1, 'Vegetables', 10, 10,
  'Food',    1, 'Water',     20, 0,
  'Clothes',  3, 'Pants',    20, 0,
  'Clothes',  3, 'Shirts',    5, 0,
  'Clothes',  3, 'Socks',    10, 0,
  'Clothes',  3, 'Shoes',     2, 0,
  
  
)

sample2 %>% 
  group_by(Category) %>% 
  mutate(progress = 
           case_when(
             sum(Points_Obtained) >=15 ~ 'Met',
             TRUE ~ 'Not Met')) %>% 
  filter(progress != 'Met') %>% 
  ungroup() %>% 
  mutate(current_rank = order(order(Category_Rank, Item_Points , decreasing = T)))

The output I want to see based on this code would be

Category    Category_Rank     Item     Item_Points     Points_Obtained     progress     current_rank
Shelter     2                Tent        5               0                 Not Met         3
Shelter     2                House       10              0                 Not Met         2
Shelter     2                Hotel       20              0                 Not Met         1
Shelter     2                Yurt        2               0                 Not Met         4
Clothes     3                Pants       20              0                 Not Met         5
Clothes     3                Shirts      5               0                 Not Met         7
Clothes     3                Socks       10              0                 Not Met         6
Clothes     3                Shoes       2               0                 Not Met         8

the current_rank isnt working as expected.

Any thoughts?

Upvotes: 1

Views: 35

Answers (1)

BellmanEqn
BellmanEqn

Reputation: 789

Just apply rev() to the column you want to sort in reverse:

sample2 %>% 
  group_by(Category) %>% 
  mutate(progress = 
           case_when(
             sum(Points_Obtained) >=15 ~ 'Met',
             TRUE ~ 'Not Met')) %>% 
  filter(progress != 'Met') %>% 
  ungroup() %>% 
  mutate(current_rank = order(rev(Category_Rank), Item_Points, decreasing = T))

# A tibble: 8 x 7
  Category Category_Rank Item   Item_Points Points_Obtained progress current_rank
  <chr>            <dbl> <chr>        <dbl>           <dbl> <chr>           <int>
1 Shelter              2 Tent             5               0 Not Met             3
2 Shelter              2 House           10               0 Not Met             2
3 Shelter              2 Hotel           20               0 Not Met             1
4 Shelter              2 Yurt             2               0 Not Met             4
5 Clothes              3 Pants           20               0 Not Met             5
6 Clothes              3 Shirts           5               0 Not Met             7
7 Clothes              3 Socks           10               0 Not Met             6
8 Clothes              3 Shoes            2               0 Not Met             8

Upvotes: 2

Related Questions