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

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!

Answer 1

[Akira Agata]

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

[Robert]

Thanks Akira!

Could you let me know what 'nnz' should be?

[Akira Agata]

Hi Robert-san,

The function nnz returns number of nonzero matrix elements. So, in this case, it corresponds to "K000x_Number"

[Guillaume]

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]

Thanks, Akira and Guillaume, very appreciated!