Pandas resample time series counting backwards (or reverse resample)

4
votes

I want to resample a pandas time series counting backwards. For example, let's set up a simple time series of 11 days:

>>> index = pd.date_range('01-01-2018', '01-11-2018', freq='D')
>>> randint = np.random.randint(low=0, high=9, size=(len(index), 1))

>>> df = pd.DataFrame(randint, index=index, columns=['random'])
>>> print(df)

            random
2018-01-01       8
2018-01-02       8
2018-01-03       1
2018-01-04       4
2018-01-05       3
2018-01-06       5
2018-01-07       2
2018-01-08       6
2018-01-09       5
2018-01-10       1
2018-01-11       3

Default pandas behavior

If I resample it every 5 days, I'd get:

>>> df_5d = df.resample('5D').sum()
>>> print(df_5d)

            random
2018-01-01      24
2018-01-06      19
2018-01-11       3

Basically you have 3 groupings: the first two groups have 5 members and the last group has 1, for a total of 11 members overall:

Start        End
2018-01-01   2018-01-05
2018-01-06   2018-01-10
2018-01-11   2018-01-11

What I want is this

>>> df_5d = df.resample('5D').sum()
>>> print(df_5d)

            random
2018-01-01       8
2018-01-02      21
2018-01-07      17

And the groupings are shown below. See how I counted '5D' backwards starting from the latest date:

Start        End
2018-01-01   2018-01-01
2018-01-02   2018-01-06
2018-01-07   2018-01-11

How do I resample a pandas time series counting backwards?

3
pythonpandastime-seriesresampling
Will there every be missing days in your real data? - GeorgeLPerkins
Also, see stackoverflow.com/questions/37866145/… - GeorgeLPerkins

3 Answers

4
votes

A workaround could be to divise your original df in two, to be able to use the standard resampling, then pd.concat both resampled dataframes, such as:

res_interval = 5
df_res = pd.concat([df[:len(df)%res_interval].resample('{}D'.format(res_interval)).sum(),
                    df[len(df)%res_interval:].resample('{}D'.format(res_interval)).sum()])

and with my random number, I get:

            random
2018-01-01       1
2018-01-02      13
2018-01-07      26
2
votes

You could use

In [452]: t = np.arange(len(df.index)-1, -1, -1) // 5

In [453]: df.reset_index().groupby(t, sort=False)['index'].agg([min, max])
Out[453]:
         min        max
2 2018-01-01 2018-01-01
1 2018-01-02 2018-01-06
0 2018-01-07 2018-01-11
0
votes

I think I got a solution that is quite simple:

You can sort your time series descending by time and then do the resample.

index = pd.date_range('01-01-2018', '01-11-2018', freq='D')
randint = np.random.randint(low=0, high=9, size=(len(index), 1))

df = pd.DataFrame(randint, index=index, columns=['random'])
print(df)

            random
2018-01-01       0
2018-01-02       4
2018-01-03       6
2018-01-04       8
2018-01-05       3
2018-01-06       8
2018-01-07       3
2018-01-08       4
2018-01-09       5
2018-01-10       5
2018-01-11       4

With label and closed ='right', you tell resample that the first day should be considered in the interval of summed up values, and that it should be used as the label for the index.

print(df.sort_index(ascending=False).resample('5D',label='right',closed='right').sum())

random
2018-01-01       0
2018-01-06      29
2018-01-11      21