python - Pandas df.resample() : Specify NaN threshold for calculation of mean

Question

我想使用 how=mean 方法将 pandas 数据帧的频率从每小时重新采样为每年/每天。但是，当然，一年中会丢失一些每小时的数据。

在均值也设置为 NaN 之前，如何为允许的 NaN 比率设置阈值？考虑到在文档中我找不到任何东西......

提前致谢!

Answer 1

这是一个使用 groupby 的简单解决方案。

# Test data
start_date = pd.to_datetime('2015-01-01')
pd.date_range(start=start_date, periods=365*24, freq='H')
number = 365*24
df = pd.DataFrame(np.random.randint(1,10, number),index=pd.date_range(start=start_date, periods=number, freq='H'), columns=['values'])
# Generating some NaN to simulate less values on the first day
na_range = pd.date_range(start=start_date, end=start_date +  3 * Hour(), freq='H')
df.loc[na_range,'values'] = np.NaN

# grouping by day, computing the mean and the count
df = df.groupby(df.index.date).agg(['mean', 'count'])
df.columns = df.columns.droplevel()

# Populating the mean only if the number of values (count) is > to the threshold
df['values'] = np.NaN
df.loc[df['count']>=20, 'values'] = df['mean']
print(df.head)

# Result
                mean  count  values
2015-01-01  4.947368     20     NaN
2015-01-02  5.125000     24   5.125
2015-01-03  4.875000     24   4.875
2015-01-04  5.750000     24   5.750
2015-01-05  4.875000     24   4.875