Hi , i have 2 datasets - containing time stamp and tag value
i want combine 2 dataset with matching time stamp corresponding data to it
one dataset i have 20 rows with time stamp but in another dataset i have only 10 time stamp row. but i want combine them with corresponding value. please help me out.
i want add a column from 2nd dataset to first dataset please help me on this
Does this t_stamp have miliseconds too? Because then it might be hard to find two matching time’s.
Yes it has but I want combine timestamp column and add tag path in new column
I tired system.dataset.append but it’s merging time stamp and but not adding new column. It’s merging tag values in second column itself
No i don’t want to match time… I want to merge time stamp from 2 dataset to single colum and tag values with 2 differnent colum
Timestamp, tagpath, tagpath2 like this
you can try, system.dataset.addColumn
https://docs.inductiveautomation.com/display/DOC81/system.dataset.addColumn
Or you can made a temporay dataset with the 3 colum headers and add the rows in by iteration over everything
You can convert the dataset to a Pydataset to for some more options if you are familiar with that
OK Wil try
data = event.source.parent.getComponent('Tag Browse Tree').selectedPaths
row = data.getValueAt(0, 0)
endTime = system.date.now()
endTime = system.date.addMinutes(endTime, -(event.source.parent.getComponent('Numeric Text Field').intValue))
startTime = system.date.addMinutes(endTime, -(30))
MyDataset = system.tag.queryTagHistory(paths=[row], startDate=startTime, endDate=endTime, returnSize=0, aggregationMode="Average", returnFormat='Wide',noInterpolation =1)
Newcolumn= MyDataset.getColumnAsList(1)
print Newcolumn
event.source.parent.getComponent('Chart').Data = system.dataset.addColumn(event.source.parent.getComponent('Chart').Data , Newcolumn,row,float)
tired this script but getting this error
2nd arg can't be coerced to org.python.core.PySequenceds1Header = ["time", "clm1"]
ds2Header = ["time", "clm2"]
data1 = []
data1.append([1,1])
data1.append([2,2])
data2 = []
data2.append([3,3])
data2.append([4,4])
ds1 = system.dataset.toDataSet(ds1Header, data1)
ds2 = system.dataset.toDataSet(ds2Header, data2)
columnData = []
for i in range(ds1.getRowCount()):
columnData.append(0)
ds1 = system.dataset.addColumn(ds1, 2, columnData, "clm2", int)
columnData = []
for i in range(ds2.getRowCount()):
columnData.append(0)
ds2 = system.dataset.addColumn(ds2, 1, columnData, "clm1", int)
ds3 = system.dataset.appendDataset(ds1, ds2)
system.tag.write("PATH",ds3)
(dont actually overwrite ds1 and ds2 with the extra columdataset)
like that? sry not the best names but tried to be fast xd
You will need to iterate through each dataset. I find dictionaries useful for this.
# Set up sample datasets
data1 = [['t_stamp', 'val1'],
[1, 123],
[3, 456],
[4, 789],
[9, 345],
[10, 678],
[11, 901],
[15, 234],
[17, 567],
[20, 890],
]
data2 = [['t_stamp', 'val2'],
[2, 987],
[3, 654],
[7, 321],
[9, 876],
[12, 543],
[14, 210],
[17, 765],
[20, 432],
[22, 109],
]
dataset1 = system.dataset.toDataSet(data1[0], data1[1:])
dataset2 = system.dataset.toDataSet(data2[0], data2[1:])
########################################################
def combineDatasets(dataList, commonCol = 't_stamp'):
''' Combine multiple datasets based on a common column
dataList: list of datasets
commonCol: column name common to all datasets
'''
# Convert all datsets to BasicDataset, if needed
for i, data in enumerate(dataList):
if 'com.inductiveautomation.ignition.common.BasicDataset' not in str(type(data)):
dataList[i] = system.dataset.toDataSet(data)
# Create default value dictionary containing all column names
# with None values
blankValueDict = {}
for data in dataList:
colNames = list(data.getColumnNames())
for col in colNames:
if col != commonCol and col not in blankValueDict.keys():
blankValueDict[col] = None
# Process the data
dataDict = {}
for data in dataList:
colNames = list(data.getColumnNames())
for i in xrange(data.rowCount):
commonColValue = data.getValueAt(i, commonCol)
if commonColValue not in dataDict.keys():
dataDict[commonColValue] = blankValueDict.copy()
for col in colNames:
if col != commonCol:
dataDict[commonColValue][col] = data.getValueAt(i, col)
# Create combined dataset
headers = [commonCol] + sorted(blankValueDict.keys())
data = []
for key in sorted(dataDict.keys()):
newRow=[]
newRow.append(key)
for col in headers[1:]:
newRow.append(dataDict[key][col])
data.append(newRow)
return system.dataset.toDataSet(headers, data)
combinedData = combineDatasets([dataset1, dataset2])
Output:
row | t_stamp | val1 | val2
---------------------------
0 | 1 | 123 | None
1 | 2 | None | 987
2 | 3 | 456 | 654
3 | 4 | 789 | None
4 | 7 | None | 321
5 | 9 | 345 | 876
6 | 10 | 678 | None
7 | 11 | 901 | None
8 | 12 | None | 543
9 | 14 | None | 210
10 | 15 | 234 | None
11 | 17 | 567 | 765
12 | 20 | 890 | 432
13 | 22 | None | 109
That seems overly complex, 2 addColums and an append work aswell
Mine works with a variable number of datasets, both PyDataset and BasicDatasets, and you can specify the common column. 
He also has more than one location and I’m sure he will want to do the same operation on more than just these two datasets.
Thanks i will implement and check
Thanks i will check jordan
True, you made a general function. Its really usefull, i might add it in a library for myself.
But prasath seems to be learning the program, so it might be nice for him to try and do it himself atleast once:p before using your code.
One question I have tag tree browser where the user selecting the tag and it’s showing data in chart using query history function
When the user selecting single tag 10 times so ten dataset is showing in that chart
combineDatasets([dataset1, dataset2])
Is there any way to call dataset dynamically?
If they select four tags u want to combine for dataset which I am query using single system.tag.queryhistory
If you monitor the tag browser’s selectedPaths property, you can build the tag list.
if event.propertyName = 'selectedPaths':
selectedPaths = event.source.selectedPaths
tagList = []
for i in selectedPaths.rowCount:
tagList.append(selectedPaths.getValueAt(i,0))
error - 'int' object is not iterable excuting the script
for i in selectedPaths.rowCount(): - error showing in this line
actually my question is i am running system.tag.queryTagHistory. to get the data for dataset. in chart we have one dataset called data.
i need to pass the queried data to the chart dataset(called data)
instead of using each dataset for every query . i want to pass the dataset data to single data set
so only i am confused how to use combineDatasets([dataset1, dataset2]) this function dynamically to pass the data to dataset for each query i am doing for selected tag from tag tree browser
Sorry, remove the parenthesis after rowCount.
If you pass the tag names as a list to the query, then it should return the fully formed single dataset, without having to use the function I gave you.