Demetri P Demetri P - 10 months ago 70
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 3 2 2
B 2 3 1
C 2 1 3

Answer Source

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)

#Store  A   B   C
#   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: