Linear interpolation of a curve to compare with a reference curve

[AdrienS]

Hi everyone,

I have a new problem, at each time-stamp, I am getting a curve of X and Y values. The number of X-Y pairs vary at each timestamp. I have a reference curve of Xref-Yref and now I want to create an interpolation of my X-Y values as Xref-Yinterpolated so I can compare the curves.
Here it is step by step:

 index=index source=source | head 1 | dedup PartId1 | rex field=_raw "<FloatPoints(?P<FP_Values>.*)<\/FloatPoints>" | rex mode=sed field=FP_Values "s/>/|/" | rex field=FP_Values "\|(?<XWert>\d+.\d+)" max_match=5000 | table PartId1 XWert | mvexpand XWert | streamstats count | join type=OUTER count [ search index=atmo_pc* source=mysql* | head 1 | rex field=_raw "<FloatPoints(?P<FP_Values>.*)<\/FloatPoints>" | rex field=FP_Values ";(?<YWert>\-*\d\.\d+)" max_match=5000 | table YWert | mvexpand YWert | streamstats count ] 

So I get the following:
PartId---Xwert-----YWert---count
0001-----75.11-----0.004-----1
0001-----75.32-----0.010-----2
0001-----75.53-----0.032-----3
...
I can easily add my reference curve (with an increment of 0.3 on the X ) via an inputlookup command but the count of Xvalues, Yvalues is different from Xref, Yref.

PartId---Xwert-----YWert----Xref----Yref-----count
0001-----75.11-----0.004---74.9---0.0021---1
0001-----75.32-----0.010---75.2---0.0035---2
0001-----75.53-----0.032---75.5---0.0058---3
...
So here is the question, I want to create a linear interpolation that I will do via a loop in python, R, even excel... but here I don't know how to get the following:
PartId---Xwert-----YWert----Xref----Yref----- Yinterpolated ----count
0001-----75.11-----0.004---74.9---0.0021---------0.000-------------1
0001-----75.32-----0.010---75.2---0.0035---------0.007-------------2
0001-----75.53-----0.032---75.5---0.0058---------0.030-------------3
...

So then I could easily compare YWert ant Y interpolated and check if it is outside the 2 standard deviations.

Many thanks in advance, I hope it is clear enough.

[AdrienS · Accepted solution]

OK, I managed to do it, it was not that complex at the end. (thanks to a previous post from @martin_mueller)

1) Join your X-Y pairs on streamstats count
2) make continuous your count to the increment of X required (called disp here)
3) join the reference X-Y pairs on disp
4) use streamstats and reverse to get your last X, last Y, next X, next Y
5) Calculate your interpolated Y and merge Yvalues and Y interpolated
6) Create your outlier plot

 index=source source=source | head 1 | dedup PartId1 | rex field=_raw "<FloatPoints(?P<FP_Values>.*)<\/FloatPoints>" | rex mode=sed field=FP_Values "s/>/|/" | rex field=FP_Values "\|(?<XWert>\d+.\d+)" max_match=5000 | table PartId1 XWert | mvexpand XWert | streamstats count | join type=OUTER count [ search index=atmo_pc* source=mysql* | head 1 | rex field=_raw "<FloatPoints(?P<FP_Values>.*)<\/FloatPoints>" | rex field=FP_Values ";(?<YWert>\-*\d\.\d+)" max_match=5000 | table YWert | mvexpand YWert | streamstats count ] | makecontinuous XWert span=0.005 | eval disp=round(XWert,3) | join disp [|inputlookup ref_curve.csv |rename Displacement as disp] | eval value_disp = case(isnotnull(YWert), disp) | streamstats last(YWert) as last_force last(value_disp) as last_disp | reverse | streamstats last(YWert) as next_force last(value_disp) as next_disp | reverse | eval interpolated_force_val = if(last_force + ((disp - last_disp) / (next_disp - last_disp)) * (next_force - last_force)=0,last_force, last_force + ((disp - last_disp) / (next_disp - last_disp)) * (next_force - last_force))| eval interpolated_force=if(isnotnull(YWert), YWert,interpolated_force_val) | eval F_pstdev=Force+2*stdev | eval F_mstdev=Force-2*stdev | table disp, interpolated_force, F_mstdev,  F_pstdev

I hope it can help people in the future.
AdrienS