Business day resampling

[0x0L]

Hi everyone,

I just stumbled across this odd behaviour:

For a daily time series, I would expect resample('B', how='last') to be equivalent to selecting rows whose weekday is not Saturday or Sunday. However, resample groups together Fridays, Saturdays and Sundays and hence we get Sunday's value on Friday.

In the time series context, I guess most people would expect resample('B', ...) to bin Saturdays, Sundays and Mondays, so that no looking into the future occurs.

Am I missing something?

[jreback]

pls show a specific example

[0x0L]

In [2]: pd.__version__
Out[2]: '0.16.2'

In [3]: s = pd.Series(list(range(10)), pd.date_range('2015-09-01', '2015-09-10'))

In [4]: s
Out[4]: 
2015-09-01    0
2015-09-02    1
2015-09-03    2
2015-09-04    3  <--- Friday
2015-09-05    4
2015-09-06    5
2015-09-07    6  <--- Monday
2015-09-08    7
2015-09-09    8
2015-09-10    9
Freq: D, dtype: int64

In [5]: s.resample('B', how='last')
Out[5]: 
2015-09-01    0
2015-09-02    1
2015-09-03    2
2015-09-04    5  <--- expected 3
2015-09-07    6
2015-09-08    7
2015-09-09    8
2015-09-10    9
Freq: B, dtype: int64

[jreback]

see also #11128

In [34]: import calendar
In [35]: weekdays = [calendar.day_name[i].lower() for i in range(7)]
In [36]: s = pd.Series(list(range(10)), pd.date_range('2015-09-01', '2015-09-10'))
In [37]: s2 = s.to_frame('value').assign(weekday=Series(s.index.weekday).map(Series(weekdays)).values)

In [38]: s2
Out[38]: 
            value    weekday
2015-09-01      0    tuesday
2015-09-02      1  wednesday
2015-09-03      2   thursday
2015-09-04      3     friday
2015-09-05      4   saturday
2015-09-06      5     sunday
2015-09-07      6     monday
2015-09-08      7    tuesday
2015-09-09      8  wednesday
2015-09-10      9   thursday

This is equivalent to .resample('B')

In [39]: s2.groupby(pd.Grouper(freq='B')).last()
Out[39]: 
            value    weekday
2015-09-01      0    tuesday
2015-09-02      1  wednesday
2015-09-03      2   thursday
2015-09-04      5     sunday
2015-09-07      6     monday
2015-09-08      7    tuesday
2015-09-09      8  wednesday
2015-09-10      9   thursday

You can do this

In [40]: def f(x):
    # x is a Timestamp
    if x.weekday() < 5:
        return x
    return x + pd.offsets.BusinessDay(0)
   ....: 

In [41]: s2.groupby(f).last()
Out[41]: 
            value    weekday
2015-09-01      0    tuesday
2015-09-02      1  wednesday
2015-09-03      2   thursday
2015-09-04      3     friday
2015-09-07      6     monday
2015-09-08      7    tuesday
2015-09-09      8  wednesday
2015-09-10      9   thursday

[jreback]

I think you should need to create another business day offset to actually do this with a freq. E.g. you could sub-class BusinessDay and create 2 new offsets, e.g. BF and BM (or somesuch), where BF would be the same as BusinessDay. So i'll mark this as an enhancement.

[0x0L]

I didn't dig into the code yet, but maybe we can use the keyword arg convention ?

On a more general level, I'm not sure the present behaviour should be the default one. It really doesn't feel right to have a peek into the future in the time series context.

[jreback]

you misunderstand, you would need to create a new frequency. This is a long time convention. You could add to, but not change this.

[0x0L]

However,

In [35]: s2.to_period('B')
Out[35]: 
            value    weekday
2015-09-01      0    tuesday
2015-09-02      1  wednesday
2015-09-03      2   thursday
2015-09-04      3     friday
2015-09-07      4   saturday
2015-09-07      5     sunday
2015-09-07      6     monday
2015-09-08      7    tuesday
2015-09-09      8  wednesday
2015-09-10      9   thursday

groups together Saturdays, Sundays and Mondays which is the sane default behaviour IMO.

Don't you agree this is a bit inconsistent ?

[jreback]

@0x0L you are talking about 2 different things here. periods are timespans which encompass a start-end. timestamps are points-in-time. conversions between one and another must use a convention (e.g. start/end).

However, resample is a point-in-time operation (at least on a DatetimeIndex). So the issue is what convention to use. The existing convention is to include fri-sat-sun as indicated.

doing to_period just marks the intervals, and is NOT a resample

[0x0L]

@jreback I understand. That's what I was trying to say when I earlier mentioned we could use the convention keyword arg. Right now,

In [10]: x = s2.resample('B', how='last', convention='start')

In [11]: y = s2.resample('B', how='last', convention='end')

are the same. Why not use it to make resample group sat-sun-mon ?