how to group by and sort the columns given the column value in a function

user_8448146 0
Viewed 55

I have a data frame as below, I need to write a function which should be able to give me the below results:

Input Parameters:

  1. Country, for example 'INDIA'
  2. Age, for example 'Student'

My input dataframe looks like this:

   Card Name    Country      Age         Code  Amount
0        AAA      INDIA    Young        House     100
1        AAA  Australia      Old     Hardware     200
2        AAA      INDIA  Student        House     300
3        AAA         US    Young     Hardware     600
4        AAA      INDIA  Student  Electricity     200
5        BBB  Australia    Young  Electricity     100
6        BBB      INDIA  Student  Electricity     200
7        BBB  Australia    Young        House     450
8        BBB      INDIA      Old        House     150
9        CCC  Australia      Old     Hardware     200
10       CCC  Australia    Young        House     350
11       CCC      INDIA      Old  Electricity     400
12       CCC         US    Young        House     200

The expected output would be 

          Code  Total Amount  Frequency  Average
0  Electricity           400          2      200
1        House           300          1      300

Top 10 ( In our case, we can get only Top 2 ) Code for the given Country ( = India) and Age ( = Student ) based on the Total sum of the Amount. In addition it should also give a new column ‘Frequency’ which will count the no. of records in that group and column ‘Average’ will be the Total sum / Frequency

I have tried

df.groupby(['Country','Age','Code']).agg({'Amount': sum})['Amount'].groupby(level=0, group_keys=False).nlargest(10)

which produces

Country    Age      Code       
Australia  Young    House          800
           Old      Hardware       400
           Young    Electricity    100
INDIA      Old      Electricity    400
           Student  Electricity    400
                    House          300
           Old      House          150
           Young    House          100
US         Young    Hardware       600
                    House          200
Name: Amount, dtype: int64

which is unfortunately different from the expected output.

pandas-groupby pandas dataframe python
1 Answers

Given

>>> df                                                                                                                 
   Card Name    Country      Age         Code  Amount
0        AAA      INDIA    Young        House     100
1        AAA  Australia      Old     Hardware     200
2        AAA      INDIA  Student        House     300
3        AAA         US    Young     Hardware     600
4        AAA      INDIA  Student  Electricity     200
5        BBB  Australia    Young  Electricity     100
6        BBB      INDIA  Student  Electricity     200
7        BBB  Australia    Young        House     450
8        BBB      INDIA      Old        House     150
9        CCC  Australia      Old     Hardware     200
10       CCC  Australia    Young        House     350
11       CCC      INDIA      Old  Electricity     400
12       CCC         US    Young        House     200

you can filter your dataframe first:

>>> country = 'INDIA'                                                                                                  
>>> age = 'Student'                                                                                                    
>>> tmp = df[df.Country.eq(country) & df.Age.eq(age)].loc[:, ['Code', 'Amount']]                                       
>>> tmp                                                                                                                
          Code  Amount
2        House     300
4  Electricity     200
6  Electricity     200

... and then group:

>>> result = tmp.groupby('Code')['Amount'].agg([['Total Amount', 'sum'], ['Frequency', 'size'], ['Average', 'mean']]).reset_index() 
>>> result                             
          Code  Total Amount  Frequency  Average
0  Electricity           400          2      200
1        House           300          1      300

If I understand your filtering criterion by the Total Amount correctly, you can then issue

result.nlargest(10, 'Total Amount')
user_36200030
Related