Calculate ratio of specefic rows

user_13052464 0
Viewed 89

I have data like this

      col1  col2  col3  col4
A      4     6     12    10
B      16   12      8     5
C       4    5     20     9  
D       4   10     10    18
E      10   10     12     9

I want to calculate ratio of rows A and B, C and D and B and E i.e create new row that contain the result of A/B and other row that contain the result of C/D and other row that conatin the result of B and E

so the result will be

        col1    col2  col3  col4
    A      4       6     12    10
    B      16     12      8     5
    C       4      5     20     9  
    D       4     10     10    18
    E      10     10     12     9 
   A/B     0.25   0.5    1.5    2
   C/D      1     0.5     2    0.5
   B/E     1.6    1.2   0.66   0.55

I want to know if there's any way in case where there's a lot of rows and lot of cases we want to calculate (case where we want to calculate lot of ratios )

Thank you

dplyr rows data-manipulation dataframe r
3 Answers

Another possible solution:

library(tidyverse)

map2_dfr(c("A", "C", "B"), c("B", "D", "E"), 
 ~ (df[.x,] / df[.y,]) %>% `row.names<-`(str_c(.x,"/", .y))) %>% bind_rows(df, .)

#>      col1 col2       col3       col4
#> A    4.00  6.0 12.0000000 10.0000000
#> B   16.00 12.0  8.0000000  5.0000000
#> C    4.00  5.0 20.0000000  9.0000000
#> D    4.00 10.0 10.0000000 18.0000000
#> E   10.00 10.0 12.0000000  9.0000000
#> A/B  0.25  0.5  1.5000000  2.0000000
#> C/D  1.00  0.5  2.0000000  0.5000000
#> B/E  1.60  1.2  0.6666667  0.5555556
user_115644870

In base R, we can do this as

v1 <- c("A", "C", "B")
v2 <- c("B", "D", "E")
df[paste(v1, v2, sep = "/"),] <- df[v1,]/df[v2,]

-output

> df
     col1 col2       col3       col4
A    4.00  6.0 12.0000000 10.0000000
B   16.00 12.0  8.0000000  5.0000000
C    4.00  5.0 20.0000000  9.0000000
D    4.00 10.0 10.0000000 18.0000000
E   10.00 10.0 12.0000000  9.0000000
A/B  0.25  0.5  1.5000000  2.0000000
C/D  1.00  0.5  2.0000000  0.5000000
B/E  1.60  1.2  0.6666667  0.5555556

data

df <- structure(list(col1 = c(4L, 16L, 4L, 4L, 10L), col2 = c(6L, 12L, 
5L, 10L, 10L), col3 = c(12L, 8L, 20L, 10L, 12L), col4 = c(10L, 
5L, 9L, 18L, 9L)), class = "data.frame", row.names = c("A", "B", 
"C", "D", "E"))
user_37322710

You can do this in base R without using any external package.

For better control of the operation, I've define a ratio_df for storing the numerator and denominator information of the requested division.

The idea of this solution is to use lapply to go through all rows in ratio_df and perform the actual division. Then rbind the result of each lapply iteration using do.call. The outer-most rbind is used to bind your original df together with the lapply results.

ratio_df <- data.frame(numerator = c("A", "C", "B"), 
                       denominator = c("B", "D", "E"))

rbind(df, 
      do.call(rbind, 
              setNames(
                lapply(1:nrow(ratio_df), function(x) df[ratio_df[x, 1],]/df[ratio_df[x, 2],]), 
                paste(ratio_df[, 1], ratio_df[, 2], sep = "/")
                )
              )
      )

     col1 col2       col3       col4
A    4.00  6.0 12.0000000 10.0000000
B   16.00 12.0  8.0000000  5.0000000
C    4.00  5.0 20.0000000  9.0000000
D    4.00 10.0 10.0000000 18.0000000
E   10.00 10.0 12.0000000  9.0000000
A/B  0.25  0.5  1.5000000  2.0000000
C/D  1.00  0.5  2.0000000  0.5000000
B/E  1.60  1.2  0.6666667  0.5555556
user_166474960
Related