# Compare Rows Within a Group and Rank Best to Worst

I'm interested in an approach for comparing rows "within group" to produce a ranking from best to worst based on the performance relative to other rows within a group.

For example, if given this DataFrame:

import pandas as pd
pd.set_option('display.max_columns', 500)

d = {'L1': ['Group 1', 'Group 1', 'Group 1', 'Group 1', 'Group 1', 'Group 1',
'Group 1', 'Group 1', 'Group 1', 'Group 1', 'Group 1', 'Group 1',
'Group 1', 'Group 1', 'Group 1', 'Group 2', 'Group 2', 'Group 2',
'Group 2', 'Group 2', 'Group 2', 'Group 2', 'Group 2', 'Group 2',
'Group 2', 'Group 2', 'Group 2', 'Group 2', 'Group 2', 'Group 2',
'Group 2'],
'L2': ['ryxdgrdb', 'ryxdgrdb', 'tkgkrrfyydk-tk', 'tkgkrrfyydk-tk',
'tkgkrrfyydk-rrfyydk aa', 'tkgkrrfyydk-rrfyydk aa', 'tkgkvyykkdg-tk',
'tkgkvyykkdg-tk', 'tkgkvyykkdg-tk', 'tyyak ndb kaakg',
'tyyak ndb kaakg', 'tyyak ndb kaakg', 'tyyak ndb kaakg',
'tyyak ndb kaakgkdrf', 'tyyak ndb kaakgkdrf', 'tyyak-tyy', 'tyyak-tyy',
'tyyak-tyyak rrfy', 'tyyak-tyyak rrfy', 'tyyak-tyyak xfarya',
'tyyak-tyyak xfarya', 'tyyak-tyyak mdx', 'tyyak-tyyak mdx',
'tyyak-tyyak mdxm vyykk', 'tyyak-tyyak mdxm vyykk',
'tyyak-tyyak mdxm vyykk', 'tyyak-tyyak', 'tyyak-tyyak', 'tyyak-tyyak',
'tyyak-tyyak', 'tyyak-tyyak'],
'm1': [77758, 56926, 33338, 68575, 18628, 31271, 17594, 66825, 34408, 51986,
77681, 59951, 15777, 13475, 29964, 300474, 84509, 14220, 18346, 47370,
34887, 73741, 201882, 24374, 38197, 42884, 35621, 369248, 14337, 92603,
686183],
'm2': [0.001325, 0.0057090000000000005, 0.041484, 0.028114999999999998,
0.014011000000000001, 0.008378, 0.021712000000000002,
0.033714999999999995, 0.033306, 0.026392000000000002, 0.021447,
0.022969, 0.020916999999999998, 0.056401, 0.063209, 0.039478, 0.045534,
0.055555999999999994, 0.028671, 0.021490000000000002, 0.019492,
0.027270999999999997, 0.033272, 0.023755000000000002, 0.016729,
0.016393, 0.006878, 0.034549, 0.024622, 0.018304, 0.018087],
'm3': [1.801067961, 1.801876923, 0.925263919, 1.037131743, 1.5906130269999998,
1.527480916, 1.13091623, 0.94930759, 1.088324607, 1.208360058,
1.312797119, 1.307254902, 1.3567272730000002, 0.875078947, 0.768284055,
0.9478797840000001, 0.977858628, 0.821075949, 0.7244676809999999,
0.623163065, 0.427955882, 0.8715464940000001, 0.9397126690000001,
0.986839378, 0.684053208, 1.0410241820000001, 0.896897959, 0.828533354,
0.740311615, 1.074094395, 1.004682137],
'm4': [185.51, 585.61, 1279.64, 1999.59, 415.15, 400.2, 432.01, 2138.79,
1247.22, 1657.87, 2187.12, 1800.09, 447.72, 665.06, 1455.13, 11243.75,
3762.8, 648.65, 381.07, 634.38, 291.01, 1752.68, 6312.05, 571.38,
437.11, 731.84, 219.74, 10569.6, 261.33, 1820.59, 12469.11],
'm5': [1, 1, 32, 47, 4, 3, 20, 88, 44, 47, 51, 45, 9, 36, 66, 240, 71, 13, 5,
3, 1, 14, 50, 12, 0, 9, 3, 104, 3, 24, 102]}

