2

I know this should be fairly easy to fix, but for some reason I'm not getting the correct output I'm searching for. I have a large dataset where I'm trying to create a new column based on two conditions if a count column is labeled as 1 and if that row has the max payment, then create a new column where it's labeled as 1 and label the others as 0. I created a small example:

ex <- data.frame(EOC = c(1,1,2,2,2),EOC_cnt = c(1,1,1,0,0), pay = c(500,0,200,12,34))

what I want is:

ex <- data.frame(EOC = c(1,1,2,2,2),EOC_cnt = c(1,1,1,0,0), pay = c(500,0,200,12,34)),EOC_cnt1 = c(1,0,1,0,0)

I have:

out <- ex %>% group_by(EOC) %>% mutate(EOC_cnt1 = ifelse(EOC_cnt ==1 & pmax(pay) , "1", "0"))

which works on this smaller example, but when I apply it to my larger dataset I still don't get a single 1 per EOC group. Are there any other ways I could get the result I'm looking for?

(I essentially have duplicates in my EOC_cnt column and would like to create a new column where there is only a single 1 per EOC)

Here is an example of where the answer creates all 0s:

dput(ex2)
structure(list(pay = c(342.39, 48.27, 299.96, 274.12, 342.39, 
121.36), EOC = c(1, 1, 1, 1, 1, 1), EOC_cnt = c(0, 1, 0, 0, 0, 
0)), row.names = c(NA, -6L), class = c("data.table", "data.frame"
), .internal.selfref = <pointer: 0x000001cdf95a1ef0>)
Konrad Rudolph
  • 530,221
  • 131
  • 937
  • 1,214
vizidea
  • 153
  • 7

1 Answers1

2

We don't need ifelse, it can be coercced to binary with + or as.integer

ex %>% 
    group_by(EOC) %>% 
    mutate(EOC_cnt1 = +(pay == max(pay) & EOC_cnt == 1))

If there are duplicates, then we use match to find the first location where both are TRUE

ex %>%
   group_by(EOC) %>%
   mutate(EOC_cnt1 =  +(row_number() %in% which(pay == max(pay) & EOC_cnt == 1)[1]))

Based on the new data 'ex2', it is not clear whether we need to check the max of 'pay' only among the elements where 'EOC_cnt' is 1. In that case, subset the 'pay' based on the 'EOC_cnt' 1 value, take the max and do the comparison

ex2 %>% 
 group_by(EOC) %>% 
 mutate(EOC_cnt2 = +(pay == max(pay[EOC_cnt == 1]) & EOC_cnt == 1))
akrun
  • 874,273
  • 37
  • 540
  • 662
  • I have certain instances in my original dataset where it will return all 0s for a EOC group. I included an example in my question where it's occurring – vizidea Jan 15 '21 at 18:13
  • @vizidea in the new example, the pay is not max where the EOC_cnt is 1. what is your expected output – akrun Jan 15 '21 at 20:20
  • @vizidea I just wonder, whether you want `ex2 %>% group_by(EOC) %>% mutate(EOC_cnt2 = +(pay == max(pay[EOC_cnt == 1]) & EOC_cnt == 1))` – akrun Jan 15 '21 at 21:19