Import timestamps with alternating date formats

Hello!

I have .csv data files that contain gps coordinates with timestamps. I want to import these data files into MATLAB. The problem I am having is the timestamp has an alternating date format, or at least matlab import is treating it as such. Timestamps look like:

Timestamp

16:15:29

16:15:29.500000

16:15:30

16:15:30.500000

etc.

Using import tool I can set a custom date format to HH:mm:ss.SSSSSS but this doesn't work for the timestamps that don't include the .SSSSSS

The import tool assigns NaT to those timestamps. Any ideas?

[Stephen23]

If there are no date units then DURATION objects may be more suitable than DATETIME:

T = {'16:15:29'; '16:15:29.500000'; '16:15:30'; '16:15:30.500000'}

T = 4x1 cell array
    {'16:15:29'       }
    {'16:15:29.500000'}
    {'16:15:30'       }
    {'16:15:30.500000'}

M = str2double(split(T,':'));
D = duration(M, 'Format','hh:mm:ss.SSSS')

D = 4x1 duration array
   16:15:29.0000
   16:15:29.5000
   16:15:30.0000
   16:15:30.5000

Answer 1

[Star Strider]

It does not actually appear to be alternating, although the trailing zeros on some may indicate that the time is not exactly those values and instead something close to them (perhaps an additional second).

This is a bit more convoluted than I’d like it to be, however itt has the virtue of working —

Timestamp = {'16:15:29'
              '16:15:29.500000'
              '16:15:30'
              '16:15:30.500000'}

Timestamp = 4x1 cell array
    {'16:15:29'       }
    {'16:15:29.500000'}
    {'16:15:30'       }
    {'16:15:30.500000'}

TimestampMtx = str2double(split(Timestamp,':'))                                         % Convert To Numeric Array

TimestampMtx = 4x3

16.0000 15.0000 29.0000 16.0000 15.0000 29.5000 16.0000 15.0000 30.0000 16.0000 15.0000 30.5000

<mw-icon class=""></mw-icon>

<mw-icon class=""></mw-icon>

Timestamps = compose('%2d:%2d:%4.1f',TimestampMtx)                                      % Convert To Cell Array Of Strings

Timestamps = 4x1 cell array
    {'16:15:29.0'}
    {'16:15:29.5'}
    {'16:15:30.0'}
    {'16:15:30.5'}

Timestamp2 = datetime(Timestamps, 'InputFormat','HH:mm:ss.S', 'Format','HH:mm:ss.S')    % 'datetime' Array

Timestamp2 = 4x1 datetime array
   16:15:29.0
   16:15:29.5
   16:15:30.0
   16:15:30.5

.

[Chris Feist]

Thank you!

[Star Strider]

As always, my pleasure!

[Stephen23]

Without the intermediate COMPOSE:

T = {'16:15:29'; '16:15:29.500000'; '16:15:30'; '16:15:30.500000'}

T = 4x1 cell array
    {'16:15:29'       }
    {'16:15:29.500000'}
    {'16:15:30'       }
    {'16:15:30.500000'}

M = str2double(split(T,':'));
D = datetime(0,0,0,M(:,1),M(:,2),M(:,3), 'Format','HH:mm:ss.SSSS')

D = 4x1 datetime array
   16:15:29.0000
   16:15:29.5000
   16:15:30.0000
   16:15:30.5000

Answer 2

[Steven Lord]

A different approach is to convert all the data with the input format of the first entry in the list, then convert all the data that resulted in NaT values in the datetime array using a different input format or formats.

Timestamp = {'16:15:29'
              '16:15:29.500000'
              '16:15:30'
              '16:15:30.500000'};
format1 = 'HH:mm:ss';
dt = datetime(Timestamp, InputFormat = format1, Format = format1)

dt = 4x1 datetime array
   16:15:29
   NaT     
   16:15:30
   NaT     

This shows that entries 2 and 4 in Timestamp were not compatible with the input format in format1. So let's try converting those with a slightly different input format.

format2 = 'HH:mm:ss.SSSSSS';
dt(isnat(dt)) = datetime(Timestamp(isnat(dt)), InputFormat = format2)

dt = 4x1 datetime array
   16:15:29
   16:15:29
   16:15:30
   16:15:30

To confirm that entries 2 and 4 in dt have the fractional seconds:

dt.Format = format2

dt = 4x1 datetime array
   16:15:29.000000
   16:15:29.500000
   16:15:30.000000
   16:15:30.500000

If dt had still contained any NaT values I could have repeated this process with a third, fourth, etc. input format.

[Stephen23]

This topic has come up a few times before. Perhaps a useful enhancement would be to let DATETIME's "InputFormat" value be a string array or cell array of chars, and which provides exactly this behavior.