python - Pandas - FillNa with another non null row having similar column
<p>I would like to fill missing value in one column with the value of another column.</p>

<p>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.</p>

<p>Data Before</p>

<pre><code>Day  Cat1  Cat2
1    cat   ant
2    dog   elephant
3    cat   giraf
4    NaN   ant
</code></pre>

<p>Data After</p>

<pre><code>Day  Cat1  Cat2
1    cat   ant
2    dog   elephant
3    cat   giraf
4    cat   ant
</code></pre>

<p>You can use <a href="http://pandas.pydata.org/pandas-docs/stable/generated/pandas.Series.fillna.html" rel="nofollow"><code>fillna</code></a> and pass the df without <code>NaN</code> rows, setting the index to <code>Cat2</code> and then calling <a href="http://pandas.pydata.org/pandas-docs/stable/generated/pandas.Series.map.html#pandas.Series.map" rel="nofollow"><code>map</code></a> which will perform a lookup:</p>

<pre><code>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
</code></pre>

<p>So here I drop the <code>NaN</code> rows, and set the index to <code>Cat2</code>, by calling <code>map</code> on this it will lookup Cat1<code>values where</code>Cat2` matches</p>

<p>Here is the result of the <code>map</code>:</p>

<pre><code>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
</code></pre>

python pandas numpy jupyter-notebook