topic Re: How can I loop through column names in a for statement? in Discussions

How can I loop through column names in a for statement?

Liz_S — Wed, 14 Nov 2018 18:09:37 GMT

Hi, I'm trying to create a new formula column that will loop through preceding columns, The preceding columns are in two groups, each group has column names which start with one of two strings. Then these column headers end in integers, 0, 1, 2,...up through 22. Please see attached pdf screenshot of my code and let me know why it won't work. Having not used JMP before yesterday, I'm guessing that I may be using "i" or "%i" incorrectly in the column names within the IF statement.

Thanks in advance!

Re: How can I loop through column names in a for statement?

gzmorgan0 — Thu, 15 Nov 2018 10:11:00 GMT

Your PDF is difficult to read. Will only one of the 22 colums have "Matriculated"? Or will several and you want all organization codes?

Re: How can I loop through column names in a for statement?

gzmorgan0 — Thu, 15 Nov 2018 11:14:52 GMT

There are multiple methods to accomplish what I think you need. First of all you need to understand JMP syntax for referencing columns. For example, if you wanted to create a new column that randomly choose organization 1-5 here would be teh column formula

i = Random Integer(1,5);
Column( "designation_organization_" || Char( i ) )[Row()];

|| is the concatenation operator, Char(i) is the string. Then brackets represent the row from the sepcified column.

I have attached a table. I do not have any idea what your table looks like, but maybe this script will help.

Open the attached table. Then run this script

dt=data table("matriculation");
cnmes = dt << get column names("string");

dcols = {};  //create a list of decision columns
for(i=1, i<=nitems(cnmes), i++,
   if( contains(cnmes[i], "_decision_"), Insert into(dcols, cnmes[i]) )
);

//create a new column
matcol = dt << New Column("Major", Character);

//rowwise
for(i=1, i <= nrow(dt) , i++, 
	k = Contains(dt[i,dcols], "Matriculated"); 
        //dt[ i, dcols] is a list of values in the decision columns from row i
        //contains returns the position in the list for "Matriculated"
	matcol[i] = if(k>0, Column("designation_organization_" || char(k))[i], "None" );
        //if k > 0, "Matriculated" was found, so assign to matcol the organization for the corresponding k 
);

Note, JMP allows using column numbers. Here is a script, for a new column for the attached table, that uses column numbers

Re: How can I loop through column names in a for statement?

Liz_S — Thu, 15 Nov 2018 17:08:18 GMT

Hi gzmorgan0,

The first script you wrote for the table you attached does what I would like and is brilliant! I'll be able to finish my project at work today. Thanks! However, since this is day 3 for me in JMP, I would like to understand how to write the last option you wrote using the formula editor in JMP. When I tried to create the new column in your table after running the first script I encountered two problems:

1.) I could not use two independent functions in the column formula editor in two separate statements. I started with the Contains function and added a ";" after it. But then when I clicked on the If function, it embedded the Contains statement automatically. Weird. I don't want Contains embedded within If or vice versa.

2.) I didn't see how to add the row() and Index(6,10) to the Contains( Current Data Table (),... The ^ option gave me a new comma separated "part": Contains( Current Data Table(), part, "Matriculated"). How did you insert the rows / column options to your Contains function in the formula editor?

I have gone through the welcome kit tutorials but did not see enough info on scripting and using the formula editor in a new column. Do you happen to know of a Youtube video you could recommend for beginners? But I would also appreciate another reply from you if you have time.

Many Thanks!

Re: How can I loop through column names in a for statement?

gzmorgan0 — Thu, 15 Nov 2018 18:47:11 GMT

Sometimes putting a multi-statement, formula is painful and it is better to type it in.

When you double click in the formula it opens a script window. This might take a little practice. Maybe copy the formula that uses the column numbers and paste it in the blue box, then double click to bring up the script editor.

I hope that helps.

Re: How can I loop through column names in a for statement?

Liz_S — Thu, 15 Nov 2018 18:58:11 GMT

First thing I tried was to copy and paste the blue box formula into a new column script window. It doesn't paste; it is as if the information in the blue box in your solution is a picture or screen shot.
Yes, I do know how to double click in a formula box to be able to type in a script. I still can't see how to add row and column indices to a Contain statement. But thanks again for the earlier parts to your response.

Re: How can I loop through column names in a for statement?

gzmorgan0 — Thu, 15 Nov 2018 19:01:51 GMT

Here is the formula that works for the Matriculation data table, this is just JSL text.

k = Contains( Current Data Table()[Row(), Index( 6, 10 )], "Matriculated" );
If( k > 0,
	Column( "designation_organization_" || Char( k ) )[Row()],
	"None"
);

Re: How can I loop through column names in a for statement?

Liz_S — Thu, 15 Nov 2018 20:03:57 GMT

Hi,

This script worked perfectly and thanks for helping me around my unfamiliarity with the JMP formula editor. This elegant code was more what I had in mind when I starting trying to write my loop. Just one last question, could you please explain your selection of the numbers 6 and 10 within the Index statement?

I'm guessing that the Index numbers 6 and 10 were because there were 5 of each of the two column types in your test file and 10 columns altogether? So 6 starts the set of decision columns and 10 ends the decision columns? (Decided to avoid guessing and just ask you.)

Feeling grateful!

Liz_S

Re: How can I loop through column names in a for statement?

gzmorgan0 — Fri, 16 Nov 2018 00:01:14 GMT

Liz_s, yes these are referring to column numbers.

From the JMP Main Menu, select Help > Books > Scripting Guide and go page 439 (or near by). This is a section in chapter 9 Data Tables, section 1, Working with Data Table Objects, topic Accessing Data Values . Read that entire section for alternate methods to access and assign data values.

Comment: The key to your task was to find which decision column contained "Matriculated" for each row. Looping 22+ times for each row is a lot of nesting. The key here to learn more is to use the Help > Scripting Index > Functions to learn about Contains.

Contains( list|string, matching string) returns the first position where it is found. Note: Loc() returns all positions in a list or matrix where the matching value is found. Understanding that dt[row, c1::cn] where columns c1 through cn are character will return a list of the table values. Contains() will find the position (k), so this will be the correct position for the organization column.

The key is to "know your tools" how to access and assign and know your functions.

Good luck with your journey learning JMP.