MATLAB Answers

Robert
0

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

Asked by Robert
on 19 Sep 2017
Latest activity Commented on by 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!

  0 Comments

Sign in to comment.

1 Answer

Answer by Akira Agata
on 19 Sep 2017
 Accepted Answer

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

Show 1 older comment
Hi Robert-san,
The function nnz returns number of nonzero matrix elements. So, in this case, it corresponds to "K000x_Number"
nnz is a built-in function of matlab that returns the number of nonzero matrix elements in the input. It should not be anything.

Sign in to comment.