Pandas: Filter values present on either end of pairs but not both within group

user_15492238 0
Viewed 83
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 numbers 2 AND 4 being present at either end(not both), both ends, and at the end of the pairs.

In group 1 for example, only (4,1) will be kept while (2,2) and (2,4) will be filtered out. Excepted Output:

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

If I understood correctly, you can use the following snippet. It creates the pairs, checks the conditions, and slice the dataframe based on the conditions.

codes = df[['Code']].assign(Code2=df.groupby('Group')['Code'].shift(-1))

cond = codes.apply(lambda x: (4 in x.values or 2 in x.values) # 2 or 4
                              and set(x)!={2,4}               # but not both
                              and len(set(x))>1               # and another value
                   , axis=1)

df[cond|cond.shift()]

alternatively:

codes = df[['Code']].assign(Code2=df.groupby('Group')['Code'].shift(-1))

cond = codes.apply(lambda x: len(set(x)) == 2  # 2 values initially
                             and len(set(x).difference([2,4]))==1 # remains exactly 1 after dropping both 2 and 4
                   , axis=1)

df[cond|cond.shift()]

output:

   Group  Code
2      1     4
3      1     1
4      2     4
5      2     1
6      2     2
7      2     3

NB. you have several conditions, I used the "either end (exclusive)" one here

user_163434640

Another solution:

tmp = pd.DataFrame(zip(df["Code"], df.groupby("Group").Code.shift(-1)))
mask = tmp.isin([2, 4]).sum(1).eq(1) & tmp.notna().all(1)
print(df[mask | mask.shift()])

Prints:

   Group  Code
2      1     4
3      1     1
4      2     4
5      2     1
6      2     2
7      2     3
user_100359850

You can also approach by making 2 boolean masks for current row and next row code in 2 or 4. Then, form the required combination condition of present at either end(not both), as follows:

m_curr = df['Code'].isin([2,4])                              # current row code is 2 or 4
m_next = df.groupby("Group")['Code'].shift(-1).isin([2,4])   # next row code in same group is 2 or 4

# (current row or next row code in 2 or 4) AND NOT (both current row and next row code in 2 or 4)
mask = (m_curr | m_next) & ~(m_curr & m_next) 

df[mask | mask.shift()]

Result:

   Group  Code
2      1     4
3      1     1
4      2     4
5      2     1
6      2     2
7      2     3
user_150706970
Related