Average over duplicate values in xlsx file

1 次查看(过去 30 天)
This is my first time using MATLAB so I have very very less knowledge of the syntax. I am stuck with a problem. I have a xlsx file which looks like:
I want to average the columns `Response`, `ResponseC`, `ResponseCo` and `Reaction_time` for repeating values of `StimuliName` and finally in the output have something like this:
Untitled.png
So basically I want the average values with only specific fields.
Can someone please guide me as to how I can get this result?
  3 个评论
rbhatt93
rbhatt93 2019-4-2
They are stored in a cell array with the headers.
Adam Danz
Adam Danz 2019-4-2
OK; I added a comment under my answer. Let me know if you get stuck implementing the solution.

请先登录,再进行评论。

回答(2 个)

Adam Danz
Adam Danz 2019-4-1
You can use findgroups() to split the rows into groups based on StimuliName. Then you can use splitapply() to apply a funciton (ie, mean) to a column of the data for each group.
% assign group numbers to simuli names in column 6
[stimGroups, groupID] = findgroups(data(:,6));
% Use splitapply() to perform stats on grouped data in columns 14, 15, and 16
meanVals1 = splitapply(@mean, [data{:, 14}]', stimGroups);
meanVals2 = splitapply(@mean, [data{:, 15}]', stimGroups);
meanVals3 = splitapply(@mean, [data{:, 16}]', stimGroups);
From here you can store the data in a table or cell array. The best choice depends on what format the data are already stored in matlab and how you plan on using the data.
  1 个评论
Adam Danz
Adam Danz 2019-4-2
If the cell array contains headers along the first row, you can easily adapt the code above to ignore the first row.
data(:, 6)
% should become
data(2:end, 6)

请先登录,再进行评论。


Andrei Bobrov
Andrei Bobrov 2019-4-2
T = readtable('yourfile.xlsx');
T_out1 = varfun(@(x)x(1),T,'I',1:14,'G','StimuliName');
T_out2 = varfun(@mean,T,'I',15:18,'G','StimuliName');
out = [T_out1(:,3:end), T_out2(:,3:end)];

类别

Help CenterFile Exchange 中查找有关 Workspace Variables and MAT-Files 的更多信息

产品


版本

R2017a

Community Treasure Hunt

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

Start Hunting!

Translated by