CODEWITHSUNDEEP
pythonpandas
Gabriel
Gabriel

Reputation: 3797

resample gives different final dates in pandas

I'm having trouble to understand whats going on when I resample into weekly data this way, specifically into mondays. Last day of the df is today, but after resampling the final date is 1 week longer and the rest of the "resampled" data doesn't seem to be in the original dataframe. 

import pandas as pd
import numpy as np
import datetime as dt

today = dt.datetime.now()
end = today
start = end - pd.DateOffset(days=200)
rows = len(pd.date_range(start=start, end=end, freq='B'))

df  = pd.DataFrame(np.random.randn(rows,4),
    index=pd.date_range(start=start, end=end, freq='B'),
    columns =['PX_OPEN', 'PX_LAST', 'PX_HIGH', 'PX_LOW'] )

print df.tail(10)

                       PX_OPEN   PX_LAST   PX_HIGH    PX_LOW
2015-12-02 14:53:11.532  0.000887  0.277900 -0.306899 -0.698697
2015-12-03 14:53:11.532 -0.149329 -1.601389 -0.202008 -0.091503
2015-12-04 14:53:11.532 -0.377587  1.296413  0.204357 -0.764215
2015-12-07 14:53:11.532 -1.487945 -0.618986  0.843712  0.989913
2015-12-08 14:53:11.532  2.950980  1.290211  0.365665  0.633706
2015-12-09 14:53:11.532  0.156047  0.958483  0.574209 -0.708996
2015-12-10 14:53:11.532  0.696625 -0.547642 -1.299712  1.728022
2015-12-11 14:53:11.532  2.244693  1.034026 -0.914141 -2.604405
2015-12-14 14:53:11.532  0.087407  1.742234 -0.643504  0.812742
2015-12-15 14:53:11.532  1.861020 -0.637109  1.285085 -0.816025

and if we do:

print df.resample('W-MON').tail(5)

             PX_OPEN   PX_LAST   PX_HIGH    PX_LOW
2015-11-23  0.003513 -0.246844 -0.031537  0.846289
2015-11-30 -0.338132  0.607998  0.087007  0.394458
2015-12-07 -0.325123 -0.337976  0.129483 -0.136570
2015-12-14  1.227150  0.895462 -0.383497 -0.027786
2015-12-21  1.861020 -0.637109  1.285085 -0.816025

so if you notice, last row of the original df has date 2015-12-15 but last day of the resampled df has date 2015-12-21. And also the rest of the resampled values don't seem to appear in the original df.

Upvotes: 3

Views: 1068

Answers (1)

Andy Hayden
Andy Hayden

Reputation: 375565

resample defaults to mean. The mean for the week from 12-15 to 12-21 is simply the last row...

Apparently the default label is not correct (in the documentation; or for this example):

In [11]: df.resample('W-MON')
Out[11]:
             PX_OPEN   PX_LAST   PX_HIGH    PX_LOW
2015-12-07 -0.503494 -0.161516  0.134791 -0.141126
2015-12-14  1.227150  0.895462 -0.383497 -0.027786
2015-12-21  1.861020 -0.637109  1.285085 -0.816025

In [12]: df.resample('W-MON', label="left")
Out[12]:
             PX_OPEN   PX_LAST   PX_HIGH    PX_LOW
2015-11-30 -0.503494 -0.161516  0.134791 -0.141126
2015-12-07  1.227150  0.895462 -0.383497 -0.027786
2015-12-14  1.861020 -0.637109  1.285085 -0.816025

it appears to be defaulting to "right". "left" is what you want.

Similarly you probably want the left side to be closed (inclusive):

In [13]: df.resample('W-MON', label="left", closed="left")
Out[13]:
             PX_OPEN   PX_LAST   PX_HIGH    PX_LOW
2015-11-30 -0.175343 -0.009025 -0.101517 -0.518138
2015-12-07  0.912080  0.423218 -0.086053  0.007648
2015-12-14  0.974214  0.552563  0.320791 -0.001642

which gives numbers more like what you were expecting.

Upvotes: 3

Related Questions