cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
JMP is taking Discovery online, April 16 and 18. Register today and join us for interactive sessions featuring popular presentation topics, networking, and discussions with the experts.
Choose Language Hide Translation Bar
Lharbicht
Level III

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

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

View solution in original post

txnelson
Super User

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

View solution in original post

8 REPLIES 8

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.

Lharbicht
Level III

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.

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 );
Lharbicht
Level III

Re: Simple Find and Replace

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

txnelson
Super User

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
Level III

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

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
Level III

Re: Simple Find and Replace

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