Query result with 24 rows even if are less

[Aiasa21]

Hi guys,

I'm trying to have this result in an apex chart, where I show the hourly energy consumption with this simple query in MYSQL:

SELECT    Arc1_kWh
FROM      energy
WHERE 	  DATE(t_stamp) = DATE(:Day)

Obviously if I show a past day all the chart is full with the 24 hours, but if I show the chart of the actual day, the query doesn't return 24 rows but less and the result is that the xaxis of the chart if full but not with the 24 hours. I would like to have the empty space where I don't have the data available.

Here is how is now, with all the chart full even with no 24 rows

Here is how I would like to have it

To make it work I just add some empty rows with no value set.

The question is: how can I add automatically the empty values missing?

[Transistor]

Use a script transform in your binding. If query binding returns a document then the simplest code would be to check the length of the list and add the blank rows to the end.
If the chart is an XY Chart then it’s only necessary to add the point (24:00, 0) to the list.
A more robust scheme might check that each hour has a valid value in case no energy usage was recorded due to a lost connection or server / gateway down.

---

Just a point on chart style: You’ve chosen an area curve which suggests to me that the area under the curve represents the integral of the y-axis value with respect to x. This will be correct if the y-axis is power (kW) and the x-axis is time (hours). Then the area under the curve will be proportional to energy (kWh). If the y-axis is energy (kWh) then the area under the curve would be energy × time (kWh-h) which isn’t very meaningful.

[Aiasa21]

Thanks for the suggestion @Transistor.

You are right about the chart style, as you say I’m trying to show kWh, probably should be better use a graph bar instead an area chart.

About the problem, I have no idea how to append data to a json file, but I’ve made some tests and this is what I’m trying to do:

	
	for x in range(len(value), 24):
	     APPEND

	return [[row['Arc1_kWh']] for row in value]

I calculate the rows and that I cycle only what I’m missing. I can’t figure out how to append an empty value to the document now.

I know that is nothing and is not working but is an idea to start, just for not having the ready-made work.

[Transistor]

Can you disable the transform for now, right-click on the data, copy the data and post the first few rows of it so we can see the structure?

[Aiasa21]

Of course, I just let the transform script to convert the “name” of the values because the apex chart needs it to work.

So the script in work now is this

return [[row['Arc1_kWh']] for row in value]

and the data structure of today is this

[
  {
    "name": "Energy",
    "data": [
      [
        0.135108
      ],
      [
        0.145265
      ],
      [
        0.149898
      ],
      [
        0.129266
      ],
      [
        0.149391
      ],
      [
        0.147877
      ],
      [
        0.147723
      ]
    ]
  }
]

[Transistor]

Try running this in the script console:

# Script console demo.
value = [
      [0.135108], [0.145265], [0.149898], [0.129266], 
      [0.149391], [0.147877], [0.147723]
]

# Note that value is a list of single-element lists.
value		# Print the incoming value
len(value)	# Print the number of items in the 'data' value.

for i in range(24 - len(value)):
	value.append([0])

value		# Print the modified list.
len(value)	# Print the number of items in the 'data' value.

Note that in Script Console a variable on its own line gets printed in the results window.

You should be able to figure out what’s going on there and incorporate it into your transform.

[Aiasa21]

I thank you for the help.

I’m trying to figure out how to make it work in the trasform but I’m not able to make it work, sorry. Your example works perfectly in the script console, but I can’t handle in the script transform.

Now I print directly from the script trasform the value of the script traform and this is the result:

[{"Arc1_kWh":0.135108}, {"Arc1_kWh":0.145265}, {"Arc1_kWh":0.149898}, {"Arc1_kWh":0.129266}, {"Arc1_kWh":0.149391}, {"Arc1_kWh":0.147877}, {"Arc1_kWh":0.147723}, {"Arc1_kWh":0.210486}, {"Arc1_kWh":0.229795}, {"Arc1_kWh":0.142022}, {"Arc1_kWh":0.137979}, {"Arc1_kWh":0.141827}, {"Arc1_kWh":0.14992}, {"Arc1_kWh":0.138453}, {"Arc1_kWh":0.131361}, {"Arc1_kWh":0.127942}, {"Arc1_kWh":0.130167}, {"Arc1_kWh":0.148968}, {"Arc1_kWh":0.196889}]

So I guess that I should work on the “value” variable for the append and so I try to make this:

def transform(self, value, quality, timestamp):
	"""
	Transform the incoming value and return a result.

	Arguments:
		self: A reference to the component this binding is configured on.
		value: The incoming value from the binding or the previous transform.
		quality: The quality code of the incoming value.
		timestamp: The timestamp of the incoming value as a java.util.Date
	"""
	system.perspective.print(value)

	for x in range(24 - len(value)):
		value.append('Arc1_kWh', '')
		
	return [[row['Arc1_kWh']] for row in value]

