Calculating mean for time series

In matrix A, I have two columns:

Col 1 = Time (in Excel serial date format e.g. 4.264400001156250e+04 = 01-Oct-2016 00:00:00) Col 2 = Measurement

The time are in intervals of few seconds, and matrix A has about 400,000 rows, i.e. A(400000,2).

My question is, how can I script to compute the average of the Measurement variable for each day? I can do this in Excel, but it seems awfully time consuming.

Thank you.

Answer 1

[Star Strider]

I would begin by converting all the dates to integer date numbers:

de = datetime(4.264400001156250e+04, 'ConvertFrom','excel');            % Convert From Excel Date
dn = datenum(de);                                                       % Date Number (Includes Fractional Days)
day_only = fix(dn);                                                     % Date Number (Integer Days Only)

After that, I would use the unique function with the third output, and use it as the index variable for the accumarray call, something like this:

[Du,~,di] = unique(Data, 'stable');
Means = accumarray(di, Data, [], @mean);
Out = [datevec(Du)  Means]

Without your data, this is only a ‘sketch’ of sorts. It would have to be modified to work with your actual data.

[BenL]

I cant seem to run datetime(). Is it because I am using R2013a?

[Star Strider]

The datetime (link) functions were introduced in R2014b. You have a function in the Financial Toolbox (that I don’t have — so I had to look it up in the online documentation — and whose name I don’t remember — it could be x2mdate) that will do the same operation. (You referred to it in one of your other posts.)

Substitute it for my datetime call, and use that output instead to create a datenum array that you can then use in my code without significant further modification. Since I don’t have that particular function, if you have problems with a direct substitution, I will need to know what the output of that function is and what class of variable it is.

[BenL]

by the way, what is a good approach to calculate the mean, using an arbitrary time? e.g. every hour, instead of daily?

[Star Strider]

That depends on how your original data exist. I am assuming here that they have hour, minute, and second representations as well.

After first converting them to date numbers, the next step is to convert them to a (Nx6) matrix of date vectors, then substitute the last two columns of that matrix with [0 0], and convert back to date numbers. The rest of my code then remains unchanged.

Example:

dnv = [now + (0:23)/24]';                                       % Create Data (Add Hours)
dv6 = datevec(dnv);                                             % Date Vector Matrix (6 Columns)
dn4 = datenum([dv6(:,1:4) repmat([0 0],size(dv6,1),1)]);        % Substitute Last Two Columns With [0 0] & Create Dare Number Vector
check = datevec(dn4(1:5))                                       % Check Result (Delete)

The rest of my code would then be:

[Du,~,di] = unique(dn4, 'stable');
Means = accumarray(di, Data, [], @mean);
Out = [datevec(Du)  Means]

The 'stable' argument is necessary so that the dates and whatever are in your array that I call ‘Data’ here retain their correct relationships. Otherwise, the mean values calculated by accumarray will be incorrect.

NOTE — This is UNTESTED CODE. This will probably work without significant modification. By necessity, it will require some slight modifications and testing to work with your data. (There was an error in the unique call in my original Answer. I have corrected it here.)

[BenL]

may i ask, what do you mean 'retain their correct relationships'? are you referring to the rows of data?

[Star Strider]

Yes. The 'stable' argument keeps the dates and times (and all references to them) in the original order in the vector, rather than sorting them, which is the default behavior. This creates a ‘di’ vector that will correctly locate the date indices in it with the data associated with the dates and times. The output of the accumarray call will then be correct.

See the documentation for the unique function for details.