0

I have a dataframe with column a,b,c,d. I would like to have a new dataframe grouped by a, and for each group I want to get the complete row with vlaues(a,b,c,d)corresponding to the max value of the column (d)

mohamed elhafiz
  • 69
  • 1
  • 8
  • you can find an answer to your question [here](https://stackoverflow.com/questions/10202570/find-row-where-values-for-column-is-maximal-in-a-pandas-dataframe) – Mit May 13 '20 at 12:01

1 Answers1

0

You can use DataFrame.xs and get row corresponding sorted index.

dict1 = {'a': [1, 2, 3, 1, 2, 1, 1],
         'b': [4, 5, 6, 2, 2, 1, 4],
         'c': [7, 8, 9, 4, 1, 2, 4],
         'd': [1, 2, 3, 1, 2, 3, 4]}

df = pd.DataFrame(dict1)

Out:
   a  b  c  d
0  1  4  7  1
1  2  5  8  2
2  3  6  9  3
3  1  2  4  1
4  2  2  1  2
5  1  1  2  3
6  1  4  4  4

I'd use this way to group:

grp = df.set_index(['d','a']).sort_index(ascending=False)

and then, just watch at the first element and make the slice:

grp.xs(4, drop_level=False)

Out:
        b   c
d   a       
4   1   4   4

Also, you can use df.reset_index and get clear row.

Roman_N
  • 183
  • 7