python pivot table of counts

Question

I have a dataframe df as follows:

| id | movie | value |
|----|-------|-------|
| 1  | a     | 0     |
| 2  | a     | 0     |
| 3  | a     | 20    |
| 4  | a     | 0     |
| 5  | a     | 10    |
| 6  | a     | 0     |
| 7  | a     | 20    |
| 8  | b     | 0     |
| 9  | b     | 0     |
| 10 | b     | 30    |
| 11 | b     | 30    |
| 12 | b     | 30    |
| 13 | b     | 10    |
| 14 | c     | 40    |
| 15 | c     | 40    |

I want to create a 2X2 pivot table of counts as follows:

| Value | count(a) | count(b) | count ( C ) |
|-------|----------|----------|-------------|
| 0     | 4        | 2        | 0           |
| 10    | 1        | 1        | 0           |
| 20    | 2        | 0        | 0           |
| 30    | 0        | 3        | 0           |
| 40    | 0        | 0        | 2           |

I can do this very easily in Excel using Row and Column Labels. How can I do this using Python?

score 10 · Answer 1 · answered Nov 16 '17 at 02:39

By using pd.crosstab

pd.crosstab(df['value'],df['movie'])
Out[24]: 
movie          a        b        c     
value                            
0              4        2        0
10             1        1        0
20             2        0        0
30             0        3        0
40             0        0        2

edesz · Accepted Answer · 2017-11-16T03:05:22.840

It can be done this way with Pandas' basic pivot_table functionality and aggregate functions (also need to import NumPy). See the answer in this question and Pandas pivot_table documentation with examples:

import numpy as np
df = ...
ndf = df.pivot_table(index=['value'],
                     columns='movie',
                     aggfunc=np.count_nonzero).reset_index().fillna(0).astype(int)
print(ndf)

      value id      
movie        a  b  c
0         0  4  2  0
1        10  1  1  0
2        20  2  0  0
3        30  0  3  0
4        40  0  0  2

score 1 · Answer 3 · answered Nov 16 '17 at 02:57

Since you are familiar with pivot tables in Excel, I'll give you the Pandas pivot_table method also:

df.pivot_table('id','value','movie',aggfunc='count').fillna(0).astype(int)

Output:

movie     a        b        c     
value                             
 0             4        2        0
 10            1        1        0
 20            2        0        0
 30            0        3        0
 40            0        0        2

python pivot table of counts

3 Answers3