Conditional Formatting for Table

[vspadotto]

Hi everyone,

I am trying to create a table with conditional formatting for the font color, for example, if numbers are > 0 the font will be back and if they are < 0 the font will be red.

The problem is that when I run the code it shows me a error “’<=’ not supported between instances of list and int”.

Here is the code:

fisrt_column = ['Core Stores', 'AB', 'BC', 'MB', 'ON', 'SK', 'Total']		
vals = [fisrt_column, vals_gross_week, vals_net_week, vals_guest_week, vals_avgcheck_week, vals_netcapture_week, vals_gross_period, vals_net_period, vals_guest_period, vals_gross_year, vals_net_year, vals_guest_year, vals_avgcheck_year, vals_netcapture_year]
	
#the variables, such as vals_gross_week, are a list of floats 
         
trace_table_overall = go.Table(
    domain=dict(x=[0, 1],
                y=[0.7, 1]),  
    columnwidth=[2, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1],
    columnorder=[0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13],
	header=dict(values=[['', ''],
                        ['', 'Gross Sales'],
                        ['', 'Net Sales'],
                        ['<b>Week<b>', 'Guest Count'],
                        ['', 'Avg Check'],
                        ['', 'Net Sales Capture'],                        
                        ['', 'Gross Sales'],
                        ['<b>Period<b>', 'Net Sales'],
                        ['', 'Guest Count'],
                        ['', 'Gross Sales'],
                        ['', 'Net Sales'],
                        ['<b>Year<b>', 'Guest Count'],
                        ['', 'Avg Check'],
                        ['', 'Net Sales Capture']],
	           line = dict(color=['#6B2626', '#6B2626', '#6B2626', '#6B2626', '#6B2626', '#6B2626', 
                     '#47546D', '#47546D', '#47546D', '#437D55', '#437D55', '#437D55', '#437D55', '#437D55']),
                fill = dict(color=['#6B2626', '#6B2626', '#6B2626', '#6B2626', '#6B2626', '#6B2626', '#47546D',
			'#47546D', '#47546D', '#437D55', '#437D55', '#437D55', '#437D55', '#437D55']),
                font=dict(family="Arial", size=12, color='white'),
                align = ['center'] * 5),
	cells=dict(values=vals,
               line = dict(color='#CCCCCC'),
               fill_color = [colors_overall],
               font=dict(family="Arial", size=10, 
               color=['darkslategray', ['red' if val<=0 else 'blue' for val in vals]]),
               align = ['center'] * 5))

Does anybody know what is wrong?

[empet]

@vspadotto I defined a simple table:

import plotly.graph_objects as go

prods = ['Aaaa', 'Bbbb', 'Cccc', 'Dddd', 'Eeee', 'Ffff']
vals = [-1.2, 2.3, 4.5, -2.5, 3.2, -0.5]
font_color = ['rgb(40,40,40)', ['rgb(255,0,0)' if v <= 0 else 'rgb(10,10,10)' for v in vals]]

table_trace = go.Table(
                 columnwidth= [15]+[15],
                 columnorder=[0, 1],
                 header = dict(height = 50,
                               values = [['<b>Product</b>'], ['<b>Quantity</b>']],
                               line = dict(color='rgb(50,50,50)'),
                               align = ['left']*2,
                               font = dict(color=['rgb(45,45,45)']*2, size=14),
                             
                              ),
                 cells = dict(values = [prods, vals],
                              line = dict(color='#506784'),
                              align = ['left']*5,
                              
                              font = dict(family="Arial", size=14, color=font_color),
                              format = [None, ",.2f"],
                             
                              height = 30,
                              fill = dict(color='rgb(245,245,245)'))
                             )
                 

layout = go.Layout(width=400, height=415, autosize=False, 
              title_text='Table title',
                   title_x=0.5, showlegend=False)
fig = go.Figure(data=[table_trace], layout=layout)

