[Python] Pandas selecting row with max value in each group [duplicate]

I have an assignment revolving around feature selection. After doing clustering features by the "distance of correlation" from each other feature, I now need to select only the feature with best correlation to the target out of each cluster.

I do this feature selection step after already filtering to 100 features, so I arrange the features to 30 clusters.

from scipy.cluster.hierarchy import linkage, fcluster
from scipy.spatial.distance import pdist

corr_matrix = df_top_100.corr().abs()
distance_matrix = 1 - corr_matrix
Z = linkage(pdist(distance_matrix), method='ward')
clusters = fcluster(Z, t=30, criterion='maxclust')
clustered_features = pd.DataFrame({'feature': df_top_100.columns, 'cluster': clusters})
clustered_features.sort_values(by='cluster')


feature cluster
0 m2512.82 1
1 m2514.75 1
2 m2516.68 1
3 m2510.89 1
4 m2518.61 1
... ... ...
28 m1056.81 26
19 m1064.53 27
22 m1066.45 28
18 m1062.6 29
20 m1060.67 30
100 rows × 2 columns


After clustering, I add the correlation with the target for each of the features:

clustered_features['corr_with_SOC'] = clustered_features['feature'].map(df[top_100_features].corrwith(df['SOC']))


feature cluster corr_with_SOC
0 m2512.82 1 0.633739
1 m2514.75 1 0.633690
2 m2516.68 1 0.633343
3 m2510.89 1 0.633246
4 m2518.61 1 0.632762
... ... ... ...
28 m1056.81 26 0.603756
19 m1064.53 27 0.611138
22 m1066.45 28 0.609888
18 m1062.6 29 0.611323
20 m1060.67 30 0.610205
100 rows × 3 columns


Essentially, I want the first of each group from the output of this command:

clustered_features.sort_values(by=['cluster', 'corr with SOC'], ascending=[True, False])


I know I need to use df.groupby('cluster'), but can't figure out how to do a max aggregation to use for a filter on the dataframe, and it seems the pandas documentation only talks about simpler examples.

Continue reading...