Change the table cell color based on value

its working perfect thanks alot

1 Like

Hi,


This is the record i am getting from store procedure in my database and i am showing it in power table

But i want to add a row at the end and calculate the mean for each column and show the data.

how to do that can you give me idea

You need to iterate over the columns, take the average, add it to a new list, then add the new list as a row to the existing data.

# Test data
header = ['row1','row2','row3','row4']
rows = [[1.0,2.0,3.0,4.0],[4.0,5.0,6.0,7.0]]

# ds would be the results from your stored procedure 
ds = system.dataset.toDataSet(header,rows)

# List for mean values
meanRow = []

# loop over the number of columns in the dataset
for colIndex in range(len(ds.getColumnNames())):
	values = ds.getColumnAsList(colIndex)
	mean = system.math.mean(values)
	meanRow.append(mean)
	
# add mean row to original data, assign this dataset to your table when done
dsOut = system.dataset.addRow(ds,meanRow)

Output

row | row1 | row2 | row3 | row4
-------------------------------
0   | 1.0  | 2.0  | 3.0  | 4.0 
1   | 4.0  | 5.0  | 6.0  | 7.0 
2   | 2.5  | 3.5  | 4.5  | 5.5 

1 Like

Hi, tired your script its working. But i am facing one issue

i have resolved the issue in named query i have used type caste

@dkhayes117
This is the cell color in wincc report

code they are using

"#" & IIf(Int(Fields!H02.Value) < 1, "11ff",
IIf(Int(Fields!H02.Value) < 16, "ff" & Hex(17 * Int(Fields!H02.Value)),
IIf(Int(Fields!H02.Value) < 30, Hex(17 * (30-Int(Fields!H02.Value))) & "ff",
"ff11"
)
)
) & "00"

this is the color i am getting in ignition power table

code we are using is

value = int(value)
	#if colIndex != 1 and colIndex != 2 and colIndex != 39 and colIndex != 40 and colIndex != 41 and colIndex != 42 and colIndex != 43:
	if colIndex != 0 :
		if value < 1 :
			return {'background': "11ff00"}
		elif value < 16 :
			return {'background': "ff%s00" % hex(17 * value).replace("0x","")}
		elif value < 30 :
			return {'background':  "%sff00" % hex(17 * (30-value)).replace("0x","")}
		else:
			return {'background': "ff1100"}

color of power table not matching with the Wincc report color
Anything to change in the color code?

The data in the tables are not the same, why would the color be the same?

1 Like

Yes i understood. I thought like colors are mismatching

Hi @dkhayes117
this is the dataset i am getting from database using named query

I want to calculate mean - if you see my table deck number will repeat several times. for that i need to calculate mean and show the mean value for each deck number. we can insert this records in to new dataset

how to adjust your script for this requirement?

@PGriffith and @JordanCClark any idea from your side?

Do you want to average all H01s based on deck number, or average H01-H10 based on deck number?

Yes for each columns we need to get mean

Output like this

For each deck number

All I see is a table full of data. I can’t tell what numbers in that picture are the calculated averages you want. Give me your formula.

Its just example i upload

Do you want to average all H01s based on deck number - this i want

Same Deck numbers are repeating in the table like example

For that i need to calculate mean for same deck numvers and show data

