如何基于其他列条件在Pandas中实现Lead Fill填充功能

Step-by-Step Explanation #

1. Clean and Preprocess Data: Replace string-based "Nan"/"NaN" with actual NaN values, convert discount values to float, and parse date columns into datetime objects for easy range comparison.
2. Extract Promo Periods: Identify all promo start dates, corresponding end dates, and their discount values (promos are sequential with no overlaps, so starts and ends can be paired directly).
3. Check Active Status for Each Month: For each month, verify if it falls within any promo period. If yes, mark as active and set the corresponding discount; otherwise, mark inactive with 0 discount.

Complete Code #

import pandas as pd
import numpy as np

# Original dataset
events = pd.DataFrame({'yyyyww': ['2022-01','2022-02','2022-03', '2022-04','2022-05','2022-06','2022-07','2022-08','2022-09','2022-10'],
                         'promo_start': ['2022-01','Nan','2022-03','Nan','2022-05','2022-06','Nan','Nan','2022-09','Nan'],
                         'disc': ['0.1','Nan',0.2,'Nan',0.2,0.4,'Nan','Nan',0.5,'NaN'],
                         'promo_end': ['Nan', '2022-02','Nan','2022-04','2022-05','Nan','2022-07','Nan','Nan','2022-10']})

# Step 1: Clean data
events = events.replace({'Nan': np.nan, 'NaN': np.nan})
events['disc'] = events['disc'].astype(float)

# Convert date columns to datetime for comparison
events['yyyyww'] = pd.to_datetime(events['yyyyww'], format='%Y-%m')
events['promo_start'] = pd.to_datetime(events['promo_start'], format='%Y-%m')
events['promo_end'] = pd.to_datetime(events['promo_end'], format='%Y-%m')

# Step 2: Extract promo periods (start, end, discount)
promo_starts = events.loc[events['promo_start'].notna(), 'promo_start'].tolist()
promo_ends = events.loc[events['promo_end'].notna(), 'promo_end'].tolist()
promo_discs = events.loc[events['promo_start'].notna(), 'disc'].tolist()
promo_periods = list(zip(promo_starts, promo_ends, promo_discs))

# Step3: Determine active status and discount for each month
def get_promo_status(row):
    current_month = row['yyyyww']
    for start, end, disc in promo_periods:
        if start <= current_month <= end:
            return (True, disc)
    return (False, 0.0)

# Apply function to create new columns
events[['promo_active', 'promo_disc']] = events.apply(get_promo_status, axis=1, result_type='expand')

# Convert date columns back to string format to match desired output
events['yyyyww'] = events['yyyyww'].dt.strftime('%Y-%m')
events['promo_start'] = events['promo_start'].apply(lambda x: x.strftime('%Y-%m') if pd.notna(x) else 'Nan')
events['promo_end'] = events['promo_end'].apply(lambda x: x.strftime('%Y-%m') if pd.notna(x) else 'Nan')

# View the result
print(events)

Output Verification #

Running this code will produce exactly the desired_output DataFrame you specified, with promo_active correctly indicating active promo months and promo_disc showing the applicable discount (0 for inactive months).

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