pandas the row data transform to the column data

Question

I have a dataframe like ：

user_id category    view    collect
1   1   a   2   3
2   1   b   5   9
3   2   a   8   6
4   3   a   7   3
5   3   b   4   2
6   3   c   3   0
7   4   e   1   4

how to change it to a new dataframe ，each user_id can appear once，then the category with the view and collect appears to the columns ,if there is no data ,fill it with 0, like this :

user_id a_view  a_collect   b_view  b_collect   c_view  c_collect   d_view  d_collect   e_view  e_collect
1   2   3   5   6   0   0   0   0   0   0
2   8   6   0   0   0   0   0   0   0   0
3   7   3   4   2   3   0   0   0   0   0
4   0   0   0   0   0   0   0   0   1   4

Answer 1

The desired result can be obtained by pivoting df, with values from user_id becoming the index and values from category becoming a column level:

import numpy as np
import pandas as pd
df = pd.DataFrame({'category': ['a', 'b', 'a', 'a', 'b', 'c', 'e'],
 'collect': [3, 9, 6, 3, 2, 0, 4],
 'user_id': [1, 1, 2, 3, 3, 3, 4],
 'view': [2, 5, 8, 7, 4, 3, 1]}) 

result = (df.pivot(index='user_id', columns='category')
          .swaplevel(axis=1).sortlevel(axis=1).fillna(0))

yields

category    a            b            c            e        
         view collect view collect view collect view collect
user_id                                                     
1         2.0     3.0  5.0     9.0  0.0     0.0  0.0     0.0
2         8.0     6.0  0.0     0.0  0.0     0.0  0.0     0.0
3         7.0     3.0  4.0     2.0  3.0     0.0  0.0     0.0
4         0.0     0.0  0.0     0.0  0.0     0.0  1.0     4.0

Above, result has a MultiIndex. In general I think this should be preferred over a flattened single index, since it retains more of the structure of the data.

However, the MultiIndex can be flattened into a single index:

result.columns = ['{}_{}'.format(cat,col) for cat, col in result.columns]
print(result)

yields

         a_view  a_collect  b_view  b_collect  c_view  c_collect  e_view  \
user_id                                                                    
1           2.0        3.0     5.0        9.0     0.0        0.0     0.0   
2           8.0        6.0     0.0        0.0     0.0        0.0     0.0   
3           7.0        3.0     4.0        2.0     3.0        0.0     0.0   
4           0.0        0.0     0.0        0.0     0.0        0.0     1.0   

         e_collect  
user_id             
1              0.0  
2              0.0  
3              0.0  
4              4.0