Assign last value within 1 minute intervals to rows of a pandas DataFrame

user_8019519 0
Viewed 194

I have a DataFrame like this

    time                    val

    2019-10-01 04:00:00.000 568830.000
    2019-10-01 17:47:29.794 37612.000
    2019-10-01 19:41:03.697 37.000
    2019-10-01 19:41:03.698 37612.000
    2019-10-02 00:16:07.224 376.000
    2019-10-02 00:16:07.225 37612.000
    2019-10-02 01:19:05.135 3761.000
    2019-10-02 01:19:05.136 37612.000
    2019-10-02 08:26:27.623 36612.000
    2019-10-02 08:26:27.624 36493.000
    2019-10-03 07:08:54.455 364.000
    2019-10-03 07:08:54.456 36493.000

I want to create a new column that contains the last value within X minutes intervals e.g. 1 minute of column val.

Expected output:

    time                    val        result

    2019-10-01 04:00:00.000 568830.000 568830.000
    2019-10-01 17:47:29.794 37612.000  37612.000
    2019-10-01 19:41:03.697 37.000     37612.000
    2019-10-01 19:41:03.698 37612.000  37612.000
    2019-10-02 00:16:07.224 376.000    37612.000
    2019-10-02 00:16:07.225 37612.000  37612.000
    2019-10-02 01:19:05.135 3761.000   37612.000
    2019-10-02 01:19:05.136 37612.000  37612.000
    2019-10-02 08:26:27.623 36612.000  36493.000 
    2019-10-02 08:26:27.624 36493.000  36493.000
    2019-10-03 07:08:54.455 364.000    36493.000
    2019-10-03 07:08:54.456 36493.000  36493.000
pandas python
2 Answers

Use for DatetimeIndex DataFrame.resample with Resampler.transform and last:

df['result'] = df.resample('T')['val'].transform('last')
print (df)
                              val    result
time                                       
2019-10-01 04:00:00.000  568830.0  568830.0
2019-10-01 17:47:29.794   37612.0   37612.0
2019-10-01 19:41:03.697      37.0   37612.0
2019-10-01 19:41:03.698   37612.0   37612.0
2019-10-02 00:16:07.224     376.0   37612.0
2019-10-02 00:16:07.225   37612.0   37612.0
2019-10-02 01:19:05.135    3761.0   37612.0
2019-10-02 01:19:05.136   37612.0   37612.0
2019-10-02 08:26:27.623   36612.0   36493.0
2019-10-02 08:26:27.624   36493.0   36493.0
2019-10-03 07:08:54.455     364.0   36493.0
2019-10-03 07:08:54.456   36493.0   36493.0

And for column time add parameter on:

df['result'] = df.resample('T', on='time')['val'].transform('last')
print (df)
                      time       val    result
0  2019-10-01 04:00:00.000  568830.0  568830.0
1  2019-10-01 17:47:29.794   37612.0   37612.0
2  2019-10-01 19:41:03.697      37.0   37612.0
3  2019-10-01 19:41:03.698   37612.0   37612.0
4  2019-10-02 00:16:07.224     376.0   37612.0
5  2019-10-02 00:16:07.225   37612.0   37612.0
6  2019-10-02 01:19:05.135    3761.0   37612.0
7  2019-10-02 01:19:05.136   37612.0   37612.0
8  2019-10-02 08:26:27.623   36612.0   36493.0
9  2019-10-02 08:26:27.624   36493.0   36493.0
10 2019-10-03 07:08:54.455     364.0   36493.0
11 2019-10-03 07:08:54.456   36493.0   36493.0
user_29010020

So assuming that you time column is of type string. You can use the following line which will perform the group by.

df.groupby(df.time.map(lambda t: pd.to_datetime(t).minute)).val.last()

But to then make a new column that contains these values you can use transform which would look like the following

df['result'] = df.groupby(df.time.map(lambda t: pd.to_datetime(t).minute)).val.transform('last')

Note that I am assuming here that you want to group by the minute part of the timestamp.

user_49668250
Related