Simple Find and Replace

[#1]

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]

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.

[#3]

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.

[#4 · Accepted solution]

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 );

[#5]

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

[#6 · Accepted solution]

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" );
	);
);

[#7]

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 )}
			)
		)
	)
);

[#8]

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 )
);

[#9]

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