CODEWITHSUNDEEP
pythonpandas
ARJ
ARJ

Reputation: 2080

Difference between maximum and second maximum from a DataFrame

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.

Upvotes: 3

Views: 1428

Answers (3)

JoeCondron
JoeCondron

Reputation: 8906

Here's an elegant solution that doesn't involve sorting or defining any functions. It's also fully vectorized as it avoid use of the apply method. 

maxes = df.max(axis=1)
less_than_max = df.where(df.lt(maxes, axis='rows'))
seconds = less_than_max.max(axis=1)
df['diff'] = maxes - seconds

Upvotes: 1

EdChum
EdChum

Reputation: 394031

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

Upvotes: 1

Kirell
Kirell

Reputation: 9798

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)

Upvotes: 1

Related Questions