conditional replace based off prior value in same column of pandas dataframe python

Question

Feel like I've looked just about everywhere and I know its probably something very simple. I'm working with a pandas dataframe and looking to fill/replace data in one of the columns based on data from that SAME column. I'm typically more of an excel user and it is sooo simple in excel. If we have:

df = pd.DataFrame([0, -1, -1, -1, 0 , 0, 0, 1, 0])
df.columns = ['A']
df['B'] = df['A']

in excel what I'm trying to do would be " =IF(AND(A2=0, B1=-1), -1, A2) so that I could then drag down column 'B' and that would apply. In essence, based on the prior data point of column B, and the current value of column A, I need to update the current value of B.

I've tried:

df['B'] = np.where((df['A'] == 0), (df['B'].shift(1) == -1),
                   df['B'].replace(to_value = 0, method = 'ffill'), df['A'])

and lots of other version of that, as well as variations of iterrows and other incredibly extreme work-arounds with no avail.

Any suggestions are greatly appreciated.

EDIT:

the result would be:

df['B'] = [0, -1, -1, -1, -1 , -1, -1, 1, 0]

In your problem a particular value in the solution could depend on many different values of the original series not just the previous value. So loops like mishaF's solution are probably the way to go. In your attempt at the values of df['B'] are replaced all at once at the end not one-by-one. — rhaskett
– rhaskett, Commented Jun 16, 2015 at 20:56

mishaF · Accepted Answer · 2015-06-16 20:52:37Z

1

Here's a kind of brute-force method. There is probably something more elegant, but you could explicitly loop over the rows like this:

df = pd.DataFrame([0, -1, -1, -1, 0 , 0, 0, 1, 0])
df.columns = ['A']
df['B'] = df['A']

# loop here
for i in range(1,len(df)):
     if df.A[i] == 0 and df.B[i-1] == -1:
             df.B[i] = -1
     else:
             df.B[i] = df.A[i]

This gives you the result you seek:

>>> df['B']
0    0
1   -1
2   -1
3   -1
4   -1
5   -1
6   -1
7    1
8    0

answered Jun 16, 2015 at 20:52

mishaF

8,39410 gold badges34 silver badges34 bronze badges

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

Comments

steboc · Accepted Answer · 2015-06-17 09:30:14Z

0

using Where

df['B'] = df.A[0:len(df.A)-1].where((df.A==0 ) & (df.B.shift(-1)==-1),-1)
df['B'] = df['B'].fillna(df.A)

answered Jun 17, 2015 at 9:30

steboc

1,1811 gold badge7 silver badges18 bronze badges

Collectives™ on Stack Overflow

conditional replace based off prior value in same column of pandas dataframe python

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related