How to create Pandas pivot table that counts common variables?

Question

I have created the following dataframe:

df = pd.DataFrame({
  'Product ID': ['shirt', 'dress', 'shirt', 'pants', 'jacket', 'jacket', 'dress', 'hat'],
  'Discount Group': [1, 2, 3, 2, 1, 3, 4, 5]
})

  Product ID  Discount Group
0      shirt               1
1      dress               2
2      shirt               3
3      pants               2
4     jacket               1
5     jacket               3
6      dress               4
7        hat               5

And I want to create a pivot table where the rows and columns will both be "Discount Group" and the table values will be a count of shared items from "Product ID". For example, 1 (column) and 3 (row) both have "shirt" as a common item, so their value should be one.

It should look like this:

 
    1 2 3 4 5 

1   1 0 1 0 0            
2   0 1 0 1 0   
3   1 0 1 1 0 
4   0 1 0 1 0        
5   0 0 0 0 1

I have tried

df.pivot_table(values='product id', index=['discount group'], columns='discount group', aggfunc='count')

and this returns

    1 2 3 4 5 

1   1 0 0 0 0            
2   0 1 0 0 0   
3   0 0 1 0 0 
4   0 0 0 1 0        
5   0 0 0 0 1

user_14551426 · Answer

I am not sure pivot_table would help here, but here is what you can do

First we groupby on 'Discount Group' and put all 'Product ID' into a list:

df2 = df.groupby('Discount Group')['Product ID'].apply(list).reset_index()
df2

we get

      Discount Group  Product ID
--  ----------------  -------------------
 0                 1  ['shirt', 'jacket']
 1                 2  ['dress', 'pants']
 2                 3  ['shirt', 'jacket']
 3                 4  ['dress']
 4                 5  ['hat']

Next we want to make a 'cartesian product' of this df with itself. For that we do an outer merge on a constant key

df2['key'] = 0
df3 = df2.merge(df2, on = 'key', how = 'outer').drop(columns=['key'])
df3

we get this

      Discount Group_x  Product ID_x           Discount Group_y  Product ID_y
--  ------------------  -------------------  ------------------  -------------------
 0                   1  ['shirt', 'jacket']                   1  ['shirt', 'jacket']
 1                   1  ['shirt', 'jacket']                   2  ['dress', 'pants']
 2                   1  ['shirt', 'jacket']                   3  ['shirt', 'jacket']
 3                   1  ['shirt', 'jacket']                   4  ['dress']
 4                   1  ['shirt', 'jacket']                   5  ['hat']
 5                   2  ['dress', 'pants']                    1  ['shirt', 'jacket']
 6                   2  ['dress', 'pants']                    2  ['dress', 'pants']
 7                   2  ['dress', 'pants']                    3  ['shirt', 'jacket']
 8                   2  ['dress', 'pants']                    4  ['dress']
 9                   2  ['dress', 'pants']                    5  ['hat']
10                   3  ['shirt', 'jacket']                   1  ['shirt', 'jacket']
11                   3  ['shirt', 'jacket']                   2  ['dress', 'pants']
12                   3  ['shirt', 'jacket']                   3  ['shirt', 'jacket']
13                   3  ['shirt', 'jacket']                   4  ['dress']
14                   3  ['shirt', 'jacket']                   5  ['hat']
15                   4  ['dress']                             1  ['shirt', 'jacket']
16                   4  ['dress']                             2  ['dress', 'pants']
17                   4  ['dress']                             3  ['shirt', 'jacket']
18                   4  ['dress']                             4  ['dress']
19                   4  ['dress']                             5  ['hat']
20                   5  ['hat']                               1  ['shirt', 'jacket']
21                   5  ['hat']                               2  ['dress', 'pants']
22                   5  ['hat']                               3  ['shirt', 'jacket']
23                   5  ['hat']                               4  ['dress']
24                   5  ['hat']                               5  ['hat']

Note how we got each pair of 'Discount Group' and corresponding 'Product ID' on a separate row

Next, for each row, we count the number of products that exist in lists in 'Product ID_x' and 'Product ID_y' and put that into 'count' column

