Label data rows based on column values & calculate measures for each label group
Hi everyone,
I hope that you can help me with the following problem.
I have a table which lists recordings of technical data for motorbikes (e.g. speed, total cumulated distance, light on/off) over time.
These recordings represent trips, whereby a trip is defined as the engine was turned on and then off. There are 2 kind of trips. Trip A: The motorbike was actually driven (you have speed & distance recordings). Trip B: The motorbike engine was just turned on and off, without driving the bike.
I would like to do the following:
Identify all trips in this list of recordings (e.g. by attaching labels in a new column)
Calculated the time of each trip (time between turning engine on & off). If Trip B: Also calculate the distance it was driven
Identify the trips where the light was turned on and calculate the time for how long the light was on for each trip (Light on = 1; Light off = 0)
The two events (engine on/off) are defined as follows:
Engine On:
Counter: 1
Status: Empty
Total cumulated distance: 0
While the engine is on, the Counter will keep going up.
If the bike is actually driven (Trip A), the total cumulated distance will also increase.
If the bike was not driven (Trip B), the total cumulated distance should stay the same.
Engine Off:
Status: 1
After turning off the engine, the Counter goes down to 1, Status switches to Empty and the distance to 0.
The next trip starts again with:
Counter: 1
Status: Empty
Total cumulated distance: 0
I was thinking about using a Rule Engine to attach labels in a new column (Trip 1, 2, 3, …). And then group over these label groups (trips) to calculate the driven distance and the time the light was on, but I cannot get it to work. Maybe this is a tasks for loops, but unfortunately I have never worked with them before.
Any help would be much appreciated :slight_smile:
Best,
Eren
ps: Example data is attached