topic Re: Select and delete columns that contain specific string in Discussions

Select and delete columns that contain specific string

brandon_mcrv — Fri, 09 Jun 2023 22:07:49 GMT

Hi jsl folks,

Can somebody comment on what's wrong with my coding below? I am trying to look for columns which contain a string called "Dummy" and trying to delete it but it is not working. Pls help.

Names Default To Here( 1 );

dt = New Table( "Example",
    Add Rows( 3 ),
    New Column( "A_Dummy", Character, "Nominal", Set Values( {"xx", "yy", "zz"} ) ),
    New Column( "B", Character, "Nominal", Set Values( {"a", "b", "c"} ) ),
    New Column( "C_Dummy", Character, "Nominal", Set Values( {"l", "m", "n"} ) )
);

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

If( Contains( cols, "Dummy" ), 
    dt << Select Columns();
    dt << Delete Columns();
);

Re: Select and delete columns that contain specific string

brandon_mcrv — Mon, 08 Mar 2021 14:48:42 GMT

Just to add on...The reason I am asking this is because I'd like to know if there's a bunch of column names like for example 50 columns which contains "_Dummy", how can I delete it switftly without having to name the specific column names by using known delete columns command like "dt << delete column(column("A_Dummy"))". If I have to use this command, then I'd have to type 50 times all the unique column names.

Re: Select and delete columns that contain specific string

jthi — Mon, 08 Mar 2021 14:50:16 GMT

Contains will only give you first index of a match. You could either loop over the list to generate list with columns to delete or delete while you loop:

Names Default To Here( 1 );

dt = New Table( "Example",
Add Rows( 3 ),
New Column( "A_Dummy", Character, "Nominal", Set Values( {"xx", "yy", "zz"} ) ),
New Column( "B", Character, "Nominal", Set Values( {"a", "b", "c"} ) ),
New Column( "C_Dummy", Character, "Nominal", Set Values( {"l", "m", "n"} ) ));

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

for(i = 1, i <= N Items(cols), i++,
	If(Contains(cols[i],"Dummy"),
		dt << Delete Columns(cols[i])
	);	
);

Re: Select and delete columns that contain specific string

Georg — Mon, 08 Mar 2021 15:00:36 GMT

This is a modified version that may work for you and that is easy to understand.

You would need a loop over the columns to see, where the character string is in,

and your usage of select and delete was not right.

Names Default To Here( 1 );

dt = New Table( "Example",
	Add Rows( 3 ),
	New Column( "A_Dummy", Character, "Nominal", Set Values( {"xx", "yy", "zz"} ) ),
	New Column( "B", Character, "Nominal", Set Values( {"a", "b", "c"} ) ),
	New Column( "C_Dummy", Character, "Nominal", Set Values( {"l", "m", "n"} ) )
);

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

delete_lst = {};

For( i = 1, i <= N Items( cols ), i++,
	If( Contains( cols[i], "Dummy" ),
		Insert Into( delete_lst, cols[i] )
	)
);

dt << Delete Columns( delete_lst );

Re: Select and delete columns that contain specific string

pauldeen — Mon, 08 Mar 2021 15:51:04 GMT

Slight modification to eliminate the need for a extra list variable:

Names Default To Here( 1 );

dt = New Table( "Example",
	Add Rows( 3 ),
	New Column( "A_Dummy", Character, "Nominal", Set Values( {"xx", "yy", "zz"} ) ),
	New Column( "B", Character, "Nominal", Set Values( {"a", "b", "c"} ) ),
	New Column( "C_Dummy", Character, "Nominal", Set Values( {"l", "m", "n"} ) )
);

cols = dt << get column names( string );
For( i = N Items( cols ), i>=1, i--,
	If( Contains( cols[i], "Dummy" ),
		dt << Delete Columns( i );
	)
);

Re: Select and delete columns that contain specific string

brandon_mcrv — Thu, 11 Mar 2021 02:06:32 GMT

Hi jthi, thanks for trying to help. I found that the script actually deletes all the columns regardless of having the "Dummy" string or not. Anyway, Georg and pauldeen already provided the solution.

Re: Select and delete columns that contain specific string

brandon_mcrv — Thu, 11 Mar 2021 02:07:12 GMT

Excellent, it works perfectly ! Thanks !

Re: Select and delete columns that contain specific string

brandon_mcrv — Thu, 11 Mar 2021 02:08:03 GMT

It works ! Thanks for the solution, Georg !