How to group data in a column based on a column value and analyze the max or min on that?

user_8753752 0
Viewed 41

I have columns NAME,ID, SEX, AGE, GAMES, TEAM, SPORT, YEAR, where each column doesnt have any null value. Now I want to find Which team has brought the most number of female and male players. I have written this code - rf.groupby('Team')['Sex'].value_counts() and this outputs to - enter image description here

now here inturn I want to find the team which has maximum num of male and female players individually.. can anyone help me with this. "Which team has brought the most number of female and male players." - this is the question. can you let me know if I want to change the first statement groupby only that I have mentioned above ?

pandas python
1 Answers

Use Series.unstack with DataFrame.idxmax:

rf.groupby('Team')['Sex'].value_counts().unstack().idxmax()

print (rf)
    Team Sex
0  team1   M
1  team1   M
2  team1   M
3  team1   F
4  team2   M
5  team3   F
6  team3   F
7  team2   M
8  team1   M

Another idea is use crosstab with idxmax:

df = pd.crosstab(rf['Team'], rf['Sex'])
print (df)
Sex    F  M
Team       
team1  1  4
team2  0  2
team3  2  0

s = pd.crosstab(rf['Team'], rf['Sex']).idxmax()
print (s)
Sex
F    team3
M    team1
dtype: object

For scalars:

print (s['F'])
print (s['M'])

If need both info - max and team by max use DataFrame.agg:

df1 = pd.crosstab(rf['Team'], rf['Sex']).agg(['max','idxmax'])
print (df1)
            F      M
max         2      4
idxmax  team3  team1

If need scalars:

print (df1.loc['idxmax','F'])
print (df1.loc['idxmax','M'])

print (df1.loc['max','F'])
print (df1.loc['max','M'])
user_29010020
Related