Customized datetime format within table

Hi all,

I am trying to compare 2 tables (for both 1st column is datetime, others are numbers). I am trying to find the common timeperiod between those 2 tables. Unfortunately, days are not the same. My original idea is to convert dates into string and look for matching strings between both tables.

Question:

I am a beginner at Matlab, is there a better way to do it? If not, is there an elegant way to get rid of the 2 loops?

Here is the script for the transformation shown in the workspace

------------------------------------------------------------

A1 = readtable(file_name,'VariableNamingRule','preserve'); clc;
B1 = readtable('Independant variables.xlsx');
for i = 1:height(A1)
    A2{i,1} = extractBetween(datestr(A1{i,1}),4,11);
end
for j = 1:height(B1)
    B2{j,1} = extractBetween(datestr(B1{j,1}),4,11);
end

------------------------------------------------------------

Thanks and best regards,

[Stephen23]

"My original idea is to convert dates into string and look for matching strings between both tables."

Converting to string is usually a sign that the user is not leveraging the DATETIME class to do its job.

"is there a better way to do it?"

There might be some inbuilt approach using TIMETABLES.

Otherwise MAX & MIN and logical indexing would seem to do what you want.

[Walter Roberson]

See also overlapsrange -- especially with the second output

[Vic]

Thanks you both for your inputs. It helped me finding a solution to my issue.

Here is the working solution returning an array of boolean values.

--------------------------------------------------------------------

A1 = readtable(file_name,'VariableNamingRule','preserve');

B1 = readtable('Variables.xlsx');

Timeframe1 = table2timetable(A1);

Timeframe2 = timerange(B1.observation_date(1),B1.observation_date(end),'months');

[~,Overlap]= overlapsrange(Timeframe1,Timeframe2);

Answer 1

[Vic]

Thanks you both for your inputs. It helped me finding a solution to my issue.

Here is the working solution returning an array of boolean values.

--------------------------------------------------------------------

A1 = readtable(file_name,'VariableNamingRule','preserve');

B1 = readtable('Variables.xlsx');

Timeframe1 = table2timetable(A1);

Timeframe2 = timerange(B1.observation_date(1),B1.observation_date(end),'months');

[~,Overlap]= overlapsrange(Timeframe1,Timeframe2);

[Dyuman Joshi]

You can also use readtimetable directly. (available for R2019a and afterwards)

Answer 2

[Peter Perkins]

Vic, I think you want synchronize.

tt1 = timetable([1;2;3;4;5],RowTimes=datetime(2023,1:5,1))

tt1 = 5×1 timetable
       Time        Var1
    ___________    ____

    01-Jan-2023     1  
    01-Feb-2023     2  
    01-Mar-2023     3  
    01-Apr-2023     4  
    01-May-2023     5  

tt2 = timetable([6;7;8;9;10],RowTimes=datetime(2023,1:5,[31 28 31 30 31]))

tt2 = 5×1 timetable
       Time        Var1
    ___________    ____

    31-Jan-2023      6 
    28-Feb-2023      7 
    31-Mar-2023      8 
    30-Apr-2023      9 
    31-May-2023     10 

tt3 = synchronize(tt1,tt2,"monthly","firstvalue");
tt3.Time.Format = "MMM-uuuu"

tt3 = 5×2 timetable
      Time      Var1_tt1    Var1_tt2
    ________    ________    ________

    Jan-2023       1            6   
    Feb-2023       2            7   
    Mar-2023       3            8   
    Apr-2023       4            9   
    May-2023       5           10   

If you don't want the two timetables joined together, use retime on the second one with the first one's time vector as the target row times.