Reputation:
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
.
Upvotes: 0
Views: 216
Reputation: 2265
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
Upvotes: 0
Reputation: 403278
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
Upvotes: 1
Reputation: 27899
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
Upvotes: 0