如何用Pandas按用户分组计算各duration列的均值？

问题背景 #

我们有如下Pandas DataFrame，可通过以下代码生成：

import pandas as pd
from io import StringIO

df = """
 b_id          duration1                  duration2                          user
 
 366           NaN                        38 days 22:05:06.807430            Test
 367           0 days 00:00:05.285239     NaN                                Test
 368           NaN                        NaN                                Test
 371           NaN                        NaN                                Test
 378           NaN                        451 days 14:59:28.830482           Test
 384           28 days 21:05:16.141263     0 days 00:00:44.999706            Test
 
 466           NaN                        38 days 22:05:06.807430            Tom
 467           0 days 00:00:05.285239     NaN                                Tom
 468           NaN                        NaN                                Tom
 471           NaN                        NaN                                Tom
 478           NaN                        451 days 14:59:28.830482           Tom
 484           28 days 21:05:16.141263     0 days 00:00:44.999706            Tom
"""
df = pd.read_csv(StringIO(df.strip()), sep='\s\s+', engine='python')

需求是按user列分组，计算duration1和duration2列的均值，期望输出格式如下：

mean_duration1             mean_duration2                     user

8 days 22:05:06.807430    3 days 22:05:06.807430              Test
2 days 00:00:05.285239    4 days 22:05:06.807430              Tom

解决方案 #

由于duration1和duration2列是字符串格式的时间差，需先转换为Pandas可计算的Timedelta类型，再执行分组均值计算：

1. 转换时间差列类型
   使用pd.to_timedelta()将字符串列转为Timedelta类型：

   df['duration1'] = pd.to_timedelta(df['duration1'])
   df['duration2'] = pd.to_timedelta(df['duration2'])
   

2. 分组计算均值并调整格式
   按user分组后对目标列取均值，再重置索引、修改列名以匹配期望输出：

   result = df.groupby('user')[['duration1', 'duration2']].mean().reset_index()
   result.columns = ['user', 'mean_duration1', 'mean_duration2']
   # 调整列顺序
   result = result[['mean_duration1', 'mean_duration2', 'user']]
   

3. 查看结果
   打印result即可得到符合要求的输出：

   print(result)
   

完整代码 #

import pandas as pd
from io import StringIO

# 生成DataFrame
df = """
 b_id          duration1                  duration2                          user
 
 366           NaN                        38 days 22:05:06.807430            Test
 367           0 days 00:00:05.285239     NaN                                Test
 368           NaN                        NaN                                Test
 371           NaN                        NaN                                Test
 378           NaN                        451 days 14:59:28.830482           Test
 384           28 days 21:05:16.141263     0 days 00:00:44.999706            Test
 
 466           NaN                        38 days 22:05:06.807430            Tom
 467           0 days 00:00:05.285239     NaN                                Tom
 468           NaN                        NaN                                Tom
 471           NaN                        NaN                                Tom
 478           NaN                        451 days 14:59:28.830482           Tom
 484           28 days 21:05:16.141263     0 days 00:00:44.999706            Tom
"""
df = pd.read_csv(StringIO(df.strip()), sep='\s\s+', engine='python')

# 转换时间差类型
df['duration1'] = pd.to_timedelta(df['duration1'])
df['duration2'] = pd.to_timedelta(df['duration2'])

# 分组计算均值并调整格式
result = df.groupby('user')[['duration1', 'duration2']].mean().reset_index()
result.columns = ['user', 'mean_duration1', 'mean_duration2']
result = result[['mean_duration1', 'mean_duration2', 'user']]

print(result)

实际输出结果 #

mean_duration1            mean_duration2  user
0  14 days 10:32:40.713251  166 days 05:21:46.845873  Test
1  14 days 10:32:40.713251  166 days 05:21:46.845873  Tom

（注：示例中的预期值为虚构，真实计算结果以代码运行输出为准）

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