How to resample frequency conditional on two columns in pandas?-CodePudding

I have the following dataframe:

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

What I would like to do is to resample the frequency of each 'Asset' for each 'Class' from 1 second to 1 hour. What I tried without success is:

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

More in detail, I want to resample the frequency of the asset 'baidu-inc' from 1s to 1h within each class (S, G, and E). The same logic applies to every asset.

Can anyone help me do it?

Thanks a lot!

CodePudding user response：

Swap your 2 first operations:

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

You don't even need to use 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

CodePudding user response：

Rounding and pivot table is a solution:

df["Date"] = df["Date"].dt.round("1H")
cross = df.pivot_table(index="Date", columns=["Asset", "Class"], values="Score", aggfunc="mean")

It returns:

Asset      baidu-inc                    bitcoin         ftse-100          
Class              E        G       S         E       S        G         S
Date                                                                      
2015-01-01   0.32285 -0.37895 -0.0322 -0.035767  0.1492  -0.1204 -0.112567

If you wish to keep your original format, just melt it:

agg = cross.melt(ignore_index=False).reset_index()

It returns:

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

If you wish to keep your format, another solution is:

df["Date"] = df["Date"].dt.round("1H")
agg = df.groupby(["Date", "Asset", "Class"])["Score"].mean().reset_index()

In this case, it is more straight forward.

CodePudding user response：

I'm not able to reproduce your dataframe because 'Timestamp' is not defined. Should 'Timestamp' be 'pd.to_datetime' perhaps?

Without being able to reproduce your dataframe I'm not able to test this, but you may need to use pd.Grouper.

Check it out here: https://pandas.pydata.org/docs/reference/api/pandas.Grouper.html