CODEWITHSUNDEEP
rmin
St_Ge
St_Ge

Reputation: 25

R - Aggregate variables with min function and find index related to the min value

I've got a dataframe with 3 columns (ID, Position, Value). There are several Positions and Values per ID. I would like to get the minimum Value for each ID and, in the same aggregate, get the position where the value is the minimum.

For instance : 

Data <- data.frame(c("A","A","A","B","B"),c(10,2,4,1,6),c(0,5,4,3,1))

colnames(Data) <- c("ID","Position","Value")

# The result would be :
Data_min <- data.frame(c("A","B"),c(10,6),c(0,1)) 

# Aggregate function helps me getting the min value per ID : 
aggregate (Data$Value, list(Data$ID), min)

But I haven't found out yet how to get the Position related to the min. I could use a which function with the min value to be looked for but I am pretty sure there is a smarter way to do it.

Upvotes: 1

Views: 1423

Answers (3)

Antonios
Antonios

Reputation: 1939

Something like this?

df = Data %>% 
   group_by(ID) %>% 
   summarize(Position=Position[Value==min(Value)],
             Value=min(Value))

Upvotes: 0

AntoniosK
AntoniosK

Reputation: 16121

An alternative approach to @Antonis summarise answer would be to approach this problem as a filtering process:

Data <- data.frame(c("A","A","A","B","B"),c(10,2,4,1,6),c(0,5,4,3,1))

colnames(Data) <- c("ID","Position","Value")

library(dplyr)

Data %>%
  group_by(ID) %>%
  filter(Value == min(Value)) %>%
  ungroup()

# # A tibble: 2 x 3
#   ID   Position Value
#   <fct>    <dbl> <dbl>
# 1 A        10.0   0   
# 2 B         6.00  1.00

Upvotes: 0

Parfait
Parfait

Reputation: 107567

Consider ave to subset dataframe and return all rows of corresponding Value matches:

Data[Data$Value == ave(Data$Value, Data$ID, FUN=min),]

#   ID Position Value
# 1  A       10     0
# 5  B        6     1

Upvotes: 1

Related Questions