topic Re: How can I scan the text in one column, and put a key term into another column? in Discussions

How can I scan the text in one column, and put a key term into another column?

BunnyBoi12 — Tue, 01 Aug 2023 17:16:03 GMT

Hello all!

This is my first post on this board so I apologize if it is not formatted correctly. I am in the process of coming up with a new script to analyze a 12 experiment stacked data set. I need to take some key phrases from our "Batch ID" column and organize them into other columns using a formula/script (preferably formula). So in this scenario I need the "B01" phrase to go into the Bioreactor column in the same row as the batch ID containing that phrase, "VBTS-935" to do the same with the Strain column, and the date to do the same with the Date column. This is currently how our text data is formatted, but if anyone has suggestions on how to reformat it for an easier analysis please let me know!

Thank you for the help in advance.

Re: How can I scan the text in one column, and put a key term into another column?

StarfruitBob — Tue, 01 Aug 2023 17:24:28 GMT

Hello @BunnyBoi12 ,

I'd suggest adding formulas into the last 3 columns. Here are some examples you can investigate.

Bioreactor: word(2, :Batch ID, ":")

Strain: word(2, :Batch ID, ", ") // Note that there's a space in the quotes as well!

Date: word(3, :Batch ID, ", : ")

Additional note on the date column, you can wrap the word() function in a format() function and format your date if you would like.

Help > Scripting Index is also an excellent resource to explore functions that may be beneficial to you!

Re: How can I scan the text in one column, and put a key term into another column?

jthi — Tue, 01 Aug 2023 18:12:31 GMT

There are quite a few options on how you could perform this (word() and words() are few functions you could use or Regex() if you are familiar with regular expressions).

One thing I would also suggest is to explore Recode (right click on column header)

for example it has Extract Segments option (and many more)

After you have split your column, you should be able to get script from Enhanced Log

And Text To Columns which might help you to make the large problem into few smaller ones.

Also, when you post questions to JMP community, it is generally a good idea if you can provide some sort of data (mock-up or real if it can be shared) and include desired results column(s) at least for few rows to help when testing out different solutions.

Re: How can I scan the text in one column, and put a key term into another column?

BunnyBoi12 — Tue, 01 Aug 2023 19:11:40 GMT

Awesome thank you! Ill definitely use the recode option more often with those uses.

Re: How can I scan the text in one column, and put a key term into another column?

BunnyBoi12 — Tue, 01 Aug 2023 19:13:06 GMT

would you mind explaining what the "2" and "3" mean? Your formulas worked like a charm but the explanation that JMP gives is wayyyyyy over my head when it comes to the formula context and I want to make sure I cna teach the rest of my team how to use this properly

Re: How can I scan the text in one column, and put a key term into another column?

StarfruitBob — Tue, 01 Aug 2023 19:37:00 GMT

Let's say we have a string:

mystr = "my,name,is,starfruitbob"

word( <number here>, <what I'm processing>, <all delimiters, in string format> )

In my opinion, it's best to learn what each of these do slightly out of order:

<what I'm processing> : This could be a string variable or column, for example. This is the input. Must be character type.
<delimiter> : "string things here!" <-- put all of the delimiters you wish in here. EACH of them, individually, will be used as a delimiter, even if you put a space in there!
<number here> : Depending how you're delimiting your input, it will be separated into different "chunks". The number is what "chunk" you want to return.

Let's look at an example:

mystr = "my,name,is,starfruitbob";
// Note where the commas are in the variable above
print( word( 1, mystr, "," ) ); // Returns: my
print( word( 3, mystr, "," ) ); // Returns: is

// If the delimiter is changed...
print( word( 1, mystr, "s" ) ); // Returns: my,name,i
// This is because it will return the first characters BEFORE the new delimiter "s"
print( word( 2, mystr, "s" ) ); // Returns: ,
// There's only a single comma between the two "s" delimiters, and is the second "chunk" of the input

Does this help @BunnyBoi12

Re: How can I scan the text in one column, and put a key term into another column?

BunnyBoi12 — Tue, 01 Aug 2023 19:43:50 GMT

I think so. So if you did

print( word(3, mystr, "s" ) ); it would return "tarfruitbob" ?

Re: How can I scan the text in one column, and put a key term into another column?

StarfruitBob — Tue, 01 Aug 2023 19:45:36 GMT

Exactly. :)

Re: How can I scan the text in one column, and put a key term into another column?

BunnyBoi12 — Tue, 01 Aug 2023 19:49:39 GMT

You're awesome man, I hope you have a fantastic day