Estimate the mean of a DataFrameGroupBy by only considering values in a percentile range

user_7936266 0
Viewed 216

I need to estimate the mean of a pandas DataFrameGroupBy by only considering the values between a given percentile range.

For instance, given the snippet

import numpy as np
import pandas as pd
a = np.matrix('1 1; 1 2; 1 4; 2 1; 2 2; 2 4')
data = pd.DataFrame(a)
groupby = data.groupby(0)
m1 = groupby.mean()

the result is

m1 =            1
      0          
      1  2.333333
      2  2.333333

However, if a percentile range is picked to exclude the maximum and minimum values the result should be

m1 =     1
      0          
      1  2
      2  2

How can I filter, for each group, the values between an arbitrary percentile range before estimating the mean? For instance, only considering the values between the 20th and 80th percentiles.

pandas-groupby pandas dataframe group-by python
5 Answers

You can use a custom function with either np.percentile or pd.Series.quantile. The performance difference is marginal. The below example includes values only above the 20th and below the 80th percentile in calculating groupwise mean.

import pandas as pd
import numpy as np

a = np.matrix('1 1; 1 2; 1 4; 2 1; 2 2; 2 4')
data = pd.DataFrame(a)

def jpp_np(df):
    def meaner(x, lowperc, highperc):
        low, high = np.percentile(x, [lowperc, highperc])
        return x[(x > low) & (x < high)].mean()
    return df.groupby(0)[1].apply(meaner, 20, 80).reset_index()

def jpp_pd(df):
    def meaner(x, lowperc, highperc):
        low, high = x.quantile([lowperc/100, highperc/100]).values
        return x[x.between(low, high, inclusive=False)].mean()
    return df.groupby(0)[1].apply(meaner, 20, 80).reset_index()

data = pd.concat([data]*10000)

assert np.array_equal(jpp_np(data), jpp_pd(data))

%timeit jpp_np(data)  # 11.2 ms per loop
%timeit jpp_pd(data)  # 12.5 ms per loop
user_92095460

You can define a function to calculate this mean for a dataframe, then do use the apply method. Something like :

def mean_percent(df,per1,per2):
    #Write meaningful code here

data = pd.DataFrame(a)
groupby = data.groupby(0)
m1 = groupby.apply(lambda df: mean_percent(df,20,80))

This will calculate mean with value in 20-80 percentile range for each group. If you need help writing the first function, feel free to ask in the comment, and i'll edit this answer.

user_105117930

try

data.sort_values(by=1).groupby(by=0).agg(['first','last']).mean()

OR

data.sort_values(by=1).groupby(by=0).agg(['min','max']).mean()
user_57048630

One approach is to filter the dataframe before using groupby. You can sort the dataframe by the column of interest and then exclude the first and last rows.

data = data.sort_values(1).iloc[1:-1,:]
groupby = data.groupby(0)
m1 = groupby.mean()

Another side note: it's best practice to not use the same variable name as a common method such as 'groupby'. If you can change that to some other name, that would be highly recommended.

user_94542830

Use np.percentile or np.quantile with groupby+apply:

a = np.matrix('1 1 2; 1 2 3; 1 4 4; 2 1 6; 2 2 8; 2 4 16;7 8 45;9 10 9;11 12 3')
df = pd.DataFrame(a,columns=['a','b','c'])
#drop column which is key for grouping
min_val,max_val = np.percentile(df.drop('a',1).values,[20,80],axis=0)
#alternative np.quantile(df.drop('a',1).values,[0.2,0.8],axis=0)
df1 = df.groupby('a').apply(lambda x: x[(x<max_val)&(x>min_val)].mean())

print(df1)
      b    c
a           
1   3.0  4.0
2   3.0  7.0
7   8.0  NaN
9   NaN  9.0
11  NaN  NaN
user_83208610
Related