Transposing part of a pandas dataframe

dataframe, pandas, python

Solution

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 sum the indices and orientate:

>>> 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

Drop rows that are all zero:

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

Somewhat guessing in a few place here, but it should give you a start.

Problem

I have the following dataframe: ``` >>> 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'd like to pivot the data so that I end up with: ``` type group_A group_B 0 amount buy 100 0 1 number buy 0 123 2 number view 333 222 ``` How do I accomplish this?

Original source