Pandas - get subtotal of some rows-CodePudding

There is DataFrame as:

    host  status count
0   ast2  0      1
1   ast2  1      2
2   ast2  2      3
3   ast3  0      4
4   ast3  1      5
5   ast3  2      6
6   ast9  0      7
7   ast9  2      8

How to get sub-total of rows, where status is only 0 or 1 not 2? So result should be:

    host  status count
0   ast2  0      3
1   ast2  2      3
2   ast3  0      9
3   ast3  2      6
4   ast9  0      7
5   ast9  2      8

CodePudding user response：

You can replace 1 to 0 for status column and then aggregate sum:

df1 = (df.assign(status = df['status'].mask(df['status'].eq(1), 0))
        .groupby(['host','status'], as_index=False)['count']
        .sum())
print (df1)
   host  status  count
0  ast2       0      3
1  ast2       2      3
2  ast3       0      9
3  ast3       2      6
4  ast9       0      7
5  ast9       2      8

Alternative solution:

df1 = (df.replace({'status':{1:0}})
         .groupby(['host','status'], as_index=False)['count']
         .sum())

CodePudding user response：

result = df[df['status'].isin([0, 1])].groupby('host')['count'].sum()

This will give you the following output:

host
ast2    3
ast3    9
ast9    0