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
tomt
Level I

Trouble converting columns from character to numeric

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


1 ACCEPTED SOLUTION

Accepted Solutions
pmroz
Super User

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.

View solution in original post

6 REPLIES 6

Re: Trouble converting columns from character to numeric

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

Art

mpb
mpb
Level VII

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" ...

tomt
Level I

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<<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?

pmroz
Super User

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.

tomt
Level I

Re: Trouble converting columns from character to numeric

Bingo!

Many thanks!

ms
Super User (Alumni) ms
Super User (Alumni)

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 )

  )

);