topic Re: optimizing an IF statement? in Discussions

optimizing an IF statement?

jmpbeginner — Mon, 18 Jan 2016 01:30:19 GMT

I have a list of specific values I am interested in identifying in my data set (specific values of the :MeasurementGroup column). I created a new column called "Value of Interest?" with a formula to generate an indicator: either YES or NO

dt1 << New Column( "Value of Interest?",

Character,

Nominal,

Formula( If(

:MeasurementGroup == "A", "YES",

:MeasurementGroup == "B", "YES",

:MeasurementGroup == "C", "YES",

:MeasurementGroup == "D", "YES",

:MeasurementGroup == "E", "YES",

:MeasurementGroup == "F", "YES",

:MeasurementGroup == "G", "YES",

:MeasurementGroup == "AA", "YES",

:MeasurementGroup == "BB", "YES",

:MeasurementGroup == "CC", "YES",

:MeasurementGroup == "ZZ", "YES",

:MeasurementGroup == "ABC", "YES",

:MeasurementGroup == "DEC", "YES",

:MeasurementGroup == "WSA", "YES",

:MeasurementGroup == "RSS", "YES",

:MeasurementGroup == "DVC", "YES",

:MeasurementGroup == "SFS", "YES",

:MeasurementGroup == "AAZ", "YES",

:MeasurementGroup == "ZZA", "YES",

:MeasurementGroup == "TTT", "YES",

"NO") )

);

My datatable has close to 1 million rows... and it seems to churn for a bit before populating. Is there a more efficient way to acheive this same result?

Any help is greatly appreciated! thanks

Re: optimizing an IF statement?

txnelson — Mon, 18 Jan 2016 02:13:39 GMT

I would try using the LOC() function

if(N Rows(Loc({"A","B","C",...............},MeasurementGroup))>0,"Yes","NO")

Re: optimizing an IF statement?

ms — Mon, 18 Jan 2016 13:23:47 GMT

The Match function is effective for multiple choices. I did a quick test: with a million rows Match, Loc and If complete in 2.1 sec, 6.9 sec and 10.2 sec, respectively.

dt << New Column("Value of Interest",

Character,

Nominal,

Formula(

Match(:MeasurementGroup,

"A", "YES",

"B", "YES",

"C", "YES",

"D", "YES",

"E", "YES",

"F", "YES",

"G", "YES",

"AA", "YES",

"BB", "YES",

"CC", "YES",

"ZZ", "YES",

"ABC", "YES",

"DEC", "YES",

"WSA", "YES",

"RSS", "YES",

"DVC", "YES",

"SFS", "YES",

"AAZ", "YES",

"ZZA", "YES",

"TTT", "YES",

"NO"

)

);

Re: optimizing an IF statement?

msharp — Tue, 19 Jan 2016 17:02:11 GMT

Give this man an award! This is great.

Re: optimizing an IF statement?

jmpbeginner — Tue, 19 Jan 2016 17:33:28 GMT

Jim, thank you for the quick response! I was able to implement the LOC function and see an immediate improvement in speed!

MS, thank you for improving upon this further and teaching me the MATCH function (and providing the speed comparison)!

Re: optimizing an IF statement?

brady_brady — Wed, 20 Jan 2016 04:42:36 GMT

Hi JMPBeginner,

**** Please refer to the attached .jsl file to see these scripts as intended. I have been having trouble getting my posts to display correctly, and if you copy/paste or type in the scripts that appear below, I am not sure if they will work. They look incorrect when I post them, but they look OK when I edit, so I am not sure what to do except attach a script file. My apologies!

*****

If you are willing to use value labels (for large tables, this can save a good deal of disk space, as well), you have another couple of speedy options:

1) A formula-based approach using the Contains() function:

dt << New Column( "y/n 2",

formula(

Contains(

{"A", "B", "C", "D", "E", "F", "G", "AA", "BB", "CC", "ZZ", "ABC", "DEC", "WSA", "RSS", "DVC", "SFS", "AAZ", "ZZA", "TTT"},

:MeasurementGroup

) != 0

<<Add Column Properties( value labels( {0, 1}, {"NO", "YES"} ) )

);

2) A non-formula based approach: JMP works with tables very quickly; the speed of this approach shocked me a little, given that a completely new table must be created and closed:

dt = Current Data Table();

dt2 = New Table( "mt", invisible );

Column( dt2, 1 ) << datatype( character ) << set name( "MeasurementGroup" ) << set values(

{"A", "B", "C", "D", "E", "F", "G", "AA", "BB", "CC", "ZZ", "ABC", "DEC", "WSA", "RSS", "DVC", "SFS", "AAZ", "ZZA", "TTT"}

);

dt2 << New Column( "y/n", <<set each value( 1 ) );

dt << New Column( "y/n", <<set each value( 0 ) );

dt << Update( With( dt2 ), Match Columns( :Measurement Group = :MeasurementGroup ), Add Columns from Update table( None ) );

Column( dt, "y/n" ) << Add Column Properties( value labels( {0, 1}, {"NO", "YES"} ) );

Close( dt2, nosave );

On a million row table, I was getting runtimes of ~ 2.4 seconds for the Match function. (MS's computer seems to be faster than mine ;)

The runtimes of approach 1 above were ~ 0.8 seconds, while the runtimes of approach 2 were ~ 0.4 seconds.

Cheers,

Brady

Re: optimizing an IF statement?

pmroz — Wed, 20 Jan 2016 14:47:58 GMT

Looks good Brady!

BTW when I post code I copy/paste the JSL into a word document, and then copy/paste from there to the forum. That preserves the look of the JSL editor.

For example:

dt << New Column( "y/n 2",

formula(

Contains({"A", "B", "C", "D", "E", "F", "G", "AA", "BB", "CC", "ZZ", "ABC", "DEC", "WSA",

"RSS", "DVC", "SFS", "AAZ", "ZZA", "TTT"}, :MeasurementGroup

) != 0

);

Re: optimizing an IF statement?

brady_brady — Wed, 20 Jan 2016 16:06:25 GMT

Thanks Peter, I will use this trick from now on when I post.

Cheers,

Brady