topic Re: Delete columns with same value in Discussions

Delete columns with same value

OneNorthJMP — Fri, 09 Jun 2023 23:37:52 GMT

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?

Re: Delete columns with same value

Georg — Mon, 14 Sep 2020 07:39:46 GMT

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;

Re: Delete columns with same value

OneNorthJMP — Mon, 14 Sep 2020 07:59:18 GMT

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

Re: Delete columns with same value

Georg — Mon, 14 Sep 2020 09:29:44 GMT

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;

Re: Delete columns with same value

OneNorthJMP — Mon, 14 Sep 2020 09:45:07 GMT

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

Re: Delete columns with same value

Georg — Mon, 14 Sep 2020 10:25:36 GMT

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.

Re: Delete columns with same value

OneNorthJMP — Mon, 14 Sep 2020 12:57:03 GMT

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.

Re: Delete columns with same value

txnelson — Mon, 14 Sep 2020 12:58:31 GMT

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 );

Re: Delete columns with same value

OneNorthJMP — Mon, 14 Sep 2020 13:27:28 GMT

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.

Re: Delete columns with same value

Thierry_S — Mon, 14 Sep 2020 17:46:39 GMT

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

Re: Delete columns with same value

jimloughlin — Mon, 14 Sep 2020 22:55:24 GMT

When I run this code, it correctly identifies the duplicate cols but doesn't actually delete them. The log shows no errors.

"height equal to height_copy"
"weight equal to weight_copy"
to_delete_lst = {7, 6};
Scriptable[]

I am running v13.2.1

Re: Delete columns with same value

Georg — Fri, 18 Sep 2020 12:30:06 GMT

Sorry, I cannot answer,

the Code works fine on JMP 15 with Win10 on my Computer,

and the last two steps are quite Basic commands that should have been implemented in JMP 13 already (but I cannot check).

Can you perform that both steps of

select columns 6+7
and delete selected

separately one after the other? e.g. by marking script line in Editor and Pressing the Play button.

Where exactly is the Problem?

Re: Delete columns with same value

txnelson — Fri, 18 Sep 2020 15:41:59 GMT

I ran this in JMP 13.2.1 and it works.....all you need to do, is to delete the lines in the code

dt << select columns(to_delete_lst);
dt << delete columns;

and replace them with

dt << delete columns( to_delete_lst);

Re: Delete columns with same value

jimloughlin — Fri, 18 Sep 2020 20:07:35 GMT

That works !!! Thanx.

Re: Delete columns with same value

jimloughlin — Fri, 18 Sep 2020 20:08:21 GMT

TXNELSON's post below worked with JMPv13.2.1 .