Home > Net >  Find the minimum value in a column that is common to a grouping in another column
Find the minimum value in a column that is common to a grouping in another column

Time:11-09

Suppose I have a simple dataframe like this one:

mydata <- data.frame(Name = c(rep("A",4),rep("B",3),rep("C",2),rep("D",2),"E"),
           Value = c(5,2,3,6,1,3,8,13,3,5,3,3)

I want to find which is the minimum Value that is shared by all Name (in this example, 3). So far I've created a column with the minimum Value for each Name with:

mydata$min <- ave(mydata$Value, mydata$Name, FUN = min)

but it's not the result I am looking for. I think I can group the Name with a combination of unique() and mutate() (from dplyr) but I am not sure.

CodePudding user response:

We split the 'Value' by the 'Name' get the intersect using Reduce and apply min on it

mydata$min <- min(Reduce(intersect, split(mydata$Value, mydata$Name)))

Or with dplyr

library(dplyr)
mydata %>%
  group_by(Value) %>% 
  mutate(flag = n_distinct(Name) == n_distinct(mydata$Name)) %>% 
  ungroup %>%
  mutate(Min = min(Value[flag]), flag = NULL)

CodePudding user response:

You can find the result by first building each group's vector of doubles, then finding the intersect of the vectors, and finally extracting the minimum value.

Using the tidyverse you can do so with:

library(dplyr)
library(purrr)

mydata <- data.frame(
  Name = c(rep("A", 4), rep("B", 3), rep("C", 2), rep("D", 2), "E"),
  Value = c(5, 2, 3, 6, 1, 3, 8, 13, 3, 5, 3, 3)
)

mydata %>%
  group_by(Name) %>%
  summarize(Values = list(Value)) %>%
  pull(Values) %>%
  reduce(intersect) %>%
  min()
  • Related