BookmarkSubscribeRSS Feed
Choose Language Hide Translation Bar
Lharbicht

Community Trekker

Joined:

Nov 15, 2016

Simple Find and Replace

I'm looking to complete a simple find and replace. I'm building a larger script and would like to replace some text as a blank. I've tried looping and the munger function, but it seems all the examples I've found online are more complicated than I need. I've attached my dataset. I basically want to search the whole table for "< LOD" and replace it with a blank. Additionally I'd like to do the same for "---". Finally I'd like to use script to change the columns to numeric/continuous after the text has been removed.

 

Thanks for the help!

 

2 ACCEPTED SOLUTIONS

Accepted Solutions
markbailey

Staff

Joined:

Jun 23, 2011

Solution

Re: Simple Find and Replace

If you chabge the data type from character to numeric, values like "<LOD" will be left as blanks.

Names Default to Here( 1 );

dt = New Table( "test",
	New Column( "data",
		Character, Nominal,
		Values( { "3.1", "4.5", "<LOD", "6.7" } )
	)
);

Wait( 2 );

Column( 1 ) << Set Data Type( Numeric ) << Set Modeling Type( Continuous );
Learn it once, use it forever!
txnelson

Super User

Joined:

Jun 22, 2012

Solution

Re: Simple Find and Replace

Here is a simple piece of code that finds the values you want to change, changes them, and then sets the columns to numeric and continuous

Names Default To Here( 1 );
dt = Current Data Table();

colList = dt << get column names( character, string );

For( i = 1, i <= N Items( colList ), i++,
	selRows = dt << get rows where(
		Column( dt, colList[i] )[Row()] == "< LOD" | Column( dt, colList[i] )[Row()] == "---"
	);
	If( N Rows( selRows ) > 0,
		Column( dt, colList[i] )[selRows] = "";
		Column( dt, colList[i] ) << data type( "numeric" ) << modeling type( "continuous" );
	);
);
Jim
8 REPLIES 8
Highlighted
markbailey

Staff

Joined:

Jun 23, 2011

Re: Simple Find and Replace

Separate from your scripting question, replacing the <LOD values with a blank will bias any estimates using this data. This case is an example of censoring. You should use estimators that handle censored data as well as exact data.

Learn it once, use it forever!
Lharbicht

Community Trekker

Joined:

Nov 15, 2016

Re: Simple Find and Replace

I appreciate the word of caution, however, I'm just using the data for graphing fingerprints, and the simple fact that the compound wasn't detected is of interest to us. For any statistical analysis we would impute the ND values based on supplied detection limits.

markbailey

Staff

Joined:

Jun 23, 2011

Solution

Re: Simple Find and Replace

If you chabge the data type from character to numeric, values like "<LOD" will be left as blanks.

Names Default to Here( 1 );

dt = New Table( "test",
	New Column( "data",
		Character, Nominal,
		Values( { "3.1", "4.5", "<LOD", "6.7" } )
	)
);

Wait( 2 );

Column( 1 ) << Set Data Type( Numeric ) << Set Modeling Type( Continuous );
Learn it once, use it forever!
Lharbicht

Community Trekker

Joined:

Nov 15, 2016

Re: Simple Find and Replace

Well that cuts out a step then! Thanks for the help. Much easier.

txnelson

Super User

Joined:

Jun 22, 2012

Solution

Re: Simple Find and Replace

Here is a simple piece of code that finds the values you want to change, changes them, and then sets the columns to numeric and continuous

Names Default To Here( 1 );
dt = Current Data Table();

colList = dt << get column names( character, string );

For( i = 1, i <= N Items( colList ), i++,
	selRows = dt << get rows where(
		Column( dt, colList[i] )[Row()] == "< LOD" | Column( dt, colList[i] )[Row()] == "---"
	);
	If( N Rows( selRows ) > 0,
		Column( dt, colList[i] )[selRows] = "";
		Column( dt, colList[i] ) << data type( "numeric" ) << modeling type( "continuous" );
	);
);
Jim
Lharbicht

Community Trekker

Joined:

Nov 15, 2016

Re: Simple Find and Replace

Thank you, that script worked perfectly.

 

On a side note, (and please let me know if I have to start a new discussion thread for this) but with my data that I attached previously, I'm trying to make bubble plots using script. I can do everything interactively, and then copy the script however, it's specific to the Sample ID names in the first column. I will be using this script with many different data tables and the layout of the data will always be the same, however, the number of samples inlcuded in the file, and the sample IDs will change. Is there an easy way to adapt the script below to be used for any Sample ID, rather than say "CM172-001".

 

