Conditionally drop Pandas Dataframe row

user_7445528 0
Viewed 119

I wish to drop rows where the rows just before and just after has the same value for the column num2. My dataframe looks like this:

import pandas as pd

df = pd.DataFrame([
    [12, 10],
    [11, 10],
    [13, 10],
    [42, 11],
    [4, 11],
    [5, 2]
], columns=["num1", "num2"]
)

And this is my target:

df = pd.DataFrame([
    [12, 10],
    [13, 10],
    [42, 11],
    [4, 11],
    [5, 2]
], columns=["num1", "num2"]
)

What I have tried:

df["num1_diff"] = df["num2"].diff().fillna(0).astype(int)
filt = df["num1_diff"].apply(lambda x: x == 0)
print(df[filt])

Giving:

   num1  num2  num1_diff
0    12    10          0
1    11    10          0
2    13    10          0
4     4    11          0

And I was thinking to use the new num1_diff column to do the filtering. Is this a good approach, or is there perhaps a better one?

pandas dataframe python-3.x python
3 Answers

Use Series.shift twice, and check where num2 equals:

df[df['num2'].shift().ne(df['num2'].shift(-1))]

   num1  num2
0    12    10
2    13    10
3    42    11
4     4    11
5     5     2
user_90812670

IIUC,

df.loc[df['num2'].diff() != df['num2'].diff(-1)]

Output

   num1  num2
0    12    10
2    13    10
3    42    11
4     4    11
5     5     2

if you need all three to match:

df.loc[df['num2'].diff().bfill().rolling(3, center=True).sum().eq(0)]
user_118842370

Just in case you only want to drop rows where the rows just before and just after AND the current row have the same value for the column num2, use :

df[~(df['num2'].eq(df['num2'].shift()) & df['num2'].eq(df['num2'].shift(-1)))]

Here is an example :

   num1  num2
0    12    10
1    11    10
2    13    10
3     1    26
4     2     7  # <---- Do you want to drop this value ? if yes, consider Erfan 's solution
5     3    26       # if you want to keep it, I proposed another solution b)

import pandas as pd

df = pd.DataFrame([
    [12, 10],
    [11, 10],
    [13, 10],
    [1, 26],
    [2, 7],
    [3, 26]
], columns=["num1", "num2"]
)
a = df[df['num2'].shift().ne(df['num2'].shift(-1))] # Erfan 's solution
b = df[~(df['num2'].eq(df['num2'].shift()) & df['num2'].eq(df['num2'].shift(-1)))]

print(a)
print(b)

Output :

# a
   num1  num2
0    12    10
2    13    10
3     1    26
4     2     7
5     3    26

# b
   num1  num2
0    12    10
2    13    10
3     1    26
5     3    26
user_124034670
Related