In a pandas dataframe, I'd like to separate each month in a time series into separate dataframe-CodePudding

I have a DataFrame of several years of hourly data:

Timestamp                      Spd              Pres                              
2013-06-13 00:00:00            9.630            650.4
2013-06-13 01:00:00            9.309            650.4
2013-06-13 02:00:00            8.421            650.4

I'd like to split this data into months then find the average of each month to make an average representative year. My timestamps are my index.

Is there a good way to do this without iterating the number of hours each month into its own DataFrame with a loop?

Solution

import pandas as pd
from datetime import datetime

df['Month'] = df.index
df['Year'] = df.index
df['Month'] = df['Month'].apply(lambda x: x.month)
df['Year'] = df['Year'].apply(lambda x: x.year)

print(df.groupby(['Month', 'Year']).mean()

CodePudding user response：

Try:

import pandas as pd
from datetime import datetime

data = {'Timestamp': [datetime(2013, 6, 13, 0, 0, 0), datetime(2013, 6, 13, 1, 0, 0), datetime(2013, 6, 13, 2, 0, 0)], 'Spd': [9.630 , 9.309,8.421], 'Pres': [650.4, 650.4, 650.4]}
df = pd.DataFrame(data).set_index('Timestamp')
df['Month'] = df.index
df['Month'] = df['Month'].apply(lambda x: x.month)

print(df.groupby('Month').mean())