Python Pandas：利用currency_converter按年份转换金额至GBP

Got it, let's work through this problem step by step. Since you can only convert to/from EUR with your tool, we'll do a two-step conversion: original currency → EUR → GBP, using the exchange rate from the fund's start year.

Step 1: Install the required library #

First, make sure you have the currency-converter package installed (since that's what you're using):

pip install currency-converter

Step 2: Write the conversion logic #

We'll create a custom function to handle the two-step conversion for each row, using the historical rate from the fund's start year. We'll pick December 31st of the start year for a consistent annual rate—you can adjust this to January 1st or another date if needed.

Here's the full code snippet tailored to your DataFrame structure:

import pandas as pd
from currency_converter import CurrencyConverter, RateNotFoundError

# Your existing DataFrame (example included for reference)
dfFF = pd.DataFrame({
    'Sector': ['Public', 'Private'],
    'Country': ['USA', 'Hong Kong'],
    'Currency': ['USD', 'HKD'],
    'Amount': [22000, 42000],
    'Fund Start Year': [2016, 2015]
})

def convert_to_gbp(row):
    c = CurrencyConverter()
    original_curr = row['Currency']
    amount = row['Amount']
    start_year = row['Fund Start Year']
    # Use year-end date for consistent historical rate
    rate_date = f"{start_year}-12-31"
    
    try:
        # Step 1: Convert original currency to EUR
        amount_eur = c.convert(amount, original_curr, 'EUR', date=rate_date)
        # Step 2: Convert EUR to GBP
        amount_gbp = c.convert(amount_eur, 'EUR', 'GBP', date=rate_date)
        return round(amount_gbp, 2)  # Round to 2 decimal places for currency formatting
    except RateNotFoundError:
        # Handle cases where no rate exists for the given year/currency
        return None

# Add the new converted column to your DataFrame
dfFF['Amount (GBP)'] = dfFF.apply(convert_to_gbp, axis=1)

# Check the result
print(dfFF)

Quick notes to tweak as needed: #

• Historical Date: If you have the exact fund start date instead of just the year, replace rate_date with that specific date string (e.g., "2016-05-15").
• Error Handling: The RateNotFoundError catch returns None for missing rates—you can modify this to return a default value (like 0) or log a warning instead.
• Performance: For very large DataFrames, apply might be slow. You could batch process conversions or use vectorized operations, but this approach works great for most standard datasets.

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