# Add 2 dataset values and put in single dataset **URL:** \ **Category:** Ignition\ **Created:** [November 23, 2021, 11:04am UTC](https://forum.inductiveautomation.com/t/add-2-dataset-values-and-put-in-single-dataset/53382 "2021-11-23T11:04:49Z")\ **Posts on this page:** 20\ **Page:** 1
**Author:** ![prasath.t](https://avatars.discourse-cdn.com/v4/letter/p/898d66/32.png) [@prasath.t](https://forum.inductiveautomation.com/u/prasath.t)\ **Post date:** [November 23, 2021, 11:04am UTC](https://forum.inductiveautomation.com/t/add-2-dataset-values-and-put-in-single-dataset/53382/1 "2021-11-23T11:04:49Z")
Hi , I have 2 dataset having same number of rows and column first dataset [1R\*4C] values are 10,20,30,40 second dataset [1R\*4C] - same number of rows and column values are 60,70,80,90 I want to add 2 dataset corresponding row values and put in another single dataset how to achieve this? ---
**Author:** ![victordcq](https://avatars.discourse-cdn.com/v4/letter/v/f6c823/32.png) [@victordcq](https://forum.inductiveautomation.com/u/victordcq)\ **Post date:** [November 23, 2021, 11:24am UTC](https://forum.inductiveautomation.com/t/add-2-dataset-values-and-put-in-single-dataset/53382/2 "2021-11-23T11:24:18Z")
1R8C? or 2R4C ? ---
**Author:** ![prasath.t](https://avatars.discourse-cdn.com/v4/letter/p/898d66/32.png) [@prasath.t](https://forum.inductiveautomation.com/u/prasath.t)\ **Post date:** [November 23, 2021, 11:25am UTC](https://forum.inductiveautomation.com/t/add-2-dataset-values-and-put-in-single-dataset/53382/3 "2021-11-23T11:25:59Z")
i want **1R4C** i wan to add corresponding row first dataset [1R\*4C] values are 10,20,30,40 second dataset [1R\*4C] - same number of rows and column values are 60,70,80,90 output of single row **1R4C** 70,90,110,130 ---
**Author:** ![victordcq](https://avatars.discourse-cdn.com/v4/letter/v/f6c823/32.png) [@victordcq](https://forum.inductiveautomation.com/u/victordcq)\ **Post date:** [November 23, 2021, 11:37am UTC](https://forum.inductiveautomation.com/t/add-2-dataset-values-and-put-in-single-dataset/53382/4 "2021-11-23T11:37:14Z")
![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/9/8/98bb3b2a528280554fe512478ad58f49f1fe4946.png) ```auto d1 = system.dataset.toDataSet(["1","2","3","4"],[[10,20,30,40]]) d2 = system.dataset.toDataSet(["1","2","3","4"],[[60,70,80,90]]) d3 = system.dataset.toDataSet(["1","2","3","4"],[[0,0,0,0]]) for row in range(d1.getRowCount()): for col in range(d1.getColumnCount()): sum = d1.getValueAt(row, col) + d2.getValueAt(row, col) d3 = system.dataset.setValue(d3,row,col,sum) ``` ---
**Author:** ![prasath.t](https://avatars.discourse-cdn.com/v4/letter/p/898d66/32.png) [@prasath.t](https://forum.inductiveautomation.com/u/prasath.t)\ **Post date:** [November 23, 2021, 11:49am UTC](https://forum.inductiveautomation.com/t/add-2-dataset-values-and-put-in-single-dataset/53382/5 "2021-11-23T11:49:52Z")
One doubt i am getting 2 dataset from database by query first column of dataset containing date 23-11-2011 second dataset also having same date in first column 23-11-2011 i don’t want to add the date column… i want to add only the other column is it possible to do? ---
**Author:** ![victordcq](https://avatars.discourse-cdn.com/v4/letter/v/f6c823/32.png) [@victordcq](https://forum.inductiveautomation.com/u/victordcq)\ **Post date:** [November 23, 2021, 11:53am UTC](https://forum.inductiveautomation.com/t/add-2-dataset-values-and-put-in-single-dataset/53382/6 "2021-11-23T11:53:12Z")
sure just put an if on the col to check if its that name or even exclude it from the range immediatly ![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/8/8/888fef63b920bc318a2cc72ccf1a6c126e20734c.png) ```auto d1 = system.dataset.toDataSet(["1","2","3","4"],[[10,20,30,40]]) d2 = system.dataset.toDataSet(["1","2","3","4"],[[60,70,80,90]]) d3 = system.dataset.toDataSet(["1","2","3","4"],[[0,0,0,0]]) for row in range(d1.getRowCount()): for col in d1.getColumnNames(): if col != "2": sum = d1.getValueAt(row, col) + d2.getValueAt(row, col) d3 = system.dataset.setValue(d3,row,col,sum) ``` ---
**Author:** ![prasath.t](https://avatars.discourse-cdn.com/v4/letter/p/898d66/32.png) [@prasath.t](https://forum.inductiveautomation.com/u/prasath.t)\ **Post date:** [November 23, 2021, 12:00pm UTC](https://forum.inductiveautomation.com/t/add-2-dataset-values-and-put-in-single-dataset/53382/7 "2021-11-23T12:00:10Z")
thank you so much ---
**Author:** ![prasath.t](https://avatars.discourse-cdn.com/v4/letter/p/898d66/32.png) [@prasath.t](https://forum.inductiveautomation.com/u/prasath.t)\ **Post date:** [November 23, 2021, 12:18pm UTC](https://forum.inductiveautomation.com/t/add-2-dataset-values-and-put-in-single-dataset/53382/8 "2021-11-23T12:18:46Z")
```auto endTime = system.date.now() startTime = system.date.addMinutes(endTime, -30) dataSet = system.tag.queryTagHistory(paths=['[MQTT Engine]Edge Nodes/Montreal/Energy/Electric/Total_Consumption/Consumption_kW'], startDate=startTime, endDate=endTime, returnSize=1, aggregationMode="Maximum", returnFormat='Wide') d1 = dataSet d2 = dataSet d3 = system.dataset.toDataSet(["1","2"],[[0,0]]) for row in range(d1.getRowCount()): for col in d1.getColumnNames(): if col != "t_stamp": print d1.getValueAt(row, col) print d2.getValueAt(row, col) sum = d1.getValueAt(row, col) + d2.getValueAt(row, col) #print sum d3 = system.dataset.setValue(d3,row,col,sum) ``` ```auto Traceback (most recent call last): File "", line 20, in IndexError: Column "Edge Nodes/Montreal/Energy/Electric/Total_Consumption/Consumption_kW" doesn't exist. ``` getting this error … because for d3 dataset i am hard coding the header name… how to pass dynamically the header . is it possible to add ds2 to d1 dataset itself instead of putting in to another dataset? can you suggest me best solution ---
**Author:** ![victordcq](https://avatars.discourse-cdn.com/v4/letter/v/f6c823/32.png) [@victordcq](https://forum.inductiveautomation.com/u/victordcq)\ **Post date:** [November 23, 2021, 12:28pm UTC](https://forum.inductiveautomation.com/t/add-2-dataset-values-and-put-in-single-dataset/53382/9 "2021-11-23T12:28:05Z")
> [@prasath.t](#): > > `d1.getColumnNames()` d3 = system.dataset.toDataSet(d1.getColumnNames().toArray(),[[0] \* d1.getColumnCount()]) ---
**Author:** ![JordanCClark](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/jordancclark/32/83362_2.png) [@JordanCClark](https://forum.inductiveautomation.com/u/JordanCClark)\ **Post date:** [November 23, 2021, 5:50pm UTC](https://forum.inductiveautomation.com/t/add-2-dataset-values-and-put-in-single-dataset/53382/10 "2021-11-23T17:50:18Z")
PyDataSets can help alleviate some of the looping. EDIT: added Nonetype to the allowed types and a coalesce function. ```python # Sample input DataSets d1 = system.dataset.toDataSet(["1","2","3","4"],[[system.date.parse('2021-11-23 00:00:00'),'String1',30,40]]) d2 = system.dataset.toDataSet(["1","2","3","4"],[[system.date.parse('2021-11-28 00:00:00'),'String2',80,90]]) # ------------------------------------------------------------------------------------------------------------ def coalesce(*values): """Return the first non-None value or None if all values are None""" return next((v for v in values if v is not None), None) # Define allowed types to add allowedTypes = [int, float, long, type(None)] # Create PyDataSets out of our input data pyDs1 = system.dataset.toPyDataSet(d1) pyDs2 = system.dataset.toPyDataSet(d2) dataOut = [] # PyDatasets let us use slices. zip() lets us iterate through both dataset rows at once. for i,j in zip(pyDs1[0], pyDs2[0]): # check if data types are numeric if type(i) in allowedTypes and type(j) in allowedTypes: dataOut.append(coalesce(i,0)+coalesce(j,0)) # otherwise, use value from first dataset else: dataOut.append(i) # Get column names from the first dataset colNames = list(d1.getColumnNames()) # Create summed dataset d3 = system.dataset.toDataSet(colNames, [dataOut]) ``` output dataset: ```nohighlight row | 1 | 2 | 3 | 4 -------------------------------------------------------- 0 | Tue Nov 23 00:00:00 EST 2021 | String1 | 110 | 130 ``` ---
**Author:** ![prasath.t](https://avatars.discourse-cdn.com/v4/letter/p/898d66/32.png) [@prasath.t](https://forum.inductiveautomation.com/u/prasath.t)\ **Post date:** [November 24, 2021, 3:52am UTC](https://forum.inductiveautomation.com/t/add-2-dataset-values-and-put-in-single-dataset/53382/11 "2021-11-24T03:52:48Z")
Hi @JordanCClark @victordcq ```auto # Sample input DataSets d1 = system.dataset.toDataSet(["1","2","3","4"],[[system.date.parse('2021-11-23 00:00:00'),'String1','none',40]]) d2 = system.dataset.toDataSet(["1","2","3","4"],[[system.date.parse('2021-11-28 00:00:00'),'String2',34,90]]) d3 = system.dataset.toDataSet(["1","2","3","4"],[[system.date.parse('2021-11-28 00:00:00'),'String2',98,90]]) d4 = system.dataset.toDataSet(["1","2","3","4"],[[system.date.parse('2021-11-28 00:00:00'),'String2',98,'none']]) ``` sometimes when i query the data for database i am getting none values i some of the integer column also ```auto > # otherwise, use value from first dataset > else: > dataOut.append(i) ``` As we are hard coding it to get value from d1 dataset so if i get **none value in any one of the integer column of dataset 1** … other dataset have integer values … but i am **getting output as none as for that column…** i how to write this script more dynamically ? for your example i have attached you the sample datasets how my dataset i am getting while quering ---
**Author:** ![prasath.t](https://avatars.discourse-cdn.com/v4/letter/p/898d66/32.png) [@prasath.t](https://forum.inductiveautomation.com/u/prasath.t)\ **Post date:** [November 24, 2021, 3:55am UTC](https://forum.inductiveautomation.com/t/add-2-dataset-values-and-put-in-single-dataset/53382/12 "2021-11-24T03:55:11Z")
Hi @JordanCClark @victordcq ```auto # Sample input DataSets d1 = system.dataset.toDataSet(["1","2","3","4"],[[system.date.parse('2021-11-23 00:00:00'),'String1',none,40,none]]) d2 = system.dataset.toDataSet(["1","2","3","4"],[[system.date.parse('2021-11-28 00:00:00'),'String2',34,90,none]]) d3 = system.dataset.toDataSet(["1","2","3","4"],[[system.date.parse('2021-11-28 00:00:00'),'String2',98,90,none]]) d4 = system.dataset.toDataSet(["1","2","3","4"],[[system.date.parse('2021-11-28 00:00:00'),'String2',98,none,none]]) ``` sometimes when i query the data form database i am getting none values for the integer column also ```auto > # otherwise, use value from first dataset > else: > dataOut.append(i) ``` As we are hard coding it to get value from d1 dataset so if i get **none value in any one of the integer column of dataset 1** … **even other dataset have integer values for the same column** … but i am **getting output as none as for that column…** if all the row of single column have none value that time only it should return none. how to write this script more dynamically ? for your example i have attached you the sample datasets how my dataset i am getting while querying ---
**Author:** ![JordanCClark](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/jordancclark/32/83362_2.png) [@JordanCClark](https://forum.inductiveautomation.com/u/JordanCClark)\ **Post date:** [November 24, 2021, 4:32am UTC](https://forum.inductiveautomation.com/t/add-2-dataset-values-and-put-in-single-dataset/53382/13 "2021-11-24T04:32:14Z")
Edited the above script to add NoneType and a coalesce function. ---
**Author:** ![prasath.t](https://avatars.discourse-cdn.com/v4/letter/p/898d66/32.png) [@prasath.t](https://forum.inductiveautomation.com/u/prasath.t)\ **Post date:** [November 24, 2021, 4:44am UTC](https://forum.inductiveautomation.com/t/add-2-dataset-values-and-put-in-single-dataset/53382/14 "2021-11-24T04:44:02Z")
thanks @JordanCClark ---
**Author:** ![prasath.t](https://avatars.discourse-cdn.com/v4/letter/p/898d66/32.png) [@prasath.t](https://forum.inductiveautomation.com/u/prasath.t)\ **Post date:** [November 24, 2021, 7:17am UTC](https://forum.inductiveautomation.com/t/add-2-dataset-values-and-put-in-single-dataset/53382/15 "2021-11-24T07:17:22Z")
Hi @JordanCClark I am querying multiple tags at same time(multiple tags selection varies for each time), **so if difficult for me to use multiple dataset and add** it what i am thinking is **add single dataset for all the query** for each query **i will append value to single dataset** … that’s will be best for me ```auto '2021-11-23 00:00:00' 'String1' 'none' 40 '2021-11-23 00:00:00' 'String1' 'none' 40 '2021-11-23 00:00:00' 'String1' 'none' 40 ``` **3R1C** **time will be same for all the rows** … so i want to add corresponding rows of single dataset and put in new dataset so new dataset will be **1R4C** so it will be more dynamic and easy to implement for me **is it possible to edit in your script ?** ---
**Author:** ![victordcq](https://avatars.discourse-cdn.com/v4/letter/v/f6c823/32.png) [@victordcq](https://forum.inductiveautomation.com/u/victordcq)\ **Post date:** [November 24, 2021, 9:23am UTC](https://forum.inductiveautomation.com/t/add-2-dataset-values-and-put-in-single-dataset/53382/16 "2021-11-24T09:23:20Z")
[https://docs.inductiveautomation.com/display/DOC81/system.dataset.addColumn](https://docs.inductiveautomation.com/display/DOC81/system.dataset.addColumn) ---
**Author:** ![prasath.t](https://avatars.discourse-cdn.com/v4/letter/p/898d66/32.png) [@prasath.t](https://forum.inductiveautomation.com/u/prasath.t)\ **Post date:** [November 24, 2021, 2:02pm UTC](https://forum.inductiveautomation.com/t/add-2-dataset-values-and-put-in-single-dataset/53382/17 "2021-11-24T14:02:29Z")
This script helps to append a new column to dataset but i am try to add rows values each corresponding rows and put it in single dataset ---
**Author:** ![JordanCClark](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/jordancclark/32/83362_2.png) [@JordanCClark](https://forum.inductiveautomation.com/u/JordanCClark)\ **Post date:** [November 24, 2021, 2:13pm UTC](https://forum.inductiveautomation.com/t/add-2-dataset-values-and-put-in-single-dataset/53382/18 "2021-11-24T14:13:17Z")
Can you show how you are getting your values now (Maybe some sample values would help)? It will help give you a better solution. ---
**Author:** ![prasath.t](https://avatars.discourse-cdn.com/v4/letter/p/898d66/32.png) [@prasath.t](https://forum.inductiveautomation.com/u/prasath.t)\ **Post date:** [November 24, 2021, 2:19pm UTC](https://forum.inductiveautomation.com/t/add-2-dataset-values-and-put-in-single-dataset/53382/19 "2021-11-24T14:19:21Z")
your previous script works fine but we hard coding to add only 2 dataset… if we want to add 3 dataset i want to use script again… what i am doing is using **system.tag.query** in for loop… if operator select 3 tags and run the code query will run for 3 times … i am append the each query value to single dataset **data = system.dataset.appendDataset(data, dataSet) - appending like this** after the for loop runs i want to add the each row of corresponding column and pass the added data to new dataset so i will get **1R only**** first column will be date column (date will be same for all rows)** ```auto **'2021-11-23 00:00:00' 23 40** **'2021-11-23 00:00:00' 24 90** **'2021-11-23 00:00:00' 89 none** i want to add like this date elec gas (column names for example) 2021-11-23 00:00:00 136 130 - this is the output i want ``` this is the sample value in single dataset. i have run query 3 times and append to a dataset. **note my first column have date and other column are only have real values(but some row of real value will get none)** now i want to add it and make it as single row and pass it to new dataset whether my explanation is fine or you need more input? ---
**Author:** ![victordcq](https://avatars.discourse-cdn.com/v4/letter/v/f6c823/32.png) [@victordcq](https://forum.inductiveautomation.com/u/victordcq)\ **Post date:** [November 24, 2021, 3:01pm UTC](https://forum.inductiveautomation.com/t/add-2-dataset-values-and-put-in-single-dataset/53382/20 "2021-11-24T15:01:34Z")
seems like it might easier to first make just one dataset with all the rows and then merge them back to one row with the relevent columns summed up. data.getColumnAsList(colIndex) would be usefull for this [https://docs.inductiveautomation.com/display/DOC81/system.dataset.addRow](https://docs.inductiveautomation.com/display/DOC81/system.dataset.addRow) add rows for how ever many “single row” dataset you got then you can create a new one How you you get all the selected tags? ![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/0/e/0ee8a13d94b37c44727eea23453bedd62a129531.png) you just gotta put all the selected datasets in the array somehow as pydataset ```auto d1 = system.dataset.toDataSet(["1","2","3","4"],[[10,20,30,40]]) d2 = system.dataset.toDataSet(["1","2","3","4"],[[60,70,80,90]]) pyDs1 = system.dataset.toPyDataSet(d1) pyDs2 = system.dataset.toPyDataSet(d2) selectedDatasets =[pyDs1,pyDs2] d3 = system.dataset.toDataSet(d1.getColumnNames().toArray(),[[0] * d1.getColumnCount()]) AllRow = system.dataset.toDataSet(d1.getColumnNames().toArray(),[]) for x in selectedDatasets: AllRow = system.dataset.addRow(AllRow, x[0]) for col in AllRow.getColumnNames(): if col != "2": columnData = [int(unicode or 0) for unicode in AllRow.getColumnAsList(AllRow.getColumnIndex(col))] d3 = system.dataset.setValue(d3,0,col,sum(columnData)) else: d3 = system.dataset.setValue(d3,0,col,AllRow.getValueAt(0,col)) ``` edit: ah i got the if else switched around xd [Next page](https://forum.inductiveautomation.com/t/add-2-dataset-values-and-put-in-single-dataset/53382.md?page=2)