CODEWITHSUNDEEP
rcountdplyr
adrian1121
adrian1121

Reputation: 914

Count paired columns and aggregating

I have a dataframe which links two factors such that:

X       Y
A1      B2
A2      B3
A5      D6
B2      A1
B3      A2

And I would like to count the paired occurrences such that:

 i      j      Count
 A1     B2       2
 A2     B3       2
 A5     D6       1

As you can see the order of column i and j doesn't really matter. I have tried with table and dplyr using group_by and summarize(Count = n()) but I cannot make it work because it counts each paired independently so it doesn't aggregate A1-B2 and B2-A1.

I would really appreciate some suggestion as it is a relatively easy task but not trivial for my research.

Upvotes: 0

Views: 30

Answers (1)

Sotos
Sotos

Reputation: 51592

You can use apply with margin 1 to sort rowwise, and then use table function to count, i.e.

table(apply(df, 1, function(i)toString(sort(i))))

#A1, B2 A2, B3 A5, D6 
#     2      2      1

NOTE: You can wrap the result in data.frame and manipulate the output to fit your needs

Upvotes: 2

Related Questions