Group dataframe and get sum AND count

Question

I have a dataframe that looks like this                 Company Name              Organisation Name  Amount 10118  Vifor Pharma UK Ltd  Welsh Assoc for Gastro  amp  Endo 2700 00 10119  Vifor Pharma UK Ltd    Welsh IBD Specialist Group   169 00 10120  Vifor Pharma UK Ltd             West Midlands AHSN 1200 00 10121  Vifor Pharma UK Ltd           Whittington Hospital   63 00 10122  Vifor Pharma UK Ltd                 Ysbyty Gwynedd   75 93   How do I sum the Amount and count the Organisation Name  to get a new dataframe that looks like this                 Company Name             Organisation Count   Amount 10118  Vifor Pharma UK Ltd                              5 11000 00   I know how to sum or count   df groupby  Company Name   sum   df groupby  Company Name   count     But not how to do both

User · Answer

Just in case you were wondering how to rename columns during aggregation, here's how for

pandas >= 0.25: Named Aggregation

df.groupby('Company Name')['Amount'].agg(MySum='sum', MyCount='count')

Or,

df.groupby('Company Name').agg(MySum=('Amount', 'sum'), MyCount=('Amount', 'count'))

                       MySum  MyCount
Company Name                       
Vifor Pharma UK Ltd  4207.93        5

User · Answer

try this   In  110    df groupby  Company Name                 agg   Organisation Name   count    Amount    sum                  reset index                 rename columns   Organisation Name   Organisation Count                Out 110             Company Name   Amount  Organisation Count 0  Vifor Pharma UK Ltd  4207 93                   5   or if you don t want to reset index   df groupby  Company Name    Amount   agg   sum   count      or  df groupby  Company Name   agg   Amount     sum   count       Demo   In  98   df groupby  Company Name    Amount   agg   sum   count    Out 98                            sum  count Company Name Vifor Pharma UK Ltd  4207 93      5  In  99   df groupby  Company Name   agg   Amount     sum   count     Out 99                         Amount                          sum count Company Name Vifor Pharma UK Ltd  4207 93     5

User · Answer

If you have lots of columns and only one is different you could do   In 1   grouper   df groupby  Company Name   In 2   res   grouper count   In 3   res  Amount     grouper Amount sum   In 4   res Out 4                         Organisation Name   Amount Company Name                                    Vifor Pharma UK Ltd                  5  4207 93   Note you can then rename the Organisation Name column as you wish

User · Answer

df groupby  Company Name   agg   Organisation name   count   Amount   sum          apply lambda x  x sort values   count   sum    ascending False

[python] Group dataframe and get sum AND count?

pandas >= 0.25: Named Aggregation

Examples related to python

Examples related to pandas

Examples related to dataframe

Examples related to group-by

Examples related to pandas-groupby