How to improve efficiency when checking for unique combinations of table values?

Question

Dillon Heidenreich 2021-6-14

0
链接

此问题的直接链接

https://ww2.mathworks.cn/matlabcentral/answers/856290-how-to-improve-efficiency-when-checking-for-unique-combinations-of-table-values

评论： dpb 2021-6-15

Hello, I am trying to check each row in a sheet of data to see if it contains a combination of 2 values. For example, there might be a table like

Fruit | Color | related value
apple | blue | 3
apple | red | 8
banana | blue | 5
banana | yellow | 6
apple | blue | 16
pear | yellow | 7

I want to create a table with the first column as the top row and the second column as the first column, and with the values indicated by a corrdinate pair being the number of times that that particular pair appears.

For example:

            apple | banana | pear
blue         2       1        0   
red          1       0        1
yellow       0       1        1   

I'm currently using code that looks like this

fileContents = zeros(size((colorNames,1),size(fruitNames,1));
for p = 1:size(uniqueFruitNames,1)
  for q = 1:size(uniqueColorNames,1)
    for k = 1:size(rawData,1)
      if ((uniqueFruitNames(p) == rawData(k,1)) && (all((uniqueColorNames(q,:) == rawData(k,2))))
        fileContents(q,p) = fileContents(q,p) + 1;
      end
    end
  end
end

My problem is that the table that is referenced by my code contains upwards of 10 thousand values, and I have found that the above code take approximately 15 minutes to complete. The rawData sheet contains around 12 thousand rows and 70 columns. The uniqueFruitNames array is 8 x 1, and the uniqueColorNames array is aproximately 2000 x 1.

What can I do to make this more efficient?

0 个评论
显示 -2更早的评论隐藏 -2更早的评论

请先登录，再进行评论。

请先登录，再回答此问题。

Answer 1

dpb 2021-6-15

0
链接

此回答的直接链接

https://ww2.mathworks.cn/matlabcentral/answers/856290-how-to-improve-efficiency-when-checking-for-unique-combinations-of-table-values#answer_724850

在 MATLAB Online 中打开

tFruit=readtable('fruit.txt');
tFruit=tFruit(:,[1:2:end]);
tFruit.Properties.VariableNames={'Fruit','Color','Data'};
tFruit.Fruit=categorical(tFruit.Fruit);
tFruit.Color=categorical(tFruit.Color);
tFruit =
  6×3 table
    Fruit     Color     Data
    ______    ______    ____
    apple     blue        3 
    apple     red         8 
    banana    blue        5 
    banana    yellow      6 
    apple     blue       16 
    pear      yellow      7 
    % The engine    
>> histcounts2(double(tFruit.Color),double(tFruit.Fruit))
ans =
     2     1     0
     1     0     0
     0     1     1
>> 

1 个评论
显示 -1更早的评论隐藏 -1更早的评论

dpb 2021-6-15

Essentially Walter's solution packaged by TMW.

Which will be quicker for a largeish dataset I don't know...

请先登录，再进行评论。

Answer 2

Walter Roberson 2021-6-14

0
链接

此回答的直接链接

https://ww2.mathworks.cn/matlabcentral/answers/856290-how-to-improve-efficiency-when-checking-for-unique-combinations-of-table-values#answer_724840

findgroups the two columns individually. Concatenate the groups into one array of two columns. Use those as the first parameter to accumarray and use 1 as the second parameter. The result will be a count array.