Transpose part of a pandas frame

Question

I have the following framework:

>>> df Out[15]: group type amount number 0 group_A buy 100 123 1 group_A view 0 111 2 group_B view 0 222 3 group_A view 0 222 

I would like to collapse the data so that it ends up:

  type group_A group_B 0 amount buy 100 0 1 number buy 0 123 2 number view 333 222 

How to do it?

Answer 1

Using:

 df=pd.DataFrame([['group_A','buy',100,123],['group_A','view',0,111],['group_B','view',0,222],['group_A','view',0,222]],columns=['group','type','amount','number']) 

First we summarize the indices and orient:

 >>> df = df.groupby(['type','group']).sum().transpose().stack(0).reset_index() >>> df group level_0 type group_A group_B 0 amount buy 100 NaN 1 amount view 0 0 2 number buy 123 NaN 3 number view 333 222 

Discard all null lines:

 df = df[~((df['group_A']==0) | (df['group_B']==0))] 

Fillna's:

 >>> df.fillna(0) group level_0 type group_A group_B 0 amount buy 100 0 2 number buy 123 0 3 number view 333 222 

Some guessing in several places here, but this should give you a start.