CODEWITHSUNDEEP
pythonpandasdataframegroup-by
lorenzo
lorenzo

Reputation: 407

pandas groupby count co-existances

I want to get the countries affinities by products. I have such a df:

   cntr  prod
0  fr    cheese
1  ger   potato
2  it    cheese
3  it    tomato
4  fr    wine
5  it    wine
6  ger   cabbage
7  fr    cabbage

I was trying to get a co-existence matrix of number of products, which would tell me the countries affinities, as such:

    fr   ger  it
fr       1    2
ger 1         0
it  2    0

my test was first to proceed to do a cross groupby trying to add a 3rd dimension so to get

fr   fr
     ger  1
     it   2
ger  fr   1
     ger
     it   0
it   fr   2
     ger  0
     it

this is what I tryied, but it is failing to add the second layer.. any suggestion?

Upvotes: 1

Views: 40

Answers (1)

jezrael
jezrael

Reputation: 863146

I believe you need merge for cross join with crosstab and if necessary set diagonal to NaN by numpy.fill_diagonal:

df = pd.merge(df, df, on='prod')
df = pd.crosstab(df['cntr_x'], df['cntr_y']).astype(float)
np.fill_diagonal(df.values, np.nan)
print (df)
cntr_y   fr  ger   it
cntr_x               
fr      NaN  1.0  2.0
ger     1.0  NaN  0.0
it      2.0  0.0  NaN

Upvotes: 2

Related Questions