CODEWITHSUNDEEP
rminimum
Josh
Josh

Reputation: 15

How to find a minimum of two columns based on a condition?

I have a dataframe in R looking like that

ID1    <- c(1,2,3,4,5,6,7,8,9)
Value1 <- c(2,3,5,2,5,8,17,3,5)
ID2 <- c(1,2,3,4,5,6,7,8,9)
Value2 <- c(4,6,3,5,8,1,2,8,10)

df <- as.data.frame(cbind(ID1,Value1,ID2,Value2))

Now I am searching for the minimum value of the sum of Value1 and Value2 which has a sum of ID1 and ID2 equal or smaller than 9. Thus, it should show me the minimum of the combination of Value1 + Value2 (not needed to be within the same row) without exceding 9 as the sum of ID1+ID2.

The result should point me to the combination of x in Value1 and y in Value2, which together are the lowest potential values under the condition that ID1+ID2 are <=9.

Thanks in advance!

Upvotes: 0

Views: 1077

Answers (4)

IceCreamToucan
IceCreamToucan

Reputation: 28675

You can use a SQL query to answer the question with the sqldf package

library(sqldf)
#> Loading required package: gsubfn
#> Loading required package: proto
#> Loading required package: RSQLite
df <- structure(list(ID1 = c(1, 2, 3, 4, 5, 6, 7, 8, 9), Value1 = c(2, 
3, 5, 2, 5, 8, 17, 3, 5), ID2 = c(1, 2, 3, 4, 5, 6, 7, 8, 9), 
    Value2 = c(4, 6, 3, 5, 8, 1, 2, 8, 10)), class = "data.frame", row.names = c(NA, 
-9L))

# just get min sum
sqldf('
select
  min(a.Value1 + b.Value2) as min_sum
from
  df a
  join df b 
    on a.ID1 + b.ID2 <= 9
')
#>   min_sum
#> 1       3
 
# show the rows where min sum occurs
sqldf('
select
  a.Value1
  , b.Value2
  , a.ID1
  , b.ID2
from
  df a
  join df b 
    on a.ID1 + b.ID2 <= 9
group by
  1 = 1
having
  a.Value1 + b.Value2 = min(a.Value1 + b.Value2)
')
#>   Value1 Value2 ID1 ID2
#> 1      2      1   1   6

Created on 2021-11-15 by the reprex package (v2.0.1)

Upvotes: 1

Flavien PETIT
Flavien PETIT

Reputation: 106

One possibility

library(dplyr)
goodrow <- filter(df, ID1 + ID2 <= 9) %>% mutate(sumval = Value1 + Value2) %>% filter(sumval == min(sumval))

If I understand well your question, consider using the crossing function. This will compute all the combination of ID1 and ID2

library(dplyr)

df <- as.data.frame(cbind(ID1,Value1))
df2 <- as.data.frame(cbind(ID2,Value2))
df_test <- crossing(df, df2)


goodrow <- filter(df_test, ID1 + ID2 <= 9) %>% mutate(sumval = Value1 + Value2) %>% filter(sumval == min(sumval))

Upvotes: 1

Sotos
Sotos

Reputation: 51582

Another one liner,

filter(transform(df, 'new' = df$Value1 + df$Value2),(df$ID1 + df$ID2 <=9)&(new == min(new)))

Upvotes: 0

user2974951
user2974951

Reputation: 10375

For your specific case

which.min(rowSums(df[rowSums(df[,c("ID1","ID2")])<10,c("Value1","Value2")]))

Upvotes: 1

Related Questions