Select row by max value in group in a pandas dataframe

by

A standard approach is to use groupby(keys)[column].idxmax().
However, to select the desired rows using idxmax you need idxmax to return unique index values. One way to obtain a unique index is to call reset_index.

Once you obtain the index values from groupby(keys)[column].idxmax() you can then select the entire row using df.loc:

In [20]: df.loc[df.reset_index().groupby(['F_Type'])['to_date'].idxmax()]
Out[20]: 
                       start    end
F_Type to_date                     
A      20150908143000    345    316
B      20150908143000  10743   8803
C      20150908143000  19522  16659
D      20150908143000    433     65
E      20150908143000   7290   7375
F      20150908143000      0      0
G      20150908143000   1796    340

Note: idxmax returns index labels, not necessarily ordinals. After using reset_index the index labels happen to also be ordinals, but since idxmax is returning labels (not ordinals) it is better to always use idxmax in conjunction with df.loc, not df.iloc (as I originally did in this post.)

More Related Contents:

  1. How do I Pandas group-by to get sum?
  2. Get statistics for each group (such as count, mean, etc) using pandas GroupBy?
  3. How to groupby consecutive values in pandas DataFrame
  4. Python: get a frequency count based on two columns (variables) in pandas dataframe some row appers
  5. Pandas dataframe get first row of each group
  6. Pandas groupby multiple fields then diff
  7. How to access pandas groupby dataframe by key
  8. Python Pandas How to assign groupby operation results back to columns in parent dataframe?
  9. How can I use cumsum within a group in Pandas?
  10. pandas – find first occurrence
  11. Add a sequential counter column on groups to a pandas dataframe
  12. Group dataframe and get sum AND count?
  13. Pandas groupby for zero values
  14. pandas divide row value by aggregated sum with a condition set by other cell
  15. Pandas groupby with bin counts
  16. get first and last values in a groupby
  17. Pandas groupby and aggregation output should include all the original columns (including the ones not aggregated on)
  18. how to convert monthly data to quarterly in pandas
  19. Get the row corresponding to the max in pandas GroupBy [duplicate]
  20. Get frequency of item occurrences in a column as percentage [duplicate]
  21. How to get number of groups in a groupby object in pandas?
  22. Constructing pandas DataFrame from values in variables gives “ValueError: If using all scalar values, you must pass an index”
  23. Can pandas automatically read dates from a CSV file?
  24. pandas dataframe str.contains() AND operation
  25. Find the unique values in a column and then sort them
  26. how do I calculate a rolling idxmax
  27. How to filter a pandas dataframe based on the length of a entry
  28. How is pandas groupby method actually working?
  29. Replace NaN with empty list in a pandas dataframe
  30. When is it appropriate to use df.value_counts() vs df.groupby(‘…’).count()?

Leave a Comment