and my plot displays conditional font color:

[vspadotto]

Thank you @empet. It works when I test it on a simple table, however when I try it on my table it returns the same error. Probably I’m having some data type issues.

[empet]

@vspadotto

If the names in your vals definition represent lists of values, then your conditional font color is not the right one.

Replacing in my example the list vals with a list of two lists then the font_color gets:

vals = [[-1.2, 2.3, 4.5, -2.5, 3.2, -0.5], [12.456, -10.5, 17.11, 8.23, -15.67, -4.2]]
font_color = ['rgb(40,40,40)'] +  [['rgb(255,0,0)' if v <= 0 else 'rgb(10,10,10)' for v in vals[k]] for k in range(2)]

table_trace = go.Table(
                 columnwidth= [15]+[15]+[20],
                 columnorder=[0, 1, 2],
                 header = dict(height = 50,
                               values = [['<b>Product</b>'], ['<b>Quantity</b>'], ['<b>SomeValue</b>']],
                               line = dict(color='rgb(50,50,50)'),
                               align = ['left']*3,
                               font = dict(color=['rgb(45,45,45)']*3, size=14),
                             
                              ),
                 cells = dict(values = [prods, vals[0], vals[1]],
                              line = dict(color='#506784'),
                              align = ['left']*5,
                              
                              font = dict(family="Arial", size=14, color=font_color),
                              format = [None, ",.2f"],
                             
                              height = 30,
                              fill = dict(color='rgb(245,245,245)'))
                             )
                 

layout = go.Layout(width=400, height=415, autosize=False, 
              title_text='Table title',
                   title_x=0.5, showlegend=False)
fig = go.Figure(data=[table_trace], layout=layout)

[vspadotto]

Thank you @empet, I’ve used the code below to convert the list to floats and it worked.

float_vals_avgcheck_week = []
for item in vals_avgcheck_week:
    float_vals_avgcheck_week.append(float(item))

One question, do you know how can I add a % sign using the format but still round it by 2?

format = [None, ",.2f"],

Thanks again!

[empet]

@vspadotto I’m not sure that I understood your question. Do you intend to add the suffix ‘%’, as percent sign, in some column? If this is the case, then for the example with two columns given above, just add in cells definition,

suffix = [None, '%', None],

to get a a table like this one:

[vspadotto]

That’s it.
Thank for your help, it was very useful!

[mini_geek]

Hi @empet I’m working with a similar table but I when I apply the conditional formatting it applies it to the whole table. The conditional formatting should only apply to the last column containing the negative signs. Can anyone point out what i’m doing wrong? Thanks in advance!

def color_negative_red(val):
    font_color = 'red' if val==df2['Daily_Returns_Pct'].str.contains('-') else 'black'
    return 'color: %s' % color

t = go.FigureWidget([go.Table(
    header=dict(values=['Dates','Low (In $)','High (In $)','Daily Close (In $)','Daily_Returns (Natural Log)'],
                fill = dict(color='#386dea'),
                font=dict(color='#fcfcfc'),
                align = ['left'] * 5),
    cells=dict(values=[df2[col] for col in 
                       ['Dates','Low','High','Daily_Close','Daily_Returns_Pct']],
               fill = dict(color='#F5F8FF'),
               align = ['left'] * 5,
               font=dict(color=font_color)))])

[empet]

@mini_geek

It seems that you did not read my examples above, because your function doesn’t set the font color as I defined it in the second example.

I give here a new example that resembles yours:

import plotly.graph_objects as go
import pandas as pd


d = {'col1': [1, 4, 6, 8, 5],
     'col2': [2, 6, 9, 1, 6],
     'col3': ['-7.5%', '3.75%', '5.2%', '-2.34%', '-3.17%']}
df = pd.DataFrame(d)
font_color=['black']*2+[['red' if  boolv else 'black' for boolv in df['col3'].str.contains('-')]]


