如何在Python中基于预设汇率批量转换DataFrame薪资货币？

Hey there! I've got you covered—this is a common task with pandas, and it's totally manageable even with your large DataFrame. Let's break it down step by step:

Step 1: Import Pandas and Load Your Data #

First, make sure you've got pandas installed, then read in both your salary dataset and the exchange rate CSV:

import pandas as pd

# Load your salary DataFrame (adjust the file path/format as needed)
df_salary = pd.read_csv("salary_data.csv")  # or pd.read_excel(), etc.

# Load the exchange rate CSV
df_exchange = pd.read_csv("exchange_rates.csv")

Step 2: Align and Merge the DataFrames #

The key here is to match the Currency column in your salary data with the currency code column in your exchange rate table (you mentioned it's CurrencyCode in your example). We'll use a left join to ensure we keep every row from your salary DataFrame, even if there's no matching exchange rate (we'll handle that next):

# Merge the two tables on their currency columns
merged_df = df_salary.merge(
    df_exchange[["CurrencyCode", "ExchangeRate"]],  # Only keep the columns we need
    left_on="Currency",
    right_on="CurrencyCode",
    how="left"
)

Pro tip: Before merging, double-check that your currency codes are consistent (same case, no extra spaces). If not, standardize them:

# Force all currency codes to uppercase to avoid mismatches
df_salary["Currency"] = df_salary["Currency"].str.upper()
df_exchange["CurrencyCode"] = df_exchange["CurrencyCode"].str.upper()

Step 3: Calculate AUD Salary #

Now multiply the original salary by the matching exchange rate. First, though, handle any missing exchange rates (rows where no match was found in the rate table):

# Optional: Check for missing rates (to avoid silent failures)
missing_rate_rows = merged_df[merged_df["ExchangeRate"].isna()]
if len(missing_rate_rows) > 0:
    print(f"⚠️ Warning: {len(missing_rate_rows)} entries have no matching exchange rate")
    # You can choose to drop these rows, fill with a default, or investigate further

# Fill missing rates if needed (example: fill with 0, but adjust based on your needs)
merged_df["ExchangeRate"] = merged_df["ExchangeRate"].fillna(0)

# Calculate the converted salary in AUD
merged_df["Salary_AUD"] = merged_df["Salary"] * merged_df["ExchangeRate"]

Important note: Confirm your exchange rate direction! If your rate table shows 1 unit of the original currency = X AUD, then multiplying is correct (like your example where USD = 1, so 70000 USD * 1 = 70000 AUD). If instead it's 1 AUD = X original currency, you'll need to divide instead:

# Only use this if your rate is AUD to original currency
merged_df["Salary_AUD"] = merged_df["Salary"] / merged_df["ExchangeRate"]

Example Output #

Using your sample data:

That's it! Your converted salaries will be in the new Salary_AUD column, ready for analysis.

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