Multiple datasets join

Hello,
Is it possible to “concatenate” or join 2 or more datasets?
I have a list of datetimes and i wouldl like to retrieve the data for each month and merge it in a dataset.

for date in test:
   results=system.db.runPrepQuery("SELECT t_stamp as Date, value as Valeur FROM outsideairtemp_snapshot WHERE YEAR(t_stamp)=YEAR(?) AND MONTH(t_stamp)=MONTH(?) ORDER BY t_stamp",[date,date])

I tried using the append function but it works only with lists and not datasets.
Any idea how to make it work ?

Datasets and PyDatasets are normally immutable, meaning that you can not change them.

You would need to loop through both data sets, creating a new list and then use that to create a new data set containing the result of the operation.

There is almost certainly a better way to go. Depending on how many date values are in test, you would need to loop through the same data multiple times and basically accomplish nothing.

What you are asking can be done with some Python code that loops over the datasets to create another dataset.

But the easiest way to do it is by using MutablePyDataSets from the Power Scripting module.

Here is an example of doing it with MutablePyDataSets:

result1 = system.db.runPrepQuery("SELECT ...whatever")
result2 = system.db.runPrepQuery("SELECT ...whatever")

result3 = pa.dataset.toData(result1) + pa.dataset.toData(result2)

#print out result3 to see what it looks like in the console
print result3

#Convert to a regular dataset
result3 = dataset3.toDataSet()

Here is another example that shows some output of printing a MutablePyDataSet:

#Create the first dataset
columnNames = ["name","age","rank"]
rows = [["Bob",32,"Private"],
        ["Bill",28,"Major"]]
dataset1 = system.dataset.toDataSet(columnNames, rows)

#Create the second dataset
rows = [["Mike",33,"Cadet"], 
        ["Jean",67,"Second Officer"],
        ["Bean",77,"First Officer"]]
dataset2 = system.dataset.toDataSet(columnNames, rows)

dataset3 = pa.dataset.toData(dataset1) + pa.dataset.toData(dataset2)

print dataset3

#Convert to a regular dataset
dataset3 = dataset3.toDataSet()

#Here is the output from printing dataset3
"""
row | name age rank          
-----------------------------
 0  | Bob  32  Private       
 1  | Bill 28  Major         
 2  | Mike 33  Cadet         
 3  | Jean 67  Second Officer
 4  | Bean 77  First Officer 
"""
1 Like

Probably need some sanity checks in here, but this should do…

ds1 = system.db.runQuery("select * from table1","IGNITION")
ds2 = system.db.runQuery("select * from table1","IGNITION")

for i in range(ds1.getUnderlyingDataset().getColumnCount()):
	ds1.getUnderlyingDataset().getData()[i] += ds2.getUnderlyingDataset().getData()[i]
	
print ds1.getUnderlyingDataset().getData()

EDIT: probably needs to be modified using SetDataDirectly() instead of just concat.

The method below will accept multiple datasets (at least two) and return a unified dataset if the datasets have the same column specifications:

def appendDataset(self, ignoreMismatch, *dss):
	# Validate at least 2 datasets have been supplied
	if len(dss) < 2:
		raise KeyError("Must provide at least 2 datasets for appending")
		
	# Validate Column Alignment	
	if not ignoreMismatch and len([x for x in range(len(dss))[1:] if (dss[x].getColumnNames() != dss[0].getColumnNames() or dss[x].getColumnTypes() != dss[0].getColumnTypes()) and dss[x].rowCount > 0]) > 0:
		raise KeyError("Column Name/Type Mismatch!")

	# Prepare Final Dataset
	headers = map(str,dss[0].getColumnNames())
	data = []
	
	# Gather Data from supplied datasets
	for ds in dss:
		for i in range(ds.rowCount):
			data.append([x[i] for x in ds.getData()])

	# Return newly compiled dataset
	return system.dataset.toDataSet(headers, data)

Hope this helps!

6 Likes

I think he wanted “horizontal” concatenation,
what you did was “vertical”, instead you could have used

system.dataset.appendDataset()

in a loop

Not at the time, system.dataset.appendDataset() did not exist

Hey @nmudge,

Interested in this module for Ignition 8.0. Do you know if there's an existing version somewhere (that has an up-to-date certificate for 8.0?

Nick has not done any work in Ignition in several years (look at his profile activity), so don't hold your breath. And it isn't just a certificate question--almost all modules from v7.9 and before require some rework to load in v8.x.