CODEWITHSUNDEEP
pythonpandaspandas-groupby
Vishnudev Krishnadas
Vishnudev Krishnadas

Reputation: 10960

Getting value of a column where another column is minimum from group

Input:

I have an input dataframe with text, character length, and values 'x' and 'flag':

    x   text    len     flag
0   1   hi      2       1
1   1   hello   5       0
2   1   how     3       1
3   2   are     3       1
4   2   you?    4       1
5   2   kiddo   5       1

I want to groupby 'x' and get the 'text' of lowest character length where 'flag' is 1.

Output:

    x   text    len     select
0   1   hi      2       hi
1   1   hello   5       nan
2   1   how     3       nan
3   2   are     3       are
4   2   you?    4       nan
5   2   kiddo   5       nan

I tried to groupby() and get the minimum value but how do I get the text where minimum value is present? I don't wanna use apply.

df[df.flag == 1].groupby('x')['len'].transform('min')

That'll get the min char length value, but not the actual 'text' value where char length is minimum.

Upvotes: 4

Views: 156

Answers (1)

cs95
cs95

Reputation: 402333

You'll need groupby and transform with idxmin:

# Handle the `flag` requirement.
v = df.assign(len=df['len'].where(df.flag.astype(bool)))

condition = v.groupby('x')['len'].transform('idxmin')
df.loc[condition, 'select'] = df.loc[condition, 'text'].values

df
   x   text  len  flag select
0  1     hi    2     1     hi
1  1  hello    5     0    NaN
2  1    how    3     1    NaN
3  2    are    3     1    are
4  2   you?    4     1    NaN
5  2  kiddo    5     1    NaN

Upvotes: 3

Related Questions