How to convert multiple date formats in a loop?

I am processing data from an instrument, where the time read out is something like this-

'29/08/2014 14:31'

'31:41.0'

'31:41.1'

'31:41.1'

Where the full date stamp repeats around every 20 readings, but this isn't constant, so I cannot just select the data every 20th reading.

However, when using datestr on the '31:41.0' type readings, it comes out as the 1st January 2014 00:31:41.0.

I have a method of converting '31:41.0' readings to the correct date by taking the difference between the first long reading and the 1st Jan 2014 datenums and then using add time to correct for the minutes but only for one cell at a time.

What I would like to do is loop through the data and do something like;

if date stamp is in the long format- use method 1- datenum(data(1,1),'dd/mm/yyyy HH:MM')

else, use method 2

so I end up with a new column of the correct datenums.

The size of the matrix does vary for the filesets.

I have no idea how to go about this, so any help and suggestions would be appreciated. Thanks!

[per isakson]

Will there always be a long format string the first time after the hour has been incremented?

[Hannah]

The instrument produces a full date stamp every second.

Answer 1

[Stephen23]

It might be useful to recreate the complete date-string first, before trying to convert into a date number/vector. This would allow double-checking of the date values, and their use elsewhere (e.g. in a figure), although it might be a little slower to compute. Using this concept, one possibility is to split the date string using regexp, fill the missing dates using some indexing, then re-combine using strcat:

A = {'29/08/2014 14:31';'31:41.0';'31:41.1';'31/08/2014 10:05';'05:40.3';'05:40.4'};
B = regexp(A,'(\d{2}/\d{2}/\d{4} \d{2}:)?(\d{2})(:\d{2}\.\d+)?','tokens','once');
B = vertcat(B{:});
X = ~cellfun('isempty',B(:,1));
Y = B(X,1);
B = strcat(Y(cumsum(X)),B(:,2),B(:,3));

D(X,:) = datevec(B(X),'dd/mm/yyyy HH:MM');
D(~X,:) = datevec(B(~X),'dd/mm/yyyy HH:MM:SS');

Cell array B is a version of A with all strings completed to include dates. You can use the indexing array X to select the relevant timestamps (e.g. to create the matrix of date vectors D). Note also that this method assumes that:

• The first cell in A contains a date+hour and the minutes.
• A date applies to all following hours+minutes, until the next date.
• You are not interested in the fraction of seconds.

Answer 2

[Andrei Bobrov]

A={'29/08/2014 14:31'
'31:41.0'
'31:41.1'
'31:41.18'
'29/08/2014 18:23'
'23:23.0'
'23:45.2'};

d = regexp(A,':\d{2}\.\d*','match');
t =  cellfun('isempty',d);
ii = cumsum(t);
A1 = A(t);
dout = zeros(numel(A),1);
dout(~t) = datenum(strcat(A1(ii(~t)),[d{:}]'),'dd/mm/yyyy HH:MM:SS.FFF');
dout(t) = datenum(A1,'dd/mm/yyyy HH:MM');

[Oleg Komarov]

I like this approach, but it'd more useful if it had comments.

Something along these lines:

% Match only ':ss.f' part
d = regexp(A,':\d{2}\.\d*','match');

% Index full timestamp to the minute
t =  cellfun('isempty',d);

% Expand full timestamp to consecutive ones
ii = cumsum(t);

% Map partial timestamp to corresponding full one
A1 = A(t);
A1(ii(~t)) % for clarity

dout = zeros(numel(A),1);

% Cat partial with corresponding full one and convert
dout(~t) = datenum(strcat(A1(ii(~t)),[d{:}]'),'dd/mm/yyyy HH:MM:SS.FFF');

% Convert full
dout(t) = datenum(A1,'dd/mm/yyyy HH:MM');

Feel free to delete the comment if you amend the answer.

Answer 3

[per isakson]

And a third alternative assuming the data is in a textfile

    >> [ sdn, val ] = read_multiple_date();
    >> whos sdn val
      Name       Size            Bytes  Class     Attributes
      sdn       16x1               128  double              
      val       16x2               256  double

where

    function    [ sdn, val ] = read_multiple_date()

        buffer    = fileread( 'multiple_date.txt' );
        [cac,del] = strsplit( buffer, '\d{2}/\d{2}/\d{4} \d{2}:'  ... 
                          ,   'DelimiterType', 'RegularExpression' );

        % skip the rows preceeding the first long format timestamp
        cac(1) = [];     

        sdn = zeros( 0, 1 );
        val = zeros( 0, 2 );

        for jj = 1 : length( cac )
            buf = textscan( cac{jj}, '%s%f%f', 'CollectOutput', true );
            buf{1}{1} = strcat( buf{1}{1}, ':0.0' );
            abc = char(buf{1});
            abc( abc == ':' ) = ',';            % prepare for str2num
    %       vec = datevec( del{jj}, 'dd/mm/yyyy HH:' );
            vcv = textscan( del{jj}, '%f%f%f%f%f'   ...
                        ,   'Delimiter', '/:'       ...
                        ,   'CollectOutput', true   );
            vec = cat( 2, vcv{:}(1,[3,2,1,4,5]), 0 );    

            vec = vec( ones(size(abc,1), 1 ), : ); % tony's trick   
            vec(:,[5,6]) = str2num( abc );
            num = datenum( vec );
            dnm = cat( 1, 0, diff( num ) );

            if any( dnm < 0 )
                vec(:,4) = vec(:,4) + cumsum( double( dnm < 0 ) ); 
                num = datenum( vec );
            end
            sdn = cat( 1, sdn, num );
            val = cat( 1, val, buf{2} ); 
        end
    end

and

    31:41.0            1   2
    31:42.1            1   2
    29/08/2014 14:31   1   2
    31:41.0            1   2
    31:42.1            1   2
    31:43.1            1   2
    11:44.1            1   2
    12:45.1            1   2
    29/08/2014 15:31   1   2
    31:41.0            1   2
    31:42.1            1   2
    31:43.2            1   2
    31:44.0            1   2
    31:45.1            1   2
    31:46.2            1   2
    29/08/2014 16:31   1   2
    31:41.0            1   2
    31:42.1            1   2

Comments

• loop over all data blocks commencing with a long format timestamp
• the two first rows of this text file are skipped
• datevec is much slower than textscan to parse the long format timestamp
• notice the 7th and 8th row in the text file