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>some useful methods to know</p>

<p><strong><em><code>last_valid_index</code></em></strong><br>
<strong><em><code>first_valid_index</code></em></strong><br>
for columns <code>B</code> as of index <code>4</code></p>

<pre><code>df.B.ix[:4].last_valid_index()

1
</code></pre>

<p>you can use this for all columns in this way</p>

<pre><code>pd.concat([df.ix[:i].apply(pd.Series.last_valid_index) for i in df.index],
          axis=1).T
</code></pre>

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

python pandas numpy dataframe