0

I have a dataframe like this:

color    power
red      6
red      8
green    3
red      1
yellow   10
green    5

What I want is this:

color   mean_of_power
yellow  10
red     5
green   4

I have tried df.groupby("color")["power"].mean(), but this will give me a dataframe sorted alphabetically:

color   mean_of_power
green   4
red     5
yellow  10

How can I group a dataframe by one column (color), calculate the mean of another column (power) per group and sort the output by the value of that mean?

C4X
  • 87
  • 8

2 Answers2

0

You can add sort_values right after that:

df.groupby("color").power.mean().sort_values(ascending=False)

Or to create an additional column with count which we sort by:

(df.groupby("color").power
   .agg(["mean", "count"])
   .rename(columns="{}_of_power".format)
   .sort_values("count_of_power", ascending=False))

# output: 
        mean_of_power  count_of_power
color                                
red               5.0               3
green             4.0               2
yellow           10.0               1
mcsoini
  • 6,280
  • 2
  • 15
  • 38
  • Thank you, this works. Do you know how I would sort by something else? Like by the count of colors, so that I get them in the order `red`, `green`, `yellow`? – C4X Mar 26 '22 at 19:33
  • @C4X See update answer. Should be fairly self-explanatory. Remove steps from the back to see what each one does. – mcsoini Mar 26 '22 at 20:14
0
df.groupby("color")["power"].mean().sort_values('mean_of_power')
Thiago
  • 55
  • 6
  • As it’s currently written, your answer is unclear. Please [edit] to add additional details that will help others understand how this addresses the question asked. You can find more information on how to write good answers [in the help center](/help/how-to-answer). – Community Mar 26 '22 at 19:24
  • This doesn't work since I don't have a column called `mean_of_power`. – C4X Mar 26 '22 at 19:31