Average data by time

20 views (last 30 days)
Stefano Alberti
Stefano Alberti on 17 Jun 2020
Commented: per isakson on 18 Jun 2020
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,

Accepted Answer

per isakson
per isakson on 17 Jun 2020
Edited: per isakson on 18 Jun 2020
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 ... .
  2 Comments
Stefano Alberti
Stefano Alberti on 17 Jun 2020
Edited: per isakson on 18 Jun 2020
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
per isakson on 18 Jun 2020
I added to my answer

Sign in to comment.

More Answers (1)

Sujay C Sharma
Sujay C Sharma on 17 Jun 2020
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' );

Community Treasure Hunt

Find the treasures in MATLAB Central and discover how the community can help you!

Start Hunting!