Sumproduct by condition in a data frame in R

user_12073743 0
Viewed 854

Consider the following data frame:

df <- data.frame(row_id = c("r1","r2","r3","r4","r1","r2","r3","r4"),
                 v1 = c(3,2,5,2,5,2,6,4),
                 v2 = c(4,3,5,3,7,4,6,7))

I want take the sum-product by "row_id". That is, for the rows with the row_id: "r1" I want to do the following calculation: (3*4)+(5*7). And so on.

Thus, I will finally have the following matrix: 

df1 <- data.frame(row_id = c("r1","r2","r3","r4"),
                 v1 = c(47,14,61,34))

Any help will be really appreciated.

Thanks.

sumproduct vector r
5 Answers

Similar but slightly shorter:

dplyr::count(df, row_id, wt = v1*v2)
user_68518250

using base R, we could also transform then aggregate

 aggregate(tot~row_id,transform(df,tot = v1*v2),sum)

  row_id tot
1     r1  47
2     r2  14
3     r3  61
4     r4  34

or you could also do:

c(by(df[-1],df[1],do.call,what = "%*%"))
r1 r2 r3 r4 
47 14 61 34
user_83802720 
library(dplyr)
df %>%
    mutate(p = Reduce("*", .[-1])) %>%
    group_by(row_id) %>%
    summarise(v = sum(p))

OR

tapply(Reduce("*", df[-1]), df$row_id, sum)
#r1 r2 r3 r4 
#47 14 61 34
user_71289340

Using base R with split and %*%

sapply(split(df[-1], df$row_id), function(x) x[,1] %*% x[,2])
# r1 r2 r3 r4 
#47 14 61 34

Or another option is rowsum from base R

rowsum(with(df, v1 * v2), group = df$row_id)
#    [,1]
#r1   47
#r2   14
#r3   61
#r4   34

or using data.table

library(data.table)
setDT(df)[, do.call(`%*%`, .SD), row_id]
#   row_id V1
#1:     r1 47
#2:     r2 14
#3:     r3 61
#4:     r4 34
user_37322710

using dplyr:

library(dplyr)
df %>% group_by(row_id) %>% summarize(sum(v1*v2))

# which gives:
# A tibble: 4 x 2
  row_id `sum(v1 * v2)`
  <fct>           <dbl>
1 r1                 47
2 r2                 14
3 r3                 61
4 r4                 34
user_31725560
Related