topic Re: How do I transform a time data column (nominal) into continuous data in one unit (months)? in Discussions

How do I transform a time data column (nominal) into continuous data in one unit (months)?

HenrietteK — Sat, 10 Jun 2023 23:39:51 GMT

Hello All.

In JMP, I work with stability data extracted from a LIMS system. When doing the analysis, I would have time in months on the X axis. The extracted data comes in two time “formats”: Either as time given in months (e.g. “M-12” or “M-36”) or as time given in days (e.g. “EOS-968” or “EOS-1011”). All data are located in the same column.

I need the data to be continues to be able to do the required analysis. Regarding time in months, I normally just remove the “M-“ by doing a “search and replace” – and leaving replace empty. It’s more work to change the days into months.

My own initial thought was to first remove the “EOS-“ part, then recalculate into months, but since the data are all located in the same column I’m finding it hard to keep the two “data sets” apart until they have the same unit.

Would you have any nice ideas on how I can more easily do this transformation? Maybe a small script is required?

Any help is greatly appreciated.

Best regards

Henriette

Re: How do I transform a time data column (nominal) into continuous data in one unit (months)?

dale_lehman — Thu, 04 Nov 2021 14:50:49 GMT

The "word" function seems to do the trick - see the attached example file.

Re: How do I transform a time data column (nominal) into continuous data in one unit (months)?

jthi — Thu, 04 Nov 2021 15:09:55 GMT

In addition to using Word() you can use Starts With and If statement to determine how you should calculate the month.

This below script will add placeholder values 1 (month) and 2 (day). Replace them with your logic for month calculation

Names Default To Here(1);

dt = New Table("Untitled",
	Add Rows(4),
	New Column("Time", Character, "Nominal", Set Selected, Set Values({"M-12", "M-36", "EOS-968", "EOS-1011"}))
);

dt << New Column("MonthNumber", Numeric, Continuous, Formula(
	If(StartsWith(:Time, "M"),
		//month logic here
		1
	,
	StartsWith(:Time, "EOS"),
		//day logic here
		2
	,
		. //if starts with something else than "M" or "EOS"
	);
));

Re: How do I transform a time data column (nominal) into continuous data in one unit (months)?

HenrietteK — Thu, 04 Nov 2021 16:55:03 GMT

Hi Dale. Thank you for your help. As far as I can see, using the Word function as described will give me one column of continuous data (yay), but in two different units (days and months).

Any ideas on how I can recalculate the days into months, but only doing this for the rows where the numeric value is in days?

BR Henriette

Re: How do I transform a time data column (nominal) into continuous data in one unit (months)?

HenrietteK — Thu, 04 Nov 2021 17:01:27 GMT

Hello.

Thank you very much for your help. I'm totally new in JMP scripting, so maybe you can point me in the right direction doing the missing logics.

For "month logic" I just want to remove the "M-" part, so I'm thinking the Word function from Dales answer should do the trick.

For "day logic" I want to remove the "EOS-" part (using Word function) and then calculate the numeric part from days into months. Maybe a stupid question, but can I just add two line of coding or do I need to store it in a variable somehow?

Thanks :)

Re: How do I transform a time data column (nominal) into continuous data in one unit (months)?

jthi — Thu, 04 Nov 2021 17:11:02 GMT

You don't need to store it in a variable.

Example below. Run the script and check the formula for MonthNumber:

Names Default To Here(1);

dt = New Table("Untitled",
	Add Rows(4),
	New Column("Time", Character, "Nominal", Set Values({"M-12", "M-36", "EOS-968", "EOS-1011"}))
);

dt << New Column("MonthNumber", Numeric, Continuous, Formula(
	If(StartsWith(:Time, "M"),
		Num(Word(2, :Time, "-"))
	,
	StartsWith(:Time, "EOS"),
		Num(Word(2, :Time, "-"))/30
	,
		. //if starts with something else than "M" or "EOS"
	);
));

You can then modify the formula as required.

Re: How do I transform a time data column (nominal) into continuous data in one unit (months)?

dale_lehman — Thu, 04 Nov 2021 17:14:10 GMT

I see other similar ideas, attached is mine (without scripting).

Re: How do I transform a time data column (nominal) into continuous data in one unit (months)?

HenrietteK — Fri, 05 Nov 2021 17:14:20 GMT

Hello Jarmo.

That worked perfectly. Thank you very much for your help.

BR Henriette

Re: How do I transform a time data column (nominal) into continuous data in one unit (months)?

HenrietteK — Fri, 05 Nov 2021 17:16:28 GMT

Hi Dale.

Thank you very much for your help.

BR Henriette