table_trace = go.Table(
                 header = dict(height = 50,
                               values = [['<b>Col1</b>'], ['<b>Col2</b>'], ['<b>Col3</b>']],
                               align = ['left']*3,
                               fill_color = '#386dea',
                               font_color = '#fcfcfc',
                               font_size =14),
                 cells = dict(values = [df['col1'], df['col2'], list(df['col3'])],
                              line = dict(color='#506784'),
                              align = ['left']*3,
                              font_color=font_color,
                              font_family="Arial", 
                              font_size=14,   
                              height = 30,
                              fill = dict(color='rgb(245,245,245)'))
                             )
                 

layout = go.Layout(width=400, height=415, autosize=False, 
              title_text='Table title',
                   title_x=0.5, showlegend=False)
fig = go.Figure(data=[table_trace], layout=layout)

if you are displaying the list font_color it looks like this:

['black', 'black', ['red', 'black', 'black', 'red', 'red']]

i.e. the values in the first two columns are colored all in black, while in the third one, the color depends on whether the char ‘-’ is met in front of the corresponding string or not.

Hence in your case, you have 5 columns. The values in the first 4 should be colored with black, and in the last column following the above rule.

font_color=['black']*4+[['red' if  boolv else 'black' for boolv in df2['Daily_Returns_Pct'].str.contains('-')]]
#where boolv is a boolean value.

[mini_geek]

Thank you @empet! I made the changes and did the trick. Though after inspecting results there seems to to be a bug? Some negative values are not highlighted and some positive values are highlighted. I’ve read a similar issue posted, and not sure if solved. Could it be that or I messed-up the code again?

Blockquote

Possible bug with table cell font colors

[image] Hi, I noticed a potential bug when trying to assign cell colors to a financial dashboard i’m working on. After about 17 rows or so, the coloring for 18th row is skipped (instead of positive values as green and negative values as red, the entire row shows default black values) and I noticed that the error happens again around row 35. I am only modeling 52 weeks of data so i’m unsure if the issue would pop up again around row 53 or so. To reproduce I tried simple alternating colors ag…

[mini_geek]

Hi @empet any ideas on this?

[empet]

@mini_geek It seems to be a bug in go.Table. I checked whether the font color assigned to each string in column 5 is the right one for a data frame of 50 of rows and it is (i.e. the font_color is well defined):

list(zip( df['col5'].values, font_color[-1]))
[('0.34%', 'black'),
 ('0.65%', 'black'),
 ('5.98%', 'black'),
 ('-1.59%', 'red'),
 ('2.45%', 'black'),
 ('5.48%', 'black'),
 ('0.23%', 'black'),
 ('-0.59%', 'red'),
 ('7.64%', 'black'),
 ('6.68%', 'black'),
 ('1.38%', 'black'),
 ('1.36%', 'black'),
 ('1.94%', 'black'),
 ('5.76%', 'black'),
 ('8.0%', 'black'),
 ('-0.69%', 'red'),
 ('-4.34%', 'red'),
 ('5.99%', 'black'),
 ('-1.61%', 'red'),
 ('0.8%', 'black'),
 ('5.03%', 'black'),
 ('0.24%', 'black'),
 ('5.81%', 'black'),
 ('6.8%', 'black'),
 ('1.43%', 'black'),
 ('7.01%', 'black'),
 ('-1.74%', 'red'),
 ('3.78%', 'black'),
 ('4.49%', 'black'),
 ('-0.96%', 'red'),
.
.
.
]

Hence the only suggestion is to open an issue on plotly.js https://github.com/plotly/plotly.js/issues.

[mini_geek]

That’s what I thought. Thanks @empet!

[vspadotto]

I’m still having issues to figure out why one of my tables is not formatting properly.

I created a simple example and everything works fine.

