topic Re: Special Characters substitution in column names in Discussions

Special Characters substitution in column names

robert_j_maurie — Tue, 13 Sep 2016 21:42:20 GMT

Hello All, I am trying to substitute a simple underscore ( _ ) into each column name that has any conceivable "special" character without having to do individual if statements for each character.

Say column 1 is labeled Test1%good#parts, column 2 is labeled Test2*Fail#Opens, etc

I want the renamed column names consistent- Test1_good_parts, Test2_Fails_Opens, etc.. The data in the columns are counts (numeric and continuous or ordinal).

I've tried versions of:

spec_char = or(" ","~","`","!","@","#","$","%","^","&","*","(",")","-","+","\","|","[","[","]","<",">","?","/","." // no underscore here

);

dt = current data table();

col_name_list = dt<< get column names( string );

nc = N Cols( dt );

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

one_col_name = col_name_list;

If(

Contains( one_col_name, eval(spec_char) ),

Column( i ) << set name( Substitute( one_col_name, eval(spec_char) , "_" ) )

);

But rep is a pattern where eval(spec_char) is looking for a character.

Is there a clean way to perform this type of group substitution?

Thanks.

Re: Special Characters substitution in column names

txnelson — Wed, 14 Sep 2016 01:30:55 GMT

Here is a script that will do what you want:

Names Default To Here( 1 );

dt = Current Data Table();

colnames = dt << get column names( string );

For( i = 1, i <= N Items( colnames ), i++,

wordcount = 1;

thenewcolname = "";

While( Word( wordcount, colnames, " !@#$%^&*()-+[]<>?/\|" ) != "",

If( wordcount > 1,

thenewcolname = thenewcolname || "_"

);

thenewcolname = thenewcolname || Word( wordcount, colnames, " !@#$%^&*()-+[]<>?/\|" );

wordcount = wordcount + 1;

);

Column( dt, colnames) << set name( thenewcolname ) ;

);

Re: Special Characters substitution in column names

ms — Wed, 14 Sep 2016 12:26:20 GMT

The Regex function should work well for this:

dt = Current Data Table();

For(i = 1, i <= N Col(dt), i++,

Column(dt, i) << set name(Regex(Column(dt, i) << get name, "\W", "_", GLOBALREPLACE))

);

Re: Special Characters substitution in column names

robert_j_maurie — Wed, 14 Sep 2016 15:53:09 GMT

Thank you both MS and Jim, both work very smooth!

Rob M.