How can I create a Pivot Table that show sum() of group values, using my Pandas Data Frame?

Question

My df1:

               cnpj     num_doc    bc_icms
0    02817342000124  0000010154   17827.07
1    54921580000189  0000112428  108000.00
2    08953538000122  0000012865     232.00
3    08953538000122  0000012865     239.00
4    08953538000122  0000012865     215.00
5    07374346000107  0000014224     320.12
6    07374346000107  0000014231     385.04
7    07374346000107  0000014263     401.28
8    07374346000107  0000014279     391.26
9    02364118000124  0000015263   37353.10
10   02364118000124  0000015264   56214.14

The output of df1.dtypes:

cnpj        object
num_doc     object
bc_icms    float64
dtype: object

So.... I'm trying to create a pivot table to answer the following question:

What is the sum of bc_icms for each cnpj?

This is what I've wrote:

indexes = [np.array(df1['cnpj']), np.array(df1['num_doc'])]
pt1 = pd.DataFrame(df1['bc_icms'], index=indexes)
print pt1

And here's the output:

                           bc_icms
02817342000124 0000010154      NaN
54921580000189 0000112428      NaN
08953538000122 0000012865      NaN
               0000012865      NaN
               0000012865      NaN
07374346000107 0000014224      NaN
               0000014231      NaN
               0000014263      NaN
               0000014279      NaN
02364118000124 0000015263      NaN
               0000015264      NaN
               0000015265      NaN
07720786000160 0000020128      NaN

I think this is the pivot table structure that I want! Good! But...

How can I fix these NaN's ?

How can I create a "sum" line for each cnpj ?

Example in Excel:

Answer 1

IIUC, you need a sum of each cnpj values, so I would use groupby as:

g = df.groupby('cnpj')['bc_icms'].sum().reset_index(name='sum')

that returns:

             cnpj        sum
0   2364118000124   93567.24
1   2817342000124   17827.07
2   7374346000107    1497.70
3   8953538000122     686.00
4  54921580000189  108000.00

Hope that helps.

EDIT:

you can also use:

g = df.groupby(['cnpj','num_doc'])['bc_icms'].sum()

that returns the complete dataframe out:

cnpj            num_doc
2364118000124   15263       37353.10
                15264       56214.14
2817342000124   10154       17827.07
7374346000107   14224         320.12
                14231         385.04
                14263         401.28
                14279         391.26
8953538000122   12865         686.00
54921580000189  112428     108000.00