df3['count'] = df3.apply(lambda row : len(set(row['Product ID_x'])&set(row['Product ID_y'])), axis = 1)[
df3

so it looks like this

      Discount Group_x  Product ID_x           Discount Group_y  Product ID_y           count
--  ------------------  -------------------  ------------------  -------------------  -------
 0                   1  ['shirt', 'jacket']                   1  ['shirt', 'jacket']        2
 1                   1  ['shirt', 'jacket']                   2  ['dress', 'pants']         0
 2                   1  ['shirt', 'jacket']                   3  ['shirt', 'jacket']        2
 3                   1  ['shirt', 'jacket']                   4  ['dress']                  0
 4                   1  ['shirt', 'jacket']                   5  ['hat']                    0
 5                   2  ['dress', 'pants']                    1  ['shirt', 'jacket']        0
 6                   2  ['dress', 'pants']                    2  ['dress', 'pants']         2
 7                   2  ['dress', 'pants']                    3  ['shirt', 'jacket']        0
 8                   2  ['dress', 'pants']                    4  ['dress']                  1
 9                   2  ['dress', 'pants']                    5  ['hat']                    0
10                   3  ['shirt', 'jacket']                   1  ['shirt', 'jacket']        2
11                   3  ['shirt', 'jacket']                   2  ['dress', 'pants']         0
12                   3  ['shirt', 'jacket']                   3  ['shirt', 'jacket']        2
13                   3  ['shirt', 'jacket']                   4  ['dress']                  0
14                   3  ['shirt', 'jacket']                   5  ['hat']                    0
15                   4  ['dress']                             1  ['shirt', 'jacket']        0
16                   4  ['dress']                             2  ['dress', 'pants']         1
17                   4  ['dress']                             3  ['shirt', 'jacket']        0
18                   4  ['dress']                             4  ['dress']                  1
19                   4  ['dress']                             5  ['hat']                    0
20                   5  ['hat']                               1  ['shirt', 'jacket']        0
21                   5  ['hat']                               2  ['dress', 'pants']         0
22                   5  ['hat']                               3  ['shirt', 'jacket']        0
23                   5  ['hat']                               4  ['dress']                  0
24                   5  ['hat']                               5  ['hat']                    1

and we are almost done -- set the index and unstack:

df3.set_index(['Discount Group_x','Discount Group_y'])['count'].unstack(level = 1)

to get

Discount Group_y    1   2   3   4   5
Discount Group_x                    
               1    2   0   2   0   0
               2    0   2   0   1   0
               3    2   0   2   0   0
               4    0   1   0   1   0
               5    0   0   0   0   1

Another answer that uses less memory

... but somewhat uglier

from itertools import product
s = df.groupby('Discount Group')['Product ID'].apply(list)
pairs = [[(p[0][0],p[1][0]),(p[0][1] ,p[1][1])] for p in product(s.items(),repeat = 2)]
count = [[p[0][0],p[0][1],len(set(p[1][0])&set(p[1][1]))] for p in pairs]
count

produces a list of lists with an Discount ID in first and second columns and the count of overlappping items:

[[1, 1, 2],
 [1, 2, 0],
 [1, 3, 2],
 [1, 4, 0],
 [1, 5, 0],
 [2, 1, 0],
 [2, 2, 2],
 [2, 3, 0],
 [2, 4, 1],
 [2, 5, 0],
 [3, 1, 2],
 [3, 2, 0],
 [3, 3, 2],
 [3, 4, 0],
 [3, 5, 0],
 [4, 1, 0],
 [4, 2, 1],
 [4, 3, 0],
 [4, 4, 1],
 [4, 5, 0],
 [5, 1, 0],
 [5, 2, 0],
 [5, 3, 0],
 [5, 4, 0],
 [5, 5, 1]]

Now we stick it into a df and unstack

pd.DataFrame(count).set_index([0,1]).unstack(level = 1)

produces


    2
1   1   2   3   4   5
0                   
1   2   0   2   0   0
2   0   2   0   1   0
3   2   0   2   0   0
4   0   1   0   1   0
5   0   0   0   0   1

How to create Pandas pivot table that counts common variables?

1 Answers

Another answer that uses less memory