Demetri P Demetri P - 21 days ago 9
Python Question

Pivot Table and Counting

I have a data set indicating who has shopped at which stores.

ID Store
1 C
1 A
2 A
2 B
3 A
3 B
3 C


Can I use a pivot table to determine the frequency of a shopper going to other stores? I'm thinking like a 3X3 matrix where the columns and rows would indicate how many people went to both stores.

Desired output
A B C
A 3 2 2
B 2 3 1
C 2 1 3

Answer

You can create a conditional table of ID and Store with pd.crosstab() and then calculate the matrix product of its transpose and itself, which should produce what you need:

mat = pd.crosstab(df.ID, df.Store)    
mat.T.dot(mat)

#Store  A   B   C
#Store          
#   A   3   2   2
#   B   2   2   1
#   C   2   1   2

Note: Since only two IDs visited store B and C, I suppose the corresponding cells should be 2 instead of 3: