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

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

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
Craige_Hales
Super User

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

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

View solution in original post

8 REPLIES 8
txnelson
Super User

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

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
gerd
Level V

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

Thank you! I've been looking for sth like this for hours.
vince_faller
Super User (Alumni)

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

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;

 

Vince Faller - Predictum
Craige_Hales
Super User

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

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
Super User

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

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
Craige_Hales
Super User

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

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

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

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 (Alumni)

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

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 );
Vince Faller - Predictum