resample() / DatetimeIndexResampler is not resampling according to the given frequency rule

[gabrielcnr]

When using frequency rule 2Q-SEP it depends on the index of the time series it gives different resampled results. We should expect always March and September dates when using 2Q-SEP, however we might get June and December sometimes.

Code Sample, a copy-pastable example if possible

import pandas as pd

values = [100, 100, 100, 200, 200, 200, 200, 200, 200, 300, 300, 300]
index = pd.date_range('2016-01-01', '2016-12-31', freq='MS')

df = pd.DataFrame({'values': values}, index=index)
df_2Q_SEP = df.resample('2Q-SEP').mean()

# After resampling in 2Q-SEP we have March and September dates
print('GOOD:')
print(df_2Q_SEP)
print()

# Starting the TimeSeries in April
df_2 = pd.DataFrame({'values': values[3:]}, index=index[3:])
df_2_2Q_SEP = df_2.resample('2Q-SEP').mean()

# However here now after resampling in 2Q-SEP we have June and December dates
# which is not what we expected (given the desired frequency rule)
print('BAD:')
print(df_2_2Q_SEP)

Expected Output

GOOD:                           
            values              
2016-03-31     100              
2016-09-30     200              
2017-03-31     300              

BAD:                            
            values              
2016-06-30     200              
2016-12-31     250                                              

output of pd.show_versions()

INSTALLED VERSIONS                                            
------------------                                            
commit: None                                                  
python: 2.7.11.final.0                                        
python-bits: 64                                               
OS: Windows                                                   
OS-release: 7                                                 
machine: AMD64                                                
processor: Intel64 Family 6 Model 44 Stepping 2, GenuineIntel 
byteorder: little                                             
LC_ALL: None                                                  
LANG: None                                                    

pandas: 0.18.0                                                
nose: None                                                    
pip: 8.1.1                                                    
setuptools: 20.6.7                                            
Cython: None                                                  
numpy: 1.11.0                                                 
scipy: None                                                   
statsmodels: None                                             
xarray: None                                                  
IPython: None                                                 
sphinx: None                                                  
patsy: None                                                   
dateutil: 2.5.2                                               
pytz: 2016.3                                                  
blosc: None                                                   
bottleneck: None                                              
tables: None                                                  
numexpr: None                                                 
matplotlib: None                                              
openpyxl: None                                                
xlrd: None                                                    
xlwt: None                                                    
xlsxwriter: None                                              
lxml: None                                                    
bs4: None                                                     
html5lib: None                                                
httplib2: None                                                
apiclient: None                                               
sqlalchemy: None                                              
pymysql: None                                                 
psycopg2: None                                                
jinja2: None                                                  
boto: None                                                    

[jreback]

this is the same as #2764

not really sure what is going on, you are welcome to dig-in.

[MarcoGorelli]

What you showed for "expected output" is actually the current output (and is what it has been since at least 0.24.0)

I presume what you meant for the expected output was:

2016-09-30    200.0
2017-03-31    300.0
Name: values, dtype: float64

?

If that's the case, then I agree, and it looks like this has been a really long-standing bug

Simpler reproducer:

In [1]: pd.date_range('2000-03-01', periods=4, freq='2Q-DEC')
Out[1]: DatetimeIndex(['2000-03-31', '2000-09-30', '2001-03-31', '2001-09-30'], dtype='datetime64[ns]', freq='2Q-DEC')

In [2]: pd.date_range('2000-04-01', periods=4, freq='2Q-DEC')
Out[2]: DatetimeIndex(['2000-06-30', '2000-12-31', '2001-06-30', '2001-12-31'], dtype='datetime64[ns]', freq='2Q-DEC')

Expected:

In [1]: pd.date_range('2000-03-01', periods=4, freq='2Q-DEC')
Out[1]: DatetimeIndex(['2000-06-30', '2000-12-31', '2001-06-30', '2001-12-31'], dtype='datetime64[ns]', freq='2Q-DEC')

In [2]: pd.date_range('2000-04-01', periods=4, freq='2Q-DEC')
Out[2]: DatetimeIndex(['2000-06-30', '2000-12-31', '2001-06-30', '2001-12-31'], dtype='datetime64[ns]', freq='2Q-DEC')

[guroaglen]

Belive I found a pattern (in version 2.0.1):
first timestamp is chosen as if freq='Q'

In  [1]: pd.date_range("2018-08-15",periods=4,freq='Q-DEC')
Out [1]: DatetimeIndex(['2018-09-30', '2018-12-31', '2019-03-31', '2019-06-30'], dtype='datetime64[ns]', freq='Q-DEC')

In  [2]: pd.date_range("2018-08-15",periods=4,freq='2Q-DEC')
Out [2]: DatetimeIndex(['2018-09-30', '2019-03-31', '2019-09-30', '2020-03-31'], dtype='datetime64[ns]', freq='2Q-DEC')



In [3]: pd.date_range("2018-08-15",periods=4,freq='Q-NOV')
Out[3]: DatetimeIndex(['2018-08-31', '2018-11-30', '2019-02-28', '2019-05-31'], dtype='datetime64[ns]', freq='Q-NOV')

In [4]: pd.date_range("2018-08-15",periods=4,freq='2Q-NOV')
Out[4]: DatetimeIndex(['2018-08-31', '2019-02-28', '2019-08-31', '2020-02-29'], dtype='datetime64[ns]', freq='2Q-NOV')