Average timestamp 20 seconds interval data into hourly

2017 8 21
2 个回答
更新时间：2017 9 4
3 次查看（30 天）

0 个投票

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

请先登录，再回答此问题。

回答（2 个）

KL
KL 2017-8-21
编辑：KL 2017-8-21

0 个投票

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')
Andrei Bobrov
Andrei Bobrov 2017-8-21

0 个投票

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')

11 个评论
显示 9更早的评论 隐藏 9更早的评论

Zargham Ali
Zargham Ali 2017-8-21
编辑：Zargham Ali 2017-8-21
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
Andrei Bobrov 2017-8-21
编辑：Andrei Bobrov 2017-8-21
1) R2013a or R2013b ?
2) My code is working?
Zargham Ali
Zargham Ali 2017-8-23
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
KL 2017-8-23
You're not importing it correctly. Check
class(T)
class(T{:,1})
class(T{:,2})
KL
KL 2017-8-24
@Zargham Ali: Please attach a sample of your data(excel file).
Zargham Ali
Zargham Ali 2017-8-26
@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
Zargham Ali 2017-9-2
Hi Is there a solution to it? Can it be solved?
Regards
Andrei Bobrov
Andrei Bobrov 2017-9-2
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
Zargham Ali 2017-9-4
Thanks a lot for all the help. But still it is giving error. Undefined function 'datetime' for input arguments of type 'double'.
Andrei Bobrov
Andrei Bobrov 2017-9-4
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'});

请先登录，再进行评论。

类别

在 帮助中心 和 File Exchange 中查找有关 Spreadsheets 的更多信息

标签

Community Treasure Hunt

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

Start Hunting!

Translated by