special kind of sort
3 views (last 30 days)
Show older comments
My data has two columns and I am trying to find a way to sort the rows such that the values in the second column appear before they are mentioned in the first column
To make this more clear some example data where row5 should be placed before row3:
row1 NaN br1_0_40
row2 br1_0_40 br1_40_100
row3 br2_0_20 br1_100_120
row4 NaN br1_120_200
row5 NaN br2_0_20
row6 br2_0_20 br2_20_25
In reality my database is of course much larger so I am trying to find an efficient algorithm to sort in this manner so any help is appreciated
2 Comments
Guru
on 4 Jul 2013
Maybe I'm just missing something but based on your criteria do you always want row5 before row 3, no matter what?? If not, what is exactly is your criteria for sorting?
Answers (3)
dpb
on 4 Jul 2013
One possible solution...
>> sortrows(a,[2,3])
ans =
'r1' 'NaN' 'br1_0_40'
'r4' 'NaN' 'br1_120_200'
'r5' 'NaN' 'br2_0_20'
'r2' 'br1_0_40' 'br1_40_100'
'r3' 'br2_0_20' 'br1_100_120'
'r6' 'br2_0_20' 'br2_20_25'
>>
2 Comments
dpb
on 4 Jul 2013
Not positive, but it would seem there isn't necessarily a solution otomh...
But, best I can do at the moment is to suggest a heuristic approach something like (after an initial sort, of course)--
Identify "bad" places in your list - and remember them as indices. For each index shuffle that offending row until it looks ok relative to your condition.
In this way you should perturb sorting only in limited intervals, and general sorting shouldn't change much.
Again, whether there's a solution possible in the general case is, in my mind, questionable altho I've not tried to actually prove it one way or t'other...
HTH w/ thought process, anyway...
dpb
on 4 Jul 2013
Edited: dpb
on 6 Jul 2013
OK, here's a simple demonstration of what had in mind that works for the sample dataset -- I've not tested it on anything more complex; in particular haven't thought a lot (as in any) about the ramifications on traversing the unique list after the perturbation...
MATLAB code
u=unique(a(:,3));
for i=1:length(u)
[~,~,ib]=intersect(u(i),a(:,2),'stable');
if ~isempty(ib)&(i>ib)
a(ib:i,:)=circshift(a(ib:i,:),1);
end
end
%At the command line w/ the sample dataset as modified...
>> a=t
a =
'r1' 'NaN' 'br1_0_40'
'r2' 'br1_0_40' 'br1_40_100'
'r3' 'br2_0_20' 'br1_100_120'
'r4' 'NaN' 'br1_120_200'
'r5' 'br3_100_130' 'br2_0_20'
'r6' 'br2_0_20' 'br2_20_25'
>> u=unique(a(:,3));
>> for i=1:length(u),
[~,~,ib]=intersect(u(i),a(:,2),'stable');
if ~isempty(ib)&(i>ib)
a(ib:i,:)=circshift(a(ib:i,:),1);end,end
>> a
a =
'r1' 'NaN' 'br1_0_40'
'r2' 'br1_0_40' 'br1_40_100'
'r5' 'br3_100_130' 'br2_0_20'
'r3' 'br2_0_20' 'br1_100_120'
'r4' 'NaN' 'br1_120_200'
'r6' 'br2_0_20' 'br2_20_25'
>>
Salt to suit... :)
How much any of this might be vectorized w/ arrayfun and friends I've not even begun to consider, either...
1 Comment
dpb
on 4 Jul 2013
OK, just had an idea to test it just a little...
M
>> sortrows(a,[3 2])
ans =
'r1' 'NaN' 'br1_0_40'
'r3' 'br2_0_20' 'br1_100_120'
'r4' 'NaN' 'br1_120_200'
'r2' 'br1_0_40' 'br1_40_100'
'r5' 'br3_100_130' 'br2_0_20'
'r6' 'br2_0_20' 'br2_20_25'
>> for i=1:length(u),[~,~,ib]=intersect(u(i),a(:,2),'stable');if ~isempty(ib)&(i>ib) a(ib:i,:)=circshift(a(ib:i,:),1);end,end
>> a
a =
'r1' 'NaN' 'br1_0_40'
'r2' 'br1_0_40' 'br1_40_100'
'r5' 'br3_100_130' 'br2_0_20'
'r4' 'NaN' 'br1_120_200'
'r3' 'br2_0_20' 'br1_100_120'
'r6' 'br2_0_20' 'br2_20_25'
>>
IOW, resorted which screwed it up again but in a different initial order. Looks like the same logic still worked. Granted this is still undoubtedly a toy case, but...
See Also
Categories
Find more on Logical in Help Center and File Exchange
Community Treasure Hunt
Find the treasures in MATLAB Central and discover how the community can help you!
Start Hunting!