BookmarkSubscribeSubscribe to 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
ms

Super User

Joined:

Jun 23, 2011

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

ms

Super User

Joined:

Jun 23, 2011

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

));