pythonhtmlpandasdataframesmtp

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())

enter image description here


Solution

  • 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
    

    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)
    

    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')
    

    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>
    

    enter image description here


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