Return rows in an R dataframe where there is the max for each group based on a condition

user_9926677 0
Viewed 71

Hi I have a simple data frame with 2 columns

scenario    item   ROA
0 -10 0      A   6.0
0   0 10     A   2.1
0  10 20     A   3.0
0 -30 0      B   0.5
10 0  0      B   1.0

What I need is to create a way to be able to condition the scenarios for each item (only positive or negative) and return the row where there is the max value of ROA

For example if I condition A to use only scenarios positive and B to only negative I should get

    scenario    item   ROA
     0  10 20     A   3.0
     0 -30 0      B   0.5

the values on the scenario column are strings not numbers, but there is only one negative sign, so it can be checked with a condition if it contains the "-" sign.

the condition for being negative scenario is just that it has a negative char in the string, the condition for being positive is that it does not.

Here you can create some sample data

scenario    <- c("0 -10 0","0 0 10","0 10 20","0 -30 0","0 -30 0")
item <- c("A","A","A","B","B")
ROA <- c(6,2.1,3,0.5,1)

dfts <- data.frame(scenario=scenario, item=item, ROA=ROA )

Thanks for the help

dplyr r
4 Answers

Here is just another approach: I used strsplit function from base R which splits a string based on sep argument which here is a single white space " ". This function returns a list the same length as x where ith element of the list is the separated elements of x[i]. Here each elements of your scenario is of length 1. Then I chose the second separated element and transformed it to numeric class:

library(dplyr)
library(purrr)

dfts %>%
  filter(map2_lgl(scenario, item, ~ (.y == "A" & as.numeric(strsplit(.x, " ")[[1]][2]) > 0) |
                    (.y == "B" & as.numeric(strsplit(.x, " ")[[1]][2]) < 0)))

  scenario item ROA
1  0 10 20    A 3.0
2  0 -30 0    B 0.5
user_143145200

A base R option

subset(
  subset(
    dfts,
    (1 + grepl("-", scenario)) == match(item, c("A", "B"))
  ),
  ave(ROA, item, FUN = max) == ROA
)

gives

  scenario item ROA
3  0 10 20    A 3.0
4  0 -30 0    B 0.5
user_121587570

Are you looking for this?

library(dplyr)

dfts %>%
  filter((!grepl('-', scenario) & item == 'A') | 
           grepl('-', scenario) & item == 'B') %>%
  group_by(item) %>%
  slice(which.max(ROA)) %>%
  ungroup

#  scenario item    ROA
#  <chr>    <chr> <dbl>
#1 0 10 20  A       3  
#2 0 -30 0  B       0.5
user_39629140

We can use

library(dplyr)
library(stringr)
dfts %>%
     filter(!str_detect(scenario, '-') & item == 'A'| 
        str_detect(scenario, '-') & item == 'B') %>% 
     group_by(item) %>% 
     slice_max(n = 1, order_by = ROA)
user_37322710
Related