Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

if-else condition in pandas DataFrame referencing two rows

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.

like image 489
laszlopanaflex Avatar asked Dec 31 '22 16:12

laszlopanaflex


1 Answers

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)
like image 95
liakoyras Avatar answered Feb 23 '23 08:02

liakoyras