Filter based on pairs within a group - if value represent at the end

user_15492238 0
Viewed 39
Group Code
 1     2
 1     2
 1     4
 1     1
 2     4 
 2     1
 2     2 
 2     3
 2     1
 2     1
 2     3

Within each group there are pairs. In Group 1 for example; the pairs are (2,2),(2,4),(4,1)

I want to filter these pairs based on code number 2 OR 4 being present at the END of the pair. In group 1 for example, only (2,2) and (2,4) will be kept while (4,1) will be filtered out.

The code am I using for determining code number being present at the beginning is

  df[df.groupby("Group")['Code'].shift().isin([2,4])|df['Code'].isin([2,4])]

Excepted Output:

Group Code
 1     2
 1     2
 1     4
 2     1 
 2     2
pandas dataframe filter numpy python
2 Answers

Using your own suggested code, you can modify it to achieve your goal:

idx = df.groupby("Group")['Code'].shift(-1).isin([2,4])
df[idx | idx.shift()]

First you groupby 'Group' and then shift one up and check for values 2 or 4. Finally, you want both the end of pairs satisfying the condition (i.e. idx) and the begin of the pair (i.e. idx.shift())

output:

    Group   Code
0   1   2
1   1   2
2   1   4
5   2   1
6   2   2
user_49759810

Assuming the data is sorted by Group, you can also do it without using groupby() to save some processing and speed up the process, as follows:

m = df['Code'].isin([2,4]) & df['Group'].eq(df['Group'].shift())
df[m | m.shift(-1)]

Result:

   Group  Code
0      1     2
1      1     2
2      1     4
5      2     1
6      2     2
user_150706970
Related