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

### Robert (view profile)

on 19 Sep 2017
Latest activity Commented on by Robert

on 21 Sep 2017

### Akira Agata (view profile)

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!

### Akira Agata (view profile)

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

Show 1 older comment
Akira Agata

### Akira Agata (view profile)

on 19 Sep 2017
Hi Robert-san,
The function nnz returns number of nonzero matrix elements. So, in this case, it corresponds to "K000x_Number"
Guillaume

### Guillaume (view profile)

on 19 Sep 2017
nnz is a built-in function of matlab that returns the number of nonzero matrix elements in the input. It should not be anything.
Robert

### Robert (view profile)

on 21 Sep 2017
Thanks, Akira and Guillaume, very appreciated!