I have a data set of roadway and roadway segments vehicle count calculations.
Data set contains:
Road_ID= ID of each road
Seg_num= Index of each segment in each Road_ID
V1= Vehicle counts of type 1 in each Seg_num
V2= Vehicle counts of type 2 in each Seg_num
V3= Vehicle counts of type 3 in each Seg_num
V1cum= Cumulative sum of V1 by Seg_num and Road_ID,
V2cum= Cumulative sum of V2 by Seg_num and Road_ID,
V3cum= Cumulative sum of V3 by Seg_num and Road_ID.
Data frame looks like:
<tbody>
</tbody>I need to restart cumulative sum in V1cum, V2cum and V3cum after reaching 5 in all these (V1cum, V2cum and V3cum) columns and also when starting a new Road_ID. And get the cumulative sum values from V1cum, V2cum and V3cum before restarting the cumulative sum and place in each row of that range in separate column named, V1sum, V2sum and V3sum.
My expected result is
<tbody>
</tbody>
Please help me reaching this result. V1cum, V2cum, V3cum columns are for better understanding, but I need only columns V1sum, V2sum and V3sum columns.
Thanks in advance.
Sadia
Data set contains:
Road_ID= ID of each road
Seg_num= Index of each segment in each Road_ID
V1= Vehicle counts of type 1 in each Seg_num
V2= Vehicle counts of type 2 in each Seg_num
V3= Vehicle counts of type 3 in each Seg_num
V1cum= Cumulative sum of V1 by Seg_num and Road_ID,
V2cum= Cumulative sum of V2 by Seg_num and Road_ID,
V3cum= Cumulative sum of V3 by Seg_num and Road_ID.
Data frame looks like:
Road_ID | Seg_num | V1 | V1cum | V2 | V2cum | V3 | V3cum |
1 | 1 | 0 | 0 | 1 | 1 | 0 | 0 |
1 | 2 | 0 | 0 | 0 | 1 | 0 | 0 |
1 | 3 | 35 | 35 | 14 | 15 | 1 | 1 |
1 | 4 | 20 | 55 | 7 | 22 | 3 | 4 |
1 | 5 | 23 | 78 | 3 | 25 | 2 | 6 |
1 | 6 | 2 | 80 | 0 | 25 | 9 | 15 |
1 | 7 | 4 | 84 | 3 | 28 | 0 | 15 |
1 | 8 | 3 | 87 | 4 | 32 | 1 | 16 |
1 | 9 | 3 | 90 | 2 | 34 | 1 | 17 |
1 | 10 | 0 | 90 | 0 | 34 | 4 | 21 |
1 | 11 | 0 | 90 | 0 | 34 | 6 | 27 |
1 | 12 | 17 | 107 | 7 | 41 | 0 | 27 |
1 | 13 | 3 | 110 | 2 | 43 | 1 | 28 |
1 | 14 | 0 | 110 | 4 | 47 | 2 | 30 |
2 | 1 | 2 | 2 | 0 | 0 | 1 | 1 |
2 | 2 | 4 | 6 | 3 | 3 | 1 | 2 |
2 | 3 | 3 | 9 | 4 | 7 | 4 | 6 |
2 | 4 | 3 | 12 | 2 | 9 | 1 | 7 |
2 | 5 | 0 | 12 | 4 | 13 | 2 | 9 |
<tbody>
</tbody>
My expected result is
Road_ID | Seg_num | V1 | V1cum | V1sum | V2 | V2cum | V2sum | V3 | V3cum | V3sum |
1 | 1 | 0 | 0 | 78 | 1 | 1 | 25 | 0 | 0 | 6 |
1 | 2 | 0 | 0 | 78 | 0 | 1 | 25 | 0 | 0 | 6 |
1 | 3 | 35 | 35 | 78 | 14 | 15 | 25 | 1 | 1 | 6 |
1 | 4 | 20 | 55 | 78 | 7 | 22 | 25 | 3 | 4 | 6 |
1 | 5 | 23 | 78 | 78 | 3 | 25 | 25 | 2 | 6 | 6 |
1 | 6 | 2 | 2 | 9 | 0 | 0 | 7 | 9 | 9 | 10 |
1 | 7 | 4 | 6 | 9 | 3 | 3 | 7 | 0 | 9 | 10 |
1 | 8 | 3 | 9 | 9 | 4 | 7 | 7 | 1 | 10 | 10 |
1 | 9 | 3 | 3 | 20 | 2 | 2 | 9 | 1 | 1 | 11 |
1 | 10 | 0 | 3 | 20 | 0 | 2 | 9 | 4 | 5 | 11 |
1 | 11 | 0 | 3 | 20 | 0 | 2 | 9 | 6 | 11 | 11 |
1 | 12 | 17 | 20 | 20 | 7 | 9 | 9 | 0 | 11 | 11 |
1 | 13 | 3 | 3 | 3 | 2 | 2 | 6 | 1 | 1 | 3 |
1 | 14 | 0 | 0 | 3 | 4 | 6 | 6 | 2 | 3 | 3 |
2 | 1 | 2 | 2 | 9 | 0 | 0 | 7 | 1 | 1 | 6 |
2 | 2 | 4 | 6 | 9 | 3 | 3 | 7 | 1 | 2 | 6 |
2 | 3 | 3 | 9 | 9 | 4 | 7 | 7 | 4 | 6 | 6 |
2 | 4 | 3 | 3 | 3 | 2 | 2 | 6 | 1 | 1 | 3 |
2 | 5 | 0 | 0 | 3 | 4 | 6 | 6 | 2 | 3 | 3 |
<tbody>
</tbody>
Please help me reaching this result. V1cum, V2cum, V3cum columns are for better understanding, but I need only columns V1sum, V2sum and V3sum columns.
Thanks in advance.
Sadia