CODEWITHSUNDEEP
pythonpandas
Aman Singh
Aman Singh

Reputation: 1241

How to check if next 3 consecutive rows in pandas column have same value?

I have a pandas dataframe with 3 columns - id, date and value.

| id | date | value |
| --- | --- | --- |
| 1001 | 1-04-2021 | 61 |
| 1001 | 3-04-2021 | 61 |
| 1001 | 10-04-2021 | 61 |
| 1002 | 11-04-2021 | 13 |
| 1002 | 12-04-2021 | 12 |
| 1015 | 18-04-2021 | 42 |
| 1015 | 20-04-2021 | 42 |
| 1015 | 21-04-2021 | 43 |
| 2001 | 8-04-2021 | 27 |
| 2001 | 11-04-2021 | 27 |
| 2001 | 12-04-2021 | 27 |
| 2001 | 27-04-2021 | 27 |
| 2001 | 29-04-2021 | 27 |

I want to check how many rows are there for each id where the next 3 or more than 3 next consecutive rows having the same value in value column? Once identified that the next 3 or more consecutive rows are having the same value, flag them as 1 in a separate column else 0.

So the final dataframe would look like the following,

| id | date | value | pattern
| --- | --- | --- | --- |
| 1001 | 1-04-2021 | 61 | 1 |
| 1001 | 3-04-2021 | 61 | 1 |
| 1001 | 10-04-2021 | 61 | 1 |
| 1002 | 11-04-2021 | 13 | 0 |
| 1002 | 12-04-2021 | 12 | 0 |
| 1015 | 18-04-2021 | 42 | 0 |
| 1015 | 20-04-2021 | 42 | 0 |
| 1015 | 21-04-2021 | 43 | 0 |
| 2001 | 8-04-2021 | 27 | 1 |
| 2001 | 11-04-2021 | 27 | 1 |
| 2001 | 12-04-2021 | 27 | 1 |
| 2001 | 27-04-2021 | 27 | 1 |
| 2001 | 29-04-2021 | 27 | 1 |

Upvotes: 1

Views: 2113

Answers (2)

SomeDude
SomeDude

Reputation: 14238

How about this:

def f(x):
    x = x.fillna(0)
    y = len(x)*[0]
    for i in range(len(x)-3):
        if x[i+1] == 0 and x[i+2] == 0:
            y[i] = 1
            y[i+1] = 1
            y[i+2] = 1
    if x[len(x)-1] == 0 and x[len(x)-2] == 0 and x[len(x)-3] == 0:
        y[len(x)-1] = 1
    return pd.Series(y)

df['value'].diff().transform(f)

Upvotes: 0

Quang Hoang
Quang Hoang

Reputation: 150745

Try with groupby:

df['pattern'] = (df.groupby(['id', df['value'].diff().ne(0).cumsum()])
                   ['id'].transform('size').ge(3).astype(int)
                )

Upvotes: 4

Related Questions