use pandas to make pivot_table but an error occur

Question

I have the head of a dataframe like this and I want to make a pivot_table.

    user_id     item_id cate_id action_type action_date
0   11482147    492681  1_11    view          15
1   12070750    457406  1_14    deep_view     15
2   12431632    527476  1_1     view          15
3   13397746    531771  1_6     deep_view     15
4   13794253    510089  1_27    deep_view     15

There are 20000+ user_id,and 37 cate_id, 5 action_type. I want to make a pivot_table like this which I do it with excel.The values in the table should be the value_count for every user_id with every cate_id. pivot_table I've try the following code.

user_cate_table = pd.pivot_table(user_cate_table2,index = ['user_id','cate_id'],columns=np.unique(train['action_type']),values='action_type',aggfunc=np.count_nonzero,fill_value=0)

And I got this message.

ValueError: Grouper and axis must be same length

the head of dataframe user_cate_table2.

    user_id     item_id cate_id action_type
0   11482147    492681  1_11    1.0
1   12070750    457406  1_14    2.0
2   12431632    527476  1_1     1.0
3   13397746    531771  1_6     2.0
4   13794253    510089  1_27    2.0
5   14378544    535335  1_6     2.0
6   1705634     535202  1_10    1.0
7   6943823     478183  1_3     2.0
8   5902475     524378  1_6     1.0

Answer 1

I think you need groupby + size + unstack:

df1 = df.groupby(['user_id','cate_id', 'action_type']).size().unstack(fill_value=0)
print (df1)
action_type       deep_view  view
user_id  cate_id                 
11482147 1_11             0     1
12070750 1_14             1     0
12431632 1_1              0     1
13397746 1_6              1     0
13794253 1_27             1     0

Another solution with pivot_table:

df1 = df.pivot_table(index=['user_id','cate_id'], 
                     columns='action_type', 
                     values='item_id', 
                     aggfunc=len, 
                     fill_value=0)
print (df1)
action_type       deep_view  view
user_id  cate_id                 
11482147 1_11             0     1
12070750 1_14             1     0
12431632 1_1              0     1
13397746 1_6              1     0
13794253 1_27             1     0