Pandas中DataFrame刷新时自动计算预估字段m2_estimated与m3_estimated

Got it, let's work through this problem to get your desired output and set up automatic recalculation whenever your DataFrame updates. Here's a step-by-step solution:

Step 1: Full Working Code #

First, let's start with the complete code that handles date conversion, calculates the estimated fields, and ensures the logic is reusable:

import pandas as pd
import numpy as np

# Your original DataFrame
d = {'ID': [0, 1, 2, 3, 4], 
     'm1': ['2019-12-06', '2019-12-07','2019-12-07', '2019-12-06', '2020-12-09'], 
     'm2': ['2019-12-07', None, None, '2019-12-07', None], 
     'm3': [None, None, None, '2019-12-09', None], 
     'm1_m2': [1, 1, 2, 2, 3], 
     'm2_m3': [3, 3, 4, 1, 2]}
dat = pd.DataFrame(d)

# Convert string dates to datetime type (critical for date arithmetic)
dat[['m1', 'm2', 'm3']] = dat[['m1', 'm2', 'm3']].apply(pd.to_datetime)

# Function to compute estimated dates (call this whenever your DataFrame updates)
def refresh_estimated_dates(df):
    # Calculate m2_estimated: use m1 + m1_m2 days when m2 is missing
    df['m2_estimated'] = np.where(
        df['m2'].isna(),
        df['m1'] + pd.to_timedelta(df['m1_m2'], unit='D'),
        None
    )
    
    # Calculate m3_estimated: use m2 if available, else m2_estimated, plus m2_m3 days when m3 is missing
    base_m3_date = np.where(df['m2'].notna(), df['m2'], df['m2_estimated'])
    df['m3_estimated'] = np.where(
        df['m3'].isna(),
        base_m3_date + pd.to_timedelta(df['m2_m3'], unit='D'),
        None
    )
    
    # Convert NaT (datetime nulls) back to None to match your expected output formatting
    df[['m2_estimated', 'm3_estimated']] = df[['m2_estimated', 'm3_estimated']].replace({pd.NaT: None})
    
    return df

# Apply the function to get your final DataFrame
dat = refresh_estimated_dates(dat)

# Print the result to verify
print(dat.to_string(index=False))

Step 2: What This Code Does #

Let's break down the key parts:

• Date Conversion: We first turn the string date columns (m1, m2, m3) into proper datetime objects. This lets us easily add days to dates using Pandas' timedelta functionality.
• m2_estimated Logic: We use np.where to check if m2 is missing. If it is, we add m1_m2 days to m1; if m2 exists, we set m2_estimated to None.
• m3_estimated Logic: For this field, we first pick the correct base date: if m2 is present, we use that; if not, we use the computed m2_estimated. Then we add m2_m3 days to this base date only when m3 is missing.
• Auto-Execution: By wrapping the calculation logic in refresh_estimated_dates(), you can call this function every time your DataFrame is updated (e.g., after adding rows, modifying existing values, or refreshing source data). Just run dat = refresh_estimated_dates(dat) to update the estimated fields instantly.

Note on Expected Output Discrepancy #

In your provided expected output, the m2_estimated for ID=4 is listed as 2019-12-12, but according to the stated logic (m1 + m1_m2 days), it should be 2020-12-12 (since 2020-12-09 + 3 days = 2020-12-12). The code above follows your specified calculation rules correctly.

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