python pandas groupby eventType and eventId and get date diff between each eventType

Question

My data format is the following:

    id  eventSource        eventType                           timestamp
0  102    sensor1          received                   2021-10-06 13:45:51
1  102    sensor1          startingCalculation        2021-10-06 13:45:55
2  102    sensor1          endedCalculation           2021-10-06 13:46:00
3  103    sensor2          received                   2021-10-06 13:46:00
4  103    sensor2          startingCalculation        2021-10-06 13:46:04
5  103    sensor2          endedCalculation           2021-10-06 13:46:10

I need help with the following task:

I want to group by the Id eventSource and eventType and show diffs between the received and endedCalculation events for each of them like so:

    id   eventSource         diff 
0  102      sensor1           9
0  103      sensor2           10

And so on ...

I am kind of stuck in the groupby stage,

Thank you for anyone trying to help

Anurag Dabas · Accepted Answer · 2021-06-10T14:52:22.013

1

try:

m=df['eventType'].isin(['received','endedCalculation'])
#created a boolean mask

Finally pass that mask and use groupby() and agg() method:

out=df[m]
out['diff']=out.groupby(['id','eventSource'])['timestamp'].diff().dt.total_seconds()
out=out.dropna(subset=['diff']).drop('eventType',1).reset_index(drop=True)

output of out:

    id  eventSource     timestamp               diff
0   102     sensor1     2021-10-06 13:46:00     9.0
1   103     sensor2     2021-10-06 13:46:10     10.0

edited Jun 10 '21 at 14:52

answered Jun 10 '21 at 14:13

Anurag Dabas

23,866
9
21
41

Hey, I tried your solution and i get the error : "Function does not reduce" also, i originally have another field that i group by, is it the reason I get the error ? – Eitank Jun 10 '21 at 14:23
yes if you have added other field in groupby that may caused the error – Anurag Dabas Jun 10 '21 at 14:25
ok so I have 2 fields that I group by, what do I do to make it work ? – Eitank Jun 10 '21 at 14:25
I thought that it wouldn't matter, but one sec let me edit my question – Eitank Jun 10 '21 at 14:29
@Eitank Updated answer...Kindly have a look **:)** – Anurag Dabas Jun 10 '21 at 14:39
updated answer friend kindly have a look **:)** – Anurag Dabas Jun 10 '21 at 14:52
Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/233619/discussion-between-eitank-and-anurag-dabas). – Eitank Jun 10 '21 at 14:53

python pandas groupby eventType and eventId and get date diff between each eventType

1 Answers1