How to filter duplicate entries

Hi,

I have below entries (cell array matrix),

FirstEntry  SecondEntry Index Score
A               B        5     25
C               D        62    8  
C               K        31    2
B               A        6     25
K               C        9     2

The entry A & B (in row 1)is same as B & A(in row 4) (without consider its first entry or second entry), I only want to keep one entry for this kind of entries. Likewise, row 3 (c & K) is same as row 5 (K & C)

My desired output:
FirstEntry  SecondEntry Index Score
A               B        5     25
C               D        62    8  
C               K        31    2

Kindly some one help, many thanks in advance,

Answer 1

[Adam Danz]

Your original cell array is named 't'.

t = {
'A'              'B'        5     25
'C'               'D'        62    8  
'C'               'K'        31    2
'B'               'A'        6     25
'K'               'C'        9     2};

'unqIdx' is an index of row numbers of 't' that are unique.

'desired' is your unique array without duplicates.

[~, unqIdx] = unique(cellfun(@strcat, sort(t(:,[1,2])')'), 'rows'); 
desired = t(unqIdx, :);

[Mekala balaji]

Sir,

It works, But can you help explain the logic, inside the brackets

[Adam Danz]

Sure. The best way is to step through the functions starting from the inside out.

1) We want to sort the rows of the first 2 columns of 't' so that {'B' 'A'} becomes {'A', 'B'}. The tick marks (') merely transpose the array which really could be avoided but I included it so the orientation of the data is consistent.

    sort(t(:,[1,2])')'
    ans =
      5×2 cell array
        {'A'}    {'B'}
        {'C'}    {'D'}
        {'C'}    {'K'}
        {'A'}    {'B'}
        {'C'}    {'K'}

2) Now we want to join (concatenate) the two columns of strings into single strings. {'A' 'B'} becomes 'AB'.

cellfun(@strcat, sort(t(:,[1,2])')')
ans =
  5×2 char array
    'AB'
    'CD'
    'CK'
    'AB'
    'CK'

3) Now we want to know which rows of the above character array are unique. The 2nd output of unique() gives us those row numbers.

[~, unqIdx] = unique(cellfun(@strcat, sort(t(:,[1,2])')'), 'rows')
unqIdx =
   1
   2
   3

4) Finally we use those row numbers (indices) to create the unique version of 't'.

t(unqIdx, :)
ans =
  3×4 cell array
    {'A'}    {'B'}    {[ 5]}    {[25]}
    {'C'}    {'D'}    {[62]}    {[ 8]}
    {'C'}    {'K'}    {[31]}    {[ 2]}

[Mekala balaji]

Sir,

Thanks Understood!

But I encounter one error when I use the below input:

t = {'1 A2B.th' 'BG' 5 25;'C2G N' 'DV.NH' 62 8;'90CY' 'FK.JK' 31 2;'BG' '1 A2B.th' 6 25;'FK.JK' '90C' 9 2}

[~, unqIdx] = unique(cellfun(@strcat, sort(t(:,[1,2])')'), 'rows'); 
desired = t(unqIdx, :);
Error using cellfun
Non-scalar in Uniform output, at index 1, output 1.
Set 'UniformOutput' to false.

I even modify using arrayfun and UniformOutput', false

[~, unqIdx] = unique(arrayfun(@strcat, sort(t(:,[1,2])')'), 'rows');

Error using cell/unique (line 105)
Unrecognized flag 'UniformOutput'. Valid flags are 'rows', 'first', 'last', 'stable', 'sorted', 'legacy'.
Still can not work,

[Adam Danz]

I see. Here's the updated code that works with the old and new dataset. I could no longer do it in 1 line of code. The new version uses 2 lines and the use of cellfun() is no longer necessary. I also added the 'stable' parameter to unique() so that your final unique array is in the same order as your input array.

tSort = sort(t(:,[1,2])')';
[~, unqIdx] = unique(strcat(tSort(:,1), tSort(:,2)), 'rows', 'stable')
t(unqIdx, :)

[Mekala balaji]

Thanks Sir,

It works.