How can I filter specific column values after groupby?

Question

Below is a sample of my dataframe.

     A      R   K   S
130 Robe    41  35  snail
131 Slippers    44  42  hamster
132 Coat    47  49  mouse
133 Robe    50  56  goldfish
134 Slippers    53  63  bird
... ... ... ... ...
1166    Slippers    3149    7287    bird
1167    Coat    3152    7294    iguana
1168    Robe    3155    7301    snail
1169    Slippers    3158    7308    hamster
1170    Coat    3161    7315    mouse

How can I find the std of column R where column S is 'hamster' or column A is 'Coat'?

I have tried dfe.groupby(['A', 'S']).sum().R.std() , but the output is incorrect.

Try this: https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.core.groupby.DataFrameGroupBy.filter.html. More here: https://stackoverflow.com/questions/27488080/python-pandas-filter-rows-after-groupby — skrubber, Oct 25 '20 at 03:06

score 0 · Answer 1 · answered Oct 25 '20 at 04:37

The following code can be used to support multiple aggregation functions for column R.

df.groupby(['A','S'])['R'].agg(['sum','std'])

               sum  std
    A   S       
Coat    iguana  3152    NaN
        mouse   3208    2201.930517
Robe    goldfish    50  NaN
        snail   3196    2201.930517
Slippers    bird    3202    2189.202595
         hamster    3202    2201.930517

score 0 · Accepted Answer · answered Oct 25 '20 at 04:47

According to the description of your task (find the std of column R where column S is...), you don't need any grouping.

First filter the DataFrame on your criterion:

df.query("A == 'Coat' or S == 'hamster'")

The result from your data sample is:

             A     R     K        S
131   Slippers    44    42  hamster
132       Coat    47    49    mouse
1167      Coat  3152  7294   iguana
1169  Slippers  3158  7308  hamster
1170      Coat  3161  7315    mouse

Then compute what you want, i.e. .R.std().

So the complete code is:

df.query("A == 'Coat' or S == 'hamster'").R.std()

and the result:

1704.2421482876193

How can I filter specific column values after groupby?

2 Answers2