Issue
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?
Solution
We just need np.where here instead of an inline if else.
Some other notes:
- When creating a Styler object from
DataFrame.stylewe should save that as a variable calledstyleror something similar instead ofdfto denote the type of object has changed from DataFrame to Styler - We do not need to assign back when working with adding additional styles to an already declared Styler object
- We need
np.whereto create an ndarray of styles based on all of the boolean values produced by doing boolean operations on a Series - We should prefer the empty string (
'') to represent no styles overNone(for consistent dtype processing) - Instead of tying to use python string formatting we should just use (as of pandas 1.3.0)
Styler.to_htmlwithdoctype_html=Trueinstead ofStyler.render
# 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
})
Answered By - Henry Ecker
0 comments:
Post a Comment
Note: Only a member of this blog may post a comment.