New dataframe column using existing column names as values

Question

I have the following pandas dataframe:

ID	Class	LR	XG	SV	BEST_R2
1	Class1	.76	.78	.99	.99
2	Class2	.92	.89	.91	.92
3	Class3	.87	.95	.87	.95

This is a dataframe with the R2 of each of a series of machine learning models (LR/XG/SV) for each ID. The column "BEST_R2" represents the best R2 score for that ID across models (.max(axis=1)). I need another column with the model name for best score. For example, the dataframe below. Any tips on how to achieve this programmatically?

ID	Class	LR	XG	SV	BEST_R2	BEST MODEL
1	Class1	.76	.78	.99	.99	SV
2	Class2	.92	.89	.91	.92	LR
3	Class3	.87	.95	.87	.95	XG

fsimonjetz · Accepted Answer · 2022-08-12 22:37:09Z

4

Assuming that ID is the index, you can do

df["Best Model"] = df[["LR", "XG", "SV"]].idxmax(axis=1)

Result:

      LR    XG    SV  BEST_R2 Best Model
ID                                      
1   0.76  0.78  0.99     0.99         SV
2   0.92  0.89  0.91     0.92         LR
3   0.87  0.95  0.87     0.95         XG

edited Aug 12, 2022 at 22:37

answered Aug 12, 2022 at 18:40

fsimonjetz

5,7923 gold badges7 silver badges23 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Dr.Data Over a year ago

I included a simplified version of the dataframe I'm working with in the OP. The real DF has a column with non-numeric values, so I need to only select the value of LR, XG, or SV which is the highest and use that values column name as the new columns (Best Model) value. I've updated the DF in the OP to reflect this. Your code works only if I reset the index to the Class column and set that as numeric. It works, though, so thanks!

fsimonjetz Over a year ago

@Dr.Data Thanks for the feedback! It's mostly just a matter of selecting the relevant columns, I edited my answer to account for that.

flbyrne · Accepted Answer · 2022-08-12 21:51:02Z

0

Here is a way to do it:

best_models = []
for row in df.itertuples():
    tuples = sorted([('LR',row.LR), ('XG',row.XG), ('SV',row.SV)],reverse = True, key = lambda x:x[1])
    best_models.append(tuples[0][0])
df['Best Models'] = best_models
df

answered Aug 12, 2022 at 21:51

flbyrne

3313 silver badges10 bronze badges

Collectives™ on Stack Overflow

New dataframe column using existing column names as values

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related