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.
Advertisement
Answer
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 inv1
andv2
are equal, False’s otherwise. The second contains True’s where the value inv1
is less than the value inv2
. The third contains True’s where the value inv1
is greater thanv2
.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 columnsR2ESL
andMCL
..min(axis=1)
gets the minimum value of all the columns for each row.