CODEWITHSUNDEEP
rdataframetidyverse
MKR
MKR

Reputation: 1700

Comparing all rows within a dataframe and apply a function conditionally

I have this dataframe and I want to cross-compare all the values inside this data frame.

dat <- tibble::tibble(
name = c("a","b","c"),
value = c(1,2,3))

I want to compare all the row pairs inside this dataframe and in this case I want to divide the smaller number by the bigger number.

The final dataframe should look like this:

a,b,0.5
a,c,0.33
b,c,0.66

Is there a method to achieve this?

Upvotes: 2

Views: 80

Answers (3)

M--
M--

Reputation: 28825

We can use tidyverse:

library(tidyverse)
dat %>% expand(name, name) %>% cbind(expand(dat, value,value)) %>%
        filter(value1>value) %>%
        mutate(ratio=value/value1)

    #>   name name1 value value1     ratio
    #> 1    a     b     1      2 0.5000000
    #> 2    a     c     1      3 0.3333333
    #> 3    b     c     2      3 0.6666667

Or just a doodle in base r:

df <- cbind(expand.grid(dat$name,dat$name), expand.grid(dat$value, dat$value))   
df <- df[order(df[,3], -df[,4]),]    
df <- df[df[,3] < df[,4],]
df$ratio <- df[,3] / df[,4]
df[,-c(3,4)] -> df
df

    #>   Var1 Var2     ratio
    #> 7    a    c 0.3333333
    #> 4    a    b 0.5000000
    #> 8    b    c 0.6666667

Upvotes: 1

akrun
akrun

Reputation: 887088

One option would be

v1 <-  setNames(dat$value, dat$name)
do.call(rbind, combn(v1, 2, FUN = function(x) 
 setNames(data.frame(as.list(names(x)), round(Reduce(`/`, x[order(x)]), 2)), 
      c("col1", "col2", "val")), simplify = FALSE))
#   col1 col2  val
#1    a    b 0.50
#2    a    c 0.33
#3    b    c 0.67

Or an option with fuzzyjoin (inspired from @IceCreamToucan's post)

library(fuzzyjoin)
fuzzy_inner_join(dat, dat, by = "name", match_fun = list(`<`)) %>% 
    transmute(col1 = name.x, col2 = name.y, val = value.x/value.y)
# A tibble: 3 x 3
#  col1  col2    val
#  <chr> <chr> <dbl>
#1 a     b     0.5  
#2 a     c     0.333
#3 b     c     0.667

Upvotes: 2

IceCreamToucan
IceCreamToucan

Reputation: 28675

Using the data.table package, we can join dat with itself on the condition that one value is less than the other, and compute the ratio with the columns of the joined table.

library(data.table)
setDT(dat)

out <- 
  dat[dat, on = .(value < value), 
       .(name1 = x.name,
         name2 = i.name,
         ratio = x.value/i.value)]

out <- out[!is.na(ratio)]

out
#    name1 name2     ratio
# 1:     a     b 0.5000000
# 2:     a     c 0.3333333
# 3:     b     c 0.6666667

Upvotes: 3

Related Questions