如何为Pandas透视表同时应用货币格式化与颜色样式？

Here's a practical solution to apply your desired number formatting and conditional styling, then export to HTML suitable for email:

1. Sample Pivot Table Setup #

First, create a sample pivot table (replace this with your actual data):

import pandas as pd
import numpy as np

# Sample dataset
data = {
    'Category': ['A', 'A', 'B', 'B'],
    'Var 1': [1500, -1200, 800, -900],
    'Var 2': [-1500, 1200, -800, 900],
    'Total': [3000, -3000, 1600, -1600]
}
df = pd.DataFrame(data)
pivot_table = df.pivot_table(index='Category', values=['Var 1', 'Var 2', 'Total'])

2. Custom Currency Formatting Function #

Define a function to format numbers exactly as requested: $ prefix, thousands separator, no decimals, negative values wrapped in parentheses.

def format_currency(value):
    if pd.isna(value):
        return ''  # Handle missing values cleanly
    if value >= 0:
        return f'${value:,.0f}'
    else:
        # Format negatives as $(X,XXX)
        return f'${abs(value):,.0f}'.replace('$', '$(') + ')'

3. Combine Formatting and Conditional Styling #

Use Pandas Styler to apply both the number format and color rules:

# Initialize styler object
styler = pivot_table.style

# Apply currency formatting to all numeric columns
styler = styler.format(format_currency)

# Define conditional highlighting logic
def highlight_extreme_values(value):
    if value < -1000:
        return 'color: green'
    elif value > 1000:
        return 'color: red'
    return ''  # No styling for values within the -1000 to 1000 range

# Apply highlighting to Var 1 and Var 2 columns
styler = styler.applymap(highlight_extreme_values, subset=['Var 1', 'Var 2'])

4. Export to HTML for Email #

Convert the styled table to HTML (uses inline CSS, which works with most email clients):

# Generate final HTML content
html_content = styler.to_html()

# Optional: Save to file to preview before sending
with open('formatted_pivot.html', 'w') as file:
    file.write(html_content)

Key Notes #

• Inline CSS ensures compatibility with email clients that block external stylesheets.
• Adjust the subset parameter in applymap if you need to target different columns.
• Modify the format_currency function's NaN handling if you need a placeholder like "N/A" instead of an empty string.

内容的提问来源于stack exchange，提问作者tonytone