生成数据
random.seed(42)
date_rng = pd.date_range(start='1/1/2018', end='1/08/2018', freq='H')
df = pd.DataFrame(np.random.randint(0,10,size=(len(date_rng), 3)),
columns=['data1', 'data2', 'data3'],
index= date_rng)
daily_mean_df = pd.DataFrame(np.zeros([len(date_rng), 3]),
columns=['data1', 'data2', 'data3'],
index= date_rng)
mask = np.random.choice([1, 0], df.shape, p=[.35, .65]).astype(bool)
df[mask] = np.nan
# Data column to calculate each day
day = [['data1', 'data2'],
['data1', 'data2'],
['data2', 'data3'],
['data1', 'data3'],
['data2', 'data3'],
['data1', 'data2'],
['data2', 'data3'],
['data1', 'data3']]
我想按天选择数据,对于每一天,我将根据day
变量给出的一些列(每天不同(计算平均值。
预期成果:
mean
2018-01-01 00:00:00 1.0
2018-01-01 01:00:00 5.0
2018-01-01 02:00:00 2.0
2018-01-01 03:00:00 6.0
2018-01-01 04:00:00 5.5
... ...
2018-01-07 20:00:00 4.0
2018-01-07 21:00:00 7.0
2018-01-07 22:00:00 5.0
2018-01-07 23:00:00 NaN
2018-01-08 00:00:00 2.0
我知道我可以循环一天并通过以下方式计算:
i = 0
b = 0
for day_ in np.unique(test.index.date):
a = test[test.index.date == day_]
print(day_)
print(b, len(a))
daily_mean_df.iloc[b:b+len(a), 0] = np.mean(a[~np.isnan(a[day[i]])], axis = 1)
i += 1
b += len(a)
但是,对于大型数据集,此方法将花费很长时间。 请建议我有效地计算这个问题的方法。
的想法是创建具有相同索引的布尔掩码,就像第一DataFrame
每行具有get_dummies
和max
以及DataFrame.reindex
具有method='ffill'
:
day = [['data1', 'data2'],
['data1', 'data2'],
['data2', 'data3'],
['data1', 'data3'],
['data2', 'data3'],
['data1', 'data2'],
['data2', 'data3'],
['data1', 'data3']]
df1 = pd.DataFrame(day, index=df.index.floor('d').unique())
df1 = pd.get_dummies(df1, prefix='', prefix_sep='', dtype=bool).max(axis=1, level=0)
df1 = df1.reindex(df.index, method='ffill')
print (df1.head())
data1 data2 data3
2018-01-01 00:00:00 True True False
2018-01-01 01:00:00 True True False
2018-01-01 02:00:00 True True False
2018-01-01 03:00:00 True True False
2018-01-01 04:00:00 True True False
因此,您可以通过DataFrame.where
将不匹配的值替换为NaN
s 并计算mean
:
df = df.where(df1).mean(axis=1).to_frame('mean')
print (df)
mean
2018-01-01 00:00:00 6.0
2018-01-01 01:00:00 NaN
2018-01-01 02:00:00 4.0
2018-01-01 03:00:00 4.0
2018-01-01 04:00:00 4.5
...
2018-01-07 20:00:00 NaN
2018-01-07 21:00:00 6.0
2018-01-07 22:00:00 5.0
2018-01-07 23:00:00 3.0
2018-01-08 00:00:00 5.0
[169 rows x 1 columns]
设置:
np.random.seed(42)
date_rng = pd.date_range(start='1/1/2018', end='1/08/2018', freq='H')
df = pd.DataFrame(np.random.randint(0,10,size=(len(date_rng), 3)),
columns=['data1', 'data2', 'data3'],
index= date_rng)
daily_mean_df = pd.DataFrame(np.zeros([len(date_rng), 1]),
columns=['data1'],
index= date_rng)
mask = np.random.choice([1, 0], df.shape, p=[.35, .65]).astype(bool)
df[mask] = np.nan
验证解决方案:
i = 0
b = 0
for day_ in np.unique(df.index.date):
a = df[df.index.date == day_]
# print(day_)
# print(b, len(a))
daily_mean_df.iloc[b:b+len(a), 0] = np.mean(a[~np.isnan(a[day[i]])], axis = 1)
i += 1
b += len(a)
print (daily_mean_df)
data1
2018-01-01 00:00:00 6.0
2018-01-01 01:00:00 NaN
2018-01-01 02:00:00 4.0
2018-01-01 03:00:00 4.0
2018-01-01 04:00:00 4.5
...
2018-01-07 20:00:00 NaN
2018-01-07 21:00:00 6.0
2018-01-07 22:00:00 5.0
2018-01-07 23:00:00 3.0
2018-01-08 00:00:00 5.0
[169 rows x 1 columns]
您可以从日期时间索引中提取日期,如下所示:
df["day"] = df.index.floor('D')
然后按此新列分组并计算每组的平均值:
df.groupby("day").mean()
结果:
data1 data2 data3
day
2018-01-01 4.428571 3.714286 5.454545
2018-01-02 5.066667 5.444444 5.222222
2018-01-03 4.850000 3.705882 4.812500
2018-01-04 4.555556 4.818182 3.764706
2018-01-05 4.000000 4.058824 3.818182
2018-01-06 6.058824 4.588235 4.866667
2018-01-07 4.176471 4.071429 5.055556
2018-01-08 4.400000 4.400000 4.285714
请注意,默认情况下,pandas.mean 跳过 NaN 值,但可以使用 skipna 参数进行更改。