Python Pandas Groupby 只有相同的 ID 并且当列值为 false 时

代码

# filter the columns that you would like to aggregate
c1 = df.filter(like='_sum')
c2 = df.filter(like='_mean')

# create a agg dictionary which maps column names
# to the corresponding aggregation functions
agg_dict = {
    'model': 'last', 
    'update': 'any',
    **dict.fromkeys(c1, 'sum'), 
    **dict.fromkeys(c2, 'mean'),
}

# grouper to identify different blocks of rows followed by True
b = df[::-1]['update'].cumsum()

# group the dataframe by serial_number and blocks and aggregate
result = df.groupby(['serial_number', b]).agg(agg_dict)

# Query the results to remove the rows that do 
# not have any subsequent rows with 'update=true',
# for example, (b, 2023-01-05).
result = result.droplevel(1).query('update').reset_index()

结果

  serial_number       model  update  numerical_1_sum  numerical_2_sum  numerical_mean
0             a  2023-01-03    True               40               40            10.0
1             b  2023-01-04    True               30               20            15.0
2             b  2023-01-02    True               30               20            12.5
3             c  2023-01-04    True               15               10            15.0