python - dynamic concatenation of columns for finding max

Question

Here's my data -

ID,Pay1,Pay2,Pay3,Low,High,expected_output
1,12,21,23,1,2,21
2,21,34,54,1,3,54
3,74,56,76,1,1,74

The goal is to calculate the max Pay of each row as per the Pay column index specified in Low and High columns.

For example, for row 1, calculate the max of Pay1 and Pay2 columns as Low and High are 1 and 2.

I have tried building a dynamic string and then using eval function which is not performing well.

question from:https://stackoverflow.com/questions/65932066/dynamic-concatenation-of-columns-for-finding-max

Answer 1

Idea is filter only Pay columns and then using numpy broadcasting select columns by Low and High columns, pass to DataFrame.where and last get max:

df1 = df.filter(like='Pay')

m1  = np.arange(len(df1.columns)) >= df['Low'].to_numpy()[:, None] - 1
m2  = np.arange(len(df1.columns)) <= df['High'].to_numpy()[:, None] - 1

df['expected_output'] = df1.where(m1 & m2, 0).max(axis=1)
print (df)
   ID  Pay1  Pay2  Pay3  Low  High  expected_output
0   1    12    21    23    1     2               21
1   2    21    34    54    1     3               54
2   3    74    56    76    1     1               74