Transpose Rows and Columns

Ignition
1

I have a table component displaying daily flow totals on each day of the week. It executes an sql query which returns a dataset with each item name as a column and each day as a row. For display purposes I’d like to transpose the rows and columns so that the days are displayed along the top and item names along the side.

I’ve been trying to accomplish this with scripting. It seems such a simple operation but it’s proving much more difficult than I thought. Is it actually possible or does this just contradict the purpose of the table?

*In case you’re interested in what I’ve already done: I converted the dataset to a pyDataSet and transposed it using some of the techniques suggested here. The resulting PyDataSet seems fine but refuses to be converted back into a standard ignition dataset so I can’t display it.

2

I have something that could be massaged into what you need. The follow code is designed to take one or more datasets, each with a just a few rows, and flip all the rows into columns. Should be close to what you need:


#----------
# Construct a dataset with columns from supplied datasets rows.  The column
# names from the first dataset become the string values in the first column
# of the result.  The first argument to the function is a sequence of names
# for the dataset arguments.  The name string is used verbatim if the
# dataset has one row.  Otherwise it is concatenated with the row number.
def flipData(names, *args):
	from com.inductiveautomation.ignition.common.util import DatasetBuilder
	from java.lang import String as jString, Object as jObject, Long as jLong
	ds1 = args[0]
	firstcolumn = [x for x in ds1.columnNames]
	types = [jString]
	heads = ['_']
	columns = [firstcolumn]
	for dsn, ds in enumerate(args):
		collist = range(ds.columnCount)
		if ds.rowCount==0:
			heads.append(names[dsn])
			types.append(jObject)
			columns.append([None for x in firstcolumn])
		for rn in range(ds.rowCount):
			if rn==0:
				heads.append(names[dsn])
			else:
				heads.append("%s %d" % (names[dsn], 1+rn))
			types.append(jObject)
			row = [ds.getValueAt(rn, cn) for cn in collist]
			for i, x in enumerate(row):
				if isinstance(x, long):
					row[i] = jLong(x)
			columns.append(row)
	rows = zip(*tuple(columns))
	builder = DatasetBuilder.newBuilder().colNames(heads).colTypes(types)
	for row in rows:
		t = tuple(row)
		builder.addRow(*t)
	return builder.build()
3 Likes
3

Thanks Phil. I’ll give this a try.

4

I wrestled with this myself yesterday. If the data type of the columns in the original DataSet are identical, the following code works:

dataset = system.dataset.toDataSet(["col1","col2"], [[1,4],[2,5],[3,6]])
dataset.rowCount
dataset.columnCount
matrix = system.dataset.toPyDataSet(dataset)
headers = ['row%s'%row for row in range(len(matrix))]
transpose = [[matrix[row][col] for row in range(len(matrix))] for col in range(len(matrix[0]))] # nested list comprehension
print 'matrix: ', str(matrix)
print 'header: %s'% headers
print 'transpose: ', str(transpose)
ds = system.dataset.toDataSet(headers,transpose)
ds.rowCount
ds.columnCount

If the original matrix has dissimilar column types, then one must coerce the data eg. into a string:

dataset = system.dataset.toDataSet(["int","str"], [[1,'a'],[2,'b'],[3,'c']])
dataset.rowCount
dataset.columnCount
matrix = system.dataset.toPyDataSet(dataset)
transpose = [['%s'%matrix[row][col] for row in range(len(matrix))] for col in range(len(matrix[0]))]
print 'matrix: ', str(matrix)
print 'transpose: ', str(transpose)
ds = system.dataset.toDataSet(transpose[0],transpose)
ds.rowCount
ds.columnCount

For my purpose, I packaged this basic code up in a component script (inside a template), essentially:

def transpose(self, ds):
	matrix = system.dataset.toPyDataSet(ds)
	header = ['row%s'%(1+row) for row in range(len(matrix))]
	transpose = [['%s'%matrix[row][col] for row in range(len(matrix))] for col in range(len(matrix[0]))]
	return system.dataset.toDataSet(header,transpose)
2 Likes
5

You’ll notice my script uses the DatasetBuilder class, with which you can explicitly identify your actual column data types (as Java types), or use the generic java.lang.Object to accept any value. The script function system.dataset.toDataSet() uses the builder under the hood, but must attempt to figure out which column types to use. Explicit construction is more reliable in weird cases like this.

6

Like most things with datasets, it is easier to do it with the MutablePyDataSet from the Power Scripting Module.

Here’s how to transpose rows and columns of a dataset using the Power Scripting Module:

a = system.dataset.toDataSet(["one","two","three"], [["first","second","third"],["fourth","fifth","sixth"]])
v = pa.dataset.toData()
for index,row in enumerate(system.dataset.toPyDataSet(a)):
	v.insertColumn(index,"row %s"% index,row)

print v
Output:
row | row 0  row 1
------------------
 0  | first  fourth
 1  | second fifth 
 2  | third  sixth
1 Like
8

Forgive me I'm fairly new to Ignition and I have a named query in a table with 2 rows and 25 columns which I need to transpose. first column contains datetime the rest are integers. the datetime need to be the new headers or at least at the top. how do I use this script?