how to select rows with a certain pattern

user_15369052 0
Viewed 59

I'm stuck in a problem, because I can't find any solution to deal with it, I have the following sample:

data = [['John', 6, 'A'], ['Paul', 6, 'D'],
        ['Juli', 9, 'D'], ['Geeta', 4, 'A'],
        ['Jay', 6, 'D'], ['Sara', 6, 'A'],
        ['Mario', 3, 'D'], ['Peter', 6, 'A'],
        ['Jin', 6, 'D'], ['Carl', 6, 'A']]

df = pd.DataFrame(data, columns=['Name', 'Number', 'Label'])

I previously grouped by number with the following line of code:

df = df.sort_values('number')

and got this output:

Name Number Label

Mario   3   D
Geeta   4   A
Peter   4   A
Jin     4   D
John    6   A
Paul    6   D
Jay     6   D
Sara    6   A
Carl    6   A
Juli    9   D

So I just want to select pair of rows which have an 'A' in the last column and followed by a row with a 'D' in the last column, and find all pair of rows that match this pattern in the same group (I don't want the last 'A' of a group and the 'D' of the next group), so the solution of the problem is:

Name Number Label
Peter  4      A
Jin    4      D
John   6      A
Paul   6      D

Anyone can help me?

pandas python
1 Answers

You need to use:

# is the row label A?
m1 = df['Label'].eq('A')
# id the next row label D?
m2 = df['Label'].shift(-1).eq('D')
# create a mask combining both conditions
mask = m1&m2

# select the matching rows and the next one (boolean OR)
df[mask|mask.shift()]

output:

    Name  Number Label
0   John       6     A
1   Paul       6     D
3  Geeta       4     A
4    Jay       6     D

update: match on group

as your rows are sorted per group you can add another condition:

m1 = df['Label'].eq('A')
m2 = df['Label'].shift(-1).eq('D')
m3 = df['Number'].eq(df['Number'].shift(-1))
mask = m1&m2&m3

df[df[mask|mask.shift()]]

output:

    Name  Number Label
2  Peter       4     A
3    Jin       4     D
4   John       6     A
5   Paul       6     D
user_163434640
Related