CODEWITHSUNDEEP
python-3.xpandasdatetime
Jr Analyst
Jr Analyst

Reputation: 68

Shifting datetime index to first Monday/Tuesday/etc of next month

I feel like this should be a) relatively simple, and b) already answered somewhere else, but I can't seem to figure it out and a few hours of googling have led me nowhere...

I have some monthly data in a pandas dataframe. The index is a datetime index. I would like to shift this index to the first Monday/Tuesday/etc of the next month, but I can't seem to figure out how to do it...

I've figured out how to shift to the first business day and last business day of the month using freq='BMS' and freq='BM' respectively using the shift() method. However, I'm lost as to how I can proceed from there. An example of shifting my data index is written below:

#setting up an arbitrary example df with a similar datetime index:
date_rng = pd.date_range(start='1/1/1950', end='1/08/1955', freq='MS').shift(14, freq='D')
test_df = pd.DataFrame(range(len(date_rng)), index=date_rng, columns=['ticker_name'])

#what I know how to do so far
rng_index = test_df.index
new_range = rng_index.shift(1, freq='BMS') #first business day of month (use 'BM' for last business day of month

test_df.index = new_range #My df should now have a new index pointing to the first business day of the next month.

Instead of pointing to the first business day of the next month, I would like to get first Monday or first Tuesday or first ... of the next month.

Any and all help is greatly appreciated! Cheers

Upvotes: 0

Views: 649

Answers (1)

Quang Hoang
Quang Hoang

Reputation: 150745

After playing with a manual solution, it seems like pandas has a built-in solution:

date_rng = pd.date_range(start='1/1/1950', end='1/08/1955', freq='MS').shift(14, freq='D')

date_rng.shift(1, freq='WOM-1MON')

Output seems to be correct:

DatetimeIndex(['1950-02-06', '1950-03-06', '1950-04-03', '1950-05-01',
               '1950-06-05', '1950-07-03', '1950-08-07', '1950-09-04',
               '1950-10-02', '1950-11-06', '1950-12-04', '1951-01-01',
               '1951-02-05', '1951-03-05', '1951-04-02', '1951-05-07',
               '1951-06-04', '1951-07-02', '1951-08-06', '1951-09-03',
               '1951-10-01', '1951-11-05', '1951-12-03', '1952-01-07',
               '1952-02-04', '1952-03-03', '1952-04-07', '1952-05-05',
               '1952-06-02', '1952-07-07', '1952-08-04', '1952-09-01',
               '1952-10-06', '1952-11-03', '1952-12-01', '1953-01-05',
               '1953-02-02', '1953-03-02', '1953-04-06', '1953-05-04',
               '1953-06-01', '1953-07-06', '1953-08-03', '1953-09-07',
               '1953-10-05', '1953-11-02', '1953-12-07', '1954-01-04',
               '1954-02-01', '1954-03-01', '1954-04-05', '1954-05-03',
               '1954-06-07', '1954-07-05', '1954-08-02', '1954-09-06',
               '1954-10-04', '1954-11-01', '1954-12-06', '1955-01-03',
               '1955-02-07'],
              dtype='datetime64[ns]', freq='WOM-1MON')

Upvotes: 2

Related Questions