Table Segmentation,Data Reduction, Sorting And Writing in MATLAB/EXCEL

Question

I would like to do a data reduction operation on a spreadsheet. Preferably I would like to use MATLAB/(or excel) since I need separate output files for each case.

The link is for the spreadsheet is below

Spreadsheet

A screenshot of the spreadsheet is as below

The output I required in text files is something as below The first sheet in the .xls file is the main input. Wheras the the following sheets (d**) are my required output. I also need these sheets in a separate ASCII file (.dat) to plot hem later on. Here is how the algorithm works

Lookup the number/string in column B(FileName)
Extract all data in Columns C and D (Saturation and ETC) with same FileName Value(Column B)
Lookup the matching FileName(Column B) value in Column E (ImageIndex).
Copy Value of ImageName(Column F) to the corresponding Value in Image(IndexColumn E)
Result would be three columns (ImageName,Saturation,ETC). ImageName would be same for each subcase
Sort the columns based on Saturation
Write each sub case as a separate .dat file

I tried using a few recipes using categorical arrays (findgroups and splitapply) in MATLAB. Didn't seem to work out for me. I would be later working on a larger data set so automation is necessary. I think this could be done using macros on excel, but I would prefer using MATLAB since I would use MATLAB to plot the data. Any other alternative suggestions are welcome

Thanks,

Jonas Jonas · Accepted Answer · 2017-02-06T08:06:54

Here's a Matlab solution. You could do it with a rather convoluted accumarray call, but readability would be rather bad, so I'm opting for a loop here.

out is a structure which you can use to either write files, or to plot the data.

tbl = readtable('yourFile.xls');

%# get the group indices for the files
%# this assumes that you have cleaned up the dash after the 1
%# so that all of the entries in the FileName column are numeric
idx = tbl.FileName;

%# the uIdx business is to account for the possibility
%# that there are images missing from the sequence
uIdx = unique(idx);
nImages = length(uIdx);

%# preassign output structure
out(1:nImages) = struct('name','','saturation',0,'etc',0);

%# loop to extract relevant information
for iImage = uIdx(:)'
   myIdx = idx==iImage;
   data = tbl(myIdx,{'Saturation','ETC'});
   data = sortrows(data,'Saturation');
   name = tbl.ImageName{tbl.ImageIdx==iImage};

   out(iImage==uIdx).name = name;
   out(iImage==uIdx).saturation = data.Saturation;
   out(iImage==uIdx).etc= data.ETC;
end

%# plotting
for iImage = 1:nImages
    figure('name',out(iImage).name)
    plot(out(iImage).saturation, out(iImage).etc,'.');
end

Table Segmentation,Data Reduction, Sorting And Writing in MATLAB/EXCEL

1 Answers