CODEWITHSUNDEEP
pythonpandasnumpy
roberto swiss
roberto swiss

Reputation: 155

Count Positive and Negative Consecutive Elements in a Dataframe based on condition

I have a dataframe like this. 

import pandas as pd

df = pd.DataFrame({
    'col': ['neutral', 'neutral', 'neutral', 'positive', 'positive', 'negative', 'neutral']})

Now I want to update 'col' such that i get a true when there is a change from positive to negative or the other way round and a false otherwise (ie consecutive equal values) and also a false when there is a change from positive/negative to neutral and the other way round.

I have tried several methods with grouby and transform but nothing works.

My desired output would be: 

df = pd.DataFrame({
    'col': ['False', 'False', 'False', 'False', 'False', 'True', 'False']})

Upvotes: 2

Views: 106

Answers (2)

d_kennetz
d_kennetz

Reputation: 5359

A long expression that generates booleans based on your conditions:

(((df['col'] == 'positive') & (df['col'].shift(1) == 'negative')) | ((df['col'] == 'negative') & (df['col'].shift(1) == 'positive')))

This uses bitwise operators to say if the current position is positive and the one after is negative, True, or The current position is negative and the following is positive, True else return false.

>>> (((df['col'] == 'positive') & (df['col'].shift(1) == 'negative')) | ((df['col'] == 'negative') & (df['col'].shift(1) == 'positive')))
0    False
1    False
2    False
3    False
4    False
5     True
6    False
Name: col, dtype: bool

Either both of the first conditions must be met to return true, or both of the second conditions must be met to return true.

Upvotes: 0

user3483203
user3483203

Reputation: 51155

map + diff

Map positive and negative to 1 and -1 respectively, and neutral to 0. When you take the difference of the series, the absolute value of the output every time positive and negative follow each other will be 2, which you can easily check for, resulting in your desired outcome:

d = {'positive': 1, 'negative': -1, 'neutral': 0}

df.col.map(d).diff().abs().eq(2)


0    False
1    False
2    False
3    False
4    False
5     True
6    False
Name: col, dtype: bool

Upvotes: 1

Related Questions