How do I obtain the second highest value in a row?

Question

I want to obtain the second highest value of a certain section for each row from a dataframe. How do I do this?

I have tried the following code but it doesn't work:

df.iloc[:, 5:-3].nlargest(2)(axis=1, level=2)

Is there any other way to obtain this?

Answer 1

Using apply with axis=1 you can find the second largest value for each row. by finding the first 2 largest and then getting the last of them

df.iloc[:, 5:-3].apply(lambda row: row.nlargest(2).values[-1],axis=1)

Example

The code below find the second largest value in each row of df.

In [1]: import pandas as pd

In [2]: import numpy as np

In [3]: df = pd.DataFrame({'Col{}'.format(i):np.random.randint(0,100,5) for i in range(5)})

In [4]: df
Out[4]: 
   Col0  Col1  Col2  Col3  Col4
0    82    32    14    62    90
1    62    32    74    62    72
2    31    79    22    17     3
3    42    54    66    93    50
4    13    88     6    46    69

In [5]: df.apply(lambda row: row.nlargest(2).values[-1],axis=1)
Out[5]: 
0    82
1    72
2    31
3    66
4    69
dtype: int64

Answer 2

I think you need sorting per rows and then select:

a = np.sort(df.iloc[:, 5:-3], axis=1)[:, -2]

Sample:

np.random.seed(100)
df = pd.DataFrame(np.random.randint(10, size=(10,10)))
print (df)
   0  1  2  3  4  5  6  7  8  9
0  8  8  3  7  7  0  4  2  5  2
1  2  2  1  0  8  4  0  9  6  2
2  4  1  5  3  4  4  3  7  1  1
3  7  7  0  2  9  9  3  2  5  8
4  1  0  7  6  2  0  8  2  5  1
5  8  1  5  4  2  8  3  5  0  9
6  3  6  3  4  7  6  3  9  0  4
7  4  5  7  6  6  2  4  2  7  1
8  6  6  0  7  2  3  5  4  2  4
9  3  7  9  0  0  5  9  6  6  5

print (df.iloc[:, 5:-3])
   5  6
0  0  4
1  4  0
2  4  3
3  9  3
4  0  8
5  8  3
6  6  3
7  2  4
8  3  5
9  5  9

a = np.sort(df.iloc[:, 5:-3], axis=1)[:, -2]
print (a)
[0 0 3 3 0 3 3 2 3 5]

If need both values:

a = df.iloc[:, 5:-3].values
b = pd.DataFrame(a[np.arange(len(a))[:, None], np.argsort(a, axis=1)])
print (b)
   0  1
0  0  4
1  0  4
2  3  4
3  3  9
4  0  8
5  3  8
6  3  6
7  2  4
8  3  5
9  5  9