nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

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

HenrietteK

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

Accepted answers

jthi

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.

dale_lehman

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

test data.jmp

All comments

dale_lehman

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

test data.jmp

jthi

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

HenrietteK

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

HenrietteK

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

jthi

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.

dale_lehman

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

test data.jmp

HenrietteK

Hi Dale.

Thank you very much for your help.

BR Henriette