Dataset string to float

We are collecting data from a scale using the TCP driver, which gives us data back in the Message tag, and it comes back as a string that we have to parse. Once it’s parsed we end up with a few columns, and one of them is actual weight data, but it is still a String. We would like to Total, avg, chart, etc. that data. It works great in the Reporting module, but we can’t seem to get that data from a string, to a float so we can chart it. How would we go about taking a column in a dataset that is String (3.49g, 3.61g, 3.50g, etc…) and turn it into a float in a new dataset so it can be charted? (we don’t need the ‘g’ that comes out with the parsing).

[code]>>> s = ‘3.95g’

s = s.replace(‘g’, ‘’)
print float(s)

I can see that for an individual cell, but how would we apply that to a whole column within a dataset? We want to have a new dataset with a column of floats instead of strings, not just one cell.

Can you apply the conversion during your parsing, before you build the dataset?

Kevin, It isn’t possible to apply the code during parsing, (as far as I know). But, we did find a solution that you and other users may/may not find useful; what we did is develop a query and perform a math function on that query, which would have normally (without the math function) returned a string dataset, but with the math function it returns a float dataset. See below:

SELECT ((SUBSTR(Message,9,11)) - 0)AS ‘L1 ABOVE WT’
order by t_stamp DESC
limit 10

With the “- 0” as part of the query it subtracts zero (the math function) without actually changing the number and outputs the results as float data, which can then be summed, averaged, etc.

Hope this helps anybody who query’s and needs to seperate numbers from a string.

Cool solution, thanks for posting it up!

Scott - is your example for MySQL?

Yes, this is in MySQL.