英文:
Calculate the rolling average every two weeks for the same day and hour in a DataFrame
问题
我有一个类似以下的数据框：
df = pd.DataFrame()df['datetime'] = pd.date_range(start='2023-1-2', end='2023-1-29', freq='15min')df['week'] = df['datetime'].apply(lambda x: int(x.isocalendar()[1]))df['day_of_week'] = df['datetime'].dt.weekdaydf['hour'] = df['datetime'].dt.hourdf['minutes'] = pd.DatetimeIndex(df['datetime']).minutedf['value'] = range(len(df))df.set_index('datetime', inplace=True)
我想要计算相同小时/分钟/日的"value"列的平均值，每两周连续的一组。我希望得到以下结果：
df=valueday_of_week hour minutes datetime0 0 0 2023-01-02 00:00:00 NaN2023-01-09 00:00:00 NaN2023-01-16 00:00:00 3362023-01-23 00:00:00 100815 2023-01-02 00:15:00 NaN2023-01-09 00:15:00 NaN2023-01-16 00:15:00 3372023-01-23 00:15:00 1009
所以前两周应该有NaN值，第三周应该是第一周和第二周的平均值，然后第四周应该是第二周和第三周的平均值，以此类推。我尝试了以下代码，但它似乎不符合我的预期：
df = pd.DataFrame(df.groupby(['day_of_week', 'hour', 'minutes'])['value'].rolling(window='14D', min_periods=1).mean())
因为我得到的结果是：
valueday_of_week hour minutes datetime0 0 0 2023-01-02 00:00:00 02023-01-09 00:00:00 3362023-01-16 00:00:00 10082023-01-23 00:00:00 168015 2023-01-02 00:15:00 12023-01-09 00:15:00 3372023-01-16 00:15:00 10092023-01-23 00:15:00 1681
我认为你可以尝试以下代码来获得你想要的结果：
# 计算每两周的平均值df['average_value'] = df.groupby(['day_of_week', 'hour', 'minutes'])['value'].rolling(window=14, min_periods=1).mean().reset_index(level=0, drop=True)# 将结果重塑为你想要的形式result = df[['average_value']].unstack(0)# 重新命名列result.columns = [f'week-{i}' for i in range(1, len(result.columns) + 1)]# 重置索引result = result.reset_index()result = result.rename_axis(None, axis=1)# 创建目标日期列表target_dates = pd.date_range(start='2023-01-02', end='2023-01-29', freq='D')# 将目标日期与结果合并result['datetime'] = target_datesresult.set_index('datetime', inplace=True)# 移动结果列以匹配你的期望result = result[['day_of_week', 'hour', 'minutes'] + [f'week-{i}' for i in range(1, len(result.columns))]]# 填充NaN值result = result.fillna(method='ffill')# 打印结果print(result)
这应该给你想要的结果。
英文:
I have a Dataframe like the following:
df = pd.DataFrame()df['datetime'] = pd.date_range(start='2023-1-2', end='2023-1-29', freq='15min')df['week'] = df['datetime'].apply(lambda x: int(x.isocalendar()[1]))df['day_of_week'] = df['datetime'].dt.weekdaydf['hour'] = df['datetime'].dt.hourdf['minutes'] = pd.DatetimeIndex(df['datetime']).minutedf['value'] = range(len(df))df.set_index('datetime',inplace=True)df = week day_of_week hour minutes valuedatetime2023-01-02 00:00:00 1 0 0 0 02023-01-02 00:15:00 1 0 0 15 12023-01-02 00:30:00 1 0 0 30 22023-01-02 00:45:00 1 0 0 45 32023-01-02 01:00:00 1 0 1 0 4... ... ... ... ... ...2023-01-08 23:00:00 1 6 23 0 6682023-01-08 23:15:00 1 6 23 15 6692023-01-08 23:30:00 1 6 23 30 6702023-01-08 23:45:00 1 6 23 45 6712023-01-09 00:00:00 2 0 0 0 672
And I want to calculate the average of the column "value" for the same hour/minute/day, every two consecutive weeks.
What I would like to get is the following:
df=valueday_of_week hour minutes datetime0 0 0 2023-01-02 00:00:00 NaN2023-01-09 00:00:00 NaN2023-01-16 00:00:00 3362023-01-23 00:00:00 100815 2023-01-02 00:15:00 NaN2023-01-09 00:15:00 NaN2023-01-16 00:15:00 3372023-01-23 00:15:00 1009
So the first two weeks should have NaN values and week-3 should be the average of week-1 and week-2 and then week-4 the average of week-2 and week-3 and so on.
I tried the following code but it does not seem to do what I expect:
df = pd.DataFrame(df.groupby(['day_of_week','hour','minutes'])['value'].rolling(window='14D', min_periods=1).mean())
As what I am getting is:
valueday_of_week hour minutes. datetime0 0 0 2023-01-02 00:00:00 02023-01-09 00:00:00 3362023-01-16 00:00:00 10082023-01-23 00:00:00 168015 2023-01-02 00:15:00 12023-01-09 00:15:00 3372023-01-16 00:15:00 10092023-01-23 00:15:00 1681
答案1
得分: 1
我认为你想要在每个分组内进行位移。然后你需要另一个 groupby：
(df.groupby(['day_of_week', 'hour', 'minutes'])['value'].rolling(window='14D', min_periods=2).mean() # `min_periods` 不同.groupby(['day_of_week', 'hour', 'minutes']).shift() # 在每个分组内进行位移.to_frame())
输出：
valueday_of_week hour minutes datetime0 0 0 2023-01-02 00:00:00 NaN2023-01-09 00:00:00 NaN2023-01-16 00:00:00 336.02023-01-23 00:00:00 1008.015 2023-01-02 00:15:00 NaN...6 23 30 2023-01-15 23:30:00 NaN2023-01-22 23:30:00 1006.045 2023-01-08 23:45:00 NaN2023-01-15 23:45:00 NaN2023-01-22 23:45:00 1007.0
英文:
I think you want to shift within each group. Then you need another groupby:
(df.groupby(['day_of_week','hour','minutes'])['value'].rolling(window='14D', min_periods=2).mean() # `min_periods` is different.groupby(['day_of_week','hour','minutes']).shift() # shift within each group.to_frame())
Output:
valueday_of_week hour minutes datetime0 0 0 2023-01-02 00:00:00 NaN2023-01-09 00:00:00 NaN2023-01-16 00:00:00 336.02023-01-23 00:00:00 1008.015 2023-01-02 00:15:00 NaN... ...6 23 30 2023-01-15 23:30:00 NaN2023-01-22 23:30:00 1006.045 2023-01-08 23:45:00 NaN2023-01-15 23:45:00 NaN2023-01-22 23:45:00 1007.0
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论