Demetri P - 1 year ago 86

Python Question

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

Recommended for you: Get network issues from **WhatsUp Gold**. **Not end users.**

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)
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:

Recommended from our users: **Dynamic Network Monitoring from WhatsUp Gold from IPSwitch**. ** Free Download**