topic Re: how to find and replace all instances of specified characters within a given column in Discussions

how to find and replace all instances of specified characters within a given column

fbm73 — Fri, 13 Mar 2020 18:10:29 GMT

This is a very simple question. I have a given column in a JMP table, and I want a JMP scripting command that will find and replace all instances of specified characters within that column. For example, I have a column called GROUP, and certain rows in GROUP contain values like "A05 or "B17 and I would like to replace all " characters in GROUP so that the entries become A05 and B17 instead of "A05 and "B17. This can easily be done manually by going to Table - Search - Find and then doing a find and replace, but is there a simple way to script this?

Re: how to find and replace all instances of specified characters within a given column

alecpena — Fri, 13 Mar 2020 18:22:18 GMT

You can use Recode to do replace in your jmp table. Before you submit it you can then grab the script to use in the future.

dt = Current Data Table();
dt << Begin Data Update;
dt << Recode Column(
	dt:GROUP,
	{Map Value( _rcOrig, {"\!"A05", "A05", "\!"B17", "B17"}, Unmatched( _rcNow ) )},
	Target Column( :GROUP )
);
dt << End Data Update;

Re: how to find and replace all instances of specified characters within a given column

fbm73 — Fri, 13 Mar 2020 20:25:04 GMT

Thanks for that reply. Maybe I am mistaken, but I thought Recode can be used to replace entire, known values with different values. But in my case, to be more specific, the values of GROUP can be like "A05, or "B17, or anything of the form "***, where all I know is that the value starts with a " character and then the * characters are wildcards which could be anything. Really all I want to do is just strip the " out of the values.

Re: how to find and replace all instances of specified characters within a given column

txnelson — Sat, 14 Mar 2020 08:12:46 GMT

Here is a simple piece of JSL that will handle your A05 issue

Names Default To Here( 1 );

dt = Current Data Table();
foundRows = dt:yourColumn << get rows where( Contains( :yourColumn, "A05" ) );
If( N Rows( foundRows ) > 0,
	:yourColumn[foundRows] = "A05"
);

Re: how to find and replace all instances of specified characters within a given column

pmroz — Sun, 15 Mar 2020 12:37:17 GMT

To remove quotes loop over the rows and use the substitute function.

dt = New Table( "Test Quote Removal", Add Rows( 4 ),
	New Column( "Group", Character, "Nominal",
		Set Values( {"A05", "\!"A06", "\!"A07", "A08"} )
	)
);

for (i = 1, i <= nrows(dt), i++,
	dt:group[i] = substitute(dt:group[i], "\!"", "");
);