create & transpose column data based on intervals

haleyhoewt

Hi Data Experts!

I have a set of data I'm looking to better organize. 

Problem Statement: would like a intervaled report-out of the mass rate of change with respect to time.

 

Time	Mass (kg)
1	20
2	21
3	25
...	...
10	60
11	62
12	64
...	...
20	79
21	73
22	74
...	...
30	92
...	...

 

While i'm open to alternative solves to satisfy the above problem statement, currently what I'm finding as likely the best path forward (but having difficulty understanding how to implement) is as follows:

 

Transpose data in subsets of 10s to get this:

Time Intervals	1-10	11-21	22-32	33-43	44-54	etc...
dM/dt	=(60-20)/10	=(62-73)/10	ect..

 

wondering if someone can offer insight on how i would do this...

 

Thanks!

-Haley

 

jthi

The intervals are changing, is it on purpose? First is 10 values but the rest are 11 values.

I would add two formula columns:

1. First to calculate category
2. Second to calculate dM/dt

Then I would create Summary table and report that.

Start table:

End result:

 

Here is example script with two calculations for categories and ending up with summary table, this all can also be done interactively from JMP:

Names Default To Here(1);
dt = New Table("Untitled",
	Add Rows(33),
	New Column("Time",
		Numeric,
		"Continuous",
		Format("Best", 12),
		Set Values(
			[1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 31,
			32, 33]
		)
	),
	New Column("Mass",
		Numeric,
		"Continuous",
		Format("Best", 12),
		Set Values(
			[78, 89, 72, 72, 74, 81, 70, 74, 84, 89, 52, 81, 52, 60, 64, 83, 53, 60, 70, 64, 70, 55, 82, 74, 69, 83, 56, 71, 54,
			50, 53, 67, 88]
		)
	)
);

dt << New Column("Category", Numeric, Ordinal,
	Formula(Floor((:Time -1) / 10)+1)
);

dt << New Column("Category2", Character, Numeric,
	Formula(
		Char(Floor((:Time - 1) / 10)*10 + 1) || "-" || Char((Floor((:Time - 1)/ 10)+1) * 10)
	)
);

dt << New Column("dmdt", Numeric, Continuous,
	Formula((Col Max(:Mass, :Category) - Col Min(:Mass, :Category)) / 10)
);

dt_sum = dt << Summary(
	Group(:Category),
	Mean(:dmdt),
	Freq("None"),
	Weight("None"),
	statistics column name format("column"),
	Link to original data table(0)
);

 

interval_data.jmp