CODEWITHSUNDEEP
dataframepandas-groupby
shashi kiran
shashi kiran

Reputation: 21

Value error while trying to group by the pandas data frame and get the row having maximum value for one of the columns

I am new to use data frame and trying to group by the below data-frame by the field Name and want to get the rows having maximum value for the column 'high'.

    Name    date        high    low
0   20MICRONS   06-03-2020  31.55   27.45
1   AGRITECH    06-03-2020  33.2    30.2
2   20MICRONS   09-03-2020  30      26.85
3   AGRITECH    09-03-2020  30.45   26.4
4   AGRITECH    11-03-2020  28.75   26.55
5   INFY        11-03-2020  695.95  669.05
6   20MICRONS   13-03-2020  24.7    19.45
7   AGRITECH    13-03-2020  26.45   22.55
8   INFY        06-03-2020  744     729.1
9   INFY        09-03-2020  725.85  697
10  20MICRONS   11-03-2020  28.25   24.65
11  20MICRONS   12-03-2020  28.7    21.5
12  AGRITECH    12-03-2020  28.5    24.85
13  INFY        12-03-2020  670     627.5
14  INFY        13-03-2020  667     570

required output with the maximum of 'high' column for all the stocks group-wise:

    Name    date        high    low
8   INFY        06-03-2020  744 729.1
1   AGRITECH    06-03-2020  33.2    30.2
0   20MICRONS   06-03-2020  31.55   27.45

likewise it would be helpful if I get the minimum as well. i have tried max() and idxmax() functions as below but i recive value error. could you please help me.

df[['IndexName','date','high']].loc[df[['IndexName','date','high']].reset_index().groupby(['IndexName'])['high'].idxmax()]

did not help but.

Upvotes: 1

Views: 59

Answers (1)

shashi kiran
shashi kiran

Reputation: 21

i am able to solve my problem as below using an additional for loop and comparison.

grouped=df.reset_index().groupby('Name')

dfinal= pd.DataFrame(columns=['Name','date','high','low'])

if(sys_Arg_highlow==1):

    for group in grouped:
       dfinal = dfinal.append(group[group['high']==group['high'].max()])

else:
    for group in grouped:
      dfinal = dfinal.append(group[group['low']==group['low'].min()])

However , i would like to know if there are any better ways of doing this. thank you.

Upvotes: 1

Related Questions