Display result of multi index array groupby in pandas dataframe

Question

I have a data frame which looks like:

        D        Type    Value    
0       1        A          2
1       1        B          4
2       2        C          1
3       1        A          1

I want to group by D and Type and sum the values.

data=df.groupby(['D','Type']).sum()

print(data)

Which gives me this result:

D      Type               Value
1       A                    3
        B                    4
2       C                    3

But I want it in this format:

D      A         B        C     
1      3         4        Nan
2      Nan       Nan      3

Answer 1

UPDATE:

r = df.pivot_table(index=['D'], columns='Type', aggfunc='sum').reset_index()
r.columns = [tup[1] if tup[1] else tup[0] for tup in r.columns]
r.to_csv('c:/temp/out.csv', index=False)

Result:

D,A,B,C
1,3.0,4.0,
2,,,1.0

Original answer:

you can use pivot_table() method:

In [7]: df.pivot_table(index=['D'], columns='Type', aggfunc='sum', fill_value=0)
Out[7]:
     Value
Type     A  B  C
D
1        3  4  0
2        0  0  1

or with NaN's:

In [8]: df.pivot_table(index=['D'], columns='Type', aggfunc='sum')
Out[8]:
     Value
Type     A    B    C
D
1      3.0  4.0  NaN
2      NaN  NaN  1.0

PS i think you have a typo in your groupby... section:

In [10]: df.groupby(['D','Type']).sum()
Out[10]:
        Value
D Type
1 A         3
  B         4
2 C         1

there should be C --> 1 instead of C --> 3