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
OneNorthJMP
Level V

Delete columns with same value

I run into problem when i run my model with some parameters. So i want to do some data cleaning before run the model. Is there a quick way to delete columns with same values? Please advise how to do it with JSL or any other way that may fit? 

1 ACCEPTED SOLUTION

Accepted Solutions
OneNorthJMP
Level V

Re: Delete columns with same value

Georg, thanks for the columns viewer idea.

I think i will do that with columns viewer -> Summary -> sort the table by stdev = 0. Then i can remove the columns later.

View solution in original post

14 REPLIES 14
Georg
Level VII

Re: Delete columns with same value

I'm not sure if I get you Right,

for modelling you can select the columns that you think are important, so there is no Need to delete the column.

 

You want to delete some rows where a column has the same value?

Below is the Code for it:

Otherwise please describe more in Detail what you Need, an example May be very helpful.

 

Names Default To Here( 1 );
dt = Open( "$SAMPLE_DATA/Big Class.jmp" );

wait(3);

dt << Select duplicate rows( Match( :age ) );
dt << delete rows;

 

 

Georg
OneNorthJMP
Level V

Re: Delete columns with same value

I don't want to delete rows. I want to delete columns that have same value.
Georg
Level VII

Re: Delete columns with same value

This one deletes columns:

Names Default To Here( 1 );
dt = Open( "$SAMPLE_DATA/Big Class.jmp" );

// make some copies of columns
dt << New Column( "weight_copy", set each value( :weight ) );
dt << New Column( "height_copy", set each value( :height ) );


to_delete_lst = {};

For( i = 1, i < N Col( dt ), i++,
	For( j = i + 1, j <= N Col( dt ), j++,
		If( (Column( i ) << get data type) == (Column( j ) << get data type), 
		// all celles are equal ?
			If( Mean( (Column( i ) << get values) == (Column( j ) << get values) ) == 1,
				Print( (Column( i ) << get name) || " equal to " || (Column( j ) << get name) );
				insert into(to_delete_lst, j);
			);
		);
	)
);

// show list of equals
Show(to_delete_lst);
wait(3);

dt << select columns(to_delete_lst);
dt << delete columns;
Georg
OneNorthJMP
Level V

Re: Delete columns with same value

Thanks for JSL. But i am not sure it does what i want.
Georg
Level VII

Re: Delete columns with same value

I'm not sure, too :)

 

This means, you Need to be more specific on what you Need.

 

If you simply want to explore the values of a large table, you May want to use the columns viewer

Columns->Columns viewer

There you easily can compare columns of a large table.

Georg
OneNorthJMP
Level V

Re: Delete columns with same value

Georg, thanks for the columns viewer idea.

I think i will do that with columns viewer -> Summary -> sort the table by stdev = 0. Then i can remove the columns later.
txnelson
Super User

Re: Delete columns with same value

Here is a different take on how to do this.  It uses RSquare as an indicator of matching data.  

Names Default To Here( 1 );
dt = Current Data Table();

colNames = dt << get column names( continuous );

obj = dt <<
Response Screening(
	Y( Eval( colNames ) ),
	X( Eval( colNames ) )
);
dtCalc = obj << get PValues;

dtCalc << select where(Round(:RSquare,4) != 1 | :X == :Y );
try( dtCAlc << delete rows );

dtCompare = dtCalc << subset( selected rows(0), columns("X","Y"));
close(dtCalc,nosave);

for each row(
	If(:X > :Y,
		hold=:X;
		:X = :Y;
		:Y = hold;
	)
);

dtFinal = dtCompare << Summary(
	Group( :X, :Y ),
	Freq( "None" ),
	Weight( "None" ),
	Link to original data table( 0 )
);

close( dtCompare, nosave );
Jim
OneNorthJMP
Level V

Re: Delete columns with same value

Hi nelson, 

 

Thanks for advice.  But the response screening is take way too much processing time and it is not ideal. Running 45s and haven't half way finish processing. 

image.png

Thierry_S
Super User

Re: Delete columns with same value

Hi,
Based on this later post, I think we did not understand the statement of "same values"; we interpreted as column 1 = column 2 for each row but I surmise that you mean any column with identical values in each row (i.e. 0 StdDev). Is that correct?
If it is, I or somebody more experience that I am should be able to provide a relatively short script.
Best,
TS
Thierry R. Sornasse