how to average the minutes data to hourly data if the data is missing for some minutes(samples) in between

hi everyone,

I am having data every two minutes time interval with the format

9/10/2013 4:00 721.19141 4.999999523 0 35.29541 -300 -0.011333227

9/10/2013 4:02 721.67969 4.999999523 0 35.43457 -300 0.561000109 ....

Now i would like to average the data to each hour and save.

Now the problem is there are some data missing. how to overcome this problem??

date and time are in same column, the remaining data are in different columns.

 please see attached file

Answer 1

[Andrei Bobrov]

n = xlsread('Book1.xlsx');
n(:,1) = n(:,1) + datenum('30.12.1899','dd.mm.yyyy');
[Y,M,D,H] = datevec (n(:,1));
[c0,~,c1] = unique([Y,M,D,H],'rows');
[ii,jj] = ndgrid(c1,1:size(n,2)-1);
mm = accumarray([ii(:),jj(:)],reshape(n(:,2:end),[],1),[],@mean);
out = [c0, mm];

[reddy]

for second line matlab says Undefined function 'plus' for input arguments of type 'cell'.

[reddy]

i think there is no need of second line in the code...

[Ingrid]

yes there is, it is because matlab and excel use a different date as starting point to calculate the datenumber

also see: http://nl.mathworks.com/help/finance/x2mdate.html

[reddy]

thank you so much guys...

especially thanks to Andrei Bobrov

Answer 2

[Ingrid]

first read in the excel data in matlab then use datenum on the first column to get the date in matlab use histc and accumarray to get the average data per hour