I'd like to filter my dataset by picking rows that are between two values (dimamucally defined as quantiles) per each group. Concretely, I have a dataset like

import pandas as pd
df = pd.DataFrame({'day': ['one', 'one', 'one', 'one', 'one', 'one', 'two', 'two', 'two', 'two', 'two'], 
                   'weather': ['rain', 'rain', 'rain', 'sun', 'sun', 'sun', 'sun', 'rain', 'rain', 'sun', 'rain'], 
                   'value': [1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11]})

I'd like to select the rows where the values are between the 0.1 and 0.9 quantile per each day and per each weater. I can calculate the quantiles via

df.groupby(['day', 'weather']).quantile([0.1, .9])

But then I feel stuck. Joining the resulting dataset with the original one it's a waste (the original dataset can be quite big), and I am wondering if there is something along the lines of

df..groupby(['day', 'weather']).select('value', between=[0.1, 0.9])

CodePudding user response：

Transform value with quantile

g = df.groupby(['day', 'weather'])['value']
df[df['value'].between(g.transform('quantile', 0.1), g.transform('quantile', 0.9))]

   day weather  value
1  one    rain      2
4  one     sun      5
8  two    rain      9