Average over duplicate values in xlsx file
显示 更早的评论
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:
So basically I want the average values with only specific fields.
Can someone please guide me as to how I can get this result?
回答（2 个）
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
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
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)];
类别
在 帮助中心 和 File Exchange 中查找有关 Spreadsheets 的更多信息
2019-4-2
Community Treasure Hunt
Find the treasures in MATLAB Central and discover how the community can help you!
Start Hunting!
Translated by 
