0

I have a dataframe, "df", with a datetime index. Here is a rough snapshot of its dimensions:

            V1  V2  V3  V4  V5
1/12/2008   4   15  11  7   1
1/13/2008   5   2   8   7   1
1/14/2008   13  13  9   6   4
1/15/2008   14  15  12  9   3
1/16/2008   1   10  2   12  15
1/17/2008   10  5   9   9   1
1/18/2008   13  11  5   7   2
1/19/2008   2   6   7   9   6
1/20/2008   5   4   14  3   7
1/21/2008   11  11  4   7   15
1/22/2008   9   4   15  10  3
1/23/2008   2   13  13  10  3
1/24/2008   12  15  14  12  8
1/25/2008   1   4   2   6   15

Some of the days in the index are weekends and holidays.

I would like to move all dates, in the datetime index of "df", to their respective closest (US) business day (i.e. Mon-Friday, excluding holidays).

How would you recommend for me to do this? I am aware that Pandas has a "timeseries offset" facility for this. But, I haven't been able to find an example that walks a novice reader through this.

Can you help?

john_mon
  • 487
  • 1
  • 3
  • 13

1 Answers1

1

I am not familiar with this class but after looking at the source code it seems fairly straightforward to achieve this. Keep in mind that it picks the next closest business day meaning Saturday turns into Monday as opposed to Friday. Also making your index be non-unique will decrease performance on your DataFrame, so I suggest assigning these values to a new column.

The one prerequisite is you have to make sure your index is any of these three types, datetime, timedelta, pd.tseries.offsets.Tick.

offset = pd.tseries.offsets.CustomBusinessDay(n=0)

df.assign(
    closest_business_day=df.index.to_series().apply(offset)
)

            V1  V2  V3  V4  V5 closest_business_day
2008-01-12   4  15  11   7   1           2008-01-14
2008-01-13   5   2   8   7   1           2008-01-14
2008-01-14  13  13   9   6   4           2008-01-14
2008-01-15  14  15  12   9   3           2008-01-15
2008-01-16   1  10   2  12  15           2008-01-16
2008-01-17  10   5   9   9   1           2008-01-17
2008-01-18  13  11   5   7   2           2008-01-18
2008-01-19   2   6   7   9   6           2008-01-21
2008-01-20   5   4  14   3   7           2008-01-21
2008-01-21  11  11   4   7  15           2008-01-21
2008-01-22   9   4  15  10   3           2008-01-22
2008-01-23   2  13  13  10   3           2008-01-23
2008-01-24  12  15  14  12   8           2008-01-24
2008-01-25   1   4   2   6  15           2008-01-25
gold_cy
  • 13,648
  • 3
  • 23
  • 45