Filtering of values within a column based on the dates in another column

user_14091382 0
Viewed 69

Hi all I have a pandas dataframe with columns Date and Values. What I am trying to do is to keep values within the 25% and 75% quantile based on the different dates for the table below. Is there a way to do this?

Date Values
2020-1-23 455.0
2020-1-25 428.0
2020-1-25 379.0
2020-1-25 386.0
2020-1-26 385.0
2020-1-26 476.0
2020-1-26 427.0
2020-1-26 399.0
2020-1-26 374.0
2020-1-26 419.0
quantile pandas percentile python-3.x
1 Answers

Use GroupBy.transform with lambda functions and pass to Series.between for filter in boolean indexing:

g = df.groupby('Date')['Values']
s1 = g.transform(lambda x: x.quantile(0.25))
s2 = g.transform(lambda x: x.quantile(0.75))

df = df[df['Values'].between(s1, s2)]
print (df)
        Date  Values
0  2020-1-23   455.0
3  2020-1-25   386.0
7  2020-1-26   399.0
9  2020-1-26   419.0

Or use DataFrameGroupBy.quantile with DataFrame.join:

df1 = df.join(df.groupby('Date')['Values'].quantile([0.25,0.75]).unstack(), on='Date')

df = df[df['Values'].between(df1[0.25], df1[0.75])]
print (df)
        Date  Values
0  2020-1-23   455.0
3  2020-1-25   386.0
7  2020-1-26   399.0
9  2020-1-26   419.0
user_29010020
Related