topic How to find a specific text (word) within a column character - nominal data type ? in Discussions

How to find a specific text (word) within a column character - nominal data type ?

VCO — Tue, 21 Apr 2020 23:43:19 GMT

Hi,

I'd like to get some help on finding a specific string (actually 2 strings, say "JMP1" and "JMP2") that may be within a column with text, as we move down that column, and then have a formula column next to it to label "JMP1" or "JMP2", if it finds it, or label say "NA" otherwise.

A couple of notes:

1- "JMP1" or "JMP2" can be in any position within the text in Column 1

2- "JMP1" and "JMP2" are not contained in the same text in Column 1

So in my mind it will be something like this for the Formula Column (Column 2 - the label column):

IF (Column 1 contains "JMP1", then Column 2 = "JMP1", else (IF Column 1 contains "JMP2", then Column 2 = "JMP2", else Column 2 = "NA"));

I looked up to the Match function, but I got a bit confused with the multiple arguments and expressions. Maybe I'm not on the right track either, hence why I need advice

It will be great if someone could share their thoughts (preferably through the Formulas area, but will be open to suggestions through JSL).

Thanks,

Vasco

Re: How to find a specific text (word) within a column character - nominal data type ?

KarenC — Wed, 22 Apr 2020 00:06:05 GMT

If( Contains( :name, "JA" ),
	"JA",
	If( Contains( :name, "MA" ),
		"MA",
		"NA"
	)
)

I think you are there. JMP has a character function called contains. You can use that as illustrated above for the big class sample data file. See the help for character functions at character function help

Re: How to find a specific text (word) within a column character - nominal data type ?

vince_faller — Wed, 22 Apr 2020 02:13:01 GMT

Regex is really powerful and used in a lot of languages. Though it can admittedly be quite hard to understand.

Names default to here(1);
dt = new table("Example", 
	New Column("JMPs", character, <<Set Values(repeat({"JMP1", "JMP2", "JMP14", "Something Else"}, 5)));
);

dt << new Column("Simple Regex with Or", 
	character, 
	formula(
		// 14 needs to be first because JMP1 would also match
		r = regex(:JMPs, "JMP14|JMP1|JMP2");
		if(!ismissing(r), 
			r
		, // else
			"NA"
		)
	)
);

// a more compliated example
// if you know something about the form you can do some neat stuff

dt << new Column("regex with pattern", 
	character, 
	formula(
		// this looks for "JMP" than any number of digits so you don't have to make a billion if statements
		r = regex(:JMPs, "JMP\d+");
		if(!ismissing(r), 
			r
		, // else
			"NA"
		)
	)
);

Re: How to find a specific text (word) within a column character - nominal data type ?

VCO — Wed, 22 Apr 2020 08:46:41 GMT

Hi @KarenC ,

Thanks, it worked ! Simple and straightforward :)

I actually looked at the Contains function in the Character functions' help, but was getting confused with the description as it says that it will retrieve a "numeric position". Your solution was pretty much what I mentioned on my post, just had to converted into an actual JMP formula using the contains function.

Cheers,

Vasco

Re: How to find a specific text (word) within a column character - nominal data type ?

VCO — Wed, 22 Apr 2020 08:50:48 GMT

Hi @vince_faller ,

I must admit that your proposal is a bit too much advanced for me at this point.Thanks for your input., though :)

I really liked Karen's simple proposal for my problem.

Regards,

Vasco

Re: How to find a specific text (word) within a column character - nominal data type ?

KarenC — Wed, 22 Apr 2020 13:58:59 GMT

Ah, I see the confusion. So since we used the full string that you were looking for in the contains function we got either a "1" as the string started in position 1 or a 0 since the string didn't start in any position. This worked in the If statement. If we had used Contains(:column, "P") it would have returned "3" for any row with a JMPx value, since P is in the third position. I didn't think (or really know) about that nuance as I typically use the contain function as you were trying to do...is something there or not. The Regex solution is more sophisticated and is a good solution for something that is going to be used repeatability with changing data.