How to replace nan values of a column based on certain values of other column

Question

I've two columns, col1 refers to level of education and col2 to their job. col2 have some nan values, so I want to replace this nan values based on the value of column 1. for example if col1='bachelor' then col2 must be ='teacher' if col1='high school' then col2='actor'.. and so on, I have 7 different values of col1.

I've tried to create a function like this:

def rep_nan(x):
    if x['col1']=='bachelor':
        x['col2']='teacher'
    elif x['col1']=='blabla':
        x['col2']='blabla'
    .....
    elif x['col1']='high school':
        x['col2']='actor'

then I applied to my dataset:

df.apply(rep_nan,axis=1)

but I get as result a None column

where is the error? or how could I do this task?

Answer 1

You can make a dictionary here:

rep_nan = {
    'bachelor': 'tacher',
    'blabla': 'blabla',
    'high school': 'actor'
}

Then we can replace the nan values with:

df.loc[df['col2'].isnull(), 'col2'] = df[df['col2'].isnull()]['col1'].replace(rep_nan)

For example:

>>> df
          col1   col2
0     bachelor   None
1     bachelor  clown
2       blabla   None
3  high school   None
>>> df.loc[df['col2'].isnull(), 'col2'] = df[df['col2'].isnull()]['col1'].replace(rep_nan)
>>> df
          col1    col2
0     bachelor  tacher
1     bachelor   clown
2       blabla  blabla
3  high school   actor