使用Pandas条件更新多重索引DataFrame：货币转换需求实现

Got it, let's tackle this problem step by step. The core challenge is aligning the exchange rates from frame1 with the country-year values in frame2, then applying the conversion logic only to non-EURO currencies. Below are two practical, reproducible methods using pandas.

First: Set Up Sample Data (for testing) #

Let's recreate your DataFrames to ensure our code works as expected:

import pandas as pd

# frame1 as provided
frame1 = pd.DataFrame({
    'Loc': ['FRA', 'CAN', 'MEX'],
    'Time': [2008, 2007, 2010],
    'Code': ['F-', 'G', 'I'],
    'Value': [1.224, 1.99, 3.55]
})

# frame2 as provided
frame2 = pd.DataFrame({
    'Country': ['FRANCE', 'CANADA', 'MEXICO'],
    'Ccy': ['EURO', 'CANADIAN', 'Peso'],
    '2007': [5.225, 53.65, 15434.154],
    '2008': [6.299, 4.445, 14564.3],
    '2009': [7.555, 8.445, 4.455]
})

Method 1: Melt + Merge + Pivot (Clean, Scalable) #

This method reshapes frame2 to long format for easier rate matching, then converts back to the original wide structure. It's ideal for larger datasets:

1. Map Country Abbreviations to Full Names
   First, link frame1's short Loc codes to frame2's full country names:

   country_map = {
       'FRA': 'FRANCE',
       'CAN': 'CANADA',
       'MEX': 'MEXICO'
   }
   

2. Prepare Exchange Rate Lookup
   Transform frame1 into a lookup table indexed by (Country, Year):

   rate_lookup = frame1.assign(Country=frame1['Loc'].map(country_map)) \
                       .set_index(['Country', 'Time'])['Value']
   

3. Reshape frame2 to Long Format
   Convert year columns into rows to simplify merging with rates:

   frame2_long = frame2.melt(
       id_vars=['Country', 'Ccy'],
       var_name='Year',
       value_name='Local_Value'
   )
   # Convert Year to integer to match frame1's Time data type
   frame2_long['Year'] = frame2_long['Year'].astype(int)
   

4. Merge Rates & Convert to Euros
   Match exchange rates and apply conversion logic (keep EURO values as-is, multiply others by the rate):

   merged = frame2_long.merge(
       rate_lookup.reset_index(),
       on=['Country', 'Year'],
       how='left'
   )
   
   # Calculate euro values (keep original if no rate exists)
   merged['Euro_Value'] = merged.apply(
       lambda row: row['Local_Value'] if row['Ccy'] == 'EURO' 
       else row['Local_Value'] * row['Value'] if pd.notna(row['Value']) 
       else row['Local_Value'],
       axis=1
   )
   

5. Restore Wide Format
   Convert back to frame2's original structure:

   result = merged.pivot(
       index=['Country', 'Ccy'],
       columns='Year',
       values='Euro_Value'
   ).reset_index()
   result.columns.name = None  # Clean up the column index label
   

Method 2: Row-by-Row Apply (Simpler for Small Datasets) #

If you prefer working directly with the original frame2 structure, use apply to process each row individually:

1. Create Exchange Rate Dictionary

   rate_dict = frame1.replace({'Loc': country_map}) \
                     .set_index(['Loc', 'Time'])['Value'] \
                     .to_dict()
   

2. Define Conversion Function

   def convert_row_to_euro(row):
       country = row['Country']
       ccy = row['Ccy']
       # Iterate over each year column
       for year_col in ['2007', '2008', '2009']:
           if ccy == 'EURO':
               continue
           # Fetch the matching exchange rate
           rate = rate_dict.get((country, int(year_col)))
           if rate is not None:
               row[year_col] = row[year_col] * rate
       return row
   

3. Apply the Function

   result = frame2.apply(convert_row_to_euro, axis=1)
   

Key Notes #

• Exchange Rate Direction: If frame1's Value represents 1 Euro = X Local Currency (instead of 1 Local Currency = X Euros), replace the multiplication (*) with division (/). Adjust based on your actual rate definition.
• Missing Rates: The code retains original values for years where no rate exists (e.g., 2009 in frame1). You can modify this to set NaN or a default value if needed.
• Type Consistency: Ensuring Year/Time are the same data type (integer) is critical for matching rates correctly.

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