topic Re: Deleting columns that are not in a list in Discussions

Deleting columns that are not in a list

Wed, 18 Aug 2010 21:12:19 GMT

Hi Guys,

I have a set of columns that I want to keep and then delete all except this set.

The columns that I want to delete are large in number.

Is there any easy way to do this using a script?

Thanks.

Re: Deleting columns that are not in a list

ms — Thu, 19 Aug 2010 12:57:00 GMT

Loop through all columns to create a list of those Columns you wish to delete, by comparing each column with your list of keepers.

Example

Re: Deleting columns that are not in a list

Thu, 19 Aug 2010 16:49:36 GMT

Thanks, that worked. I wish you could use two lists and subtract/ex or one from the other.

Re: Deleting columns that are not in a list

ms — Thu, 19 Aug 2010 19:00:52 GMT

That might work if you have only numeric items (L-values). However that's rarely the case with column names. Maybe you could switch between names and numbers by using associative arrays in a clever way.

Re: Deleting columns that are not in a list

Von_Andre — Fri, 12 May 2017 01:21:59 GMT

Hello. I was browsing this discussion. Found the subject very helpful for me but I cannot see the solution. Must have been deleted or something? Appreciate if you can post it back. Thanks!

Re: Deleting columns that are not in a list

David_Burnham — Fri, 12 May 2017 09:35:54 GMT

Slightly different bu the same idea, the following code deletes columns that are in a list. Specifically you have a list of columns (all), and items are removed if they are contained in the list excl.

all = dt << Get Column Names(string);
// assume excl is a list of column names that i want to remove
For (i=1,i<=NItems(excl),i++,
	pos = Contains(all,excl[i]);
	If (pos>0,
		RemoveFrom(all,pos)
	)
);

Re: Deleting columns that are not in a list

Von_Andre — Mon, 15 May 2017 01:41:49 GMT

Hi Dave, Appreciate your quick response on my query. I did your sample code but I seems the columns on my list still appear in my table. Is there something I missed? dt = Data Table( "temp" ); all = dt << Get Column Names( string ); vt = {"lot_id", "wf_id", "die_x"}; For( i = 1, i <= N Items( vt ), i++, pos = Contains( all, vt[i] ); If( pos > 0, Remove From( all, pos ) ); );

Re: Deleting columns that are not in a list

Kevin_Anderson — Mon, 15 May 2017 18:06:31 GMT

"There's a million ways to get things done..." Talking Heads, "What A Day That Was", 1984

If the number of columns to be deleted is truly large, it might be more efficient to Subset the table with all rows, keeping only the columns in your list. That could save lots of iterative deletion.

Your application might not be that easy, though.

Re: Deleting columns that are not in a list

David_Burnham — Mon, 15 May 2017 20:54:54 GMT

Sorry, maybe I misuunderstood. The code removes items from the list not from the table. But the updated list can be used with a Subset command to generate a subset table only with the columns you are interested in.

More here: http://www.pega-analytics.co.uk/blog/scripting-table-subsets/

Re: Deleting columns that are not in a list

Von_Andre — Tue, 16 May 2017 01:11:12 GMT

Hi Dave, Appreciate all the help. Nice blog by the way! I have a very large data set consisting >8000 columns and I have some columns to keep and some don't. I ended up by using subset on the first columns that I want to keep. After which I run a loop on the rest of the table for keeping columns that matches from my keywords like "_M". Then finally join the subset and the table I had kept with column names matching my criteria. It works fine now. However, I then again try to use list on below code but seems not running. Is using of list not allowed on this? dt = Current Data Table(); // select data table dt << Set Name( "Raw_Data" ); dt << Subset( Columns( 1 :: 30 ), Rows( all ), Output Table( "Subset" ) ); // keep main column names dt << Revert(); //Keep vt columns colList = Data Table( "Raw_Data" ) << Get Column Names( String ); For( i = N Cols( dt ), i >= 1, i--, If( !Contains( colList[i], "_M" ), //Keep only columns with "_M"" in the name dt << Delete Column( Column( colList[i] ) ) ) );

Re: Deleting columns that are not in a list

Von_Andre — Tue, 16 May 2017 01:21:12 GMT

Thanks! Is it possible to subset columns that matches within my criteria?
I'm interested to subset only columns containing keywords. And not the full column names only (since I have a lot of data).