turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- JMP User Community
- :
- Discussions
- :
- Discussions
- :
- Trouble converting columns from character to numeric

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

May 8, 2012 8:51 AM
(6356 views)

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

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.

6 REPLIES

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

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

Art

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

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?

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

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.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Bingo!

Many thanks!

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

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

**)**

**)**;