Average data by time

20 vues (au cours des 30 derniers jours)
Stefano Alberti
Stefano Alberti le 17 Juin 2020
Commenté : per isakson le 18 Juin 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,

Réponse acceptée

per isakson
per isakson le 17 Juin 2020
Modifié(e) : per isakson le 18 Juin 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 commentaires
Stefano Alberti
Stefano Alberti le 17 Juin 2020
Modifié(e) : per isakson le 18 Juin 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 le 18 Juin 2020
I added to my answer

Connectez-vous pour commenter.

Plus de réponses (1)

Sujay C Sharma
Sujay C Sharma le 17 Juin 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' );
  1 commentaire
Stefano Alberti
Stefano Alberti le 17 Juin 2020
Thanks, but I need all columns

Connectez-vous pour commenter.

Catégories

En savoir plus sur Logical dans Help Center et File Exchange

Community Treasure Hunt

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

Start Hunting!

Translated by