comparing datetime from two different files

Hello,

I have 1 mat file called tempo_u1300processo that has times, from with 1 minute of frequency like 01/01/2016 00:00:00, 01/01/2016 00:01:00, etc. In u1200lab(:,1) I got also times, but in the frequency of days: 01/01/2016 04:30:00, 03/01/2016 4:30:00, etc.

The goal of my for loops is to check the times from the 2, in order to create a u1200lab table with a frequency of minutes and to place the value of the variables in front.

This works "fine", but it takes me for my calculation 7 hours to do for 1 variable because the month of January takes 2min, February 4 min (Jan + 2 min), mars 6 min (Jan + Feb + 2), April 8 minutes (Jan Feb Mars + 2min). So i think when starting May, it will do again January February mars April and just may after.

So my question is how to make him not do the months that are already done.

Thanks for your time.

clear;
clc;
close all;
%% ------------------------------------------------------------------------
%% Data Loading - Lab1200 + Lab1300 + Processo1300
load('u1200lab.mat');
load('u1300lab.mat');
load('u1300processo.mat');
load('tempo_u1300processo.mat');
variavel_u1300processo = u1300processo{:,2:end};
%% ------------------------------------------------------------------------
%% Laboratório 1200
[n1200,m1200] = size(u1200lab);
ind_aux1200 = [];
nvar1200 = m1200/2;
for j = 1:nvar1200 % Para os dados de Lab da 1200
  for i = 1:length(tempo_u1300processo)
      ind_aux1200 = find(u1200lab{:,2*j-1}==tempo_u1300processo(i,1));
      if  ~isempty(ind_aux1200)
          tempo_u1200lab(i,j) = tempo_u1300processo(i,1)
          variavel_u1200lab(i,j) = u1200lab(ind_aux1200,2*j); 
      else % Não existe registo para o tempo_processo(i)
          tempo_u1200lab(i,j) = tempo_u1300processo(i,1);
          variavel_u1200lab{i,j} = NaN;
      end
  ind_aux1200=[];
  end
  num_pontos1200(:,j) = sum(~isnan(variavel_u1200lab{:,j}));
  Pergunta1(1,j) = isequal(tempo_u1200lab(:,j),tempo_u1300processo)
end

[KSSV]

Convert dates to datenumbers using datenum and use ismemebr

[Tiago Dias]

So i give you an example of what are my inputs.

My goal is to have a matrix like in 3.xls

I understand what the function ismember does. The next step is to replace 0 for NaN's, and 1 for the value of variable in Excel 2.xls

but in the way i do, for i=3 variavel_lab(i,1) = lab(i,1); lab(3,1) does not exist because lab has only 2 rows.

clear;
  clc;
  close all;
  processo = readtable('1.xlsx','Range','A1:B11');
  tempo_processo = datetime(processo{:,1},'InputFormat','dd-MM-uuuu hh:mm:ss');
  lab = readtable('2.xlsx','Range','A1:B3');
  tempo_lab = datetime(lab{:,1},'InputFormat','dd-MM-uuuu hh:mm:ss');

[nlab,mlab] = size(lab);
  nvarlab = mlab/2;

Pergunta = ismember(tempo_processo,tempo_lab)
npergunta = length(Pergunta)

      for i = 1:npergunta
          if Pergunta(i,1)==0
              variavel_lab(i,1) = NaN;
          else
              variavel_lab(i,1) = lab(i,2);
          end
      end

Answer 1

[Peter Perkins]

It's very likely that what you describe can be done in one line of code, using timetables and the synchronize function. For example

>> X = (0:4*1440)';
>> tt1 = timetable(X,'RowTimes',datetime(2018,2,15,0,0:4*1440,0));
>> Y = (100:100:400)';
>> tt2 = timetable(Y,'RowTimes',datetime(2018,2,15:18,4,30,0));

>> tt = synchronize(tt1,tt2,'minutely','spline')
tt =
  8×2 timetable
            Time            X      Y   
    ____________________    _    ______
    15-Feb-2018 00:00:00    0     81.25
    15-Feb-2018 00:01:00    1    81.319
    15-Feb-2018 00:02:00    2    81.389
    15-Feb-2018 00:03:00    3    81.458
    15-Feb-2018 00:04:00    4    81.528
    15-Feb-2018 00:05:00    5    81.597
    15-Feb-2018 00:06:00    6    81.667
    15-Feb-2018 00:07:00    7    81.736

That requires R2016b or later.Not sure what release you have.

But your sample files don't match your description, and in fact they don't even match their own file extensions (as was also true in a previous post). You have not even explained how you want to transform your daily data to a 1-minute frequency.

Post a short, simple example that does not involve files, showing what you want to start with and what you want to end up with.

[Tiago Dias]

Ok, i will try to explain better what i have and what i want.