#create dataframe
d = {'Location': ['Aaa','Bbb','Ccc', 'Ddd'], 
	'Sales1': [100, -200, 300, 100],
	'Sales0': [90, -210, 320, 0],
	'Sales3': [1009, -2000, 400, 100],
	'Sales2': [1000, -2910, 320, 0]}
df = pd.DataFrame(data=d)

df['Variance1'] = df['Sales1']/df['Sales0']-1
df['Variance2'] = df['Sales3']/df['Sales2']-1

#create table
df_locations = df['Location']
vals = [df['Sales1'], df['Sales0'], df['Variance1'],
		df['Sales3'], df['Sales2'], df['Variance2']]

font_color = ['darkslategray'] +  [['rgb(192,0,0)' if v < 0 else 'darkslategray' for v in vals[k]] for k in range(4)]

table = go.Table(
    domain=dict(x=[0,1],
                y=[0,1]),  
	header=dict(values=['Location', 'Sales1', 'Sales0','Variance1',
						'Sales3', 'Sales2','Variance2']),
	cells=dict(values = [df_locations, vals[0], vals[1], vals[2], vals[3],
						vals[4], vals[5]],
               font=dict(color=font_color),
               format = [None,",.0f", ",.0f", ".1%",",.0f", ",.0f", ".1%"]))

data = [table]

fig = go.Figure(data=data)

pyo.plot(fig, filename='test.html')

I will get this.

I try to do the same thing to my other table, using the code below.

gross_week_location = gross_week['Location']
vals = [gross_week['WTD'],gross_week['LYWTD'],gross_week['WTD Variance'],
		gross_week['PTD'],gross_week['LYPTD'],gross_week['PTD Variance'],
		gross_week['YTD'],gross_week['LYTD'],gross_week['YTD Variance']]

font_color = ['darkslategray'] +  [['rgb(192,0,0)' if v < 0 else 'darkslategray' for v in vals[k]] for k in range(9)]

trace_gross_week = go.Table(
    domain=dict(x=x,
                y=y),  
    columnwidth=[2, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1],
    columnorder=[0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10],
    visible=True,
	header=dict(values=['Location', 'WTD', 'LYWTD','Week','PTD', 'LYPTD', 
						'Period', 'YTD', 'LYTD', 'Year'],
	            line = dict(color=['#6B2626', '#6B2626', '#6B2626', 
	            					'#BA7603', '#47546D', '#47546D', '#47546D', 
	            					'#437D55', '#437D55', '#437D55']),
                fill = dict(color=['#6B2626', '#6B2626', '#6B2626', 
                					'#BA7603', '#47546D', '#47546D', '#47546D', 
                					'#437D55', '#437D55', '#437D55']),
                font=dict(family="Arial", size=12, color='white'),
                align = ['center'] * 5),
	cells=dict(values = [gross_week_location, vals[0], vals[1], vals[2],
						vals[3], vals[4], vals[5], vals[6], vals[7], vals[8]],
               line = dict(color='#CCCCCC'),
               fill_color = [colors],
               font=dict(family="Arial", size=10, color=font_color),
               format = [None, 
               			",.0f", ",.0f", ".1%", 
               			",.0f", ",.0f", ".1%",
               			",.0f", ",.0f", ".1%"],
               align = ['center'] * 5))

But the conditional formatting is being applied partially only in the last column of the table and not for all columns. Am I using the wrong range? My table has 45 rows.

Any idea what can be wrong in my code?

[apache]

How to set
Inverted triangle if less than or equal to 0
Greater than 0 is positive triangle
As shown below

[apache]

@empet can you solve it? thank you very much~

[empet]

Hi @apache,

The two symbols are: marker_symbol='triangle-up', respectively, marker_symbol='triangle-down'.
Unfortunately, at the moment there is no means to insert columns of marker symbols in a go.Table.

[apache]

@empet How to solve it in dash_table.DataTable? thanks~

[empet]

@apache For dash data table please repost your question under category, dash.

[apache]

@empet all right ,thank you ~