I have data as follows. The score column is the score of x vs y (which is equivalent to y vs x).
from collections import Counter
import pandas as pd
d = pd.DataFrame([('a','b',1), ('a','c', 2), ('b','a',3), ('b','a',3)],
columns=['x', 'y', 'score'])
x y score
0 a b 1
1 a c 2
2 b a 3
3 b a 3
I want to evaluate the count of the score of each combination, so ('a' vs 'b) and ('b' vs 'a') should be grouped together, i.e.
score
x y
a b {1: 1, 3: 2}
c {2: 1}
However if I do d.groupby(['x', 'y']).agg(Counter)
, ('a', 'b') and ('b', 'a') are not combined together. Is there a way to solve this? Thanks!
score
x y
a b {1: 1}
c {2: 1}
b a {3: 2}