groupby with idxmax is not giving me required output?

Question

Date    Open    High    Low Close   volume  Stockname
0   2017-09-01  250.00  255.00  237.50  237.50  4658997 ABCAPITAL
1   2017-09-04  225.65  225.65  225.65  225.65  309923  ABCAPITAL
2   2017-09-05  214.40  214.40  214.40  214.40  192377  ABCAPITAL
3   2017-09-06  203.70  203.70  203.70  203.70  267160  ABCAPITAL
4   2017-09-07  193.55  213.85  193.55  206.95  34241770    ABCAPITAL
... ... ... ... ... ... ... ...
1046    2020-03-26  125.05  133.15  119.00  128.75  12044426    ZEEL
1047    2020-03-27  132.00  138.65  127.00  128.05  8765500 ZEEL
1048    2020-03-30  122.30  135.00  122.30  125.65  8707583 ZEEL
1049    2020-03-31  130.50  130.65  123.00  123.95  8733424 ZEEL
1050    2020-04-01  124.00  126.75  120.00  120.90  6571551 ZEEL

So I have this dataframe which has multiple stocks and O,H,L,C data

I want to create a new dataframe which has max closing price for each stock. I want the complete line item for this purpose

Here is my code

x = new.groupby('Stockname')
new.loc[x['Close'].idxmax()]

Output which is coming is totally wrong. The closing prices are not max for each stock. Kindly help me with this

Date    Open    High    Low Close   volume  Stockname
0   2017-09-01  250.00  255.00  237.50  237.50  4658997 ABCAPITAL
0   2016-01-01  233.00  263.45  232.00  248.00  307528  ABFRL
0   2016-01-01  1362.00 1380.40 1360.00 1373.65 161982  ACC
0   2016-01-01  261.00  268.30  260.10  267.55  1347893 ADANIPORTS
0   2016-01-01  32.50   34.40   32.30   34.00   12331921    ADANIPOWER
... ... ... ... ... ... ... ...
506 2018-01-16  232.90  237.20  229.20  233.00  1198721 VGUARD
506 2018-01-16  635.00  639.95  618.50  623.05  921801  VOLTAS
506 2018-01-16  1529.95 1538.65 1499.00 1507.75 18023   WHIRLPOOL
506 2018-01-16  238.27  251.13  237.59  249.59  6166445 WIPRO
506 2018-01-16  610.40  618.30  607.50  613.40  4805427 ZEEL

Check https://stackoverflow.com/questions/15705630/get-the-rows-which-have-the-max-value-in-groups-using-groupby — jcaliz, Apr 06 '20 at 07:17

JvdV · Accepted Answer · 2020-04-06T07:28:46.040

1

This gave me what you are after:

df = df[df.groupby(['Stockname'])['Close'].transform(max) == df['Close']]

Or:

df = df.loc[df.groupby('Stockname')['Close'].idxmax()]

Or:

df = df.sort_values('Close', ascending=False).drop_duplicates('Stockname')

Indeed, as per @jcaliz in comment mentioned, taken from here and here and here. All boiled down to result below:

          Date    Open    High    Low   Close    volume  Stockname
0   2017-09-01  250.00  255.00  237.5  237.50   4658997  ABCAPITAL
506 2020-03-26  125.05  133.15  119.0  128.75  12044426       ZEEL

If this has worked please give credit to the original posters, I merely copied over working code.

edited Apr 06 '20 at 07:28

answered Apr 06 '20 at 07:23

JvdV

70,606
8
39
70

Yes, I saw this solution. Can't this be done through idxmax () ? – Joseph arasta Apr 06 '20 at 07:25
Yes, even I used the same code before - df = df.loc[df.groupby('Stockname')['Close'].idxmax()] & its not working. I posted the output to this code above. Not sure why – Joseph arasta Apr 06 '20 at 07:33
Have you tried it in a single line like I showed? @Josepharasta. Worked totally fine for me. Your two line approach however, didn't – JvdV Apr 06 '20 at 07:40
Yes, i tried the same one liner and still not working. Not sure why – Joseph arasta Apr 06 '20 at 07:53
Okay, I will use transform feature then. Thanks Jvdv for your help. Upvoting this – Joseph arasta Apr 06 '20 at 07:57
@Josepharasta, I can't think of why it wouldn't have worked for you. I merely copied your data and applied `idxmax` and it worked. – JvdV Apr 06 '20 at 07:59

groupby with idxmax is not giving me required output?

1 Answers1