用日期时间索引插值并填充 pandas 数据框
我正在尝试插入一个具有datetimeIndex索引的数据框.
Hi I'm trying to interpolate a Dataframe where I have a datetimeIndex index.
这是数据
res = pd.DataFrame(cursor.execute("SELECT DATETIME,VALUE FROM {} WHERE DATETIME > ? AND DATETIME < ?".format(table),[start,end]).fetchall(),columns=['date','value'])
res.set_index('date',inplace=True)
产生
2013-01-31 00:00:00 517
2012-12-31 00:00:00 263
2012-11-30 00:00:00 1917
2012-10-31 00:00:00 391
2012-09-30 00:00:00 782
2012-08-31 00:00:00 700
2012-07-31 00:00:00 799
2012-06-30 00:00:00 914
2012-05-31 00:00:00 141
2012-04-30 00:00:00 342
2012-03-31 00:00:00 199
2012-02-29 00:00:00 533
2012-01-31 00:00:00 1393
2011-12-31 00:00:00 497
2011-11-30 00:00:00 1457
2011-10-31 00:00:00 997
2011-09-30 00:00:00 533
2011-08-31 00:00:00 626
2011-07-31 00:00:00 1933
2011-06-30 00:00:00 4248
2011-05-31 00:00:00 1248
2011-04-30 00:00:00 904
2011-03-31 00:00:00 3280
2011-02-28 00:00:00 390
2011-01-31 00:00:00 601
2010-12-31 00:00:00 423
2010-11-30 00:00:00 748
2010-10-31 00:00:00 433
2010-09-30 00:00:00 734
2010-08-31 00:00:00 845
2010-07-31 00:00:00 1693
2010-06-30 00:00:00 2742
2010-05-31 00:00:00 669
这都是不连续的.我想要一个每日值，所以想使用某种插值法来填写缺失的值.
This is all non contiguous. I want to have a daily value so, want to fill in the missing values using some kind of interpolation.
首先尝试设置索引，然后进行插值.
First tried to set the index and then interpolate.
new_index = pd.date_range(date(2010,1,1),date(2014,1,31),freq='D')
df2 = res.reindex(new_index) # This returns NaN
df2.interpolate('cubic') # Fails with error TypeError: Cannot interpolate with all NaNs.
我希望得到的是一个数据帧，其中每个日期值介于2010年至2014年之间，并根据其周围的点计算出一个插值.
What I would hope to get back is a dataframe with each date value between 2010-2014, with a interpolated value calculated from the points surrounding it.
似乎可以简单地做到这一点，但是我不确定.
It seems like there probably is a way to do this simply, but I'm not sure what.
这是一种实现方法.
首先从df.index个日期的max min中获得一个新索引
First get a new index from max min of df.index dates
In [152]: df_reindexed = df.reindex(pd.date_range(start=df.index.min(),
end=df.index.max(),
freq='1D'))
然后在系列上使用interpolate(method='linear')来获取值.
Then use interpolate(method='linear') on the series to get values.
In [153]: df_reindexed.interpolate(method='linear')
Out[153]:
Value
2010-05-31 669.000000
2010-06-01 738.100000
2010-06-02 807.200000
2010-06-03 876.300000
2010-06-04 945.400000
2010-06-05 1014.500000
...
2013-01-25 467.838710
2013-01-26 476.032258
2013-01-27 484.225806
2013-01-28 492.419355
2013-01-29 500.612903
2013-01-30 508.806452
2013-01-31 517.000000
[977 rows x 1 columns]