Pandas多索引分组时序数据扩展：新增new_date列需求

Got it, let's walk through solving this problem step by step. The core goal is: for each unique id, find the earliest timestamp that comes after the date column and has a value greater than 0, then add that timestamp as a new new_date column to every row of the original DataFrame.

Step 1: Prepare & Clean the Data #

First, let's recreate your sample input and make sure our date columns are properly formatted as datetime objects—this is critical for accurate timestamp comparisons.

import pandas as pd

# Sample input data
data = {
    'id': [1,1,1,1,1,2,2,2,2,2],
    'timestamp': ['2001-01-01', '2001-10-01', '2001-10-02', '2001-10-03', '2001-10-04',
                  '2001-01-01', '2001-10-01', '2001-10-02', '2001-10-03', '2001-10-04'],
    'date': ['2001-05-01']*10,
    'value': [1,0,1,0,1,1,0,0,0,1]
}

df = pd.DataFrame(data)

# Convert string dates to datetime objects (required for comparison)
df['timestamp'] = pd.to_datetime(df['timestamp'])
df['date'] = pd.to_datetime(df['date'])

Step 2: Calculate the First Valid Timestamp per ID #

We'll group the DataFrame by id, then for each group:

1. Filter rows where timestamp is after date and value is greater than 0
2. Grab the earliest (minimum) timestamp from those filtered rows
3. Merge this result back to the original DataFrame so every row for an id gets the same new_date

# Group by id to find the first valid timestamp for each group
first_valid_ts = df.groupby('id').apply(
    lambda group: group[(group['timestamp'] > group['date']) & (group['value'] > 0)]['timestamp'].min()
).reset_index(name='new_date')

# Merge the result with the original DataFrame
df = df.merge(first_valid_ts, on='id', how='left')

# Optional: Convert datetime back to string format to match your example output
df['new_date'] = df['new_date'].dt.strftime('%Y-%m-%d')

Step 3: Verify the Output #

Running the code above will produce exactly the expected result:

id  timestamp       date  value    new_date
0   1 2001-01-01 2001-05-01      1  2001-10-02
1   1 2001-10-01 2001-05-01      0  2001-10-02
2   1 2001-10-02 2001-05-01      1  2001-10-02
3   1 2001-10-03 2001-05-01      0  2001-10-02
4   1 2001-10-04 2001-05-01      1  2001-10-02
5   2 2001-01-01 2001-05-01      1  2001-10-04
6   2 2001-10-01 2001-05-01      0  2001-10-04
7   2 2001-10-02 2001-05-01      0  2001-10-04
8   2 2001-10-03 2001-05-01      0  2001-10-04
9   2 2001-10-04 2001-05-01      1  2001-10-04

Edge Case Notes #

• If an id has no rows meeting the criteria (timestamp > date and value > 0), new_date will be NaN. You can handle this by adding a fill value, e.g., first_valid_ts['new_date'] = first_valid_ts['new_date'].fillna(pd.NaT) to keep it as a datetime, or a placeholder string like 'No valid date'.
• Always double-check that your timestamp and date columns are datetime types—string comparisons won't work correctly for dates.

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