I have a data frame in the format mentioned in the screenshot below. Column 'Candidate Won' has only 'loss' as the column value for all the rows. I want to update the Column 'Candidate Won' to a value 'won' if the corresponding row's '% of Votes' is maximum when grouped by 'Constituency' Column otherwise the value should be 'loss'. I want to achieve the result by using a combination of apply, lambda, and group by, instead of using loops/iterations.
DataFrame : (df_andhrapradesh)
Code below works for a specific constituency in the data frame :
df_amalapuram=df_andhrapradesh[df_andhrapradesh['Constituency']=='Amalapuram'] df_amalapuram['Candidate Won']=df_amalapuram['% of Votes'].apply(lambda x:"Won" if x==df_amalapuram['% of Votes'].max() else "Loss")
Tried something like below to make it work for the entire data frame which has different constituencies but it failed:
df_andhrapradesh['Candidate Won']=df_andhrapradesh['% of Votes'].apply(lambda x:"Won" if x==df_andhrapradesh.groupby('Constituency')['% of Votes'].max() else "Loss")