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

Need Your Help

Items appended to listview are not clickable

jquery listview jquery-mobile

When I add content to my listview in jQuery Mobile, they don't trigger the click event when clicked. In my example below, 'Cadillac' is not clickable. Am I doing something wrong?

use .search() or regex to find elements that contain X in the ID

javascript jquery regex dynamic salesforce

I have a group of input boxes that are dynamically built and added to the page. I can control the ID that is placed on the elements but it is wrapped in with a bunch of garbage. For example, I give...

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.