I have the following pandas dataframe:
Circuit-ID DATETIME LATE?
78899 07/06/2018 15:30 1
78899 08/06/2018 17:30 0
78899 09/06/2018 20:30 1
23544 12/07/2017 23:30 1
23544 13/07/2017 19:30 0
23544 14/07/2017 20:30 1
And I need to calculate the shifted value for the DATETIME and LATE? columns to get the following result:
Circuit DATETIME LATE? DATETIME-1 LATE-1
78899 07/06/2018 15:30 1 NA NA
78899 08/06/2018 17:30 0 07/06/2018 15:30 1
78899 09/06/2018 20:30 1 08/06/2018 17:30 0
23544 12/07/2017 23:30 1 NA NA
23544 13/07/2017 19:30 0 12/07/2017 23:30 1
23544 14/07/2017 20:30 1 13/07/2017 19:30 0
I tried the following code :
df.groupby(['circuit ID, DATETILE', LATE? ]) \
.apply(lambda x : x.sort_values(by=['circuit ID, 'DATETILE', 'LATE?'], ascending = [True, True, True]))['LATE?'] \
.transform(lambda x:x.shift()) \
.reset_index(name= 'LATE-1')
But I keep getting erroneous results on some rows where the first shifted value is different from Nan. Could you please indicate a more clean way to get the desired result?
Use groupby and shift, then join it back:
df.join(df.groupby('Circuit-ID').shift().add_suffix('-1'))
Circuit-ID DATETIME LATE? DATETIME-1 LATE?-1
0 78899 07/06/2018 15:30 1 NaN NaN
1 78899 08/06/2018 17:30 0 07/06/2018 15:30 1.0
2 78899 09/06/2018 20:30 1 08/06/2018 17:30 0.0
3 23544 12/07/2017 23:30 1 NaN NaN
4 23544 13/07/2017 19:30 0 12/07/2017 23:30 1.0
5 23544 14/07/2017 20:30 1 13/07/2017 19:30 0.0
A similar solution uses concat for joining:
pd.concat([df, df.groupby('Circuit-ID').shift().add_suffix('-1')], axis=1)
Circuit-ID DATETIME LATE? DATETIME-1 LATE?-1
0 78899 07/06/2018 15:30 1 NaN NaN
1 78899 08/06/2018 17:30 0 07/06/2018 15:30 1.0
2 78899 09/06/2018 20:30 1 08/06/2018 17:30 0.0
3 23544 12/07/2017 23:30 1 NaN NaN
4 23544 13/07/2017 19:30 0 12/07/2017 23:30 1.0
5 23544 14/07/2017 20:30 1 13/07/2017 19:30 0.0