如何找出每个城市中哪个canidate[男性或女性]得分最高？[重复]

Question

我有这个数据框架，我想知道谁在每个城市获得了最高分。

import pandas as pd 

df = pd.DataFrame({"City":["Delhi","Delhi","Mumbai","Mumbai","Lahore","Lahore"],
"Points":[90.1,90.3,94.1,95,89,90.5],
"Gender":["Male","Female","Female","Male","Female","Male"]})

到目前为止，我已经尝试过了，但这给了我每个城市的男性和女性的顶尖人物，

df.groupby(by=["City","Gender"],sort=False)["Points"].max()

我想要一个在每个城市得分最高的候选人[男性或女性]。

Answer 1

这个只会给你一个最高分的表格

import pandas as pd 

df = pd.DataFrame({
    "City":["Delhi","Delhi","Mumbai","Mumbai","Lahore","Lahore"],
    "Points":[90.1,90.3,94.1,95,89,90.5],
    "Gender":["Male","Female","Female","Male","Female","Male"]
})

df.groupby('City').apply(lambda x: x.sort_values('Points').tail(1)).reset_index(drop=True)

输出:

     City  Points  Gender
0   Delhi    90.3  Female
1  Lahore    90.5    Male
2  Mumbai    95.0    Male

Answer 2

尝试使用仅“City”作为索引的数据透视表：

df.pivot_table(values='Points',index=['City'],aggfunc='max')

#输出

        Points
City          
Delhi     90.3
Lahore    90.5
Mumbai    95.0

如果你想使用groupby，请随意使用，但请记住，这里只使用“城市”，性别并不重要

df.groupby(by=["City"],sort=False)["Points"].max()
#Output
City
Delhi     90.3
Mumbai    95.0
Lahore    90.5
Name: Points, dtype: float64

如果你也想看性别，用这个:

df.groupby('City').apply(lambda x: x.sort_values('Points').iloc[-1].reset_index(drop=True))

输出

    0   1   2
City            
Delhi   Delhi   90.3    Female
Lahore  Lahore  90.5    Male
Mumbai  Mumbai  95.0    Male