Split values for a time-frame to every 15 minutes

Hello all,

Firstly, apologies if the title is wrong. So, feel free to edit it. And, here's my situation. I have a certain values for a time-frame, like (table format) attached as pic:

I want to re-sample the data every 15 minutes and the corresponding values. It should be such that when I sum the intervals, I should get the Volume for the time-frame.

Thanks

[Star Strider]

See if the retime function will do what you want.

[Guillaume]

It's unclear what it is you want to resample to every 15 minutes. Is it the Start values which in your screenshot have intervals varying from 30 minutes down to 5 seconds? In which case what should happen to End and Volume when time points are added in the 30 minutes case (maybe interpolate for the volumne, no idea for End) and what should happen when several rows are grouped for the 5 seconds case (sum? average?)

Or is it the interval between Start and End which in you screenshot varies from around a day to 10 minutes? In which case, what should happen to Volume?

An example of the desired result would really help.

[Alex]

Thank you Star Strider and Guillaume for your comments.

@star: I did check retime and resample but without any success.

@Guillaume: I can explain you for the first time-frame. Volume for time-frame is 6.79. And the total time is ~23hours. So, if I want to resample the Volume to every 1 hour, for each hour the value should be 0.295. In such case, when I sum up all Volume values, I will get 6.79.

Similarly, I want to for every 15 minutes.

Hope I was clear. Please let me know.

Answer 1

[Guillaume]

This should work.

First a function to operate on each row of your table:

function spreadtable = spreadvolume(dstart, dend, volume, dt)
    %resample the interval [dstart, dend] into smaller intervals of duration dt and spread volume over these intervals
    %note that the last interval may be shorter if dend-dstart is not a multiple of dt
    %returns a table with 3 variables {'Start', 'End', 'Volume'} and as many rows as necessary
    starts = (dstart:dt:dend)';
    if starts(end) == dend   %don't want [dend, dend] as the last interval.
        starts(end) = [];
    end
    ends = [starts(2:end); dend];
    dts = seconds(ends - starts);
    volumes = volume / sum(dts) .* dts;
    spreadtable = table(starts, ends, volumes, 'VariableNames', {'Start', 'End', 'Volume'});
end

Then apply to your table:

demotable = [array2table(datetime({'01-Jan-2018 13:11:02', '02-Jan-2018 12:15:40'; '01-Jan-2018 13:42:13', '02-Jan-2018 09:00:12'; '01-Jan-2018 13:42:18', '01-Jan-2018 14:02:33'}), 'VariableNames', {'Start', 'End'}), table([6.7; 19.25; 2.25], 'VariableNames', {'Volume'})]

resampled = rowfun(@(s, e, v) spreadvolume(s, e, v, minutes(15)), demotable, 'OutputFormat', 'cell');
resampled = vertcat(resampled{:})

[Alex]

Thanks Guillaume! It works like a charm.