topic Re: Script recode values for multiple columns using Associative Array and loop in Discussions

Script recode values for multiple columns using Associative Array and loop

Valentina — Wed, 21 Jun 2023 21:03:12 GMT

Hello, JMP user community,

Very often I need to re-code values and I like to use a file with mapping keys (attached, help_med_code.jmp).

From this mapping data I made the associative array and to re-code values in my dataset (help_data.jmp) I re-code values for each column.

Sometimes I have a lot of columns and using the loop would be preferable.

However, my script for loop doesn’t work (attached, help2.jsl).

Please, help to use the associative array in the loop. Other suggestions for re-coding are also will be greatly appreciated.

I hope I didn’t make any silly typo(s) and didn’t repeat the question if it had been already answered here.

Best to you all,

Valentina

JMP® 16.2.0 (570548)
Microsoft Windows 10 Enterprise 64-bit (10.0.19044.0)

Re: Script recode values for multiple columns using Associative Array and loop

jthi — Thu, 22 Jun 2023 13:56:22 GMT

How do you build your mapping for column names? Are the original columns always in same format? I make the assumption in my example script that they are, so those can be looped over

Names Default To Here(1);

// this is original datatable 
dt_data = Open("help_data.jmp");

// this is mapping file
dt_mapping = Open("help_med_code.jmp");

//The associative array 
aa_mapping = Associative Array(:number, :med);
aa_mapping << Set Default Value("MISSING MAPPING INFO");

// build column name mapping
aa_colnames = Associative Array();
For Each({col_name}, dt_data << Get Column Names("Character", "String"), 
	If(Starts With(col_name, "med_"),
		aa_colnames[col_name] = Substitute(col_name, "med_m", "drugname_");
	)
);

For Each({{orig_col, new_col}}, aa_colnames,
	dt_data << New Column(new_col, Character, Nominal, << Set Each Value(
		aa_mapping[Column(dt_data, orig_col)[Row()]]
	));
);

Re: Script recode values for multiple columns using Associative Array and loop

Valentina — Thu, 22 Jun 2023 21:48:05 GMT

Thank you!

I got data together with labels/dictionary for the data.

I am glad the script works.