Resample multiple overlapping datetime to 1-minute (and 15-minutes) resolution?

Coming back again on resampling datetime....

Situation is I have many water pumps which are filled up in different time-frames. I have this table with multiple overlapping time and the total volume of individual pumps. Each row is basically a different water pump.

Column 1 (Start time), Column 2 (End time), Column 3 (Volume)

I want to sample the data to minute resolution (also 15 mins resolution) with the specified volume.

Example:

Pump1: 00:04:34 06:33:34 56

Pump2: 00:10:09 09:50:01 93

What I want it

00:04:00 56

00:05:00 56

....

00:10:00 56+93

00:11:00 56+93

....

06:34:00 93

As you can see, it sums up when there is overlapping time. Or else it is the only volume.

I have attached the actual table as well.

Thanks!

[Adam Danz]

Continuing from https://www.mathworks.com/matlabcentral/answers/476451-resample-datetime-to-minute-resolution

How does this differ from the solution in the previous question (link above)?

Also, where is the overlapping time? Maybe a longer, complete example would be helpful.

[Alex]

Thanks Adam! I have attached a screenshot.

As you can see, all pumps start within first 30minutes of day. When I try to use the solution from previous post, it does not sum up the total volume as I mentioned. For instance, at 01-Jan-2018 00:17:00, it should be 2.87+4.7090. However, it shows only 4.7090.

Hope I was clear.

Thanks again

[Adam Danz]

That is called a cumulative sum. For example,

example.Volume = cumsum(example.Volume);

[Alex]

Depends on the days. Like this (based on the picture in my previous message):

01-Jan-2018 00:15 2.87

...

01-Jan-2018 00:17 2.87+4.7090

...

01-Jan-2018 00:22 2.87+4.7090+4.7150

... and it continues

Once the End day for Pump #1 is reached, it will be deleted from the cumulative sum. Hope you got it?

[Adam Danz]

The data in your example is all from the same day so it looks like you just need to apply the cumsum() like I showed in my previous comment.

[Alex]

Yes, it is part of the big table. As you can see, End Time of some pumps are same day and for some it is the next day (i.e., 2nd january).

[Adam Danz]

I think I understand now. After you've interpolated the time stamps to minute resolution and replicated the volumne values, (as was demonstrated here), you get a timetable with two columns. Can you just apply the cumulative sum to the "vol" column? I haven't looked too closely as to why that wouldn't work.

[Update]

I think Steven's approach below is the way to go.

Answer 1

[Steven Lord]

Let's start off with two simple timetable arrays, one per pump.

pump1 = timetable(datetime(["01-Jan-2018 00:14:09"; "01-Jan-2018 11:22:13"]), ...
    [2.87; 2.87], 'VariableNames', {'Volume_pump1'})
pump2 = timetable(datetime(["01-Jan-2018 00:16:40"; "01-Jan-2018 14:15:10"]), ...
    [4.709; 4.709], 'VariableNames', {'Volume_pump2'})

synchronize the two timetable arrays to a new one whose Times are regularly spaced (every 15 minutes), spanning the union of the Times for the two smaller timetable arrays. I'm using linear interpolation and no extrapolation, so the pump is only 'on' during the range spanned by the Times in its smaller timetable array from above.

P = synchronize(pump1, pump2, 'regular', 'linear', ...
    'TimeStep', minutes(15), ...
    'EndValues', 0)

Note that because I told synchronize to give regular spacing and the Times from pump1 and pump2 aren't multiples of 15 minutes, they're not included. You could construct a NEWTIMES vector (combine the Times together and use bounds to determine the earliest and latest times) and pass that into synchronize if you need those exact times to be included.

To get the total volume:

P2 = timetable(P.Time, sum(P.Variables, 2), 'VariableNames', {'totalVolume'})

[Alex]

Thank you, Steven, for the answer. It works. But I am still wondering how to use it for a larger dataset. I have data till May.

Should I always breakdown with respect to pump #? Or is there any other way to handle the situation?

Thanks again!

[Steven Lord]

If you have multiple pump cycles, you'd probably want to add a row to each of the individual timetable arrays with Volume_pumpN equal to 0 at the start and end of each time the pump is turned off. Otherwise synchronize would think it's on continuously from the first time it turns on to the last time it turns off.

You could probably store all this data in one timetable with multiple variables from the start and use retime instead of synchronize but I don't have example code for that. It should be fairly similar.

[Alex]

Thanks Steven for the clarification. However, I have a minor issue. Now, I run a loop for the 'synchronization' function. But, I am unable to run it for the whole dataset.

Seems I am missing out something in the loop, i.e., adding up the columns. Any pointers?

In the below script: pump_data is the cell array with different pump timetables.

pump1 = pump_data{1,1}(:,1);
pump1.vol(:)=0;
pump_fuel = [];
for f1 = 1:numel(pump_data)
    pump_fuel = synchronize(pump_data{1,f1+1}(:,1),pump1,'regular', 'linear','TimeStep', minutes(15),'EndValues', 0);
end