nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

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

Adi-B

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

Accepted answers

Craige_Hales

@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.

All comments

jthi

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;

Craige_Hales

@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.

Adi-B

Thanks a lot !!

DonMcCormack

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