Python Pandas按小时和计数行分组日期时间 [英] Python Pandas group datetimes by hour and count row

问题描述

这是我的交易数据框，其中每一行表示一笔交易:

This is my transaction dataframe, where each row mean a transaction :

date               station
30/10/2017 15:20    A
30/10/2017 15:45    A
31/10/2017 07:10    A
31/10/2017 07:25    B
31/10/2017 07:55    B

我需要将start_date分组为一个小时间隔，并对每个城市进行计数，因此最终结果将是:

I need to group the start_date to a hour interval and count each city, so the end result will be:

date        hour      station   count
30/10/2017  16:00        A       2
31/10/2017  08:00        A       1
31/10/2017  08:00        B       2

第一行表示2017年10月30日从15:00到16:00，A站有2笔交易

Where the first row means from 15:00 to 16:00 on 30/10/2017, there are 2 transactions in station A

如何在熊猫中做到这一点?

How to do this in Pandas?

我尝试了这段代码，但是结果是错误的:

I tried this code, but the result is wrong :

df_start_tmp = df_trip[['Start Date', 'Start Station']]

times = pd.DatetimeIndex(df_start_tmp['Start Date'])

df_start = df_start_tmp.groupby([times.hour, df_start_tmp['Start Station']]).count()

非常感谢您的帮助

推荐答案

IIUC size + pd.Grouper

df.date=pd.to_datetime(df.date)
df.groupby([pd.Grouper(key='date',freq='H'),df.station]).size().reset_index(name='count')
Out[235]: 
                 date station  count
0 2017-10-30 15:00:00       A      2
1 2017-10-31 07:00:00       A      1
2 2017-10-31 07:00:00       B      2

这篇关于Python Pandas按小时和计数行分组日期时间的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！