Divide one table by another, with matching index

user_13284811 0
Viewed 158

I have two table with a shared index, I want to divide one by another. This could be done with division on two data frames. But It seems arbitrary (how would I know I am dividing the right number?) and does not preserve index, so I want to do this division by matching rows with the same index. What's the best way to do this? Is there a best practice in terms of table division in this case?

tb1 <- data.frame(index = c(1, 2, 3), total_1 = c(100, 450, 300), total_2 = c(20, 39, 60))
tb2 <- data.frame(index = c(1, 2, 3), unit_1 = c(4, 2, 3), unit_2 = c(2, 3, 6))
tb1[,-1]/tb2[,-1]
  total_1 total_2
1      25      10
2     225      13
3     100      10

Another case, two col of index must match.

tb2 <- data.frame(index_1 = c("a", "b", "b"), index_2 = c("c", "d", "b"), unit_1 = c(4, 2, 3), unit_2 = c(2, 3, 6))
tb1 <- data.frame(index_1 = c("a", "b", "b"), index_2 = c("c", "d", "b"), total_1 = c(100, 450, 300), total_2 = c(20, 39, 60))
r
3 Answers

If both data have the same index and the number of rows are same. One way is to order by 'index' in both data to enforce that they are in the same order. Then do the division

tb1new <- tb1[order(tb1$index),]
tbl2new <- tb2[order(tb2$index),]
tb1new[-1] <- tbl1new[-1]/tbl2new[-1]

Or we can make a check on both 'index' first and use that condition to do the division

i1 <- all.equal(tbl1$index, tbl2$index)
if(i1) tb1[-1]/tbl2[-1]

Or another option in a join

library(data.table)
nm1 <- c('total_1', 'total_2')
nm2 <- c('unit_1', 'unit_2')
setDT(tb1)[tb2,  (nm1) :=  .SD/mget(nm2), on = .(index), .SDcols = nm1]
user_37322710

You can perform a join and divide the columns. In base R :

result <- merge(tb1, tb2, by = c('index_1', 'index_2'))
result

#  index_1 index_2 total_1 total_2 unit_1 unit_2
#1       a       c     100      20      4      2
#2       b       b     300      60      3      6
#3       b       d     450      39      2      3

total_cols <- grep('total', names(result), value = TRUE)
unit_cols <- grep('unit', names(result), value = TRUE)

result[total_cols]/result[unit_cols]

#  total_1 total_2
#1      25      10
#2     100      10
#3     225      13
user_39629140

Maybe this is not the most efficient solution but here is another way:

library(dplyr)
library(tidyr)

# For one index matching

tb1 %>%
  left_join(tb2, by = "index") %>%
  mutate(result_1 = get(paste("total", 1, sep = "_")) / get(paste("unit", 1, sep = "_")),
         result_2 = get(paste("total", 2, sep = "_")) / get(paste("unit", 2, sep = "_")))


  index result_1 result_2
1     1       25       10
2     2      225       13
3     3      100       10

# For two indices matching

tb1 %>%
  left_join(tb2, by = c("index_1", "index_2")) %>%
  mutate(result_1 = get(paste("total", 1, sep = "_")) / get(paste("unit", 1, sep = "_")),
         result_2 = get(paste("total", 2, sep = "_")) / get(paste("unit", 2, sep = "_"))) %>%
  select(!starts_with(c("total", "unit")))

  index_1 index_2 result_1 result_2
1       a       c       25       10
2       b       d      225       13
3       b       b      100       10
user_143145200
Related