How to do a complicated filter in python

Question

I am trying to do a filter based on one column. For example, I want to remove the transaction whenever I see it has account_no 1111

Input

Date        Trans   account_no
2017-12-11  10000   1111
2017-12-11  10000   1112
2017-12-11  10000   1113
2017-12-11  10001   1111
2017-12-11  10002   1113

Desired Output

Date        Trans   account_no
2017-12-11  10002   1113

Edit:

This is different than operator chaining because you are dealing with a duplication/conditional filter

Possible duplicate of pandas: filter rows of DataFrame with operator chaining — splash58
– splash58, Commented Dec 27, 2017 at 21:12

BENY · Accepted Answer · 2017-12-27 21:19:58Z

2

By using issubset + transform

df[~df.groupby('Trans').account_no.transform(lambda x : set([1111]).issubset(x))]
Out[1658]: 
         Date  Trans  account_no
4  2017-12-11  10002        1113

answered Dec 27, 2017 at 21:19

BENY

324k22 gold badges176 silver badges250 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Reese Fitzmaurice Over a year ago

do you know how to modify to the code to filter out for multiple numbers not just 1111 but lets you also add 1112

BENY Over a year ago

@ReeseFitzmaurice df[~df.Trans.isin(df.loc[df.account_no.isin([1112,1111]),'Trans'])]

Reese Fitzmaurice Over a year ago

I am still trying to keep the original question logic, would this work? df=df[~df.groupby('Trans').account_no.transform(lambda x : set([1112,1111]).issubset(x))]

BENY Over a year ago

@ReeseFitzmaurice this will not then , cause they will match both [1112 and 1111, when both condition satisfied, they will return true, seems like you need logic or rather than and right ?

DJK · Accepted Answer · 2017-12-27 23:44:18Z

1

You could do this in two steps. First find all Trans values that have an account_no that is ever equal to 1111 using .loc. Then select all other transactions with isin()

df[~df.Trans.isin(df.loc[df.account_no == 1111,'Trans'])]

         Date  Trans  account_no
4  2017-12-11  10002        1113

edited Dec 27, 2017 at 23:44

answered Dec 27, 2017 at 21:39

DJK

9,3424 gold badges28 silver badges41 bronze badges

Comments

DragonBobZ · Accepted Answer · 2017-12-27 21:43:34Z

0

You can use .loc to filter based on a series.

def complex_filter_criteria(x):
    return x != 1111
df.loc[df['account_no'].apply(complex_filter_criteria)]

df['account_no'].apply(complex_filter_criteria) will return a series of True/False evaluations for each entry in the column account_no. Then, when you pass that into df.loc, it returns a dataframe consisting only of the rows corresponding to the True evaluations from the series.

answered Dec 27, 2017 at 21:43

DragonBobZ

2,51824 silver badges35 bronze badges

Collectives™ on Stack Overflow

How to do a complicated filter in python

3 Answers 3

4 Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

4 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related