Apply groupby on a DataFrame to display cumulative stats

Question

Let's say I have a DataFrame that looks like this:

Bank Name     House     This Wk
Barc          Germany   100
Barc          UK        300
Barc          UK        500
JPM           Japan     200
JPM           NYC       100
BOA           LA        900
BOA           LA        50
BOA           LA        50
DB            Italy     45

I would like to group-by Bank Name, while outputting the largest House Value as well as the total value...

For example, using the example above would result in:

Bank Name     Total     House     This Wk
Barc          900       UK        500
JPM           300       Japan     200
BOA           1000      LA        900
DB            45        Italy     45

Essentially, it is grouping the Total by Bank Name, but also outputting the largest contributor, House, to the total and the amount contributed is This Wk.

How can I go about doing this?

@ands literally similar to the one above.. what do you mean what format? — sgerbhctim, Aug 24 '17 at 19:43
No it's coming from a csv - Bank Name and House are strings, the others are floats — sgerbhctim, Aug 24 '17 at 19:45

MaxU - stand with Ukraine · Answer 1 · 2017-08-24T19:50:53.800

5

In [121]: df.groupby('Bank Name', group_keys=False) \
     ...:   .apply(lambda x: x.nlargest(1, 'This Wk').assign(Total=x['This Wk'].sum())) \
     ...:   [['Bank Name','Total','House','This Wk']]
     ...:
Out[121]:
  Bank Name  Total  House  This Wk
5       BOA   1000     LA      900
2      Barc    900     UK      500
8        DB     45  Italy       45
3       JPM    300  Japan      200

edited Aug 24 '17 at 19:50

answered Aug 24 '17 at 19:44

MaxU - stand with Ukraine

205,989
36
386
419

Any chance you can format it the way I showed..? As in the columns: `Bank Name, Total, House, This Wk`? – sgerbhctim Aug 24 '17 at 19:45
And can I just set the result to equal a different dataframe? – sgerbhctim Aug 24 '17 at 19:46
Notice the order of column names and their respective values – sgerbhctim Aug 24 '17 at 19:49
@sgerbhctim, i've updated my post - is that what you need? – MaxU - stand with Ukraine Aug 24 '17 at 19:53
Yo how's it going. Any thoughts? https://stackoverflow.com/questions/45856926/reading-in-a-pretty-printed-formatted-dataframe-using-pd-read-clipboard – cs95 Aug 24 '17 at 19:53

cs95 · Accepted Answer · 2017-08-24T19:54:47.560

3

You can consider df.groupby with a list of dfGroupBy.agg functions:

In [732]: out = df.groupby('Bank Name')['This Wk'].agg(['sum', 'idxmax', 'max'])\
               .rename(columns={'sum' : 'Total', 'idxmax' : 'House', 'max' : 'This Wk'})\
               .reset_index()


In [734]: out['House'] = df.loc[out['House'], 'House'].values; out
Out[734]: 
  Bank Name  Total  House  This Wk
0       BOA   1000     LA      900
1      Barc    900     UK      500
2        DB     45  Italy       45
3       JPM    300  Japan      200

edited Aug 24 '17 at 19:54

answered Aug 24 '17 at 19:46

cs95

379,657
97
704
746

How would I keep the format as the one I showed above, meaning the columns are `Bank Name, Total, House, This Wk`? – sgerbhctim Aug 24 '17 at 19:47
Not just rename, how do I change the order of the columns? – sgerbhctim Aug 24 '17 at 19:48
Oh, that's really simple. Just change the order of the agg functions. – cs95 Aug 24 '17 at 19:48
@sgerbhctim Fixed with order too. – cs95 Aug 24 '17 at 19:52
@cᴏʟᴅsᴘᴇᴇᴅ for the print , you can try `import tabulate from tabulate print(tabulate.tabulate(df, df.columns, tablefmt="orgtbl"))` – BENY Aug 24 '17 at 20:16
@Wen Thank you :) – cs95 Aug 24 '17 at 20:17

score 0 · Answer 3 · answered Aug 24 '17 at 19:54

Another way using apply would be

In [17]: (df.groupby('Bank Name', sort=False)
            .apply(lambda x: pd.Series(
                             [x['This Wk'].sum(), 
                              x.loc[x['This Wk'].idxmax(), 'House'], 
                              x['This Wk'].max()], 
                   index=['Total', 'House', 'This Wk']))
            .reset_index())
Out[17]:
  Bank Name  Total  House  This Wk
0      Barc    900     UK      500
1       JPM    300  Japan      200
2       BOA   1000     LA      900
3        DB     45  Italy       45

Apply groupby on a DataFrame to display cumulative stats

3 Answers3