cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
JMP is taking Discovery online, April 16 and 18. Register today and join us for interactive sessions featuring popular presentation topics, networking, and discussions with the experts.
Choose Language Hide Translation Bar
jmpbeginner
Level III

optimizing an IF statement?

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

1 ACCEPTED SOLUTION

Accepted Solutions
ms
Super User (Alumni) ms
Super User (Alumni)

Re: optimizing an IF statement?

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"

        )

    )

);

View solution in original post

7 REPLIES 7
txnelson
Super User

Re: optimizing an IF statement?

I would try using the LOC() function

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

Jim
ms
Super User (Alumni) ms
Super User (Alumni)

Re: optimizing an IF statement?

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"

        )

    )

);

msharp
Super User (Alumni)

Re: optimizing an IF statement?

Give this man an award!  This is great.

jmpbeginner
Level III

Re: optimizing an IF statement?

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?

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

pmroz
Super User

Re: optimizing an IF statement?

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?

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

Cheers,

Brady