select deck_number::text as Deck_No,avg(h01)::numeric(10,0) as H01 ,avg(h02)::numeric(10,0) as H02 ,avg(h03)::numeric(10,0) as H03 ,avg(h04)::numeric(10,0) as H04 ,
avg(h05)::numeric(10,0) as H05 ,avg(h06)::numeric(10,0) as H06 ,avg(h07)::numeric(10,0) as H07 ,avg(h08)::numeric(10,0) as H08 ,
avg(h09)::numeric(10,0) as H09 ,avg(h10)::numeric(10,0) as H10 ,avg(h11)::numeric(10,0) as H11 ,avg(h12)::numeric(10,0) as H12 ,
avg(h13)::numeric(10,0) as H13 ,avg(h14)::numeric(10,0) as H14 ,avg(h15)::numeric(10,0) as H15 ,avg(h16)::numeric(10,0) as H16 ,
avg(h17)::numeric(10,0) as H17 ,avg(h18)::numeric(10,0) as H18 ,avg(h19)::numeric(10,0) as H19 ,avg(h20)::numeric(10,0) as H20 ,
avg(h21)::numeric(10,0) as H21 ,avg(h22)::numeric(10,0) as H22 ,avg(h23)::numeric(10,0) as H23 ,avg(h24)::numeric(10,0) as H24 ,
avg(h25)::numeric(10,0) as H25 ,avg(h26)::numeric(10,0) as H26 ,avg(h27)::numeric(10,0) as H27 ,avg(h28)::numeric(10,0) as H28 ,
avg(h29)::numeric(10,0) as H29 ,avg(h30)::numeric(10,0) as H30 ,avg(h31)::numeric(10,0) as H31 ,avg(h32)::numeric(10,0) as H32 ,
avg(h33)::numeric(10,0) as H33 ,avg(h34)::numeric(10,0) as H34 ,avg(h35)::numeric(10,0) as H35 ,
avg(h36)::numeric(10,0) as H36 from humidity_temperature 
where date_time >= :start ::timestamp without time zone and date_time <= :end ::timestamp without time zone group by deck_number 
order by deck_number desc;

this is the query i am using in postgres sql… But i want this to be implemented using python script

Why do you need it in a python script?

Because i am going to fetch the data using system.tag.query history. Then i need to calculate mean for the data

My requirement is not possible to do?

It is very possible, just thinking about best way to do it…

1 Like

What about using a GROUP BY clause in the query?

Sorry, just saw the history part.

Do you have any sample data you can share as a csv? EDIT (from the tag history)

The use of dictionaries is useful when you want to place things in ‘bins’. In this example thie decks are the main bins and the heats (at least, I assume they are heats) are sub-bins. I printed the end results of the dictionary and what the resultant dataset would look like.

def averageHeats(dataIn):
	'''
	Make an averaged value dataset by deck number.
	'''
	# Get the column names from the dataset
	colNames = list(dataIn.getColumnNames())
	
	# Initialize dictionary.
	processedData = {}
	for row in system.dataset.toPyDataSet(dataIn):
		# Initialize entry if deck doesn't exist
		if row['Deck_no'] not in processedData.keys():
			processedData[row['Deck_no']] = {heat:[row[heat]] for heat in colNames[1:]}
		else:
		# otherwise, append heat values
			for heat in colNames[1:]:
				processedData[row['Deck_no']][heat].append(row[heat])

	#for row in processedData.items():
	#	print row
	
	dataOut = []
	# Average the heat values and make the new dataset.
	for deck in sorted(processedData.keys()):
		dataOut.append([deck] + [round(system.math.mean(processedData[deck][heat]), 1) for heat in colNames[1:]])
	
	return system.dataset.toDataSet(colNames, dataOut)


sampleHeaders = ['Deck_No','H01','H02','H03','H04','H05','H06','H07','H08','H09','H10'] 
sampleData = [[5,11,16,16,14,14,14,16,18,18,19],
              [3,6,13,13,13,13,13,13,14,14,15],
              [1,0,17,13,13,13,13,13,13,16,18],
              [2,12,12,14,14,14,16,18,18,18,18],
              [14,12,12,13,13,13,14,14,14,16,17],
              [12,4,15,14,14,14,15,15,16,20,20],
              [7,14,14,14,15,16,16,7,17,17,18],
              [5,19,19,15,14,14,15,16,16,18,19],
              [3,13,13,13,13,13,13,13,14,14,16],
              [1,2,15,13,13,13,13,13,13,17,17],
              [2,7,13,13,14,14,16,16,17,17,17],
              [14,0,13,13,13,14,14,14,15,16,16],
              [12,12,12,13,14,15,15,16,17,17,20],
              [7,0,17,15,15,16,17,16,16,18,18],
              [5,8,18,18,14,14,16,16,17,20,20],
              [8,10,10,10,10,10,10,10,10,10,10]
             ]
	             
