topic Re: How to perform "find & replace" in specific a column using JSL script? in Discussions

How to perform "find & replace" in specific a column using JSL script?

Adi-B — Fri, 09 Jun 2023 19:53:43 GMT

Hi,

How can I re-create the "Find & replace" operation on a specific column by JSL script?

For example - I want to replace "K" with "1e3" & "M" with "1e6" in "Count" column

(to convert it to numeric type later).

I didn't find a simple way to do it without using a loop (there so it would be fast when many rows needs to be checked)

Thanks :)

Re: How to perform "find & replace" in specific a column using JSL script?

jthi — Thu, 29 Jul 2021 19:15:37 GMT

I think Recode can do this:

Other way would be creating new column with some sort of column:

conversionAa = ["K" => "1e3", "M" => "1e6"];
If(Contains(conversionAa, Right(:Count, 1)),
	Substitute(:Count, Right(:Count,1), conversionAa[Right(:Count, 1)])
);

My suggestion would be to use Recode and if you need the script you can get it from the Recode platform:

Names Default To Here(1);
dt = Data Table("Untitled");
dt << Begin Data Update;
col1 = dt << New Column(dt:Count);
col1 << Set Name("Count 2");
dt << Move Selected Columns({col1}, after(dt:Count));
dt << Recode Column(
	dt:Count,
	{Substitute(_rcNow, "K", "1e3"), Substitute(_rcNow, "M", "1e6")},
	Target Column(col1)
);
dt << End Data Update;

Re: How to perform "find & replace" in specific a column using JSL script?

Craige_Hales — Fri, 30 Jul 2021 15:02:55 GMT

@DonMcCormack

The big win on a lot of the ways you might do this will be to use the Begin/End Data Update as @jthi showed. Here's another way to do it:

dt = Open( "$sample_data/big class.jmp" );
dt << Begin Data Update;
For Each Row( dt,
	dt:name = Substitute( dt:name, 
		"A", "a",
		"E", "e",
		"I", "i",
		"O", "o",
		"U", "u"
	)
);
dt << End Data Update;

Lower case vowels.

Re: How to perform "find & replace" in specific a column using JSL script?

Adi-B — Mon, 02 Aug 2021 05:54:05 GMT

Thanks a lot !!

Re: How to perform "find & replace" in specific a column using JSL script?

DonMcCormack — Tue, 03 Aug 2021 19:08:56 GMT

Thanks @Craige_Hales. Looks like the makings for a good Challenge question.