Add header and text color in HTML table email body based on disk usage condition using Python

Question

I am sending out an html table in an email body using python. The html table consists of disk usage and I need to add the header (first row) and text in red in the table when disk usage is above 80 percent.

This is the code I'm using that works to get the email with colouring the text but it does not include the headers (server,total size, total data, usage in %):

me = 'noreply@automationtest.com'

server = 'some smtp server'
you = 'email@someautomation.com'

text = """
{table}
"""

html = """
<html>
<head>
<style> 
  table, th, td {{ border: 1px solid black; border-collapse: collapse; }}
  th, td {{ padding: 5px; }}
</style>
</head>
<body><p style="font-family:verdana">Hi,</p>
<p style="font-family:verdana">sometext</p>
{table}
<p style="font-family:verdana">sometext</p>
<p style="font-family:verdana">Regards</p>
<p style="font-family:verdana">someme</p>
</body></html>
""" 

with open('files/file.csv') as input_file:
    reader = DictReader(input_file)
    data = list(reader)
    for row in data:
      row['Usage in %'] = pd.to_numeric(row['Usage in %'])
      if row['Usage in %'] >= 80:
      row['Usage in %'] = "<p style='color:red'>%s</p>"%row['Usage in %']


text = text.format(table=tabulate(data, headers="firstrow", tablefmt="grid"))

html = html.format(table=tabulate(data, headers="firstrow", tablefmt="unsafehtml"))
message = MIMEMultipart("alternative", None, [MIMEText(text), MIMEText(html,'html')])
print(html)


message['From'] = me
message['To'] = you
server = smtplib.SMTP(server)
server.ehlo()
server.starttls()
server.login(me, password)
server.sendmail(me, you, message.as_string())
server.quit()

I'm getting the below output with text color but without the header:

Expected output:

Any help is greatly appreciated.

Henry Ecker · Accepted Answer · 2021-12-10T03:49:28.353

In pandas 1.3.0 and newer, the most appropriate way is to use the pandas Table Visualization and create a Subclass

Create a folder "templates" and two files "myhtml.tpl" and "mystyles.tpl"

In myhtml.tpl put any additional HTML code needed:

{% extends "html_table.tpl" %}
{% block table %}
<p style="font-family:verdana">Hi,</p>
<p style="font-family:verdana">sometext</p>
{{ super() }}
<p style="font-family:verdana">sometext</p>
<p style="font-family:verdana">Regards</p>
<p style="font-family:verdana">someme</p>
{% endblock table %}

In mystyles.tpl add any additional styles:

{% extends "html_style.tpl" %}
{% block style %}
{{ super() }}
<style>
    table, th, td {
        border: 1px solid black;
        border-collapse: collapse;
    }

    th, td {
        padding: 5px;
    }
</style>
{% endblock style %}

(Code to generate this file structure is at the end of this answer)

We can now generate a Styler subclass with from_custom_template

import numpy as np
import pandas as pd
from pandas.io.formats.style import Styler

# Build Styler Subclass from templates
MyStyler = Styler.from_custom_template(
    "templates",  # Folder to Search
    html_table="myhtml.tpl",  # HTML Template
    html_style='mystyles.tpl'  # CSS Template
)
# trim extra whitespace from HTML
MyStyler.env.trim_blocks = True

# Read in CSV
df = pd.read_csv('files/file.csv')

# Add styles using Styler apply and render to_html
html = MyStyler(df).apply(
    lambda s: np.where(s >= 80, 'color: red', None), subset='Usage in %'
).format(
    # Apply format string (remove insignificant zeros)
    formatter='{:g}', subset='Usage in %'
).hide_index().to_html(doctype_html=True)

print(html)

The resulting html string is something like:

<!DOCTYPE html>
<html>

<head>
  <meta charset="utf-8">

  <style type="text/css">
    #T_22fdb_row2_col3,
    #T_22fdb_row3_col3 {
      color: red;
    }
  </style>

  <style>
    table,
    th,
    td {
      border: 1px solid black;
      border-collapse: collapse;
    }
    
    th,
    td {
      padding: 5px;
    }
  </style>
</head>

<body>

  <p style="font-family:verdana">Hi,</p>
  <p style="font-family:verdana">sometext</p>
  <table id="T_22fdb_">
    <thead>
      <tr>
        <th class="col_heading level0 col0">Server</th>
        <th class="col_heading level0 col1">Total size</th>
        <th class="col_heading level0 col2">Total Data in</th>
        <th class="col_heading level0 col3">Usage in %</th>
      </tr>
    </thead>
    <tbody>
      <tr>
        <td id="T_22fdb_row0_col0" class="data row0 col0">A</td>
        <td id="T_22fdb_row0_col1" class="data row0 col1">100</td>
        <td id="T_22fdb_row0_col2" class="data row0 col2">25</td>
        <td id="T_22fdb_row0_col3" class="data row0 col3">25</td>
      </tr>
      <tr>
        <td id="T_22fdb_row1_col0" class="data row1 col0">B</td>
        <td id="T_22fdb_row1_col1" class="data row1 col1">100</td>
        <td id="T_22fdb_row1_col2" class="data row1 col2">20</td>
        <td id="T_22fdb_row1_col3" class="data row1 col3">20</td>
      </tr>
      <tr>
        <td id="T_22fdb_row2_col0" class="data row2 col0">C</td>
        <td id="T_22fdb_row2_col1" class="data row2 col1">100</td>
        <td id="T_22fdb_row2_col2" class="data row2 col2">85</td>
        <td id="T_22fdb_row2_col3" class="data row2 col3">85.6</td>
      </tr>
      <tr>
        <td id="T_22fdb_row3_col0" class="data row3 col0">D</td>
        <td id="T_22fdb_row3_col1" class="data row3 col1">100</td>
        <td id="T_22fdb_row3_col2" class="data row3 col2">90</td>
        <td id="T_22fdb_row3_col3" class="data row3 col3">90.8</td>
      </tr>
    </tbody>
  </table>

  <p style="font-family:verdana">sometext</p>
  <p style="font-family:verdana">Regards</p>
  <p style="font-family:verdana">someme</p>
