基于DataFrame与Jinja2为每位投资者生成双货币报表的技术求助

Here's a step-by-step implementation to generate per-investor PDF reports with currency-specific tables:

1. Prepare Your DataFrame #

First, organize your data to group each investor's transactions by currency. Using pandas, we'll create a nested structure where each investor maps to their USD and EUR transaction data.

import pandas as pd

# Replace with your Excel import: pd.read_excel("investors.xlsx")
df = pd.DataFrame({
    "Investor": ["Alice", "Alice", "Bob", "Bob", "Charlie"],
    "Currency": ["USD", "EUR", "USD", "EUR", "USD"],
    "Amount": [1000, 900, 1500, 1300, 2000],
    "Transaction_Date": ["2024-01-01", "2024-01-02", "2024-01-03", "2024-01-04", "2024-01-05"],
    "Description": ["Deposit", "Withdrawal", "Deposit", "Deposit", "Withdrawal"]
})

# Group data by investor and currency
investor_data = {}
currency_list = ["USD", "EUR"]

for investor in df["Investor"].unique():
    investor_subset = df[df["Investor"] == investor]
    investor_data[investor] = {
        curr: investor_subset[investor_subset["Currency"] == curr] for curr in currency_list
    }

# Define currency-specific column mappings for table headers
column_mappings = {
    "USD": {
        "Amount": "USD Amount",
        "Transaction_Date": "USD Transaction Date",
        "Description": "USD Transaction Description"
    },
    "EUR": {
        "Amount": "EUR Amount",
        "Transaction_Date": "EUR Transaction Date",
        "Description": "EUR Transaction Description"
    }
}

2. Create Jinja2 HTML Template #

Write an HTML template that loops through each investor, then renders a table for each currency (only if data exists). The template uses dynamic column headers and currency-specific formatting.

Save this as report_template.html:

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <title>Investor Transaction Reports</title>
    <style>
        body { font-family: Arial, sans-serif; margin: 20px; }
        .investor-block { margin-bottom: 30px; padding-bottom: 20px; border-bottom: 1px solid #ccc; page-break-inside: avoid; }
        table { border-collapse: collapse; width: 100%; margin: 10px 0; }
        th, td { border: 1px solid #ddd; padding: 8px; text-align: left; }
        th { background-color: #f5f5f5; }
        .currency-header { color: #2c3e50; }
        .usd-amount { color: #27ae60; }
        .eur-amount { color: #2980b9; }
    </style>
</head>
<body>
{% for investor, currency_data in investor_data.items() %}
<div class="investor-block">
    <h1>Investor: {{ investor }}</h1>
    
    {% for currency in ["USD", "EUR"] %}
        {% set curr_df = currency_data[currency] %}
        {% if not curr_df.empty %}
            <h2 class="currency-header">{{ currency }} Transactions</h2>
            <table>
                <thead>
                    <tr>
                        {% for col in curr_df.columns %}
                            {% if col not in ["Investor", "Currency"] %}
                                <th>{{ column_mappings[currency][col] }}</th>
                            {% endif %}
                        {% endfor %}
                    </tr>
                </thead>
                <tbody>
                    {% for _, row in curr_df.iterrows() %}
                        <tr>
                            {% for col in curr_df.columns %}
                                {% if col not in ["Investor", "Currency"] %}
                                    {% if col == "Amount" %}
                                        <td class="{{ currency.lower() }}-amount">
                                            {{ "$" if currency == "USD" else "€" }}{{ row[col] }}
                                        </td>
                                    {% else %}
                                        <td>{{ row[col] }}</td>
                                    {% endif %}
                                {% endif %}
                            {% endfor %}
                        </tr>
                    {% endfor %}
                </tbody>
            </table>
        {% else %}
            <p>No {{ currency }} transactions found for {{ investor }}.</p>
        {% endif %}
    {% endfor %}
</div>
{% endfor %}
</body>
</html>

3. Render HTML with Jinja2 #

Use Jinja2 to inject your structured data into the template and generate the full HTML content:

from jinja2 import Environment, FileSystemLoader

# Initialize Jinja2 environment
env = Environment(loader=FileSystemLoader("."))
template = env.get_template("report_template.html")

# Render HTML with data
rendered_html = template.render(
    investor_data=investor_data,
    column_mappings=column_mappings
)

# Save rendered HTML (optional, for debugging)
with open("rendered_report.html", "w") as f:
    f.write(rendered_html)

4. Convert HTML to PDF with WeasyPrint #

Finally, use WeasyPrint to convert the rendered HTML into a PDF document:

from weasyprint import HTML

# Generate PDF
HTML(string=rendered_html).write_pdf("investor_reports.pdf")

Key Notes #

• Handle Missing Currency Data: The template checks if a currency has transactions for an investor and skips empty tables or shows a message.
• Dynamic Columns: Using column_mappings lets you easily customize headers without hardcoding.
• Formatting: The template includes basic CSS for readability, and currency-specific styling for amounts.
• Page Breaks: The page-break-inside: avoid CSS rule prevents investor sections from splitting across pages.

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