如何按客户分组为DataFrame补全日期间隔中的缺失行？

Got it, let's walk through how to fill in missing date intervals per customer in your pandas DataFrame—this is a super common task for time-series data grouped by users/customers. Let's break it down with a concrete example so you can adapt it to your data.

Step 1: Set Up Example Data #

First, let's define a sample DataFrame that mirrors your scenario (adjust columns/values to match your actual data):

import pandas as pd

# Raw input data with missing dates per customer
df = pd.DataFrame({
    'customer_id': ['A', 'A', 'B', 'B'],
    'date': ['2023-01-01', '2023-01-03', '2023-01-02', '2023-01-04'],
    'value': [10, 30, 20, 40]
})

# Convert date column to datetime (critical for date range generation)
df['date'] = pd.to_datetime(df['date'])

Step 2: Define a Group Processing Function #

We'll create a function that takes a single customer's data, generates the full date range from their earliest to latest recorded date, then merges it back with the original data to fill gaps:

def fill_customer_dates(group):
    # Generate complete date sequence for the customer's date range (daily frequency here)
    full_date_range = pd.date_range(
        start=group['date'].min(),
        end=group['date'].max(),
        freq='D'  # Change this to 'W' for weekly, 'M' for monthly, etc.
    )
    
    # Create a DataFrame with the full date sequence
    full_dates_df = pd.DataFrame({'date': full_date_range})
    
    # Merge with the original group data to retain existing values
    merged = full_dates_df.merge(group, on='date', how='left')
    
    # Fill in the customer ID (since it's a group, all rows belong to the same customer)
    merged['customer_id'] = group['customer_id'].iloc[0]
    
    # Optional: Fill missing values for other columns (e.g., value)
    # Use fillna(0) if you want 0s for missing entries, or skip to leave NaN
    merged['value'] = merged['value'].fillna(0)
    
    return merged

Step 3: Apply the Function to Each Customer Group #

Now we'll group the DataFrame by customer_id and apply our function to each group:

# Apply the function and reset the index to clean up the output
filled_df = df.groupby('customer_id').apply(fill_customer_dates).reset_index(drop=True)

Step 4: Check the Result #

Running print(filled_df) will give you the complete DataFrame with no missing dates per customer:

customer_id       date  value
0           A 2023-01-01   10.0
1           A 2023-01-02    0.0
2           A 2023-01-03   30.0
3           B 2023-01-02   20.0
4           B 2023-01-03    0.0
5           B 2023-01-04   40.0

Key Customizations #

• Date Frequency: Change the freq parameter in pd.date_range to match your needs (e.g., 'H' for hourly, 'W' for weekly, 'M' for monthly).
• Missing Value Handling: Adjust the fillna line to use a different default (like fillna('N/A') for categorical columns) or remove it entirely to keep NaN for missing entries.
• Additional Columns: If you have other columns besides value, add similar fillna lines to handle their missing values.

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