Mean between two datetimes; if NaN, get last non-NaN value

Question

Yesterday I asked this question (with some good answers) which is very similar, but slightly different from the problem I'm presented with now. Say I have the following pd.DataFrame (dict):

    eff_timestamp       val         id  begin_timestamp     end_timestamp
0   2021-01-01 00:00:00 -0.710230   1   2021-01-01 02:00:00 2021-01-01 05:30:00
1   2021-01-01 01:00:00 0.121464    1   2021-01-01 02:00:00 2021-01-01 05:30:00
2   2021-01-01 02:00:00 -0.156328   1   2021-01-01 02:00:00 2021-01-01 05:30:00
3   2021-01-01 03:00:00 0.788685    1   2021-01-01 02:00:00 2021-01-01 05:30:00
4   2021-01-01 04:00:00 0.505210    1   2021-01-01 02:00:00 2021-01-01 05:30:00
5   2021-01-01 05:00:00 -0.738344   1   2021-01-01 02:00:00 2021-01-01 05:30:00
6   2021-01-01 06:00:00 0.266910    1   2021-01-01 02:00:00 2021-01-01 05:30:00
7   2021-01-01 07:00:00 -0.587401   1   2021-01-01 02:00:00 2021-01-01 05:30:00
8   2021-01-02 00:00:00 -0.160692   2   2021-01-02 12:00:00 2021-01-02 15:30:00
9   2021-01-02 01:00:00 0.306354    2   2021-01-02 12:00:00 2021-01-02 15:30:00
10  2021-01-02 02:00:00 NaN         2   2021-01-02 12:00:00 2021-01-02 15:30:00
11  2021-01-02 03:00:00 NaN         2   2021-01-02 12:00:00 2021-01-02 15:30:00
12  2021-01-02 04:00:00 NaN         2   2021-01-02 12:00:00 2021-01-02 15:30:00
13  2021-01-02 05:00:00 NaN         2   2021-01-02 12:00:00 2021-01-02 15:30:00
14  2021-01-02 06:00:00 NaN         2   2021-01-02 12:00:00 2021-01-02 15:30:00
15  2021-01-02 07:00:00 -0.349705   2   2021-01-02 12:00:00 2021-01-02 15:30:00

I would like to get the mean value of val for each unique id, for those val's that lie between the begin_timestamp and end_timestamp. If there are no rows that satisfy that criteria, I'd like to get the last value for that id before that period. Note that in this example, id=2 has no rows that satisfy the criteria. Previously I could slice the data so I only keep the rows between the begin and end_timestamp, and then use a groupby. The solution from my previous post then replaces the NaN value in the groupby object. However, in the example above, id=2 has no rows at all that satisfy the criteria, and therefore there is no NaN value created that can be replaced. So if I slice the data based above on the criteria:

sliced = df[(df.eff_timestamp > df.begin_timestamp) & (df.eff_timestamp < df.end_timestamp)]
sliced
>>>     

    eff_timestamp       val         id  begin_timestamp     end_timestamp
3   2021-01-01 03:00:00 0.788685    1   2021-01-01 02:00:00 2021-01-01 05:30:00
4   2021-01-01 04:00:00 0.505210    1   2021-01-01 02:00:00 2021-01-01 05:30:00
5   2021-01-01 05:00:00 -0.738344   1   2021-01-01 02:00:00 2021-01-01 05:30:00

sliced.groupby('id').val.mean()
>>>
id
1    0.185184
Name: val, dtype: float64

This result only includes id=1 with the mean value, but there is no value for id=2. How would I, instead of the mean, include the last available value for id=2, which is -0.349705?

user_15438033 · Answer

Create a temp column between_time. Then Groupby id column and then, in apply add the condition - > If for a particular id is there any value that lies within the range? If yes, take the mean else take the value present at last_valid_index.

result = (
    df.assign(
        between_time=(df.eff_timestamp > df.begin_timestamp) & (df.eff_timestamp < df.end_timestamp))
    .groupby('id')
    .apply(
        lambda x: x.loc[x['between_time']]['val'].mean()
        if any(x['between_time'].values)
        else
        x.loc[x['val'].last_valid_index()]['val']
    )
)

OUTPUT:

id
1    0.185184
2   -0.349705
dtype: float64

Mean between two datetimes; if NaN, get last non-NaN value

1 Answers

OUTPUT: