Insert missing date and corresponding values in a matrix?

Hi!

I have a matrix: Column 1 (Start time), Column 2 (End time), Column 3 (Values). However, start and end time are not continuos. It is yearly data. How can I plot the values against time? A snapshot of the data:

'03-Jan-2018 16:58:09'	'04-Jan-2018 08:08:12'	40.58000000000000
'04-Jan-2018 17:03:22'	'05-Jan-2018 08:13:39'	45.22000000000000
'05-Jan-2018 17:06:50'	'06-Jan-2018 09:19:06'	34.57000000000000
'06-Jan-2018 11:53:42'	'06-Jan-2018 13:37:27'	94.42000000000000
'06-Jan-2018 19:19:20'	'07-Jan-2018 13:32:37'	84.92000000000000

Thanks!

Answer 1

[Adam Danz]

If you're looking for timelines that contain segments of start/stop times, here's a method.

data = {
'03-Jan-2018 16:58:09'	'04-Jan-2018 08:08:12'	40.58000000000000
'04-Jan-2018 17:03:22'	'05-Jan-2018 08:13:39'	45.22000000000000
'05-Jan-2018 17:06:50'	'06-Jan-2018 09:19:06'	34.57000000000000
'06-Jan-2018 11:53:42'	'06-Jan-2018 13:37:27'	94.42000000000000
'06-Jan-2018 19:19:20'	'07-Jan-2018 13:32:37'	84.92000000000000}; 

startDate = datetime(data(:,1)); 
stopDate = datetime(data(:,2)); 
values = [data{:,3}].'; 

clf()
plot([startDate,stopDate].',[values,values].', 'k-o')

If you'd rather have the line segments connected,

dates = [datetime(data(:,1)), datetime(data(:,2))].';
values = [data{:,3}].';

clf()
plot(dates(:),repelem(values,2,1), 'k-o')

[Alex]

Thanks, Adam! It was exactly what I was looking for.

[Jon]

@Adam I was wondering whether there was a reason why you used

repelem(values,2,1)

rather than the maybe more obvious

[values;values]

or the slightly more familiar

repmat(values,2,1)

I had never seen this way of repeating a row. I guess it's probably just a style choice, but I wondered whether there was anything deeper.

[Adam Danz]

Hi Jon, repelem() and repmat() do not produce the same result.

See how these commands differ in this demo below.

values = [1;2;3;4]; 
repelem(values,2,1)
% ans =
%      1
%      1
%      2
%      2
%      3
%      3
%      4
%      4

versus

values = [1;2;3;4]; 
repmat(values,2,1)  % Or  [values;values];  same thing
% ans =
%      1
%      2
%      3
%      4
%      1
%      2
%      3
%      4

Answer 2

[Jon]

I'm not completely clear on what it is you are trying to do, but I think you want to plot the values in the third column against the times in the first column. I assume since these are mixed data types (characters in the first two columns, doubles in the last) that this "matrix" is in fact a cell array. If so, you could do something like

% cell array holding data
data = {
'03-Jan-2018 16:58:09'	'04-Jan-2018 08:08:12'	40.58000000000000
'04-Jan-2018 17:03:22'	'05-Jan-2018 08:13:39'	45.22000000000000
'05-Jan-2018 17:06:50'	'06-Jan-2018 09:19:06'	34.57000000000000
'06-Jan-2018 11:53:42'	'06-Jan-2018 13:37:27'	94.42000000000000
'06-Jan-2018 19:19:20'	'07-Jan-2018 13:32:37'	84.92000000000000}

% get MATLAB datetime values from cell array of character vectors
time = datetime(data(:,1));

% get measured values from last column as a vector of doubles
measurements = cell2mat(data(:,3))
plot(time,measurements)

[Alex]

@Jon: Thank you for your answer. let me make it clear to you then: column 1 and 2 are in datetime format. And, column3 is double format. I want to plot the values against the time.

[Jon]

Sorry, but I'm still not understanding. You have two columns that hold time values. Do you want to plot your data against the times in the first column, the times in the second column, or maybe somewhere between the times in the first and the second column?

What have you tried to do already, and what problem did you encounter?

[Alex]

Sorry for the confusion, Jon. I want to visualize the values for the correpsonding the time-intervals. However, I do not know how to visualize them. My first option was to insert zeros to missing time-frames. Since, the time values are in seconds, I was bit skeptical to do so.

[Jon]

So you want a plot that looks like a square wave with a constant value of 40.58000000000000 from '03-Jan-2018 16:58:09' to '04-Jan-2018 08:08:12', then zero from '04-Jan-2018 08:08:12' to '04-Jan-2018 17:03:22' then 45.22000000000000 from '04-Jan-2018 17:03:22' to '05-Jan-2018 08:13:39' etc?

[Alex]

Yes, true. But, it should be values vs. time.

[Jon]

So the vertical axis are the values from the third column, and the horizontal axis are time intervals? What have you tried already. What problem did you have?

[Alex]

I was only able to plot the values but not against the time. So, I could not plot values vs. given time-frame.

[Jon]

Does this do what you want

data = {
'03-Jan-2018 16:58:09'	'04-Jan-2018 08:08:12'	40.58000000000000
'04-Jan-2018 17:03:22'	'05-Jan-2018 08:13:39'	45.22000000000000
'05-Jan-2018 17:06:50'	'06-Jan-2018 09:19:06'	34.57000000000000
'06-Jan-2018 11:53:42'	'06-Jan-2018 13:37:27'	94.42000000000000
'06-Jan-2018 19:19:20'	'07-Jan-2018 13:32:37'	84.92000000000000}




% get MATLAB datetime values from cell array of character vectors
start = datetime(data(:,1));
finish = datetime(data(:,2));

% get measured values from last column as a vector of doubles
measurements = cell2mat(data(:,3))

% make loop to plot each data segment
figure
for k = 1:length(start)
    plot([start(k),finish(k)],[measurements(k) measurements(k)],'Color','k')
    hold on
end
hold off

[Jon]

As @Adam Danz points out, you can easily vectorize the above code and eliminate the loop, which is always a good idea. I just wanted to make sure you were clear about what was getting plotted and so made it a little more explicit with the loop. If you are doing this for big data sets it would definitely be better to vectorize it. In any case the vectorized approach is definitely much cleaner, so you should just use that.

[Alex]

Thanks Jon for the explanation. It really helped me to understand. In fact, the answer from Adam worked for my case, and it was exactly what I was looking for.