Very challenging brain tease Pandas problem: Timeseries cut and overlap

Question

Our data scientist is attempting to generate some fictitious data from the current load profile data, where the 'y' column represents some activity such as "use some electricity," but we only have data for a specific time period, so we want to shift the activity to another time period. As a result, the load was expected to be moved(and added to) from period A to period B, as well as the label 'y'.

So I'm looking for a function that will allow us to transfer the label and power consumption from period A to period B, and leave period A zero or low consumption in order to simulate "use some electricity" in period B, not in period A. When the 'y' label is True, we'd like to map 18:30 to 23:30 and 23:30 to 4:30 at next day.

index   ts  f1  f2  y
8   2021-10-01 17:00:00 1044.0  0.0 False
9   2021-10-01 17:30:00 26.0    12.0    False
10  2021-10-01 18:00:00 16.0    10.0    False
---------------------------------------- P1
11  2021-10-01 18:30:00 10.0    12.0    False
12  2021-10-01 19:00:00 226.0   32.0    False
13  2021-10-01 19:30:00 86.0    34.0    False
14  2021-10-01 20:00:00 106.0   12.0    False
15  2021-10-01 20:30:00 8.0 18.0    False
16  2021-10-01 21:00:00 70.0    14.0    False
17  2021-10-01 21:30:00 1604.0  0.0 False
18  2021-10-01 22:00:00 1454.0  0.0 False
19  2021-10-01 22:30:00 1724.0  0.0 False
20  2021-10-01 23:00:00 1104.0  0.0 False
---------------------------------------- P2
21  2021-10-01 23:30:00 1232.0  0.0 False
22  2021-10-02 00:00:00 798.0   8.0 False
23  2021-10-02 00:30:00 4342.0  0.0 True
24  2021-10-02 01:00:00 4352.0  0.0 True
25  2021-10-02 01:30:00 4258.0  0.0 True
26  2021-10-02 02:00:00 3350.0  0.0 False
27  2021-10-02 02:30:00 1088.0  0.0 False
28  2021-10-02 03:00:00 1090.0  0.0 False
29  2021-10-02 03:30:00 1558.0  0.0 False
30  2021-10-02 04:00:00 262.0   0.0 False
---------------------------------------- P3
31  2021-10-02 04:30:00 730.0   0.0 False
32  2021-10-02 05:00:00 296.0   0.0 False
33  2021-10-02 05:30:00 192.0   0.0 False
34  2021-10-02 06:00:00 228.0   0.0 False
35  2021-10-02 06:30:00 338.0   0.0 False
---------------------------------------- P4
......

Expected result

index   ts  f1  f2  y
8   2021-10-01 17:00:00 1044.0  0.0 False
9   2021-10-01 17:30:00 26.0    12.0    False
10  2021-10-01 18:00:00 16.0    10.0    False
---------------------------------------- P1
11  2021-10-01 18:30:00 10.0    12.0    False
12  2021-10-01 19:00:00 226.0   32.0    False
13  2021-10-01 19:30:00 86.0+4342.0 34.0+8.0    True
14  2021-10-01 20:00:00 106.0+4352.0    12.0    True
15  2021-10-01 20:30:00 8.0+4258.0  18.0    True
16  2021-10-01 21:00:00 70.0    14.0    False
17  2021-10-01 21:30:00 1604.0  0.0 False
18  2021-10-01 22:00:00 1454.0  0.0 False
19  2021-10-01 22:30:00 1724.0  0.0 False
20  2021-10-01 23:00:00 1104.0  0.0 False
---------------------------------------- P2
21  2021-10-01 23:30:00 1232.0  0.0 False
22  2021-10-02 00:00:00 798.0   8.0 False
23  2021-10-02 00:30:00 192.0   0.0 False
24  2021-10-02 01:00:00 192.0   0.0 False
25  2021-10-02 01:30:00 192.0   0.0 False
26  2021-10-02 02:00:00 3350.0  0.0 False
27  2021-10-02 02:30:00 1088.0  0.0 False
28  2021-10-02 03:00:00 1090.0  0.0 False
29  2021-10-02 03:30:00 1558.0  0.0 False
30  2021-10-02 04:00:00 262.0   0.0 False
---------------------------------------- P3
31  2021-10-02 04:30:00 730.0   0.0 False
32  2021-10-02 05:00:00 296.0   0.0 False
33  2021-10-02 05:30:00 192.0   0.0 False
34  2021-10-02 06:00:00 228.0   0.0 False
35  2021-10-02 06:30:00 338.0   0.0 False
---------------------------------------- P4
......

Raw for you to reproduce

