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

Deleting columns that are not in a list

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.
1 ACCEPTED SOLUTION

Accepted Solutions
ms
Super User (Alumni) ms
Super User (Alumni)

Re: Deleting columns that are not in a list

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

View solution in original post

10 REPLIES 10
ms
Super User (Alumni) ms
Super User (Alumni)

Re: Deleting columns that are not in a list

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

Thanks, that worked. I wish you could use two lists and subtract/ex or one from the other.
ms
Super User (Alumni) ms
Super User (Alumni)

Re: Deleting columns that are not in a list

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.
Von_Andre
Level II

Re: Deleting columns that are not in a list

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!
David_Burnham
Super User (Alumni)

Re: Deleting columns that are not in a list

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)
	)
);
-Dave
Von_Andre
Level II

Re: Deleting columns that are not in a list

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 ) ); );
Kevin_Anderson
Level VI

Re: Deleting columns that are not in a list

"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.

Von_Andre
Level II

Re: Deleting columns that are not in a list

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).
David_Burnham
Super User (Alumni)

Re: Deleting columns that are not in a list

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/

 

-Dave