This is a continuation of a question I asked previously in How to efficiently select the top N columns by grouping for each row of a pandas DataFrame?. My needs have evolved as I work with my dataset — thanks again to everyone who has helped me so far. Here is the problem as it currently stands:

Let’s say that I have a pandas DataFrame representing the scores of each “contestant” of a hypothetical contest organized by date. Note that there may be sporadic NaN values interspersed:

import numpy as np
import pandas as pd

rng = np.random.default_rng()
dates = pd.date_range('2024-08-01', '2024-08-07')
contestants = ['Alligator', 'Beryl', 'Chupacabra', 'Dandelion', 'Eggplant', 'Feldspar']
scores = rng.random(len(dates) * len(contestants))
scores[rng.integers(len(scores), size=10)] = np.nan
scores = scores.reshape((len(dates), len(contestants)))
scores = pd.DataFrame(scores, dates, contestants)
scores.index.name = 'DATE'
scores.columns.name = 'CONTESTANT'

CONTESTANT  Alligator     Beryl  Chupacabra  Dandelion  Eggplant  Feldspar
DATE
2024-08-01   0.425859  0.869790    0.025546   0.249784  0.164426  0.292931
2024-08-02   0.545743  0.245658    0.384288   0.148041  0.759137       NaN
2024-08-03   0.558930  0.773545    0.215342   0.644964  0.204309       NaN
2024-08-04   0.448075       NaN         NaN   0.795700  0.744143  0.807003
2024-08-05   0.858097  0.349170    0.339740   0.445206       NaN  0.118371
2024-08-06        NaN  0.847647    0.086368   0.806557       NaN       NaN
2024-08-07   0.167334  0.063111    0.152129   0.823477  0.613271  0.709280

In addition, each contestant is mapped to a particular category:

category_mapping = {
    'Alligator': 'Animal',
    'Beryl': 'Mineral',
    'Chupacabra': 'Animal',
    'Dandelion': 'Vegetable',
    'Eggplant': 'Vegetable',
    'Feldspar': 'Mineral'
}

Given this setup, how do I retain the best score from each category in each row, zeroing out or setting to NaN any scores that do not make the cut? For example, the results should look something like this:

CONTESTANT  Alligator     Beryl  Chupacabra  Dandelion  Eggplant  Feldspar
DATE
2024-08-01   0.425859  0.869790    0.000000   0.249784  0.000000  0.000000
2024-08-02   0.545743  0.245658    0.000000   0.000000  0.759137       NaN
2024-08-03   0.558930  0.773545    0.000000   0.644964  0.000000       NaN
2024-08-04   0.448075       NaN         NaN   0.795700  0.000000  0.807003
2024-08-05   0.858097  0.349170    0.000000   0.445206       NaN  0.000000
2024-08-06        NaN  0.847647    0.086368   0.806557       NaN       NaN
2024-08-07   0.167334  0.000000    0.000000   0.823477  0.000000  0.709280

In addition, how do I go about making this fast? My actual application is a Monte Carlo simulation operating on a DataFrame with about 250 rows, 15000 columns, and 175 categories, so efficiency is key here. I’ve gotten this to mostly work through a combination of answers from @mozway and @rezan21 (transpose-groupby-idxmax-transpose) but I suspect that my approach is suboptimal and could be much better. Thanks for your help!