How to insert a text value to new column if a condition satisfied at least once within group of rows-CodePudding

I want to insert a value "Yes" to a new column if the value "Yes" is contained at least once within the row groups in "Name" column.

df2 = pd.DataFrame({ 'Name':['John', 'Tom', 'Tom', 'Tom' ,'Ole','Ole'],
                    'SomeQty':[100, 200, 300, 400, 500,600],
                     'Match':['Yes', 'No', 'Yes','No', 'No','No'],
                    'SomeValue':[100, 200, 200, 200, 500,600]
                    })

I tried using group by transform but was not sure what function to use for the extrapolation of a text value.

df2['Match1'] = (df2.assign(Match = df2['Match'].where((df2['Match'] != "No") )).groupby(['Name'])["Match"].transform(lambda x: x))

My expected table is;

CodePudding user response：

Check if any value in Match is Yes in groupby.transform:

df2['Match1'] = df2.groupby('Name').Match.transform(lambda g: 'Yes' if g.eq('Yes').any() else 'No')

df2
   Name  SomeQty Match  SomeValue Match1
0  John      100   Yes        100    Yes
1   Tom      200    No        200    Yes
2   Tom      300   Yes        200    Yes
3   Tom      400    No        200    Yes
4   Ole      500    No        500     No
5   Ole      600    No        600     No

CodePudding user response：

You can avoid coding a lambda by using groupby.transform('any'):

m = (df2.assign(isyes=df2['Match'].eq('Yes'))
        .groupby('Name')['isyes']
        .transform('any'))

df2['Match1'] = np.where(m, 'Yes', 'No')
df2

   Name  SomeQty Match  SomeValue Match1
0  John      100   Yes        100    Yes
1   Tom      200    No        200    Yes
2   Tom      300   Yes        200    Yes
3   Tom      400    No        200    Yes
4   Ole      500    No        500     No
5   Ole      600    No        600     No