Python dataset group by date

Ignition
,
1

Hi

In perspective I get dataset from MySQL db and put it in charts and tables in object format.
To avoid to get another SQL query, I would like to group by day, month, year my dataset variable with a script transform.
Is it possible in python ?

Thanks,

2

The Simulation Aids Module might help. You can use psuedoSQL on a dataset with its view() function.

2 Likes
3

Thanks,

Do you have an example with a group by statement ?
Mydataset :

[{'t_stamp':'2021-01-01 00:00:00','value':59}, {'t_stamp':'2021-01-01 01:00:00','value':52}, ...]

SQL query in view() :

SELECT DATE(t_stamp) as t_stamp, sum(value) as value GROUP BY DATE(t_stamp)

I tried expression but it doesn’t work :

view('select date(t_stamp),sum(value) as value group by date(t_stamp)',{this.props.dataSources.example})
4

There’s no date() function. Just use the t_stamp column if it already has zeros for hour, minute, second.

5 
view('select t_stamp,sum(value) as value group by t_stamp',{this.props.dataSources.example})

It still not working. I think SimulAids Module can’t responds to my needs ==> just group a dataset by day

6

Your ‘dataset’ looks like a list of dictionaries. view() needs an actual Dataset.

7

It seems pandas library can group data but it can’t be installed in ignition. Is it true ?

8

Yes, you can not use any python library which has ‘C python’ dependencies.

9

Is there any other solution to aggregate data (grouping like sql) with python ?:sob:

10

Just get the data in dataset format in some custom property. Transform it to object format where you are currently using it, and use the dataset for the view() operation.

1 Like
11

I had the same question, my topic might be able to help you!

https://forum.inductiveautomation.com/t/replace-row-values-in-a-dataset/67987