Add Default value while grouping elements in Pandas

user_13583883 0
Viewed 588

I am working with Pandas dataframe and my data has EmployeeNo, Payable and Amount as columns.

      EmpNo     Payable    Amount

      01        Salary     20000
      01        Salary     2000
      01        Bonus      150

      02        Salary     15000

      03        Salary     10000
      03        Bonus      50
      03        Bonus      150

      04        Salary     12000
      04        Salary     3000

I wanted to group based on Salary and Bonus for each employee and sum up all the salaries and bonus for each employee. I wrote this code :

df_2 = df.groupby([ 'EmpNo', 'Payable'])['Amount'].sum()

But it doesn't show Bonus for each and every employee.

I want my output to show 0 as a default value if Bonus is not assigned to any employee in the following format:

      EmpNo     Payable    Amount
      
      01        Salary     22000
                Bonus      150

      02        Salary     15000
                Bonus      0

      03        Salary     10000
                Bonus      200

      04        Salary     15000
                Bonus      0
pandas-groupby pandas dataframe python-3.x python
2 Answers

Add Series.unstack with DataFrame.stack:

df_2 = df.groupby([ 'EmpNo', 'Payable'])['Amount'].sum().unstack(fill_value=0).stack()
print (df_2)
EmpNo  Payable
1      Bonus        150
       Salary     22000
2      Bonus          0
       Salary     15000
3      Bonus        200
       Salary     10000
4      Bonus          0
       Salary     15000
dtype: int64

If need convert to DataFrame add Series.reset_index:

df_2 = (df.groupby([ 'EmpNo', 'Payable'])['Amount']
          .sum()
          .unstack(fill_value=0)
          .stack()
          .reset_index(name='Amount'))
print (df_2)
   EmpNo Payable  Amount
0      1   Bonus     150
1      1  Salary   22000
2      2   Bonus       0
3      2  Salary   15000
4      3   Bonus     200
5      3  Salary   10000
6      4   Bonus       0
7      4  Salary   15000
user_29010020

You can also use df.pivot_table:

In [393]: df.pivot_table(index='EmpNo', columns='Payable', aggfunc='sum', fill_value=0).stack().reset_index()
Out[393]: 
   EmpNo Payable  Amount
0      1   Bonus     150
1      1  Salary   22000
2      2   Bonus       0
3      2  Salary   15000
4      3   Bonus     200
5      3  Salary   10000
6      4   Bonus       0
7      4  Salary   15000
user_58208140
Related