Problem using shift function to compare with previous row values

Question

This is my function:

def shift_turn(data):
    data['AUX'] = np.where(data['Status'] == 'Offline', '1', '0')
    data['AUX'] = np.where((data['DateTimeBegin corrected'] < data['CAP']) & (data['Status'] != 'Offline'), 0, data['AUX'])
    data['AUX'] = np.where((data.ShortLogin == data.ShortLogin.shift()) & (data.Status.shift() == 'Offline') & (data['DateTimeBegin corrected'] >= data['CAP']), 1, data['AUX'])
    data['AUX'] = np.where((data.ShortLogin == data.ShortLogin.shift()) & (data.AUX.shift() == 1), 1, data['AUX'])

    return data

And for this example I'm retrieving this;

CCMSID  ShortLogin  Status  DateTimeBegin corrected DateTimeEnd corrected   CAP AUX
38  3808806 ABCM    Offline 2021-10-11 00:00:00 2021-10-11 08:00:00 2021-10-12 05:00:00 1
39  3808806 ABCM    Meeting 2021-10-11 08:00:00 2021-10-11 09:25:00 2021-10-12 05:00:00 0
40  3808806 ABCM    Moderation Task 2021-10-11 09:25:00 2021-10-11 10:20:00 2021-10-12 05:00:00 0
41  3808806 ABCM    Rest    2021-10-11 10:20:00 2021-10-11 10:35:00 2021-10-12 05:00:00 0
42  3808806 ABCM    Moderation Task 2021-10-11 10:35:00 2021-10-11 11:35:00 2021-10-12 05:00:00 0
43  3808806 ABCM    Meeting 2021-10-11 11:35:00 2021-10-11 12:00:00 2021-10-12 05:00:00 0
44  3808806 ABCM    Meeting 2021-10-11 13:00:00 2021-10-11 15:10:00 2021-10-12 05:00:00 0
45  3808806 ABCM    Rest    2021-10-11 15:10:00 2021-10-11 15:20:00 2021-10-12 05:00:00 0
46  3808806 ABCM    Meeting 2021-10-11 15:20:00 2021-10-11 17:00:00 2021-10-12 05:00:00 0
47  3808806 ABCM    Offline 2021-10-11 17:00:00 2021-10-12 08:00:00 2021-10-12 05:00:00 1
48  3808806 ABCM    Meeting 2021-10-12 08:00:00 2021-10-12 09:20:00 2021-10-12 05:00:00 1
49  3808806 ABCM    Moderation Task 2021-10-12 09:20:00 2021-10-12 10:00:00 2021-10-12 05:00:00 1
50  3808806 ABCM    Rest    2021-10-12 10:00:00 2021-10-12 10:15:00 2021-10-12 05:00:00 0
51  3808806 ABCM    Moderation Task 2021-10-12 10:15:00 2021-10-12 10:18:00 2021-10-12 05:00:00 0
52  3808806 ABCM    Meeting 2021-10-12 10:18:00 2021-10-12 11:00:00 2021-10-12 05:00:00 0

The problem that I am having with my code is that in the lines 50, 51 and 52 I was expecting to retrieve 1 and not 0, in the column AUX, has it is showing.

data['AUX'] = np.where((data.ShortLogin == data.ShortLogin.shift()) & (data.AUX.shift() == 1), 1, data['AUX'])

I thought using this line, the code was going to see where ShortLogin is equal to previous row ShortLogin and AUX previous row equal 1, and if yes it would give 1 if not would do nothing.

Any suggestion to overcome this issue?

Also open to more efficient ways build up my function.

Thanks in advance!

Problem using shift function to compare with previous row values

0 Answers