How do you sort a dataset?

TimE · March 1, 2012, 8:37pm

I find myself stumped again! I have sorted lists before but I am at a loss as to why I cannot do this. The table I am trying to sort has three columns and about 50 rows.

ds = event.source.parent.getComponent('Table').data pyData = system.dataset.toPyDataSet(ds) ds.sort()

This is the error …

[quote]Traceback (innermost last):
File “event:actionPerformed”, line 3, in ?
AttributeError: sort

at org.python.core.Py.AttributeError(Py.java)
at org.python.core.PyInstance.invoke(PyInstance.java)
at org.python.pycode._pyx150.f$0(<event:actionPerformed>:3)
at org.python.pycode._pyx150.call_function(<event:actionPerformed>)
at org.python.core.PyTableCode.call(PyTableCode.java)
at org.python.core.PyCode.call(PyCode.java)
at org.python.core.Py.runCode(Py.java)
at com.inductiveautomation.ignition.common.script.ScriptManager.runCode(ScriptManager.java:395)
at com.inductiveautomation.factorypmi.application.binding.action.ActionAdapter.runActions(ActionAdapter.java:139)
at com.inductiveautomation.factorypmi.application.binding.action.ActionAdapter.invoke(ActionAdapter.java:247)
at com.inductiveautomation.factorypmi.application.binding.action.RelayInvocationHandler.invoke(RelayInvocationHandler.java:55)
at $Proxy6.actionPerformed(Unknown Source)
at javax.swing.AbstractButton.fireActionPerformed(Unknown Source)
at javax.swing.AbstractButton$Handler.actionPerformed(Unknown Source)
at javax.swing.DefaultButtonModel.fireActionPerformed(Unknown Source)
at javax.swing.DefaultButtonModel.setPressed(Unknown Source)
at javax.swing.plaf.basic.BasicButtonListener.mouseReleased(Unknown Source)
at java.awt.Component.processMouseEvent(Unknown Source)
at javax.swing.JComponent.processMouseEvent(Unknown Source)
at java.awt.Component.processEvent(Unknown Source)
at java.awt.Container.processEvent(Unknown Source)
at java.awt.Component.dispatchEventImpl(Unknown Source)
at java.awt.Container.dispatchEventImpl(Unknown Source)
at java.awt.Component.dispatchEvent(Unknown Source)
at java.awt.LightweightDispatcher.retargetMouseEvent(Unknown Source)
at java.awt.LightweightDispatcher.processMouseEvent(Unknown Source)
at java.awt.LightweightDispatcher.dispatchEvent(Unknown Source)
at java.awt.Container.dispatchEventImpl(Unknown Source)
at java.awt.Window.dispatchEventImpl(Unknown Source)
at java.awt.Component.dispatchEvent(Unknown Source)
at java.awt.EventQueue.dispatchEventImpl(Unknown Source)
at java.awt.EventQueue.access$000(Unknown Source)
at java.awt.EventQueue$1.run(Unknown Source)
at java.awt.EventQueue$1.run(Unknown Source)
at java.security.AccessController.doPrivileged(Native Method)
at java.security.AccessControlContext$1.doIntersectionPrivilege(Unknown Source)
at java.security.AccessControlContext$1.doIntersectionPrivilege(Unknown Source)
at java.awt.EventQueue$2.run(Unknown Source)
at java.awt.EventQueue$2.run(Unknown Source)
at java.security.AccessController.doPrivileged(Native Method)
at java.security.AccessControlContext$1.doIntersectionPrivilege(Unknown Source)
at java.awt.EventQueue.dispatchEvent(Unknown Source)
at java.awt.EventDispatchThread.pumpOneEventForFilters(Unknown Source)
at java.awt.EventDispatchThread.pumpEventsForFilter(Unknown Source)
at java.awt.EventDispatchThread.pumpEventsForHierarchy(Unknown Source)
at java.awt.EventDispatchThread.pumpEvents(Unknown Source)
at java.awt.EventDispatchThread.pumpEvents(Unknown Source)
at java.awt.EventDispatchThread.run(Unknown Source)

