Combine two datasets

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

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

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.