'Group Data by increasing but recurring values in Dataframe
I have a large amount of data that I need to group by an increasing and recurring column.
The data looks a little something like this:
No. Date Fiducial# Offset
0 219 2022-05-13 08:19:46 1091 0
1 220 2022-05-13 08:19:46 1091 0
2 221 2022-05-13 08:19:46 1091 0
3 222 2022-05-13 08:19:47 1092 46
4 223 2022-05-13 08:19:47 1092 46
5 224 2022-05-13 08:19:47 1092 46
6 225 2022-05-13 08:19:48 1093 47
7 226 2022-05-13 08:19:48 1093 47
8 227 2022-05-13 08:19:48 1093 47
... ... ... ... // at some point this repeats and starts at 0 again
49 267 2022-05-13 08:20:10 1107 0
As you can see, the Offset column is the one that is increasing. The blocks I need always start at 0 however the numbers between them are completly random but always increasing. There is also no way to know how many parts are in each block.
So far I have tried to simply group by Fiducial# which also increases with each subset (in this case 3 entries at a time). This works reasonably well but I was wondering if there is a better solution.
I then tried something along the lines of this post but there is a problem. This creates three groups for the first 3 times a 0 is in the set.
No. Date Fiducial# Offset
0 219 2022-05-13 08:19:46 1091 0
=======
No. Date Fiducial# Offset
1 220 2022-05-13 08:19:46 1091 0
=======
No. Date Fiducial# Offset
2 221 2022-05-13 08:19:46 1091 0
3 222 2022-05-13 08:19:47 1092 46
4 223 2022-05-13 08:19:47 1092 46
5 224 2022-05-13 08:19:47 1092 46
6 225 2022-05-13 08:19:48 1093 47
7 226 2022-05-13 08:19:48 1093 47
8 227 2022-05-13 08:19:48 1093 47
... ... ... ...
No. Date Fiducial# Offset // at this point the next group starts
49 267 2022-05-13 08:20:10 1107 0
The last of the three is correct however it is very important that the first two are in the same group, which should look like this:
No. Date Fiducial# Offset
0 219 2022-05-13 08:19:46 1091 0
1 220 2022-05-13 08:19:46 1091 0
2 221 2022-05-13 08:19:46 1091 0
3 222 2022-05-13 08:19:47 1092 46
4 223 2022-05-13 08:19:47 1092 46
5 224 2022-05-13 08:19:47 1092 46
6 225 2022-05-13 08:19:48 1093 47
7 226 2022-05-13 08:19:48 1093 47
8 227 2022-05-13 08:19:48 1093 47
... ... ... ...
No. Date Fiducial# Offset
49 267 2022-05-13 08:20:10 1107 0 // at this point the next group starts
One thing that is important here, is that in the end I need the first and last Date and No. of each group.
So now I'm wondering how I can achieve this?
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|