When trying to fill timetable gaps retime puts everything NaN

I have a big timetable with different kind of data as 8xdouble, char arrays etc

The timetable is organized in seconds with data from several days but there are gap of the days that there is no data. I want to fill this gaps with NaN so whe I plot the timetable all the data is shown in block, without line filling the time gaps.

I'm doing:

TT_nan=retime(TT,'secondly','fillwithmissing');

And this adds the desired NaNs in the rows time gaps but also converts the rest of the columns in NaN

Transforms this:

Into this:

Any clue of what is happening or how to solve this?

Answer 1

[Star Strider]

That is what the 'fillwithmissing' option does.

If you want a different result, choose a different method.

[David Santos]

I think that 'fillwithmissing' "Fill gaps with missing data indicators", but in this case is filling all the table with NaN non only the gaps between time entries

[Star Strider]

I am not certain what is going on with that file, since I do not have it to work with.

Simulating that seems to work, although the fix for it is slightly cumbersome. The problem seems to be that the times are not exact seconds (adding random positive microseconds here to simulate that), and this causes problems for retime —

t = datetime(2023,9,6,14,25,0)+seconds(0:10).'+rand(11,1)*1E-6;     % Add Random Microseconds
data = rand(numel(t), 3)*1E+3;
T1 = table(data);
T1 = addvars(T1, t, 'before',1);
TT1 = table2timetable(T1)

TT1 = 11×1 timetable
             t                         data           
    ____________________    __________________________

    06-Sep-2023 14:25:00    301.66    12.612    73.811
    06-Sep-2023 14:25:01    174.04    677.78    427.61
    06-Sep-2023 14:25:02    876.32    108.19    661.78
    06-Sep-2023 14:25:03    634.08     702.4    811.49
    06-Sep-2023 14:25:04    12.557    869.81    252.27
    06-Sep-2023 14:25:05    650.03    183.81    297.01
    06-Sep-2023 14:25:06    815.07    916.13    791.38
    06-Sep-2023 14:25:07    584.42    5.2837    527.36
    06-Sep-2023 14:25:08      50.3    116.18    555.12
    06-Sep-2023 14:25:09    729.57    152.84    491.95
    06-Sep-2023 14:25:10     552.3    153.04    551.37

TT1r = retime(TT1, 'secondly', 'fillwithmissing')

TT1r = 12×1 timetable
             t                    data       
    ____________________    _________________

    06-Sep-2023 14:25:00    NaN    NaN    NaN
    06-Sep-2023 14:25:01    NaN    NaN    NaN
    06-Sep-2023 14:25:02    NaN    NaN    NaN
    06-Sep-2023 14:25:03    NaN    NaN    NaN
    06-Sep-2023 14:25:04    NaN    NaN    NaN
    06-Sep-2023 14:25:05    NaN    NaN    NaN
    06-Sep-2023 14:25:06    NaN    NaN    NaN
    06-Sep-2023 14:25:07    NaN    NaN    NaN
    06-Sep-2023 14:25:08    NaN    NaN    NaN
    06-Sep-2023 14:25:09    NaN    NaN    NaN
    06-Sep-2023 14:25:10    NaN    NaN    NaN
    06-Sep-2023 14:25:11    NaN    NaN    NaN

This reproduces the observed behaviour.

The retime function (for whatever reason) then considers them missing and apparently cannot effectively resample them. Artifically rounding them to whole seconds (that apparently requires regenerating the entire date and time entries from their components), then works with retime to produce the desired result.

Starting back from the original ‘T1’ table data and fixing the seconds —

s = floor(second(T1.t));                                            % Extract The 'second' Field, Eliminate The Fracional Seconds
[h,m] = hms(T1.t);                                                  % Return Components
[y,m,d] = ymd(T1.t);                                                % Return Components
T1.t = datetime(y,m,d,h,m,s)                                        % Re-Create New 'datetime' Array

T1 = 11×2 table
             t                         data           
    ____________________    __________________________

    06-Sep-2023 14:09:00    301.66    12.612    73.811
    06-Sep-2023 14:09:01    174.04    677.78    427.61
    06-Sep-2023 14:09:02    876.32    108.19    661.78
    06-Sep-2023 14:09:03    634.08     702.4    811.49
    06-Sep-2023 14:09:04    12.557    869.81    252.27
    06-Sep-2023 14:09:05    650.03    183.81    297.01
    06-Sep-2023 14:09:06    815.07    916.13    791.38
    06-Sep-2023 14:09:07    584.42    5.2837    527.36
    06-Sep-2023 14:09:08      50.3    116.18    555.12
    06-Sep-2023 14:09:09    729.57    152.84    491.95
    06-Sep-2023 14:09:10     552.3    153.04    551.37

TT1 = table2timetable(T1);
TT1r = retime(TT1, 'secondly', 'fillwithmissing')

TT1r = 11×1 timetable
             t                         data           
    ____________________    __________________________

    06-Sep-2023 14:09:00    301.66    12.612    73.811
    06-Sep-2023 14:09:01    174.04    677.78    427.61
    06-Sep-2023 14:09:02    876.32    108.19    661.78
    06-Sep-2023 14:09:03    634.08     702.4    811.49
    06-Sep-2023 14:09:04    12.557    869.81    252.27
    06-Sep-2023 14:09:05    650.03    183.81    297.01
    06-Sep-2023 14:09:06    815.07    916.13    791.38
    06-Sep-2023 14:09:07    584.42    5.2837    527.36
    06-Sep-2023 14:09:08      50.3    116.18    555.12
    06-Sep-2023 14:09:09    729.57    152.84    491.95
    06-Sep-2023 14:09:10     552.3    153.04    551.37

Apparently, if the times are not exactly in seconds, retime considers them missing. The fix for that is a bit ‘kludgy’, however this approach seems to work. This may be a ‘bug’ in retime that you incidentally discovered.

.