[FIXED] Select Best Value Based on Highest Score

Issue

Need to create 2 new column(expected_value and expected_score) by identifying maximum score between Score and Score.1 and returning corresponding value from Value and Value.1 columns whose score is that maximum.

dict = {'Alias': ['JOB_NUMBER','GOOD_DATE','SALARY','SALARY_DATE','NAME','NUMBER','NEXT_DATE','COMMISSION_AMOUNT',
'BALANCE','ADDRESS'],'Value': ['123456789','8/10/2022','$373.28','8/10/2022','Group Group','08764510160','8/10/2022','$9.22','$125,770.04','11111111 N Test Rd'],'Score': [98.0,98.0,95.0,98.0,92.0,99.0,51.0,94.0,82.0,95.0],'page': [0, 0, 0, 0, 0, 0, 0, 0, 0, 0],'Alias.1': ['JOB_NUMBER','GOOD_DATE','SALARY','SALARY_DATE','NAME','NUMBER','NEXT_DATE',
'COMMISSION_AMOUNT','BALANCE','ADDRESS'],'Value.1': [9090909090,np.nan,np.nan,np.nan,np.nan,
np.nan,'8/1/2022',np.nan,np.nan,np.nan],'Score.1': [99.0,0.0,0.0,0.0,0.0,0.0,98.0,0.0,0.0,0.0],
'page.1': [1, 1, 1, 1, 1, 1, 1, 1, 1, 1]}

df = pd.DataFrame(dict)

Original DataFrame:

Expected Result:

My Attempt not able to make any inroad:

# column name with max duration value
max_col_name = df.filter(like='Score', axis=1).max(axis = 1).idxmax()

# index of max_col_name
max_col_idx =df.columns.get_loc(max_col_name)

# row index of max value in max_col_name
max_row_idx = df[max_col_name].idxmax()

# output with .loc
df.iloc[max_row_idx, [0, max_col_idx, max_col_idx + 1 ]]

Solution

With dynamic columns for Score (e.g. Score, Score.1, Score.2 ... Score.n) and same with Value columns, try as follows:

# don't use "dict" as a variable to store a `dict`, it will overwrite
# its built-in functionality!

df = pd.DataFrame(my_dict)

# group all cols that start with `Score`|`Value`

scores = df.filter(like='Score', axis=1).columns
values = df.filter(like='Value', axis=1).columns

# create a mask with `True` for each cell in `df[scores]` that
# matches the max of its row (`axis=1`)

max_values = df[scores].max(axis=1)
m = df[scores].eq(max_values, axis=0).to_numpy()

# apply (inverse of) mask to `df[values]` and fill up any `NaN` value
# in 1st column with the first value that is found in 2nd, 3rd, etc column
# and then select only that first column; will now always be filled

df['expected_value'] = df[values].mask(~m).fillna(method='bfill', axis=1).iloc[:,0]
df['expected_score'] = max_values

print(df)

               Alias               Value  ...      expected_value  expected_score
0         JOB_NUMBER           123456789  ...          9090909090            99.0
1          GOOD_DATE           8/10/2022  ...           8/10/2022            98.0
2             SALARY             $373.28  ...             $373.28            95.0
3        SALARY_DATE           8/10/2022  ...           8/10/2022            98.0
4               NAME         Group Group  ...         Group Group            92.0
5             NUMBER         08764510160  ...         08764510160            99.0
6          NEXT_DATE           8/10/2022  ...            8/1/2022            98.0
7  COMMISSION_AMOUNT               $9.22  ...               $9.22            94.0
8            BALANCE         $125,770.04  ...         $125,770.04            82.0
9            ADDRESS  11111111 N Test Rd  ...  11111111 N Test Rd            95.0

Answered By - ouroboros1