topic Trouble converting columns from character to numeric in Discussions

Trouble converting columns from character to numeric

tomt — Tue, 08 May 2012 15:51:30 GMT

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

IF(Data table(i)<<GetName() =="Sort data",

Continue();

close(DataTable(i));

);

dt<<delete rows({1,2,3,4,5,6,7,8,9}); // the first nine rows are not data - is there a way to capture this in the open() command?

column (1)<<set name("Wafer ID");

column (2)<<set name("Xref-Yref");

column (3)<<set data type("numeric"); // this doesn't work....

//column ({3,4,5,6,7,8,9,10,11,12,13}) <<set data type(numeric); //nor does this

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

col<<set formula(:vmp *:imp /:Voc /:Isc); // this returns errors as I can't change the offending columns to numeric.

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

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

column("voc")<<set data type("numeric"); //still doesn't work

Re: Trouble converting columns from character to numeric

Tue, 08 May 2012 15:56:26 GMT

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

Art

Re: Trouble converting columns from character to numeric

mpb — Tue, 08 May 2012 18:58:44 GMT

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

dt:voc << etc...

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

Re: Trouble converting columns from character to numeric

tomt — Wed, 09 May 2012 13:26:15 GMT

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<<set formula(:vmp *:imp /:Voc /:Isc);

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

pmroz — Wed, 09 May 2012 13:50:51 GMT

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

tomt — Wed, 09 May 2012 13:54:40 GMT

Bingo!

Many thanks!

Re: Trouble converting columns from character to numeric

ms — Wed, 09 May 2012 13:56:38 GMT

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 )

)

);