Find the difference between the max value and 2nd highest value within a subset of pandas columns

Question

I have a fairly large dataframe:

	A	B	C	D
0	17	36	45	54
1	18	23	17	17
2	74	47	8	46
3	48	38	96	83

I am trying to create a new column that is the (max value of the columns) - (2nd highest value) / (2nd highest value).

In this example it would look something like:

	A	B	C	D	Diff
0	17	36	45	54	.20
1	18	23	17	17	.28
2	74	47	8	46	.57
3	48	38	96	83	.16

I've tried df['diff'] = df.loc[:, 'A': 'D'].max(axis=1) - df.iloc[:df.index.get_loc(df.loc[:, 'A': 'D'].idxmax(axis=1))] / ...

but even that part of the formula returns an error, nevermind including the final division. I'm sure there must be an easier way going about this.

Edit: Additionally, I am also trying to get the difference between the max value and the column that immediately precedes the max value. I know this is a somewhat different question, but I would appreciate any insight. Thank you!

Answer 1

One way using pandas.Series.nlargest with pct_change:

df["Diff"] = df.apply(lambda x: x.nlargest(2).pct_change(-1)[0], axis=1)

Output:

    A   B   C   D      Diff
0  17  36  45  54  0.200000
1  18  23  17  17  0.277778
2  74  47   8  46  0.574468
3  48  38  96  83  0.156627

Answer 2

One way is to apply a udf:

def get_pct(x):
    xmax2, xmax = x.sort_values().tail(2)
    return (xmax-xmax2)/xmax2

df['Diff'] = df.apply(get_pct, axis=1)

Output:

    A   B   C   D      Diff
0  17  36  45  54  0.200000
1  18  23  17  17  0.277778
2  74  47   8  46  0.574468
3  48  38  96  83  0.156627