Average timestamp 20 seconds interval data into hourly

Hi I have a excel file having 2 columns of data i.e. timestamp and vlaues. The timestamp is at interval of 20 seconds. I want this to be averaged on hourly basis . Also data corresponding to timestamp column be averaged.

Also when these 2 columns are averaged. It should update excel file or write it to a new file.

Timestamp data 01.01.2015 00:00:00 10 01.01.2015 00:00:20 12 01.01.2015 00:00:40 14 01.01.2015 00:01:00 8

Answer 1

[KL]

dt = datetime('01.01.2015 00:00:00'):seconds(20):datetime('01.01.2015 05:00:00');
val = rand(901,1);
TT = timetable(dt',val);
TT2 = retime(TT,'hourly','mean')

Answer 2

[Andrei Bobrov]

for older version MATLAB:

T = readtable('data.xls');
[Y M D H] = datevec(T{:,1});
[~,~,T.groups] = unique([Y M D H],'rows');
T_temp = varfun(@mean,T,'G','groups');
T_out = T_temp(:,3:4);
T_out.Properties.VariableNames = T.Properties.VariableNames(1:2);
writetable(T_out,'data2.xlsx')

[Zargham Ali]

Thanks for answer. The MATLAB version is 2013. Is it also considering seconds?

Also the data i have is in regular 20 seconds interval. no repetition or missing timestamp

[Andrei Bobrov]

1) R2013a or R2013b ?

2) My code is working?

[Zargham Ali]

R2013b No it is giving an error: Error using table/varfun>dfltErrHandler (line 230). Undefined function 'sum' for input arguments of type 'cell'. Error in B (line 4) T_temp = varfun(@mean,T,'G','groups');

these are the errors

[KL]

You're not importing it correctly. Check

class(T)
class(T{:,1})
class(T{:,2})

[Zargham Ali]

Still not working :/

[KL]

@Zargham Ali: Please attach a sample of your data(excel file).

[Zargham Ali]

@KL: The format is like this. The data is quite big. This is just sample. The interval is regular. Nothing is missing or repeated. Regards

[Zargham Ali]

Hi Is there a solution to it? Can it be solved?

Regards

[Andrei Bobrov]

T = readtable('B.xls');
[Y,M,D,H] = datevec(T.Timestamp);
[a,~,c] = unique([Y,M,D,H],'rows');
T_out = table(datetime([a,0,0],'F','uuuu-MM-dd'),...
                   accumarray(c,T.data,[],@mean),'v',{'Timestamp','data'});

[Zargham Ali]

Thanks a lot for all the help. But still it is giving error. Undefined function 'datetime' for input arguments of type 'double'.

[Andrei Bobrov]

T = readtable('B.xls');
T.Timestamp = datetime(T{:,1},'I','dd.MM.uuuu HH:mm:ss');
[Y,M,D,H] = datevec(T.Timestamp);
[a,~,c] = unique([Y,M,D,H],'rows');
T_out = table(datetime([a,zeros(size(a,1),2)],'F','uuuu-MM-dd HH:mm'),accumarray(c,T.data,[],@mean),'v',{'Timestamp','data'});