Averaging column values into one row

Hi,

It was very tough to come up with the relevant title for my question.

Here is what I am trying to do:

I have an excel file that has 3 columns: StudentID, StudentName, and Grades. One student can have more than one grade:

a112 Student1 96

a112 Student1 88

a114 Student2 70

I am trying to process this data and have the average of grade per student, so the final result for above data should be:

a112 Student1 92

a114 Student2 70

I am reading the excel file in matlab as follows:

[a b] = xlsread('student_grades.xlsx');

a has the Grades and b is the cell array of StudentID and StudentName. I can do this in three for loops but I read that loops are inefficient in matlab. What is the best way of accomplishing this in this in matlab?

Thanks!

[Jan]

Does it matter, that the original file is an Excel file? If not, would this meet your cuurent situation: "I have two cells. The first contains a list of IDs as strings, the second a number. I want to get the average of the numbers of all elements belonging to the same ID." ?

Answer 1

[Jan]

Some functions are performed fater by "vectorized" methods, e.g. for linear algebra. But the rumor, that loops are very slow in Matlab is not true since Matlab 6.5 anymore -- for 10 years now.

Since you did not specify the type and dimensions of the available inputs, I can only suggest the general commands: unique, accumarray, histc. Using these function you need just a few lines.

Perhaps something like this helps - with loops:

ID = {'a112', 'a113', 'a112', 'a114'};
Num = [1,3,4,2];
uID = unique(ID);
averageNum = zeros(1, length(uID));  % Pre-allocate!
for i = 1:length(uID)
  averageNum(i) = mean(Num(strcmp(ID, uID{i}));
end

[S]

Thanks for the quick response. The input is an excel file, which if read using xlsread will result in a cell array (for strings) and double array (for grades).

What is Num?