Multiple formatting rules applied on HTML-exported DataFrame?

user_14382183 0
Viewed 420

I need to paste a pandas DataFrame into the HTML body of a newly generated email draft through a function. The problem is, I need to apply more than one conditional styling to it before it ends in the email draft.

import datetime as dt
import pandas as pd
import win32com.client as win32

def create_mail(text, subject, recipient):
    outlook = win32.Dispatch('outlook.application')
    mail = outlook.CreateItem(0)
    mail.To = recipient
    mail.Subject = subject
    mail.HtmlBody = text
    mail.save()

df = pd.DataFrame(data = {'Number': [100.00, -100.00], 'Date': [dt.date(2020, 1, 1), dt.date(2022, 1, 1)]})
df['Date'] = pd.to_datetime(df['Date']).dt.date

The styles are defined as follows:

def style_negative(v, props = 'color:red;'):
    return props if v < 0 else None
def style_red_date(v, props = 'color:red;'):
    return props if v < dt.datetime.now().date() else None

Negative numbers in Number column must be colored red. Dates falling before today in Date column must be colored red as well.

If I apply only one (either) style to the DataFrame object through df.style.applymap(), it works perfectly fine. However, when I want to apply another style to the (now) Styler object through .apply()

df = df.style.applymap(style_negative, subset = ['Number'])
df = df.apply(style_red_date, subset = ['Date'])

I get the following error:

ValueError: The truth value of a Series is ambiguous. Use a.empty, a.bool(), a.item(), a.any() or a.all().

The rest of my code:

html = (
    df
    .format({'Number':"{:.2f}"})
    .set_properties(**{'font-size':'11pt'})
    .hide_index()
    .render()
    )

mail_content = """
<html><head></head><body>
{0}
</body></html>
""".format(html)

create_mail(mail_content, "Subject", "")

I've read through this question as to export a style from Styler object, but it seems that it can only be used on a DataFrame object. Is there a way to have multiple formatting rules applied to a DataFrame / Styler object and have it exported as HTML? Am I missing something trivial here?

pandas-styles pandas dataframe formatting python
1 Answers

We just need np.where here instead of an inline if else.

Some other notes:

  1. When creating a Styler object from DataFrame.style we should save that as a variable called styler or something similar instead of df to denote the type of object has changed from DataFrame to Styler
  2. We do not need to assign back when working with adding additional styles to an already declared Styler object
  3. We need np.where to create an ndarray of styles based on all of the boolean values produced by doing boolean operations on a Series
  4. We should prefer the empty string ('') to represent no styles over None (for consistent dtype processing)
  5. Instead of tying to use python string formatting we should just use (as of pandas 1.3.0) Styler.to_html with doctype_html=True instead of Styler.render
    • if needing more complex HTML manipulation a Subclass should be used rather than attempting string manipulation on the results of render/to_html
# import numpy as np


def style_negative(v, props='color:red;'):
    return np.where(v < 0, props, '')


def style_red_date(v, props='color:red;'):
    return np.where(v < dt.datetime.now().date(), props, '')


styler = df.style.applymap(style_negative, subset=['Number'])
styler.apply(style_red_date, subset=['Date'])

mail_content = (
    styler
        .format({'Number': "{:.2f}"})
        .set_properties(**{'font-size': '11pt'})
        .hide_index()
        .to_html(doctype_html=True)
)

We can also do a single chain of styles like:

# Using same modified functions as above but as a function chain:
mail_content = df.style.applymap(
    style_negative, subset=['Number']
).apply(
    style_red_date, subset=['Date']
).format(
    {'Number': "{:.2f}"}
).set_properties(
    **{'font-size': '11pt'}
).hide_index().to_html(doctype_html=True)

These options produce the following HTML in mail_content:

<!DOCTYPE html>
<html>

<head>
  <meta charset="">
  <style type="text/css">
    #T_0f6b6_row0_col0,
    #T_0f6b6_row1_col1 {
      font-size: 11pt;
    }
    
    #T_0f6b6_row0_col1,
    #T_0f6b6_row1_col0 {
      color: red;
      font-size: 11pt;
    }
  </style>
</head>

<body>
  <table id="T_0f6b6_">
    <thead>
      <tr>
        <th class="col_heading level0 col0">Number</th>
        <th class="col_heading level0 col1">Date</th>
      </tr>
    </thead>
    <tbody>
      <tr>
        <td id="T_0f6b6_row0_col0" class="data row0 col0">100.00</td>
        <td id="T_0f6b6_row0_col1" class="data row0 col1">2020-01-01</td>
      </tr>
      <tr>
        <td id="T_0f6b6_row1_col0" class="data row1 col0">-100.00</td>
        <td id="T_0f6b6_row1_col1" class="data row1 col1">2022-01-01</td>
      </tr>
    </tbody>
  </table>
</body>

</html>

Setup:

import datetime as dt

import numpy as np
import pandas as pd

# Reproducible example with year offset so styles will always
# be reproducible same even in future years 
# (even though date values will change) 
df = pd.DataFrame({
    'Number': [100.00, -100.00],
    'Date': pd.to_datetime(
        [dt.date(dt.datetime.now().year - 1, 1, 1),
         dt.date(dt.datetime.now().year + 1, 1, 1)]
    ).date
})
user_154978880
Related