如何将Pandas数据帧重采样为每小时平均值，同时考虑时间和具有字符串值的列？

Question

我试图在python中对数据帧进行每小时平均值计算，方法是将日期信息和特定列中的字符串信息考虑在内。请参见下面的示例

  station                time temperature
0    EHAM 2020-01-01 13:30:00           2
1    EHAM 2020-01-01 13:50:00           5
2    EHAM 2020-01-02 13:30:00           7
3    EHAM 2020-01-02 13:50:00           1
4    EBBR 2020-01-01 13:30:00           6
5    EBBR 2020-01-01 13:55:00           1
6    EBBR 2020-01-02 14:30:00           2
7    EBBR 2020-01-02 14:40:00           3

从这个示例中，理想情况下，我希望获得以下新数据帧：

  station                time temperature
0    EHAM 2020-01-01 13:00:00         3.5
1    EHAM 2020-01-02 13:00:00           4
2    EBBR 2020-01-01 13:00:00         3.5
3    EBBR 2020-01-02 14:00:00         2.5

此数据帧的代码为：

import pandas as pd
from datetime import datetime

flights = {'station': ['EHAM','EHAM','EHAM','EHAM','EBBR','EBBR','EBBR','EBBR'],
        'time': [datetime.strptime('1/1/2020 1:30 PM', '%d/%m/%Y %I:%M %p'),datetime.strptime('1/1/2020 1:50 PM', '%d/%m/%Y %I:%M %p'),
                 datetime.strptime('2/1/2020 1:30 PM', '%d/%m/%Y %I:%M %p'),datetime.strptime('2/1/2020 1:50 PM', '%d/%m/%Y %I:%M %p'),
                 datetime.strptime('1/1/2020 1:30 PM', '%d/%m/%Y %I:%M %p'),datetime.strptime('1/1/2020 1:55 PM', '%d/%m/%Y %I:%M %p'),
                 datetime.strptime('2/1/2020 2:30 PM', '%d/%m/%Y %I:%M %p'),datetime.strptime('2/1/2020 2:40 PM', '%d/%m/%Y %I:%M %p')],
        'temperature': ['2', '5','7','1','6','1','2','3']}

df = pd.DataFrame(flights, columns = ['station', 'time','temperature'])

任何帮助都将不胜感激

Answer 1

通过^{}将datetimes转换为日期，聚合mean：

#convert sampel data to numeric
df['temperature'] = df['temperature'].astype(int)

df1 = (df.groupby(['station', df['time'].dt.date], sort=False)['temperature']
         .mean()
         .reset_index())
print (df1)
  station        time  temperature
0    EHAM  2020-01-01          3.5
1    EHAM  2020-01-02          4.0
2    EBBR  2020-01-01          3.5
3    EBBR  2020-01-02          2.5

含有Grouper的溶液：

df1 = (df.groupby(['station', pd.Grouper(key='time', freq='D')], sort=False)['temperature']
         .mean()
         .reset_index())

print (df1)
  station       time  temperature
0    EHAM 2020-01-01          3.5
1    EHAM 2020-01-02          4.0
2    EBBR 2020-01-01          3.5
3    EBBR 2020-01-02          2.5