topic Sum all Columns that contain a string in the column name in Discussions

Sum all Columns that contain a string in the column name

dfmoore — Fri, 05 Apr 2013 23:02:25 GMT

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!

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

ms — Sat, 06 Apr 2013 00:47:57 GMT

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^))" ) ) );

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

dfmoore — Sat, 06 Apr 2013 14:38:41 GMT

Works like a charm!

Thanks,

Daniel