Pandas dataframe if else condition based on previous rows not working

Question

I have a pandas dataframe as below:

df = pd.DataFrame({'X':[1,1,1, 0, 0]})
df

    X
0   1
1   1
2   1
3   0
4   0

Now I want to modify X based on the below condition:

If X = 0 , previous row + 1 So, my final output should look like below:

This can be achieved by iterating over rows and setting up a current and previous row and using iloc and is working as expected

for i in range(0, len(df)):
    current_row = df.iloc[i]
    if i > 0:
        previous_row =df.iloc[i-1]
    else:
        previous_row = current_row
    if (current_row['X'] == 0):
        current_row['X']  = previous_row['X'] +1

I want more efficient way of doing that and I tried the below code but the output is not what I expected (the value of X for 5th row should be 3):

conditions = [df["X"] == 0]
values = [df["X"] .shift() + 1]
df['X'] = np.select(conditions, values)

>>> df
     X
0  1
1  1
2  1
3  2
4  1

It is giving me the same result

Shanoo
– Shanoo

2019-10-15 20:19:54 +00:00
Commented Oct 15, 2019 at 20:19 — Shanoo
– Shanoo, Commented Oct 15, 2019 at 20:19

Dani Mesejo · Accepted Answer · 2019-10-15 21:45:24Z

1

You could try the following:

import numpy as np
import pandas as pd

df = pd.DataFrame({'X': [1, 1, 1, 0, 0]})

# values previous to zero
pe_zero = df.X.shift(-1).eq(0) * df.X  # [0 0 1 0 0]

# 1 for reach zero value as you sum one to the previous value
eq_zero = df.X.eq(0)

# find consecutive groups of 0
groups = pe_zero + eq_zero
consecutive = (groups.gt(0) != groups.gt(0).shift()).cumsum()

# find cumulative sum by groups
cumulative = groups.groupby(consecutive).cumsum()

# choose from cumulative when equals to zero else from original
result = np.where(eq_zero, cumulative, df.X)

print(result)

Output

[1 1 1 2 3]

UPDATE

For df = pd.DataFrame({'X': [1, 1, 1, 0, 0, 1, 1, 0, 0]}) returns:

[1 1 1 2 3 1 1 2 3]

edited Oct 15, 2019 at 21:45

answered Oct 15, 2019 at 20:48

Dani Mesejo

62.2k6 gold badges56 silver badges86 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Shanoo Over a year ago

But this doesnot work if I have a dataframe like this : df = pd.DataFrame({'X':[1,1,1, 0, 0, 1, 1, 0, 0]})

Dani Mesejo Over a year ago

@Shanoo Updated the answer!

josemz · Accepted Answer · 2019-10-15 20:53:35Z

0

You could try this:

arr = df.X.values # extract the column as a numpy array for faster iteration
for i, val in enumerate(arr[1:], start=1):
    if val == 0:
        arr[i] = arr[i-1] + 1

edited Oct 15, 2019 at 20:53

answered Oct 15, 2019 at 20:45

josemz

1,3128 silver badges15 bronze badges

Collectives™ on Stack Overflow

Pandas dataframe if else condition based on previous rows not working

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related