conditional replace based off prior value in same

问题:

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]

回答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

回答2:

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)