Drop row if string is not equal to value - pandas

user_9029949 0
Viewed 2651

I'm hoping to drop rows if the string after a specific value is not equal to a list of value. Specifically, if the subsequent row after either 'Up' or 'Left' is not followed by 'Right' or 'Down' then I'm aiming to drop those rows.

import pandas as pd

df = pd.DataFrame({      
    'Label' : ['A','B','A','B','B','B','A','B','B','A','A','A'],   
    'Item' : ['X','Left','X','Left','Down','Right','Up','Y','Right','Y','Right','Up'],      
    })

df1 = df[~(df['Item'].isin(['Up','Left']).shift(1)) & (df['Item'].isin(['Right','Down']))]

   Label   Item
0      A      X
1      B   Left
2      A      X # Drop. Not Right/Down
3      B   Left
4      B   Down # Keep. Right/Down
5      B  Right
6      A     Up
7      B      Y # Drop. Not Right/Down
8      B  Right
9      A      Y
10     A  Right
11     A     Up

intended output:

   Label   Item
0      A      X
1      B   Left
3      B   Left
4      B   Down
5      B  Right
6      A     Up
8      B  Right
9      A      Y
10     A  Right
11     A     Up
pandas python
4 Answers

Your code is fine, it's just missing the not in the second condition

if the subsequent row after either 'Up' or 'Left' is not followed by 'Right' or 'Down' then I'm aiming to drop those rows.

So it should be like this:

df1 = df[~(((df['Item'].isin(['Up','Left']).shift(1)) & ~(df['Item'].isin(['Right','Down']))))]
user_155921620

I modified the mask. It'll solve your problem now -

import pandas as pd
import numpy as np
df = pd.DataFrame({      
    'Label' : ['A','B','A','B','B','B','A','B','B','A','A','A'],   
    'Item' : ['X','Left','X','Left','Down','Right','Up','Y','Right','Y','Right','Up'],      
    })

mask = (~(df['Item'].isin(['Up','Left']) & (~df['Item'].shift(-1).isin(['Right','Down',np.NAN]))))
df1 = df[mask.shift(1,fill_value=True)]
print(df1)

output -

   Label   Item
0      A      X
1      B   Left
3      B   Left
4      B   Down
5      B  Right
6      A     Up
8      B  Right
9      A      Y
10     A  Right
11     A     Up
user_154380330

First create a left up mask determining the index of Left and Up value in column Item.

Then shift the left up mask to get right down mask determining the location we want to check.

lu_mask = (df['Item'] == 'Left') | (df['Item'] == 'Up')
rd_mask = lu_mask.shift(1).fillna(False)

Moreover, use right down mask to check if the row is Right or Down.

index_to_drop = ((df.loc[rd_mask, 'Item'] != 'Right') & (df.loc[rd_mask, 'Item'] != 'Down')).loc[lambda s: s == True]

At last, drop the row.

df_ = df[~df.index.isin(index_to_drop.index)]

# print(df_)

   Label   Item
0      A      X
1      B   Left
3      B   Left
4      B   Down
5      B  Right
6      A     Up
8      B  Right
9      A      Y
10     A  Right
11     A     Up
user_103151630

I think here is simplier solution like accepted answer:

Your solution change by chain by | for bitwise OR and first shifting values and then compare:

df2 = df[df['Item'].isin(['Right','Down']) | ~df['Item'].shift().isin(['Up','Left'])]
print (df2)
   Label   Item
0      A      X
1      B   Left
3      B   Left
4      B   Down
5      B  Right
6      A     Up
8      B  Right
9      A      Y
10     A  Right
11     A     Up
user_29010020
Related