Efficient way to select most recent index with finite value in column from Pandas DataFrame?
<p>I'm trying to find the most recent index with a value that is not 'NaN' relative to the current index. So, say I have a DataFrame with 'NaN' values like this:</p>

<pre><code>       A       B       C
0    2.1     5.3     4.7
1    5.1     4.6     NaN
2    5.0     NaN     NaN
3    7.4     NaN     NaN
4    3.5     NaN     NaN
5    5.2     1.0     NaN
6    5.0     6.9     5.4
7    7.4     NaN     NaN
8    3.5     NaN     5.8
</code></pre>

<p>If I am currently at index 4, I have the values:</p>

<pre><code>       A       B       C
4    3.5     NaN     NaN
</code></pre>

<p>I want to know the last known value of 'B' relative to index 4, which is at index <code>1</code>:</p>

<pre><code>       A       B       C
1    5.1   -&gt; 4.6    NaN
</code></pre>

<p>I know I can get a list of all indexes with NaN values using something like:</p>

<pre><code>indexes = df.index[df['B'].apply(np.isnan)]
</code></pre>

<p>But this seems inefficient in a large database. Is there a way to <code>tail</code> just the last one relative to the current index?</p>

<p>You may try something like this, convert the <code>index</code> to a series that have the same <code>NaN</code> values as column <code>B</code> and then use <code>ffill()</code> which carries the last non missing index forward for all subsequent <code>NaN</code>s:</p>

<pre><code>import pandas as pd
import numpy as np
df['Last_index_notnull'] = df.index.to_series().where(df.B.notnull(), np.nan).ffill()
df['Last_value_notnull'] = df.B.ffill()
df
</code></pre>

<p><a href="https://i.stack.imgur.com/b7p1B.png"><img src="https://i.stack.imgur.com/b7p1B.png" alt="enter image description here"></a></p>

<p>Now at index <code>4</code>, you know the last non missing value is <code>4.6</code> and index is <code>1</code>.</p>

python pandas numpy dataframe