donald donald - 2 months ago 15
Python Question

python - Pandas - FillNa with another non null row having similar column

I would like to fill missing value in one column with the value of another column.

I read that looping through each row would be very bad practice and that it would be better to do everything in one go but I could not find out how to do it with the fillna method.

Data Before

Day Cat1 Cat2
1 cat ant
2 dog elephant
3 cat giraf
4 NaN ant


Data After

Day Cat1 Cat2
1 cat ant
2 dog elephant
3 cat giraf
4 cat ant

Answer

You can use fillna and pass the df without NaN rows, setting the index to Cat2 and then calling map which will perform a lookup:

In [108]:
df['Cat1'] = df['Cat1'].fillna(df['Cat2'].map(df.dropna().set_index('Cat2')['Cat1']))
df

Out[108]:
   Day Cat1      Cat2
0    1  cat       ant
1    2  dog  elephant
2    3  cat     giraf
3    4  cat       ant

So here I drop the NaN rows, and set the index to Cat2, by calling map on this it will lookup Cat1values whereCat2` matches

Here is the result of the map:

In [112]:
df['Cat2'].map(df.dropna().set_index('Cat2')['Cat1'])

Out[112]:
0    cat
1    dog
2    cat
3    cat
Name: Cat2, dtype: object
Comments