topic How to rename multiple variables from 1 column? in Discussions

How to rename multiple variables from 1 column?

Azim — Fri, 09 Aug 2024 12:20:46 GMT

I have multiple variables in 1 column (i.e., B,F,T) which needed to be renamed.

Local({dt, col1},
	dt = Data Table("ProductDefect");
	dt << Begin Data Update;
	col1 = dt << New Column(dt:LayerData);
	col1 << Set Name("Side");
	dt << Move Selected Columns({col1}, after(dt:LayerData));
	For Each Row(
		dt,
		col1[] = If(Contains(dt:LayerData, "B"),
			"Back",
			dt:LayerData
		)
	);
	dt << End Data Update;
);

Using this script, I am only changing for B while the other variable (i.e., "F","T") will not be changed. What should be done for all to be renamed in new column; B = Back, F = Front, T = Top?

Re: How to rename multiple variables from 1 column?

jthi — Fri, 09 Aug 2024 12:27:24 GMT

Recode platform can create this type of conversion for you easily.

Local({dt, col1},
	dt = Data Table("Big Class");
	dt << Begin Data Update;
	col1 = dt << New Column(dt:sex);
	col1 << Set Name("sex 2");
	dt << Move Selected Columns({col1}, after(dt:sex));
	dt << Recode Column(
		dt:sex,
		{Map Value(_rcOrig, {"F", "Female", "M", "Male"}, Unmatched(_rcNow))},
		Update Properties(1),
		Target Column(col1)
	);
	dt << End Data Update;
);

You might also want to consider using Value Labels column property

It won't change the data in the table, but you will see the "recoded" values

Re: How to rename multiple variables from 1 column?

Azim — Fri, 09 Aug 2024 12:59:25 GMT

Thank you for the feedback!

However, by using recode platform, the script cannot be automated as i will have to upload data from database and manually Recode it everytime there are new additions to database.

As Sample attached, say, my data keeps being updated from system the naming will be changing continuously while the number and variable maintains (e.g, 1B-xx, 1F-xx, 1T-xx, 2B-xx, 2F-xx, 2T-xx, ...). From system, it will not be automatically displayed whereby B = Back, F = Front, T = Top.

Is there a way without having to repeat Recode everytime I want to do analysis?

Re: How to rename multiple variables from 1 column?

jthi — Fri, 09 Aug 2024 13:03:06 GMT

Should "2B-New" be "Back" or "2Back-New" and so on?

Re: How to rename multiple variables from 1 column?

Azim — Fri, 09 Aug 2024 13:06:43 GMT

Yes, in new column, I only want to specify the "Side". So 2B-New will be "Back", 2T-Basic will be "Top" and so on.

Re: How to rename multiple variables from 1 column?

jthi — Fri, 09 Aug 2024 13:10:36 GMT

You can use Recode and Replace string multiple times. I don't know your data, so I'm not sure what would be the best way (I would maybe go with simple regex and if more complicated is necessary you could also do that)

Repeat this for all with their own patterns

And JMP creates you a script

// Recode column: ProductDefect
Local({dt},
	dt = Data Table("Sheet1");
	dt << Begin Data Update;
	dt << Recode Column(
		dt:ProductDefect,
		{Regex(_rcNow, ".*?\d+B.*", "Back", GLOBALREPLACE),
		Regex(_rcNow, ".*?\d+T.*", "Top", GLOBALREPLACE),
		Regex(_rcNow, ".*?\d+F.*", "Front", GLOBALREPLACE)},
		Update Properties(1),
		Target Column(:ProductDefect)
	);
	dt << End Data Update;
);

If you don't want to do it in place,run recode without that enabled

Re: How to rename multiple variables from 1 column?

Azim — Fri, 09 Aug 2024 13:28:52 GMT

Thanks a lot for the help! This works as what I have in mind!