Difference between maximum and second maximum from a DataFrame

Question

I have a DataFrame I wanted the difference between the maximum and second maximum from the DataFrame as a new column appended to the DataFrame as output.

The data frame looks like this for example (this is quite a huge DataFrame):

 gene_id    Time_1  Time_2  Time_3
a   0.01489251  8.00246 8.164309
b   6.67943235  0.8832114   1.048761

So far I tried the following but it's just taking the headers,

largest = max(df)
second_largest = max(item for item in df if item < largest)

and returning the header value alone.

Answer 1

You can define a func which takes the values, sorts them, slices the top 2 values ([:2]) then calculates the difference and returns the second value (as the first value is NaN). You apply this and pass arg axis=1 to apply row-wise:

In [195]:
def func(x):
    return -x.sort(inplace=False, ascending=False)[:2].diff()[1]

df['diff'] = df.loc[:,'Time_1':].apply(func, axis=1)
df

Out[195]:
  gene_id    Time_1    Time_2    Time_3      diff
0       a  0.014893  8.002460  8.164309  0.161849
1       b  6.679432  0.883211  1.048761  5.630671

Answer 2

Here is my solution:

# Load data
data = {'a': [0.01489251, 8.00246, 8.164309], 'b': [6.67943235, 0.8832114, 1.048761]}
df = pd.DataFrame.from_dict(data, 'index')

The trick is to do a linear sort of the values and keep the top-2 using numpy.argpartition. You do the difference of the 2 maximum values in absolute value. The function is applied row-wise.

def f(x):
    ind = np.argpartition(x.values, -2)[-2:]
    return np.abs(x.iloc[ind[0]] - x.iloc[ind[1]])

df.apply(f, axis=1)