Rename columns with Regex

tarkan_bih

Hi,

 

I have a list of a huge number of columns to stack that have column format name of TEST_BOUROUGH_GENDER, as shown below. Now the BOUROUGH can be missing from the column name and that imples the value from the column is from all the BOUROUGHs (ie NYC). I stacking all of the TEST_BOUROUGH_GENDER column names and then creating new columns called test, location and gender (see below). When I run into the case of a column that is missing a BOUROUGH name I would like to add NYC to that column name. I would also prefer if this is could be done with regular expressions. Any thoughts or input is greatly appreciated. 

 

column format name: CALC1_BROOKLYN_M, CALC2_QUEENS_F, CALC2_M, ..

 

 

ref = Current Data Table();

stat stack << ref stack(Source Label Column("Label"), Stacked Data Column("Data"));

stat stack << New Column("test", Formula(Right( :Label, 5 )));

stat stack << New Column("location", Formula(Regex( :Label, "_(.*)_", "\1" )));

stat stack << New Column("gender", Formula(Right( :Label, 1)))

txnelson

This solution on changing the names doesn't use regular expressions, but it is a very simple piece of code:

Names Default To Here( 2 );
dt = Current Data Table();
colNamesList = dt << get column names( string );

For( i = 1, i <= N Items( colNamesList ), i++,
	If( Word( 3, colNamesList[i], "_" ) == "",
		Column( colNamesList[i] ) << set name(
			Word( 1, colNamesList[i], "_" ) || "_NYC_" || Word( 2, colNamesList[i], "_" )
		)
	)
);