Combine two datasets

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

1 Like
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.PySequence
ds1Header = ["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 
1 Like

That seems overly complex, 2 addColums and an append work aswell

1 Like

Mine works with a variable number of datasets, both PyDataset and BasicDatasets, and you can specify the common column. :wink:

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.

1 Like

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.

sir what i am trying to achieve is , in chart when user selected 2 tags and in chart its showing data from 4 am to 5am for both the tags
but what i am trying is do offset concept. i want to show second tag from 4.10am to 4.50am some offset to second tag

so i am trying to run system.tag.queryTagHistory for every selection of tags in tag tree browser and i am changing start time and end time in query to show some offset between the tags

but i want to show all the tags data in single dataset. because timestamp column will be same

can you help me on this

If you are running a separate query for each one, then you can append each query result to a list, then sent that list to the function

datasetList = []
for tag in tagList:
	datasetList.append(system.tag.queryTagHistory(tag, other_params, as_needed)

combinedData = combineDatasets(datasetList)

‘int’ object is not iterable still same error for i in selectedPaths.rowCount: - in this line

Sorry, my caffeine must have run out. Time for more coffee…

for i in range(selectedPaths.rowCount):

no problem sir

readonly attribute - getting this error