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!

New in Observability - Improvements to Custom Metrics SLOs, Log Observer Connect & ...

The latest enhancements to the Splunk observability portfolio deliver improved SLO management accuracy, better ...

Improve Data Pipelines Using Splunk Data Management

  Register Now   This Tech Talk will explore the pipeline management offerings Edge Processor and Ingest ...

3-2-1 Go! How Fast Can You Debug Microservices with Observability Cloud?

Register Join this Tech Talk to learn how unique features like Service Centric Views, Tag Spotlight, and ...