how to Send dataframe as html table with font styling based on text value as a email attachment

I have a dataframe, In that if the value is starting with letter “A” i’m styling it in red color, now i need to send it as html table in mail but when i execute it its coming without that styling, below is the code i tried please help. please check the image for df style

import os
import smtplib
from email.mime.multipart import MIMEMultipart
from email.mime.text import MIMEText
import pandas as pd

def color_failed(values):
    if values.startswith("A"):
        color="Red"
    else:
        color="yellow"

    return 'color: %s' % color

def test_mail():
    try:
        server=smtplib.SMTP()
        d={"One":["Abhi","Shek"],"two":["Arjun","Echo"],"three":["Virat","Gandalf"],"four":["Emma","Amma"]}
        df=pd.DataFrame(d)
        df.style.applymap(color_failed)
 
        msg = MIMEMultipart()
        msg['Subject'] = "Testing"
        msg['From'] = mail_id
        msg['To']=mail_id
        html = """
                    <html>
                        <head>Test Email
                        <style>
                        </style>
                        </head>
                            <body>
                                {0}
                            </body>
                    </html>
                """.format(df.to_html())

        email_body = MIMEText(html, 'html')
        msg.attach(email_body)

        server.sendmail(mail_id, mail_id, msg.as_string())

JavaScript
​x
 
import os
import smtplib
from email.mime.multipart import MIMEMultipart
from email.mime.text import MIMEText
import pandas as pd
​
def color_failed(values):
    if values.startswith("A"):
        color="Red"
    else:
        color="yellow"
​
    return 'color: %s' % color
​
def test_mail():
    try:
        server=smtplib.SMTP()
        d={"One":["Abhi","Shek"],"two":["Arjun","Echo"],"three":["Virat","Gandalf"],"four":["Emma","Amma"]}
        df=pd.DataFrame(d)
        df.style.applymap(color_failed)
 
        msg = MIMEMultipart()
        msg['Subject'] = "Testing"
        msg['From'] = mail_id
        msg['To']=mail_id
        html = """
                    <html>
                        <head>Test Email
                        <style>
                        </style>
                        </head>
                            <body>
                                {0}
                            </body>
                    </html>
                """.format(df.to_html())
​
        email_body = MIMEText(html, 'html')
        msg.attach(email_body)
​
        server.sendmail(mail_id, mail_id, msg.as_string())
​

enter image description here

Answer

EDIT:

I found you can assign styled dataframe to variable and use .to_html() on this dataframe.

def color_failed(values):
    if values.startswith("A"):
        color = "red"
    else:
        color = "yellow"

    return f'color: {color}'

df_styled = df.style.applymap(color_failed)

print(df_styled.to_html())       # display HTML

df_styled.to_html('index.html')  # save in file

JavaScript
 
def color_failed(values):
    if values.startswith("A"):
        color = "red"
    else:
        color = "yellow"
​
    return f'color: {color}'
​
df_styled = df.style.applymap(color_failed)
​
print(df_styled.to_html())       # display HTML
​
df_styled.to_html('index.html')  # save in file
​

OLD ANSWER:

df.to_html() always gives HTML without styles.

You may add some parameters in to_html(....) to change something. See doc for to_html().

You may use formatters to convert value into <div style="color: red">value</div>. It may need escape=False to put it as HTML in table.

def color_failed(value):
    if value.startswith("A"):
        color = "red"
    else:
        color = "yellow"

    return f'<div style="color: {color}">{value}</div>'

df.to_html(formatters=[color_failed, color_failed, color_failed, color_failed], escape=False)

JavaScript
 
def color_failed(value):
    if value.startswith("A"):
        color = "red"
    else:
        color = "yellow"
​
    return f'<div style="color: {color}">{value}</div>'
​
df.to_html(formatters=[color_failed, color_failed, color_failed, color_failed], escape=False)
​

Every column need own formatter so I repeated it 4 times in list.

Because email is not important in this problem so I skip it and I save data in file index.html and I use webbrowser to show it automatically in browser.

import pandas as pd

def color_failed(value):
    if value.startswith("A"):
        color = "red"
    else:
        color = "yellow"

    return f'<div style="color: {color}">{value}</div>'

data = {
    "one": ["Abhi", "Shek"],
    "two": ["Arjun", "Echo"],
    "three": ["Virat", "Gandalf"],
    "four": ["Emma", "Amma"]
}

df = pd.DataFrame(data)

print(df.to_html(formatters=[color_failed, color_failed, color_failed, color_failed], escape=False))

# --- show in web browser ---

df.to_html('index.html', formatters=[color_failed, color_failed, color_failed, color_failed], escape=False)

import webbrowser
webbrowser.open('index.html')

JavaScript
 
import pandas as pd
​
def color_failed(value):
    if value.startswith("A"):
        color = "red"
    else:
        color = "yellow"
​
    return f'<div style="color: {color}">{value}</div>'
​
data = {
    "one": ["Abhi", "Shek"],
    "two": ["Arjun", "Echo"],
    "three": ["Virat", "Gandalf"],
    "four": ["Emma", "Amma"]
}
​
df = pd.DataFrame(data)
​
print(df.to_html(formatters=[color_failed, color_failed, color_failed, color_failed], escape=False))
​
# --- show in web browser ---
​
df.to_html('index.html', formatters=[color_failed, color_failed, color_failed, color_failed], escape=False)
​
import webbrowser
webbrowser.open('index.html')
​

Result:

<table border="1" class="dataframe">
  <thead>
    <tr style="text-align: right;">
      <th></th>
      <th>one</th>
      <th>two</th>
      <th>three</th>
      <th>four</th>
    </tr>
  </thead>
  <tbody>
    <tr>
      <th>0</th>
      <td><div style="color: red">Abhi</div></td>
      <td><div style="color: red">Arjun</div></td>
      <td><div style="color: yellow">Virat</div></td>
      <td><div style="color: yellow">Emma</div></td>
    </tr>
    <tr>
      <th>1</th>
      <td><div style="color: yellow">Shek</div></td>
      <td><div style="color: yellow">Echo</div></td>
      <td><div style="color: yellow">Gandalf</div></td>
      <td><div style="color: red">Amma</div></td>
    </tr>
  </tbody>
</table>

JavaScript
 
<table border="1" class="dataframe">
  <thead>
    <tr style="text-align: right;">
      <th></th>
      <th>one</th>
      <th>two</th>
      <th>three</th>
      <th>four</th>
    </tr>
  </thead>
  <tbody>
    <tr>
      <th>0</th>
      <td><div style="color: red">Abhi</div></td>
      <td><div style="color: red">Arjun</div></td>
      <td><div style="color: yellow">Virat</div></td>
      <td><div style="color: yellow">Emma</div></td>
    </tr>
    <tr>
      <th>1</th>
      <td><div style="color: yellow">Shek</div></td>
      <td><div style="color: yellow">Echo</div></td>
      <td><div style="color: yellow">Gandalf</div></td>
      <td><div style="color: red">Amma</div></td>
    </tr>
  </tbody>
</table>
​

For more complex table you may have to format it on your own (using for-loops to work with every row and column separatelly).

Advertisement

Answer