String Parsing into Separate Variables - InfluxDB Cloud

Background
I am using a Particle IOT controller that outputs a concatenated string of variables separated by “_”. I am having trouble parsing this string into separate variables and displaying them in influxdb cloud. Ultimately, I would like to be able to plot these variables against each other for analysis.

I can get the variables separated out into columns in a table but do not know where to begin on how to store these in influx db cloud as separate variables for analysis later.

I tried looking through this forum as well as searching the web but have not found any obvious things to try. Has anybody accomplished something similar and can point me in the right direction?

Output from Particle controller:
4_1_0.000155_0.000154_0.000000_1.100000_0_1614201642_0_0.000000_64_1000.000000_0.400000_0.010000_1_1614208748

Initial attempt at a parsing query:

//necessary libraries to import
import “strings”
import “experimental/array”

//select bucket and filter to correct string variable
from(bucket: “GENII”)
|> range(start: v.timeRangeStart, stop: v.timeRangeStop)
|> filter(fn: ® => r["_measurement"] == “tag1=port”)
|> filter(fn: ® => r["_field"] == “PortOutputString”)
|> filter(fn: ® => r[“tag2”] == “output”)
|> aggregateWindow(every: v.windowPeriod, fn: last, createEmpty: false)

//table formatting - columns for each variable after parsing string
|> map(fn: ® =>
{
parts = strings.split(v: r[“value"], t: "”)
return
{
port: parts[0],
active: parts[1],
rateactual: parts[2],
ratetarget: parts[3],
ratedifference: parts[4],
totalO2: parts[5],
state: parts[6],
webupdatetime: parts[7],
webresetflag: parts[8],
pressure: parts[9],
timebetweendoses: parts[10],
tanksize: parts[11],
mgLmonth: parts[12],
dosesizeaverage: parts[13],
doseneededflag: parts[14],
lastdose: parts[15]
}
}
)

Current Output:
Currently I can output a table with columns for each variable separated out.

Hello @oOo_Alex_oOo,
Sorry for the delay. If you’re able to output a table with columns for each variable separated out, what’s the problem from this point on? What do you mean by store them as separate variables for analysis later? When you say variables, do you mean dashboard variables? Use and manage dashboard variables | InfluxDB OSS 2.0 Documentation
Or do you mean flux variables?

You can use the to() function to write this transformed data to a new bucket or measurement so you can. easily view this output.

Can you please provide me with some clarification on what you would like to see as your final output pease?

Thanks for looking into this @Anaisdg. I apologize that my terminology wasn’t very precise (still learning the correct terms). Let me try again to explain what I am attempting.

For the final output, the column ‘port’ from the table I created should be a tag and all other columns should be individual time series that can be viewed in a dashboard and/or viewed and compared in data explorer. The reason for this is that I am interested in how these columns change over time in relation to each other. For example, I would set up a dashboard to view ‘port 1’ rows only using the tag and then plot the columns ‘rateactual’ and ‘ratetarget’ for a quick visual comparison over time. Another example is I would filter to only ‘port 2’ rows in data explorer by tag then view ‘pressure’ and ‘state’ on the same plot.

The to() function seems like the solution. I tried implementing it today but could not figure out how to get ‘port’ to be a tag and have the other columns from my table be individual time series. I used this webpage to() function | InfluxDB OSS 2.0 Documentation to get to where I am now. I tried to get to() function working with just 1 column at first but have not had success. Do you have any thoughts or additional resources I could review to get the to() function working?

This is the most recent error I am recieiving:

 type error @39:6-46:4: expected [A] but found (r:{B with _value:string}) => {webupdatetime:string, webresetflag:string, totalO2:string, timebetweendoses:string, tanksize:string, state:string, ratetarget:string, ratedifference:string, rateactual:string, pressure:string, port:string, mgLmonth:string, lastdose:string, dosesizeaverage:string, doseneededflag:string, active:string} (argument tables)

I attached .txt files with a sample set of input data and the query I am using.

tofunctiontrial.txt (1.4 KB)
2021-03-16-13-13_influxdb_data.txt (8.6 KB)

Hello @oOo_Alex_oOo,
Thanks for your reply and sorry for the delay.
I believe what you’re really searching for is an unpivot
Add unpivot functionality · Issue #2539 · influxdata/flux · GitHub?

  //necessary libraries to import
import "csv"
import "strings"
import "experimental/array"

mydata = "#group,FALSE,FALSE,TRUE,TRUE,FALSE,FALSE,TRUE,TRUE,TRUE
#datatype,string,long,dateTime:RFC3339,dateTime:RFC3339,dateTime:RFC3339,string,string,string,string
#default,,,,,,,,,							
,result,table,_start,_stop,_time,_value,_field,_measurement,tag2
,,0,2021-03-15T21:13:27.582274686Z,2021-03-16T00:13:27.582274686Z,2021-03-15T21:14:00Z,2_1_0.000000_0.077160_-200.000000_0.000000_1_1615841237_0_2.551200_73_10000.000000_20.000000_5.670000_1_1615841270,PortOutputString,tag1=port,output
,,0,2021-03-15T21:13:27.582274686Z,2021-03-16T00:13:27.582274686Z,2021-03-15T21:15:00Z,1_1_0.000000_0.038580_-100.000000_0.000000_1_1615841237_0_2.534926_146_10000.000000_10.000000_5.670000_1_1615841270,PortOutputString,tag1=port,output
,,0,2021-03-15T21:13:27.582274686Z,2021-03-16T00:13:27.582274686Z,2021-03-15T21:16:00Z,2_1_0.000000_0.077160_-200.000000_0.000000_1_1615841237_0_2.556357_73_10000.000000_20.000000_5.670000_1_1615841270,PortOutputString,tag1=port,output
"

startingpoint = csv.from(csv: mydata)

//table formatting - columns for each variable after parsing string
  //table formatting - columns for each variable after parsing string
  |> map(fn: (r) => 
  {
    parts = strings.split(v: r["_value"], t: "_")
    return 
      {
        _time: r._time,
        port: parts[0], 
        active: parts[1],
        rateactual: parts[2],
        ratetarget: parts[3],
        ratedifference: parts[4],
        totalO2: parts[5],
        state: parts[6],
        webupdatetime: parts[7],
        webresetflag: parts[8],
        pressure: parts[9],
        timebetweendoses: parts[10],
        tanksize: parts[11],
        mgLmonth: parts[12],
        dosesizeaverage: parts[13],
        doseneededflag: parts[14],
        lastdose: parts[15]
      }
  }
)

startingpoint 
|> rename(columns: {rateactual: "_value"})
|> toFloat()
|> set(key: "_field", value: "rateactual")
|> yield(ame: "ratetactual")

startingpoint
|> rename(columns: {ratetarget: "_value"})
|> toFloat()
|> set(key: "_field", value: "ratetarget")
|> yield(name: "ratetarget")

However, you can use a combination of rename and set to help you visualize each value if you want.

Does this help?

Thank you kindly for your patience (I was on vacation)

1 Like

SOLVED

I looked through the unpivot link you shared and I do believe that is what I am searching for. Until I can figure out a way to unpivot, your workaround using rename and set have gotten me to a point where I can visualize what I need.

Thank you for the help @Anaisdg!

Hello @oOo_Alex_oOo,
I’m happy to help! It was an interesting use case/visualization challenge.