Custom ranking selection with Pandas

Question

I have a 100x5 pandas dataframe with values ranging from 1 to 499.

# seed for reproducability
np.random.seed(3)
sample = pd.DataFrame(np.random.randint(0, 500, size = (100, 5)))
sample.columns = "X Y Z F V".split()

I want to select 10 rows from this dataframe, where for each column I select the rows corresponding the top 2 values for each of the columns (separately) and without duplicates.

If there are duplicates lets say for top 1st of column X and top 2nd of Y, then randomly keep one of them and the other one replace with the next biggest (top 3rd for X or top 3rd for Y -> randomly) and do it until there are no duplicate rows selected for any columns.

What I have so far

# convert it to long format and use groupby to get top values and their index - ID
stacked = (
            sample
           .stack()
           .reset_index()
           .rename(columns = {"level_0": "ID", "level_1": "Feature", 0: "Value"})
           .set_index("ID")
          )

stacked.groupby("Feature").Value.nlargest(2)

Which gives me this

Feature ID  Value
F   37  489
F   32  481
V   19  497
V   22  497
X   25  495
X   32  491
Y   17  498
Y   22  496
Z   95  496
Z   45  489

It means, I need to select the rows based on ID values from that dataset. However, as you see, for column V and Y, F and X I have duplicate rows selected. I could not come up with the implementation of the logic with duplicates. I would be grateful for any help

One potential approach could be to select 5 rows per each column top values with groupby.nlargest() and from that dataframe select two as top as possible rows per each column where there are no duplicates. Unfortunately, I do not know any pythonic ways of doing this:

    Feature ID  Value
0   F   37  489
1   F   32  481
2   F   65  474
3   F   82  470
4   F   66  467
5   V   19  497
6   V   22  497
7   V   11  489
8   V   98  486
9   V   15  484
10  X   25  495
11  X   32  491
12  X   99  490
13  X   76  487
14  X   93  486
15  Y   17  498
16  Y   22  496
17  Y   89  494
18  Y   68  493
19  Y   3   480
20  Z   95  496
21  Z   45  489
22  Z   62  488
23  Z   79  485
24  Z   22  484

Desirable result would be (there is randomality)

    X   Y   Z   F   V
37  133 212 351 489 106
32  491 135 441 481 427
19  48  445 289 308 497
22  182 496 484 91  497
25  495 444 216 311 267
99  490 164 345 23  365
17  400 498 274 331 183
89  309 494 122 82  140
95  275 213 496 167 98
45  267 246 489 252 17

Maybe I explained the logic in a complex way, but if you look at dataset and desirable outcome, you might get more clue

EDIT: fixed the desirable result, it contained duplicate

user_12833166 · Answer

Here is one possible way to approach the problem, where we use set to store the indices corresponding to top two largest values from each column

Pseudocode

For every column in the dataframe
- Drop the indices that are already selected from the previous columns
- drop the duplicate values
- Sort the values in descending order
- Using set union (|=) add the indices corresponding to top 2 largest values in the current column

ix = set()
for c in df.columns:
    s = df[c].drop(ix).drop_duplicates().sort_values(ascending=False)
    ix |= set(s.index[:2])

>>> df.loc[ix]

      X    Y    Z    F    V
32  491  135  441  481  427
65  274  320  455  474  437
37  133  212  351  489  106
11  375   54    0  192  489
45  267  246  489  252   17
17  400  498  274  331  183
19   48  445  289  308  497
22  182  496  484   91  497
25  495  444  216  311  267
95  275  213  496  167   98

user_14764038 · Answer

I came up with a solution although it is not the most elegant:

indexes = []
while len(indexes) < 2 * len(sample.columns):
    new_df = sample.drop(index=indexes)
    if new_df.iloc[:, len(indexes) // 2].idxmax() not in indexes:
        indexes.append(new_df.iloc[:, len(indexes) // 2].idxmax())
sample.iloc[indexes]

Output:

>>> print(sample.iloc[indexes])

      X    Y    Z    F    V
25  495  444  216  311  267
32  491  135  441  481  427
17  400  498  274  331  183
22  182  496  484   91  497
95  275  213  496  167   98
45  267  246  489  252   17
37  133  212  351  489  106
65  274  320  455  474  437
19   48  445  289  308  497
11  375   54    0  192  489

Custom ranking selection with Pandas

2 Answers