如何按Bodlane分组为DataFrame DF补全缺失日期？

Got it, here's how you can fill in the missing dates for each Bodlane group in your DataFrame using pandas. Let's break this down into clear, actionable steps:

Step 1: Prepare the Input Data #

First, let's replicate your input DataFrame and ensure the date column is in the correct format (critical for date operations):

import pandas as pd

# Your input DataFrame
df = pd.DataFrame({
    'Bodlane': ['185_AB1', '185_AB1', '201_AB1', '201_AB1'],
    'Day': ['2020-05-01', '2020-05-05', '2020-05-02', '2020-05-05'],
    'Holiday': [True, True, True, True]
})

# Convert 'Day' to datetime dtype so pandas recognizes it as dates
df['Day'] = pd.to_datetime(df['Day'])

Step 2: Expand Dates for Each Bodlane Group #

We'll group the DataFrame by Bodlane, generate a full date range for each group (from the earliest to latest date in that group), then merge back with the original data to retain existing Holiday values and fill missing ones with NaN (pandas' equivalent of NA):

# Function to expand dates for a single Bodlane group
def expand_group_dates(group):
    # Create a continuous date range for the group
    full_date_range = pd.date_range(start=group['Day'].min(), end=group['Day'].max(), freq='D')
    # Make a new DataFrame with all dates for the current Bodlane
    expanded_dates = pd.DataFrame({'Day': full_date_range, 'Bodlane': group['Bodlane'].iloc[0]})
    # Merge with original group to keep existing Holiday values, fill missing with NaN
    return expanded_dates.merge(group, on=['Bodlane', 'Day'], how='left')

# Apply the function to each group and combine results
result_df = df.groupby('Bodlane').apply(expand_group_dates).reset_index(drop=True)

Step 3: View the Final Result #

Printing result_df will give you exactly the output you're expecting. Missing Holiday values show as NaN, which aligns with your desired NA output:

Bodlane        Day Holiday
0  185_AB1 2020-05-01    True
1  185_AB1 2020-05-02     NaN
2  185_AB1 2020-05-03     NaN
3  185_AB1 2020-05-04     NaN
4  185_AB1 2020-05-05    True
5  201_AB1 2020-05-02    True
6  201_AB1 2020-05-03     NaN
7  201_AB1 2020-05-04     NaN
8  201_AB1 2020-05-05    True

If you specifically want the string NA instead of NaN, add this line after generating result_df:

result_df['Holiday'] = result_df['Holiday'].fillna('NA')

That's all! This method ensures each Bodlane has every consecutive date between its first and last recorded entry, with original Holiday values preserved and gaps filled appropriately.

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