Subscribe Bookmark RSS Feed

How to check if a list contents is all numeric without loops

stephen_pearson

Community Trekker

Joined:

Oct 6, 2014

Hi,

I am writing some code which imports data and I would like to do some data checking similar to MessyTables for python.

Data can be accidentally imported as character due to "NULL" or similar being present in the data. After removing the common instances of these I would like to check the data again to see if it is numeric.

  • The easiest way to do this is to use Try() and Matrix() after removing any "" around items in the list.
  • I could loop through the columnKeys and if the Num() conversion fails trigger a Throw(), this would be wasteful if the table is long and the column has many unique numbers.

The code below writes an error to the long, which is fine. However it also triggers a JMP Alert window once I incorporate it into my add-in but not if I run it directly.


Any suggestions of alternatives to check if a list is numeric or to suppress the JMP Alert dialog?


Thanks,


Stephen


dt = New Table( "Check Data",

  Add Rows( 2 ),

  New Column( "Column 1", Character, "Nominal", Set Values( {"+1%", "2", "-45"} ) )

);

a = Column(dt, "Column 1" ) << Get Values;

Try(

  b = Matrix( Parse( Substitute( Char( a ), "\!"", "", " " ) ) );

  Show( "Converted to Numeric." );

  Show(b);

  ,

  Show( "Not all Numeric..." );

  Show(a);

);

1 ACCEPTED SOLUTION

Accepted Solutions
Solution

Finally, to suppress the dialog, use the BatchInteractive function.  Notice there are two calls, one before, and one after.  If you leave JMP running in the BatchInteractive(1) state, you'll have a hard time closing JMP.

dt = New Table( "Check Data",

    Add Rows( 2 ),

    New Column( "Column 1", Character, "Nominal", Set Values( {"1", "2a", "-45"} ) )

);

a = Column( dt, "Column 1" ) << Get Values;

Batch Interactive(1);

cap = Log Capture(

    Try(

        Parse( "[" || Concat Items( a, "," ) || "]" );

        result = "ok";

    ,

        result = "bad"

    )

);

Batch Interactive(0);

Show( result );

Craige
7 REPLIES
txnelson

Super User

Joined:

Jun 22, 2012

I find the easiest way to check to see if an imported column should actually be numeric, when in fact it was imported as character is to use this simple line of code.  It returns the number of numeric values found:

numberofnumerics = Col Number(num(<the target column>));

you can also get a ratio of the number of numerics to the number of non blank columns

ratio = Col Number(num(<the target column>)) / Col Number(<the target column>);

Jim
vince_faller

Super User

Joined:

Mar 17, 2015

This seems pretty fast. It just uses JMPs feature(?) where it just turns non-numeric values into missing values if the column is specified numeric.

*Edit for speed*

lista =  {"+1%", "2", "-45", ""};

dt = New Table( "Check Data",

  New Column( "Character", Character, "Nominal", Set Values( lista ) ),

  New Column("Numeric", Numeric, Set Values( lista )),

  invisible

);

if( ColNMissing(Column(dt, "Character")) ==  ColNMissing(Column(dt, "Numeric")),

       ret = as list(Column(dt, "Numeric") << get values),

       ret = Expr(print("Non-Convertable"));

);

close(dt, no save);

ret;

Craige_Hales

Staff

Joined:

Mar 21, 2013

Here's a variation on your original idea:

dt = New Table( "Check Data",

  Add Rows( 2 ),

  New Column( "Column 1", Character, "Nominal", Set Values( {"1", "2%", "-45"} ) )

);

a = Column(dt, "Column 1" ) << Get Values;

try(parse("["||concatitems(a,",")||"]"),"nope")

Craige
Craige_Hales

Staff

Joined:

Mar 21, 2013

and to suppress the log messages:

dt = New Table( "Check Data",

    Add Rows( 2 ),

    New Column( "Column 1", Character, "Nominal", Set Values( {"1", "2", "-45"} ) )

);

a = Column( dt, "Column 1" ) << Get Values;

cap = Log Capture(

    Try(

        Parse( "[" || Concat Items( a, "," ) || "]" );

        result = "ok";

    ,

        result = "bad"

    )

);

Show( result );

Re: how to bypass parse("a+b)") error? discussed logcapture and try.


edit: removed redundant assignment to result.

Craige
Solution

Finally, to suppress the dialog, use the BatchInteractive function.  Notice there are two calls, one before, and one after.  If you leave JMP running in the BatchInteractive(1) state, you'll have a hard time closing JMP.

dt = New Table( "Check Data",

    Add Rows( 2 ),

    New Column( "Column 1", Character, "Nominal", Set Values( {"1", "2a", "-45"} ) )

);

a = Column( dt, "Column 1" ) << Get Values;

Batch Interactive(1);

cap = Log Capture(

    Try(

        Parse( "[" || Concat Items( a, "," ) || "]" );

        result = "ok";

    ,

        result = "bad"

    )

);

Batch Interactive(0);

Show( result );

Craige
stephen_pearson

Community Trekker

Joined:

Oct 6, 2014

Craige@JMP​ Thanks, that worked a charm and I'm sure it something I will use again with other try statements.

vince_faller

Super User

Joined:

Mar 17, 2015

Probably should remove any missing values from the list as that will cause issues as well. 

 

Names Default to here(1);
a = {"1", "2", "", "45", "", "3"};
x = repeat(1, nitems(a));
x[loc(a, "")] = 0;
a = a[loc(x)];
Batch Interactive(1);
cap = Log Capture(
    Try(
        Parse( "[" || Concat Items( a, "," ) || "]" );
        result = "ok";
    ,
        result = "bad"
    )
);
Batch Interactive(0);
Show( result );