代码之家 › 专栏 › 技术社区 › ManOnTheMoon

groupby/eq计算特定列的平均值

dataframe pandas python

ManOnTheMoon · 技术社区 · 5 年前

我想弄清楚如何使用groupby/eq计算特定列的平均值,我有一个df,如下所示(原始df)。

我想按“组”和“玩家”分组,等级等于1,得到“分数”的平均值。例子:

a组,2名球员 (16+13+19)/3 = 16

+-------+---------+-------+-------+------------+
| Group | players | class | score | score_mean |
+-------+---------+-------+-------+------------+
| a     |       2 |     2 |    14 |            |
| a     |       2 |     1 |    16 |         16 |
| a     |       2 |     1 |    13 |         16 |
| a     |       2 |     2 |    13 |            |
| a     |       2 |     1 |    19 |         16 |
| a     |       2 |     2 |    17 |            |
| a     |       2 |     2 |    14 |            |
+-------+---------+-------+-------+------------+

我试过:

df['score_mean'] = df['class'].eq(1).groupby(['Group', 'players'])['score'].transform('mean')

但我一直收到“钥匙错误”

原始df:

+----+-------+---------+-------+-------+
|    | Group | players | class | score |
+----+-------+---------+-------+-------+
|  0 | a     |       1 |     1 |    10 |
|  1 | c     |       2 |     1 |    20 |
|  2 | a     |       1 |     3 |    29 |
|  3 | c     |       1 |     3 |    22 |
|  4 | a     |       2 |     2 |    14 |
|  5 | b     |       1 |     2 |    16 |
|  6 | a     |       2 |     1 |    16 |
|  7 | b     |       2 |     3 |    17 |
|  8 | c     |       1 |     2 |    22 |
|  9 | b     |       1 |     2 |    23 |
| 10 | c     |       2 |     2 |    22 |
| 11 | d     |       1 |     1 |    13 |
| 12 | a     |       2 |     1 |    13 |
| 13 | d     |       1 |     3 |    23 |
| 14 | a     |       2 |     2 |    13 |
| 15 | d     |       2 |     1 |    34 |
| 16 | b     |       1 |     3 |    32 |
| 17 | c     |       2 |     2 |    29 |
| 18 | b     |       2 |     2 |    28 |
| 19 | a     |       2 |     1 |    19 |
| 20 | a     |       1 |     1 |    19 |
| 21 | c     |       1 |     1 |    27 |
| 22 | b     |       1 |     3 |    47 |
| 23 | a     |       2 |     2 |    17 |
| 24 | c     |       1 |     1 |    14 |
| 25 | c     |       2 |     2 |    25 |
| 26 | a     |       1 |     3 |    67 |
| 27 | b     |       2 |     3 |    21 |
| 28 | a     |       1 |     3 |    27 |
| 29 | c     |       1 |     1 |    16 |
| 30 | a     |       2 |     2 |    14 |
| 31 | b     |       1 |     2 |    25 |
+----+-------+---------+-------+-------+

data = {'Group':['a','c','a','c','a','b','a','b','c','b','c','d','a','d','a','d',
               'b','c','b','a','a','c','b','a','c','c','a','b','a','c','a','b'],
      'players':[1,2,1,1,2,1,2,2,1,1,2,1,2,1,2,2,1,2,2,2,1,1,1,2,1,2,1,2,1,1,2,1],
                'class':[1,1,3,3,2,2,1,3,2,2,2,1,1,3,2,1,3,2,2,1,1,1,3,2,1,2,3,3,3,1,2,2],
      'score':[10,20,29,22,14,16,16,17,22,23,22,13,13,23,13,34,32,29,28,19,19,27,47,17,14,25,67,21,27,16,14,25]}

df = pd.DataFrame(data)

善意的建议

非常感谢&当做

2 回复 | 直到 5 年前

Anurag Dabas 5 年前

尝试:

通过 set_index() , groupby() , assign() 和 reset_index() 方法:

df=(df.set_index(['Group','players'])
   .assign(score_mean=df[df['class'].eq(1)].groupby(['Group', 'players'])['score'].mean())
   .reset_index())

更新:

如果你想要第一个 df 作为输出,然后使用:

grouped=df.groupby(['Group', 'players','class']).transform('mean')
grouped=grouped.assign(players=df['players'],Group=df['Group'],Class=df['class']).where(df['Group']=='a').dropna()
grouped['score']=grouped.apply(lambda x:float('NaN') if x['players']==1 else x['score'],1)
grouped=grouped.dropna(subset=['score'])

现在如果你打印 grouped 你会得到你想要的输出

wwnde 5 年前

如果我没弄错的话,只需要在class=1的地方返回值。不确定这将提供什么,但代码如下。使用groupby transform和chain where

df['score_mean']=df.groupby(['Group','players'])['score'].transform('mean').where(df['class']==1).fillna('')

   Group  players  class  score score_mean
0      a        1      1     10         10
1      a        2      1     20         20
2      a        3      5     29           
3      a        4      5     22           
4      a        5      5     14           
5      b        1      7     16           
6      b        2      7     16           
7      b        3      7     17           
8      c        1      4     22           
9      c        2      2     23           
10     c        3      2     22           
11     d        1      4     13           
12     d        2      4     13           
13     d        3      3     23           
14     d        4      8     13           
15     d        5      7     34           
16     e        1      7     32           
17     e        2      2     29           
18     e        3      2     28           
19     e        4      1     19         19
20     e        5      1     19         19
21     e        6      1     27         27
22     f        1      5     47           
23     f        2      5     17           
24     f        3      7     14           
25     f        4      7     25           
26     g        1      3     67           
27     g        2      3     21           
28     g        3      3     27           
29     g        4      8     16           
30     g        5      8     14           
31     g        6      8     25

Allen Qin 5 年前

你可以先按类过滤,然后通过分组和转换创建分数。

(
    df[df['class']==1]
    .assign(score_mean = lambda x: x.groupby(['Group', 'players']).score.transform('mean'))
)

    Group   players class   score   score_mean
0   a       1       1       10      14.5
1   c       2       1       20      20.0
6   a       2       1       16      16.0
11  d       1       1       13      13.0
12  a       2       1       13      16.0
15  d       2       1       34      34.0
19  a       2       1       19      16.0
20  a       1       1       19      14.5
21  c       1       1       27      19.0
24  c       1       1       14      19.0
29  c       1       1       16      19.0

如果要保留其他类并将平均值设置为“”,可以执行以下操作:

(
    df[df['class']==1]
    .groupby(['Group', 'players']).score.transform('mean')
    .pipe(lambda x: df.assign(score_mean = x))
    .fillna('')
)