python - 如何以 Pandas 两列为条件对频率进行重采样？

Question

我有以下数据框:

import pandas as pd


data_as_dict = {'Date': {0: '2015-01-01 00:00:00', 1: '2015-01-01 00:00:02', 2: '2015-01-01 00:00:02', 3: '2015-01-01 00:00:02', 4: '2015-01-01 00:00:02',
                         5: '2015-01-01 00:00:03', 6: '2015-01-01 00:00:03', 7: '2015-01-01 00:00:04', 8: '2015-01-01 00:00:04', 9: '2015-01-01 00:00:04', 10: '2015-01-01 00:00:05',
                         11: '2015-01-01 00:00:05', 12: '2015-01-01 00:00:05', 13: '2015-01-01 00:00:06', 14: '2015-01-01 00:00:07', 15: '2015-01-01 00:00:07',
                         16: '2015-01-01 00:00:07', 17: '2015-01-01 00:00:08', 18: '2015-01-01 00:00:08', 19: '2015-01-01 00:00:08'}, 'Asset': {0: 'baidu-inc', 1: 'bitcoin', 2: 'bitcoin', 3: 'ftse-100', 4: 'ftse-100', 5: 'baidu-inc', 6: 'bitcoin', 7: 'bitcoin', 8: 'ftse-100', 9: 'baidu-inc', 10: 'baidu-inc', 11: 'ftse-100', 12: 'bitcoin', 13: 'baidu-inc', 14: 'ftse-100', 15: 'bitcoin', 16: 'ftse-100', 17: 'baidu-inc', 18: 'baidu-inc', 19: 'bitcoin'}, 'Class': {0: 'S', 1: 'E', 2: 'E', 3: 'G', 4: 'G', 5: 'S', 6: 'E', 7: 'S', 8: 'G', 9: 'G', 10: 'G', 11: 'S', 12: 'S', 13: 'S', 14: 'S', 15: 'S', 16: 'S', 17: 'E', 18: 'E', 19: 'S'}, 'Score': {0: -0.8674, 1: 0.395, 2: 0.0, 3: -0.3612, 4: 0.5023, 5: 0.0129, 6: -0.5023, 7: 0.0, 8: -0.5023, 9: 0.0, 10: -0.7579, 11: -0.8843, 12: 0.8968, 13: 0.7579, 14: 0.5466, 15: 0.2023, 16: 0.0, 17: 0.6457, 18: 0.0, 19: -0.5023}}

df = pd.DataFrame.from_dict(data_as_dict)
df['Date'] = pd.to_datetime(df['Date'])

                  Date                         Asset Class   Score
0  2015-01-01 00:00:00                     baidu-inc     S -0.8674
1  2015-01-01 00:00:02                       bitcoin     E  0.3950
2  2015-01-01 00:00:02                       bitcoin     E  0.0000
3  2015-01-01 00:00:02                      ftse-100     G -0.3612
4  2015-01-01 00:00:02                      ftse-100     G  0.5023
5  2015-01-01 00:00:03                     baidu-inc     S  0.0129
6  2015-01-01 00:00:03                       bitcoin     E -0.5023
7  2015-01-01 00:00:04                       bitcoin     S  0.0000
8  2015-01-01 00:00:04                      ftse-100     G -0.5023
9  2015-01-01 00:00:04                     baidu-inc     G  0.0000
10 2015-01-01 00:00:05                     baidu-inc     G -0.7579
11 2015-01-01 00:00:05                      ftse-100     S -0.8843
12 2015-01-01 00:00:05                       bitcoin     S  0.8968
13 2015-01-01 00:00:06                     baidu-inc     S  0.7579
14 2015-01-01 00:00:07                      ftse-100     S  0.5466
15 2015-01-01 00:00:07                      bitcoin      S  0.2023
16 2015-01-01 00:00:07                      ftse-100     S  0.0000
17 2015-01-01 00:00:08                     baidu-inc     E  0.6457
18 2015-01-01 00:00:08                     baidu-inc     E  0.0000
19 2015-01-01 00:00:08                       bitcoin     S -0.5023

我想做的是对每个“类”的每个“ Assets ”的频率从 1 秒到 1 小时重新采样。我尝试没有成功的是:

df.groupby(['Asset','Class']).set_index('Date').resample('1h').mean()

更详细地说，我想在每个类别(S、G 和 E)中将 Assets “baidu-inc”的频率从 1s 重新采样到 1h。同样的逻辑适用于每项 Assets 。

谁能帮我做一下？

非常感谢!

Answer 1

交换您的 2 个第一个操作:

df.set_index('Date').groupby(['Asset','Class']).resample('1h').mean().reset_index()

       Asset Class       Date     Score
0  baidu-inc     E 2015-01-01  0.322850
1  baidu-inc     G 2015-01-01 -0.378950
2  baidu-inc     S 2015-01-01 -0.032200
3    bitcoin     E 2015-01-01 -0.035767
4    bitcoin     S 2015-01-01  0.149200
5   ftse-100     G 2015-01-01 -0.120400
6   ftse-100     S 2015-01-01 -0.112567

你甚至不需要使用set_index:

df.groupby(['Asset','Class']).resample('1h', on='Date').mean().reset_index()

       Asset Class       Date     Score
0  baidu-inc     E 2015-01-01  0.322850
1  baidu-inc     G 2015-01-01 -0.378950
2  baidu-inc     S 2015-01-01 -0.032200
3    bitcoin     E 2015-01-01 -0.035767
4    bitcoin     S 2015-01-01  0.149200
5   ftse-100     G 2015-01-01 -0.120400
6   ftse-100     S 2015-01-01 -0.112567