Our World Statistics Day conversations have been a great reminder of how much statistics can inform our lives. Do you have an example of how statistics has made a difference in your life? Share your story with the Community!
Choose Language Hide Translation Bar
Highlighted

## Apply formula to new columns based on old columns' data

Hello everyone,

I am completely new to JMP scripting.

I am trying to create a script that will go to each character column in a table, find all its values (PASS, FAIL, etc..), and create new columns suffixed with "_CODED" using assigned values (1, 2, 3, 4, 5) based on the aforementioned ones.

Here's the script I have right now, that works, but only takes into account a single column. Is there a way I can insert a variable in place of that column name so all columns are accounted for?

dt = Data Table("datatable.jmp");

cols = dt << get column names(Character); //List all character columns

For(i = 1, i <= N Items(cols), i++,

dt << New Column(cols << get name || "_CODED",

Numeric,

"Nominal",

Format("Best", 12),

Formula(Name Expr(

If(:Name("Level1") == "PASS", 1, If(:Name("Level1") == "FAIL", 2, If(:Name("Level1") == "", 3, If(:Name("Level1") == "NOTTESTED", 4, If(:Name("Level1") == "IGNORE", 5, If(:Name("Level1") == "PASS|PASS", 6, If(:Name("Level1") == "IGNORE|IGNORE", 7, If(:Name("Level1") == "NOTTESTED|NOTTESTED", 8))))))))

))

);

);

Basically, I'd like to know if there is a way to have the script go through with each Character column in a table instead of only Level 1​. I have tried to use cols ​as a variable in place of Level 1​, but this caused the script to not run because quotation marks were needed, or the script would look for a column named "Level 1", which is not the objective.

I took pieces of other scripts and tried to apply them to this one, so please also let me know if there is anything redundant. Thanks a lot.

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted

## Re: Apply formula to new columns based on old columns' data

This will do it:

New Table( "Example", Add Rows( 6 ),

New Column( "aaa", Character, Nominal,

Set Values( {"PASS", "PASS", "FAIL", "IGNORE", "", "IGNORE"} )

),

New Column( "bbb", Character, Nominal,

Set Values( {"PASS|PASS", "IGNORE|IGNORE", "PASS", "", "FAIL", "FAIL"} )

)

);

dt = Data Table("Example");

cols = dt << get column names(Character); //List all character columns

For (i = 1, i <= N Items(cols), i++,

one_col   = cols[i] << get name;

coded_col = one_col || "_CODED";

col_expr = evalinsert(

"\[   dt << New Column("^coded_col^", Numeric, "Nominal",

Format("Best", 12), Formula(

if (:name("^one_col^") == "PASS", 1,

:name("^one_col^") == "FAIL", 2,

:name("^one_col^") == "", 3,

:name("^one_col^") == "NOTTESTED", 4,

:name("^one_col^") == "IGNORE", 5,

:name("^one_col^") == "PASS|PASS", 6,

:name("^one_col^") == "IGNORE|IGNORE", 7,

:Name("^one_col^") == "NOTTESTED|NOTTESTED", 8;

);

)

);]\");

eval(parse(col_expr))

);

2 REPLIES 2
Highlighted

## Re: Apply formula to new columns based on old columns' data

This will do it:

New Table( "Example", Add Rows( 6 ),

New Column( "aaa", Character, Nominal,

Set Values( {"PASS", "PASS", "FAIL", "IGNORE", "", "IGNORE"} )

),

New Column( "bbb", Character, Nominal,

Set Values( {"PASS|PASS", "IGNORE|IGNORE", "PASS", "", "FAIL", "FAIL"} )

)

);

dt = Data Table("Example");

cols = dt << get column names(Character); //List all character columns

For (i = 1, i <= N Items(cols), i++,

one_col   = cols[i] << get name;

coded_col = one_col || "_CODED";

col_expr = evalinsert(

"\[   dt << New Column("^coded_col^", Numeric, "Nominal",

Format("Best", 12), Formula(

if (:name("^one_col^") == "PASS", 1,

:name("^one_col^") == "FAIL", 2,

:name("^one_col^") == "", 3,

:name("^one_col^") == "NOTTESTED", 4,

:name("^one_col^") == "IGNORE", 5,

:name("^one_col^") == "PASS|PASS", 6,

:name("^one_col^") == "IGNORE|IGNORE", 7,

:Name("^one_col^") == "NOTTESTED|NOTTESTED", 8;

);

)

);]\");

eval(parse(col_expr))

);

Highlighted

## Re: Apply formula to new columns based on old columns' data

Thanks a lot, it works!

Article Labels

There are no labels assigned to this post.