78886_en-US

liliconcom

How to select columns with same values

Hello,

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

Thanks,

Assaf

Re: How to select columns with same values

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.

Re: How to select columns with same values

@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

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

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, 
         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

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

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

Thanks again!