CODEWITHSUNDEEP
pythonpandas
user9518022
user9518022

Reputation:

Python pandas - get row based on previous row value

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

Answers (3)

Andrii Zarubin
Andrii Zarubin

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

cs95
cs95

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

zipa
zipa

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

Related Questions