Print Pandas Unique Rows by Column Condition-CodePudding

I am trying to print the rows whereby a data condition is met in a pandas DF based on the unique values in the DF. For example, I have data that looks like this:

DF:

site  temp  month  day
A     15    7      18
A     11    6      12
A     22    9       3
B      9    4      23
B      3    2      11
B     -1    5      18

I need the result to print the rows where the max in the 'temp' column occurs such as this for the final result: A 15 B 9

I have tried this but it is not working correctly:

for i in DF['site'].unique():
    print(DF.temp.max())

I get the same answer of:

22
22

but the answer should be:

site  temp  month  day
A     22    9      3
B      9    4     23

thank you!

CodePudding user response：

A possible solution:

df.groupby('site', as_index=False).max()

Output:

  site  temp
0    A    22
1    B     9

In case you want to use a for loop:

for i in df['site'].unique():
    print(df.loc[df['site'].eq(i), 'temp'].max())

Output:

22
9

CodePudding user response：

df.groupby('site').max()

output:

        temp    month   day
site            
A       22      9       18
B       9       5       23

CodePudding user response：

Let us do sort_values drop_duplicates

df = df.sort_values('temp',ascending=False).drop_duplicates('site')
Out[190]: 
  site  temp  month  day
2    A    22      9    3
3    B     9      4   23