Pandas:按时间间隔滚动平均值
Pandas:按时间间隔滚动平均值
提问于 2013-04-03 02:22:45
我有一堆民意调查数据；我想计算Pandas滚动平均值，以获得基于三天窗口的每一天的估计值。根据this question的说法，rolling_*函数基于指定数量的值来计算窗口，而不是基于特定的日期时间范围。
如何实现此功能？
输入数据示例：
polls_subset.tail(20)
Out[185]:
favorable unfavorable other
enddate
2012-10-25 0.48 0.49 0.03
2012-10-25 0.51 0.48 0.02
2012-10-27 0.51 0.47 0.02
2012-10-26 0.56 0.40 0.04
2012-10-28 0.48 0.49 0.04
2012-10-28 0.46 0.46 0.09
2012-10-28 0.48 0.49 0.03
2012-10-28 0.49 0.48 0.03
2012-10-30 0.53 0.45 0.02
2012-11-01 0.49 0.49 0.03
2012-11-01 0.47 0.47 0.05
2012-11-01 0.51 0.45 0.04
2012-11-03 0.49 0.45 0.06
2012-11-04 0.53 0.39 0.00
2012-11-04 0.47 0.44 0.08
2012-11-04 0.49 0.48 0.03
2012-11-04 0.52 0.46 0.01
2012-11-04 0.50 0.47 0.03
2012-11-05 0.51 0.46 0.02
2012-11-07 0.51 0.41 0.00对于每个日期，输出将只有一行。
回答 7
Stack Overflow用户
回答已采纳
发布于 2016-10-07 20:56:30
同时，添加了时间窗口功能。请参阅此link。
In [1]: df = DataFrame({'B': range(5)})
In [2]: df.index = [Timestamp('20130101 09:00:00'),
...: Timestamp('20130101 09:00:02'),
...: Timestamp('20130101 09:00:03'),
...: Timestamp('20130101 09:00:05'),
...: Timestamp('20130101 09:00:06')]
In [3]: df
Out[3]:
B
2013-01-01 09:00:00 0
2013-01-01 09:00:02 1
2013-01-01 09:00:03 2
2013-01-01 09:00:05 3
2013-01-01 09:00:06 4
In [4]: df.rolling(2, min_periods=1).sum()
Out[4]:
B
2013-01-01 09:00:00 0.0
2013-01-01 09:00:02 1.0
2013-01-01 09:00:03 3.0
2013-01-01 09:00:05 5.0
2013-01-01 09:00:06 7.0
In [5]: df.rolling('2s', min_periods=1).sum()
Out[5]:
B
2013-01-01 09:00:00 0.0
2013-01-01 09:00:02 1.0
2013-01-01 09:00:03 3.0
2013-01-01 09:00:05 3.0
2013-01-01 09:00:06 7.0Stack Overflow用户
发布于 2013-08-27 21:38:11
我也有同样的问题，但是数据点的间距是不规则的。重采样在这里并不是一个真正的选项。所以我创建了我自己的函数。也许它也会对其他人有用：
from pandas import Series, DataFrame
import pandas as pd
from datetime import datetime, timedelta
import numpy as np
def rolling_mean(data, window, min_periods=1, center=False):
''' Function that computes a rolling mean
Parameters
----------
data : DataFrame or Series
If a DataFrame is passed, the rolling_mean is computed for all columns.
window : int or string
If int is passed, window is the number of observations used for calculating
the statistic, as defined by the function pd.rolling_mean()
If a string is passed, it must be a frequency string, e.g. '90S'. This is
internally converted into a DateOffset object, representing the window size.
min_periods : int
Minimum number of observations in window required to have a value.
Returns
-------
Series or DataFrame, if more than one column
'''
def f(x):
'''Function to apply that actually computes the rolling mean'''
if center == False:
dslice = col[x-pd.datetools.to_offset(window).delta+timedelta(0,0,1):x]
# adding a microsecond because when slicing with labels start and endpoint
# are inclusive
else:
dslice = col[x-pd.datetools.to_offset(window).delta/2+timedelta(0,0,1):
x+pd.datetools.to_offset(window).delta/2]
if dslice.size < min_periods:
return np.nan
else:
return dslice.mean()
data = DataFrame(data.copy())
dfout = DataFrame()
if isinstance(window, int):
dfout = pd.rolling_mean(data, window, min_periods=min_periods, center=center)
elif isinstance(window, basestring):
idx = Series(data.index.to_pydatetime(), index=data.index)
for colname, col in data.iterkv():
result = idx.apply(f)
result.name = colname
dfout = dfout.join(result, how='outer')
if dfout.columns.size == 1:
dfout = dfout.ix[:,0]
return dfout
# Example
idx = [datetime(2011, 2, 7, 0, 0),
datetime(2011, 2, 7, 0, 1),
datetime(2011, 2, 7, 0, 1, 30),
datetime(2011, 2, 7, 0, 2),
datetime(2011, 2, 7, 0, 4),
datetime(2011, 2, 7, 0, 5),
datetime(2011, 2, 7, 0, 5, 10),
datetime(2011, 2, 7, 0, 6),
datetime(2011, 2, 7, 0, 8),
datetime(2011, 2, 7, 0, 9)]
idx = pd.Index(idx)
vals = np.arange(len(idx)).astype(float)
s = Series(vals, index=idx)
rm = rolling_mean(s, window='2min')Stack Overflow用户
发布于 2014-10-09 08:14:33
User2689410的代码正是我所需要的。提供我的版本(credits to user2689410)，由于在DataFrame中一次计算整行的平均值，因此速度更快。
希望我的后缀约定是可读的：_s: string、_i: int、_b: bool、_ser: Series和_df: DataFrame。在您找到多个后缀的地方，type可以是both。
import pandas as pd
from datetime import datetime, timedelta
import numpy as np
def time_offset_rolling_mean_df_ser(data_df_ser, window_i_s, min_periods_i=1, center_b=False):
""" Function that computes a rolling mean
Credit goes to user2689410 at http://stackoverflow.com/questions/15771472/pandas-rolling-mean-by-time-interval
Parameters
----------
data_df_ser : DataFrame or Series
If a DataFrame is passed, the time_offset_rolling_mean_df_ser is computed for all columns.
window_i_s : int or string
If int is passed, window_i_s is the number of observations used for calculating
the statistic, as defined by the function pd.time_offset_rolling_mean_df_ser()
If a string is passed, it must be a frequency string, e.g. '90S'. This is
internally converted into a DateOffset object, representing the window_i_s size.
min_periods_i : int
Minimum number of observations in window_i_s required to have a value.
Returns
-------
Series or DataFrame, if more than one column
>>> idx = [
... datetime(2011, 2, 7, 0, 0),
... datetime(2011, 2, 7, 0, 1),
... datetime(2011, 2, 7, 0, 1, 30),
... datetime(2011, 2, 7, 0, 2),
... datetime(2011, 2, 7, 0, 4),
... datetime(2011, 2, 7, 0, 5),
... datetime(2011, 2, 7, 0, 5, 10),
... datetime(2011, 2, 7, 0, 6),
... datetime(2011, 2, 7, 0, 8),
... datetime(2011, 2, 7, 0, 9)]
>>> idx = pd.Index(idx)
>>> vals = np.arange(len(idx)).astype(float)
>>> ser = pd.Series(vals, index=idx)
>>> df = pd.DataFrame({'s1':ser, 's2':ser+1})
>>> time_offset_rolling_mean_df_ser(df, window_i_s='2min')
s1 s2
2011-02-07 00:00:00 0.0 1.0
2011-02-07 00:01:00 0.5 1.5
2011-02-07 00:01:30 1.0 2.0
2011-02-07 00:02:00 2.0 3.0
2011-02-07 00:04:00 4.0 5.0
2011-02-07 00:05:00 4.5 5.5
2011-02-07 00:05:10 5.0 6.0
2011-02-07 00:06:00 6.0 7.0
2011-02-07 00:08:00 8.0 9.0
2011-02-07 00:09:00 8.5 9.5
"""
def calculate_mean_at_ts(ts):
"""Function (closure) to apply that actually computes the rolling mean"""
if center_b == False:
dslice_df_ser = data_df_ser[
ts-pd.datetools.to_offset(window_i_s).delta+timedelta(0,0,1):
ts
]
# adding a microsecond because when slicing with labels start and endpoint
# are inclusive
else:
dslice_df_ser = data_df_ser[
ts-pd.datetools.to_offset(window_i_s).delta/2+timedelta(0,0,1):
ts+pd.datetools.to_offset(window_i_s).delta/2
]
if (isinstance(dslice_df_ser, pd.DataFrame) and dslice_df_ser.shape[0] < min_periods_i) or \
(isinstance(dslice_df_ser, pd.Series) and dslice_df_ser.size < min_periods_i):
return dslice_df_ser.mean()*np.nan # keeps number format and whether Series or DataFrame
else:
return dslice_df_ser.mean()
if isinstance(window_i_s, int):
mean_df_ser = pd.rolling_mean(data_df_ser, window=window_i_s, min_periods=min_periods_i, center=center_b)
elif isinstance(window_i_s, basestring):
idx_ser = pd.Series(data_df_ser.index.to_pydatetime(), index=data_df_ser.index)
mean_df_ser = idx_ser.apply(calculate_mean_at_ts)
return mean_df_ser页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/15771472
复制相关文章
相似问题
腾讯云开发者