but sincerly I’m just trying without lucky, I know I have no knowledge about it

[Transistor]

Going back to your original post I can see that the binding was on data, not on series.0 so I’ve misguided you a little. All you want back is the list:

[
    [0.135108], [0.145265], [0.149898], [0.129266], [0.149391], [0.147877], 
    [0.147723], [0], [0], [0], [0], [0], 
    [0], [0], [0], [0], [0], [0], 
    [0], [0], [0], [0], [0], [0]
]

I think your transform should just be,

	for x in range(24 - len(value)):
		value.append([0])

	return value

So, what we’re doing is adding elements to a list (array) and each element that we’re adding is itself an single-element list.

I’ll fix my earlier post.

[Aiasa21]

I do apologize for the misunderstanding, you’re right, in the first post I copy all the “series” of the chart but actually the trasform is made to the binding, so to the value I think. Sorry again.

I did try the transformation you suggest, but I had error, I try it again and the exact error is

Thanks again for your time

[Transistor]

I assume that line 5 is value.append([0]).
I’m not a Python / JSON expert but have figured out enough to do what I need. You can post use https://jsonlint.com to check for valid JSON and the original array and [0] are both valid. I suspect that something is getting messed up along the way.

At the bottom of the binding dialog you can perform a limited inspection of the query and the transform. Does the query still look like the list in (my) post 7 or does it have “Arc1_kWh” in it?

[Aiasa21]

@Transistor

to check for valid JSON and the original array and [0] are both valid. I suspect that something is getting messed up along the way.

I'm sorry but I don't understand what exactly I should do.

@Transistor

Does the query still look like the list in (my) post 7 or does it have “Arc1_kWh” in it?

The query preview shows something like

[ { "Arc1_kWh": 0.135108}, { "Arc1_kWh": 0.145265}, ecc ] 

The trasform preview of this script traform

return [[row['Arc1_kWh']] for row in value]

shows exactly what you wrote in post 7, obviously without the [0] values that you append to complete the 24 hours.

[Transistor]

Script console demo.

# The SQL query returns an array of dictionaries.
value = [ { "Arc1_kWh": 0.10}, { "Arc1_kWh": 0.20}, { "Arc1_kWh": 0.30} ]

# Note that value is a list of singgle-element lists.
value		# Print the incoming value
len(value)	# Print the number of items in the 'data' value.

output = []	# Initialise the output array.

for i in range(len(value)):
	output.append([value[i]['Arc1_kWh']])	# Extract the value from each key: value pair.
	# Note that we're adding it to the output inside [] braces to make each reading an array.
	
for i in range(24 - len(value)):
	output.append([0])	# Pad out the array with zero values, [0], [0], ...

output		# Print the modified list.
len(output)	# Print the number of items in the 'data' value.

Binding script transform code (fix the indentation when copied):

# The SQL query returns an array of dictionaries.
# Input will be of form [ { "Arc1_kWh": 0.10}, { "Arc1_kWh": 0.20}, { "Arc1_kWh": 0.30} ]
# Output will be of form [[0.1], [0.2], [0.3], [0], [0], [0], [0], ... ]

# Note that value is a list of single-element lists.
output = []	# Initialise the output array.

for i in range(len(value)):
	output.append([value[i]['Arc1_kWh']])	# Extract the value from each key: value pair.
	
for i in range(24 - len(value)):
	output.append([0])	# Pad out the array with zero values, [0], [0], ...

return output

[Aiasa21]

Thank you very much for your help, I appreciate it.

Your code is working good, I had some minute to test is this morning and is what I wanted to achieve.

Testing it I figure out that the chart is not 100% correct, I try to explain.
The first value is shown in the Xaxis[0] (the cross X-Y to understand), but actually the first value of the JSON is the 00:00 to 01:00 kWh. So I think that the solution could be to shift all the values to 1 and insert in value[0] the kWh of value[1].

I have to make some test later, now I’m with the phone.

[JordanCClark]

There are also other ways to get your values. While these are considred more ‘pythonic’, use whatever makes sense to you until you get comforatble with them.

output = [row["Arc1_kWh"] for row in value]
output.extend([0] * (24-len(value)))

[Aiasa21]

@JordanCClark, thanks for your suggestion. Both work good, maybe your method is much “clean”.

I modify the code in this way, so to insert a new value at index[0] and copy in it the value of index[1].

	output = [row["Arc1_kWh"] for row in value]
	output.insert(0, output[0])
	output.extend([0] * (24-len(output)))
	
	return output

I add the new line inserting the index[0] in the new index[0] and I change len(value) with len(output) so to have the exactly count after the insert.

Is correct what I’ve done or could be done better?

[JordanCClark]

If it works, it works.

[Aiasa21]

It works