Made pivot_table using pandas with errors… here is a solution to the problem.
Made pivot_table using pandas with errors
I
have the header of such a data frame 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 20,000+ user_id, 37 cate_id, 5 action_type.
I want to make a pivot table like this one, I do it with excel. The values in the table should be value_count per user_id and per cate_id.
pivot_table
I tried 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)
I received this message.
ValueError: Grouper and axis must be same length
The head of the data frame 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
Solution
I think you need to >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 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