Forum Discussion
Conditional binning / summing of data
Hello,
I have a dataset with rows which represent periods of work.
I would like to group these periods of work based on a few conditions.
- The first row starts a period of 3 hours.
- If between the previous row end-date and the next row start date there is less than 1-hour, it is counted for the existing period
- If between the previous row end-date and the next row start date there is more than 1 hour and it starts outside of the initial 3 hours period, it is counted as a new period.
- In case the 'worktime' is less than 180 minutes in a period, the worker is eligible for 180 minutes
- In case the 'worktime' is over 180 minutes in a period, the worker is eligible for sum of 'worktime'.
I need to analyse how effective planning of workers is done, by analysing effective and uneffective time.
Example data including desired output:
I have no idea where to start. So I was hoping that you can help me out!