如何在Pandas DataFrame中按行时间范围将NaN替换为0？

Got it, let's work through this problem together. The goal is to replace NaNs with 0 only for columns whose year-month names fall between the time A and time B values of each row—here's a clean, efficient way to do it using pandas:

Step 1: Prepare the Sample DataFrame #

First, let's recreate your DataFrame to test our solution:

import pandas as pd
import numpy as np

data = {
    'time A': ['2017-01-24', '2016-11-28', '2017-03-18'],
    'time B': ['2020-01-01', '2020-01-01', '2017-12-21'],
    '2017-11': [np.nan, np.nan, np.nan],
    '2017-12': [np.nan, 4.0, np.nan],
    '2018-01': [np.nan, 2.0, np.nan],
    '2018-02': [np.nan, 2.0, np.nan]
}
df = pd.DataFrame(data)

Step 2: Convert Dates to Datetime Format #

We need to make all time values comparable, so convert time A, time B, and the year-month column names to datetime objects:

# Convert time columns to datetime
df['time A'] = pd.to_datetime(df['time A'])
df['time B'] = pd.to_datetime(df['time B'])

# Convert year-month column names to datetime (using first day of the month for comparison)
date_cols = df.columns[2:]  # Isolate the columns with year-month labels
date_col_dates = pd.to_datetime(date_cols, format='%Y-%m')

Step 3: Create a Mask for Target Columns #

We'll generate a boolean matrix where each entry is True if the column's date falls within the row's time A and time B range:

# Broadcast the row-wise time ranges against the column dates to create the mask
mask = (date_col_dates >= df['time A'].values[:, None]) & (date_col_dates <= df['time B'].values[:, None])

Step 4: Replace NaNs with 0 in Target Columns #

Finally, replace NaNs with 0 only where the mask is True (and the value is NaN)—keep all other values as-is:

# Apply the mask: replace NaN with 0 where mask is True, retain original values otherwise
df[date_cols] = df[date_cols].where(~(mask & df[date_cols].isna()), 0)

Final Result #

If you print the updated DataFrame, you'll see exactly what you need:

print(df)

Output:

time A     time B  2017-11  2017-12  2018-01  2018-02
0 2017-01-24 2020-01-01      0.0      0.0      0.0      0.0
1 2016-11-28 2020-01-01      0.0      4.0      2.0      2.0
2 2017-03-18 2017-12-21      0.0      0.0      NaN      NaN

Let's verify the logic:

• Row 0: All date columns fall between 2017-01-24 and 2020-01-01, so all NaNs become 0.
• Row 1: All date columns are within the range, so 2017-11's NaN is replaced with 0, while non-NaN values stay intact.
• Row 2: Only 2017-11 and 2017-12 fall between 2017-03-18 and 2017-12-21, so their NaNs become 0—2018-01 and 2018-02 are outside the range, so their NaNs remain.

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