Search code examples
pythonpandasresample

pandas resample '3M' to JAN-MAR / APR-JUN /

I have a pandas datable as (showing only 2 lines as example, sorted by DATE_1 after some processing)

DATE_1 DATE _ 2 DIFF
1175 2010-01-01 2010-11-16 320
1170 2010-05-19 2010-11-06 171
.... ... ... ...

so the first date is 2010-01-01.

I want to resample on a 3 months period and sum the DIFF values. I do this by: df[['DIFF']].resample('3M').sum()

what I get is a sum, 3 months after 3 months, as expected. But the dates are not starting where I would expected/wanted them to.
2010-01-31 320
2010-04-30 NaN
2020-07-31 171

How can I make it so that the 3M matches JAN-MAR, APR-JUN, JUL-SEP, OCT-DIC?
2010-03-31 320
2010-06-30 171
2010-09-30 value..
2010-12-31 value ...
2011-03-31 value ...

ps: can't get the last table to format correctly (it does on the preview...)

Solution

  • Closing the left interval on M frequencies has the effect of intervals "going up" from first date of series:

    df['diff'].resample('3M', closed='left').sum()

date1
2010-03-31    320
2010-06-30    171