{'ts': [Timestamp('2021-10-01 17:00:00'),
  Timestamp('2021-10-01 17:30:00'),
  Timestamp('2021-10-01 18:00:00'),
  Timestamp('2021-10-01 18:30:00'),
  Timestamp('2021-10-01 19:00:00'),
  Timestamp('2021-10-01 19:30:00'),
  Timestamp('2021-10-01 20:00:00'),
  Timestamp('2021-10-01 20:30:00'),
  Timestamp('2021-10-01 21:00:00'),
  Timestamp('2021-10-01 21:30:00'),
  Timestamp('2021-10-01 22:00:00'),
  Timestamp('2021-10-01 22:30:00'),
  Timestamp('2021-10-01 23:00:00'),
  Timestamp('2021-10-01 23:30:00'),
  Timestamp('2021-10-02 00:00:00'),
  Timestamp('2021-10-02 00:30:00'),
  Timestamp('2021-10-02 01:00:00'),
  Timestamp('2021-10-02 01:30:00'),
  Timestamp('2021-10-02 02:00:00'),
  Timestamp('2021-10-02 02:30:00'),
  Timestamp('2021-10-02 03:00:00'),
  Timestamp('2021-10-02 03:30:00'),
  Timestamp('2021-10-02 04:00:00'),
  Timestamp('2021-10-02 04:30:00'),
  Timestamp('2021-10-02 05:00:00'),
  Timestamp('2021-10-02 05:30:00'),
  Timestamp('2021-10-02 06:00:00'),
  Timestamp('2021-10-02 06:30:00'),
  Timestamp('2021-10-02 07:00:00'),
  Timestamp('2021-10-02 07:30:00'),
  Timestamp('2021-10-02 08:00:00'),
  Timestamp('2021-10-02 08:30:00'),
  Timestamp('2021-10-02 09:00:00'),
  Timestamp('2021-10-02 09:30:00'),
  Timestamp('2021-10-02 10:00:00'),
  Timestamp('2021-10-02 10:30:00'),
  Timestamp('2021-10-02 11:00:00'),
  Timestamp('2021-10-02 11:30:00'),
  Timestamp('2021-10-02 12:00:00'),
  Timestamp('2021-10-02 12:30:00')],
 'f1': [1044.0,
  26.0,
  16.0,
  10.0,
  226.0,
  86.0,
  106.0,
  8.0,
  70.0,
  1604.0,
  1454.0,
  1724.0,
  1104.0,
  1232.0,
  798.0,
  4342.0,
  4352.0,
  4258.0,
  3350.0,
  1088.0,
  1090.0,
  1558.0,
  262.0,
  730.0,
  296.0,
  192.0,
  228.0,
  338.0,
  1454.0,
  46.0,
  196.0,
  28.0,
  110.0,
  206.0,
  132.0,
  992.0,
  212.0,
  2462.0,
  532.0,
  1366.0],
 'f2': [0.0,
  12.0,
  10.0,
  12.0,
  32.0,
  34.0,
  12.0,
  18.0,
  14.0,
  0.0,
  0.0,
  0.0,
  0.0,
  0.0,
  8.0,
  0.0,
  0.0,
  0.0,
  0.0,
  0.0,
  0.0,
  0.0,
  0.0,
  0.0,
  0.0,
  0.0,
  0.0,
  0.0,
  0.0,
  14.0,
  28.0,
  8.0,
  20.0,
  26.0,
  28.0,
  22.0,
  56.0,
  4.0,
  8.0,
  6.0],
 'y': [False,
  False,
  False,
  False,
  False,
  False,
  False,
  False,
  False,
  False,
  False,
  False,
  False,
  False,
  False,
  True,
  True,
  True,
  False,
  False,
  False,
  False,
  False,
  False,
  False,
  False,
  False,
  False,
  False,
  False,
  False,
  False,
  False,
  False,
  False,
  False,
  False,
  False,
  False,
  False]
 }

Other solutions connect with this one, here and here is the answer to the subproblem of this one.

Do we really need this much input in order to understand the problem? On the other hand, what actually *is* the problem? "and I want to cut some rows in P2-P3" - **which rows**? *How do we know* which ones? What do you mean by "cut"? - "and overlap on the P1-P2 period" Overlap how? What does that have to do with the expected output?. "And fill the cut rows to some min value in f1 and f2 on the P2-P4 period." Again, I can't understand this. — Karl Knechtel, Sep 23 '22 at 06:12
"The problem has been solved by breaking in into small problems" Cool. Please read [ask] and note well that this is **not a discussion forum**. Questions that can be broken down like that are not valid here, as they "need more focus". Questions that are duplicates are also not wanted. Also, please do not update questions in that way - we don't put "solved" markers on posts, because they are not fixed-time discussions - they are **questions** which are part of a Q&A library, which might be edited or curated any time, even years later. — Karl Knechtel, Sep 23 '22 at 06:14
@KarlKnechtel, Thank you, will update the question and make it more concise. — Yiffany, Sep 23 '22 at 08:12
@KarlKnechtel, Updated, Hope this version could help you better understand the question! — Yiffany, Sep 23 '22 at 08:30

Very challenging brain tease Pandas problem: Timeseries cut and overlap

0 Answers0

Linked