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.
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
An alternative; I expect @jezrael's solution to be faster as it is within numpy and pd.wide_to_long is not particularly fast:
grouping = (
pd.wide_to_long(df.filter(regex="^Pay|Low|High"),
i=["Low", "High"],
stubnames="Pay",
j="num")
.query("Low==num or High==num")
.groupby(level=["Low", "High"])
.Pay.max()
)
grouping
Low High
1 1 74
2 21
3 54
Name: Pay, dtype: int64
df.join(grouping.rename("expected_output"), on=["Low", "High"])
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