Ignition v7.3.1 (b496)
Java: Sun Microsystems Inc. 1.6.0_27
[/quote]

What is the proper way to perform a sort?
Thanks,

Kevin.Herron · March 1, 2012, 9:33pm

Yeah, DataSet doesn’t have a sort() method… looks like you need to sort it yourself. Will get back to you with a code snippet…

TimE · March 1, 2012, 9:36pm

Correction to the original post. (I had tried every combination I could think of)

ds = event.source.parent.getComponent('Table').data pyData = system.dataset.toPyDataSet(ds) pyData.sort()

Kevin.Herron · March 1, 2012, 10:30pm

This is a bit ugly… we should really add a scripting function that will sort a DataSet.

headers = ["Column 1", "Column 2"]
data = [['z','foo'],['w','bar'],['a','baz']]
ds = system.dataset.toDataSet(headers, data)

def sortDataSet(ds, key_column = 0):
	# create headers
	headers = [ds.getColumnName(x) for x in range(ds.columnCount-1)]
	
	# create data
	import system
	py_ds = system.dataset.toPyDataSet(ds)
	rows = [py_ds[x] for x in range(len(py_ds))]
	rows.sort(lambda x,y,key=key_column: cmp(x[key], y[key]))
	data = [[row[x] for x in range(len(rows)-1)] for row in rows]
	
	return system.dataset.toDataSet(headers, data)

print sortDataSet(ds)

TimE · March 2, 2012, 8:21pm

I copied your code and it worked in the playground but not when I entered it into my project.

def sortData(ds, key_column = 2): import system headers = [ds.getColumnName(x) for x in range(ds.columnCount)] pyData = system.dataset.toPyDataSet(ds) rows = [pyData[x] for x in range(len(pyData))] rows.sort(lambda x,y,key=key_column: cmp(x[key], y[key])) data = [[row[x] for x in range(len(rows)-1)] for row in rows] return system.dataset.toDataSet(headers, data)

Line 366 is the 'data = ’ statement above.

[quote]Traceback (innermost last):
File “event:actionPerformed”, line 37, in ?
File “module:math”, line 366, in sortData
IndexError: Column index 3 out of range.

