Pandas：如何按开始和结束时间汇总每小时计数

Question

我有一个数据框，其中包含每个唯一评分ID的开始和结束时间。

d={'ID':['01','02','03','04','05','06'],'Hour Start':[5,9,13,15,20,23],'Hour End':[6,9,15,19,0,2]}
df=pd.DataFrame(data=d)

我的目标是聚合整个数据集中每小时有多少活跃的评分。例如，ID:01在早上5点到6点期间开始。那么早上5点和6点都应该增加1次计数。

但对于ID:06，评级从晚上11点开始，第二天凌晨2点结束。因此，每小时应该从晚上11点到凌晨2点都添加1次计数。

我想输出一个按小时汇总的表格，如下所示。

我已经思考了一段时间的解决方案。

任何帮助都将非常感激！谢谢！

- C4TNT

---

好问题，但您需要澄清一些假设。例如：1.这两列是否保证相对于它们所代表的绝对时间是单调的？2.当某一列中的数字低于前一个数字时，是否保证这意味着时间恰好晚了一天？ - timgeb

@timgeb 谢谢您的回复。1. 是的，两列都是。2. 如果结束时间早于开始时间，则表示跨越到下一天。 - C4TNT

@C4TNT，我看到你编辑了这篇帖子，计算小时汇总表的逻辑是什么？ - Shubham Sharma

@ShubhamSharma 很抱歉我没有表达清楚。让我试着用类比来解释。每小时我有很多老虎机在运行，我想知道每小时有多少台老虎机在运行。但是我只有每台老虎机的开始时间和结束时间。 - C4TNT

Answer 1

您可以将开始时间和结束时间列都转换为日期时间格式。然后计算时间差。最后，将时间差转换为小时差（将秒数除以3600）：

df['Hours_s'] = pd.to_datetime(df['Hour Start'], format='%H' )
df['Hours_e'] = pd.to_datetime(df['Hour End'], format='%H' )
df['delta'] = df['Hours_e']-df['Hours_s']
df["count"] = df["delta"].apply(lambda x: x.seconds//3600)

输出：

ID   Hour_Start Hour_End count
0          5       6       1
1          9       9       0
2          13      15      2
3          15      19      4
4          20      0       4
5          23      2       3

更新：

final_tab = pd.DataFrame({"Hour": range(0,24), "Count": [0]*24})

for i, row in df.iterrows():
    if row["delta"].days != 0:
        final_tab.iloc[row["Hour Start"]:24,1] =final_tab.iloc[row["Hour Start"]:24,1] +1
        final_tab.iloc[0:row["Hour End"]+1,1] =final_tab.iloc[0:row["Hour End"]+1,1] +1
    else:
        final_tab.iloc[row["Hour Start"]:row["Hour Start"]+row["count"],1] = final_tab.iloc[row["Hour Start"]:row["Hour Start"]+row["count"],1] + 1

输出：

print(final_tab)
   Hour Count
0   0   2
1   1   1
2   2   1
3   3   0
4   4   0
5   5   1
6   6   1
7   7   0
8   8   0
9   9   1
10  10  0
11  11  0
12  12  0
13  13  1
14  14  1
15  15  2
16  16  1
17  17  1
18  18  1
19  19  1
20  20  1
21  21  1
22  22  1
23  23  2

- DavideBrex

Answer 2

如果我理解正确的话，您可以使用pd.to_datetime和pd.date_range来完成这个操作：

#Convert hours to datetime
df['endTime'] = pd.to_datetime(df['Hour End'], format='%H')
df['startTime'] = pd.to_datetime(df['Hour Start'], format='%H')

#If 'Hour End' less thn 'Hour Start' assume next day
df['endTime'] = np.where(df['Hour End'] < df['Hour Start'], 
                         df['endTime']+pd.Timedelta(days=1), 
                         df['endTime'])

#Create a series of hours per defined ranges ('Hour Start' to 'Hour End')
df_hourly = df.apply(lambda x: pd.Series(pd.date_range(x['startTime'], 
                                                       x['endTime'], 
                                                       freq='H')), 
                                         axis=1)\
              .stack().dt.hour

#Use value counts to count the hours and reindex to 24-hour day to fill missing hours.
df_hourly.value_counts().reindex(np.arange(0,24)).fillna(0).astype(int)

输出：

0     2
1     1
2     1
3     0
4     0
5     1
6     1
7     0
8     0
9     1
10    0
11    0
12    0
13    1
14    1
15    2
16    1
17    1
18    1
19    1
20    1
21    1
22    1
23    2

另外一种方法是使用explode和value_counts函数：

df.apply(lambda x: pd.date_range(x['startTime'], 
                                 x['endTime'], 
                                 freq='H'), axis=1)\
  .explode().dt.hour.value_counts()\
  .reindex(np.arange(0,24), fill_value=0)

- Scott Boston

---

非常感谢！这就是我想要输出的内容！ - C4TNT