Match two columns with names/strings that are not 100% identical
7 vues (au cours des 30 derniers jours)
Afficher commentaires plus anciens
Evening, in a table, is there a way to compare two columns with names/strings and find "similar" matches that are not 100% identical?
Instead, they share 80% or more characters to be considered a match. Ignore case and spaces.
For example, the first entry in column1 is NAME123ABC that has 10 characters, and I woud like the index of column 2 if
1- the percentage of shared characters is 80% or more,
2- the percentage of shared characters is 80% or more AND order of characters is identical.
I attached the table with two columns, the same data is pasted below:
'NAME123ABC' 'XYZ'
'NAME2XYZ' 'Name1ABC'
'NAME3333' 'abc'
'NAME ZZZ' '123A'
'' 'AAA'
'' 'ABC ABC'
'' 'Name1 ABCD'
'' 'abcxyname1'
'' 'NAme 1'
'' 'XYZ2'
'' 'ABC NAME1'
'' '123ABC'
For this example, the answer for
1 - with the condition of at least 80% of characters the indices in column2 are: 2, 7, 8 ,11
2 - with the condition of at least 80% of characters AND order of characters the indices in column2 are: 2, 7
danke
4 commentaires
Jan
le 14 Oct 2022
@Dave: Again, we do need an exact mathematical definition of what you call "a match", before we can implement it as code. What does "order of characters" mean in case of repeated characters?
This case occurs in your test data: "NAME123ABC" and "Name1 ABCD"
We cannot invent this detail for you, because this is the scientific problem you want to solve.
Réponses (2)
Jan
le 13 Oct 2022
Pool = {'NAME123ABC', 'NAME2XYZ', 'NAME3333', 'NAME ZZZ'}
Data = {'XYZ', 'Name1ABC', 'abc', '123A', 'AAA', 'ABC ABC', 'Name1 ABCD', ...
'abcxyname1', 'NAme 1', 'XYZ2', 'ABC NAME1', '123ABC'};
Data = lower(strrep(Data, ' ', '')); % Remove spaces
Pool = lower(pool);
orderFlag = true;
match = false(size(Data));
for k = 1:numel(Data)
for p = 1:numel(Pool)
if compare(Data{k}, Pool{p}, orderFlag)
match(k) = true;
break;
end
end
end
function m = compare(A, B, flag)
m = (sum(ismember(A, B)) / numel(A) > 0.8);
if m && flag % Order matters:
... Further clarification required
end
end
2 commentaires
Jan
le 14 Oct 2022
See my comments to your question. The meaning of "order" must be defined accurately before this can be implemented.
John D'Errico
le 13 Oct 2022
Modifié(e) : John D'Errico
le 13 Oct 2022
Searching for degree of similarity is not a completely trivial task. In a sense, you want to describe the distance between two "words" or sequences of characters as a number in some way.
One idea might be to consider PAIRS of consecutive characters. For example, suppose we allow any combination of upper case, lower case, numbers, and a space.
Clist = ['A':'Z','a':'z','0:9',' '];
That represents the universe of things we will care about. I suppose I could add in punctation, etc., but that will do for here.
Now, consider all possible combinations of two consecutive elements from that set.
[C1,C2] = meshgrid(Clist);
Clist2 = [C1(:),C2(:)]
So there are 3136 possible 2 character combinations from that set. Now think of the two sequences: 'ABC123' and the sequence '3ABC12'. They have exactly the same set of characters, but in a slightly different sequence, since the 3 is in a different place.
We can use the above set of 2 character sequences to compute the distance between a pair of strings. For example, comare 'ABC123' to 'B1A3C2'. Again, the same numbers and letters, but in a very different sequence here. The previous pair would be seen to have a much smaller distance. I'd suggest a simple scheme. Look at the pairs of consecutive elements of each sequence, and count the num,ber of times a pair is seen in BOTH sequences, so look for overlap of two character sub-sequences. That is, we have these three sequences:
'ABC123' ~ {'AB', 'BC', 'C1', '12', '23'}
'3ABC12' ~ {'3A', 'AB', 'BC', 'C1', '12'}
'B1A3C2' ~ {'B1', '1A', 'A3', '3C', 'C2'}
I've broken them each into 2 digit subsquences. See that 'AB' occurs in BOTH sequences 1 and 2. Similarly, 'BC' also appears in both cases. In fact, 4 of those 5 sequences of 2 characters appear in BOTH sequences 1 and 2. Compare that instead to sequences 1 and 3. There we see NO 2 character subsequences that are the same. So we could define a distance between two sequences in terms of the number of 2 character subsequences they have in common.
You should see this uses the order of characters as an important factor. It would be quite doable, and give you a simple measure of distance. You could add more information into such a measure, but this would gve you a start.
0 commentaires
Voir également
Catégories
En savoir plus sur Cell Arrays dans Help Center et File Exchange
Community Treasure Hunt
Find the treasures in MATLAB Central and discover how the community can help you!
Start Hunting!