topic Re: How to select and exclude rows from dt1 based on unique list of values from dt2? in Discussions

How to select and exclude rows from dt1 based on unique list of values from dt2?

aliegner1 — Fri, 09 Jun 2023 19:48:38 GMT

so I'm using dt1 to run t-test analysis (Mean by Chamber by ChartID) then creating a combined data table, dt2, of all the pvalues. I want to filter this down to rows where p<=0.05, then create a distinct, unique list of these low pValues and use that to replot my chart with just the low PValues.

I'm a bit stuck now trying to link back to the original dt1. The final select where doesn't seem to be working.

also to note, if there's a better way to select, exclude, join back to source dt, please let me know. I tried summary, associative array.

//creating the table of pValues
dt2 = Report( ow[1] )["Means Comparisons", "Comparisons for all pairs using Tukey-Kramer HSD","Ordered Differences Report"][Table Box( 1 )] << Make Combined Data Table;
dt2 << set name( "pValue PairWise by Channel Tukey")

//select and exclude high pVals
dt2 << select where("p-Value"n > 0.05);
dt2 << exclude;

//get list of remaining Unique Chart_ID's
summarize(uniqueVals=by(CHART_ID));

//trying to select on the original source data
dt1 << select where( contains( uniqueVals, :CHART_ID));
//then need to invert and exclude

Re: How to select and exclude rows from dt1 based on unique list of values from dt2?

brady_brady — Fri, 04 Jun 2021 19:45:35 GMT

Are your chart IDs, by chance, numeric? The by() result in Summarize is a list of character strings--even if the original group identifiers were numbers. This will prevent those list elements (which, again, are strings) from matching any values in the original table (which are numbers).

Cheers,

Brady

Re: How to select and exclude rows from dt1 based on unique list of values from dt2?

jthi — Fri, 04 Jun 2021 19:57:23 GMT

My first guess would be mismatch between character and numerical values.

Below is an example with Big Class:

Names Default To Here(1);
dt1 = Open("$SAMPLE_DATA/Big Class.jmp");
ow = Oneway(Y(:height), X(:age), All Pairs(1));
dt2 = Report(ow)["Means Comparisons", "Comparisons for all pairs using Tukey-Kramer HSD","Ordered Differences Report"][Table Box( 1 )] << Make Combined Data Table;
dt2 << set name( "pValue PairWise by Channel Tukey");
dt2 << select where(:"p-Value"n > 0.05);
dt2 << exclude;
summarize(dt2, uniqueVals=by(:level)); //check out the type of :level column, could be character
//convert to numbers OR you could convert values in dt1 to characters
For( i = 1, i <= N Items(uniqueVals ), i++,
	uniqueVals[i] = Num(uniqueVals[i] )
);

dt1 << select where(Contains(uniqueVals, :age));

Re: How to select and exclude rows from dt1 based on unique list of values from dt2?

aliegner1 — Fri, 04 Jun 2021 20:31:57 GMT

@brady_brady

good questions. So my source data (dt1) has Chart_ID as Numeric Nominal. After creating charts and running t-Test then outputting a Combined Data Table (dt2) the Chart_ID column is Character Nominal.

Re: How to select and exclude rows from dt1 based on unique list of values from dt2?

aliegner1 — Fri, 04 Jun 2021 20:55:08 GMT

@jthi

so after doing the summarize, it looks like the Chart_ID is Numeric Continuous, while the original source data dt1 Chart_ID is numeric Nominal, and the pvalue table dt2 is Character Nominal.

//extra, just to look at the data
dtUnique = New Table( "uniqueVals less 0.05");
dtUnique << New Column( "ChannelID", Values(uniqueVals));

Re: How to select and exclude rows from dt1 based on unique list of values from dt2?

jthi — Sat, 05 Jun 2021 07:46:36 GMT

uniqueVals varible has to have same Data Type as dt1, so you can choose which one you would change. If you don't need to have Chart_ID as numeric data type, I would most likely change that to Character in dt1 in this case.