# Multiple datasets join **URL:** \ **Category:** Ignition\ **Created:** [October 16, 2017, 2:45pm UTC](https://forum.inductiveautomation.com/t/multiple-datasets-join/15680 "2017-10-16T14:45:58Z")\ **Posts on this page:** 10\ **Page:** 1
**Author:** ![pascal](https://avatars.discourse-cdn.com/v4/letter/p/e9a140/32.png) [@pascal](https://forum.inductiveautomation.com/u/pascal)\ **Post date:** [October 16, 2017, 2:45pm UTC](https://forum.inductiveautomation.com/t/multiple-datasets-join/15680/1 "2017-10-16T14:45:58Z")
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. ```auto 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 ? ---
**Author:** ![lrose](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/lrose/32/62724_2.png) [@lrose](https://forum.inductiveautomation.com/u/lrose)\ **Post date:** [October 16, 2017, 2:58pm UTC](https://forum.inductiveautomation.com/t/multiple-datasets-join/15680/2 "2017-10-16T14:58:40Z")
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. ---
**Author:** ![nmudge](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/nmudge/32/3394_2.png) [@nmudge](https://forum.inductiveautomation.com/u/nmudge)\ **Post date:** [October 16, 2017, 3:29pm UTC](https://forum.inductiveautomation.com/t/multiple-datasets-join/15680/3 "2017-10-16T15:29:23Z")
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](http://doc.perfectabstractions.com/modules/powerscripting/mutablepydataset.html) from the [Power Scripting module](http://www.perfectabstractions.com/pa-power-scripting-module.html). Here is an example of doing it with MutablePyDataSets: ```auto 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: ```auto #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 """ ``` ---
**Author:** ![cmisztur2](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/cmisztur2/32/4243_2.png) [@cmisztur2](https://forum.inductiveautomation.com/u/cmisztur2)\ **Post date:** [October 16, 2017, 6:03pm UTC](https://forum.inductiveautomation.com/t/multiple-datasets-join/15680/4 "2017-10-16T18:03:39Z")
> [@lrose](#): > > Datasets and PyDatasets are normally immutable Probably need some sanity checks in here, but this should do.... ```auto 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()](http://files.inductiveautomation.com/sdk/javadoc/ignition79/794-beta1/com/inductiveautomation/ignition/common/BasicDataset.html#setDataDirectly-java.lang.Object:A:A-) instead of just concat. ---
**Author:** ![Kevin.Collins](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/kevin.collins/32/13497_2.png) [@Kevin.Collins](https://forum.inductiveautomation.com/u/Kevin.Collins)\ **Post date:** [October 16, 2017, 7:08pm UTC](https://forum.inductiveautomation.com/t/multiple-datasets-join/15680/5 "2017-10-16T19:08:55Z")
The method below will accept multiple datasets (at least two) and return a unified dataset if the datasets have the same column specifications: ```python 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! ---
**Author:** ![jespinmartin1](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/jespinmartin1/32/57175_2.png) [@jespinmartin1](https://forum.inductiveautomation.com/u/jespinmartin1)\ **Post date:** [February 25, 2021, 6:18pm UTC](https://forum.inductiveautomation.com/t/multiple-datasets-join/15680/7 "2021-02-25T18:18:44Z")
I think he wanted “horizontal” concatenation, what you did was “vertical”, instead you could have used ```auto system.dataset.appendDataset() ``` in a loop ---
**Author:** ![lrose](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/lrose/32/62724_2.png) [@lrose](https://forum.inductiveautomation.com/u/lrose)\ **Post date:** [February 25, 2021, 6:22pm UTC](https://forum.inductiveautomation.com/t/multiple-datasets-join/15680/8 "2021-02-25T18:22:20Z")
Not at the time, system.dataset.appendDataset() did not exist ---
**Author:** ![andrewk](https://avatars.discourse-cdn.com/v4/letter/a/bb73d2/32.png) [@andrewk](https://forum.inductiveautomation.com/u/andrewk)\ **Post date:** [November 21, 2022, 3:43pm UTC](https://forum.inductiveautomation.com/t/multiple-datasets-join/15680/9 "2022-11-21T15:43:06Z")
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? ---
**Author:** ![pturmel](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pturmel/32/3295_2.png) [@pturmel](https://forum.inductiveautomation.com/u/pturmel)\ **Post date:** [November 21, 2022, 5:15pm UTC](https://forum.inductiveautomation.com/t/multiple-datasets-join/15680/10 "2022-11-21T17:15:02Z")
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. ---
**Author:** ![mojoeswa](https://avatars.discourse-cdn.com/v4/letter/m/45deac/32.png) [@mojoeswa](https://forum.inductiveautomation.com/u/mojoeswa)\ **Post date:** [March 9, 2023, 4:38pm UTC](https://forum.inductiveautomation.com/t/multiple-datasets-join/15680/11 "2023-03-09T16:38:29Z")
I created this code, but maybe there is a better way... ```auto # **************************************************************** # Function to join two datasets (horizontal) # **************************************************************** def joinDatasets(data,dataset1, dataset2, datasetAlias): """ Arguments: data: This is a map, whose keys are report data keys (must NOT start with a number) and values should be sequences, maps, scalar values, or datasets to provide information to the report. dataset1: First dataset dataset2: Second dataset """ # Get datasets and parse to pydatasets pyDataset1 = system.dataset.toPyDataSet(dataset1) pyDataset2 = system.dataset.toPyDataSet(dataset2) # Build header and code string with unicode headerDataset1 = pyDataset1.getColumnNames() headerDataset2 = pyDataset2.getColumnNames() header = [] for item in headerDataset1: header.append( u"{0}".format(str(item))) for item in (headerDataset2): header.append( u"{0}".format(str(item))) # Convert from PyRow to list newListDataset1 = [list(item) for item in pyDataset1] newListDataset2 = [list(item) for item in pyDataset2] # Zip lists rowzip = zip(newListDataset1,newListDataset2) newRows = [item[0]+item[1] for item in rowzip] # convert the pydataset to a standard dataset rows = system.dataset.toDataSet(header, newRows) # create a new data source data[datasetAlias] = rows ```