Pandas replace column values by condition with averages based on a value in another column

Question

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 > age. For example age=30 and experience=40, which is a problem.

I am trying to perform a following task: If experience > 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'] > df['age']].

AFAIK it could be done by something like this:

mask = (df['experience'] > 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.

Answer 1

1. Get a dictionary with average age where experience < age

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

2. Use this dictionary to replace values where experience > age

df.loc[df['age']<df['experience'],'experience'] = df[df['age']<df['experience']]['age'].map(mapping)