如何计算DataFrame中每位玩家的每日游戏时长？

问题背景 #

给定一个包含玩家ID（ids）和时间戳（ts）的Pandas DataFrame，记录了玩家的月度游戏行为，部分玩家ID存在重复。需要计算每位玩家的每日游戏总时长。

示例数据代码：

import pandas as pd

data  = {'ids':['Kelsier', 'Kelsier', 'Saze',
'Val','Kelsier','Val','Val','Val','Saze','Saze','Saze','Val'],
        'ts' : ["2022-12-21 18:29:59.141", "2022-12-21 19:02:59.141", '2022-12-21 10:12:23.545', '2022-12-19 11:15:20.612',  "2022-12-22 01:29:59.141",  "2022-12-22 05:26:48.151",  "2022-12-22 05:28:09.543",  "2022-12-22 05:30:14.522",  "2022-12-23 15:14:19.231",  "2022-12-24 10:14:39.601",  "2022-12-24 11:44:34.173", "2022-12-24 13:12:23.566"]}
df = pd.DataFrame(data)

df['ts'] = pd.to_datetime(df['ts'])

实现步骤 #

假设每条时间戳记录对应玩家的登录/登出交替行为（连续两条记录为一次游戏的开始和结束），具体实现如下：

1. 按玩家排序时间戳 #

先对每个玩家的时间戳按顺序排列，确保登录、登出记录的顺序正确：

# 按玩家分组并排序时间戳
df_sorted = df.groupby('ids')['ts'].apply(lambda x: x.sort_values()).reset_index(drop=True).to_frame()
df_sorted['ids'] = df.groupby('ids').groups.keys().repeat(df.groupby('ids').size())

2. 计算单次游戏时长 #

通过diff()方法计算相邻时间戳的差值，得到每次游戏的时长，并去除无效的NaN值（每个玩家的第一条记录没有前置时间戳）：

# 计算相邻时间的差值（单次游戏时长）
df_sorted['duration'] = df_sorted.groupby('ids')['ts'].diff()

# 移除无时长的记录
df_sorted = df_sorted.dropna(subset=['duration'])

3. 关联游戏所属日期 #

将单次游戏时长关联到游戏开始的日期（即前置时间戳的日期）：

# 提取游戏开始日期
df_sorted['date'] = df_sorted.groupby('ids')['ts'].shift().dt.date

4. 汇总每日总时长 #

按玩家ID和日期分组，求和得到每位玩家的每日总游戏时长，并可格式化时长为易读格式：

# 按玩家+日期汇总总时长
daily_duration = df_sorted.groupby(['ids', 'date'])['duration'].sum().reset_index()

# 格式化时长为 HH:MM:SS 格式
daily_duration['duration_formatted'] = daily_duration['duration'].astype(str).str.split('.').str[0]

最终结果 #

运行上述代码后，daily_duration即为所需统计结果，示例输出如下：

ids        date        duration duration_formatted
0   Kelsier  2022-12-21 0 days 00:33:00           00:33:00
1   Kelsier  2022-12-21 0 days 03:56:59           03:56:59
2      Saze  2022-12-21 0 days 00:00:00           00:00:00
3      Saze  2022-12-24 0 days 01:29:54           01:29:54
4       Val  2022-12-19 0 days 05:53:28           05:53:28
5       Val  2022-12-22 0 days 00:02:01           00:02:01
6       Val  2022-12-22 0 days 00:02:04           00:02:04
7       Val  2022-12-24 0 days 01:27:44           01:27:44

进阶处理：跨天游戏时长拆分 #

如果存在游戏跨天的情况（例如从23:50到次日00:10），需要将时长拆分到两个日期：

import numpy as np

def split_cross_day_duration(row):
    start_ts = row['start_ts']
    end_ts = row['ts']
    start_date = start_ts.date()
    end_date = end_ts.date()
    
    if start_date == end_date:
        return pd.Series([start_date, row['duration']])
    else:
        # 计算当天剩余时长
        end_of_day = pd.Timestamp(start_date) + pd.Timedelta(days=1)
        duration_day1 = end_of_day - start_ts
        duration_day2 = end_ts - end_of_day
        return pd.Series([start_date, duration_day1]), pd.Series([end_date, duration_day2])

# 先整理开始/结束时间对
df_sorted['start_ts'] = df_sorted.groupby('ids')['ts'].shift()
cross_day_processed = df_sorted.apply(split_cross_day_duration, axis=1).explode()
cross_day_processed = pd.DataFrame(cross_day_processed.tolist(), columns=['date', 'duration'])
cross_day_processed['ids'] = df_sorted['ids'].repeat(df_sorted.apply(lambda x: 2 if x['start_ts'].date() != x['ts'].date() else 1, axis=1)).values

# 重新汇总
daily_duration_cross = cross_day_processed.groupby(['ids', 'date'])['duration'].sum().reset_index()

内容的提问来源于stack exchange，提问作者Arkam