4850_en-US

liliconcom

Trouble converting columns from character to numeric<meta http-equiv="Content-Type" content="text/html; charset=UTF-8" />

Hello All,

this is my first post - I'm trying to automate data processing where I receive data in the form of excel spreadsheets... There's one tab in the workbook with all the relevant information.

JMP appears to import a lot of the data as character, not numeric, and I can convert the data to numeric manually in the table (usual way) but not with a script:

Any ideas?

thanks

Tom

Set current directory("C:\JDSU\ServerCopy\Data\Post-processing characterization\Flash I-V\AWSC");

dt=open(); // this is the only way I've found to let me select the relevant worksheet "Sort Data" in the .xls file

tables = ntable();

OpenDTs = List();

savethis = list();

closethese = list();

show(dt);

/* THis loop I thought would work to eliminate the open tables which I don't want, but it only closes a few, not all, of the unwanted tables

for(i=1, i<=tables, i++,

Insert into( openDTs, Data Table(i)<

IF(Data table(i)<

  Continue();

  close(DataTable(i));

);

);

*/

dt<

column (1)<

column (2)<

column (3)<

//column ({3,4,5,6,7,8,9,10,11,12,13}) <

col = New COlumn("FF_Calc", numeric);

col<

col = NEw COlumn ("xref", character);

col = new column ("yref", character);

column("voc")<

Re: Trouble converting columns from character to numeric

You need to "run" the column formulas before using them.  Put this line before the for each row statement:

dt << run formulas;

where dt points to your data table.

Re: Trouble converting columns from character to numeric

Tom, I think you want to repost this in the JMP forum.

Art

Re: Trouble converting columns from character to numeric

An example of correct syntax to change a character column to numeric:

column("voc") << Data Type( numeric );

column("voc") << Modeling Type( continuous ); // if you want continuous modeling type

or

dt:voc << etc...

Looks like your "if" statement needs a comma after "continue" ...

Re: Trouble converting columns from character to numeric

Many thanks.

Do you have any idea why a for each row() loop works fine if I run it by highlighting and then Ctrl+R in the JSL window, but it doesn't work when executing as part of the whole script?

col = New COlumn("FF_Calc", numeric) << Modeling Type ( continuous );

col<

New Column("xref", Character, Ordinal, Formula(If(Right(Left(:Name("Xref-Yref"), 2), 1) == "-", Left(:Name("Xref-Yref"), 1), Left(:Name("Xref-Yref"), 2))), Set Property("Value Ordering", {"1", "2", "3", "4", "5", "6", "7", "8", "9", "10", "11", "12", "13", "14", "15", "16"}));

New Column("yref", Character, Ordinal, Formula(If(Left(Right(:Name("Xref-Yref"), 2), 1) == "-", Right(:Name("Xref-Yref"), 1), Right(:Name("Xref-Yref"), 2))), Set Property("Value Ordering", {"1", "2", "3", "4", "5", "6", "7", "8", "9", "10", "11", "12", "13", "14", "15", "16"}));

for each row(

if(:FF_Calc < 0, rowstate() = excluded state(1), :FF_calc > 1, rowstate() = excluded state(1), rowstate() = excluded state(0)

);

);

for each row(

if(dt:FF_Calc < 0, rowstate() = excluded state(1), dt:FF_Calc > 1, rowstate() = excluded state(1), rowstate() = excluded state(0)

);

); // neither of these work in the script, but if executed manually they work fine.  Do I have to select the table or something like that?

Re: Trouble converting columns from character to numeric

Bingo!

Many thanks!

Re: Trouble converting columns from character to numeric

How does it not work? Do you get an error message or just the wrong result?

Anyway, the column variable col is not properly assigned. You have to send the modeling type message either in a separate statement or within New Column(). Once assigned you can use col instead of :FF_Calc.

E.g.

col = New Column( "FF_Calc", numeric, continuous );

col << set values( {-1, 0, 1, 2} );

For Each Row(

  If(

  col < 0, Row State() = Excluded State( 1 ),

  col > 1, Row State() = Excluded State( 1 ),

  Row State() = Excluded State( 0 )

  )

);