Using df.apply to return the count of rows where a condition is met

user_14605345 0
Viewed 102

I have an dataframe as given:

Initial Dataframe

Now as seen from the dataframe the player x lost 6 times and the player y lost 9 times. I want to make a dataframe which enlists the players and how many times they have lost. Hence the final dataframe should look like this

Final Dataframe

One option which I found was to use dataframe.apply wherein I could return the no of rows which maintained the condition. The code for the same is:

import pandas as pd

df=pd.read_csv('C:/Users/sadik/Desktop/Data.csv')

df = df.apply(lambda x : True
            if x['Result'] == "Lost" else False, axis = 1)
num_rows = len(df[df == True].index)
print('Number of Rows in dataframe in which Condition is met: ',
      num_rows )

This gives me the ouput

runfile('C:/Users/sadik/untitled0.py', wdir='C:/Users/sadik')
Number of Rows in dataframe in which Condition is met:  15

My question is how do I use the same logic to output the count by the name of the players as shown in the expected ouput

pandas dataframe python
2 Answers

You can just use groupby:

import pandas as pd
df = pd.DataFrame({"name": ['x'] * 6 + ['y'] * 9, 'result': ['LOST'] * 15})


df.groupby('name').count()
>>>
    result
name    
x   6
y   9

In case you want to use apply you can do that too:

df.groupby('name')[['result']].apply(lambda x: x.count())
user_76485780

You can group by the Name and then aggregate the total number of Losts:

out = df.groupby("Name").Result.agg(lambda s: s.eq("Lost").sum()).to_frame("Count")

where we lastly turn it to a dataframe with the counts named "Count",

to get

>>> out

      Count
Name
x         6
y         9
user_93321870
Related