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

Special Characters substitution in column names

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.

2 ACCEPTED SOLUTIONS

Accepted Solutions
txnelson
Super User

Re: Special Characters substitution in column names

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

);

Jim

View solution in original post

ms
Super User (Alumni) ms
Super User (Alumni)

Re: Special Characters substitution in column names

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

);

View solution in original post

3 REPLIES 3
txnelson
Super User

Re: Special Characters substitution in column names

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

);

Jim
ms
Super User (Alumni) ms
Super User (Alumni)

Re: Special Characters substitution in column names

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

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

Rob M.