Adding rows to matrix conditionally

I have long datafiles that I need to alter and cannot figure out how to do this.

The simplified version of what I'm trying to do is:

x =  1 20
     3 50
     6 10

Column 1 is time (t) (sec 1, 3, 6) and col 2 the values.

I want to create a new matrix with time axis with 1 sec interval and that the values (col 2) are repeated until next (t) ( in col1)

The resulting matrix should be:

     x2 = 1 20
          2 20
          3 50
          4 50
          5 50
          6 10

Sorry about the elementery question, I'm a newby

[Star Strider]

I will defer to Guillaume’s Answer, since he posted the solution first. The data file is attached.

Answer 1

[Guillaume]

We don't have enough details to give you a complete answer but what you want to do should be easily done by

• reading your file as a timetable. Could be as easy as:

data = readtimetable(yourfile);

depending on the actual file

• retime the timetable to 1 second interval, which is simply:

resampled = retime(data, 'secondly', 'previous');  %retime in one second interval using the previously known value for the missing times

• save into a new file

writetimetable(resampled, 'newfile.csv');

for example

[Guillaume]

Now that we have example data, it's possible to give more details. The gist of my answer still stand: import the data as timetable, then one-liner retime to resample the data with whichever time interval you want.

The difficulty with your file is that the actual time/date is split over several variables which is not ideal. In addition, the time column being in 24 hours format but still having a PM indicator for afternon but no AM indicator for morning really complicates things a bit.

opts = detectImportOptions('data.csv');  %have to use import options because of the wonky format of the file
opts = opts.setvaropts(3, 'Type', 'char');  %prevent decoding as duration which would fail because of the AM/PM
data = readtable('data.csv', opts);  %import as table
data.(3) = duration(strrep(data.(3), ' PM', ''));  %get rid of the useless PM, then convert to duration
data.DateTimeUnified = data.(2) + data.(3) + seconds(data.(4) / 1e6);  %convert microseconds to seconds and add the whole lot
data.DateTimeUnified.Format = 'dd MMM yyyy HH:mm:ss.SSSSSS';  %use whichever display format you prefer
data(end, :) = []; %get rid of the last row which is nonsense
data(:, 2:4) = [];  %optionally remove original date/time columns
tdata = table2timetable(data, 'RowTimes', 'DateTimeUnified');  %convert to timetable

and then as said, retime to get the data in interval of 30 seconds:

resampled = retime(tdata, 'regular', 'previous', 'TimeStep', seconds(30));

[HOl]

Thank you very much for the help. Clearly this would have been above my skills to figure out!

When I try, the first line passes ok but the second yields an error:

>> opts = detectImportOptions('data.csv');  %have to use import options because of the wonky format of the file
>> opts = opts.setvaropts(3, 'Type', 'char');
Error using matlab.io.internal.functions.SetVarOpts/setType (line 140)
The 'Type' property cannot be set with SETVAROPTS. Use the following instead:
  opts = setvartype(opts,...,type)

Error in matlab.io.internal.shared.VarOptsInputs/set.Type (line 64)
            obj.Type_ = setType(obj,val);

Error in matlab.io.internal.functions.ExecutableFunction/validateArguments (line 82)
            func.(name) = paramStruct.(name); % validate by object setter

Error in matlab.io.internal.functions.ExecutableFunction/validate (line 103)
        [func, supplied, additionalArgs, results] = func.validateArguments(varargin{:});

Error in matlab.io.internal.functions.SetVarOpts/validate (line 105)
        [func, supplied, additionalArgs] = [e-mail](func,varargin{:});

Error in matlab.io.internal.functions.SetVarOpts/validateAndExecute (line 73)
        [func, supplied] = validate(func,varargin{:});

Error in matlab.io.ImportOptions/setvaropts (line 402)
            opts = func.validateAndExecute(opts,varargin{:});

[Guillaume]

Which version of matlab are you using? It must be a version before setvaropts allowed you to specify the type.

Do what it says then, replace:

opts = opts.setvaropts(3, 'Type', 'char');

by

opts = opts.setvartype(3, 'char');

It's possible you may get more errors if you version is too old.

"Clearly this would have been above my skills to figure out!"

Most of the code is just there to cope with the poor formatting of the time in your file. Had that been written properly as a 24h HH:mm without a sometimes missing PM indicator, a simple readtable would have worked. If the date and time had been combined in just one column, it would have been even easier to read. If you have the opportunity to change the output format of whatever generated the file, I'd encourage you to do so. It would allow you to replace the 9 lines of code by just:

tdata = readtimetable('data.csv');
%or in versions of matlab without readtimetable
%tdata = table2timetable(readtable('data.csv'));

[HOl]

The version I have is 2019b (trial version).

I had noticed that this time column was super wacky, but the data is the output of a software that unfortunately does not let me change anything in how it exports.

I tried the algo you recommend and it goes through without error (victory!) and the output is ALMOST what I need. The number of rows is correct and the states have been repeated correctly.

The but is that the time column, now "dateTimeUnified" does have 30 s increments but does not start at the same time as the original time.

In the original data the start time in first row was "21:18:52" so I need the "new time" go:

newtime = 21:18:52
           21:19:22
           21:19:52
           21:20:22, etc

Is it possible to make the "DateTimeUnified" start from the first time stamp?

[Guillaume]

You can construct a new time vector starting at the time of your choosing and pass that to retime instead of letting it construct it:

newtimes = (tdata.DateTimeUnified(1):seconds(30):tdata.DateTimeUnified(end)+seconds(30))';
resampled = retime(tdata, newtimes, 'previous');

[HOl]

Excellent, that works. Thank you very much!!

For any other newby's out there with wonky data here is the final code:

>> opts = detectImportOptions('data.csv');  %have to use import options because of the wonky format of the file
>> opts = opts.setvartype(3, 'char');
>> data = readtable('data.csv', opts);  %import as table
>> data.(3) = duration(strrep(data.(3), ' PM', ''));  %get rid of the useless PM, then convert to duration
>> data.DateTimeUnified = data.(2) + data.(3) + seconds(data.(4) / 1e6);  %convert microseconds to seconds and add the whole lot
>> data.DateTimeUnified.Format = 'dd MMM yyyy HH:mm:ss.SSSSSS';
>> data(end, :) = []; %get rid of the last row which is nonsense
>> tdata = table2timetable(data, 'RowTimes', 'DateTimeUnified');  %convert to timetable
>> newtimes = (tdata.DateTimeUnified(1):seconds(30):tdata.DateTimeUnified(end)+seconds(30))';
>> resampled = retime(tdata, newtimes, 'previous');

Answer 2

[Andrei Bobrov]

out = [(x(1,1):x(end,1))',repelem(x(:,2),[diff(x(:,1));1])];