Home > Software engineering >  How to color a dataframe row based on a column value when converting to HTML?
How to color a dataframe row based on a column value when converting to HTML?

Time:10-28

I am trying to color a row based on a minimum value in a column.

My mystyle.css is

.mystyle {
    font-size: 11pt; 
    font-family: Arial;
    border-collapse: collapse; 
    border: 1px solid rgb(7, 7, 7);

}

.mystyle td, th {
    padding: 5px;
}

and I am generating a random dataframe,

import pandas as pd
import numpy as np

pd.set_option('display.width', 1000)
pd.set_option('colheader_justify', 'center')


np.random.seed(6182018)
demo_df = pd.DataFrame({'date': np.random.choice(pd.date_range('2018-01-01', '2018-06-18', freq='D'), 50),
                        'analysis_tool': np.random.choice(['pandas', 'r', 'julia', 'sas', 'stata', 'spss'],50),              
                        'database': np.random.choice(['postgres', 'mysql', 'sqlite', 'oracle', 'sql server', 'db2'],50), 
                        'os': np.random.choice(['windows 10', 'ubuntu', 'mac os', 'android', 'ios', 'windows 7', 'debian'],50), 
                        'num1': np.random.randn(50)*100,
                        'num2': np.random.uniform(0,1,50),                   
                        'num3': np.random.randint(100, size=50),
                        'bool': np.random.choice([True, False], 50)
                       },
                        columns=['date', 'analysis_tool', 'num1', 'database', 'num2', 'os', 'num3', 'bool']
          )

print(demo_df.head()) 

introducing the function for changing color,

def highlight(df, colname, min_value):
    if df[colname].min() == min_value:
        return ['background-color: yellow'] * len(df)
    else:
        return ['background-color: white'] * len(df)

doing the html string separately,

pd.set_option('colheader_justify', 'center')   # FOR TABLE <th>

html_string = '''
<html>
  <head><title>HTML Pandas Dataframe with CSS</title></head>
  <link rel="stylesheet" type="text/css" href="mystyle.css"/>
  <body>
    {table}
  </body>
</html>.
'''

and finding the minimum value and applying the style.

min_value = demo_df["num1"].min()
print(min_value)
demo_df.style.apply(highlight(demo_df, "num1", min_value), axis=1)

with open('myhtml.html', 'w') as f:
    f.write(html_string.format(table=demo_df.to_html(classes='mystyle')))

But it is not working. How to accomplish coloring a column with a condition and apply css as well?

CodePudding user response:

This line stands out to me:

if df[colname].min():

You should probably check that this is equal to something, right? Otherwise it just reads as something like "if 6:" which is never true.

Update: You want to color the whole row if the value in that column matches the max of the column, so the .apply(function, axis=1) works fine. Try this as a complete solution:



def highlight(s, df, colname):
  colpos = df.columns.get_loc(colname)
  if s.values[colpos] == df[colname].max():
    return ['background-color:yellow']*len(s)
  return ['']*len(s)

data = [[1, 6], [2, 3], [4, 5]]
columns = ["myvar", "num1"]
demo_df = pd.DataFrame(data=data, columns=columns
s = demo_df.style.apply(highlight, df=demo_df, colname="num1", axis=1)

You'll notice that I assign df.style.apply() to the variable s. In your code, you're not saving that styler object anywhere. Now you should be able to use s.to_html() in your last line, though I haven't checked that. Let me know if it works.

Update 2: To add the "mystyle" class to the table, use this line:

html_string.format(table=s.set_table_attributes('').to_html())
  • Related