Dashboards & Visualizations

Linear interpolation of a curve to compare with a reference curve

AdrienS
Explorer

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.

0 Karma
1 Solution

AdrienS
Explorer

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

View solution in original post

0 Karma

AdrienS
Explorer

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

0 Karma
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...