The Origin Forum
File Exchange
Try Origin for Free
The Origin Forum
Home | Profile | Register | Active Topics | Members | Search | FAQ | Send File to Tech support
 All Forums
 Origin Forum
 Origin Forum
 Mask entire column with intercolumn condition

Note: You must be registered in order to post a reply.
To register, click here. Registration is FREE!

Screensize:
UserName:
Password:
Anti-Spam Code:
Format Mode:
Format: BoldItalicizedUnderlineStrikethrough Align LeftCenteredAlign Right Horizontal Rule Insert HyperlinkUpload FileInsert Image Insert CodeInsert QuoteInsert List
   
Message:

* HTML is OFF
* Forum Code is ON
Smilies
Smile [:)] Big Smile [:D] Cool [8D] Blush [:I]
Tongue [:P] Evil [):] Wink [;)] Clown [:o)]
Black Eye [B)] Eight Ball [8] Frown [:(] Shy [8)]
Shocked [:0] Angry [:(!] Dead [xx(] Sleepy [|)]
Kisses [:X] Approve [^] Disapprove [V] Question [?]

 
Check here to subscribe to this topic.
   

T O P I C    R E V I E W
belebele Posted - 10/28/2021 : 09:01:18 AM
Hello,

I have five consecutive columns filled with values (columns A to E). From this, I extract the mean values and the standard deviations for each row using the X-function sum(A:E)_mean in col(F) and sum(A:E)_sd in col(G) (see below).



My problem is the following:

I would like to mask entirely one (or more) of the initial columns (A to E) if it contains, between the rows 5 and 15 only, at least one value which is too far from the calculated mean (for example the outlier can be ">(col(F)+col(G))" (mean+sd) and "<(col(F)-col(G))" (mean-sd).

With the command window, this works with the following code:

for(int ii=5; ii<=15; ii++)
{
	double dLower = col(F)[ii]-col(G)[ii];  // mean-sd
	double dUpper = col(F)[ii]+col(G)[ii];  // mean+sd
	for(int jj=1; jj<=5; jj++)
	{
		double dOutlier = col($(jj))[ii];  // get cell value
		if(dOutlier<dLower || dOutlier>dUpper)
		{
			wcellmask irng:=col($(jj));  // mask the cell
		}
	}
};




However, I don't want to use the command window but I want to implement this code as a X-function in all 5 columns. The goal behind this is to run automatically the code every time I import new data (I use a data connector) without any human intervention. Is there a way to do such a thing?

It looks like it is possible to mask cells with a condition intracolumn but not intercolumn.

Thanks for your help.

Best regards,
Greg
3   L A T E S T    R E P L I E S    (Newest First)
YimingChen Posted - 10/28/2021 : 12:12:22 PM
OK. Assume you already have 5 workbooks with calculated row mean/std in the project. You can run the script below to extract the outlier columns. So in the first loop, the script find the outlier columns in each sheet and label the longname as "outlier". Then in the second loop, the columns with longname "outlier" is then copied to the summary workbook. 


doc -e LB {
	for(int ii=5; ii<=15; ii++)
	{
		double dLower = col(F)[ii]-col(G)[ii];  // mean-sd
		double dUpper = col(F)[ii]+col(G)[ii];  // mean+sd
		for(int jj=1; jj<=5; jj++)
		{
			double dOutlier = col($(jj))[ii];  // get cell value
			if(dOutlier<dLower || dOutlier>dUpper)
			{
				col($(jj))[L]$ = "outlier";
			}
		}
	}	
}

newbook name:="Summary";
int colindex = 1;
doc -e LB {
	for (int j = 1; j <= wks.ncols; j++) {
		if (wcol(j)[L]$ == "outlier") {
			range r1 = wcol(j);
			range r2 = ["Summary"]1!wcol(colindex);
			r2 = r1;
			r2[L]$ = %H;
			colindex++;
		}
	}
}



James
belebele Posted - 10/28/2021 : 11:15:52 AM
Thanks James for the help, it works when I click on "Run" (in the "script" tab of the "worksheet properties" window) but not when I import data, even though I've checked "Run After import".

Actually, the problem is that I import my data in 5 different workbooks corresponding to one sample each (named "sample#" with # the number of the sample). Then I gather some of the results in another "summary" workbook.

The columns that I want to mask as described in my post are in the workbook "summary", such that I never import data directly into the worksheet where I have the script.

Greg
YimingChen Posted - 10/28/2021 : 10:14:27 AM
Can you select from menu Worksheet->Worksheet Script.. and put your LabTalk script in the panel. Check the box After Import. Click OK to save. Then each time you connect to a new data file, the script gets executed and the columns should masked according to your condition. See attached workbook as an example. You can click on the Data Connector icon and set the Data Source to check the result.
https://my.originlab.com/ftp/forum_and_kbase/Images/TemplateMask.zip

James

The Origin Forum © 2020 Originlab Corporation Go To Top Of Page
Snitz Forums 2000