pandas: filtering by group size and data value

Viewed 7535

Having grouped data, I want to drop from the results groups that contain only a single observation with the value below a certain threshold.

Initial data:

df = pd.DataFrame(data={'Province' : ['ON','QC','BC','AL','AL','MN','ON'], 
                            'City' :['Toronto','Montreal','Vancouver','Calgary','Edmonton','Winnipeg','Windsor'],
                            'Sales' : [13,6,16,8,4,3,1]})

        City Province  Sales
0    Toronto       ON     13
1   Montreal       QC      6
2  Vancouver       BC     16
3    Calgary       AL      8
4   Edmonton       AL      4
5   Winnipeg       MN      3
6    Windsor       ON      1

Now grouping the data:

df.groupby(['Province', 'City']).sum()

                    Sales
Province City
AL       Calgary        8
         Edmonton       4
BC       Vancouver     16
MN       Winnipeg       3
ON       Toronto       13
         Windsor        1
QC       Montreal       6

Now the part I can't figure out is how to drop provinces with only one city (or generally N observations) with the total sales less then 10. The expected output should be:

                    Sales
Province City
AL       Calgary        8
         Edmonton       4
BC       Vancouver     16
ON       Toronto       13
         Windsor        1

I.e. MN/Winnipeg and QC/Montreal are gone from the results. Ideally, they won't be completely gone but combined into a new group called 'Other', but this may be material for another question.

2 Answers
Related