topic Re: Formula to interpolate between columns in Discussions

Formula to interpolate between columns

markschahl — Fri, 09 Jun 2023 23:25:21 GMT

Hi! Got a request from one our users in refining to interpolate a crude distillation curve. Some background info:

Data table has twelve columns with the boiling points for different volume%iles of the crude. I want a formula that will interpolate across the columns to calculate how much has a boiling point less than T.Of.Interest.

I know that the Interpolate() function does this. I also know that I need some way to get the values of the %ile columns into a list, probably using Get As Matrix(). I have yet to venture into the Matrix...

Interpolate( T.Of.Interest, [0.05, 5, 10, 20, 30, 40, 50, 60, 70, 80, 90, 95], [<the columns with the boiling points>] );

How do I get the column values into a list?

Or, is there a better way to do this?

Without transforming the data table, is there a way to plot the BP list versus the %ile list?

Thanks!

Re: Formula to interpolate between columns

gzmorgan0 — Sat, 21 Jul 2018 10:50:38 GMT

You did not mention which version of JMP that you are using. JMP 14 allows scripting syntax, that earlier versions do not allow. My suggestion will use JSL, in case you are using an older version of JMP.

Unless you create a graph from matrices, that is draw your own graph, the table needs a different structure, either stacked or transposed. The attached script reorganizes the table and creates a few graphs. Hope this helps.

Here is the JSL for interpolate

dt = Data Table( "Distillation Curve Interpolation" );

qofi = Column(dt, "%ile.Of.Interest" );
qvec = [.005, .05,.10, .20, .30, .40, .50, .60, .70, .80, .90, .95];
tofi = :Name("T.Of.Interest");

For( i=1, i <= nrow(dt), i++,
   bpvec   = dt[i,2::13];
   qofi[i] = Round( interpolate(tofi, bpvec, qvec ), 3);	
);

Re: Formula to interpolate between columns

jerry_cooper — Sat, 21 Jul 2018 14:26:01 GMT

Hi @markschahl,

As @gzmorgan0 says, reshaping your data will give you much more flexibility with your visualization options. However, since you asked how to do it without transforming the data table, JMP 14 has a new option in the Graph Builder Parallel Plot that will do this.

Drag your %ile columns to the x-axis and select the Parallel Plot element. Right-click the x-axis and select Combine Scales=>Parallel Merged:

Regarding the formula for your %ile.Of.Interest column, this should work:

Interpolate(
:T.Of.Interest,
Data Table( "Distillation Curve Interpolation" )[Row(), Index( 2, 13 )],
[0.05, 5, 10, 20, 30, 40, 50, 60, 70, 80, 90, 95]
)

Hope this helps.

Re: Formula to interpolate between columns

markschahl — Sat, 21 Jul 2018 16:36:53 GMT

Thanks! Worked perfectly. I am using JMP Pro 14.

Re: Formula to interpolate between columns

markschahl — Sat, 21 Jul 2018 16:39:19 GMT

Thanks, Jerry! Worked nicely. Many folks in the company will leverage both solutions to this post.

Re: Formula to interpolate between columns

gzmorgan0 — Sat, 21 Jul 2018 19:06:38 GMT

Jerry, This is very cool.

Re: Formula to interpolate between columns

gzmorgan0 — Sun, 22 Jul 2018 05:23:13 GMT

This is an FYI.

Since you are using JMP 14, below is the formula to get columns 2-13 into a row vector. Note the grave accent, the symbol for transpose or Transpose( Current Data Table()[ Row(), 2::13] ).

Current Data Table()[Row(), Index( 2, 13 )]`

I tried using the table variable qvec, but the formula would not work. However, using a column where all values were the quantiles did work. The table is attached.

The formula column had a different looking syntax in the display. In JSL, it is Interpolate(T.Of.Interest, BPvec, Qvec)

-fyi-