Pandas replace column values by condition with averages based on a value in another column I have a dataframe with people's CV data.
Among others, there's a column with years of experience, and a column with age.
Some people stated their age and experience in a way that experience &gt; age. For example age=30 and experience=40, which is a problem.
I am trying to perform a following task:
If experience &gt; age, replace the value of experience with an average experience of people of the same age. For example if age=30 and experience=40, replace experience with an average experience of all 30-year olds.
The problem is that I don't know how to obtain the age of a specific person in the slice df[df['experience'] &gt; df['age']].
AFAIK it could be done by something like this:
mask = (df['experience'] &gt; df['age'])

df['experience_cleaned'] = np.where(mask, df[df['age'] == age]['experience'].mean() ,df['experience'])

I would appreciate if you show me how it can be done, because I could use the technique to also preprocess more data in a similar way.
Get a dictionary with average age where experience &lt; age

mapping = df[df['age']&gt;df['experience']].groupby('age').mean().apply(list).to_dict()['experience']


Use this dictionary to replace values where experience &gt; age

df.loc[df['age']&lt;df['experience'],'experience'] = df[df['age']&lt;df['experience']]['age'].map(mapping)