Subscribe Bookmark RSS Feed

using column names in loops

qt

Community Trekker

Joined:

Oct 17, 2012

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

1 ACCEPTED SOLUTION

Accepted Solutions
Solution

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

));

5 REPLIES
qt

Community Trekker

Joined:

Oct 17, 2012

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

Solution

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

));

qt

Community Trekker

Joined:

Oct 17, 2012

Thanks!

This evalinsert really helps. Now the code is working!

Best regards,

Qian

ms

Super User

Joined:

Jun 23, 2011

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

));

qt

Community Trekker

Joined:

Oct 17, 2012

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

));