基于Pandas在多国家苹果价格DataFrame间按行值线性插值

Alright, let's walk through how to perform linear interpolation across your country-specific apple price DataFrames and apply it to your portfolio data using Pandas. Here's a step-by-step approach that's clean and scalable:

Step 1: Consolidate All Country Data First #

Instead of managing separate DataFrames for each country, let's combine them into a single master DataFrame. This makes it easier to reference and filter data later on.

import pandas as pd

# Create individual country DataFrames (your existing data)
tenors = pd.Series(['1W', '1M', '1Y'])
days = pd.Series([7, 30, 365])

# China
apples_china_df = pd.DataFrame({
    'tenors': tenors,
    'apples_price': [5.1, 6.2, 7.1],
    'days': days,
    'country': 'China'
})

# USA
apples_usa_df = pd.DataFrame({
    'tenors': tenors,
    'apples_price': [4.8, 5.9, 6.8],
    'days': days,
    'country': 'USA'
})

# EU
apples_eu_df = pd.DataFrame({
    'tenors': tenors,
    'apples_price': [5.5, 6.7, 7.5],
    'days': days,
    'country': 'EU'
})

# Merge all into one DataFrame
all_apples_data = pd.concat(
    [apples_china_df, apples_usa_df, apples_eu_df],
    ignore_index=True
)

Step 2: Build a Reusable Interpolation Function #

We'll create a function that takes a target number of days and a country's filtered data, then returns the linearly interpolated apple price. This handles both interpolation between existing tenors and optional extrapolation for days outside your existing range.

Option 1: Using Pandas' Built-in Interpolation #

This is great if you want to stick strictly to Pandas:

def get_interpolated_price(target_day, country_data):
    # Ensure data is sorted by days (critical for accurate interpolation)
    sorted_data = country_data.sort_values('days').reset_index(drop=True)
    
    # Create a temporary series that includes our target day
    temp_series = pd.Series(
        index=sorted_data['days'].append(pd.Series([target_day])).sort_values(),
        data=sorted_data['apples_price'].append(pd.Series([None]))
    )
    
    # Perform linear interpolation; use limit_direction='both' to allow extrapolation
    # Remove that argument if you want to restrict interpolation to existing day ranges
    interpolated_price = temp_series.interpolate(
        method='linear',
        limit_direction='both'
    ).loc[target_day]
    
    return interpolated_price

Option 2: Using Scipy's Interp1d (More Flexible) #

If you need more control over interpolation/extrapolation behavior, scipy.interpolate.interp1d is a solid choice:

from scipy.interpolate import interp1d

def get_interpolated_price_scipy(target_day, country_data):
    sorted_data = country_data.sort_values('days')
    # Create interpolation function; fill_value='extrapolate' allows out-of-range days
    price_interpolator = interp1d(
        sorted_data['days'],
        sorted_data['apples_price'],
        fill_value='extrapolate',
        kind='linear'
    )
    # Return scalar value instead of array
    return price_interpolator(target_day).item()

Step 3: Apply Interpolation to Your Portfolio Data #

Assuming your portfolio DataFrame has columns for country and the target number of days (e.g., target_days), we'll use apply() to run our interpolation function on each row.

# Example portfolio data
portfolio = pd.DataFrame({
    'country': ['China', 'USA', 'EU', 'China', 'USA'],
    'target_days': [15, 45, 180, 500, 5]  # Includes days inside and outside existing ranges
})

# Apply the interpolation function (use whichever option you prefer)
portfolio['interpolated_apple_price'] = portfolio.apply(
    lambda row: get_interpolated_price(
        row['target_days'],
        all_apples_data[all_apples_data['country'] == row['country']]
    ),
    axis=1
)

# Check the result
print(portfolio)

Key Notes: #

• Sorting: Always make sure your data is sorted by days before interpolating—unsorted data will lead to wrong results.
• Extrapolation: If you don't want to allow extrapolation (e.g., target days outside 7-365), remove the limit_direction='both' (for Pandas) or fill_value='extrapolate' (for Scipy) argument. The function will return NaN for out-of-range days instead.
• Scalability: This approach works even if you add more countries later—just append their DataFrame to all_apples_data and the rest of the code stays the same.

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