Script for table processing JMP14

[#1]

Hi. Could you please help me to write a script that takes the original table and make a new table out of it? Original table has two lines per each sample temperature measurement. In the output table we use only one row per each sample and calculate number of the days between the two measurements and the temperature delta.

[#2]

Could you provide the example data as JMP table so we don't have to create it from the image (formats can have big impact on the script)

[#3]

Hi Jarmo. Thanks for your support. Please find attached.

[#4]

You can use following formulas

:Temperature - (Col Moving Average(:Temperature, 1, 1, 0, :Sample) * 2 - :Temperature)

and

(:Date - (Col Moving Average(:Date, 1, 1, 0, :Sample) * 2 - :Date)) / In Days(1)

to calculate the deltas.

Then either create new table without the 0 rows (don't include formulas in that table) or remove formulas and delete those 0 rows.

I think this script should work for JMP14 (I cannot test it as I don't have access to that)

Names Default To Here(1);

dt = Open("$DOWNLOADS/source table.jmp");

dt << New Column("Time Delta", Numeric, "Continuous", Formula(
	:Temperature - (Col Moving Average(:Temperature, 1, 1, 0, :Sample) * 2
	- :Temperature)
));

dt << New Column("Temperature Delta", Numeric, "Continuous", Formula(
	(:Date - (Col Moving Average(:Date, 1, 1, 0, :Sample) * 2 - :Date)) /
	In Days(1)
));

dt << Select Where(:Temperature Delta == 0) << Invert Row Selection;

dt_subset = dt << Subset(
	Copy formula(0),
	Selected Rows(1),
	Columns({"Sample", "Time Delta", "Temperature Delta"}),
	Output Table("Deltas")
);

dt << Clear Select;

[#5]

Kiitos Jarmo. Let me try it.

[#6]

Jarmo, thanks a lot for your solution. It is working.

But I have two more questions:

1. Could you please explain what does the formula with Moving Average calculation does? I just want to understand the logic of your approach...

2. I just noticed that if the first line goes with later date, it gives the negative Time delta... The Time Delta should always be positive. Will it work if I sort the source table in the very beginning so your script works correctly?

[#7]

Sorting should work, but you could consider also other formulas if that is the case (if the order is always later - earlier).  I use Col Moving Average, so I can access to value from earlier row for the group.

[#8]

You could add two formula columns that calculate the maximum - minimum for each sample (temperature and date):

Or run below script to get your example table with formula columns.

New Table( "Example",
	Add Rows( 8 ),
	New Column( "Sample",
		Numeric,
		"Continuous",
		Format( "Best", 12 ),
		Set Values( [1, 1, 2, 2, 3, 3, 99, 99] )
	),
	New Column( "Temperature",
		Numeric,
		"Continuous",
		Format( "Best", 12 ),
		Set Values( [199, 210, 201, 217, 200, 215, 199, 220] )
	),
	New Column( "Temp Diff",
		Numeric,
		"Continuous",
		Format( "Best", 12 ),
		Formula(
			Col Maximum( :Temperature, :Sample )
			-Col Minimum( :Temperature, :Sample )
		)
	),
	New Column( "Date (ISO 9601 format)",
		Numeric,
		"Continuous",
		Format( "yyyy-mm-dd", 10 ),
		Input Format( "yyyy-mm-dd" ),
		Set Values(
			[3795552000, 3798576000, 3795552000, 3798748800, 3795552000, 3798662400,
			3795552000, 3798921600]
		)
	),
	New Column( "Date Diff",
		Numeric,
		"Continuous",
		Format( "Best", 12 ),
		Formula(
			Date Difference(
				Col Minimum( :"Date (ISO 9601 format)"n, :Sample ),
				Col Maximum( :"Date (ISO 9601 format)"n, :Sample ),
				"Day"
			)
		)
	)
)

And then a quick summary will give you the table you were after:

[#9]

Hi Paul. Thanks for your idea. Will this approach work if the temperature delta sometimes is negative, and it is critical for me?

[#10]

Okay. I checked, it is not working. Can we use some other formula to pick the temperature measurement from the later date and from the earlier date?