</body>

</html>

The more naïve way to solve this problem would be to manipulate html strings directly with formatting, however, this can lead to poorly formed html:

import numpy as np
import pandas as pd

html = """
<html>
<head>
<style> 
  table, th, td {{ border: 1px solid black; border-collapse: collapse; }}
  th, td {{ padding: 5px; }}
</style>
</head>
<body><p style="font-family:verdana">Hi,</p>
<p style="font-family:verdana">sometext</p>
{table}
<p style="font-family:verdana">sometext</p>
<p style="font-family:verdana">Regards</p>
<p style="font-family:verdana">someme</p>
</body></html>
"""

# Read in CSV
df = pd.read_csv('files/file.csv')

# Add styles using Styler apply and render to_html
table_html = df.style.apply(
    lambda s: np.where(s >= 80, 'color: red', None), subset='Usage in %'
).format(
    # Apply format string (remove insignificant zeros)
    formatter='{:g}', subset=['Total Data in', 'Usage in %']
).hide_index().to_html()

html = html.format(table=table_html)

print(html)

Which results in the following html (notice the misplaced style element):

<html>
<head>
<style> 
  table, th, td { border: 1px solid black; border-collapse: collapse; }
  th, td { padding: 5px; }
</style>
</head>
<body><p style="font-family:verdana">Hi,</p>
<p style="font-family:verdana">sometext</p>
<style type="text/css">
#T_139a3_row2_col3, #T_139a3_row3_col3 {
  color: red;
}
</style>
<table id="T_139a3_">
  <thead>
    <tr>
      <th class="col_heading level0 col0" >Server</th>
      <th class="col_heading level0 col1" >Total size</th>
      <th class="col_heading level0 col2" >Total Data in</th>
      <th class="col_heading level0 col3" >Usage in %</th>
    </tr>
  </thead>
  <tbody>
    <tr>
      <td id="T_139a3_row0_col0" class="data row0 col0" >A</td>
      <td id="T_139a3_row0_col1" class="data row0 col1" >100</td>
      <td id="T_139a3_row0_col2" class="data row0 col2" >25</td>
      <td id="T_139a3_row0_col3" class="data row0 col3" >25</td>
    </tr>
    <tr>
      <td id="T_139a3_row1_col0" class="data row1 col0" >B</td>
      <td id="T_139a3_row1_col1" class="data row1 col1" >100</td>
      <td id="T_139a3_row1_col2" class="data row1 col2" >20</td>
      <td id="T_139a3_row1_col3" class="data row1 col3" >20</td>
    </tr>
    <tr>
      <td id="T_139a3_row2_col0" class="data row2 col0" >C</td>
      <td id="T_139a3_row2_col1" class="data row2 col1" >100</td>
      <td id="T_139a3_row2_col2" class="data row2 col2" >85</td>
      <td id="T_139a3_row2_col3" class="data row2 col3" >85.6</td>
    </tr>
    <tr>
      <td id="T_139a3_row3_col0" class="data row3 col0" >D</td>
      <td id="T_139a3_row3_col1" class="data row3 col1" >100</td>
      <td id="T_139a3_row3_col2" class="data row3 col2" >90</td>
      <td id="T_139a3_row3_col3" class="data row3 col3" >90.8</td>
    </tr>
  </tbody>
</table>

<p style="font-family:verdana">sometext</p>
<p style="font-family:verdana">Regards</p>
<p style="font-family:verdana">someme</p>
</body></html>

Simple script to build the templates folder and the two template files:

import pathlib


# Code to generate the Templates and folder
pathlib.Path('./templates').mkdir(exist_ok=True)
with open('./templates/myhtml.tpl', 'w') as f:
    f.write('''
{% extends "html_table.tpl" %}
{% block table %}
<p style="font-family:verdana">Hi,</p>
<p style="font-family:verdana">sometext</p>
{{ super() }}
<p style="font-family:verdana">sometext</p>
<p style="font-family:verdana">Regards</p>
<p style="font-family:verdana">someme</p>
{% endblock table %}
'''.strip())
with open('./templates/mystyles.tpl', 'w') as f:
    f.write('''
{% extends "html_style.tpl" %}
{% block style %}
{{ super() }}
<style>
    table, th, td {
        border: 1px solid black;
        border-collapse: collapse;
    }

    th, td {
        padding: 5px;
    }
</style>
{% endblock style %}
'''.strip())

The files/file.csv content:

Server,Total size,Total Data in,Usage in %
A,100,25,25
B,100,20,20
C,100,85,85.6
D,100,90,90.8

Hi @Henry, thanks a lot for your reply. This is great and works as expected. However, I'm getting extra zeros when I add decimals in the csv file (for instance: 85.5 and 90.7) I get in html 85.500000 and 90.700000. If I'd like to exclude the zeros and keep the values as the csv files I'm not sure how to achieve that. — ayuuk ja'ay, Dec 08 '21 at 03:43
Default Styler precision is 6 decimal places @chiko I've update my answer to include a [format](https://pandas.pydata.org/docs/reference/api/pandas.io.formats.style.Styler.format.html) string to remove insignificant 0s. You can include multiple columns by changing the `subset` from `subset='Usage in %'` to a list `subset=['Total Data in', 'Usage in %']` (for example). — Henry Ecker, Dec 08 '21 at 04:15

Add header and text color in HTML table email body based on disk usage condition using Python

1 Answers1