433076_en-US

liliconcom

create & transpose column data based on intervals

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

 

AnalyticsData TableScriptingWindowsRe: create & transpose column data based on intervals

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

 

Re: create & transpose column data based on intervals

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