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?
1R8C?
or
2R4C ?
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
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)
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?
sure just put an if on the col to check if its that name or even exclude it from the range immediatly
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)
thank you so much
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)
Traceback (most recent call last):
File "<input>", line 20, in <module>
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
d3 = system.dataset.toDataSet(d1.getColumnNames().toArray(),[[0] * d1.getColumnCount()])
PyDataSets can help alleviate some of the looping.
EDIT: added Nonetype to the allowed types and a coalesce function.
# 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:
row | 1 | 2 | 3 | 4
--------------------------------------------------------
0 | Tue Nov 23 00:00:00 EST 2021 | String1 | 110 | 130
# 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
> # 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
# 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
> # 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
Edited the above script to add NoneType and a coalesce function.
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
'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 ?
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
Can you show how you are getting your values now (Maybe some sample values would help)? It will help give you a better solution.
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)
**'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?
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
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?
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