topic Re: How do I identify words (using formula in column) that have more than 2 characters different from my reference word? in Discussions

How do I identify words (using formula in column) that have more than 2 characters different from my reference word?

SaraHorta23 — Sat, 10 Jun 2023 23:43:55 GMT

Hi Everyone,

I have a column composed by words that have combination of different characters (ie. AAAAAAAAAA - word with 10 characters). I want to identify in an adjacent column the words that have more than 2 characters different (ie. ABAAAACEAA). The position at which the letters are different doesn't matter and additionally, some words can also be shorter or longer than my reference word.

Therefore, I want to achieve a formula that would allow me to distinguish/identify words that have less/more than 2 different characters difference from my reference sequence and have the same length. I'm still a beginner in JMP and don't know if this is possible to achieve. Let me know if you have any suggestions :)

Re: How do I identify words (using formula in column) that have more than 2 characters different from my reference word?

ian_jmp — Wed, 09 Feb 2022 10:19:47 GMT

Take a look at the attached table, which should give you a start. The heart of it is a Formula Column (called 'Decision' and denoted by the '+' sign in the columns panel of the table). Clicking on the '+' gives:

You need some knowledge of JSL to understand this, and can use 'Help > Scripting Index' to figure out exactly how it works if you need to.

Re: How do I identify words (using formula in column) that have more than 2 characters different from my reference word?

jthi — Wed, 09 Feb 2022 10:37:56 GMT

Many ways to do this. Here is one additional option:

Names Default To Here(1);

/*
a = "ABAAAACEAA";
b = "AAAAAAAAAA";
Show(!IsMissing(Substitute(a, Left(a,1), "")));
Show(!IsMissing(Substitute(b, Left(b,1), "")));
*/
New Table("Untitled 2",
	Add Rows(3),
	New Column("Column 1", Character, "Nominal", Set Values({"ABAAAACEAA", "AAAAAAAAAA", "BBBBAAA"})),
	New Column("Column 2",
		Numeric,
		"Continuous",
		Format("Best", 12),
		Formula(!Is Missing(Substitute(:Column 1, Left(:Column 1, 1), "")))
	)
);

Column1 is the column with characters

Formula(!Is Missing(Substitute(:Column 1, Left(:Column 1, 1), "")))

in this simple example idea is to replace all values in the string found from first position and if there are still some characters left, we know that there were more than 1 character in the string. If you have more than one value to replace, you should be able to use same idea by modifying Substitute function arguments

Re: How do I identify words (using formula in column) that have more than 2 characters different from my reference word?

SaraHorta23 — Wed, 09 Feb 2022 11:26:04 GMT

Hi Ian,

Thanks for the feedback. I tried to implement the formula you wrote but I don't get where can I define that my reference sequence is AAAAAAA and not something else. I replaced the "" in the items selection by "AAAAAAAA" but the result was not correct. Any suggestion how can I upgrade the formula you wrote?

Re: How do I identify words (using formula in column) that have more than 2 characters different from my reference word?

SaraHorta23 — Wed, 09 Feb 2022 11:36:43 GMT

Hi Jthi, thanks for the suggestion.

To be honest I'm still trying to understand the formula :'). In the file I'm working on, I have only one reference word and I have to screen more than a million row entries with every possible combination of position/letter. I assume that this formula would not work because I cannot define those values in the formula. Would it be possible to adapt it considering a large database Ive?

Re: How do I identify words (using formula in column) that have more than 2 characters different from my reference word?

ian_jmp — Wed, 09 Feb 2022 12:46:33 GMT

Looks like I misunderstood what you meant by "I want to identify in an adjacent column the words that have more than 2 characters different". What I suggested would flag rows where each cell in the source column contains a string that is itself made up of more than two distinct characters (so there is no reference sequence or string).

Re: How do I identify words (using formula in column) that have more than 2 characters different from my reference word?

Craige_Hales — Wed, 09 Feb 2022 12:56:40 GMT

We need some more examples. What is the number of differences for (don't over-specify, indicate where it isn't important.)

ref = "AEIOUA" // trying to understand the rule for the repeated letter

test = "AEIOUA" // nDiff = 0 for identical string

test = "AAEIOU" // nDiff = ? for same letters, reordered

test = "AEIOU" // nDiff = ? for same letters, missing one on right

test = "EIOUA" // nDiff = ? for same letters, missing one on left

test = "EIOU" // nDiff = ?

test = "AA" // dDiff = ?

test = "AXA" // dDiff = ?

test = "AXXXXA" // dDiff = ? for matching length

test = "AEIUOA" // dDiff = ? for internal letters swapped

test = "AEIOUEIOU" // nDiff = ? for repeated letters

These answers would help choose an algorithm:

You said ~1e6 test words; how many ref words are you looking at?

Will you need answers other than a "yes/no there are more than 2 differences"? Is a count of diffs going to be useful?

Re: How do I identify words (using formula in column) that have more than 2 characters different from my reference word?

SaraHorta23 — Wed, 09 Feb 2022 13:23:14 GMT

Hi Craige,

Thanks for helping out. I have 1 to 5e6 and only one reference word. I basically want to filter out words that have more than 2 letter different comparing to a reference word and therefore I do not require more than yes/no or 0/1 as answer. Going through your questions:

test = "AEIOUA" // nDiff = 0 for identical string

test = "AAEIOU" // nDiff = 1 - for same letters, reordered.

- The first A is only one kept at the same position. (5 diff letters)

test = "AEIOU" // nDiff = 1 for same letters, missing one on right

test = "EIOUA" // nDiff = 1 for same letters, missing one on left

test = "EIOU" // nDiff = 1

test = "AA" // dDiff = 1

test = "AXA" // dDiff = 1

- Length is not same as AEIOUA. For this I applied a function Len to filter those words out.

test = "AXXXXA" // dDiff = 1 for matching length

- The first and last A are kept at the same position. (4 diff letters)

test = "AEIUOA" // dDiff = 0 for internal letters swapped

Only 2 letters different from AEIOUA, so it will pass the criteria.

test = "AEIOUEIOU" // nDiff = 1 for repeated letters

Long sequence. Removed using Len.

Sorry, I was not clear when explaining my question/problem. I recreated an example of what i'm looking for in addition to your questions.

Reference sequence	FEBRUARY

List of sequences	Label - more than 2 characters different from reference?
FEBAUARY	No
WAARUARY	Yes
YRAURBEF	Yes
FEBRURRR	No
FEBRUARW	No
YEAURBEF	Yes
YEAURARY	Yes

Re: How do I identify words (using formula in column) that have more than 2 characters different from my reference word?

jthi — Wed, 09 Feb 2022 14:00:31 GMT

Thank you for extra info! Check out Shortest Edit Script from Scripting index:

With that you might be able to do something, unless it is too slow. Pat Match could also maybe do this, but I haven't (yet) looked into how to use it properly.

editList = Shortest Edit Script(:List of sequences, "FEBRUARY");
common = "";
For(i = 1, i <= N Items(editList), i++,
	If(editList[i][1] == "Common",
		common = common || editList[i][2]
	)
);
Length(common) < Length(:List of sequences) - 2;

Re: How do I identify words (using formula in column) that have more than 2 characters different from my reference word?

SaraHorta23 — Wed, 09 Feb 2022 14:35:54 GMT

Thanks Jthi! Works nicely ;)