Strategies for reducing calculation time: Finding values in a large array

I have multiple individual large arrays (each are as much as 1 million rows) making up a "complete dataset". Each has two columns. Column 1 has indentifying values (ID's), and column 2 has measurement values (Data). Each ID may be repeated an unknown number of times. I need to find each instance of each ID, calculating the mean of the Data for the IDs that repeat.

This code prodives and example of the raw data formatting for one such array, and outputs the expected results. However, speed is the issue, as the loop in Step 3 may be as large as 600,000 or more iterations for each array in the complete dataset.

% Step 1: representation of data format
RawData = [randi([1,3],10,1)/10,rand(10,1)];

% Step 2: Preallocate array with the unique IDs, sorted by default
UniqueData(:,1) = unique(RawData(:,1));

% Step 3: for each unique ID find the mean of the values matching that ID, storing results
for i = 1:length(UniqueData(:,1))
	UniqueData(i,2) = mean(RawData(RawData(:,1) == UniqueData(i,1),2));
end

Using timeit(), I found the above to be the fastest of the methods I tried, but it still takes around 2 hours to calculate Step 3 for one complete dataset (consisting of 10 such arrays).

I also tried replacing Step 3 with this:

UniqueData(:,2) = arrayfun(@(x) mean(RawData((RawData(:,1) == x),2)),UniqueData(:,1));

and this:

for i = 1:length(UniqueData(:,1))
    foo = RawData(RawData(:,1) == UniqueData(i,1),2);
    UniqueData(i,2) = mean(foo);
end

... without improved performance.

Is there a faster method for completing this calculation? I can't think of a method besides using a loop or arrayfun. Thanks.

Answer 1

[Jan]

[uniqID, ~, index] = unique(RawData(:, 1));
avg    = accumarray(index, RawData(:, 2), [], @mean);
result = [uniqID, avg];

Do you have a C compiler installed? Then a small C code might be even faster. I could post it on demand.

[Joe Vinciguerra]

WOW! What took 2 hours yesterday runs in 21 seconds. Thank you!

That looks like a useful little function; I'll have to read up more on it.

Please share your C code with me if you don't mind. I don't have any experience, but once I'm done prototyping in Matlab it will likely be migrated into C by a colleague .

Cheers.