How to interpolate only between values (stopping before and after last NaN in a column) with pandas?
<p>If I have a <code>df</code> similar to this one: </p>

<pre><code>print(df)
                       A  B  C    D    E
 DATE_TIME                               
2016-08-10 13:57:00  3.6  A  1  NaN  NaN
2016-08-10 13:58:00  4.7  A  1  4.5  NaN
2016-08-10 13:59:00  3.4  A  0  NaN  5.7
2016-08-10 14:00:00  3.5  A  0  NaN  NaN
2016-08-10 14:01:00  2.6  A  0  4.6  NaN
2016-08-10 14:02:00  4.8  A  0  NaN  4.3
2016-08-10 14:03:00  5.7  A  1  NaN  NaN
2016-08-10 14:04:00  5.5  A  1  5.7  NaN
2016-08-10 14:05:00  5.6  A  1  NaN  NaN
2016-08-10 14:06:00  7.8  A  1  NaN  5.2
2016-08-10 14:07:00  8.9  A  0  NaN  NaN
2016-08-10 14:08:00  3.6  A  0  NaN  NaN

print (df.dtypes)
A    float64
B     object
C      int64
D    float64
E    float64
dtype: object
</code></pre>

<p>Thanks to a lot of input from the community I have this code now which allows me to upsample my df to second intervals, applying different methods to different <code>dtypes</code> </p>

<pre><code>int_cols = df.select_dtypes(['int64']).columns
index = pd.date_range(df.index[0], df.index[-1], freq="s")
df2 = df.reindex(index)

for col in df2:
if col == int_cols.all(): 
    df2[col].ffill(inplace=True)
    df2[col] = df2[col].astype(int)
elif df2[col].dtype == float:
    df2[col].interpolate(inplace=True)
else:
    df2[col].ffill(inplace=True)
</code></pre>

<p>I am looking for a way now, to only interpolate between my actual measurements. The interpolate function extends my last measurement until the end of the <code>df</code>: </p>

<pre><code> df2.tail()
Out[75]: 
                            A  B  C    D    E
2016-08-10 14:07:56  3.953333  A  0  5.7  5.2
2016-08-10 14:07:57  3.865000  A  0  5.7  5.2
2016-08-10 14:07:58  3.776667  A  0  5.7  5.2
2016-08-10 14:07:59  3.688333  A  0  5.7  5.2
2016-08-10 14:08:00  3.600000  A  0  5.7  5.2
</code></pre>

<p>But I would like to stop this when the last measurement took place (for example at 14:04:00 <code>col['D']</code> and 14:06:00 <code>col['D']</code>) and leave the NaNs. </p>

<p>It tried adding a zero value for 'limit' and 'limit_direction' to 'both':</p>

<pre><code> for col in df2:
if col == int_cols.all(): 
    df2[col].ffill(inplace=True)
    df2[col] = df2[col].astype(int)
elif df2[col].dtype == float:
    df2[col].interpolate(inplace=True,limit=0, limit_direction='both')
else:
    df2[col].ffill(inplace=True)
</code></pre>

<p>but this didn't change anything to the output. I than tried to incorporate the solution I found to this question: <a href="http://stackoverflow.com/questions/33691591/pandas-interpolation-where-first-and-last-data-point-in-column-is-nan">Pandas: interpolation where first and last data point in column is NaN</a> into my code:</p>

<pre><code>for col in df2:
if col == int_cols.all(): 
    df2[col].ffill(inplace=True)
    df2[col] = df2[col].astype(int)
elif df2[col].dtype == float:
   df2[col].loc[df2[col].first_valid_index(): df2[col].last_valid_index()]=df2[col].loc[df2[col].first_valid_index(): df2[col].last_valid_index()].astype(float).interpolate(inplace=True)
else:
    df2[col].ffill(inplace=True)
</code></pre>

<p>...but that did not work and my <code>float64</code> columns are purely NaNs now...Also, the way I tried to insert the code, I know it would only have affected the <code>float</code> columns. In an ideal solution I would hope to do the set this <code>first_valid_index():.last_valid_index()</code> selection also to the <code>object</code> and <code>int64</code> columns. Can somebody help me? ..thank you </p>

<p>You were very close! Here's an example to get the point across that's very similar to the code you posted toward the end of your post:</p>

<pre><code>import numpy as np
import pandas as pd

df = pd.DataFrame({'A': [np.nan, 1.0, np.nan, np.nan, 4.0, np.nan, np.nan],
                   'B': [np.nan, np.nan, 0.0, np.nan, np.nan, 2.0, np.nan]},
                  columns=['A', 'B'], 
                  index=pd.date_range(start='2016-08-10 13:50:00', periods=7, freq='S'))
print df

A_first = df['A'].first_valid_index()
A_last = df['A'].last_valid_index()
df.loc[A_first:A_last, 'A'] = df.loc[A_first:A_last, 'A'].interpolate()

B_first = df['B'].first_valid_index()
B_last = df['B'].last_valid_index()
df.loc[B_first:B_last, 'B'] = df.loc[B_first:B_last, 'B'].interpolate()

print df
</code></pre>

<p>result:</p>

<pre><code>                       A    B
2016-08-10 13:50:00  NaN  NaN
2016-08-10 13:50:01  1.0  NaN
2016-08-10 13:50:02  NaN  0.0
2016-08-10 13:50:03  NaN  NaN
2016-08-10 13:50:04  4.0  NaN
2016-08-10 13:50:05  NaN  2.0
2016-08-10 13:50:06  NaN  NaN

                       A         B
2016-08-10 13:50:00  NaN       NaN
2016-08-10 13:50:01  1.0       NaN
2016-08-10 13:50:02  2.0  0.000000
2016-08-10 13:50:03  3.0  0.666667
2016-08-10 13:50:04  4.0  1.333333
2016-08-10 13:50:05  NaN  2.000000
2016-08-10 13:50:06  NaN       NaN
</code></pre>

<p>The two problems in your code were:</p>

<ol>
<li>If you are going to do <code>df[...] = df[...].interpolate()</code>, you need to
remove <code>inplace=True</code> since that will make it return <code>None</code>. That is your main problem and why you were getting all <code>NaNs</code>.   </li>
<li>Though it seems to work here, in general, chained indexing is bad:</li>
</ol>

<p>You want:</p>

<pre><code>df.loc[A_first:A_last, 'A'] = df.loc[A_first:A_last, 'A'].interpolate()
</code></pre>

<p>Not: </p>

<pre><code>df['A'].loc[A_first:A_last] = df['A'].loc[A_first:A_last].interpolate()
</code></pre>

<p>See here for more detail: <a href="http://pandas.pydata.org/pandas-docs/stable/indexing.html#indexing-view-versus-copy" rel="nofollow">http://pandas.pydata.org/pandas-docs/stable/indexing.html#indexing-view-versus-copy</a></p>

python pandas interpolation