So u1300Processo is a table with time (minute in minute) and with a value of a variable X1. u1300processo =

            tempo            X1
    _____________________    __

    '01-01-2018 01:01:00'     2
    '01-01-2018 01:02:00'     3
    '01-01-2018 01:03:00'     4
    '01-01-2018 01:04:00'     5
    '01-01-2018 01:05:00'     6
    '01-01-2018 01:06:00'     7
    '01-01-2018 01:07:00'     8
    '01-01-2018 01:08:00'     9
    '01-01-2018 01:09:00'    10
    '01-01-2018 01:10:00'    11

Lab has also time and value of a variable. (note that this one is not minute in minute, has a different frequency of the previous table!)

u1200lab =

            tempo            X1
    _____________________    __

    '01-01-2018 01:05:00'    10
    '01-01-2018 01:08:00'    14

What is the goal? The goal is to expand the lab table so i have this: final =

            tempo            X1 
    _____________________    ___

    '01-01-2018 01:01:00'    NaN
    '01-01-2018 01:02:00'    NaN
    '01-01-2018 01:03:00'    NaN
    '01-01-2018 01:04:00'    NaN
    '01-01-2018 01:05:00'    10
    '01-01-2018 01:06:00'    NaN
    '01-01-2018 01:07:00'    NaN
    '01-01-2018 01:08:00'    14
    '01-01-2018 01:09:00'    NaN
    '01-01-2018 01:10:00'    NaN

To achieve that I use the code in the 1st description of the question, where tempo_u1300process is the time of the table u1300Processo

[Tiago Dias]

The problem is with a small table like this it works super fast, the problem is that my u1300processo goes from 01/01/2016 to 01/01/2017 (~2 years), and i run my FOR loops, i think its not efficient, it does January in 2 min, february in 4 min, mars 6 min, april 6 min, etc. So i guess that for the next step ot i he does the old ones and the new. thats why each month i get the 2 min + the time of the previous rows.

[Peter Perkins]

This is exactly what retime and synchronize are for. Use timetables, and use datetimes for timestamps, not text.

>> fmt = 'dd-MM-yyyy HH:mm:ss';
>> Time = datetime({'01-01-2018 01:01:00';'01-01-2018 01:02:00';'01-01-2018 01:03:00';'01-01-2018 01:04:00';'01-01-2018 01:05:00';'01-01-2018 01:06:00';'01-01-2018 01:07:00';'01-01-2018 01:08:00';'01-01-2018 01:09:00';'01-01-2018 01:10:00'}, ...
                'InputFormat',fmt);
>> X1 = (2:11)';
>> tt1 = timetable(Time,X1)
tt1 =
  10×1 timetable
            Time            X1
    ____________________    __
    01-Jan-2018 01:01:00     2
    01-Jan-2018 01:02:00     3
    01-Jan-2018 01:03:00     4
    01-Jan-2018 01:04:00     5
    01-Jan-2018 01:05:00     6
    01-Jan-2018 01:06:00     7
    01-Jan-2018 01:07:00     8
    01-Jan-2018 01:08:00     9
    01-Jan-2018 01:09:00    10
    01-Jan-2018 01:10:00    11
>> Time = datetime({'01-01-2018 01:05:00';'01-01-2018 01:08:00'},'InputFormat',fmt);
>> X2 = [10;14];
>> tt2 = timetable(Time,X2)
tt2 =
  2×1 timetable
            Time            X2
    ____________________    __
    01-Jan-2018 01:05:00    10
    01-Jan-2018 01:08:00    14
>> synchronize(tt1,tt2,'first')
ans =
  10×2 timetable
            Time            X1    X2 
    ____________________    __    ___
    01-Jan-2018 01:01:00     2    NaN
    01-Jan-2018 01:02:00     3    NaN
    01-Jan-2018 01:03:00     4    NaN
    01-Jan-2018 01:04:00     5    NaN
    01-Jan-2018 01:05:00     6     10
    01-Jan-2018 01:06:00     7    NaN
    01-Jan-2018 01:07:00     8    NaN
    01-Jan-2018 01:08:00     9     14
    01-Jan-2018 01:09:00    10    NaN
    01-Jan-2018 01:10:00    11    NaN
>> retime(tt2,tt1.Time)
ans =
  10×1 timetable
            Time            X2 
    ____________________    ___
    01-Jan-2018 01:01:00    NaN
    01-Jan-2018 01:02:00    NaN
    01-Jan-2018 01:03:00    NaN
    01-Jan-2018 01:04:00    NaN
    01-Jan-2018 01:05:00     10
    01-Jan-2018 01:06:00    NaN
    01-Jan-2018 01:07:00    NaN
    01-Jan-2018 01:08:00     14
    01-Jan-2018 01:09:00    NaN
    01-Jan-2018 01:10:00    NaN

And as for speed ...

>> Time = (datetime(2016,1,1,0,0,0):minutes(1):datetime(2017,1,1,0,0,0))';
>> X1 = randn(size(Time));
>> tt1 = timetable(Time,X1);
>> Time = datetime(2016,1,1,0,0,0) + minutes(sort(randperm(length(Time),10000)))';
>> X2 = randn(size(Time));
>> tt2 = timetable(Time,X2);
>> tic, synchronize(tt1,tt2,'first'); toc
Elapsed time is 0.086065 seconds.