how to find the row number of the input 3d data from 86 millions of table data?

Hello,

I have a table data with 86 millions of rows and 8 colums. The columns 1,2,3 are a 3d data points(size_1,size_2,size_3). And columns 4,5,6 are also a 3d data points(pattern_1,pattern_2,pattern_3). The last two columns 7 and 8 are a 2d data points(price_1, error_2). The picture only shows 12 rows of the table.

I want to input a data (X,Y,Z), which includs in the data set of (size_1,size_2,size_3), that is to say, the input data contain in the columns 1,2 and 3. And I would like to know directly which row in the table contains (X,Y,Z).

I do not want to use the for loop or find function because it take long time. Is there any function in maltab that can do this in an efficiency way?

Many thanks!

Answer 1

[Harry Laing]

Because you have 86 million rows, searching will take time. Try the ismember function to see if thats faster than whet youve already tried... Here I assume your data is imported into matlab as a numeric data variable (not a table variable) and is called my_data:

% Generate a column of logical values for each row of your data (for the first 3 columns)
% where the data in the row matches exactly X, Y and Z.
logical_index = ismember(my_data(:,1:3),[X Y Z],'rows');

% Now determine the actual row number these values occur at
row_numbers = find(logical_index);


% Yes, you could also just write:
row_numbers = find( ismember(my_data(:,1:3),[X Y Z],'rows') );

I tried it myself for a matrix of 86 million data points, the line ismember(my_data(:,1:3),[X Y Z],'rows'); took around 60 seconds to complete. But I have a fairly fast machine. Be patient when working with such large datasets.

[Alan]

Thank you! It takes 65 seconds to find the row by my computer. Efficiently enough.