comparing values of subgroups of a group in pandas dataframe

user_13538895 0
Viewed 190

I have a grouped dataframe as below which summarizes the no. of products bought by male and female customers:

                          Qty
prod_cat         Gender
Bags             F       2362
                 M       2346
Books            F       7070
                 M       7587
Clothing         F       3425
                 M       3748
Electronics      F       5832
                 M       6476
Footwear         F       3716
                 M       3555
Home and kitchen F       4895
                 M       5041

I want to get those products which have been more popular among female customers than males.So, the expected output is as below:

   prod_cat
0      Bags
4  Footwear

I have tried this below way which works, but looking for a better and simpler solution. 

        temp =temp.reset_index()
   ...: prod_f = temp[temp["Gender"] =="F"].sort_index().drop(columns="Gender").reset_index(drop=True)
   ...: prod_m = temp[temp["Gender"] =="M"].sort_index().drop(columns="Gender").reset_index(drop =True)
   ...:
   ...: display(prod_f,prod_m)
   ...:
   ...: f_popular = prod_f[["prod_cat"]].where(prod_f["Qty"]>prod_m["Qty"])
   ...: f_popular = f_popular.loc[~f_popular.prod_cat.isnull(),]
   ...: f_popular

output

           prod_cat   Qty
0              Bags  2362
1             Books  7070
2          Clothing  3425
3       Electronics  5832
4          Footwear  3716
5  Home and kitchen  4895

           prod_cat   Qty
0              Bags  2346
1             Books  7587
2          Clothing  3748
3       Electronics  6476
4          Footwear  3555
5  Home and kitchen  5041


   prod_cat
0      Bags
4  Footwear

I want to compare values of male and female subgroups within a product category group and get the rows or group where female value is more.

pandas grouping group-by python
1 Answers

You can try unstack to create a column per gender and then use where to catch if Female is more than Male. It does not give the exact type of output, but you would get the information. 

print (df['Qty'].unstack().where(lambda x: x.F>=x.M).dropna().index.tolist())
['Bags', 'Footwear']

or if you want dataframe, you could replace tolist() by to_frame() and get

          prod_cat
prod_cat          
Bags          Bags
Footwear  Footwear
user_92747320
Related