Group index by minute and compute average

问题:

So I have a pandas dataframe called 'df' and I want to remove the seconds and just have the index in YYYY-MM-DD HH:MM format. But also the minutes are then grouped and the average for that minute is displayed.

So I want to turn this dataFrame

                        value
2015-05-03 00:00:00     61.0
2015-05-03 00:00:10     60.0
2015-05-03 00:00:25     60.0
2015-05-03 00:00:30     61.0
2015-05-03 00:00:45     61.0
2015-05-03 00:01:00     61.0
2015-05-03 00:01:10     60.0
2015-05-03 00:01:25     60.0
2015-05-03 00:01:30     61.0
2015-05-03 00:01:45     61.0
2015-05-03 00:02:00     61.0
2015-05-03 00:02:10     60.0
2015-05-03 00:02:25     60.0
2015-05-03 00:02:40     60.0
2015-05-03 00:02:55     60.0
2015-05-03 00:03:00     59.0
2015-05-03 00:03:15     59.0
2015-05-03 00:03:20     59.0
2015-05-03 00:03:35     59.0
2015-05-03 00:03:40     60.0

into this dataFrame

                        value
2015-05-03 00:00        60.6
2015-05-03 00:01        60.6
2015-05-03 00:02        60.2
2015-05-03 00:03        59.2

ive tried code like

df['value'].resample('1Min').mean()

or

df.index.resample('1Min').mean()

but this does not seem to work. Any ideas?

回答1:

You need first convert index to DatetimeIndex:

df.index = pd.DatetimeIndex(df.index)
#another solution
#df.index = pd.to_datetime(df.index)

print (df['value'].resample('1Min').mean())
#another same solution
#print (df.resample('1Min')['value'].mean())
2015-05-03 00:00:00    60.6
2015-05-03 00:01:00    60.6
2015-05-03 00:02:00    60.2
2015-05-03 00:03:00    59.2
Freq: T, Name: value, dtype: float64

Another solution with seting values of seconds in index to 0 by astype:

print (df.groupby([df.index.values.astype('<M8[m]')])['value'].mean())
2015-05-03 00:00:00    60.6
2015-05-03 00:01:00    60.6
2015-05-03 00:02:00    60.2
2015-05-03 00:03:00    59.2
Name: value, dtype: float64