Pandas, Filling between dates with average change between previous rows

Vote count:

0

I think this is best illustrated with examples. Lets say we have a DataFrame like such:

                 295340      299616
2014-11-02   304.904110  157.123288
2014-12-02   597.303413  305.488493
2015-01-02   896.310372  454.614630
2015-02-02  1192.379580  599.588466
2015-02-04  1211.285484         NaN
2015-03-02          NaN  726.622932

Now let's say I want to reindex this, like such:

rng = pd.date_range(df.index[0], df.index[-1])

df.reindex(rng)
                 295340      299616
2014-11-02   304.904110  157.123288
2014-11-03          NaN         NaN
2014-11-04          NaN         NaN
2014-11-05          NaN         NaN
2014-11-06          NaN         NaN
2014-11-07          NaN         NaN
2014-11-08          NaN         NaN
2014-11-09          NaN         NaN
2014-11-10          NaN         NaN
2014-11-11          NaN         NaN
2014-11-12          NaN         NaN
2014-11-13          NaN         NaN
2014-11-14          NaN         NaN
2014-11-15          NaN         NaN
2014-11-16          NaN         NaN
2014-11-17          NaN         NaN
2014-11-18          NaN         NaN
2014-11-19          NaN         NaN
2014-11-20          NaN         NaN
2014-11-21          NaN         NaN
2014-11-22          NaN         NaN
2014-11-23          NaN         NaN
2014-11-24          NaN         NaN
2014-11-25          NaN         NaN
2014-11-26          NaN         NaN
2014-11-27          NaN         NaN
2014-11-28          NaN         NaN
2014-11-29          NaN         NaN
2014-11-30          NaN         NaN
2014-12-01          NaN         NaN
2014-12-02   597.303413  305.488493

Now if we look at 295340, we see the difference between their values is (597.30-304.90) = 292.39.

The amount of days between the two values is 31. So the average increase is 9.43 a day.

So what I would want is something like such:

                 295340      299616
2014-11-02   304.904110  157.123288
2014-11-03   314.336345         NaN
2014-11-04   323.768581         NaN
2014-11-05   333.200816         NaN

The way I calculated that was:

304.904110 + (((597.303413-304.904110) / 31) * N)

Where N is 1 for the first row since row 1, 2 after, etc.

I would obviously want all the columns filled this way, so 299616 with the same method and such.

Any ideas for something that is as efficient as possible? I know of ways to do this, but nothing seems efficient and it seems like there should be some type of fillna() or something that works for this type of finance related problem.

NOTE: The columns will not all be spaced out the same. Each one can have numbers anywhere within the range of dates, so I can't just assume that the next number for each column will be at X date.

asked 15 secs ago

user1610719

136

Pandas, Filling between dates with average change between previous rows