nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

using column names in loops

Dear JMP experts,

I have a table with many repetitive columns:

A_X1_Y1, A_X1_Y2, ....A_X1_Y99, A_X2_Y1, A_X2_Y99, ...

B_X1_Y1, B_X1_Y2, ....B_X1_Y99, B_X2_Y1, B_X2_Y99, ...

i.e. Xi, i=1, 99, Yj, j=1,20,

If I want to add multiple columns:

C_Xi_Yj = A_Xi_Yj - B_Xi_Yj

C_?, where C_? = A_? - B_?

Can I use loop with Xi, Yj, or I can use something even more abstract, like: C_? = A_?-B_?

Thanks,

Qian

Accepted answers

Check out the function Evalinsert(). It can be useful when specifying formulas that depends on e.g. counter.

Example with formula columns:

// make example table

dt=new table("test");

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

for( j=1,j<=4,j++,

dt<<new column("A_X"||char(i)||"_Y"||char(j), numeric, values([5,6]))

));

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

for (j=1,j<=4,j++,

dt<<new column("B_X"||char(i)||"_Y"||char(j), numeric, values([2,2]))

));

// Add formula columns

f=expr(parse(evalinsert("A_X"||"^char(i)^"||"_Y"||"^char(j)^-B_X"||"^char(i)^"||"_Y"||"^char(j)^")));

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

for( j=1,j<=4,j++,

dt<<new column("C_X"||char(i)||"_Y"||char(j), numeric, formula(eval(f)))

));

All comments

here is my failed trials, someone please help:

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

for ( j = 1, j <=4, j++,

col_new_name="C_"||char(i)||"_"||char(j);

data table (dt_out) << add multiple columns (col_new_name, 1, "Numeric");

parse (col_new_name) << formula ( ascolumn (parse("A_"||char(i)||"_"||char(j)) -

ascolumn (parse("B_"||char(i)||"_"||char(j)) );

);

The columns with corrected names are added to my table, but the formula is just literally:

parse("A_"||char(i)||"_"||char(j))-parse("B_"||char(i)||"_"||char(j)

I also tried eval(parse (*)), without 'parse', without ascolumn, etc. none is working.

Please help.

Thanks,

Qian

Check out the function Evalinsert(). It can be useful when specifying formulas that depends on e.g. counter.

Example with formula columns:

// make example table

dt=new table("test");

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

for( j=1,j<=4,j++,

dt<<new column("A_X"||char(i)||"_Y"||char(j), numeric, values([5,6]))

));

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

for (j=1,j<=4,j++,

dt<<new column("B_X"||char(i)||"_Y"||char(j), numeric, values([2,2]))

));

// Add formula columns

f=expr(parse(evalinsert("A_X"||"^char(i)^"||"_Y"||"^char(j)^-B_X"||"^char(i)^"||"_Y"||"^char(j)^")));

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

for( j=1,j<=4,j++,

dt<<new column("C_X"||char(i)||"_Y"||char(j), numeric, formula(eval(f)))

));

For very large tables a solution based on matrix operations is probably more efficient.

An example;

// The script assumes that the columns are in the right order: first all A's then all B's in the same order

//dt<<reorder by name;

n=ncol(dt);

AB=dt<<get as matrix;

C=AB[0,1::n/2]-AB[0,n/2+1::n];

dt << set matrix(C);

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

for( j=1,j<=4,j++,

col=Column(n+1); col<<set name("C_X"||char(i)||"_Y"||char(j)); n++

));

Thanks!

This evalinsert really helps. Now the code is working!

Best regards,

Qian

It takes me a while to understand this. It is a very cool idea.

Thanks!

----------------------------------------------------------------------------------------------------------------------------------------

For very large tables a solution based on matrix operations is probably more efficient.

An example;

// The script assumes that the columns are in the right order: first all A's then all B's in the same order

//dt<<reorder by name;

n=ncol(dt);

AB=dt<<get as matrix;

C=AB[0,1::n/2]-AB[0,n/2+1::n];

dt << set matrix(C);

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

for( j=1,j<=4,j++,

col=Column(n+1); col<<set name("C_X"||char(i)||"_Y"||char(j)); n++

));