Is there a way to sort a dataset on two different columns. The data in the dataset is the result of a SQL query. Lets say i have a dateset with 10 columns. I have a column that has a flag that is set for example either UP or Down. Another column has a Sequence number that is unique for the two different types. So in the data I have 10 UPs and 10 Downs for a total of 20 rows. I want to sort first on the Type and get all my UPs, then within each type I want to sort by the sequence number so i end up with the first half of the table listing the UPs in order 1, 2,3, and the second half of the table listing the Downs, 1, 2, 3.
I am suspecting I might have to run a query to get all the UPs and Order by Sequence, then run a query to get all the Downs and order by Sequence, then combine the tables without mixing the data and the sorts. Is either of these two options possible?
I could see a couple ways to do it. If your doing a query to create the dataset Iâd just do and âOrder Byâ in the query. In there you can do both at once and gets rid of the extra step.
If for some reason you canât do it in the query you can use either the expression sortDataset twice or you can use the script function system.dataset.sort(). Again you would have to run it twice. Iâm pretty sure you will need to order it by your sequence number first then by your Upâs and downâs to get it to show how you want it to.
If for some reason you decide you want to do what you mentioned last, you can use system.dataset.appendDataset() to combine the datasets. It should leave them how you put them together, it would just combine the two by putting the second dataset at the bottom.
The view() expression function from my Simulation Aids module can do this.
view("SELECT * ORDER BY someColumn, anotherColumn",
{Root Container.sourceData})
If youâre displaying the results in a PowerTable; sort the type column and then when holding ctrl click to sort on the sequence column header
Decided to go with running two queries into separate datasets, then appending them together. This orders them within the groups, then orders the groups. One time table setup that then will be processed by the operators for a few days, working down the table from top to bottom. We also add a column for âCompleteâ not contained in the original SQL data so we can track the progress. I can post this simple code if anyone wants it.
I just wanted to respond here with a solution to the problem using Python scripting, with the Python sorted() function. The below function, pydsort, can take a dataset and sort it across multiple columns and provide the intuitive sort results.
import time
import operator
import functools
from com.inductiveautomation.ignition.common import BasicDataset
# helper function just to print out sorted datasets
def print_dataset(d):
py=system.dataset.toPyDataSet(d)
print('\t'.join(py.getColumnNames()))
print('-'*30)
for i in range(py.rowCount):
print('\t'.join(py[i]))
# ignition sorted dataset func, with loop for multiple columns
def ignsort(dataset, order_by):
for oby in order_by:
dataset = system.dataset.sort(dataset, *oby)
return dataset
# python sort for multiple columns
def pydsort(dataset, order_by):
def row_sort_cmp(left, right, order_by):
for icol, is_asc in order_by:
order = operator.gt(left[icol], right[icol]) - \
operator.lt(left[icol], right[icol])
if order != 0:
pol = 1 if is_asc else -1
return order * pol
return 0
cols = list(dataset.getColumnNames())
icols = tuple((cols.index(c), o) for c, o in order_by)
rows = tuple(tuple(dataset.getValueAt(i, c) for c in cols) for i in range(dataset.rowCount))
cmp_func = functools.partial(row_sort_cmp, order_by=icols)
return system.dataset.toDataSet(cols, sorted(rows, cmp=cmp_func))
# create sample dataset
cols = ['col_a', 'col_b', 'col_c']
rows = [
["1","-1","abc"]
,["1","2","aab"]
,["1","2","aaa"]
,["2","3","b"]
,["2","4","b"]
,["2","5","aba"]
,["3","6","a"]
,["3","6","b"]
,["3","6","c"]
,["6","3","a"]
,["5","3","a"]
,["5","3","b"]
,["4","1","a"]
]
dataset = system.dataset.toDataSet(cols, rows)
# column sort order: (Column Name/Number, Is Ascending boolean)
order_by = [
('col_a', True),
('col_b', False),
('col_c', True)
]
ignds = ignsort(dataset, order_by)
print_dataset(ignds)
pyds = pydsort(dataset, order_by)
print_dataset(pyds)
"""
Results:
col_a col_b col_c
------------------------------
3 6 a
5 3 a
6 3 a
4 1 a
1 2 aaa
1 2 aab
2 5 aba
1 -1 abc
3 6 b
2 4 b
2 3 b
5 3 b
3 6 c
col_a col_b col_c
------------------------------
1 2 aaa
1 2 aab
1 -1 abc
2 5 aba
2 4 b
2 3 b
3 6 a
3 6 b
3 6 c
4 1 a
5 3 a
5 3 b
6 3 a
You can see from the results, that the Python version sorted across multiple columns and provided the same results you would expect from a SQL order by clause. The Ignition sort results are not consistent with what you would get from SQL.
"""
The solution above works, but you may have to handle a large amount of data and that could be a problem. Taking the same dataset and progressively duplicating and sorting, we can see the Python solution always slower than the Ignition version.
row_mults = [1, 5, 10, 100, 1000, 10000, 25000, 50000, 100000]
times = []
print('\t'.join(['Row Count', 'Ignition Sort', 'Python Sort', 'Python/Ignition']))
for row_mult in row_mults:
rcount = len(rows) * row_mult
dataset = system.dataset.toDataSet(cols, rows * row_mult)
s=time.time()
ignds = ignsort(dataset, order_by)
ign_secs = time.time() - s
s=time.time()
pyds = pydsort(dataset, order_by)
py_secs = time.time() - s
py_ign_mult = 1
if ign_secs > 0:
py_ign_mult = py_secs / ign_secs
print('\t'.join(map(str, [rcount, ign_secs, py_secs, py_ign_mult])))
"""
|Row Count|Ignition Sort|Python Sort|Python/Ignition|
|13|0.0|0.000999927520752|1|
|65|0.0|0.00500011444092|1|
|130|0.000999927520752|0.00600004196167|6.00047687172|
|1300|0.00399994850159|0.0250000953674|6.25010430947|
|13000|0.0339999198914|0.147000074387|4.32354178646|
|130000|0.394000053406|0.957000017166|2.42893372448|
|325000|0.994999885559|2.77200007439|2.78593004343|
|650000|1.56300020218|5.45999979973|3.49328156971|
|1300000|8.10800004005|11.0249998569|1.35976810588|
"""
If you can, sort the data in SQL, especially when your record sets are >=100,000 records. That said, this option is available for when you need to modifying the data or combine data in a client which is not available in the database.
Iâm confused: In your Ignition sort routine you repeatedly call system.dataset.sort(), which only sorts on one column, replacing the dataset each time through the loop. Why would you think each call would retain any prior sort condition? But you note in a comment that it doesnât appear to match what SQL would yield, so what is the point of comparing a non-working Ignition sort to your python multi-column sort?
From what @bpreston said, it sounded like he was suggesting to call system.dataset.sort() twice:
In any case, the python code for sorting in a script by multiple columns is still valid.
A, missed @bprestonâs suggestion. Itâs wrong. Calling system.dataset.sort() twice doesnât provide any form of chained sorting. The sort from the first call is discarded pseudo-randomly. If you want to sort quickly on multiple columns, make a python list of all the rows, then supply a key function or comparator function to pythonâs built-in list sort method. (Key function is faster than comparator function, but canât do mixed order reversal.)
@pturmel When I suggested it, I was going off a simple test like what I show below. For the first results, the second sort was commented out. The second shows them both in. I didnât notice anything that was lost from the first sort with the second. The first sort placed the cities in order and the second then kept the city order but grouped them by timezone in order.
Is there something Iâm missing?
I donât think that is a guaranteed behavior. It isnât documented to do so, and your typical high-performance algorithms wonât do so.
@pturmel, this is something I would be interested in learning more about: based on my previous question⌠Hereâs the scheme: tags in c_name Folder/desk_name Folder/fp_name Tags
The browseTags() actually returns 3215 rows in a 3 col memory tag dataset, which is not exactly what I want to do but, I am getting somewhere in my learning curve. I need help! I need to 1) create a memory dataset tag named âFP Listâ in each c_name folder/desk_name subfolder. 2) create a memory dataset tag named âDesk Listâ in each c_name folder. 3) create a memory dataset tag named âC Listâ under the root, âSchafer/â in this caseâŚ
The following is what Iâve been able to accomplish so farâŚ
# Dataset memory tag configuration properties
dataType='DataSet'
tagType='AtomicTag'
valueSource='memory'
# Dataset memory tag configuration list
config={'valueSource':valueSource,'dataType':dataType,'name':'FP List','tagType':tagType}
data=[]
def browseTags(path,filter):
results=system.tag.browse(path,filter)
for result in results.getResults():
if result['hasChildren']==True and str(result['tagType'])!='Folder':
fullPath=str(result['fullPath'])
splitPath=fullPath.split('/')
c_name=splitPath[1]
desk_name=splitPath[2]
fp_name=splitPath[3]
data.append([c_name,desk_name,fp_name])
# print c_name,desk_name,fp_name
elif str(result['tagType'])=='Folder':
browseTags(str(result['fullPath']),filter)
# print c_name,desk_name,fp_name
# data.append([c_name,desk_name,fp_name])
system.tag.configure('Schafer',[config],'o')
header=['c_name','desk_name','fp_name']
table=system.dataset.toDataSet(header,data)
system.tag.write('Schafer/FP List',table)
browseTags('Schafer',{})
Iâm making a Data Instance that has a data set within it.
When I try to use an Expression tag within my Data instance This is not working.
Any suggestions? Presort Data is created From Pyton Script
Error messages? Wrong results? How is it not working? (Did you install my module?)
I have the Module installed and have used it many times.
It seems like the Expression Tag i created within a Tag Data type does not hold the data it finds.
Even a basic view(âSELECT ITEM_NOâ,{[.]PresortHealthData}) returns 0 Row when there should be 46.
Note: Presort Data was created From Script and placed into that Memory Tag
Ah, you are experiencing the one unavoidable negative behavior of Transient Datasetsâthey donât transfer from gateway to client or designer. What version of Ignition is this? If v7.9, you can wrap the view() function in a toDataSet() function to make it non-transient. This doesnât work in v8+.
(If you operate on the data entirely in Gateway scope, itâll be visible there.)
Thank you...I'll try the toDataSet(). I used View on my power table and made a custom property to hold Tag Data.