topic Re: Need help to recode a column with lab values in Discussions

Need help to recode a column with lab values

LogitTurtle576 — Sat, 10 Jun 2023 23:51:51 GMT

In my table I have 3 columns : time, lab data and one temperature.

For the lab data I would like to keep only the value when it comes available and set all consecutive rows as null until we have a new lab result.

How should I write a script to handle this ?

Thanks

Re: Need help to recode a column with lab values

SDF1 — Wed, 27 Jul 2022 18:22:12 GMT

Hi @LogitTurtle576 ,

One possible way is to generate a new column with the following formula:

If(
	:Column 1 == Lag(:Column 1, 1), .,
	Row() == 1, :Column 1[1],
	:Column 1[Row()]
)

where :Column 1 is your data column of interest. This will set all values to null, except when there is a change from one row to the next, then it'll use that value. The middle condition sets the first row to the value of row 1 of your data column.

I'm sure there are other ways, but I know this one can do what you're interested in doing. Plus, since you have another column, you don't lose any of your original data by recoding or overwriting the original column.

Hope this helps!,

Re: Need help to recode a column with lab values

Georg — Wed, 27 Jul 2022 17:21:52 GMT

In addition to formula solution of DS you could handle it like shown below.

First we need to sort Big Class by age (this is your lab data).

The we select the column age and let jmp find all repeating rows for age.

For these we delete the age values.

BR Georg

Names Default To Here( 1 );

dt = Open( "$SAMPLE_DATA\Big Class.jmp" );
dt << sort( by( age, name ), Replace Table );

dt << select columns(age);
dt << select duplicate rows();
dt:age[dt << get selected rows()]=.;

Re: Need help to recode a column with lab values

jthi — Wed, 27 Jul 2022 18:17:52 GMT

Couple of additional formula options (using just replace << set each value with Formula if you need one)

Names Default To Here(1);

dt = New Table("Untitled",
	Add Rows(9),
	New Column("Column 1", Numeric, "Continuous", Format("Best", 12), Set Values([1, 1, 1, 2, 2, 2, 3, 3, 3])),
	New Column("Column 2", Numeric, "Continuous", Format("Best", 12), Set Values([1.2, 1.2, 1.3, 2.3, 2.4, 2.1, 3.5, 3.6, 3.1]))
);

dt << New Column("Result", Numeric, Continuous, << Set Each Value(
	IfMZ(:Dif(:Column 1) == 0,
		.
	,
		:Column 1
	)
));

dt << New Column("Result2", Numeric, Continuous, << Set Each Value(
	If(Row() == Col Min(Row(), :Column 1),
		:Column 1
	,
		.
	);
));

Re: Need help to recode a column with lab values

LogitTurtle576 — Thu, 28 Jul 2022 07:38:58 GMT

Thanks for your quick reply