Convert this array of data into a dataset

Ignition
1

How can i convert this array into a dataset? I'm trying to use a transform expression to convert the data to a dataset that i can use for a table.
array

Here is the new dataset tag i want it to go to with a transform expression.
expression

Here's what it looks like in that dataset tag.

dataset
dataset1606×404 113 KB

2

I don't think you need to do a transform. Just bind the table props.data to count.
Each item in the list (0, 1, 2, etc.) becomes a table row. Each element in the item becomes a table column.

3

I agree with @Transistor, pretty sure there is no need to convert it.

In fact, you should be able to bind directly to the tag and it be treated just like any dataset object. I know I do this in scripting in a couple of places and I've never had to reconstruct the dataset.

That being said, I don't think there is any expression function that will convert the Count object array to a dataset. If you read the documentation it says that (basically) only datasets and PyDatasets can be coerced into a dataset using the toDataset() function.

You can do it with a Script transform, though.

Something like:

	headers = []
	data = []
	
	for row in value:
		if not headers:
			headers = row.keys()
		data.append([val for key,val in row.iteritems()])
		
	return system.dataset.toDataSet(headers,data)

EDIT: I should note that, this script will error if the object lengths are uneven (e.g. any object has more or less keys than another object). You can get around that by supplying a list of headers and filtering the objects for only those keys.

EDIT 2: Also, noticed that you are using an expression binding to bind to only a property. I don't know for sure, but I would believe there is some unneeded overhead there for the expression to parse and look up the property. A property binding would probably be better here.

2 Likes
4

Yes the table works with the data like it is, its just not in the correct order. I want the table to be hour, actual, and then std and I couldn't figure out a way to change the order it shows in the table, I figured if I converted it to a dataset then i would be able to change the dataset so the table looked correct. I'm pretty new to perspective so I don't really know the correct way to do it so i'm trying a bunch of things to see what works.
table

5

Add columns and set the fields in the order that you want. Away from my computer atm so can’t provide a screen grab.

2 Likes
6

Thanks for your help, the table does work with the data from the original tag, I was trying to reconstruct the dataset so I could make the table look correct. I'm new to perspective so I'm still learning how this stuff works.

I do have another question for you guys. How do I create a dataset property in Perspective? I only see value, object, and array in the dropdown when I create a new property. I have a few already but they're system generated from being bound to a SQL query. Do I have to use a script like above to create the dataset first? or is there a manual way to do it?

7

There is no way to make a "dataset" type object by hand Although, that's not a bad idea for a suggestion. The default is the Array of Objects, as you have it.

You can change the order of the columns (and insure that order is fixed) by using the props.columns on the table. Add the number of columns that you have in your table and then set the field for each column as you want them to appear in the table.

image
image758×189 21.2 KB

8

Thank you. That worked great.

9

I always create an expression binding with toDataset(''). After pressing apply, I then remove the binding I just created and edit the dataset object manually.

4 Likes
10

To arrange as per the requirement, i tried with defining the header list instead of defining a blank list. but it changes the order of the header but not for data. Please note that i am not displaying the data in a table but I want to export the data to excel with formatted order of column

12

You can use this:

ds = system.dataset.toDataSet(
	headers,
	[[row[h] for h in headers] for row in data]
)

Where data is a list of dicts and headers is a list of strings.
Keys in the objects should match the column names in headers
You'll get a dataset with columns in the same orders as in headers

2 Likes
13

As @pascal.fragnoud shows you can use the list of headers to force the order of the columns in the dataset. You could also us system.dataset.filterColumns() to do this. Then assuming this is perspective, initiate the download:

ds = system.dataset.toDataset(data.keys(),[val for _,val in row.iteritems()])
orderedColumns = system.dataset.filterColumns(ds,["Col1","Col3","Col4"])
system.perspective.download("ExcelFile",system.dataset.toExcel(True, orderedColumns))
2 Likes
14

In the original example, the keys in the dicts were in different orders:

I'm not sure how this happened, but if it does, you can't just pull the values. That's why I used the header to reorder things.

Side note: if you're not gonna use the key, you can use .itervalues()

1 Like