How to obtain statistics between rows from columns with similar variable name when using timetable?

1 view (last 30 days)
Robert
Robert on 19 Sep 2017
Commented: Robert on 21 Sep 2017
Hello,
Wondering how to obtain the mean (and number), for each row, from columns with a similar variable name (see below, K000?), when working with a timetable. In my case, I have a 6000 (times) by 10000 (columns) timetable. Here is a small example...
Time K0001_AA K0001_AB K0002_AA K0003_AA K0003_AB
'10-Mar-2002 00:00:00' 23 43 0.9 45 12
'11-Mar-2002 00:00:00' 12 56 3.8 12 1.2
'12-Mar-2002 00:00:00' 76 12 6 1 0.01
'13-Mar-2002 00:00:00' 22 57 14 0.33 1
'14-Mar-2002 00:00:00' 198 34 98 0.34 10
'15-Mar-2002 00:00:00' 34 23 12 45 0.4
...
As a result of a loop, I would like to end with a timetable that shows the mean of the rows, from similar columns name (K0001, K0003), and the # of columns used to generate this result BUT keeping variables with no similar columns as K0002 in this example.
Time K0001_Mean K0001_Number K0002_Mean K0002_Number K0003_Mean K0003_Number
'10-Mar-2002 00:00:00' 33 2 0.9 1 28.5 2
'11-Mar-2002 00:00:00' 34 2 3.8 1 6.6 2
'12-Mar-2002 00:00:00' 44 2 6 1 0.505 2
'13-Mar-2002 00:00:00' 39.5 2 14 1 0.665 2
'14-Mar-2002 00:00:00' 116 2 98 1 5.17 2
'15-Mar-2002 00:00:00' 28.5 2 12 1 22.7 2
...
Thanks!

Accepted Answer

Akira Agata
Akira Agata on 19 Sep 2017
Assuming that your timetable is TT, the following code can generates what you want to obtain.
list = TT.Properties.VariableNames;
for kk = 1:3
str = ['K',num2str(kk,'%04d'),'_'];
idx = contains(list, str);
TTout{:,[str,'_Mean']} = mean(TT{:,idx},2);
TTout{:,[str,'_Number']} = repmat(nnz(idx), [height(TT), 1]);
end
  4 Comments

Sign in to comment.

More Answers (0)

Community Treasure Hunt

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

Start Hunting!