MATLAB: How to generate a time variable from CSV and set new time steps

csvretime

Hi all,

here is my little problem: I have a CSV-file which contains several columns. I only need column 4:11 (date, time, act,…,pim). Unfortunalty the time column is written in 10 sec steps but I need it in 5 sec steps. And by expanding the time steps, every other column needs to be interpolated (for the new empty fields every 5 secs).

I tried to use the

retime

command, but I'm not able to create 1 table column out of date and time to convert it into a timetable and use retime.

Maybe someone could help me solving this problem and explain how he/she did that?

Thanks a lot!

Best Answer

The solution would be like this:

% Read CSV file
T = readtable('S1b2-20-17_AWedited.csv','Format','%s%s%f%s%s%f%f%f%f%f%f');
% Make datetime array and add to the table
str = strcat(T.Date,'-',T.Time);
T.DateTime = datetime(str,'InputFormat','MM/dd/yy-HH:mm:ss');
% Convert the table into timetable
TT = table2timetable(T);
% Remove unrequired columns
TT(:,1:5) = [];
% Make new time array (5 sec resolution)
newT = TT.DateTime(1):seconds(5):TT.DateTime(end);
% Retime with interpolate
TT = retime(TT,newT,'linear');

Related Solutions

MATLAB: Merging date and time columns in a timetable

tt = table2timetable( YourTable, 'RowTimes', datetime(YourTable.Date) + duration(YourTable.Time) );

MATLAB: Average data by time

I encountered two problems with the csv-file

the first line, which I just deleted
the strings, NA, which I replaced by nan

(I'm sure, this brute force can be replaced with options to readtable)

After that

%%




a = readtable ('COR_0200.csv');
tt = table2timetable( a );
%%
ttMean = retime(tt,'monthly','mean');
%%
ttMean(1:3,1:6)

outputs

ans =
  3×6 timetable
           Var1             Var2       Var3       Var4       Var5       Var6        Var7  
    ___________________    _______    _______    _______    _______    _______    ________
    2000-11-01 00:00:00      -1.93    -1.7106    -1.4689    -1.2977    -1.0718    -0.90138
    2000-12-01 00:00:00    -1.7591    -1.5721    -1.3575    -1.2406    -1.1437     -1.0818
    2001-01-01 00:00:00    -2.3507    -2.0724    -1.7183     -1.514    -1.3615     -1.2918
>>

In response to comment

"report how to replace the NA values please?" I did it interactively with an editor, which is the quickest and simplest way to do it if you need to read a few files. An alternative is to read a lot of fineprint in the documentation.

The goal when reading a file with redatable() should be to create a table with columns of suitable data types, i.e datetime, string, double, etc., not all character arrays. There are two different syntax

T = readtable(filename,Name,Value) creates a table from a file with additional options specified by one or more name-value pair arguments. This mimics the syntax of textscan(), which old-timer might like.
T = readtable(filename,opts) creates a table using the import options opts. This uses more magic, which is good when it works and confusing when it doesn't.

An example of each

%%
% magic takes care of "NA" and interprets the first column as date-time  
opt = detectImportOptions('COR_0200.csv');  
opt.DataLines = [2,inf];                    % skip the first line

T1  = readtable( 'COR_0200.csv', opt );
tt1 = table2timetable( T1 );
tm1 = retime( tt1, 'monthly','mean' );
%%
T3  = readtable( 'COR_0200.csv'       ...  % magic interprets the first column as date-time  
               , 'TreatAsEmpty', 'NA' ...  % idicate how to handle "NA"   
               , 'HeaderLines' , 1    );   % skip the first line
tt3 = table2timetable( T3 );
tm3 = retime( tt3, 'monthly','mean' );

Lastly there is the experimenting tactic, which I dislike ... flame war ... .

Best Answer

Related Solutions

MATLAB: Merging date and time columns in a timetable

MATLAB: Average data by time

Related Question