CODEWITHSUNDEEP
rdataframedata-wrangling
LT17
LT17

Reputation: 343

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

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.

Upvotes: 1

Views: 728

Answers (2)

Santiago
Santiago

Reputation: 706

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()

Upvotes: 1

akrun
akrun

Reputation: 886938

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)

Upvotes: 2

Related Questions