Rearrange Column Order in dataset

Ignition
1

I have a dataset coming from a query where it is basically a SELECT * from varying different tables, and I can always count on my timestamp column being the same, but it is in a different order in the various tables which means sometimes its the second column in my dataset and something the nth.

I would like to take the dataset and find the timestamp column wherever it may be and fix it to always be the second column and display in a power table (vision) version 8.1.25. I am creating the ds and manipulating it in propertyChange script already so it would be great to have some code to do this.

2

The name is a bit misleading, but you can use the filter columns function to rearrange as well:

https://docs.inductiveautomation.com/display/DOC81/system.dataset.filterColumns

3

This is the right place to also do the re-arranging, so you only do the dataset creation once (it isn't terribly light-weight). Share your code.

1 Like
4

I can't use filter columns because I don't know the column names, and they are different from table to table.

if event.propertyName == 'rawData' or event.propertyName == 'page':
	preview = event.source
	ads = event.source.rawData

	page = event.source.page
	pageRows = event.source.pageRows
	lowRange = pageRows * page
	highRange = (pageRows * page) + pageRows
	
	rowsToDelete = [i for i in range(ads.rowCount) if i not in range(lowRange, highRange)]
	ads = system.dataset.deleteRows(ads, rowsToDelete)
	ads = system.dataset.sort(ads, "TimeStamp", False)
	
	#Need to rearrange the TimeStamp column to be at the beginning
	
	preview.pageData = ads
5

You can build the list of new columns from the dataset's list of columns. Like so, starting with your line 14:

.....
	#Need to rearrange the TimeStamp column to be at the beginning
	colNames = list(ads.columnNames)
	# We don't care where the timestamp column currently is, we're unconditionally moving it.
	colNames.remove('TimeStamp') 
	# New columns are prior first column, timestamp column, then all the rest of the original columns
	newColumns = colNames[:1] + ['TimeStamp'] + colNames[1:]
	ads = system.dataset.filterColumns(ads, newColumns)
	
	preview.pageData = ads
.....
1 Like
6

Wouldn't something like colNames.insert(1, 'TimeStamp') be faster than slicing and adding with newColumns = colNames[:1] + ['TimeStamp'] + colNames[1:]? Even if that's a wash, it's at least more readable to me.

1 Like
7

Yeah, probably better. For some reason, I never think of this method.

1 Like
8

Even if you end up not using this method at all (because there's a better way, as Phil showed you), python allows low < x < hi.

1 Like
9

The code I supplied replaced just three original lines, not including the part you reference. So, still applicable.

10

Didn't read the whole thing :X

11

Thanks Phil and Justin