0

I have a big data set where I'm trying to filter only the rows that match certain criteria. More specifically, I want to get all rows where Type == A if Type == B is 2

So in the following example it would result in the row 2 Node-1 A 1

>>> import pandas as pd
>>> data = [['Node-0', 'A', 1],['Node-0', 'B', 1],['Node-1','A', 1],['Node-1', 'B', 2]]
>>> df = pd.DataFrame(data,columns=['Node','Type','Value'])
>>> print df
     Node Type  Value
0  Node-0    A     1
1  Node-0    B     1
2  Node-1    A     1
3  Node-1    B     2

I can filter the rows using df.loc[df['Type'] == 'A'], but that gives me lines 0 and 2.

Improve this question
1
  • 1
    Type == A if Type == B is 2 does not make sense. Can you phrase it correctly? How can Type contain both A and B ? Do you want Value to be 2? Commented Jun 11, 2018 at 15:59

3 Answers 3

Reset to default
1

IIUC, using some masking with groupby.

m = df.Type.eq('B') & df.Value.eq(2)
df[m.groupby(df.Node).transform('any') & df.Type.eq('A')]

     Node Type  Value
2  Node-1    A      1
Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

0

I bet there is a better solution, but this should sort it out for time being:

condition1 = (df['Node'].isin(df.query("Type=='B' & Value==2")['Node']))
#All the 'Node' values whose 'Type' and 'Value' columns have values 'B' and 2
#.isin() filters to rows that match the above criteria

condition2 = (df['Type']=='A')
#all the rows where 'Type' is 'A'

df.loc[condition1&condition2]
#intersection of above conditions    

#     Node Type  Value
#2  Node-1    A      1
Improve this answer

2 Comments

I'd separate those conditions. This is hard to read (my opinion).
@AntonvBR I'm not the best with descriptions, but did try :)
0

Consider the following:

# Get rows maching first criteria
dd1 = df[df.Type == 'A'][df.Value == 1]

# Get "previous" rows maching second criteria
df2 = df.shift(-1)
dd2 = df2[df2.Type == 'B'][df2.Value == 2]

# Find intersection
pd.merge(dd1, dd2, how='inner', on='Node')

Result:

     Node Type_x  Value_x Type_y  Value_y
0  Node-1      A        1      B      2.0
Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.