Removing min, max and calculating average

user_14695007 0
Viewed 578

I have columns of numbers and I would need to remove only one min. and one max. and then calculate the average of the numbers that remain. The hitch is that the min/max could be anywhere in the column and some rows may be blank (null) or have a zero, or the column might have only 3 values. All numbers will be between 0 and 100. For example:

Value    Property
80          H
30.5        D
40          A
30.5        A
72          H
56          D
64.2        H

If there is more than one min or max, only one can be removed.

To calculate the minimum and maximum of a column, I did as follows:

maximum = df['Value'].max()
minimum = df['Value'].min()

In the condition for calculating this average, I also included the condition where it is not null and where it is not equal to zero. However, I do not know how to remove only one max and one min, and add information on greater than 3 rows/values.

I hope you can provide some help/tips on this.

pandas python
3 Answers

If the objective is to calculate the average without one min and one max, you can just do

(df['Value'].sum() - df['Value'].min() - df['Value'].max())/(len(df)-2)

which outputs 52.54 for your data. Note that this will ignore NaNs etc. This will not modify your df which, if I read the question right, was not the objective anyway

user_145514260

Let us do idxmin and idxmax

out = df.drop([df.Value.idxmax(),df.Value.idxmin()])
Out[27]: 
   Value Property
2   40.0        A
3   30.5        A
4   72.0        H
5   56.0        D
6   64.2        H
user_79645270

Lately I struggled a little bit with similar problem. Finally I came across on numpy.ma library and found this to be elegant solution.

import numpy.ma as ma
df['Value'].values

# output -> array([80. , 30.5, 40. , 30.5, 72. , 56. , 64.2])

col_name= 'Value'
ma.masked_outside(df[col_name].values, df[col_name].min()+0.02, df[col_name].max()-0.05)

# output -> masked_array(data=[--, --, 40.0, --, 72.0, 56.0, 64.2],
#             mask=[ True,  True, False,  True, False, False, False],
#       fill_value=1e+20

# mean for values without outliers
ma.masked_outside(df[col_name].values, df[col_name].min()+0.02, df[col_name].max()-0.05).mean()
user_97748250
Related