How do I take repeat the calculation of average column?

Hi suppose I have the above data, how do I repeat the calculation for the average column of data 1, data 2 and data 3 respectively suppose that the 0-4 means 0 to 4 seconds and 4-8 means 4 to 8 seconds and each data have different length. I wish to do this for 100 data. I wish to rename the data to just data1,data2 and data3...

my final result should be something as shown below:

Answer 1

[Rik]

splitapply will do the trick. You can fairly easily find the number between 'data' and '--', so creating the group IDs shouldn't be too hard.

If you need help with implementing this, share the data instead of an image.

[tinkiewinkie]

Hi thank you for your reply, I have attached the data as Book2.xlsx. Can you kindly show the implementation steps? Thank you!

[Rik]

1. Read the data to Matlab variables.
2. Parse the text label to a group ID.
3. Either use splitapply in a loop, or write a custom function that returns the mean by column.

[num,str]=xlsread('Book2.xlsx');
tokens=regexp(str,'(data[0-9])+--','tokens');
tokens=[tokens{:}];tokens=[tokens{:}];%unwrap
[~,~,G]=unique(tokens);%convert to positive integer
splitapply(@mean,num,G)

[tinkiewinkie]

Thank you for the implementation! Really appreciate it!

1) May I also ask how can I show data1,data2 and data3 in the first column? As right now the implemented code is only showing the mean numerical values.

2)

tokens=regexp(str,'(data[0-9])+--','tokens');

only matches data 1 to 9, how do I match data 1 to 100 or 1 to 1000?

[Rik]

You can mix data types, so will have to chose:

1. use a cell array: [ tokens , num2cell(output) ]
2. or use a table (see documentation for explanation and examples)

For the regex I see I made a small mistake: it matches the entire token 1 or more times, not just the number. If you move the + to directly after the square bracket it should work for 10 and larger.

If my answer solved your issue, please consider marking it as accepted answer, if not, feel free to comment with your remaining issues.

[tinkiewinkie]

Yes it does work for 10 and larger now but now the G variable does not run in order.

[~,~,G]=unique(tokens);%convert to positive integer

it should be value 2 at row 8 to row 10 and value 3 at row 11 to 16

[Rik]

That is because it sorts them as text. You can also convert everything to a double:

tokens=regexp(str,'data([0-9]+)--','tokens');
[~,~,G]=unique(str2double(tokens));%convert to positive integer

[tinkiewinkie]

I tried running the code below with the data file of 31 data and 13 columns but the G is just counting down with rows. row 1 with value 1, row 2 with value 2 now....

[num,str]=xlsread('output2.xlsx');
tokens=regexp(str,'(sound[0-9]+)--','tokens');
tokens=[tokens{:}];tokens=[tokens{:}];%unwrap
[~,~,G]=unique(str2double(tokens));%convert to positive integer
splitapply(@mean,num,G)

[Rik]

I'm on mobile now so I can't test the code for you. You can use the first output of unique to get the number of each group. Then you can use sprintf to put them back in a char array.

[tinkiewinkie]

Do you mean this?

[num,str]=xlsread('output2.xlsx');
tokens=regexp(str,'(sound[0-9]+)--','tokens');
tokens=[tokens{:}];tokens=[tokens{:}];%unwrap
[G,~,~]=unique(str2double(tokens));%convert to positive integer
K=sprintf(G);
splitapply(@mean,num,G)

If I get the first output of unique, it is displayed as NaN values.

[Rik]

No, I meant this:

[num,str]=xlsread('output2.xlsx');

tokens=regexp(str,'sound([0-9]+)--','tokens');%capture the number as token
tokens=[tokens{:}];tokens=[tokens{:}];%unwrap
[~,IDs,G]=unique(str2double(tokens));%convert to positive integer

names=arrayfun(@(x) sprintf('sound%d',x),IDs,'UniformOutput',false);
output=splitapply(@(x) mean(x,1),num,G);

[tinkiewinkie]

Yes thank you for your great help in this!