at org.python.core.Py.IndexError(Py.java)
at com.inductiveautomation.ignition.common.script.builtin.DatasetUtilities$PyDataSet$PyRow.__getitem__(DatasetUtilities.java:102)
at sun.reflect.GeneratedMethodAccessor97.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
at java.lang.reflect.Method.invoke(Unknown Source)
at org.python.core.PyReflectedFunction.__call__(PyReflectedFunction.java)
at org.python.core.PyMethod.__call__(PyMethod.java)
at org.python.core.PyObject.__call__(PyObject.java)
at org.python.core.PyInstance.invoke(PyInstance.java)
at org.python.core.PyInstance.__getitem__(PyInstance.java)
at org.python.pycode._pyx273.sortData$16(<module:math>:366)
at org.python.pycode._pyx273.call_function(<module:math>)
at org.python.core.PyTableCode.call(PyTableCode.java)
at org.python.core.PyTableCode.call(PyTableCode.java)
at org.python.core.PyTableCode.call(PyTableCode.java)
at org.python.core.PyFunction.__call__(PyFunction.java)
at org.python.core.PyObject.invoke(PyObject.java)
at org.python.pycode._pyx272.f$0(<event:actionPerformed>:37)
at org.python.pycode._pyx272.call_function(<event:actionPerformed>)
at org.python.core.PyTableCode.call(PyTableCode.java)
at org.python.core.PyCode.call(PyCode.java)
at org.python.core.Py.runCode(Py.java)
at com.inductiveautomation.ignition.common.script.ScriptManager.runCode(ScriptManager.java:395)
at com.inductiveautomation.factorypmi.application.binding.action.ActionAdapter.runActions(ActionAdapter.java:139)
at com.inductiveautomation.factorypmi.application.binding.action.ActionAdapter.invoke(ActionAdapter.java:247)
at com.inductiveautomation.factorypmi.application.binding.action.RelayInvocationHandler.invoke(RelayInvocationHandler.java:55)
at $Proxy5.actionPerformed(Unknown Source)
at javax.swing.AbstractButton.fireActionPerformed(Unknown Source)
at javax.swing.AbstractButton$Handler.actionPerformed(Unknown Source)
at javax.swing.DefaultButtonModel.fireActionPerformed(Unknown Source)
at javax.swing.DefaultButtonModel.setPressed(Unknown Source)
at javax.swing.plaf.basic.BasicButtonListener.mouseReleased(Unknown Source)
at java.awt.Component.processMouseEvent(Unknown Source)
at javax.swing.JComponent.processMouseEvent(Unknown Source)
at java.awt.Component.processEvent(Unknown Source)
at java.awt.Container.processEvent(Unknown Source)
at java.awt.Component.dispatchEventImpl(Unknown Source)
at java.awt.Container.dispatchEventImpl(Unknown Source)
at java.awt.Component.dispatchEvent(Unknown Source)
at java.awt.LightweightDispatcher.retargetMouseEvent(Unknown Source)
at java.awt.LightweightDispatcher.processMouseEvent(Unknown Source)
at java.awt.LightweightDispatcher.dispatchEvent(Unknown Source)
at java.awt.Container.dispatchEventImpl(Unknown Source)
at java.awt.Window.dispatchEventImpl(Unknown Source)
at java.awt.Component.dispatchEvent(Unknown Source)
at java.awt.EventQueue.dispatchEventImpl(Unknown Source)
at java.awt.EventQueue.access$000(Unknown Source)
at java.awt.EventQueue$1.run(Unknown Source)
at java.awt.EventQueue$1.run(Unknown Source)
at java.security.AccessController.doPrivileged(Native Method)
at java.security.AccessControlContext$1.doIntersectionPrivilege(Unknown Source)
at java.security.AccessControlContext$1.doIntersectionPrivilege(Unknown Source)
at java.awt.EventQueue$2.run(Unknown Source)
at java.awt.EventQueue$2.run(Unknown Source)
at java.security.AccessController.doPrivileged(Native Method)
at java.security.AccessControlContext$1.doIntersectionPrivilege(Unknown Source)
at java.awt.EventQueue.dispatchEvent(Unknown Source)
at java.awt.EventDispatchThread.pumpOneEventForFilters(Unknown Source)
at java.awt.EventDispatchThread.pumpEventsForFilter(Unknown Source)
at java.awt.EventDispatchThread.pumpEventsForHierarchy(Unknown Source)
at java.awt.EventDispatchThread.pumpEvents(Unknown Source)
at java.awt.EventDispatchThread.pumpEvents(Unknown Source)
at java.awt.EventDispatchThread.run(Unknown Source)

Ignition v7.3.1 (b496)
Java: Sun Microsystems Inc. 1.6.0_27
[/quote]

I attached the data I am working with. I am trying to sort this so I can extract rows for the top 5 heights.
Thanks,
data.txt (2.76 KB)

Colby.Clegg · March 2, 2012, 11:15pm

I think there’s a problem in the script, which you seemed to catch in one place already, where the row count is being used instead of the column count.

I didn’t mock this up, but it looks like on that line you want to loop through all columns for all rows. Instead of “data = [[row[x] for x in range(len(rows)-1)] for row in rows]” I think you want:

data = [[row[x] for x in range(ds.columnCount)] for row in rows]

EDIT:
Here is the final CORRECT version of this code.

[code]headers = [“Column 1”, “Column 2”]
data = [[‘z’,‘foo’],[‘w’,‘bar’],[‘a’,‘baz’]]
ds = system.dataset.toDataSet(headers, data)

def sortDataSet(ds, key_column = 0):

create headers

