How to loop through a dataframe with multiple values to use the max to identify the value

Question

I am trying to find the max value of a group and the associated row information for that max value where there are different groups repeated.

For example, the dataset looks as follows:

   newid sex visitnum sbpval
36 13580   M        2     NA
37 13580   M        3    124
38 13580   M        4    116
39 21525   F        2    410
40 21525   F        3    116

I would like the output to look like this:

  newid sex visitnum sbpval
1 13580   M        3    124
2 21525   F        2    410

I am trying to create a loop that loops through but is having a hard time figuring out how to group them.

This is the code I have so far:

> for (i in 1:length(df)){
+   maxsbp = max(i, na.rm = F)
+   max = cbind(maxsbp,max)
+   result = cbind(result, df[[i]])
+ }
> result

This is what it's giving me:

      [,1] [,2] [,3] [,4] [,5] [,6] [,7]    [,8] [,9] [,10]
[36,] 32   2    NA   32   2    NA   "13580" "M"  2    NA   
[37,] 32   3    124  32   3    124  "13580" "M"  3    124  
[38,] 32   4    116  32   4    116  "13580" "M"  4    116  
[39,] 33   2    410  33   2    410  "21525" "F"  2    410  
[40,] 33   3    116  33   3    116  "21525" "F"  3    116

Welcome to StackOverflow! I see you have multiple questions without any solutions accepted in your history. Please take time to accept the best solution by clicking the checkmark and also consider upvoting the other solutions that work as well. — Eric, Mar 13 '21 at 02:29

score 1 · Accepted Answer · answered Mar 12 '21 at 21:12

Is this what you want

subset(
  df,
  ave(sbpval, newid, FUN = function(x) max(x, na.rm = TRUE)) == sbpval
)

which gives

   newid sex visitnum sbpval
37 13580   M        3    124
39 21525   F        2    410

Data

> dput(df)
structure(list(newid = c(13580L, 13580L, 13580L, 21525L, 21525L
), sex = c("M", "M", "M", "F", "F"), visitnum = c(2L, 3L, 4L,
2L, 3L), sbpval = c(NA, 124L, 116L, 410L, 116L)), class = "data.frame", row.names = c("36",     
"37", "38", "39", "40"))

score 1 · Answer 2 · answered Mar 12 '21 at 23:05

1

Another option with slice_max

library(dplyr)
df %>% 
     group_by(newid) %>%
     slice_max(sbpval) %>%
     ungroup

answered Mar 12 '21 at 23:05

akrun

874,273
37
540
662

score 1 · Answer 3 · answered Mar 13 '21 at 02:28

Base R Solution

df <- structure(list(newid = c(13580L, 13580L, 13580L, 21525L, 21525L
), sex = c("M", "M", "M", "F", "F"), visitnum = c(2L, 3L, 4L,
2L, 3L), sbpval = c(NA, 124L, 116L, 410L, 116L)), class = "data.frame", row.names = c("36",
"37", "38", "39", "40"))


df <- merge(aggregate(sbpval ~ sex, max, data = df), df)

# Reorder the columns
df[, c(3, 1, 4, 2)]

#>   newid sex visitnum sbpval
#> 1 21525   F        2    410
#> 2 13580   M        3    124

^{Created on 2021-03-12 by the reprex package (v0.3.0)}

How to loop through a dataframe with multiple values to use the max to identify the value

3 Answers3