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

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

stephen_pearson — Mon, 12 Feb 2018 21:35:23 GMT

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);
);

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

txnelson — Mon, 12 Feb 2018 21:36:56 GMT

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>);

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

vince_faller — Mon, 12 Feb 2018 21:37:22 GMT

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;

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

Craige_Hales — Mon, 12 Feb 2018 21:37:54 GMT

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");

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

Craige_Hales — Mon, 12 Feb 2018 21:38:30 GMT

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.

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

Craige_Hales — Mon, 12 Feb 2018 21:35:59 GMT

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 );

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

stephen_pearson — Tue, 20 Sep 2016 06:58:16 GMT

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

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

vince_faller — Thu, 12 Jan 2017 23:18:50 GMT

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 );

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

gerd — Sat, 10 Feb 2018 21:17:53 GMT

Thank you! I've been looking for sth like this for hours.