The Origin Forum

File Exchange

Try Origin for Free

The Origin Forum

All Forums

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:

Message:

* HTML is OFF
* Forum Code is ON

Smilies

[quote][i]Originally posted by belebele[/i]
[br]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).

[img]https://my.originlab.com/ftp/forum_and_kbase/Images/Screenshot%202021-10-28%20141635.png[/img]

My problem is the following:

I would like to [b]mask entirely one (or more) of the initial columns[/b] (A to E) if it contains, [b]between the rows 5 and 15 only[/b], [b]at least one value which is too far from the calculated mean[/b] (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:

[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
		}
	}
};[/code]

[img]https://my.originlab.com/ftp/forum_and_kbase/Images/Screenshot%202021-10-28%20142601.png[/img]

However, I don't want to use the command window but I want to [b]implement this code as a X-function in all 5 columns[/b]. The goal behind this is [b]to run automatically the code every time I import new data[/b] (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
[/quote]

Check here to include your profile signature.
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

Snitz Forums 2000