# Sum values of combinations within a group

For a analysis I would like to transform data from:

data <- data.frame( Customer = c("A", "A", "B", "B", "C", "C", "C"), Product = c("X", "Y", "X", "Z", "X", "Y", "Z"), Value = c(10, 15, 5, 10, 20, 5, 10) ) data # Customer Product Value # 1 A X 10 # 2 A Y 15 # 3 B X 5 # 4 B Z 10 # 5 C X 20 # 6 C Y 5 # 7 C Z 10

To:

Product Product Sum Value -------|-------|--------- X |Y |50 X |Z |45 Y |Z |15

Basically I want to get the sum of the value for every product combination within a customer. I guess it could work with some help of the reshape package but I cannot get it to work.

Thanks for your time.

## Answers

Here is one way, in two steps:

1) transform your data into a long data.frame of all pairs within customers. For that, I rely on combn to provide the indices of all possible pairs:

process.one <- function(x) { n <- nrow(x) i <- combn(n, 2) data.frame(Product1 = x$Product[i[1, ]], Product2 = x$Product[i[2, ]], Value = x$Value[i[1, ]] + x$Value[i[2, ]]) } library(plyr) long <- ddply(data, "Customer", process.one) long # Customer Product1 Product2 Value # 1 A X Y 25 # 2 B X Z 15 # 3 C X Y 25 # 4 C X Z 30 # 5 C Y Z 15

2) drop the Customer dimension and aggregate your values:

aggregate(Value ~ ., long[c("Product1", "Product2", "Value")], sum) # Product1 Product2 Value # 1 X Y 50 # 2 X Z 45 # 3 Y Z 15