Assign values in dataframe group using previous group row

user_12716774 0
Viewed 92

Consider the following dataframe:

A |  B |  C
_____________
a |  1 |  1
a |  5 |  NaN
b |  3 |  1
b |  4 |  NaN
c |  2 |  1
c |  2 |  NaN
a |  1 |  NaN
b |  3 |  NaN
c |  4 |  NaN

My goal is to update column C based on a rule that also includes the previous row, for each group. Just as an example, if the value from B column is smaller than the previous one, the C should have a value of 0, otherwise keep the value from the previous C.

So this would give me the following:

A |  B |  C
_____________
a |  1 |  1
a |  5 |  1
b |  3 |  1
b |  4 |  1
c |  2 |  1
c |  2 |  1
a |  1 |  0
b |  3 |  0
c |  4 |  1

I was thinking of using a kind of

df.groupby(A).apply(lambda x: x['C'].shift(1) if x['B'].shift(1) >= x['B'] else 0)

but obviously this does not work as apply cannot access former rows ( I think)

If all fails, I would build individual DF's from each group and modify them individually, so not to include another group's rows in the result, but I believe there must be a more elegant solution using the original dataframe.

Any suggestions?

pandas group-by python
1 Answers

Try:

import numpy as np
def fill(x):
    x['C'] = x['C'].fillna(method='ffill')
    x['C'] = np.where(x['B'].values <= x['B'].shift(1).values, 0, x['C'])
    return x
df = df.groupby('A').apply(fill)

Here, the idea is to 1st fill the NAN values with the previous value then replace the value with 0 if the condition is satisfied.

user_154380330
Related