topic Re: create & transpose column data based on intervals in Discussions

create & transpose column data based on intervals

haleyhoewt — Sat, 10 Jun 2023 23:39:52 GMT

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

Re: create & transpose column data based on intervals

jthi — Thu, 04 Nov 2021 16:33:17 GMT

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

I would add two formula columns:

First to calculate category
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)
);

Re: create & transpose column data based on intervals

ron_horne — Thu, 04 Nov 2021 18:20:25 GMT

Hi @haleyhoewt ,

in the following table you have a script that will produce the table summary.

you can change the size of the ranges by modifying one number in the sequence column as in the picture,

let us know if it helps

Re: create & transpose column data based on intervals

haleyhoewt — Mon, 08 Nov 2021 22:33:43 GMT

Hi @jthi : i did a really poor job at explaining. so this is just a snippet. it's arbitrary as far as the range.

For context, i'm discharging material to a drum from a tank. When drum gets close to full, i'll shut down the system (so drum mass changes marginally during this time), but this is arbitrary--it's not shut down every single time at the same drum mass.

so basically, i need to loop through the dataset. to get how many drums we go through, what are the starting mass values, ending mass values, and what is the duration.

Re: create & transpose column data based on intervals

brady_brady — Mon, 08 Nov 2021 23:53:47 GMT

Hi,

As @jthi mentions, a good way to do this is with the Summary platform, provided you capture the data with some "Group" variable that changes whenever the process is stopped. Here, I've used "Cycle" for this purpose.

For example, suppose we have this data:

We can use the Summary platform, filling out the dialog like so:

Which produces:

Then, in this new Summary table, create a new column, dMdt, with a formula (the 3600 factor is to convert the base time unit of seconds to hours):

Now we have:

I've attached the first dataset so you can try this out.

Cheers,

Brady