Conditional filtering on multiple columns for a pandas dataframe

Question

I have a pandas dataframe with three columns, A (Dates), B (Categorical Values), and C (Actual Values).

A     |  B   |  C
01-19 |  5   |  0.34
01-19 |  3   |  0.25
01-19 |  7   |  0.07
01-20 |  5   |  0.15
01-20 |  2   |  0.36

And so on.

What I want is to filter rows according to their dates and a specific threshold - something like:

[(01-19, x<6), (01-20, x<3)]

In which case that'd give me

A     |  B   |  C
01-19 |  5   |  0.34
01-19 |  3   |  0.25
01-20 |  2   |  0.36

My solution is to set up a multi index with A and B, but then I'm not entirely sure how to filter through the B's.

jezrael · Accepted Answer · 2019-11-12 07:38:32Z

5

Idea is create dictionary of tresholds for all values of A column, then Series.map to new Series, so possible compare by B column and filter by boolean indexing:

d = {'01-19': 6, '01-20' : 3}
df = df[df['B'] < df['A'].map(d)]
print (df)
       A  B     C
0  01-19  5  0.34
1  01-19  3  0.25
4  01-20  2  0.36

Detail:

print (df['A'].map(d))
0    6
1    6
2    6
3    3
4    3
Name: A, dtype: int64

answered Nov 12, 2019 at 7:38

jezrael

868k103 gold badges1.4k silver badges1.3k bronze badges

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

3 Comments

jezrael Over a year ago

@AlexanderCécile - I always want create the more general solutions, so my solution working if many another values in A - only is necessary expand dictionary by this new values.

AMC Over a year ago

Yeah I understood what you were doing on second glance, that’s why I deleted my previous comment. Really useful trick, I’ll have to remember that. Although I don’t see what it has to do with the size of A.

Umar.H Over a year ago

This is a smart use of map and dicts nice one @Jezrael

Andrei Berenda · Accepted Answer · 2019-11-12 07:52:12Z

0

If only two conditional you can you "and" and "or" operators like that:

df = df[((df['A'] == '01-19') & (df['B'] < 6)) | ((df['A'] == '01-20') & (df['B'] < 3))]

answered Nov 12, 2019 at 7:52

Andrei Berenda

1,9862 gold badges15 silver badges30 bronze badges

Collectives™ on Stack Overflow

Conditional filtering on multiple columns for a pandas dataframe

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related