Dataframe group and sort

Question

I have a dataframe that roughly looks like this:

I could group and sort them with df_players.groupby(['vehicle']).agg({'frags':sum}).sort_values(['frags'],ascending=False)

How can I group the vehicles and get their win rates just like this:

EDIT: (data)

[
    {"player": "bob", "vehicle": "a", "hasWon": True, "frags": 5},
    {"player": "foo", "vehicle": "b", "hasWon": False, "frags": 3},
    {"player": "bar", "vehicle": "c", "hasWon": True, "frags": 2}
]

kindly post data, not pics – sammywemmy May 14 '20 at 07:28 — sammywemmy, May 14 '20 at 07:28

score 1 · Accepted Answer · answered May 14 '20 at 07:34

1

I think you need aggregate mean of hasWon column, because Trues are processing like 1, then multiple by 100, round and last convert to column:

df_players = pd.DataFrame({'vehicle': list('aabbccc'), 
                           'hasWon':[True, False, True, True, False, True, False], 
                           'frags':[5,2,3,6,5,4,5]})
print (df_players)
  vehicle  hasWon  frags
0       a    True      5
1       a   False      2
2       b    True      3
3       b    True      6
4       c   False      5
5       c    True      4
6       c   False      5

df = (df_players.groupby(['vehicle'])['hasWon']
                 .mean()
                 .mul(100)
                 .round(2)
                 .reset_index(name='winrate'))
print (df)
  vehicle  winrate
0       a    50.00
1       b   100.00
2       c    33.33

answered May 14 '20 at 07:34

jezrael

822,522
95
1,334
1,252

How would I ignore vehicles that has only `n` amount of entries? – conquistador May 14 '20 at 07:50
@conquistador - so in data sample if `n=3` are removed vehicles of `c`, because 3 times? and if some another group e.g. `d` has 4 values, not removed? – jezrael May 14 '20 at 07:52
Yes. How would I filter it? – conquistador May 14 '20 at 07:56
2

@conquistador So before my solution with `mean` use `df_players = df_players[df_players['vehicle'].map( df_players['vehicle'].value_counts()).ne(3)]` – jezrael May 14 '20 at 07:59
1

@conquistador - Solution from [this](https://stackoverflow.com/q/29836836) – jezrael May 14 '20 at 08:00

score 1 · Answer 2 · answered May 14 '20 at 07:42

1

I would just assign a new column to just use a mean:

df.assign(winrate=np.where(df['hasWon'], 100, 0)).groupby('vehicle').agg({'winrate': 'mean'})

answered May 14 '20 at 07:42

Serge Ballesta

143,923
11
122
252

CypherX · Answer 3 · 2020-05-14T08:10:11.913

Solution

# Assuming 
#     winrate = sum(hasWon*frags)/sum(frags)
# df['winrate'] = df['hasWon']*df['frags']/df['frags'].sum()

# Calculate Aggregate Result
result = (df.groupby(['vehicle'])
            .agg({
                'frags': sum, 
                'winrate': sum
            })

Dummy Data

import numpy as np
import pandas as pd

df = pd.DataFrame({'player': ['bob', 'foo', 'bar', 'foo2', 'bar2'], 
                   'vehicle': list('abcab'), 
                   'hasWon': [True, False, True, True, True], 
                   'frags': [5, 3, 2, 4, 2]})
# Assuming 
#     winrate = sum(hasWon*frags)/sum(frags)
df['winrate'] = df['hasWon']*df['frags']/df['frags'].sum()

df

Dataframe group and sort

3 Answers3

Solution

Dummy Data