topic Re: How to select columns with same values in Discussions

How to select columns with same values

Assaf1 — Sun, 14 Oct 2018 11:33:27 GMT

Hello,

How to select columns with same values (continues and nominal)?

Thanks,

Assaf

Re: How to select columns with same values

Assaf1 — Sun, 14 Oct 2018 11:35:17 GMT

Just to clarify - not doplicated columns but column that all the rows contain the same value.

Thanks again!

Re: How to select columns with same values

dale_lehman — Sun, 14 Oct 2018 15:05:03 GMT

Use Rows - Row Selection - Select Where - and check the box that says compare columns. You can select 2 columns and select all rows that match in those two columns. If you want to extend it to more columns, just do it again, but make sure to change the drop down menu to say "restrict" to current selection.

Re: How to select columns with same values

uday_guntupalli — Sun, 14 Oct 2018 15:11:12 GMT

@Assaf1,
You could try something like this, I am pretty sure there are always other ways to achieve the same

dt = New Table("Test"); 
Vals = Index(1,4,1);

dt << New Column("A",Numeric,Continuous,<< Set Values(Vals))
   << New Column("B",Numeric,Continuous,<< Set Values(Vals))
   << New Column("C",Character,Continuous,<< Set Values({"a","b","c","d"}))
   << New Column("D",Character,Continuous,<< Set Values({"a","b","c","d"}));
   
AVals = dt:A << Get Values; 
BVals = dt:B << Get Values; 
CVals = dt:C << Get Values; 
DVals = dt:D << Get Values;

If(All(AVals == BVals),Print("It's a match"), Print("They aren't same")); 
If(All(CVals == DVals),Print("It's a match"), Print("They aren't same"));

Re: How to select columns with same values

gzmorgan0 — Sun, 14 Oct 2018 23:47:46 GMT

You did not specify which version of JMP that you are using. Table Summary > N Categories is available in JMP 12 and later. Script is also attached as a file.

Names Default to Here(1);

dt = Open("$sample_data/semiconductor capability.jmp");

cnames = dt << get column names(Continuous|Ordinal|Nominal, "String");

uni = dt  << Summary( Invisible,
	N Categories( evalList(cnames) ),
	Freq( "None" ),
	Weight( "None" ),
	statistics column name format( "column" ),
	Link to Original Data Table(0)
);

nmiss =  dt  << Summary( Invisible,
	N Missing( evalList(cnames) ),
	Freq( "None" ),
	Weight( "None" ),
	statistics column name format( "column" ),
	Link to Original Data Table(0)
);

ftbl = uni << Stack( Columns(EvalList(cnames) ), 
	Source Label Column( "Name" ),
	Stacked Data Column( "N Categories" )
);
Close(uni,NoSave);

tmptbl = nmiss << Stack(Invisible, Columns(EvalList(cnames) ), 
	Source Label Column( "Name" ),
	Stacked Data Column( "N Missing" )
);
Close(nmiss,NoSave);

ftbl << Update( With( tmptbl ) );
Close(tmptbl, NoSave);
(ftbl << select where ( :N Categories > 1)) << delete rows;

//Note you can select columns where they have at least k unique values.

Re: How to select columns with same values

Assaf1 — Mon, 15 Oct 2018 14:26:32 GMT

Thanks for your support.
My question is to select all the column with the same value (and not opening new table with list of such column).
The motivation is to identify these columns and let me decide if I want to delete these columns or hide...

I found satisfied solution:

dt = Current Data Table();
dt << clear column selection ();
m = dt << getAllColumnsAsMatrix;
wait (0);
for(c=1, c<=NCol(m), c++,
if( (max(m[0,c]) - min(m[0,c]) == 0),
column (dt, c) << set selected ();
);// Loop over the columns and record which are contains only single value

);

Thanks a lot

Re: How to select columns with same values

uday_guntupalli — Mon, 15 Oct 2018 13:33:12 GMT

@Assaf1,
What is it that you really want ? Are you trying to find all columns where all the values in column 1 are the same as all the values in column 2 ? If yes, I don't see how the logic you have will work. The logic that you are showing is only comparing the delta between the maximum and minimum value, so if you had a column where maximum is 6, minimum is -6 and another column where maximum is 7, minimum is -7 , they will both satisfy your condition even though the values in the columns are not necessarily the same

Re: How to select columns with same values

Jeff_Perkinson — Mon, 15 Oct 2018 17:27:07 GMT

I think what you're asking for is a way to find all columns that have only one value in the whole column, for example a column with only of 1 or a column full of "X"s.

If that's the case, I find that the Columns Viewer (Cols->Columns Viewer) is a good tool for doing this.

You can sort the Summary Statistics table by the N Categories or the Std Dev column to get all the columns with only 1 category or a Std Dev of 0 together.