cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
JMP is taking Discovery online, April 16 and 18. Register today and join us for interactive sessions featuring popular presentation topics, networking, and discussions with the experts.
Choose Language Hide Translation Bar
dfmoore
Level II

Sum all Columns that contain a string in the column name

I'm trying to take a data table and add a column with a formula that sums all columns that contain the string "ToAdd" in the column name. I'm trying to script this because the columns that will have "ToAdd" somewhere in the name will change.

So far, I have the script that can collect all the column names into a list, but I'm not sure how to then use that list to generate a new column with a formula that sums all those columns together. This seemed like the best way to start, but not sure where to go from here (or if it really is a good way to start)

dt = current data table();

N = N Col( dt );

AddColumns = {};

For( i=1, i <= N, i++,

    colname = Column(i) << getname();

    If( Contains( colname, "ToAdd") > 1,

        Insert Into(AddColumns, colname )

    );

    )

);

Thanks for any help in advance!

1 ACCEPTED SOLUTION

Accepted Solutions
ms
Super User (Alumni) ms
Super User (Alumni)

Re: Sum all Columns that contain a string in the column name

It's a good start. To set column formulas by script, however, is a bit tricky as the argument is interpreted literally. Building a formula dynamically usually involves building strings or expressions that finally is parsed/evaluated. Otherwise local or session-specific variables will end up in the formula and it will not work as expected. At least that is my experience.

Here's an example using the string approach:

dt = Current Data Table();

N = N Col( dt );

s = "";

For( i = 1, i <= N, i++,

  colname = Column( i ) << getname();

  If( Contains( colname, "ToAdd" ) > 0,

  s = s || If( Length( s ) > 0, ", ", "" ) || colname

  );

);

show(s);

col = dt << New Column( "Sum(AddColumns)", numeric );

Eval( Parse( Eval Insert( "col<<set formula(sum(^s^))" ) ) );

View solution in original post

2 REPLIES 2
ms
Super User (Alumni) ms
Super User (Alumni)

Re: Sum all Columns that contain a string in the column name

It's a good start. To set column formulas by script, however, is a bit tricky as the argument is interpreted literally. Building a formula dynamically usually involves building strings or expressions that finally is parsed/evaluated. Otherwise local or session-specific variables will end up in the formula and it will not work as expected. At least that is my experience.

Here's an example using the string approach:

dt = Current Data Table();

N = N Col( dt );

s = "";

For( i = 1, i <= N, i++,

  colname = Column( i ) << getname();

  If( Contains( colname, "ToAdd" ) > 0,

  s = s || If( Length( s ) > 0, ", ", "" ) || colname

  );

);

show(s);

col = dt << New Column( "Sum(AddColumns)", numeric );

Eval( Parse( Eval Insert( "col<<set formula(sum(^s^))" ) ) );

dfmoore
Level II

Re: Sum all Columns that contain a string in the column name

Works like a charm!

Thanks,

Daniel