I want to check if for each pair, an opposite one exists, e.g. pair (1,2) and pair (2,1), and if so - sum the counter value for both pairs.
As a result, I want to have an output in a form of a table like this one:
We can sort the first two column row wise with apply (MARGIN=1), cbind with the third column ('d1'), get an index of duplicates of the first two column ('i1'). Convert to 'data.table' (setDT(d2)), grouped by 'user_A', and 'user_B', get the sum of 'Counter' and the first row of 'i1'.
mutate() is used to redefine user_A and user_B such that the smaller of the two values is always in the first column. Then the auxiliary column user_A2 is dropped. The data is grouped by user_A and user_B and two summaries are calculated for each group: The sum over counter and whether there was more than one value. This latter uses n() which counts the number of rows in the group.
The last line uses as.data.frame to transform back to a data frame. This is only needed, if you insist on having a data frame as your result.
Asked in February 2016Viewed 3,559 timesVoted 8Answered 2 times