headers = [ds.getColumnName(x) for x in range(ds.columnCount)]

create data

import system
py_ds = system.dataset.toPyDataSet(ds)
rows = [py_ds[x] for x in range(len(py_ds))]
rows.sort(lambda x,y,key=key_column: cmp(x[key], y[key]))
data = [[row[x] for x in range(ds.columnCount)] for row in rows]

return system.dataset.toDataSet(headers, data)

this uses the Ignition dataset (not a pydataset) that was created in line 3

print sortDataSet(ds)[/code]

Regards,

Kevin.Herron · March 27, 2012, 9:21pm

Scripting and expression functions for sorting a dataset have been added for the 7.4 final release.

juan · November 7, 2019, 4:29pm

in 8.1 the sort method does not sort my dataset

Kevin.Herron · November 7, 2019, 4:30pm

Are you from the future? There is no version 8.1

Show us the code you’re using?

jacque_trahan · November 29, 2023, 5:11pm

This works great, but I was curious.
Is there a way to sort Alphabetically based on the headers? This sort, sorts, exactly as expect based on column index, but it would be amazingly to not have to make a custom script to sort the headers and instead use the ignition built in function. Just would like to make sure I am not missing this somewhere before I make my own function for it.

Docs on the Ignition function I am currently using:
system.dataset.sort

Code example:

	# function to build a dataSet dictionary to update our tagPath
	def buildDataSet(colHeader):
		"""
		♠ Get the headers/Values of a a CSV files to start building out the Dataset Values
		"""
		dataSet = {}
		for key, value in colHeader.items():
			if key not in ['TagPath', 'MeterPath']:
				dataSet[key] = value
		return dataSet

# *Read in a CSV file as a Dict here to get colHeader (left this out for this commit)*

dataSetToWrite = buildDataSet(colHeader)

# create the new dataset
updatedDataset = system.dataset.toDataSet(dataSetToWrite.keys(), [dataSetToWrite.values()])
# sort data set based on column index 0
sortedDataSet = system.dataset.sort(updatedDataset, 0,True)
# write to the current Row's Tagpath to OVERWRITE the old Dataset
system.tag.writeBlocking(tagPath, sortedDataSet)

Kevin.Herron · November 29, 2023, 5:23pm

I don't know what you're asking here. Can you explain and provide an example?

jacque_trahan · November 29, 2023, 6:25pm

Sure!
When I write to the memory tag that has a data set the headers are random.
Example:

Headers: C | D| E | A | B

I would like them to be like this:

Headers: A | B | C | D | E

I may not know which column index starts with, in the above example, "A". So, sorting by column index is a little difficult and would rather a dynamic way to just alphabetize the headers then write the data to the tag.

Kevin.Herron · November 29, 2023, 6:28pm

Okay, you'll have to do that yourself. The built-in sort function is for sorting the rows, not sorting the columns.

Felipe_CRM · November 29, 2023, 6:31pm

sorted_headers = dataset.getColumnNames()
sorted_headers.sort()
column_sorted_dataset = system.dataset.filterColumns(dataset, sorted_headers)

should do what you want.

EDIT: forgot python sorts in place.

PGriffith · November 29, 2023, 6:34pm

You can do that with the (somewhat confusingly named) system.dataset.filterColumns function efficiently.

Well, @Felipe_CRM beat me to it but sorted is also nice to make this a one-liner

ds = system.dataset.toDataSet(["bat", "rat", "cat", "hat"], [])

def sortColumns(ds):
	return system.dataset.filterColumns(ds, sorted(ds.columnNames))

	
print ds.columnNames
print sortColumns(ds).columnNames

Felipe_CRM · November 29, 2023, 6:35pm

Well, I just learned something today with the sorted() function, very nice!

pascal.fragnoud · November 29, 2023, 6:47pm

can't try it, but something like this should do it:

headers = sorted(ds.columnNames)
ds = system.dataset.filterColumns(ds, headers)

edit:
huh, I was late to the party