时间戳

首页 > 解决方案 > 对另一列python中的每个类别执行多个groupby

python - 对另一列python中的每个类别执行多个groupby

问题描述

如表 1（数据帧，df）所示，我想根据开始时间（ST）和结束时间（ET）对资源变量 R 进行分组。我使用以下代码 1 来执行任务。

表 1 (df)

RID ST              ET              R   CAT
1   0 days 04:52:00 0 days 05:06:00 1   1
2   0 days 04:54:00 0 days 08:59:00 1   1
3   0 days 04:56:00 0 days 10:16:00 1   1
4   0 days 04:57:00 0 days 09:15:00 1   2
5   0 days 04:57:00 1 days 01:33:00 1   2
6   0 days 05:02:00 0 days 08:53:00 1   2
7   0 days 05:04:00 0 days 19:08:00 1   3
8   0 days 05:04:00 0 days 20:23:00 1   3
9   0 days 05:06:00 0 days 09:24:00 1   3
10  0 days 05:07:00 0 days 08:20:00 1   3

df=pd.DataFrame({'RID': {0: 1, 1: 2, 2: 3, 3: 4, 4: 5, 5: 6, 6: 7, 7: 8, 8: 9, 9: 10},
 'ST': {0: '0 days 04:52:00',
  1: '0 days 04:54:00',
  2: '0 days 04:56:00',
  3: '0 days 04:57:00',
  4: '0 days 04:57:00',
  5: '0 days 05:02:00',
  6: '0 days 05:04:00',
  7: '0 days 05:04:00',
  8: '0 days 05:06:00',
  9: '0 days 05:07:00'},
 'ET': {0: '0 days 05:06:00',
  1: '0 days 08:59:00',
  2: '0 days 10:16:00',
  3: '0 days 09:15:00',
  4: '1 days 01:33:00',
  5: '0 days 08:53:00',
  6: '0 days 19:08:00',
  7: '0 days 20:23:00',
  8: '0 days 09:24:00',
  9: '0 days 08:20:00'},
 'R': {0: 1, 1: 1, 2: 1, 3: 1, 4: 1, 5: 1, 6: 1, 7: 1, 8: 1, 9: 1},
 'CAT': {0: 1, 1: 1, 2: 1, 3: 2, 4: 2, 5: 2, 6: 3, 7: 3, 8: 3, 9: 3}})

代码 1

df1.index=pd.to_timedelta(df['ET'])
df2.index=pd.to_timedelta(df['ST'])
df_final=df1.groupby(pd.Grouper(freq='1Min')).sum()['R'].fillna(0).subtract(df2.groupby(pd.Grouper(freq='1Min')).sum()['R'].fillna(0),fill_value=0).cumsum()
x = pd.DataFrame(df_final.reset_index())

我得到的结果是表 2 中的前两列。但是，无论变量“CAT”中的类别值如何，操作都会发生。如何对 CAT 中的每个值执行上述操作。

表 2

index           R   
0 days 04:52:00 -1  
0 days 04:53:00 -1  
0 days 04:54:00 -2
0 days 04:55:00 -2
0 days 04:56:00 -3
0 days 04:57:00 -5
0 days 04:58:00 -5
0 days 04:59:00 -5
0 days 05:00:00 -5
0 days 05:01:00 -5
0 days 05:02:00 -6
0 days 05:03:00 -6
0 days 05:04:00 -8
0 days 05:05:00 -8
0 days 05:06:00 -8
0 days 05:07:00 -10

标签： pythonpandasgroup-bypandas-groupbycategories

解决方案

您可以尝试以下代码：

df1.index=pd.to_timedelta(df['ET'])
df2.index=pd.to_timedelta(df['ST'])
df_final=df1.groupby(['CAT',pd.Grouper(freq='1Min')]).sum()['R'].fillna(0).subtract(df2.groupby(['CAT',pd.Grouper(freq='1Min')]).sum()['R'].fillna(0),fill_value=0).cumsum()
x = pd.DataFrame(df_final.reset_index())

将CAT列添加到 groupby，它应该可以解决您的问题。

推荐阅读