基于Pandas生成相对日期列、计算间隔天数及插值的技术问询

Got it, let's break this down step by step. Here's how you can add the required columns and handle date propagation/interpolation for your sales data:

Step 1: Import Required Libraries #

We'll use pandas for data handling, datetime to get the current date, and dateutil.relativedelta to accurately add weeks/months/years (since adding fixed days for months isn't reliable—think February vs March).

import pandas as pd
from datetime import date
from dateutil.relativedelta import relativedelta

Step 2: Set Up Your Existing Data #

Your initial code to create the DataFrame stays exactly as you have it:

time_pillars = pd.Series(['1W', '1M', '3M', '1Y'])
sales = pd.Series([4.75, 5.00, 5.10, 5.75])
data = {'time_pillar': time_pillars, 'sales': sales}
df = pd.DataFrame(data)

Step 3: Add date and days_from_now Columns #

We'll write a small helper function to convert each time pillar (like '1W') into an actual future date relative to today. Then we'll calculate how many days each date is from now.

today = date.today()

def get_future_date(time_str):
    # Pull out the number and unit from the time string (e.g., '1' and 'W' from '1W')
    time_num = int(time_str[:-1])
    time_unit = time_str[-1]
    
    # Use relativedelta to add the correct interval
    if time_unit == 'W':
        return today + relativedelta(weeks=time_num)
    elif time_unit == 'M':
        return today + relativedelta(months=time_num)
    elif time_unit == 'Y':
        return today + relativedelta(years=time_num)
    else:
        raise ValueError(f"Unsupported time unit: {time_unit}")

# Add the future date column (convert to pandas datetime type for consistency)
df['date'] = pd.to_datetime(df['time_pillar'].apply(get_future_date))

# Calculate days between each future date and today
df['days_from_now'] = (df['date'] - pd.to_datetime(today)).dt.days

After this step, your DataFrame will look something like this (example based on today's date):

Step 4: Date Propagation and Sales Interpolation #

If you want to fill in daily dates between today and the farthest future date, and interpolate sales values for those intermediate dates, here's how to do it:

# Create a continuous daily date range from today to the last future date in your data
full_daily_range = pd.date_range(start=today, end=df['date'].max(), freq='D')

# Set 'date' as the index and reindex to the full daily range
df_interpolated = df.set_index('date').reindex(full_daily_range)

# Interpolate missing sales values (linear interpolation works well for smooth trends)
df_interpolated['sales'] = df_interpolated['sales'].interpolate(method='linear')

# Add back the days_from_now column for all daily dates
df_interpolated['days_from_now'] = (df_interpolated.index - pd.to_datetime(today)).days

# Optional: Reset index to make 'date' a regular column again
df_interpolated = df_interpolated.reset_index().rename(columns={'index': 'date'})

Now you have a daily sequence where sales values are smoothly interpolated between your original data points. You can adjust the interpolation method (e.g., method='quadratic' for curved trends) if needed.

Quick Notes #

• Using relativedelta ensures accurate month/year calculations (e.g., adding 1 month to January 31st gives February 29th in a leap year, not March 2nd).
• If you want to include today as a starting point in the interpolated data, just add a row to the original df with time_pillar='0D' and your current sales value before running the interpolation.

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