How to create new field that takes max value given group

I have a table that looks like so:

PARTY_ID | PARTYNUM | WEIGHTED_CONF | CONF_SCORE
1           ABC       HIGH            3
1           ABC       HIGH            3
1           ABC       MEDIUM          2
2           DEF       LOW             1
2           DEF       MEDIUM          2
2           DEF       HIGH            3
3           GHI       PERFECT         4
3           GHI       HIGH            3
3           GHI       HIGH            3

I would like to create a new field that takes the highest 'CONF_SCORE' by each 'PARTYNUM' group.

Desired output

PARTY_ID | PARTYNUM | WEIGHTED_CONF | CONF_SCORE | MAX
1           ABC       HIGH            3            3
1           ABC       HIGH            3            3
1           ABC       MEDIUM          2            3
2           DEF       LOW             1            3
2           DEF       MEDIUM          2            3
2           DEF       HIGH            3            3
3           GHI       PERFECT         4            4
3           GHI       HIGH            3            4
3           GHI       HIGH            3            4

I tried this but my output returns '-inf'

new_dataset_final <- new_dataset1 %>%
group_by(PARTYNUM) %>%
  mutate(MAX = max(as.numeric(new_dataset$Conf_Score)))

CodePudding user response：

As r2evans mentions, you're requesting the max of the ungrouped data frame by using the $ notation and specifying new_dataset a second time. This should work:

new_dataset_final <- new_dataset1 %>%
group_by(PARTYNUM) %>%
  mutate(MAX = max(as.numeric(CONF_SCORE)))

CodePudding user response：

In base R we can do

aggregate(CONF_SCORE  ~PARTYNUM, 
        data = new_dataset1, max)

Or to add as a new column, use ave

new_dataset1$MAX <- with(new_dataset1, ave(CONF_SCORE, PARTYNUM, FUN = max))