Pandas groupby nlargest sum

by

You can use apply after performing the groupby:

df.groupby('State')['Population'].apply(lambda grp: grp.nlargest(2).sum())

I think this issue you’re having is that df.groupby('State')['Population'].nlargest(2) will return a DataFrame, so you can no longer do group level operations. In general, if you want to perform multiple operations in a group, you’ll need to use apply/agg.

The resulting output:

State
Alabama    150
Wyoming    330

EDIT

A slightly cleaner approach, as suggested by @cᴏʟᴅsᴘᴇᴇᴅ:

df.groupby('State')['Population'].nlargest(2).sum(level=0)

This is slightly slower than using apply on larger DataFrames though.

Using the following setup:

import numpy as np
import pandas as pd
from string import ascii_letters

n = 10**6
df = pd.DataFrame({'A': np.random.choice(list(ascii_letters), size=n),
                   'B': np.random.randint(10**7, size=n)})

I get the following timings:

In [3]: %timeit df.groupby('A')['B'].apply(lambda grp: grp.nlargest(2).sum())
103 ms ± 1.08 ms per loop (mean ± std. dev. of 7 runs, 10 loops each)

In [4]: %timeit df.groupby('A')['B'].nlargest(2).sum(level=0)
147 ms ± 3.38 ms per loop (mean ± std. dev. of 7 runs, 10 loops each)

The slower performance is potentially caused by the level kwarg in sum performing a second groupby under the hood.

More Related Contents:

  1. Get statistics for each group (such as count, mean, etc) using pandas GroupBy?
  2. How do I create a new column from the output of pandas groupby().sum()?
  3. Python Pandas : group by in group by and average?
  4. Pandas GroupBy.apply method duplicates first group
  5. Pandas dataframe get first row of each group
  6. Pandas groupby multiple fields then diff
  7. Pandas Groupby Range of Values
  8. How to access pandas groupby dataframe by key
  9. group by pandas dataframe and select latest in each group
  10. Pandas GroupBy and select rows with the minimum value in a specific column
  11. How to group a pandas dataframe by a defined time interval?
  12. Pandas: resample timeseries with groupby
  13. Make Pandas groupby act similarly to itertools groupby
  14. Python pandas – filter rows after groupby
  15. Python Pandas How to assign groupby operation results back to columns in parent dataframe?
  16. why does pandas rolling use single dimension ndarray
  17. Add a sequential counter column on groups to a pandas dataframe
  18. pandas group by and find first non null value for all columns
  19. Get total of Pandas column
  20. Group dataframe and get sum AND count?
  21. group by week in pandas
  22. Pivot Tables or Group By for Pandas?
  23. Split pandas dataframe based on values in a column using groupby
  24. pandas divide row value by aggregated sum with a condition set by other cell
  25. Pandas Correlation Groupby
  26. Add a sequence number to each element in a group using python
  27. What’s the best way to sum all values in a Pandas dataframe? [duplicate]
  28. Pandas: groupby forward fill with datetime index
  29. How to get number of groups in a groupby object in pandas?
  30. Why do I get a KeyError when using pandas apply?

Leave a Comment