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
Username:
 Password:
Save Password
Forgot your Password? | Admin Options

 All Forums
 Origin Forum for Programming
 LabTalk Forum
 Replacing data errors in worksheet		  New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic Lock Topic Edit Topic Delete Topic New Topic Reply to Topic

Nainiv

Russia
Posts
Posted - 11/20/2005 :  09:49:53 AM  Show Profile  Edit Topic  Reply with Quote  View user's IP address  Delete Topic
Hello everyone.
Can you help me with one problem, please.
I have a worksheet with many rows of data.
In some rows there are data errors ( for example in row '1' '1' '1' '56' '1' ; '56'- data error). There is a 3s (s - standart deviation) criterion to find data errors in row. How can i find and replace data errors with average value on row? Actually there are too many rows and i cant do it manually.

Mike Buess

USA
3037 Posts
Posted - 11/20/2005 :  11:49:54 AM  Show Profile  Edit Reply  Reply with Quote  View user's IP address  Delete Reply
First you need to clarify two points.

1. 'Errors' are found by comparing each value to mean +/- 3*SD, where mean and SD are determined from all values in the same row? I ask that because mean=10.17 and SD=22.45 for your example so 56 does not qualify as an error.

2. What version of Origin? If previous point is correct this is easier to do in Origin C.

Mike Buess
Origin WebRing Member
Go to Top of Page

Nainiv

Russia
Posts
Posted - 11/20/2005 :  12:10:08 PM  Show Profile  Edit Reply  Reply with Quote  View user's IP address  Delete Reply
1. Yes wrong data found by comparing each value to mean. In my example it doesn't work becouse 3s criterion only useable when there are more then 10 data values in sample. I apologise, i've posted wrong example.

2.Version of Origin - 7.0
Go to Top of Page

Mike Buess

USA
3037 Posts
Posted - 11/20/2005 :  12:40:16 PM  Show Profile  Edit Reply  Reply with Quote  View user's IP address  Delete Reply
This should work in 7.0 SR4. If you have an X column that shouldn't be included in the row analysis then duplicate the wks and remove the X column first.
void smooth_outliers()
{
	Worksheet wks = Project.ActiveLayer();
	matrix mm;
	mm.CopyFromWks(wks);
	Dataset dd;
	BasicStats bsStat;
	double dMin,dMax;
	for(int i=0; i<mm.GetNumRows(); i++)
	{
		mm.GetRow(dd,i);
		Data_sum(&dd, &bsStat);
		dMin = bsStat.mean - 3*bsStat.sd;
		dMax = bsStat.mean + 3*bsStat.sd;
		for(int j=0; j<dd.GetSize(); j++)
		{
			if(dd[j]>dMax || dd[j]<dMin)
				dd[j] = bsStat.mean;
		}
		mm.SetRow(dd,i);
	}
	for(i=0; i<mm.GetNumCols(); i++);
	{
		dd.Attach(wks,i);
		mm.GetColumn(dd,i);
	}
}
...Sorry, since this is the LabTalk forum I should explain how to use that Origin C function.

1. Open CodeBuilder and select File > New.
2. Select C File on the list at left.
3. Check the Add to Workspace and Fill with Default Contents options.
4. Enter a filename and path, then click OK.
5. Scroll to the bottom of your new file, enter a new line and paste the function to the file.
6. Select Tools > Rebuild All to compile.
7. Close CodeBuilder and execute the function by typing smooth_outliers in the script window and pressing Enter.

Mike Buess
Origin WebRing Member

Edited by - Mike Buess on 11/20/2005 7:34:14 PM

Edited by - Mike Buess on 11/21/2005 09:15:14 AM
Go to Top of Page

Nainiv

Russia
Posts
Posted - 11/21/2005 :  1:38:32 PM  Show Profile  Edit Reply  Reply with Quote  View user's IP address  Delete Reply
First of all - thank you, Mike, for your help.
Actually i had version of origin 70SR0 so i've upgraded my origin up to version 7.5 to use your script. There were no errors on steps 1-6 but when i tried to execute function in script window following error message appeared:

smooth_outliers;

general operation failure
general operation failure
general operation failure
#Command Error!

I did something wrong? wks was selected before running the script.

Go to Top of Page

Mike Buess

USA
3037 Posts
Posted - 11/21/2005 :  4:36:31 PM  Show Profile  Edit Reply  Reply with Quote  View user's IP address  Delete Reply
Did you notice my correction (09:15 AM)...?

Inner for loop should be for(int j=0; j<dd.GetSize(); j++)

rather than for(int j=0; j<=dd.GetSize(); j++) as originally written.

...Found two more errors: (1) Dataset dd must be sized when it is declared and (2) an errant semicolon after the final for statement. Try the following...
void smooth_outliers()
{
	Worksheet wks = Project.ActiveLayer();
	matrix mm;
	mm.CopyFromWks(wks);
	int nCols = mm.GetNumCols();
	int nRows = mm.GetNumRows();
	Dataset dd(nCols);
	BasicStats bsStat;
	double dMin,dMax;
	for(int i=0; i<nRows; i++)
	{
		mm.GetRow(dd,i);
		Data_sum(&dd, &bsStat);
		dMin = bsStat.mean - 3*bsStat.sd;
		dMax = bsStat.mean + 3*bsStat.sd;
		for(int j=0; j<nCols; j++)
		{
			if( dd[j]>dMax || dd[j]<dMin )
				dd[j] = bsStat.mean;
		}
		mm.SetRow(dd,i);
	}
	for(i=0; i<nCols; i++)
	{
		dd.Attach(wks,i);
		mm.GetColumn(dd,i);
	}
}

Mike Buess
Origin WebRing Member

Edited by - Mike Buess on 11/21/2005 8:17:39 PM
Go to Top of Page

Nainiv

Russia
Posts
Posted - 11/21/2005 :  10:27:48 PM  Show Profile  Edit Reply  Reply with Quote  View user's IP address  Delete Reply
New script works fine!!
Thank you so much, Mike!!
Go to Top of Page
  Previous Topic Topic Next Topic Lock Topic Edit Topic Delete Topic New Topic Reply to Topic
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
The Origin Forum © 2020 Originlab Corporation Go To Top Of Page
Snitz Forums 2000