topic Re: jsl script to recode multiple columns in Discussions

jsl script to recode multiple columns

MFVIT — Fri, 09 Jun 2023 22:02:54 GMT

Dear All,

I am trying to recode multiple columns by using the following script (also in attachment together with an example data table). I tried several changes but it does not work. The values in the columns just disappears instead of being recoded.

I appreciate any help.

Many thanks,
Marcello.

dt3 = Current Data Table();
n3 = dt3 << Get Column Names();
Show( n3 );

lstColNames_n3 = dt3 << Get Column Names();
Show( lstColNames_n3 );


Show( lstCols_n3 );
lstCols_n3 = {};
For( i = 1, i <= N Items( lstColNames_n3 ), i++,
    colName = lstColNames_n3[i];
    col = Column( dt3, i );
    Insert Into( lstCols_n3, col );
);
Show( lstCols_n3 );

For Each Row(
    For( i = 1, i <= N Items( lstColNames_n3 ), i++,
        As Column( lstColNames_n3[i] ) = Match( As Column( lstColNames_n3[i] ),
            "1", "too low",
            "2", "too low",
            "3", "right",
            "4", "too high",
            "5", "too high"
        )
    )
);

Re: jsl script to recode multiple columns

jthi — Tue, 12 Jan 2021 12:58:27 GMT

Do you have to recode values or could you possibly use column property Value Labels (this way you won't loose the original data), example:

Edit:

And if you have to recode them in place, there are multiple ways to do it (I would use Associative Array):

Names Default To Here(1);

dt3 = current data Table ();
n3 = dt3 << Get Column Names(string);
show(n3);
//create associative array to use matching keys to values
matchAa = Associative Array(
	{"1","2","3","4","5"}, 
	{"too low","too low","right","too high","too high"}
);
show(matchAa);

//loop over column names
For(i = 1, i <= N Items(n3), i++,
	//refer to column and use set each value to update values
	Column(dt3, n3[i]) << Set Each Value(
		matchAa[AsColumn(n3[i])[Row()]]
	);
);

And for your solution I think there is an issue in how you refer to values in Match function and you have to add [Row()] to get single values for Matching

Match(As Column( lstColNames_n3[i])[Row()],

Edit2:

Added full example script as attachment

Re: jsl script to recode multiple columns

MFVIT — Tue, 12 Jan 2021 09:52:25 GMT

I would need to recode values. Thanks.

Re: jsl script to recode multiple columns

jthi — Tue, 12 Jan 2021 09:55:42 GMT

I edited my previous reply, it should help with the recoding

Re: jsl script to recode multiple columns

MFVIT — Tue, 12 Jan 2021 10:18:26 GMT

Thank you very much, your code with the associative array worked perfectly.

I tried also the other option, by adding

Match(As Column( lstColNames_n3[i])[Row()],

but that did not work.

But I solved my problem.

Many thanks.

Re: jsl script to recode multiple columns

txnelson — Tue, 12 Jan 2021 10:33:43 GMT

Here is the solution using the Recode feature written in JSL It is much faster than doing row by row processing

Names Default To Here( 1 );
dmdt = current data table();

nc = dmdt << get column names( Character );

For( i = 1, i <= N Items( nc ), i++,
	col = Column( dt, nc[i] );
	colname = col << getname();

	Eval(
		Parse(
			Eval Insert(
				"\[
		dmdt << Recode Column(
			:Name("^colname^"),
			{Map Value( _rcOrig, 
			{	"1", "too low",
				"2", "too low",
				"3", "right",
				"4", "too high",
				"5", "too high"}, 
			Unmatched( _rcNow ) )},
			Update Properties( 1 ),
			Target Column( :Name("^colname^") )
		);
	]\"
			)
		)
	);
);

Re: jsl script to recode multiple columns

MFVIT — Tue, 12 Jan 2021 18:01:07 GMT

Many thanks!

Re: jsl script to recode multiple columns

ThuongLe — Fri, 15 Jan 2021 04:31:58 GMT

One work-around solution would be creating a new column with formula based on the column you want to recode.
Then delete the original column :)

Re: jsl script to recode multiple columns

MFVIT — Fri, 15 Jan 2021 09:40:54 GMT

Thanks!