df = pd.DataFrame(d)


You define "good" for certain columns when those values are higher, and some columns are "good" when they have lower values. You then think you might want to weight these columns at some point.

lookup = {
'm1': {'higher_is_better': True, 'weight': 1.0},
'm2': {'higher_is_better': True, 'weight': 1.0},
'm3': {'higher_is_better': False, 'weight': 1.0},
'm4': {'higher_is_better': False, 'weight': 1.0},
'm5': {'higher_is_better': True, 'weight': 1.0}
}

for idx,g in df.groupby(by=['L1', 'L2']):
print(g)


Example group from printing g:

         L1           L2      m1        m2        m3        m4   m5
26  Group 2  tyyak-tyyak   35621  0.006878  0.896898    219.74    3
27  Group 2  tyyak-tyyak  369248  0.034549  0.828533  10569.60  104
28  Group 2  tyyak-tyyak   14337  0.024622  0.740312    261.33    3
29  Group 2  tyyak-tyyak   92603  0.018304  1.074094   1820.59   24
30  Group 2  tyyak-tyyak  686183  0.018087  1.004682  12469.11  102


So what I'm saying is, WITHIN EACH GROUP in the groupby...

Question 1 Given that I know what direction "good" and "bad" are for each m1 - m5 (metric) column, is there a smart way to rank these rows based on their relative performance to other rows within the group?

Question 2 Is there such a thing as "in-group" classification? A model trained from groups within a dataset?

Output would be a rank column - 1 (best), 2 (second best) ... n (worst).

For the sake of discussion, let's say column m5 is the most important feature.

What I've tried

Min/Max scaling, classification (dabbled a bit), ranking. I have OK results but 1) getting the weights right is challenging and 2) this feels like a subjective task dependent on what one's opinion of a "good" ranking is. Just my observations at the moment.

I'd say you're on the right track. If your intention were to classify rows within a group based on a strict ordering of the columns (i.e. top in m5 is definitely top, and on from there) then it would just be a matter of sorting using d.sort_values(by=["L1","m5",...]). But it seems like you're definitely interested in applying some weights to the columns and then coming up with a weighted score for each row.

I think a good option for you is to use principal components analysis (PCA) on the columns of interest and generate a score from that. PCA is a dimensionality reduction method. In a nutshell, it analyzes the covariance between a set of variables and looks for common underlying patterns (e.g. when m5 is up m3 and m2 go down,...) each pattern is a component which can usually be tied to trends in the data. You find a better explanation here.

To use this on your data simply:

from sklearn.decomposition import PCA

# you have to decide the number of components you want to use (less than # columns)
pca = PCA(n_components=2)

# get all the "m" columns assuming those are the ones you care about
X = d[[c for c in d.columns if 'm' in c]]

# fit the PCA model to your data
pca.fit(X)

# apply the dimensionality reduction (predict/transform) and attach it to the data
d = pd.concat([d,pca.transform(X)],axis=1)


The advantage of PCA is that it provides you with a weight for each component based on how much of the variance in the variables they explain.

pca.explained_variance_ratio_


You can multiply each weight with each component in the transformed data, get the weighted sum across rows and the sort by group and the weighted sum. You could apply the PCA to each group individually by creating subsets of your data (X=d[d['L1']=='Group1']) but more data will yield a more reliable PCA result unless there is some remarkably pronounced difference in what you expect from each group. Hope this helps.

• Thanks for the suggestion to use PCA. I tried your suggestions today but it hasn't been immediately fruitful. Case 1: when only two rows in a group, PCA produces positive and negative values of the same number. When this is sorted by group and weighted sum, it isn't choosing the "obvious" best winner consistently. In any event, I'll keep trying this or a variant approach! Thanks for the idea. – Jarad Jun 28 '19 at 3:44