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!

Mission Control | Explore the latest release of Splunk Mission Control (2.3)

We’re happy to announce the release of Mission Control 2.3 which includes several new and exciting features ...

Cloud Platform | Migrating your Splunk Cloud deployment to Python 3.7

Python 2.7, the last release of Python 2, reached End of Life back on January 1, 2020. As part of our larger ...

Splunk Observability Cloud | Enhancing Your Onboarding Experience with the ...

We understand that your initial experience with getting data into Splunk Observability Cloud is crucial as it ...