Extract Data from Timetable

Hi, i have a daytime Matrix and i also have a second timetable of 2 columns (first Time, second Data) of dates from which i want to extract for each daytime of the first Matrix the Data that refers to this Date fromthe second one!

Is there an easy way, without using a for loop, to do so?

[dpb]

Yes

[Ioannis Tsikriteas]

How?

[Paolo]

Can you share the code for what you have tried so far?

[Ioannis Tsikriteas]

I have no code yet.

I only have a timetable with my Data and a second datetime Matrix which includes the dates that i want to know my Data

[Paolo]

An example:

A datetime matrix:

 daytimemat = datetime(['13/04/2018';'25/04/2018';'28/04/2018'],'Format','dd/MM/yyyy');

A timetable with dates and data.

 tt = timetable(datetime({'13/04/2018';'25/04/2018';'26/04/2018';'28/04/2018'}), [37.3;39.1;42.3;21]);

The following command:

 tt.Var1(tt.Time(daytimemat))

outputs :

 37.3000
 39.1000
 21.0000

which is the data contained in the timetable tt corresponding to the dates in daytimemat array.

Does this help?

[Ioannis Tsikriteas]

Yes that was the answer!!!! Thanks a lot.

By the way, i have a second problem. I use the addtodate function to add to my datetime 1 year but the problem is that it increases 1 year and 1 second every other row on my daytime matrix. Do you have any idea why this is happening? This is the code i use to add and subtract 5 years to my timetable data

function[y]=add_dates(x,air_temp)
clear y
up=datenum(air_temp.Time(height(air_temp)));
down=datenum(air_temp.Time(1));
x=datenum(x);
for i=1:length(x)
  t=addtodate(x(i,1),-1,'year');
if (t>= down) && (t <= up)
y(i,5)=t;
else
  y(i,5)=0;
end
t=addtodate(x(i,1),-2,'year');
if (t>= down) && (t <= up)
 y(i,4)=t;
 else
  y(i,4)=0;
end
  t=addtodate(x(i,1),-3,'year');
if (t>= down) && (t <= up)
 y(i,3)=t;
 else
 y(i,3)=0;
end
  t=addtodate(x(i,1),-4,'year');
if (t>= down) && (t <= up)
 y(i,2)=t;
 else
 y(i,2)=0;
end
 t=addtodate(x(i,1),-5,'year');
if (t>= down) && (t <= up)
 y(i,1)=t;
 else
  y(i,1)=0;
end
   t=addtodate(x(i,1),1,'year');
if (t>= down) && (t <= up)
y(i,6)=t;
else
  y(i,6)=0;
end
t=addtodate(x(i,1),2,'year');
if (t>= down) && (t <= up)
 y(i,7)=t;
 else
  y(i,7)=0;
end
  t=addtodate(x(i,1),3,'year');
if (t>= down) && (t <= up)
 y(i,8)=t;
 else
 y(i,8)=0;
end
  t=addtodate(x(i,1),4,'year');
if (t>= down) && (t <= up)
 y(i,9)=t;
 else
 y(i,9)=0;
end
 t=addtodate(x(i,1),5,'year');
if(t>= down) && (t <= up)
 y(i,10)=t;
 else
  y(i,10)=0;
end
 i=i+1;
end
end

%

[Paolo]

@Ioannis, I have submitted an answer for the original question. If that solved the problem, please accept it so it will be easily visible to other users (rather than having the solution in the comment section).

As for your second question, I was unable to replicate the problem.

 x = datetime('08-Jul-2012 00:19:59','Format','dd-MMM-yyyy hh:mm:ss');

 x = addtodate(datenum(x),-1,'year');

 datestr(x)

The output is:

 08-Jul-2011 00:19:59

[Ioannis Tsikriteas]

Paolo it seems the problem is at the conversion from datenum to datetime I changed my code so it does not need to use the addtodate function and to convert to datenum.

Here is my new code and it works

function[y]=add_dates(x,air_temp)
clear y
y=datetime;
up=air_temp.Time(height(air_temp));
down=air_temp.Time(1);
%x=datenum(x);
for i=1:length(x)
  t=datetime(x(i)) - calyears([1]);
  if (t>= down) && (t <= up)
y(i,5)=t;
else
  y(i,5)=NaT;
end
t=datetime(x(i)) - calyears([2]);
if (t>= down) && (t <= up)
 y(i,4)=t;
 else
  y(i,4)=NaT;
end
   t=datetime(x(i)) - calyears([3]);
if (t>= down) && (t <= up)
 y(i,3)=t;
 else
 y(i,3)=NaT;
end
   t=datetime(x(i)) - calyears([4]);
if (t>= down) && (t <= up)
 y(i,2)=t;
 else
 y(i,2)=NaT;
end
 t=datetime(x(i)) - calyears([5]);
if (t>= down) && (t <= up)
 y(i,1)=t;
 else
  y(i,1)=NaT;
end
   t=datetime(x(i)) + calyears([1]);
if (t>= down) && (t <= up)
  y(i,6)=t;
else
  y(i,6)=NaT;
end
 t=datetime(x(i)) + calyears([2]);
if (t>= down) && (t <= up)
 y(i,7)=t;
 else
  y(i,7)=NaT;
end
   t=datetime(x(i)) + calyears([3]);
if (t>= down) && (t <= up)
 y(i,8)=t;
 else
 y(i,8)=NaT;
end
   t=datetime(x(i)) + calyears([4]);
if (t>= down) && (t <= up)
 y(i,9)=t;
 else
 y(i,9)=NaT;
end
  t=datetime(x(i)) + calyears([5]);
if(t>= down) && (t <= up)
 y(i,10)=t;
 else
  y(i,10)=NaT;
end
 i=i+1;
end
end

[Peter Perkins]

Ioannis, I'm pretty sure every loop in your code is unnecessary. For example:

y = NaT(somePreallocationSize);
i = isbetween(x-calyears(1),down,up);
y(i,5) = t(i,5);

I can't really follow everything that's in your code, but that's the idea. This will run MUCH faster and is much simpler to write and maintain.

[Ioannis Tsikriteas]

Thank's a lot. You know...i am not as experienced in Matlab as you are. I wanted to add and to substract also to my dates 5 years. Indeed it was slow to execute all these loops but eventually it was a succesful attempt

Answer 1

[Paolo]

This example will help you.

A datetime matrix:

 daytimemat = datetime(['13/04/2018';'25/04/2018';'28/04/2018'],'Format','dd/MM/yyyy');

A timetable with dates and data.

 tt = timetable(datetime({'13/04/2018';'25/04/2018';'26/04/2018';'28/04/2018'}), [37.3;39.1;42.3;21]);

The following command:

 %Use the index for which tt.Time and daytimemat are equal to find data.
 tt.Var1(tt.Time(daytimemat))

outputs :

 37.3000
 39.1000
 21.0000

which is the data contained in the timetable tt corresponding to the dates in daytimemat array.