Appending value from a row onto the same row but in a new column when condition in for loop is met-CodePudding

I need to attach the numeric values, whichever is less, from columns, R2ESL or MCL, into a new column, Lesser Value, for each row (ex. A, B, C).

For example, for param A, MCL with value of 7 is less than R2ESL with value of 10. I want to get 7 into a new column (Lesser Value), but on the same row (A).

So far, I have only been able to use 'append' in a for-loop to attach the Limiting Criteria column.

param	R2ESL	MCL	Limiting Criteria	Lesser Value
A	10	7	MCL	7
B	100	150	R2ESL	100
C	55	55	equal	55

excel_df = pd.read_excel('ESLs_MCL_Comparison_copy.xls')

# Changing data type in columns to float
R2ESL_float = [float(item) for item in R2ESL]
print(R2ESL_float)

MCL_float = [float(item) for item in MCL]
print(MCL_float)

# Comparing the values in each column
result = []
result2 = []
for (R2ESL_float, MCL_float) in zip(R2ESL_float, MCL_float):
    if R2ESL_float > MCL_float:
        result.append("R2ESL")
        # result2.append(param_row_value_R2ESL) <-- Need help here
    elif R2ESL_float < MCL_float:
        result.append("MCL")
        # result2.append(param_row_value_MCL) <-- Need help here
    elif R2ESL_float == MCL_float:
        result.append("Equal")
        # result2.append("param_row_value_MCL_or_R2ESL") <-- Need help here
    else:
        result.append("null")
        # result2.append("null")

excel_df["Limiting Criteria"] = result
# excel_df["Lesser Value"] = result2
print(excel_df)

I am new to python and have only been learning for a few weeks, so please explain clearly.

CodePudding user response：

v1, v2 = df['R2ESL'].values, df['MCL'].values
df['Limiting Criteria'] = np.select([v1==v2, v1<v2, v1>v2], ['equal', 'R2ESL', 'MCL'])

# Determine the lesser value
df['Lesser Value'] = df[['R2ESL', 'MCL']].min(axis=1)

For example (this is copied from an interactive Python session):

>>> df
  param  R2ESL  MCL
0     A     10    7
1     B    100  150
2     C     55   55


>>> v1, v2 = df['R2ESL'].values, df['MCL'].values
>>> df['Limiting Criteria'] = np.select([v1==v2, v1<v2, v1>v2], ['equal', 'R2ESL', 'MCL'])
>>> df
  param  R2ESL  MCL Limiting Criteria
0     A     10    7               MCL
1     B    100  150             R2ESL
2     C     55   55             equal


>>> df['Lesser Value'] = df[['R2ESL', 'MCL']].min(axis=1)
>>> df
 param  R2ESL  MCL Limiting Criteria  Lesser Value
0     A     10    7               MCL             7
1     B    100  150             R2ESL           100
2     C     55   55             equal            55

What it does:

For Limiting Criteria:

.values returns the numpy array behind the Series (or DataFrame)
[v1==v2, v1<v2, v1>v2] creates a list of three numpy arrays containing only boolean values (so-called "masks"). The first contains True's where the item in v1 and v2 are equal, False's otherwise. The second contains True's where the value in v1 is less than the value in v2. The third contains True's where the value in v1 is greater than v2.
np.select takes two parameters: a list of "conditions" (really masks), and a list of values, and replaces True's in each condition with the corresponding value from the list of values

For Lesser Value:

df[['R2ESL', 'MCL']] basically selects a subset dataframe of the original dataframe, with only the columns R2ESL and MCL.
.min(axis=1) gets the minimum value of all the columns for each row.