I have an example where I need to populate a data frame column according to an if-else condition, which references the current row as well as the preceding row. Here is the example data set:
time = pd.Series(pd.date_range(start='20140101', end='20190901', freq='Q').astype('period[Q]'), name='time')
results = pd.Series(['0','W','W','W','0','0','L','L','L','L','W','W','W','0','0','W','W','W','0','L','L','0'], name='result')
df = pd.concat([time, results], axis=1)
I want to create a column, df['last win']
, which contains the value of time
for that current row if it is W
, or the last time
, which had a W
. So, the desired output would be:
time result last_win
0 2014Q1 0 NaT
1 2014Q2 W 2014Q2
2 2014Q3 W 2014Q3
3 2014Q4 W 2014Q4
4 2015Q1 0 2014Q4
5 2015Q2 0 2014Q4
6 2015Q3 L 2014Q4
7 2015Q4 L 2014Q4
8 2016Q1 L 2014Q4
9 2016Q2 L 2014Q4
10 2016Q3 W 2016Q3
11 2016Q4 W 2016Q4
12 2017Q1 W 2017Q1
13 2017Q2 0 2017Q1
14 2017Q3 0 2017Q1
15 2017Q4 W 2017Q4
16 2018Q1 W 2018Q1
17 2018Q2 W 2018Q2
18 2018Q3 0 2018Q2
19 2018Q4 L 2018Q2
20 2019Q1 L 2018Q2
21 2019Q2 0 2018Q2
I am not sure how to use the .apply
, or maybe the .shift
feature in a way that works conditionally.
Use where
and then fillna
with forward-fill specified to fill the loses and ties:
df['last_win'] = np.where(df['result'] == 'W', df['time'], np.NaN)
df.fillna(method="ffill", inplace=True)
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With