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

[#1]

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

[#2]

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

[#3]

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"
	);
));

[#4]

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

[#5 · Accepted solution]

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.

[#6]

Hello Jarmo.

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

BR Henriette

[#7]

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

[#8 · Accepted solution]

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

[#9]

Hi Dale.

Thank you very much for your help.

BR Henriette