Average data by time

Hi all,

I have some problems with a csv file. It's composed by a first column with time and the other by temperature values.

I'd like to create average value for each column by date (now the values are daily, i wanna monthly).

Do you have any suggestions?

I tried with retime comand but I did not have success.

clear all;
close all;

a = readtable ('COR_0200.csv');
tt = timetable(datetime(a.Var1,'InputFormat','yyyy-MM-dd HH:mm:ss')', a);
ttMean = retime(tt,'monthly','mean');

Thanks,

Answer 1

[per isakson]

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 ... .

[Stefano Alberti]

Thanks, yuo can also report how to replace the NA values please?

Because right now I am not able to reach you results.

I tried like this but MatLab gave me an error:

'Error using table2timetable (line 57)

Input table must contain datetime or duration vector for row times.'

Data = readtable ('COR_0200.csv');
B = standardizeMissing(Data,'NA');
F = fillmissing(B,'constant','NaN');
Data = F(2:end,:);
%%
tt = table2timetable(Data);
%%
ttMean = retime(tt,'monthly','mean');

Many thanks!

[per isakson]

I added to my answer

Answer 2

[Sujay C Sharma]

Hi,

Using the table2timetable function prior to using retime seemed to work when I tried to create a monthly average value for each column using the csv file you have attached. Hopefully this helps you out also.

Data = readtable('COR_0200.csv');
TT = table2timetable( Data, 'rowTimes','Var1' );
MonthlyMean = retime( TT, 'monthly','mean' );

[Stefano Alberti]

Thanks, but I need all columns