Formula to interpolate between columns

[#1]

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

YouTube Video on Distillation Plots

ASTM D7169

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!

[#2 · Accepted solution]

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

[#3]

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

[#4 · Accepted solution]

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.

[#5]

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

[#6]

Jerry, This is very cool.  

[#7]

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-