MATLAB: Averaging and summing parts of a column based on date

summin parts of columns

Greetings Matlab community

I have some very large data sets and I am wanting to sum and average parts of a table column based on the date. I have multiple readings of e.g. temperature for a given date and want to both sum and average these for each given date and get a corresponding table with the results.

A VERY cut down version of the data is attached and I want to get a result that has three columns – the date, the average Maxium temperature and the Total max temp for each date.

Much appreciated

Best Answer

Ten hours without an answer. Here is the result from my first experiment with timetable. See retime Resample or aggregate data in timetable, and resolve duplicate or irregular times

%%




T = readtable('Matlab question.xlsx');
%%
TT = table2timetable( T, 'rowTimes','Var1' );
%%
dm = retime( TT, 'daily','mean' );
%%
dx = retime( TT, 'daily','max' );
%%
ds = retime( TT, 'daily','sum' );

Related Solutions

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

MATLAB: Date computations in a table

LC.date = datetime(sprintfc('%d',LC.date),'InputFormat','yyyyMMdd');
LC.month_year = [month(LC.date),year(LC.date)]

Best Answer

Related Solutions

MATLAB: Average data by time

MATLAB: Date computations in a table

Related Question