Working on doing some subtraction of daily meter readings for a report using a script data source. Raw data source is from a tag historian query (‘METER’).
I am getting an error message (subject of this post) and the data type I’m performing the operation on should be a floating point value. I believe the code I referenced might be generically adding an integer column, not sure? Do I need to use system.dataset.addColumn in lieu of headers = list(pyDataset.getColumnNames()) + [‘Difference’]? If so, how would I use system.dataset.addColumn here?
Another question is, I actually have a query and datakey where I have used a tag historian query to get the previous value. How would I reference that instead of initializing previousValue to 0? It is a query with a fixed sample size of 1.
Thank you for any help!
def updateData(data, sample):
#data['myKey']='Example'
# Access the raw data from an existing data key
rawDataset = data['METER'].getCoreResults()
#Convert the dataset to a pydataset for easier raw iteration
pyDataset = system.dataset.toPyDataSet(rawDataset)
# Initialize a list to hold the new data, including a new difference column
newData = []
headers = list(pyDataset.getColumnNames()) + ['Difference']
# Subtract the value in the current row from the value in the previous row for a 'Value' column
previousValue = 0 # Initialize the 'previous' value
for i, row in enumerate(pyDataset):
currentValue = row['ty_total']
if i == 0:
difference = 0 # No previous row for the first entry
else:
difference = currentValue - previousValue
# Append all existing values and the new difference value to the list
rowList = list(row) + [difference]
newData.append(rowList)
previousValue = currentValue # Update the previous value for the next iteration
#Convert the list of new data back into a standard dataset
resultDataset = system.dataset.toDataSet(headers, newData)
# Assign the new dataset to a new data key
data['METERDIFFERENCE'] = resultDataset
The system functions that create new datasets, or add columns, use the values in the first row to infer the data types to use for the columns.
If that doesn't yield correct behavior (due to mixed types), you need to instead use the DatasetBuilder utility, where you explicitly set the column types.
Thank you @tgarrison. Once I changed both previousValue to 0.0 and difference = 0.0, it cleared up the data type mismatch.
What would the syntax be to reference another tag historian query with sample size 1 that is the previous value? The dataset is called ‘PREVIOUS’ and the datakey is ‘ty_total’. Instead of initializing to 0.0, what would I use? Thank you for any help.
If it is an option, I recommend updating your initial query to have the database calculate & report the difference of two values at time-of-query, and NOT post-process the data. That aside...
Then, calculate your column data and add the new column:
# Generate data for new column:
newColData = [None] # First row initial value - if your schema allows null values
# Get values from existing dataset, calculate new values & append:
for i in range(1, dataset.getRowCount()): # Start from second row
previousValue = dataset.getValueAt(i-1, colName) # Previous row value
currentValue = dataset.getValueAt(i, colName) # Current row value
newColData .append(currentValue - previousValue)
newColName = 'Difference'
# Generate new dataset with added column (omit colIndex to add to end):
newDataset = system.dataset.addColumn(dataset, newColData, newColName, colType)
While I prefer to see None in a row where data cannot be calculated, you may utilize a default value of your choosing. The addColumn method will attempt to coerce all of your column data to the specified type, where possible.
Disclaimer, untested... let me know if you find any mishaps, I'm happy to edit & update as necessary.