Home > Software engineering >  Select value in column based on criteria in another
Select value in column based on criteria in another

Time:03-02

I have some data that looks like this

import pandas as pd

df = pd.DataFrame({'Col1':['a', 'a', 'a', 'a', 'a', 'b', 'b', 'b', 'b', 'b'],
                    'Col2':[1, 2, 3, 4, 5, 1, 2, 3, 4, 5],
                    'Col3':[.09, .1, .13, .2, .3, .04, .5, .12, .17, .2]})

df.head(10)

    Col1    Col2    Col3
0   a          1    0.09
1   a          2    0.10
2   a          3    0.13
3   a          4    0.20
4   a          5    0.30
5   b          1    0.04
6   b          2    0.50
7   b          3    0.12
8   b          4    0.17
9   b          5    0.20

I want to filter this data for the first time that a value in Col3 is greater than .1, based on the value in Col

The output would look like this

    Col1    Col2    Col3
1   a          2    0.10
7   b          3    0.12

I've tried things like this, and it's not really working out for me. What i'm really interested in is the value in column 2.

df[df['Col3'] >= .1 & df['Col1'] == 'a']['Col2'].max()

CodePudding user response:

Check with groupby idxmax

out = df.loc[df['Col3'].gt(0.1).groupby(df['Col1']).idxmax()]
Out[458]: 
  Col1  Col2  Col3
2    a     3  0.13
6    b     2  0.50
  • Related