Ranking by median in python

user_4792022 0
Viewed 115

I am newish to pandas. I have a dataframe (df) with the columns, height (numeric) and team (string) I'd like to rank the teams by their median height.

Here is what I have done so far

df = old_df.groupby("team")
a=df["height"].agg([np.median])
a=a.sort_values(by=['median'])
a.reset_index(inplace=True)
b=a["team"].tolist()

this outputs a list of the team names (b), in order of median height, which could be converted into a dictionary and used to create a new col of the data frame. I am not sure how to do that, but my intuition is that there is almost certainly a simpler way of doing this.

Can you see a better way?

pandas dataframe dictionary
1 Answers

Use groupby.agg to group the teams before computing/sorting their medians:

df.groupby('team')['height'].agg('median').sort_values()

Toy example:

df = pd.DataFrame({'team': ['foo']*3 + ['bar']*2, 'height': [1,3,7,20,40]})

#   team  height
# 0  foo       1
# 1  foo       3
# 2  foo       7
# 3  bar      20
# 4  bar      40

ranked = df.groupby('team')['height'].agg('median').sort_values()

# team
# foo     3
# bar    30
# Name: height, dtype: int64

ranked.to_dict()

# {'foo': 3, 'bar': 30}

Update: To compute the ranks explicitly, use Series.rank:

ranked = df.groupby('team')['height'].agg('median').rank(ascending=False)

# team
# bar    1.0
# foo    2.0
# Name: height, dtype: float64

ranked.to_dict()

# {'bar': 1.0, 'foo': 2.0}
user_131383640
Related