Bubble Plot(
	SendToByGroup( {:Sample ID == "CM172-001"} ),
	X( :RT1 ),
	Y( :RT2 ),
	Sizes( :Area ),
	Coloring( :Group ),
	Title Position( 0, 0 ),
	By( :Sample ID ),
	SendToByGroup(
		{:Sample ID == "CM172-001"},
		SendToReport(
			Dispatch(
				{"Bubble Plot of RT2 by RT1 Sized by Area Sample ID=CM172-001"},
				"1",
				ScaleBox,
				{Min( 11 ), Max( 22 ), Inc( 2 ), Minor Ticks( 1 )}
			),
			Dispatch(
				{"Bubble Plot of RT2 by RT1 Sized by Area Sample ID=CM172-001"},
				"2",
				ScaleBox,
				{Min( 2.84 ), Max( 2.97 ), Inc( 0.02 ), Minor Ticks( 1 )}
			)
		)
	),
	SendToByGroup(
		{:Sample ID == "CM172-002"},
		SendToReport(
			Dispatch(
				{"Bubble Plot of RT2 by RT1 Sized by Area Sample ID=CM172-002"},
				"1",
				ScaleBox,
				{Min( 10 ), Max( 35 ), Inc( 5 ), Minor Ticks( 1 )}
			),
			Dispatch(
				{"Bubble Plot of RT2 by RT1 Sized by Area Sample ID=CM172-002"},
				"2",
				ScaleBox,
				{Min( 2.8 ), Max( 3.7 ), Inc( 0.1 ), Minor Ticks( 0 )}
			)
		)
	),
	SendToByGroup(
		{:Sample ID == "CM172-003"},
		SendToReport(
			Dispatch(
				{"Bubble Plot of RT2 by RT1 Sized by Area Sample ID=CM172-003"},
				"1",
				ScaleBox,
				{Min( 11 ), Max( 22 ), Inc( 2 ), Minor Ticks( 1 )}
			),
			Dispatch(
				{"Bubble Plot of RT2 by RT1 Sized by Area Sample ID=CM172-003"},
				"2",
				ScaleBox,
				{Min( 2.83 ), Max( 2.96 ), Inc( 0.02 ), Minor Ticks( 1 )}
			)
		)
	),
	SendToByGroup(
		{:Sample ID == "CM172-004"},
		SendToReport(
			Dispatch(
				{"Bubble Plot of RT2 by RT1 Sized by Area Sample ID=CM172-004"},
				"1",
				ScaleBox,
				{Min( 10 ), Max( 30 ), Inc( 5 ), Minor Ticks( 1 )}
			),
			Dispatch(
				{"Bubble Plot of RT2 by RT1 Sized by Area Sample ID=CM172-004"},
				"2",
				ScaleBox,
				{Min( 2.6 ), Max( 3.6 ), Inc( 0.2 ), Minor Ticks( 1 )}
			)
		)
	),
	SendToByGroup(
		{:Sample ID == "CM172-005"},
		SendToReport(
			Dispatch(
				{"Bubble Plot of RT2 by RT1 Sized by Area Sample ID=CM172-005"},
				"1",
				ScaleBox,
				{Min( 10 ), Max( 30 ), Inc( 5 ), Minor Ticks( 1 )}
			),
			Dispatch(
				{"Bubble Plot of RT2 by RT1 Sized by Area Sample ID=CM172-005"},
				"2",
				ScaleBox,
				{Min( 2.7 ), Max( 3.7 ), Inc( 0.2 ), Minor Ticks( 1 )}
			)
		)
	)
);
txnelson

Super User

Joined:

Jun 22, 2012

Re: Simple Find and Replace

Many times, you can eliminate the by group specifics and let JMP figure out what to use, if you just specify to use a By Group.  The script below works just great without all of the clutter

Bubble Plot(
	X( :RT1 ),
	Y( :RT2 ),
	Sizes( :Area ),
	Coloring( :Group ),
	Title Position( 0, 0 ),
	By( :Sample ID )
);
Jim
Lharbicht

Community Trekker

Joined:

Nov 15, 2016

Re: Simple Find and Replace

I think the theme of my lessons today is "Less is More". Thank you. That (again) works perfectly.