Sort Pandas Dataframe per column I have a dataset with age + another 14 variables. I have created 13 bins representing different age groups like so:
data[&quot;age_bins&quot;] = pd.cut(data[&quot;age&quot;], [16,20,25,30,35,40,45,50,55,60,65,70,75,80])

Then calculated the mean value of those 14 other variables per age group like so
data_age_bins_means = data.groupby([&quot;age_bins&quot;]).mean()

resulting in a 14 by 13 DataFrame called data_age_bins_means
Finally, I want to output a data structure with the 5 variables with the greatest mean value in descending order per each age group i.e. first sort each age group column separately and then choose those five variables with the greatest mean values for each age group. I was thinking about a MultiIndex solution but would badly need some help on a neat solution here. Many thanks!
P.s. I finally want to save that data structure to .json for easy loading to JavaScript
I think first converting the data from a wide to long format should allow you to sort the variables and get the result you want. This would look something like this:
import pandas as pd

# create example df
df = pd.DataFrame({
    &quot;age_bin&quot;: [&quot;0-10&quot;, &quot;10-20&quot;, &quot;20-30&quot;],
    &quot;col1&quot;: [5, 4, 2],
    &quot;col2&quot;: [15, 23, 34],
    &quot;col3&quot;: [32, 12, 24]
})






age_bin
col1
col2
col3




0
0-10
5
15
32


1
10-20
4
23
12


2
20-30
2
34
24




(
    df
    # convert from wide to long format
    .melt(id_vars=&quot;age_bin&quot;)
    # sort dataframe to make sure highest means are first
    .sort_values([&quot;age_bin&quot;, &quot;value&quot;], ascending=[True, False])
    # create groups based on &quot;age_bin&quot; column
    .groupby(&quot;age_bin&quot;)
    # select top N rows within the groups created, change to 5 to get the top 5 columns
    .head(1)
)

Result:





age_bin
variable
value




6
0-10
col3
32


4
10-20
col2
23


5
20-30
col2
34