sampleDataset =  system.dataset.toDataSet(sampleHeaders, sampleData)

averagedDataset = averageHeats(sampleDataset)
(1, {u'H10': [18, 17], u'H01': [0, 2], u'H03': [13, 13], u'H02': [17, 15], u'H05': [13, 13], u'H04': [13, 13], u'H07': [13, 13], u'H06': [13, 13], u'H09': [16, 17], u'H08': [13, 13]})
(2, {u'H10': [18, 17], u'H01': [12, 7], u'H03': [14, 13], u'H02': [12, 13], u'H05': [14, 14], u'H04': [14, 14], u'H07': [18, 16], u'H06': [16, 16], u'H09': [18, 17], u'H08': [18, 17]})
(3, {u'H10': [15, 16], u'H01': [6, 13], u'H03': [13, 13], u'H02': [13, 13], u'H05': [13, 13], u'H04': [13, 13], u'H07': [13, 13], u'H06': [13, 13], u'H09': [14, 14], u'H08': [14, 14]})
(5, {u'H10': [19, 19, 20], u'H01': [11, 19, 8], u'H03': [16, 15, 18], u'H02': [16, 19, 18], u'H05': [14, 14, 14], u'H04': [14, 14, 14], u'H07': [16, 16, 16], u'H06': [14, 15, 16], u'H09': [18, 18, 20], u'H08': [18, 16, 17]})
(7, {u'H10': [18, 18], u'H01': [14, 0], u'H03': [14, 15], u'H02': [14, 17], u'H05': [16, 16], u'H04': [15, 15], u'H07': [7, 16], u'H06': [16, 17], u'H09': [17, 18], u'H08': [17, 16]})
(8, {u'H10': [10], u'H01': [10], u'H03': [10], u'H02': [10], u'H05': [10], u'H04': [10], u'H07': [10], u'H06': [10], u'H09': [10], u'H08': [10]})
(12, {u'H10': [20, 20], u'H01': [4, 12], u'H03': [14, 13], u'H02': [15, 12], u'H05': [14, 15], u'H04': [14, 14], u'H07': [15, 16], u'H06': [15, 15], u'H09': [20, 17], u'H08': [16, 17]})
(14, {u'H10': [17, 16], u'H01': [12, 0], u'H03': [13, 13], u'H02': [12, 13], u'H05': [13, 14], u'H04': [13, 13], u'H07': [14, 14], u'H06': [14, 14], u'H09': [16, 16], u'H08': [14, 15]})


row | Deck_No | H01  | H02  | H03  | H04  | H05  | H06  | H07  | H08  | H09  | H10 
-----------------------------------------------------------------------------------
0   | 1       | 1.0  | 16.0 | 13.0 | 13.0 | 13.0 | 13.0 | 13.0 | 13.0 | 16.5 | 17.5
1   | 2       | 9.5  | 12.5 | 13.5 | 14.0 | 14.0 | 16.0 | 17.0 | 17.5 | 17.5 | 17.5
2   | 3       | 9.5  | 13.0 | 13.0 | 13.0 | 13.0 | 13.0 | 13.0 | 14.0 | 14.0 | 15.5
3   | 5       | 12.7 | 17.7 | 16.3 | 14.0 | 14.0 | 15.0 | 16.0 | 17.0 | 18.7 | 19.3
4   | 7       | 7.0  | 15.5 | 14.5 | 15.0 | 16.0 | 16.5 | 11.5 | 16.5 | 17.5 | 18.0
5   | 8       | 10.0 | 10.0 | 10.0 | 10.0 | 10.0 | 10.0 | 10.0 | 10.0 | 10.0 | 10.0
6   | 12      | 8.0  | 13.5 | 13.5 | 14.0 | 14.5 | 15.0 | 15.5 | 16.5 | 18.5 | 20.0
7   | 14      | 6.0  | 12.5 | 13.0 | 13.0 | 13.5 | 14.0 | 14.0 | 14.5 | 16.0 | 16.5
2 Likes