Separating Multiple Columns of Data Based on a Value in One Column

My lab has an instrument which outputs data into 5 columns: region of interest (ROI), frame, x column, wavelength, and intensity. We have multiple ROI for each experiment, and each ROI is designated a number starting from 1. Each ROI has 11,300 rows of data give or take a couple hundred. All of the ROI and their corresponding data are displayed in one column (per the labels listed above) and I'm trying to create a generic script or function which can separate the data into separate columns based on their ROI (no matter how many ROI or rows of data there are) so I can plot the data for each ROI. I've included (1) an image displaying the data as is output by the instrument and (2) an image of the end goal for the data. I've only been using MATLAB for a few months and I don't really know where to start for this project, so any help would be appreciated!

Answer 1

[Stijn Haenen]

You can make a structure for each ROI like this:

x=data;

sep_x=struct;

for ROI=[1,2,5,8]%??

a=find(x(:,1)==ROI);

sep_x.(sprintf('ROI%g',ROI))=x(a,:);

end

[Guillaume]

Please use the formatting tools to make your post readable.

Note that find as you use it is completely unnecessary

a = x(:, 1) == ROI;

would produce exactly the same result.

Also, note that this could be achieve more cleanly without a loop.

[Stijn Haenen]

a = x(:, 1) == ROI; 

does not give the same as

a=find(x(:,1)==ROI);

The first one gives a logical array and the second gives the row numbers of x(:,1) that have the same value as ROI

[Guillaume]

Sorry,

a = x(:, 1) == ROI;
something = x(a, :);

and

a = find(x(:,1) == ROI);
something = x(a, :);

produce exactly the same something. The find only slows things down. As I said, as you use it it's completely unnecessary. What's in a is completely irrelevant it's a temporary variable.

Answer 2

[Guillaume]

While you could indeed explicitly separate the data per ROI, you probably don't need to. Matlab has several functions that allows you to do the same thing to data grouped according to certain criteria.

For example, if you wanted to have a plot for intensity vs wavelength for each ROI you would:

First import the data as a table, with your excel file, it should be as simple as:

data = readtable('yourexcelfile');  %creates a table with 5 variables: ROI, frame, x_column, wavelength, intensity (based on the header in the spreadsheet)

Then prepare a figure:

figure;
hold('on'); %so that we can see the multiple plots on the same graph

Then plot intensity vs wavelength for each ROI:

rowfun(@plot, data, 'InputVariables', {'wavelength', 'intensity'}, 'GroupingVariables', 'ROI');

Done! You get a different line for each ROI. We could make it even neater by giving each plot a name and displaying a legend:

figure;
hold('on');
rowfun(@(x, y, roi) plot(x, y, 'DisplayName', compose('ROI %d', roi)), 'InputVariables', {'wavelength', 'intensity', 'ROI'}, 'GroupingVariables', 'ROI');

Similarly you could compute statistics for all ROIs at once:

groupsummary(data, 'ROI', {'mean', 'std', 'max'}, {'wavelength', 'intensity'})

computes the mean, standard deviation of maximum wavelength and intensity for each ROI.

See rowfun, varfun, groupsummary, and more.