Sorting/Arranging Data Sections in one 48772x15 matrix

Hello,

I have a 48772x15 matrix with data in columns 1-14. In column 15 there are 0s with the occasional nonzero number (from 1-8) as a marker for a project I'm doing. What I need help with is when there is a nonzero number I want to take/grab the data from all columns from that row until there is another nonzero number in that column and put that data into a different area so I can sort it. ie. when marker number 6 shows up I want to take that data (including the row with the marker) until there is another nonzero marker in that column (excluding the data starting with the row with the next marker) and then put that data with the other number 6's data.

I am not that proficient in matlab so any help would be beneficial. I know I should use an else if statement and grab data but not sure how to formulate it.

Thank you.

Answer 1

[Star Strider]

This seems to work, and runs quickly:

D = load('Analysis_of_Excel.mat');
M = D.Analysis_of_Excel;
M15 = [find(M(:,15) > 0); size(M,1)+1];     % Need Row Indices

for k1 = 1:8
    Ix1 = find(M(:,15) == k1);              % Row Blocks Starting With ‘k1’
    for k2 = 1:length(Ix1)
        Ix2 = M15(find(M15 > Ix1(k2), 1, 'first'));
        Data{k1,k2} = M(Ix1:Ix2-1,:);
    end
end

I can’t think of a way to do it without the nested for loops, because it is necessary to determine the first row index equal to ‘k1’, then find all locations of that number (in ‘Ix1’) and look for the next non-zero value in Column 15, the function of the ‘k2’ loop that determines ‘Ix2’. The rest is straightforward. The ‘M15’ vector stores the indices of all the non-zero values in ‘M(:,15)’.

The ‘Data’ matrix has the values of the ‘M(:,15)’ indicators as the row index (so ‘1’ is row #1, etc.) with the data for each value across the columns. This looks correct, but it’s difficult to check manually because the matrix is so large. If there are problems, post them and I will do the best I can to resolve the problems with my code.

[Matthew]

When I tried using the script you posted above it came up with odd sorting. If you look at the picture you can see the different sizes of the matrices of the cell array. The first column of the cell shows the correct separation of data with {1,1} being data for one set of target #1. But {1,2} shows the same as {1,1} with additional data of target #6, target #5, target #3 etc. Not sure what is going wrong, hopefully you can help. Unfortunately I cannot send the data since the array takes up so much data.

[Star Strider]

I didn’t take a close look at your data because the matrix was too large to check everything. My code first finds the row indices of all the non-zero rows in Column #15. It then finds the locations of the ‘1’ values for instance, then locates the next non-zero value (that may or may not be a ‘1’) and uses the rows up to but not including that row for the row-length of that section of the matrix. It then searches for the next ‘1’ and repeats the process, eventually iterating through ‘8’, repeating the process each time.

When I checked it (adding the ‘Diagnostic’ lines, ‘ChkLen’, ‘ChkVal’, and ‘Q’), it appears to be working the way I intended it to, at least with respect to its index calculations. It’s possible that I misunderstood something in your Question, but I can’t find the problem.

D = load('Analysis_of_Excel.mat');
M = D.Analysis_of_Excel;
M15 = [find(M(:,15) > 0); size(M,1)+1];     % Need Row Indices
ChkLen = diff(M15);                     % Diagnostic
ChkVal = M(M15(1:end-1),15);            % Diagnostic

for k1 = 1:8
    Ix1 = find(M(:,15) == k1);              % Row Blocks Starting With ‘k1’
    for k2 = 1:length(Ix1)
        Ix2 = M15(find(M15 > Ix1(k2), 1, 'first'));
        Q{k1,k2} = [k1  k2  Ix1(k2)  Ix2  M(Ix1(k2),15)  M(min(Ix2,size(M,1)),15)]; % Diagnostic
        Data{k1,k2} = M(Ix1:Ix2-1,:);
    end
end
Q{1:3,:}

Answer 2

[Sean de Wolski]

You could absolutely do this with a for-loop and if else. Here's a vectorized approach that bins based on index directly:

x = [magic(6) [1; 0; 0; 2; 0; 1]]; % simple easy to understand example

idx = logical(x(:,end)); % Where are the non zero values
bin = x(idx,end); % which bin?

idxx = cumsum(idx); % group zeros with previous

C = accumarray(bin(idxx),(1:numel(idxx)),[],@(v){x(sort(v),:)}) % aggregate

C{:}

C is a cell array, the first element C{1} corresponds to the 1s, etc. This approach requires that the first element in the last column be non-zero. You could remove the earlier rows if this is not the case.