Sort Pandas Dataframe per column [closed]

I have a dataset with age + another 14 variables. I have created 13 bins representing different age groups like so:

data["age_bins"] = pd.cut(data["age"], [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(["age_bins"]).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

How about a lambda using df.apply? So,

import pandas as pd
import numpy as np
#initializing variable names
variable_names =['var_' + str(i) for i in range(1, 15, 1)]
variable_names.insert(0, 'age')


Generating random data

data =pd.DataFrame(np.random.randint(0,100,size=(100, len(variable_names))), columns=variable_names)
# 5 bins
data['age_bins'] =pd.cut(data["age"],5)


I assumed 5 bins, you can pass in fixed bins if you like. You might have to deal with NaNs .

#remove the age column
data_age_bins_mean =data.groupby(['age_bins']).mean().drop('age', 1)
col_names =variable_names[1:]
#creating a dict for each age_bin containing key value pairs for the top 5 values
age_bin_top_vars =data_age_bins_mean.apply(lambda x: {col_names[i]: x[i] for i in np.argsort(x)[::-1][:5]},
axis =1)


So, np.argsort to sort the variables with the highest values. Output age_bin_top_vars

age_bins
(0.902, 20.6]    {u'var_7': 52.523809523809526, u'var_6': 51.28...
(20.6, 40.2]     {u'var_7': 65.36842105263158, u'var_6': 57.157...
(40.2, 59.8]     {u'var_6': 52.0, u'var_3': 54.04347826086956, ...
(59.8, 79.4]     {u'var_5': 52.2, u'var_14': 56.3, u'var_12': 5...
(79.4, 99.0]     {u'var_4': 57.11764705882353, u'var_13': 58.35...



Convert the series to json

age_bin_top_vars.to_json()

'{"{"closed":"right","closed_right":true,"length":19.698,"open_left":true,"right":20.6}":{"var_7":52.5238095238,"var_6":51.2857142857,"var_14":51.8095238095,"var_12":52.4285714286,"var_9":51.5238095238},"{"closed":"right","closed_right":true,"length":19.6,"open_left":true,"right":40.2}":{"var_7":65.3684210526,"var_6":57.1578947368,"var_4":55.9473684211,"var_12":56.3684210526,"var_8":53.9473684211},"{"closed":"right","closed_right":true,"length":19.6,"open_left":true,"right":59.8}":{"var_6":52.0,"var_3":54.0434782609,"var_2":55.6086956522,"var_9":55.8695652174,"var_8":52.6086956522},"{"closed":"right","closed_right":true,"length":19.6,"open_left":true,"right":79.4}":{"var_5":52.2,"var_14":56.3,"var_12":54.05,"var_10":50.3,"var_8":50.55},"{"closed":"right","closed_right":true,"length":19.6,"open_left":true,"right":99.0}":{"var_4":57.1176470588,"var_13":58.3529411765,"var_11":58.4117647059,"var_1":65.8235294118,"var_10":60.3529411765}}'


age_bins is a categorical index, if instead of

"{"closed":"right","closed_right":true,"length":19.698,"open_left":true,"right":20.6}"


style keys you want '(19.698,20.6]' change the age_bins to string.

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({
"age_bin": ["0-10", "10-20", "20-30"],
"col1": [5, 4, 2],
"col2": [15, 23, 34],
"col3": [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="age_bin")
# sort dataframe to make sure highest means are first
.sort_values(["age_bin", "value"], ascending=[True, False])
# create groups based on "age_bin" column
.groupby("age_bin")
# select top N rows within the groups created, change to 5 to get the top 5 columns