CODEWITHSUNDEEP
pythonpandasdataframe
mananana
mananana

Reputation: 403

Pandas - new column based on other row

I want to create a new column in my dataframe with the value of a other row.
DataFrame

       TimeStamp  Event  Value
0  1603822620000    1    102.0
1  1603822680000    1    108.0
2  1603822740000    1    107.0
3  1603822800000    2      1
4  1603823040000    1    106.0
5  1603823100000    2      0
6  1603823160000    2      1
7  1603823220000    1    105.0

I would like to add a new column with the previous value where event = 1.

       TimeStamp  Event  Value  PrevValue
0  1603822620000    1    102.0     NaN
1  1603822680000    1    108.0    102.0
2  1603822740000    1    107.0    108.0
3  1603822800000    2      1      107.0
4  1603823040000    1    106.0    107.0
5  1603823100000    2      0      106.0
6  1603823160000    2      1      106.0
7  1603823220000    1    105.0    106.0

So I can't simply use shift(1) and also not groupBy(event).shift(1).

Current solution

df["PrevValue"] =df.timestamp.apply(lambda ts: (df[(df.Event == 1) & (df.timestamp < ts)].iloc[-1].value))

But I guess, that's not the best solution.
Is there something like shiftUntilCondition(condition)?

Thanks a lot!

Upvotes: 1

Views: 43

Answers (1)

BENY
BENY

Reputation: 323366

Try with

df['new'] = df['Value'].where(df['Event']==1).ffill().shift()
Out[83]: 
0      NaN
1    102.0
2    108.0
3    107.0
4    107.0
5    106.0
6    106.0
7    106.0
Name: Value, dtype: float64

Upvotes: 1

Related Questions