0

I have been looking for a way to resample a dataframe backwards in time. So the last row is included in the result.

Imagine this is my dataframe

                                Close  Volume
Datetime                                     
2022-09-01 09:30:00-04:00  314.589996   73674
2022-09-01 09:31:00-04:00  314.100006   21192
2022-09-01 09:32:00-04:00  314.119995   35324
2022-09-01 09:33:00-04:00  313.809998    9273
2022-09-01 09:34:00-04:00  313.600006    6713
...                               ...     ...
2022-09-01 15:55:00-04:00  316.600006   14146
2022-09-01 15:56:00-04:00  316.640015   14465
2022-09-01 15:57:00-04:00  316.649994   25201
2022-09-01 15:58:00-04:00  316.875000   20435
2022-09-01 15:59:00-04:00  317.070007  100410

I resample using 75min as example (but it must work with any value>1 min).

df = df.resample("75min").agg({'Close': 'last', 'Volume': 'sum', })

The result is:

                                Close   Volume
Datetime                                      
2022-09-01 08:45:00-04:00  314.339996   357726
2022-09-01 10:00:00-04:00  314.320007   801669
2022-09-01 11:15:00-04:00  314.850006   533845
2022-09-01 12:30:00-04:00  314.940002   331956
2022-09-01 13:45:00-04:00  315.946594   432379
2022-09-01 15:00:00-04:00  317.070007  3328100

But I actually want to resample from the last row (15:59) and then backwards in time.

                                Close   Volume
Datetime                                      
2022-09-01 75min earlier   close        volume
2022-09-01 75min earlier   close        volume
2022-09-01 75min earlier   close        volume
2022-09-01 75min earlier   close        volume
2022-09-01 14:44:00-04:00  close        volume
2022-09-01 15:59:00-04:00  317.070007   3328100

I have been trying to fix this for days but cannot find a solution so far. I would be great if someone here could help me out.

Thanks! Ivo

Ivo
  • 21
  • 4

1 Answers1

1

I think adding the origin="end" parameter to resample is what you want.

>>> df.resample('75min', origin='end').agg({'Close': 'last', 'Volume': 'sum'})
                                Close  Volume
Datetime
2022-09-01 09:44:00-04:00  313.600006  146176
2022-09-01 10:59:00-04:00         NaN       0
2022-09-01 12:14:00-04:00         NaN       0
2022-09-01 13:29:00-04:00         NaN       0
2022-09-01 14:44:00-04:00         NaN       0
2022-09-01 15:59:00-04:00  317.070007  174657
BeRT2me
  • 12,699
  • 2
  • 13
  • 31
Xnot
  • 171
  • 1
  • 3
  • Thank you so much. Such a simple solution... I read about origin=end_day which confused me.. Thanks again. – Ivo Sep 29 '22 at 06:36