Discussions
Solve problems, and share tips and tricks with other JMP users.
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 Solutions
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;
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;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;
