Find average of categorical values in Pandas with groupby of more than two columns

user_9761749 0
Viewed 429

I have a df:

player_id   action  level
    1   miss    level_1
    1   kill    level_1
    1   miss    level_1
    1   miss    level_1
    1   miss    level_1
    1   miss    level_2
    2   miss    level_3
    2   miss    level_3
    2   kill    level_3
    3   miss    level_3

How can I calculate the average kills per user in per level? I have already tried:

d = df.groupby(['level', 'player_id', 'action']).level.count()

which gives me a Pandas series of:

level   player_id  action
level_1    1       kill     1
                   miss     4
level_2    1       miss     1
level_3    2       kill     1
                   miss     2
           3       miss     1

What I just want further is the average number of kills per player per level. The final df should like like this:

level      average_kills
level_1    0.2
level_2    0
level_3    0.1667
pandas-groupby pandas dataframe python-3.x
2 Answers

Try:

df.action.eq('kill').groupby([df.level, df.player_id]).mean().mean(level=0)

level
level_1    0.200000
level_2    0.000000
level_3    0.166667
Name: action, dtype: float64
user_23366540

From the initial DataFrame you can groupby and value_counts then unstack to ensure you have kill and miss for each player who played a level. Then take the mean by level.

res = (df.groupby(['player_id', 'level'])
          ['action'].value_counts(normalize=True)
          .unstack(-1).fillna(0)
          ['kill'].mean(level='level'))

level
level_1    0.200000
level_2    0.000000
level_3    0.166667
Name: kill, dtype: float64
user_43333590
Related